Library Cache Internals Julian Dyke Independent Consultant Web

Agenda u u u 2 © 2006 Julian Dyke Introduction Multiple Parent Cursors Multiple

Shared Pool u 3 Shared pool includes: u Permanent - Static structures - allocated at startup u Session arrays u Process arrays u Segmented arrays u Other static structures u Heap - Dynamic structures - allocated at runtime u Library Cache u Dictionary (Row) Cache © 2006 Julian Dyke juliandyke. com

Shared Pool Size u u Determines amount of memory allocated to library cache In Oracle 10. 2 and above specified as follows -- If Automatic Memory Management IF SGA_TARGET specified THEN IF SHARED_POOL_SIZE specified THEN minimum size of shared pool = SHARED_POOL_SIZE ELSE size of shared pool determined automatically ELSE -- Manual memory management IF SHARED_POOL_SIZE specified THEN size of shared pool = SHARED_POOL_SIZE ELSE IF 32 -bit platform size = 32 M IF 64 -bit platform size = 84 M 4 © 2006 Julian Dyke juliandyke. com

Library Cache u 5 Contains objects of various types required to parse and execute SQL statements including u tables u indexes u cursors u parent u child u PL/SQL u procedures u functions u packages u Types u methods u Java classes © 2006 Julian Dyke juliandyke. com

Object Types u Every object in the library cache has an object type. Object types include: u. Cursor u. Type u. Index-Organized u. Table u. Type u. Java u. Index u. Cluster u. View u. Synonym u. Sequence u. Procedure u. Function u. Package Body u. Trigger 6 © 2006 Julian Dyke Body u. Object u. User u. Database Link u. Pipe u. Table Partition u. Index Partition u. LOB u. Library u. Directory u. Queue Table Source u. Java Class u. Java Resource u. Java JAR u. Table Subpartition u. Index Subpartition u. LOB Partition u. LOB Subpartition u. Summary u. Dimension u. Stored Outline juliandyke. com

Namespaces u Every object in the library cache belongs to a namespace Namespaces include: u. Cursor u. Table/Procedure u. Body u. Trigger u. Index u. Cluster u. Object u. Java Source u. Java Resource u. Context Policy u. Publish/Subscribe u. Dimension 7 © 2006 Julian Dyke u. Application Context u. Stored Outline u. Ruleset u. Resource Plan u. Resource Consumer Group u. Subscription u. Location u. Remote Object u. Snapshot Metadata u. Java Shared Data u. Security Profile juliandyke. com

V$DB_OBJECT_CACHE u u 8 Reports all objects currently in the library cache OWNER VARCHAR 2(64) NAME VARCHAR 2(1000) DB_LINK VARCHAR 2(64) NAMESPACE VARCHAR 2(28) TYPE VARCHAR 2(28) SHARABLE_MEM NUMBER LOADS NUMBER EXECUTIONS NUMBER LOCKS NUMBER PINS NUMBER KEPT VARCHAR 2(3) CHILD_LATCH NUMBER INVALIDATIONS NUMBER Based on X$KGLOB © 2006 Julian Dyke juliandyke. com

Library Cache Dumps u To dump the contents of the library cache use ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL n'; u u u 9 where <n> specifies information to be included u 1 - include library cache statistics u 2 - include library cache hash table u 4 - include library cache handles and objects u 8 - include dependencies, read-only dependencies, accesses, authorizations, translations, schemas and data blocks u 16 - include sizes for data blocks u 32 - include heap dumps for data blocks All levels include permanent space allocation section Above levels apply in Oracle 10. 2 u Dump levels vary in earlier versions © 2006 Julian Dyke juliandyke. com

SQL Statements u Each SQL statement has u Parent cursor u One or more

Parent Cursor 11 u One parent cursor for each textually different SQL statement in library cache u Statements identified by hash value u Generated from statement text u Contains u KGLHD - Handle structure u KGLOB - Object structure u KGLNA - Name structure u Externalised by u V$SQLAREA u X$KGLOB (WHERE kglhdpar = kglhdadr) © 2006 Julian Dyke juliandyke. com

Parent Cursor KGLHD KGLOB KGLNA 12 © 2006 Julian Dyke SELECT c 1, c

