Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the relevant knowledge points of Oracle Cursor". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

A cursor is a basic object that is a complete executable representation constructed by SQL statements or PL/SQL programmers and can be used and reused by any authorized session. Cursors must be created, located (found by search), destroyed (recycled), invalidated and overloaded. If any part of the cursor is not in the shared pool and is needed for any reason, the cursor must be reloaded, which degrades performance.

Developers usually have a good understanding of cursors because they need to specifically create, open, execute, get, and close cursors. DBA usually treats cursors as simple blocks of memory related to SQL. However, this overly simple point limits our ability to create solutions to performance problems related to cursors. Therefore, if you take the time to better understand cursors, you will notice a significant increase in performance solution options.

Parent and child cursors

The term cursor itself is an abstract concept that refers to shared information (located in the shared SQL area), private information (located in the PGA of the session), and library cache chain nodes used to locate various cursor components (called handle when referencing library cache). Unfortunately, this multi-purpose definition also adds confusion. When a cursor is closed, Oracle does not simply recycle the three cursor components. Instead, Oracle may recycle cursor components on demand.

When a cursor is executed for the first time, there is a parent and child cursor. Subsequent sessions, even if the same session executes the same SQL statement (with the same hash), may use different child cursors. Although the SQL statements are identical in text, subcursors are created to capture specific features, such as differences in optimization patterns (such as first_rows), which can lead to different execution plans or different session-level parameters (cursor_sharing=similar). The following example simply shows that the same session executes the same SQL statement twice, but the alter session command is executed between executions, which is enough to force the creation of an additional subcursor. The trace command is used to prove that two child cursors were created.

SQL > oradebug setmypidStatement processed.SQL > alter session set optimizer_mode = all_rows;Session altered.SQL > select * from dual;D-XSQL > alter session set optimizer_mode = first_rows;Session altered.SQL > select * from dual;D-XSQL > alter session set events' immediate trace name library_cache level 10 session altered.SQL > oradebug tracefile_name/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_6675.trc

The following is part of the trace file created by the trace command above. We search for select * from dual to locate what we care about and check the SQL statement. At this point, we are interested in that this SQL statement is executed by only one session, but it creates two child cursors.

Bucket: # = 108289 Mutex=0xc5eeae00 (3298534883328, 1118, 0,6) LibraryHandle: Address=0xcf2e9a48 Hash=382da701 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select * from dual FullHashValue=0d54fc02b2ad4044a2cb0974382da701 Namespace=SQL AREA (00) Type=CURSOR (00) ContainerId=1 ContainerUid=1 Identifier=942515969 OwnerIdn=0 Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1 Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0 Concurrency: DependencyMutex=0xcf2e9af8 (0,2,0,0) Mutex=0xcf2e9b98 (768,37,0) 6) Flags=RON/PIN/TIM/PN0/DBN/ [10012841] Flags2= [0000] WaitersLists: Lock=0xcf2e9ad8 [0xcf2e9ad8re0xcf2e9ad8] Pin=0xcf2e9ab8 [0xcf2e9ab8 report0xcf2e9ab8] LoadLock=0xcf2e9b30 [0xcf2e9b30 remark 0xcf2e9b30] Timestamp: Current=04-17-2019 09:33:16 HandleReference: Address=0xcf2e9c20 Handle= (nil) Flags= [00] ReferenceList: Reference: Address=0x84497a08 Handle=0x818e2850 Flags=ROD [21] Reference: Address=0x84c9e3d0 Handle=0xb28b76a0 Flags=ROD [21] LibraryObject: Address=0xbd5972a8 HeapMask=0000-0001-0001-0000 Flags=EXS [0000] Flags2= [0000] PublicFlags= [0000] DataBlocks: Block: # ='0' name= KGLH0 ^ 382da701 pins=0 Change=NONE Heap=0x83043cc0 Pointer=0xbd597378 Extent=0xbd597200 Flags=I/-/P/A/- FreedLocation=0 Alloc=3.390625 Size=3.976562 LoadTime=4111958371 ChildTable: size='16' Child: id='0' Table=0xbd598128 Reference=0xbd597bf8 Handle=0xb38e2928 Child: id='1' Table=0xbd598128 Reference=0xbd597f48 Handle=0xbdfc20a8 NamespaceDump: Parent Cursor: sql_id=a5ks9fhw2v9s1 parent=0xbd597378 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0 CursorDiagnosticsNodes: ChildNode: ChildNumber=0 ID=3 reason=Optimizer mismatch (10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=2

