690 likes | 896 Views
Library Cache Internals. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Introduction Multiple Parent Cursors Multiple Child Cursors DBMS_SHARED_POOL Summary. Agenda. Shared Pool. Shared pool includes: Permanent - Static structures - allocated at startup
E N D
Library CacheInternals Julian Dyke Independent Consultant Web Version juliandyke.com
Introduction Multiple Parent Cursors Multiple Child Cursors DBMS_SHARED_POOL Summary Agenda
Shared Pool • Shared pool includes: • Permanent - Static structures - allocated at startup • Session arrays • Process arrays • Segmented arrays • Other static structures • Heap - Dynamic structures - allocated at runtime • Library Cache • Dictionary (Row) Cache
Shared Pool Size • Determines amount of memory allocated to library cache • In Oracle 10.2 and above specified as follows -- If Automatic Memory ManagementIF SGA_TARGET specified THEN IF SHARED_POOL_SIZE specified THEN minimum size of shared pool = SHARED_POOL_SIZE ELSE size of shared pool determined automaticallyELSE -- Manual memory management IF SHARED_POOL_SIZE specified THEN size of shared pool = SHARED_POOL_SIZE ELSE IF 32-bit platform size = 32M IF 64-bit platform size = 84M
Library Cache • Contains objects of various types required to parse and execute SQL statements including • tables • indexes • cursors • parent • child • PL/SQL • procedures • functions • packages • Types • methods • Java classes
Object Types • Every object in the library cache has an object type. Object types include: • Cursor • Table • Index • Cluster • View • Synonym • Sequence • Procedure • Function • Package • Package Body • Trigger • Type • Type Body • Object • User • Database Link • Pipe • Table Partition • Index Partition • LOB • Library • Directory • Queue • Index-Organized Table • Java Source • Java Class • Java Resource • Java JAR • Table Subpartition • Index Subpartition • LOB Partition • LOB Subpartition • Summary • Dimension • Stored Outline
Namespaces • Every object in the library cache belongs to a namespace Namespaces include: • Cursor • Table/Procedure • Body • Trigger • Index • Cluster • Object • Java Source • Java Resource • Context Policy • Publish/Subscribe • Dimension • Application Context • Stored Outline • Ruleset • Resource Plan • Resource Consumer Group • Subscription • Location • Remote Object • Snapshot Metadata • Java Shared Data • Security Profile
V$DB_OBJECT_CACHE • Reports all objects currently in the library cache • Based on X$KGLOB
Library Cache Dumps • To dump the contents of the library cache use ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL n'; • where <n> specifies information to be included • 1 - include library cache statistics • 2 - include library cache hash table • 4 - include library cache handles and objects • 8 - include dependencies, read-only dependencies, accesses, authorizations, translations, schemas and data blocks • 16 - include sizes for data blocks • 32 - include heap dumps for data blocks • All levels include permanent space allocation section • Above levels apply in Oracle 10.2 • Dump levels vary in earlier versions
Parent Child 0 Child 1 Child 2 Child 3 SQL Statements • Each SQL statement has • Parent cursor • One or more child cursors
Parent Cursor • One parent cursor for each textually different SQL statement in library cache • Statements identified by hash value • Generated from statement text • Contains • KGLHD - Handle structure • KGLOB - Object structure • KGLNA - Name structure • Externalised by • V$SQLAREA • X$KGLOB (WHERE kglhdpar = kglhdadr)
KGLHD Parent Cursor KGLOB KGLNA SELECT c1,c2,c3,c4,c5,c6,c7,c8,c9,c10FROM t1,t2,t3,t4,t5WHERE t1.c1=t2.c1.. KGLNA KGLNA
V$SQLAREA • Contains one row for each parent cursor
Child Cursors • Each parent cursor can have one or more child cursors • Child cursor contains • Environment • Statistics • Execution Plan • Contains • KGLHD - Handle structure • KGLOB - Object structure • Subheaps • Externalised by • V$SQL • X$KGLOB (WHERE kglhdpar != kglhdadr) • X$KGLCURSOR_CHILD (in Oracle 10.2 and above)
KGLHD KGLHD KGLHD KGLHD KGLHD KGLHD Child Cursors KGLHD ParentCursor KGLOB KGLNA
KGLHD KGLOB Heap 0 Heap 6 Child Cursors ChildCursor Bind Variables ExecutionPlan Environment Statistics
V$SQL • Contains one row for each child cursor
Object Heaps • Every object can have optional sub heaps • Both parent and child cursors have sub heap 0 • In addition child cursors have sub heap 6 (execution plan)
Multiple Parent Cursors • Each parent requires at least one child cursor Parent Parent Parent Parent Child Child Child Child
Multiple Parent Cursors • Created because of differences in SQL statement text • The following statements all require separate parent cursors: SELECT COUNT(*) FROM t1; select count(*) from T1; SELECT /* COMMENT */ COUNT(*) FROM t1; SELECT COUNT(*) FROM t1; • Some Oracle tools perform limited formatting to standardize SQL statements e.g. SQL*Plus and PL/SQL
Cursor Sharing • Introduced in Oracle 8.1.6 • Replaces literals in SQL statements with variables • Increases probability that parent cursors are shared • Specified using CURSOR_SHARING parameter • Can be specified at SYSTEM or SESSION level • Can be • EXACT (default) • FORCE • SIMILAR (Oracle 9.0.1 and above)
Parent Parent 6BA7F7F8 6B8FB104 Child Child 6B9B6BE4 6BB158F0 Cursor Sharing - Exact • Exact cursor sharing (default) ALTER SESSION SET cursor_sharing = EXACT; SELECT c2 FROM t1 WHERE c1 = 0;SELECT c2 FROM t1 WHERE c1 = 1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT c2 FROM t1%';
Parent 6BA93574 Child 6B8D1A84 Cursor Sharing - Force • Forced cursor sharing ALTER SESSION SET cursor_sharing = FORCE; SELECT c2 FROM t1 WHERE c1 = 0;SELECT c2 FROM t1 WHERE c1 = 1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT c2 FROM t1%';
Cursor Sharing - Similar • Example CREATE TABLE t1 (c1 NUMBER,c2 VARCHAR2(10), c3 VARCHAR2(3),c4 VARCHAR2(3)); DECLARE v_c1 t1.c1%TYPE; v_c2 t1.c2%TYPE; v_c3 t1.c3%TYPE; v_c4 t1.c4%TYPE;BEGIN FOR v_key IN 1..10000 LOOP v_c1 := v_key; v_c2 := LPAD (TO_CHAR (v_key),10,'0'); IF v_key < 100 THEN v_c3 := TO_CHAR (TRUNC (v_key / 10) + 1); ELSE v_c3 := TO_CHAR (0); END IF; v_c4 := v_c3; INSERT INTO t1 VALUES (v_c1,v_c2,v_c3,v_c4); END LOOP; COMMIT;END;/
Cursor Sharing - Similar • Example -- Create a non-unique index on c3CREATE INDEX t1_i1 ON t1 (c3); -- Create a non-unique index on c4CREATE INDEX t1_i2 ON t1 (c4); -- Gather statistics BEGIN DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'US01', tabname => 'T1', estimate_percent => NULL, method_opt => 'FOR COLUMNS SIZE 4 c4' );END;/ ALTER SYSTEM FLUSH SHARED_POOL;
Parent 6BA812EC Child 6BB34F64 Cursor Sharing - Similar • Example - column without histogram ALTER SYSTEM SET cursor_sharing = SIMILAR; SELECT COUNT(*) FROM t1 WHERE c3 = '0';SELECT COUNT(*) FROM t1 WHERE c3 = '1';SELECT COUNT(*) FROM t1 WHERE c3 = '2';SELECT COUNT(*) FROM t1 WHERE c3 = '3'; SELECT sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1 WHERE c3 = %';
Parent Parent 6B8A5D54 67026E34 Child Child Child Child 6BB2D674 671B91E0 6702E9A4 6B95A4A8 Cursor Sharing - Similar • Example - column with histogram ALTER SYSTEM SET cursor_sharing = SIMILAR; SELECT COUNT(*) FROM t1 WHERE c4 = '0';SELECT COUNT(*) FROM t1 WHERE c4 = '1';SELECT COUNT(*) FROM t1 WHERE c4 = '2';SELECT COUNT(*) FROM t1 WHERE c4 = '3'; SELECT sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1 WHERE c4 = %';
Multiple Child Cursors • Can be created for a number of reasons including differences in: • System / Session parameters • Object translation • Bind variables • NLS parameters
V$SQL_SHARED_CURSOR • Describes each loaded child cursor • Contains set of boolean values describing why cursors could not be shared • Heap 0 must still exist for child cursor to be reported • Boolean values for first child of each parent will always be false • First child for parent may change over time as earlier children are aged out • Reasons for mismatches not always clear
V$SQL_SHARED_CURSOR • Based on X$KKSCS • To quickly ascertain why cursors are not being shared use: SELECT TO_CHAR (bitvector,'XXXXXXXXXXXXXXXX'), COUNT(*) FROM x$kkscsGROUP BY TO_CHAR (bitvector,'XXXXXXXXXXXXXXXX')ORDER BY 1; • To list the hexadecimal values for each Boolean column use: SELECT column_name, TO_CHAR (POWER (2,RANK () OVER (ORDER BY column_id) - 1),'XXXXXXXXXXXXXXXX')FROM dba_tab_columnsWHERE table_name = 'V_$SQL_SHARED_CURSOR'AND data_type = 'VARCHAR2'AND data_length = 1ORDER BY column_id;
Optimizer Mode • Different optimizer modes require separate child cursors • As USER1 set optimizer mode to CHOOSE (default) ALTER SESSION SET optimizer_mode = CHOOSE; SELECT COUNT(*) FROM t1; • Change optimizer mode to ALL_ROWS ALTER SESSION SET optimizer_mode = ALL_ROWS; SELECT COUNT(*) FROM t1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
Child Child 6B9B6BE4 6BB158F0 Optimizer Mode • As SYSDBA SELECT child_number, child_address, optimizer_mode_mismatchFROM v$sql_shared_cursorWHERE address = ' 6B97E3C0' Parent 6B97E3C0 optimizer_mode = CHOOSE optimizer_mode = ALL_ROWS
Optimizer Parameters • Optimizer parameters are specified in • V$SYS_OPTIMIZER_ENV - Instance level • V$SES_OPTIMIZER_ENV - Session level • V$SQL_OPTIMIZER_ENV - Statement level • In Oracle 10.2 there are 25 supported optimizer parameters
Supported Optimizer Parameters • For example, OPTIMIZER_INDEX_CACHING ALTER SESSION SET optimizer_index_caching = 0; SELECT COUNT(*) FROM t1; ALTER SESSION SET optimizer_index_caching = 20; SELECT COUNT(*) FROM t1; ALTER SESSION SET optimizer_index_caching = 40; SELECT COUNT(*) FROM t1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
Child Child Child 6B855EB4 6BB1DE24 6BA937EC Supported Optimizer Parameters • As SYSDBA SELECT child_number, child_address, optimizer_mismatchFROM v$sql_shared_cursorWHERE address = ' 6B97E3C0' Parent 6B97E3C0 optimizer_index_caching 0 20 40
Unsupported Optimizer Parameters • Optimizer environment parameter views are based on fixed table views • V$SYS_OPTIMIZER_ENVX$QKSCESYS • V$SES_OPTIMIZER_ENVX$QKSCESES • V$SQL_OPTIMIZER_ENVX$KQLFSQCE • In Oracle 10.2 there are 184 optimizer parameters • 25 supported parameters reported in both V$ and X$ views • 8 supported parameters only reported in X$ views • 151 unsupported parameters only reported in X$ views • To list unsupported optimizer parameters use: SELECT pname_qkscesyrow FROM x$qkscesysWHERE SUBSTR (pname_qkscesyrow,1,1) = '_'ORDER BY 1;
Unsupported Optimizer Parameters • For example, _UNNEST_SUBQUERY • In Oracle 10.2, default value is TRUE ALTER SESSION SET "_unnest_subquery" = TRUE; SELECT COUNT(*) FROM t1; ALTER SESSION SET "_unnest_subquery" = FALSE; SELECT COUNT(*) FROM t1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
Child Child 6B879828 6B976F20 Unsupported Optimizer Parameters • As SYSDBA SELECT child_number, child_address, optimizer_mismatchFROM v$sql_shared_cursorWHERE address = ' 6B97E3C0' Parent 6B97E3C0 _unnest_subquery TRUE FALSE
Trace • Enabling trace results in multiple child cursors • For example SELECT COUNT(*) FROM t1; EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE; SELECT COUNT(*) FROM t1; EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE; SELECT COUNT(*) FROM t1; SELECT address, child_address, sql_text FROM v$sqlWHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
Child Child 6B99348C 6B895F5C Trace SELECT child_number, child_address, stats_row_mismatchFROM v$sql_shared_cursorWHERE address = ' 6B97E27C' • One additional child cursor is created for each parent when trace is enabled. • Can be enabled using event 10046, levels 1,4,8,12 sql_trace parameter or DBMS_MONITOR package Parent 6B97E3C0 Trace Enabled FALSE TRUE
Translations • If a statement references different objects with the same name then multiple child cursors can be generated • For example: USER1 USER2 CREATE TABLE t1 (c1 NUMBER); SELECT c1 FROM t1; CREATE TABLE t1 (c1 NUMBER); SELECT c1 FROM t1; • The statement SELECT c1 FROM t1 will have a shared parent cursor, but multiple child cursors ParentCursor SELECT c1 FROM t1; ChildCursor 1 ChildCursor 2 USER1.T1 USER2.T1
Translations • As USER1 CREATE TABLE t1 (c1 NUMBER);SELECT c1 FROM t1; • As USER2 CREATE TABLE t1 (c1 NUMBER);SELECT c1 FROM t1; • As SYSDBA SELECT address,hash_value,child_number,child_addressFROM v$sql WHERE sql_text LIKE 'SELECT c1 FROM t1%';
Child Child 6B91CA58 6BA1DB48 Translations SELECT child_number, child_address, auth_check_mismatch, translation_mismatchFROM v$sql_shared_cursorWHERE address = ' 6B8E5AEC' Parent 6B9B7F74 USER1 USER2
Translations • Determining translated objects SELECT kgltrorg, kgltrfnlFROM x$kgltrWHERE kglhdadr = '6B8B6C30'; SELECT kgltrorg, kgltrfnlFROM x$kgltrWHERE kglhdadr = '6B8DA100'; SELECT kglnaown kglnaobjFROM x$kglobWHERE kglhdadr = '6BA68DAC'; SELECT kglnaown kglnaobjFROM x$kglobWHERE kglhdadr = '6B8F9220';
Bind Variables • Length of bind variables affects number of child cursors • For example (in SQL*Plus): CREATE TABLE t1 (c1 VARCHAR2(50),c2 NUMBER); VARIABLE v1 VARCHAR2(30);SELECT c2 FROM t1 WHERE c1 = :v1; VARIABLE v1 VARCHAR2(40);SELECT c2 FROM t1 WHERE c1 = :v1; SELECT address,hash_value,child_number,child_addressFROM v$sql WHERE sql_text LIKE 'SELECT c2 FROM t1 WHERE c1 = %';
Child Child 6B91CA58 6BA1DB48 Bind Variables SELECT child_number, child_address, bind_mismatch FROM v$sql_shared_cursorWHERE address = ' 6B9B7F74' Parent 6B9B7F74 VARCHAR2(30) VARCHAR2(40)
V$SQL_BIND_METADATA • Describes bind variables for each cursor in shared pool • Based on X$KKSBV • Note, in this case ADDRESS is the address of the child cursor
Bind Variables • To check length of bind variables SELECT address, position, data_type, max_length, bind_nameFROM v$sql_bind_metadataWHERE address IN( SELECT child_address FROM v$sql WHERE address = '6B9B7F74');
Bind Variables • Multiple child cursors can be caused by bind variable lengths • By default: • NUMBER is 22 bytes • VARCHAR2 is rounded to next highest length which can be • 32 • 128 • 2000 • 4000 • Rounding of VARCHAR2 columns can be overridden by • enabling event 10503 • setting level to minimum bind variable length e.g. 128 ALTER SESSION SET EVENTS '10503 TRACE NAME CONTEXT FOREVER, LEVEL 128';