V$SQLAREA u 13 Contains one row for each parent cursor SQL_TEXT VARCHAR 2(1000) BUFFER_GETS NUMBER MODULE_HASH NUMBER SQL_FULLTEXT CLOB APPLICATION_WAIT_TIME NUMBER ACTION VARCHAR 2(64) SQL_ID VARCHAR 2(13) CONCURRENCY_WAIT_TIME NUMBER ACTION_HASH NUMBER SHARABLE_MEM NUMBER CLUSTER_WAIT_TIME NUMBER SERIALIZABLE_ABOUTS NUMBER PERSISTENT_MEM NUMBER USER_IO_WAIT_TIME NUMBER OUTLINE_CATEGORY VARCHAR 2(64) RUNTIME_MEM NUMBER PLSQL_EXEC_TIME NUMBER CPU_TIME NUMBER SORTS NUMBER JAVA_EXEC_TIME NUMBER ELAPSED_TIME NUMBER VERSION_COUNT NUMBER ROWS_PROCESSED NUMBER OUTLINE_SID VARCHAR 2(40) LOADED_VERSIONS NUMBER COMMAND_TYPE NUMBER LAST_ACTIVE_CHILD_ADDRESS RAW(4) OPEN_VERSIONS NUMBER OPTIMIZER_MODE VARCHAR 2(10) REMOTE VARCHAR 2(1) USERS_OPENING NUMBER OPTIMIZER_COST NUMBER OBJECT_STATUS VARCHAR 2(19) FETCHES NUMBER OPTIMIZER_ENV RAW(797) LITERAL_HASH_VALUE NUMBER EXECUTIONS NUMBER OPTIMIZER_ENV_HASH_VALUE NUMBER LAST_LOAD_TIME DATE PX_SERVERS_EXECUTIONS NUMBER PARSING_USER_ID NUMBER IS_OBSOLETE VARCHAR 2(1) END_OF_FETCH_COUNT NUMBER PARSING_SCHEMA_ID NUMBER CHILD_LATCH NUMBER USERS_EXECUTING NUMBER PARSING_SCHEMA_NAME VARCHAR 2(64) SQL_PROFILE VARCHAR 2(64) LOADS NUMBER KEPT_VERSIONS NUMBER PROGRAM_ID NUMBER FIRST_LOAD_TIME VARCHAR 2(19) ADDRESS RAW(4) PROGRAM_LINE# NUMBER INVALIDATIONS NUMBER HASH_VALUE NUMBER EXACT_MATCHING_SIGNATURE NUMBER PARSE_CALLS NUMBER OLD_HASH_VALUE NUMBER FORCE_MATCHING_SIGNATURE NUMBER DISK_READS NUMBER PLAN_HASH_VALUE NUMBER LAST_ACTIVE_TIME DATE DIRECT_WRITES NUMBER MODULE VARCHAR 2(64) BIND_DATA RAW(2000) © 2006 Julian Dyke juliandyke. com

Child Cursors 14 u Each parent cursor can have one or more child cursors u Child cursor contains u Environment u Statistics u Execution Plan u Contains u KGLHD - Handle structure u KGLOB - Object structure u Subheaps u Externalised by u V$SQL u X$KGLOB (WHERE kglhdpar != kglhdadr) u X$KGLCURSOR_CHILD (in Oracle 10. 2 and above) © 2006 Julian Dyke juliandyke. com

Child Cursors KGLHD KGLOB KGLHD 15 KGLHD © 2006 Julian Dyke KGLHD KGLNA KGLHD

Child Cursors KGLHD Child Cursor KGLOB Heap 0 Environment 16 © 2006 Julian Dyke

V$SQL u Contains one row for each child cursor SQL_TEXT 17 VARCHAR 2(1000) CLUSTER_WAIT_TIME NUMBER MODULE_HASH NUMBER VARCHAR 2(64) SQL_FULLTEXT CLOB USER_IO_WAIT_TIME NUMBER ACTION SQL_ID VARCHAR 2(13) PLSQL_EXEC_TIME NUMBER ACTION_HASH NUMBER SHARABLE_MEM NUMBER JAVA_EXEC_TIME NUMBER SERIALIZABLE_ABORTS NUMBER PERSISTENT_MEM NUMBER ROWS_PROCESSED NUMBER OUTLINE_CATEGORY VARCHAR 2(64) RUNTIME_MEM NUMBER COMMAND_TYPE NUMBER CPU_TIME NUMBER SORTS NUMBER OPTIMIZER_MODE VARCHAR 2(10) ELAPSED_TIME NUMBER LOADED_VERSIONS NUMBER OPTIMZER_COST NUMBER OUTLINE_SID NUMBER OPEN_VERSIONS NUMBER OPTIMZER_ENV RAW(797) CHILD_ADDRESS RAW(4) USERS_OPENING NUMBER OPTIMZER_ENV_HASH_VALUE NUMBER SQLTYPE NUMBER FETCHES NUMBER PARSING_USER_ID NUMBER REMOTE VARCHAR 2(1) EXECUTIONS NUMBER PARSING_SCHEMA_ID NUMBER OBJECT_STATUS VARCHAR 2(19) PX_SERVERS_EXECUTIONS NUMBER PARSING_SCHEMA_NAME VARCHAR 2(30) LITERAL_HASH_VALUE NUMBER END_OF_FETCH_COUNT NUMBER KEPT_VERSIONS NUMBER LAST_LOAD_TIME VARCHAR 2(19) USERS_EXECUTING NUMBER ADDRESS RAW(4) IS_OBSOLETE VARCHAR 2(1) LOADS NUMBER TYPE_CHK_HEAP RAW(4) CHILD_LATCH NUMBER FIRST_LOAD_TIME VARCHAR 2(19) HASH_VALUE NUMBER SQL_PROFILE VARCHAR 2(64) INVALIDATIONS NUMBER OLD_HASH_VALUE NUMBER PROGRAM_ID NUMBER PARSE_CALLS NUMBER PLAN_HASH_VALUE NUMBER PROGRAM_LINE# NUMBER DISK_READS NUMBER CHILD_NUMBER EXACT_MATCHING_SIGNATURE NUMBER DIRECT_WRITES NUMBER SERVICE VARCHAR 2(64) FORCE_MATCHING_SIGNATURE NUMBER BUFFER_GETS NUMBER SERVICE_HASH NUMBER LAST_ACTIVE_TIME DATE APPLICATION_WAIT_TIME NUMBER MODULE VARCHAR 2(64) BIND_DATA RAW(2000) CONCURRENCY_WAIT_TIME NUMBER © 2006 Julian Dyke juliandyke. com

