1 / 66

Library Cache Internals

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

emil
Download Presentation

Library Cache Internals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Library CacheInternals Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Introduction Multiple Parent Cursors Multiple Child Cursors DBMS_SHARED_POOL Summary Agenda

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. V$DB_OBJECT_CACHE • Reports all objects currently in the library cache • Based on X$KGLOB

  9. 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

  10. Parent Child 0 Child 1 Child 2 Child 3 SQL Statements • Each SQL statement has • Parent cursor • One or more child cursors

  11. 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)

  12. 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

  13. V$SQLAREA • Contains one row for each parent cursor

  14. 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)

  15. KGLHD KGLHD KGLHD KGLHD KGLHD KGLHD Child Cursors KGLHD ParentCursor KGLOB KGLNA

  16. KGLHD KGLOB Heap 0 Heap 6 Child Cursors ChildCursor Bind Variables ExecutionPlan Environment Statistics

  17. V$SQL • Contains one row for each child cursor

  18. 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)

  19. Multiple Parent Cursors • Each parent requires at least one child cursor Parent Parent Parent Parent Child Child Child Child

  20. 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

  21. 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)

  22. 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%';

  23. 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%';

  24. 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;/

  25. 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;

  26. 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 = %';

  27. 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 = %';

  28. Multiple Child Cursors • Can be created for a number of reasons including differences in: • System / Session parameters • Object translation • Bind variables • NLS parameters

  29. V$SQL_SHARED_CURSOR

  30. 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

  31. 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;

  32. 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%';

  33. 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

  34. 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

  35. 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%';

  36. 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

  37. 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;

  38. 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%';

  39. 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

  40. 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%';

  41. 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

  42. 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

  43. 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%';

  44. 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

  45. 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';

  46. 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 = %';

  47. 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)

  48. 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

  49. 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');

  50. 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';

More Related