Relationships between library cache objects must be maintained not only for execution purposes, but also when one of the components changes. Suppose a table is referenced by 2000 SQL statements, 100 functions, and 20 packages. Now assume that a column of the table is renamed. Oracle will invalidate all relevant SQL statements and program structures. This can lead to cascading effects when requesting latching and locking. A combination of multiple related sessions, invalidation, recompilation, and timing causes the entire Oracle instance to be locked. It is clear that Oracle is aware of the seriousness of the problem and is actively reducing the likelihood of such a situation. But for each DBA to understand the relationship between library cache is very complex and can sometimes lead to problems.

Cursor Building

A cursor is created when a search in library cache does not find a cursor. This is hard parsing. Obviously this is a relatively expensive operation that requires requesting memory management (allocation and possible recycling), using latching to ensure serialization, using locking to prevent inappropriate changes, consuming CPU resources to execute kernel code, and may require IO operations to insert data dictionary information into row cache.

Cursors are created using data from a shared pool, and if the data is not currently in the shared pool, Oracle creates its own SQL statement to retrieve data from the data dictionary table. The SQL dynamically created by Oracle is named recursive SQL and runs it. The data needed to create a cursor Oracle is optimizer statistics, session information, security information, object information and object association information.

Cursors are created by blocks of shared pool memory called heaps. Traditionally, different SQL statements require blocks of memory of different sizes. Common SQL statements usually request blocks of memory the size of 4KB. As with free exten management, requesting memory blocks of inconsistent sizes can lead to allocation, performance, and efficiency problems. Starting with Oracle 10gr2, Oracle defines all memory blocks as 4KB. When a suitable block of memory cannot be found quickly, Oracle may eventually give up and posts a 4031 error "out of shared poll memory" and stop processing the SQL statement.

Cursor Searching Introduction

Like every buffer in buffer cache, each parent and child cursor must be located and the search must be fast. This will require memory, a search structure, serialization, kernel code and a large number of CPU resources.

Because cursors and program structures are stored in library cache, there is a structure to locate objects. Oracle chooses to use a hash algorithm similar to the related hash. Part of the parsing operation is to determine whether a cursor is currently stored in the library cache. If you do find the cursor in library cache, you do some parsing, so it's really a soft parse. However, if the cursor is not found in library cache, the entire cursor needs to be created, so it is hard parsing. Cursor creation and hard parsing are quite expensive operations.

Cursor Pinning and Locking

Fixed cursors are similar to fixed buffer. It is used to ensure that cursors are not recycled (sometimes called corruption) when they are referenced. Cursors are obviously not relational structures, but SQL is related to relational structures (such as employee tables), which are used to build cursors (such as sys.col$), so locks are used-that is, queues are used. Cursor queues are also called CU queues and, like other queues, can be detected through Oracle's wait interface.

Fix the cursor when you create and execute the cursor. It's easy to understand that when you create a cursor, it's a memory structure, and you don't want other processes to reclaim related memory. Normally, cursors are not fixed after creation and execution. This means that after you execute a cursor and wait 2 minutes, you want to execute the same cursor again, and the cursor may have been reclaimed. If this happens and the desired cursor is not found in library cache, hard parsing will be performed and the cursor will be completely recreated.

Locking may also occur when creating and executing cursors. But it is different from the fixed travel ban. The fixed focus is on memory reclamation. The lock ensures that the table associated with the cursor is not modified when the cursor is created and executed. Obviously, this can cause some rather strange situations, which Oracle won't allow to happen.

This is the end of the content of "what are the relevant knowledge points of Oracle Cursor". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.