Object Heaps u u u 18 Every object can have optional sub heaps Heap # 0 1 2 3 4 Description Object Source DIANA PCODE MCODE 5 Errors 6 SQL Context 7 Free 8 Subordinate Heap 9 Subordinate Heap 10 Subordinate Heap 11 Subordinate Heap Both parent and child cursors have sub heap 0 In addition child cursors have sub heap 6 (execution plan) © 2006 Julian Dyke juliandyke. com

Multiple Parent Cursors u 19 Each parent requires at least one child cursor Parent

Multiple Parent Cursors u u Created because of differences in SQL statement text The following statements all require separate parent cursors: SELECT COUNT(*) FROM t 1; select count(*) from T 1; SELECT /* COMMENT */ COUNT(*) FROM t 1; SELECT COUNT(*) FROM t 1; u 20 Some Oracle tools perform limited formatting to standardize SQL statements e. g. SQL*Plus and PL/SQL © 2006 Julian Dyke juliandyke. com

Cursor Sharing u u u 21 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 u EXACT (default) u FORCE u SIMILAR (Oracle 9. 0. 1 and above) © 2006 Julian Dyke juliandyke. com

Cursor Sharing - Exact u Exact cursor sharing (default) ALTER SESSION SET cursor_sharing = EXACT; SELECT c 2 FROM t 1 WHERE c 1 = 0; SELECT c 2 FROM t 1 WHERE c 1 = 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT c 2 FROM t 1%'; 22 ADDRESS CHILD_ADDRESS 6 BA 7 F 7 F 8 6 B 9 B 6 BE 4 SELECT c 2 FROM t 1 WHERE c 1 = 0 6 B 8 FB 104 6 BB 158 F 0 SELECT c 2 FROM t 1 WHERE c 1 = 1 © 2006 Julian Dyke STATEMENT Parent 6 BA 7 F 7 F 8 6 B 8 FB 104 Child 6 B 9 B 6 BE 4 6 BB 158 F 0 juliandyke. com

Cursor Sharing - Force u Forced cursor sharing ALTER SESSION SET cursor_sharing = FORCE; SELECT c 2 FROM t 1 WHERE c 1 = 0; SELECT c 2 FROM t 1 WHERE c 1 = 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT c 2 FROM t 1%'; ADDRESS CHILD_ADDRESS STATEMENT 6 BA 93574 6 B 8 D 1 A 84 SELECT c 2 FROM t 1 WHERE c 1 = : "SYS_B_0" Parent 6 BA 93574 Child 6 B 8 D 1 A 84 23 © 2006 Julian Dyke juliandyke. com

Cursor Sharing - Similar u Example CREATE TABLE t 1 (c 1 NUMBER, c 2 VARCHAR 2(10), c 3 VARCHAR 2(3), c 4 VARCHAR 2(3)); DECLARE v_c 1 t 1. c 1%TYPE; v_c 2 t 1. c 2%TYPE; v_c 3 t 1. c 3%TYPE; v_c 4 t 1. c 4%TYPE; BEGIN FOR v_key IN 1. . 10000 LOOP v_c 1 : = v_key; v_c 2 : = LPAD (TO_CHAR (v_key), 10, '0'); IF v_key < 100 THEN v_c 3 : = TO_CHAR (TRUNC (v_key / 10) + 1); ELSE v_c 3 : = TO_CHAR (0); END IF; v_c 4 : = v_c 3; INSERT INTO t 1 VALUES (v_c 1, v_c 2, v_c 3, v_c 4); END LOOP; COMMIT; END; / 24 © 2006 Julian Dyke juliandyke. com

Cursor Sharing - Similar u Example -- Create a non-unique index on c 3 CREATE INDEX t 1_i 1 ON t 1 (c 3); -- Create a non-unique index on c 4 CREATE INDEX t 1_i 2 ON t 1 (c 4); -- Gather statistics BEGIN DBMS_STATS. GATHER_TABLE_STATS ( ownname => 'US 01', tabname => 'T 1', estimate_percent => NULL, method_opt => 'FOR COLUMNS SIZE 4 c 4' ); END; / ALTER SYSTEM FLUSH SHARED_POOL; 25 © 2006 Julian Dyke juliandyke. com

Cursor Sharing - Similar u Example - column without histogram ALTER SYSTEM SET cursor_sharing = SIMILAR; SELECT COUNT(*) FROM t 1 WHERE c 3 = '0'; SELECT COUNT(*) FROM t 1 WHERE c 3 = '1'; SELECT COUNT(*) FROM t 1 WHERE c 3 = '2'; SELECT COUNT(*) FROM t 1 WHERE c 3 = '3'; SELECT sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1 WHERE c 3 = %'; ADDRESS CHILD_ADDRESS 6 BA 812 EC 6 BB 34 F 64 STATEMENT SELECT COUNT(*) FROM t 1 WHERE c 3 = : "SYS_B_0" Parent 6 BA 812 EC Child 6 BB 34 F 64 26 © 2006 Julian Dyke juliandyke. com

