ANBOB™

KGLH0 associated with the SQL Area in the Shared Pool.  Each child cursor consumes KGLH0. Large allocation in KGLH0 can occur due to many child cursors and a lack of sharing of the parent cursor.A library_cache dump(“library_cache level 16”) reveals that each child cursor is associated with one KGLH0 and one SQLA,  where KGLH0 stores environment information, and SQLA parsing tree and xplan. When memory is reclaimed,  KGLH0 stays, whereas SQLA is deallocated. The later re-use of the same child cursor will result a hard-parsing.

trace file

===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
___________________________ ____________
"free memory "                 101779936
"SQLA "                           390848
"KGLH0 "                       653554600  <<< 
"KGLH0 "                       653554600  <<< 

PARENT CURSOR

It stores the SQL text of the cursor. When two statements are identical word-by-word, they will share the same parent Cursor.
Every parent cursor would execute with at least one child cursor created for it.
Parent cursors are represented in the view V$SQLAREA. VERSION_COUNT column in the v$sqlarea can tell us how many child cursors does this parent cursor have.

CHILD CURSOR

Each parent has at least one child cursor and can have more than 1 child cursors also
While parent cursor stores the SQL Text, the child cursor stores other important information related to SQL statement like: Environment details, Statistics details, Bind Variables details, Execution Plan details Bind Variables details.
Child Cursor takes less memory space as SQL Text is not stored in child cursor
Every child cursor must belong to a parent
Child cursor decides whether a query will undergo a hard parse or a soft parse. You may find situation that SQL query is same for two statements so Parent cursors are same but the child cursor is not shareable to SQL goes for hard parse (re-compile).
Parent cursors are represented in the view V$SQL
V$SQL_SHARED_CURSOR is very useful view as it provides the reasons why the optimizer decided mark the cursor as un-shared. So anytime you see that SQL statement was same and still hard parse happened, look at this view.

cursor_sharing DATABASE PARAMETER
Since we are discussing parent and child cursors, it is very important that we discuss a bit about the cursor_sharing database parameter. CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

cursor_sharing database parameters can have three different values:

EXACT Only allows statements with identical text to share the same cursor.
FORCE Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

Diag methods

When witnessing a high allocation for the KGLH0 heap chunks in the trace file , a heap dump can be taken to get more detailed information:

sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
-- or --
alter system set events '10235 trace name context forever, level 65536';
alter system set events 'immediate trace name heapdump level 536870914';

NOTE: The event 10235 with level 65536 is not needed for Oracle release 12.2.0 and above. Any permanent allocations with comments will show in trace file.

– If you encountered ORA-4031 error set the following:

alter system set events '4031 {occurence:end_after 1} heapdump(536870914) library_cache(74)';

When KGLH0 is high compared to shared pool size, manually dump heaps and library cache:

conn / as sysdba
-- Library cache dump:
oradebug setmypid
oradebug unlimit
oradebug dump library_cache 74
oradebug tracefile_name

conn / as sysdba
-- Heapdump dump from another session:
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace

Once information gathered, unset the events:

-- 4031 if set before
alter system set events '4031 off';
-- 10235
alter system set events '10235 trace name context off';

Knows ISSUE
1. Disable Extended Cursor Sharing (ECS) and Adaptive Cursor Sharing (ACS) by setting:

alter system set "_optimizer_extended_cursor_sharing"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_adaptive_cursor_sharing"= false scope=both;

2, High number of child cursors or version count

Different version has different value:
11.2.0.4: _cursor_obsolete_threshold=1024
12.1.0.1: _cursor_obsolete_threshold=1024
12.1.0.2: _cursor_obsolete_threshold=1024
12.2.0.1: _cursor_obsolete_threshold=8192
18c: _cursor_obsolete_threshold=8192
19c: _cursor_obsolete_threshold=8192

Reduce the value of _cursor_obsolete_threshold to 1024 or less like 200.

ALTER SYSTEM SET "_cursor_obsolete_threshold"=200 scope=spfile;
Shutdown immediate;
Startup;

3, bugs

ORA-04031 Memory Errors with Argument KGLH0^ (Doc ID 2096561.1)

For ORA-04031 Memory Errors with Argument KGLH0^ if:

1. Top heap of shared pool allocation in “KGLH0”:
For Versions >= 11.2.0.4 but BELOW 12.1 – Unpublished BUG:14772891, see NOTE:14772891.8
For Versions BELOW 12.2 – BUG:20370037, see NOTE:20370037.8

2. Top heap of shared pool allocation in “init_heap_kfsg”:
For Versions >= 12.2 but BELOW 20.1 – Unpublished BUG:30173113, see NOTE:30173113.8 NOTE:2591444.1

3. On Multitenant Database excessive amounts of KGLH0 memory and parameter SHARED_POOL_SIZE or SGA_MIN_SIZE are set at the PDB Level:
For Version > 12.2, see NOTE:2590172.1

Scripts manul purge SQL

DECLARE
   curstr   VARCHAR2 (300);
BEGIN
   FOR cur
      IN (SELECT address, hash_value
            FROM v$sqlarea
           WHERE     executions=1             
           AND LAST_LOAD_TIME < SYSDATE - 1/24
)
   LOOP
      curstr := 'begin sys.dbms_shared_pool.purge(:b1,''c'',65); end;';

      --     DBMS_OUTPUT.put_line ('exec sys.dbms_shared_pool.purge('':b1,:b2'',''c'',65)');
      EXECUTE IMMEDIATE (curstr) USING cur.address || ',' || cur.hash_value;
	  EXCEPTION
         WHEN OTHERS
         THEN
            null;
      END;
   END LOOP;
END;
/

— over —

打赏

微信扫一扫，打赏作者吧～