Cursor Sharing - Similar u Example - column with histogram ALTER SYSTEM SET cursor_sharing = SIMILAR; SELECT COUNT(*) FROM t 1 WHERE c 4 = '0'; SELECT COUNT(*) FROM t 1 WHERE c 4 = '1'; SELECT COUNT(*) FROM t 1 WHERE c 4 = '2'; SELECT COUNT(*) FROM t 1 WHERE c 4 = '3'; SELECT sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1 WHERE c 4 = %'; 27 ADDRESS CHILD_ADDRESS STATEMENT 6 B 8 A 5 D 54 6 BB 2 D 674 SELECT COUNT(*) FROM t 1 WHERE c 4 = '0' 67026 E 34 6702 E 9 A 4 SELECT COUNT(*) FROM t 1 WHERE c 4 = : "SYS_B_0" 67026 E 34 671 B 91 E 0 SELECT COUNT(*) FROM t 1 WHERE c 4 = : "SYS_B_0" 67026 E 34 6 B 95 A 4 A 8 SELECT COUNT(*) FROM t 1 WHERE c 4 = : "SYS_B_0" Parent 6 B 8 A 5 D 54 67026 E 34 Child 6 BB 2 D 674 6702 E 9 A 4 671 B 91 E 0 6 B 95 A 4 A 8 © 2006 Julian Dyke juliandyke. com

Multiple Child Cursors u 28 Can be created for a number of reasons including differences in: u System / Session parameters u Object translation u Bind variables u NLS parameters © 2006 Julian Dyke juliandyke. com

V$SQL_SHARED_CURSOR SQL_ID VARCHAR 2(13) DESCRIBE_MISMATCH VARCHAR 2(1) DIFFERENT_LONG_LENGTH VARCHAR 2(1) ADDRESS RAW(4) LANGUAGE_MISMATCH VARCHAR 2(1) LOGICAL_STANDBY_APPLY VARCHAR 2(1) CHILD_ADDRESS RAW(4) TRANSLATION_MISMATCH VARCHAR 2(1) DIFF_CALL_DURN VARCHAR 2(1) CHILD_NUMBER ROW_LEVEL_SEC_MISMATCH VARCHAR 2(1) BIND_UACS_DIFF VARCHAR 2(1) UNBOUND_CURSOR VARCHAR 2(1) INSUFF_PRIVS VARCHAR 2(1) PLSQL_CMP_SWITCHS_DIFF VARCHAR 2(1) SQL_TYPE_MISMATCH VARCHAR 2(1) INSUFF_PRIVS_REM VARCHAR 2(1) CURSOR_PARTS_MISMATCH VARCHAR 2(1) OPTIMIZER_MISMATCH VARCHAR 2(1) REMOTE_TRANS_MISMATCH VARCHAR 2(1) STB_OBJECT_MISMATCH VARCHAR 2(1) OUTLINE_MISMATCH VARCHAR 2(1) LOGMINER_SESSION_MISMATCH VARCHAR 2(1) ROW_SHIP_MISMATCH VARCHAR 2(1) STATS_ROW_MISMATCH VARCHAR 2(1) INCOMP_LTRL_MISMATCH VARCHAR 2(1) PQ_SLAVE_MISMATCH VARCHAR 2(1) LITERAL_MISMATCH VARCHAR 2(1) OVERLAP_TIME_MISMATCH VARCHAR 2(1) TOP_LEVEL_DDL_MISMATCH VARCHAR 2(1) SEC_DEPTH_MISMATCH VARCHAR 2(1) SQL_REDIRECT_MISMATCH VARCHAR 2(1) MULTI_PX_MISMATCH VARCHAR 2(1) EXPLAIN_PLAN_MISMATCH VARCHAR 2(1) MV_QUERY_GEN_MISMATCH VARCHAR 2(1) BIND_PEEKED_PQ_MISMATCH VARCHAR 2(1) BUFFERED_DML_MISMATCH VARCHAR 2(1) USER_BIND_PEEK_MISMATCH VARCHAR 2(1) MV_REWRITE_MISMATCH VARCHAR 2(1) PDML_ENV_MISMATCH VARCHAR 2(1) TYPCHK_DEP_MISMATCH VARCHAR 2(1) ROLL_INVALID_MISMATCH VARCHAR 2(1) INST_DRTLD_MISMATCH VARCHAR 2(1) NO_TRIGGER_MISMATCH VARCHAR 2(1) OPTIMIZER_MODE_MISMATCH VARCHAR 2(1) SLAVE_QC_MISMATCH VARCHAR 2(1) FLASHBACK_CURSOR VARCHAR 2(1) PX_MISMATCH VARCHAR 2(1) TYPECHECK_MISMATCH VARCHAR 2(1) ANYDATA_TRANSFORMATION VARCHAR 2(1) MV_STALEOBJ_MISMATCH VARCHAR 2(1) AUTH_CHECK_MISMATCH VARCHAR 2(1) INCOMPLETE_CURSOR VARCHAR 2(1) FLASHBACK_TABLE_MISMATCH VARCHAR 2(1) BIND_MISMATCH VARCHAR 2(1) TOP_LEVEL_RPI_CURSOR VARCHAR 2(1) LITREP_COMP_MISMATCH VARCHAR 2(1) 29 © 2006 Julian Dyke juliandyke. com

V$SQL_SHARED_CURSOR 30 u Describes each loaded child cursor u Contains set of boolean values describing why cursors could not be shared u Heap 0 must still exist for child cursor to be reported u Boolean values for first child of each parent will always be false u First child for parent may change over time as earlier children are aged out u Reasons for mismatches not always clear © 2006 Julian Dyke juliandyke. com

V$SQL_SHARED_CURSOR u Based on X$KKSCS u To quickly ascertain why cursors are not being shared use: SELECT TO_CHAR (bitvector, 'XXXXXXXX'), COUNT(*) FROM x$kkscs GROUP BY TO_CHAR (bitvector, 'XXXXXXXX') ORDER BY 1; u To list the hexadecimal values for each Boolean column use: SELECT column_name, TO_CHAR (POWER (2, RANK () OVER (ORDER BY column_id) - 1), 'XXXXXXXX') FROM dba_tab_columns WHERE table_name = 'V_$SQL_SHARED_CURSOR' AND data_type = 'VARCHAR 2' AND data_length = 1 ORDER BY column_id; 31 © 2006 Julian Dyke juliandyke. com

Optimizer Mode u u Different optimizer modes require separate child cursors As USER 1 set optimizer mode to CHOOSE (default) ALTER SESSION SET optimizer_mode = CHOOSE; SELECT COUNT(*) FROM t 1; u Change optimizer mode to ALL_ROWS ALTER SESSION SET optimizer_mode = ALL_ROWS; SELECT COUNT(*) FROM t 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1%'; 32 ADDRESS CHILD_ADDRESS 6 B 97 E 3 C 0 6 BA 0 FC 18 SELECT COUNT(*) FROM t 1; 6 B 97 E 3 C 0 6 BAE 9904 SELECT COUNT(*) FROM t 1; © 2006 Julian Dyke STATEMENT juliandyke. com

Optimizer Mode u As SYSDBA SELECT child_number, child_address, optimizer_mode_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 97 E 3 C 0' CHILD_NUMBER CHILD_ADDRESS 0 1 6 BA 0 FC 18 6 BAE 9904 N Y OPTIMIZER_MODE_MISMATCH Parent 6 B 97 E 3 C 0 optimizer_mode = CHOOSE 33 © 2006 Julian Dyke Child 6 B 9 B 6 BE 4 6 BB 158 F 0 optimizer_mode = ALL_ROWS juliandyke. com

Optimizer Parameters u u Optimizer parameters are specified in u V$SYS_OPTIMIZER_ENV - Instance level u V$SES_OPTIMIZER_ENV - Session level u V$SQL_OPTIMIZER_ENV - Statement level In Oracle 10. 2 there are 25 supported optimizer parameters active_instance_count bitmap_merge_area_size cpu_count cursor_sharing hash_area_size optimizer_dynamic_sampling optimizer_features_enable optimizer_index_caching optimizer_index_cost_adj optimizer_mode optimizer_secure_view_merging parallel_ddl_mode parallel_dml_mode 34 © 2006 Julian Dyke parallel_execution_enabled parallel_query_mode parallel_threads_per_cpu pga_aggregate_target query_rewrite_enabled query_rewrite_integrity skip_unusable_indexes sort_area_retained_size sort_area_size star_transformation_enabled statistics_level workarea_size_policy juliandyke. com

Supported Optimizer Parameters u For example, OPTIMIZER_INDEX_CACHING ALTER SESSION SET optimizer_index_caching = 0; SELECT COUNT(*) FROM t 1; ALTER SESSION SET optimizer_index_caching = 20; SELECT COUNT(*) FROM t 1; ALTER SESSION SET optimizer_index_caching = 40; SELECT COUNT(*) FROM t 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1%'; 35 ADDRESS CHILD_ADDRESS 6 B 97 E 3 C 0 6 BA 937 EC SELECT COUNT(*) FROM t 1; 6 B 97 E 3 C 0 6 B 855 EB 4 SELECT COUNT(*) FROM t 1; 6 B 97 E 3 C 0 6 BB 1 DE 24 SELECT COUNT(*) FROM t 1; © 2006 Julian Dyke STATEMENT juliandyke. com

Supported Optimizer Parameters u As SYSDBA SELECT child_number, child_address, optimizer_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 97 E 3 C 0' CHILD_NUMBER CHILD_ADDRESS 0 1 2 6 BA 937 EC 6 B 855 EB 4 6 BB 1 DE 24 N Y Y OPTIMIZER_MISMATCH Parent 6 B 97 E 3 C 0 optimizer_index_caching 36 © 2006 Julian Dyke Child 6 BA 937 EC 6 B 855 EB 4 6 BB 1 DE 24 0 20 40 juliandyke. com

Unsupported Optimizer Parameters u Optimizer environment parameter views are based on fixed table views u V$SYS_OPTIMIZER_ENV X$QKSCESYS u V$SES_OPTIMIZER_ENV X$QKSCESES u V$SQL_OPTIMIZER_ENV X$KQLFSQCE u In Oracle 10. 2 there are 184 optimizer parameters u 25 supported parameters reported in both V$ and X$ views u 8 supported parameters only reported in X$ views u 151 unsupported parameters only reported in X$ views u To list unsupported optimizer parameters use: SELECT pname_qkscesyrow FROM x$qkscesys WHERE SUBSTR (pname_qkscesyrow, 1, 1) = '_' ORDER BY 1; 37 © 2006 Julian Dyke juliandyke. com

Unsupported Optimizer Parameters u u For example, _UNNEST_SUBQUERY In Oracle 10. 2, default value is TRUE ALTER SESSION SET "_unnest_subquery" = TRUE; SELECT COUNT(*) FROM t 1; ALTER SESSION SET "_unnest_subquery" = FALSE; SELECT COUNT(*) FROM t 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1%'; 38 ADDRESS CHILD_ADDRESS 6 B 97 E 3 C 0 6 B 879828 SELECT COUNT(*) FROM t 1; 6 B 97 E 3 C 0 6 B 976 F 20 SELECT COUNT(*) FROM t 1; © 2006 Julian Dyke STATEMENT juliandyke. com

Unsupported Optimizer Parameters u As SYSDBA SELECT child_number, child_address, optimizer_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 97 E 3 C 0' CHILD_NUMBER CHILD_ADDRESS 0 1 6 B 879828 6 B 976 F 20 N Y OPTIMIZER_MISMATCH Parent 6 B 97 E 3 C 0 _unnest_subquery 39 © 2006 Julian Dyke Child 6 B 879828 6 B 976 F 20 TRUE FALSE juliandyke. com

Trace u u Enabling trace results in multiple child cursors For example SELECT COUNT(*) FROM t 1; EXECUTE DBMS_MONITOR. SESSION_TRACE_ENABLE; SELECT COUNT(*) FROM t 1; EXECUTE DBMS_MONITOR. SESSION_TRACE_DISABLE; SELECT COUNT(*) FROM t 1; SELECT address, child_address, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1%'; 40 ADDRESS CHILD_ADDRESS 6 B 97 E 27 C 6 B 99348 C SELECT COUNT(*) FROM t 1; 6 B 97 E 27 C 6 B 895 F 5 C SELECT COUNT(*) FROM t 1; © 2006 Julian Dyke STATEMENT juliandyke. com

Trace SELECT child_number, child_address, stats_row_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 97 E 27 C' CHILD_NUMBER CHILD_ADDRESS 0 1 6 B 99348 C 6 B 895 F 5 C N Y STATS_ROW_MISMATCH u u 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 Trace Enabled 41 © 2006 Julian Dyke Parent 6 B 97 E 3 C 0 Child 6 B 99348 C 6 B 895 F 5 C FALSE TRUE juliandyke. com

Translations u u If a statement references different objects with the same name then multiple child cursors can be generated For example: USER 1 u USER 2 CREATE TABLE t 1 (c 1 NUMBER); SELECT c 1 FROM t 1; The statement SELECT c 1 FROM t 1 will have a shared parent cursor, but multiple child cursors Parent Cursor 42 © 2006 Julian Dyke SELECT c 1 FROM t 1; Child Cursor 1 Child Cursor 2 USER 1. T 1 USER 2. T 1 juliandyke. com

Translations u As USER 1 CREATE TABLE t 1 (c 1 NUMBER); SELECT c 1 FROM t 1; u As USER 2 CREATE TABLE t 1 (c 1 NUMBER); SELECT c 1 FROM t 1; u As SYSDBA SELECT address, hash_value, child_number, child_address FROM v$sql WHERE sql_text LIKE 'SELECT c 1 FROM t 1%'; 43 ADDRESS HASH_VALUE CHILD_NUMBER CHILD_ADDRESS 6 B 8 E 5 AEC 3805054639 0 6 B 8 B 6 C 30 6 B 8 E 5 AEC 3805054639 1 6 B 8 DA 100 © 2006 Julian Dyke juliandyke. com

Translations SELECT child_number, child_address, auth_check_mismatch, translation_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 8 E 5 AEC' CHILD_NUMBER 0 1 6 B 8 B 6 C 30 6 B 8 DA 100 AUTH_CHECK_MISMATCH N Y TRANSLATION_MISMATCH N Y CHILD_ADDRESS Parent 6 B 9 B 7 F 74 44 © 2006 Julian Dyke Child 6 B 91 CA 58 6 BA 1 DB 48 USER 1 USER 2 juliandyke. com

Translations u Determining translated objects SELECT kgltrorg, kgltrfnl FROM x$kgltr WHERE kglhdadr = '6 B 8 B 6 C 30'; KGLTRORG KGLTRFNL 6 BA 68 DAC 6 B 8 F 9220 SELECT kglnaown kglnaobj FROM x$kglob WHERE kglhdadr = '6 BA 68 DAC'; 45 SELECT kgltrorg, kgltrfnl FROM x$kgltr WHERE kglhdadr = '6 B 8 DA 100'; SELECT kglnaown kglnaobj FROM x$kglob WHERE kglhdadr = '6 B 8 F 9220'; KGLNAOWN KGLNAOBJ USER 1 T 1 USER 2 T 1 © 2006 Julian Dyke juliandyke. com

Bind Variables u u Length of bind variables affects number of child cursors For example (in SQL*Plus): CREATE TABLE t 1 (c 1 VARCHAR 2(50), c 2 NUMBER); VARIABLE v 1 VARCHAR 2(30); SELECT c 2 FROM t 1 WHERE c 1 = : v 1; VARIABLE v 1 VARCHAR 2(40); SELECT c 2 FROM t 1 WHERE c 1 = : v 1; SELECT address, hash_value, child_number, child_address FROM v$sql WHERE sql_text LIKE 'SELECT c 2 FROM t 1 WHERE c 1 = %'; 46 ADDRESS HASH_VALUE CHILD_NUMBER CHILD_ADDRESS 6 B 9 B 6 F 74 357538776 0 6 B 91 CA 58 6 B 9 B 6 F 74 357538776 1 6 BA 1 DB 48 © 2006 Julian Dyke juliandyke. com

Bind Variables SELECT child_number, child_address, bind_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 9 B 7 F 74' CHILD_NUMBER 0 CHILD_ADDRESS 1 6 B 91 CA 58 6 BA 1 DB 48 BIND_MISMATCH N Y Parent 6 B 9 B 7 F 74 Child 6 B 91 CA 58 6 BA 1 DB 48 VARCHAR 2(30) 47 © 2006 Julian Dyke VARCHAR 2(40) juliandyke. com

V$SQL_BIND_METADATA u 48 Describes bind variables for each cursor in shared pool ADDRESS RAW(4) POSITION NUMBER DATATYPE NUMBER MAX_LENGTH NUMBER ARRAY_LEN NUMBER BIND_NAME VARCHAR 2(30) u Based on X$KKSBV u Note, in this case ADDRESS is the address of the child cursor © 2006 Julian Dyke juliandyke. com

Bind Variables u To check length of bind variables SELECT address, position, data_type, max_length, bind_name FROM v$sql_bind_metadata WHERE address IN ( SELECT child_address FROM v$sql WHERE address = '6 B 9 B 7 F 74' ); 49 ADDRESS POSITION DATATYPE MAX_LENGTH BIND_NAME 6 B 91 CA 58 1 1 32 V 1 6 BA 1 DB 48 1 1 128 V 1 © 2006 Julian Dyke juliandyke. com

Bind Variables u u u Multiple child cursors can be caused by bind variable lengths By default: u NUMBER is 22 bytes u VARCHAR 2 is rounded to next highest length which can be u 32 u 128 u 2000 u 4000 Rounding of VARCHAR 2 columns can be overridden by u enabling event 10503 u setting level to minimum bind variable length e. g. 128 ALTER SESSION SET EVENTS '10503 TRACE NAME CONTEXT FOREVER, LEVEL 128'; 50 © 2006 Julian Dyke juliandyke. com

Bind Variables u 51 For example, by default the following sessions will all generate different child cursors: VAR b 1 VARCHAR 2(30) VAR b 2 VARCHAR 2(60) SELECT c 1 FROM t 1 WHERE : b 1 = 100 AND : b 2 = 200; VAR b 1 VARCHAR 2(60) VAR b 2 VARCHAR 2(30) VAR b 2 VARCHAR 2(60) SELECT c 1 FROM t 1 WHERE : b 1 = 100 AND : b 2 = 200; © 2006 Julian Dyke juliandyke. com

Bind Variables u 52 On the other hand the following statements will all use the same child cursor: VAR b 1 VARCHAR 2(40) VAR b 2 VARCHAR 2(60) SELECT c 1 FROM t 1 WHERE : b 1 = 100 AND : b 2 = 200; VAR b 1 VARCHAR 2(60) VAR b 2 VARCHAR 2(40) VAR b 2 VARCHAR 2(60) SELECT c 1 FROM t 1 WHERE : b 1 = 100 AND : b 2 = 200; © 2006 Julian Dyke juliandyke. com

Internationalization 53 u Internationalization affects child cursors u Only a subset of SQL statements are affected including statements using: u Dates u Currency u Ordering © 2006 Julian Dyke juliandyke. com

Internationalization u Example 1 - Dates VAR b 1 VARCHAR(30); EXECUTE b 1 : = SYSDATE; ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'; SELECT TO_CHAR (TO_DATE (: b 1, 'DD-MON-YYYY'), 'yyyymmdd') FROM dual; ALTER SESSION SET NLS_LANGUAGE = 'GERMAN'; SELECT TO_CHAR (TO_DATE (: b 1, 'DD-MON-YYYY'), 'yyyymmdd') FROM dual; SELECT address, hash_value, child_number, child_address FROM v$sql WHERE sql_text LIKE 'SELECT TO_CHAR (TO_DATE (: b 1%'; 54 ADDRESS HASH_VALUE CHILD_NUMBER CHILD_ADDRESS 6 B 94 EA 84 659654739 0 6 BA 268 F 0 6 B 94 EA 84 659654739 1 6 B 8 E 3 B 20 © 2006 Julian Dyke juliandyke. com

Internationalization SELECT child_number, child_address, language_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 B 94 EA 84' CHILD_NUMBER CHILD_ADDRESS 0 1 6 BA 268 F 0 6 B 8 E 3 B 20 N Y LANGUAGE_MISMATCH Parent 6 B 94 EA 84 Child 6 BA 268 F 0 6 B 8 E 3 B 20 AMERICAN 55 © 2006 Julian Dyke GERMAN juliandyke. com

Internationalization u Example 2 - Ordering CREATE TABLE t 1 (c 1 NUMBER, c 2 VARCHAR 2(1)); ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'; SELECT c 1 FROM t 1 ORDER BY c 2; ALTER SESSION SET NLS_LANGUAGE = 'GERMAN'; SELECT c 1 FROM t 1 ORDER BY c 2; SELECT address, hash_value, child_number, child_address FROM v$sql WHERE sql_text LIKE 'SELECT c 1 FROM t 1 ORDER BY t 2%'; 56 ADDRESS HASH_VALUE CHILD_NUMBER CHILD_ADDRESS 6 BA 0358 C 245919138 0 6 BA 09 B 74 6 BA 0358 C 245919138 1 6 B 9 D 4560 © 2006 Julian Dyke juliandyke. com

Internationalization SELECT child_number, child_address, language_mismatch FROM v$sql_shared_cursor WHERE address = ' 6 BA 0358 C' CHILD_NUMBER CHILD_ADDRESS 0 1 6 BA 09 B 74 6 B 9 D 4560 N Y LANGUAGE_MISMATCH Parent 6 BA 0358 C Child 6 BA 09 B 74 6 B 9 D 4560 AMERICAN 57 © 2006 Julian Dyke GERMAN juliandyke. com

Public Synonyms u u This example shows behaviour of public synonyms As USER 1 CREATE TABLE t 1 (c 1 NUMBER, c 2 NUMBER); GRANT SELECT ON t 1 TO PUBLIC; CREATE PUBLIC SYNONYM t 1 FOR user 1. t 1; u As USER 2 SELECT COUNT (*) FROM t 1; SELECT address, hash_value, child_number, child_address FROM v$sql WHERE sql_text LIKE 'SELECT COUNT(*) FROM t 1%'; 58 ADDRESS HASH_VALUE CHILD_NUMBER CHILD_ADDRESS 6 B 96 CB 1 C 2856096030 6 B 8959 AC © 2006 Julian Dyke 0 juliandyke. com

Public Synonyms SELECT owner, namespace, type, sharable_mem FROM v$db_object_cache WHERE name = 'T 1'; OWNER NAMESPACE PUBLIC TABLE/PROCEDURE SYNONYM 345 USER 1 TABLE/PROCEDURE TABLE 343 USER 2 TABLE/PROCEDURE NOT LOADED u 59 TYPE SHARABLE_MEM 0 USER 2. T 1 is a virtual object u Created in library cache u Requires KGLHD, KGLOB and KGLNA structures u Does not use any subheaps u If USER 2 subsequently creates object called T 1 then the virtual object is used to identify statements which should be invalidated © 2006 Julian Dyke juliandyke. com

Public Synonyms u Each user requires one additional library cache object for each public synonym referenced Parent u Although table USER 2. T 1 is not loaded, memory is still required for the handle, object and name structures u If USER 3. T 1 executes the same statement, an additional library cache object will be created u Use fully qualified names instead of public synonyms 6 B 96 CB 1 C Child Table 6 B 91 CA 58 USER 1. T 1 Synonym PUBLIC. T 1 Table USER 2. N 1 Table USER 3. N 1 60 © 2006 Julian Dyke SELECT COUNT (*) FROM USER 1. t 1; juliandyke. com

DBMS_SHARED_POOL u u u Reports the largest objects currently in the shared pool Specify minimum size (in kilobytes) For example to list all objects with size > 64 KB use: SET SERVEROUTPUT ON EXECUTE dbms_shared_pool. sizes (64); u For example (output modified for readability): SIZE(K) KEPT NAME ---------------------428 SYS. STANDARD (PACKAGE) 200 SYS. ANYDATA (TYPE) 167 SELECT COUNT(*) FROM t 1, t 2 WHERE. . (6 B 8551 B 8, 593239587) u 61 (CURSOR) Specify size of 0 to return sizes of all objects in shared pool © 2006 Julian Dyke juliandyke. com

DBMS_SHARED_POOL u u Fragmentation of the shared pool can be reduced by specifying that objects should be kept. Kept objects are not subject to aging out of the shared pool EXECUTE dbms_shared_pool. keep ('<owner>, <object_name>', '<type>'); u u where <type> can be u 'P', 'p': Package/procedure/function u 'T', 't': Type u 'R', 'r': Trigger u 'Q', 'q': Sequence For example EXECUTE dbms_shared_pool. keep ('SYS. STANDARD', 'P'); u u 62 Any other value for <type> specifies a cursor Other object types including tables and views cannot be kept in Oracle 10. 2 or below © 2006 Julian Dyke juliandyke. com

DBMS_SHARED_POOL u To keep a cursor, first obtain the address of the parent cursor and the hash value u V$SQL. ADDRESS (hexadecimal) u V$SQL. HASH_VALUE (decimal) EXECUTE dbms_shared_pool. keep ('<address>, <hash_value>', 'C'); u For example: EXECUTE dbms_shared_pool. keep ('6 B 8551 B 8, 593239587', 'C'); u To discontinue keeping an object use UNKEEP procedure with the same parameters e. g. : EXECUTE dbms_shared_pool. unkeep ('SYS. STANDARD', 'P'); EXECUTE dbms_shared_pool. unkeep ('6 B 8551 B 8, 593239587', 'C'); 63 © 2006 Julian Dyke juliandyke. com

And Finally. . . u To flush the shared pool use: ALTER SYSTEM FLUSH

Any Questions ? info@juliandyke. com 65 © 2006 Julian Dyke juliandyke. com

Conclusion 66 u To avoid library cache invalidations u Avoid unnecessary DDL statements in application u To avoid library cache reloads u Increase size of shared pool u Avoid multiple parent cursors u Standardize SQL u Use cursor sharing u Avoid multiple child cursors caused by u Optimizer mismatches u Parameter mismatches u Translation mismatches u Bind variable mismatches u Language mismatches © 2006 Julian Dyke juliandyke. com