Transcript Library Cache Internals

Library Cache
Internals
Julian Dyke
Independent Consultant
Web Version
1
© 2006 Julian Dyke
juliandyke.com
Agenda





2
© 2006 Julian Dyke
Introduction
Multiple Parent Cursors
Multiple Child Cursors
DBMS_SHARED_POOL
Summary
juliandyke.com
Shared Pool

3
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
© 2006 Julian Dyke
juliandyke.com
Shared Pool Size


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 = 32M
IF 64-bit platform
size = 84M
4
© 2006 Julian Dyke
juliandyke.com
Library Cache

5
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
© 2006 Julian Dyke
juliandyke.com
Object Types

Every object in the library cache has an object type. Object
types include:
Cursor
Type
Index-Organized
Table
Type
Java
Index
Cluster
View
Synonym
Sequence
Procedure
Function
Package
Package
Body
Trigger
6
© 2006 Julian Dyke
Body
Object
User
Database Link
Pipe
Table Partition
Index Partition
LOB
Library
Directory
Queue
Table
Source
Java Class
Java Resource
Java JAR
Table Subpartition
Index Subpartition
LOB Partition
LOB Subpartition
Summary
Dimension
Stored Outline
juliandyke.com
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
7
© 2006 Julian Dyke
Application
Context
Stored Outline
Ruleset
Resource Plan
Resource Consumer Group
Subscription
Location
Remote Object
Snapshot Metadata
Java Shared Data
Security Profile
juliandyke.com
V$DB_OBJECT_CACHE


8
Reports all objects currently in the library cache
OWNER
VARCHAR2(64)
NAME
VARCHAR2(1000)
DB_LINK
VARCHAR2(64)
NAMESPACE
VARCHAR2(28)
TYPE
VARCHAR2(28)
SHARABLE_MEM
NUMBER
LOADS
NUMBER
EXECUTIONS
NUMBER
LOCKS
NUMBER
PINS
NUMBER
KEPT
VARCHAR2(3)
CHILD_LATCH
NUMBER
INVALIDATIONS
NUMBER
Based on X$KGLOB
© 2006 Julian Dyke
juliandyke.com
Library Cache Dumps

To dump the contents of the library cache use
ALTER SESSION SET EVENTS
'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL n';



9
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
© 2006 Julian Dyke
juliandyke.com
SQL Statements

Each SQL statement has
 Parent cursor
 One or more child cursors
Parent
Child 0
10
© 2006 Julian Dyke
Child 1
Child 2
Child 3
juliandyke.com
Parent Cursor
11

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)
© 2006 Julian Dyke
juliandyke.com
Parent Cursor
KGLHD
KGLOB
KGLNA
KGLNA
KGLNA
12
© 2006 Julian Dyke
SELECT c1,
c2,c3,c4,c5,
c6,c7,c8,c9,
c10
FROM t1,t2,
t3,t4,t5
WHERE
t1.c1=t2.c1..
juliandyke.com
V$SQLAREA

13
Contains one row for each parent cursor
SQL_TEXT
VARCHAR2(1000)
BUFFER_GETS
NUMBER
MODULE_HASH
NUMBER
SQL_FULLTEXT
CLOB
APPLICATION_WAIT_TIME
NUMBER
ACTION
VARCHAR2(64)
SQL_ID
VARCHAR2(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
VARCHAR2(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
VARCHAR2(40)
LOADED_VERSIONS
NUMBER
COMMAND_TYPE
NUMBER
LAST_ACTIVE_CHILD_ADDRESS
RAW(4)
OPEN_VERSIONS
NUMBER
OPTIMIZER_MODE
VARCHAR2(10)
REMOTE
VARCHAR2(1)
USERS_OPENING
NUMBER
OPTIMIZER_COST
NUMBER
OBJECT_STATUS
VARCHAR2(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
VARCHAR2(1)
END_OF_FETCH_COUNT
NUMBER
PARSING_SCHEMA_ID
NUMBER
CHILD_LATCH
NUMBER
USERS_EXECUTING
NUMBER
PARSING_SCHEMA_NAME
VARCHAR2(64)
SQL_PROFILE
VARCHAR2(64)
LOADS
NUMBER
KEPT_VERSIONS
NUMBER
PROGRAM_ID
NUMBER
FIRST_LOAD_TIME
VARCHAR2(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
VARCHAR2(64)
BIND_DATA
RAW(2000)
© 2006 Julian Dyke
juliandyke.com
Child Cursors
14

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)
© 2006 Julian Dyke
juliandyke.com
Child Cursors
KGLHD
KGLOB
KGLHD
15
KGLHD
© 2006 Julian Dyke
KGLHD
Parent
Cursor
KGLNA
KGLHD
KGLHD
KGLHD
juliandyke.com
Child Cursors
KGLHD
Child
Cursor
KGLOB
Heap 0
Environment
16
© 2006 Julian Dyke
Statistics
Heap 6
Bind
Variables
Execution
Plan
juliandyke.com
V$SQL

Contains one row for each child cursor
SQL_TEXT
17
VARCHAR2(1000)
CLUSTER_WAIT_TIME
NUMBER
MODULE_HASH
NUMBER
VARCHAR2(64)
SQL_FULLTEXT
CLOB
USER_IO_WAIT_TIME
NUMBER
ACTION
SQL_ID
VARCHAR2(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
VARCHAR2(64)
RUNTIME_MEM
NUMBER
COMMAND_TYPE
NUMBER
CPU_TIME
NUMBER
SORTS
NUMBER
OPTIMIZER_MODE
VARCHAR2(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
VARCHAR2(1)
EXECUTIONS
NUMBER
PARSING_SCHEMA_ID
NUMBER
OBJECT_STATUS
VARCHAR2(19)
PX_SERVERS_EXECUTIONS
NUMBER
PARSING_SCHEMA_NAME
VARCHAR2(30)
LITERAL_HASH_VALUE
NUMBER
END_OF_FETCH_COUNT
NUMBER
KEPT_VERSIONS
NUMBER
LAST_LOAD_TIME
VARCHAR2(19)
USERS_EXECUTING
NUMBER
ADDRESS
RAW(4)
IS_OBSOLETE
VARCHAR2(1)
LOADS
NUMBER
TYPE_CHK_HEAP
RAW(4)
CHILD_LATCH
NUMBER
FIRST_LOAD_TIME
VARCHAR2(19)
HASH_VALUE
NUMBER
SQL_PROFILE
VARCHAR2(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
NUMBER
EXACT_MATCHING_SIGNATURE
NUMBER
DIRECT_WRITES
NUMBER
SERVICE
VARCHAR2(64)
FORCE_MATCHING_SIGNATURE
NUMBER
BUFFER_GETS
NUMBER
SERVICE_HASH
NUMBER
LAST_ACTIVE_TIME
DATE
APPLICATION_WAIT_TIME
NUMBER
MODULE
VARCHAR2(64)
BIND_DATA
RAW(2000)
CONCURRENCY_WAIT_TIME
NUMBER
© 2006 Julian Dyke
juliandyke.com
Object Heaps



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

19
Each parent requires at least one child cursor
Parent
Parent
Parent
Parent
Child
Child
Child
Child
© 2006 Julian Dyke
juliandyke.com
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;

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






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
 EXACT (default)
 FORCE
 SIMILAR (Oracle 9.0.1 and above)
© 2006 Julian Dyke
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT c2 FROM t1%';
22
ADDRESS
CHILD_ADDRESS
6BA7F7F8
6B9B6BE4
SELECT c2 FROM t1 WHERE c1 = 0
6B8FB104
6BB158F0
SELECT c2 FROM t1 WHERE c1 = 1
© 2006 Julian Dyke
STATEMENT
Parent
Parent
6BA7F7F8
6B8FB104
Child
Child
6B9B6BE4
6BB158F0
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT c2 FROM t1%';
ADDRESS
CHILD_ADDRESS
STATEMENT
6BA93574
6B8D1A84
SELECT c2 FROM t1 WHERE c1 = :"SYS_B_0"
Parent
6BA93574
Child
6B8D1A84
23
© 2006 Julian Dyke
juliandyke.com
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;
/
24
© 2006 Julian Dyke
juliandyke.com
Cursor Sharing - Similar

Example
-- Create a non-unique index on c3
CREATE INDEX t1_i1 ON t1 (c3);
-- Create a non-unique index on c4
CREATE 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;
25
© 2006 Julian Dyke
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1 WHERE c3 = %';
ADDRESS
CHILD_ADDRESS
6BA812EC
6BB34F64
STATEMENT
SELECT COUNT(*) FROM t1 WHERE c3 = :"SYS_B_0"
Parent
6BA812EC
Child
6BB34F64
26
© 2006 Julian Dyke
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1 WHERE c4 = %';
27
ADDRESS
CHILD_ADDRESS
STATEMENT
6B8A5D54
6BB2D674
SELECT COUNT(*) FROM t1 WHERE c4 = '0'
67026E34
6702E9A4
SELECT COUNT(*) FROM t1 WHERE c4 = :"SYS_B_0"
67026E34
671B91E0
SELECT COUNT(*) FROM t1 WHERE c4 = :"SYS_B_0"
67026E34
6B95A4A8
SELECT COUNT(*) FROM t1 WHERE c4 = :"SYS_B_0"
Parent
Parent
6B8A5D54
67026E34
Child
Child
Child
Child
6BB2D674
6702E9A4
671B91E0
6B95A4A8
© 2006 Julian Dyke
juliandyke.com
Multiple Child Cursors

28
Can be created for a number of reasons including differences
in:
 System / Session parameters
 Object translation
 Bind variables
 NLS parameters
© 2006 Julian Dyke
juliandyke.com
V$SQL_SHARED_CURSOR
SQL_ID
VARCHAR2(13)
DESCRIBE_MISMATCH
VARCHAR2(1)
DIFFERENT_LONG_LENGTH
VARCHAR2(1)
ADDRESS
RAW(4)
LANGUAGE_MISMATCH
VARCHAR2(1)
LOGICAL_STANDBY_APPLY
VARCHAR2(1)
CHILD_ADDRESS
RAW(4)
TRANSLATION_MISMATCH
VARCHAR2(1)
DIFF_CALL_DURN
VARCHAR2(1)
CHILD_NUMBER
NUMBER
ROW_LEVEL_SEC_MISMATCH
VARCHAR2(1)
BIND_UACS_DIFF
VARCHAR2(1)
UNBOUND_CURSOR
VARCHAR2(1)
INSUFF_PRIVS
VARCHAR2(1)
PLSQL_CMP_SWITCHS_DIFF
VARCHAR2(1)
SQL_TYPE_MISMATCH
VARCHAR2(1)
INSUFF_PRIVS_REM
VARCHAR2(1)
CURSOR_PARTS_MISMATCH
VARCHAR2(1)
OPTIMIZER_MISMATCH
VARCHAR2(1)
REMOTE_TRANS_MISMATCH
VARCHAR2(1)
STB_OBJECT_MISMATCH
VARCHAR2(1)
OUTLINE_MISMATCH
VARCHAR2(1)
LOGMINER_SESSION_MISMATCH
VARCHAR2(1)
ROW_SHIP_MISMATCH
VARCHAR2(1)
STATS_ROW_MISMATCH
VARCHAR2(1)
INCOMP_LTRL_MISMATCH
VARCHAR2(1)
PQ_SLAVE_MISMATCH
VARCHAR2(1)
LITERAL_MISMATCH
VARCHAR2(1)
OVERLAP_TIME_MISMATCH
VARCHAR2(1)
TOP_LEVEL_DDL_MISMATCH
VARCHAR2(1)
SEC_DEPTH_MISMATCH
VARCHAR2(1)
SQL_REDIRECT_MISMATCH
VARCHAR2(1)
MULTI_PX_MISMATCH
VARCHAR2(1)
EXPLAIN_PLAN_MISMATCH
VARCHAR2(1)
MV_QUERY_GEN_MISMATCH
VARCHAR2(1)
BIND_PEEKED_PQ_MISMATCH
VARCHAR2(1)
BUFFERED_DML_MISMATCH
VARCHAR2(1)
USER_BIND_PEEK_MISMATCH
VARCHAR2(1)
MV_REWRITE_MISMATCH
VARCHAR2(1)
PDML_ENV_MISMATCH
VARCHAR2(1)
TYPCHK_DEP_MISMATCH
VARCHAR2(1)
ROLL_INVALID_MISMATCH
VARCHAR2(1)
INST_DRTLD_MISMATCH
VARCHAR2(1)
NO_TRIGGER_MISMATCH
VARCHAR2(1)
OPTIMIZER_MODE_MISMATCH
VARCHAR2(1)
SLAVE_QC_MISMATCH
VARCHAR2(1)
FLASHBACK_CURSOR
VARCHAR2(1)
PX_MISMATCH
VARCHAR2(1)
TYPECHECK_MISMATCH
VARCHAR2(1)
ANYDATA_TRANSFORMATION
VARCHAR2(1)
MV_STALEOBJ_MISMATCH
VARCHAR2(1)
AUTH_CHECK_MISMATCH
VARCHAR2(1)
INCOMPLETE_CURSOR
VARCHAR2(1)
FLASHBACK_TABLE_MISMATCH
VARCHAR2(1)
BIND_MISMATCH
VARCHAR2(1)
TOP_LEVEL_RPI_CURSOR
VARCHAR2(1)
LITREP_COMP_MISMATCH
VARCHAR2(1)
29
© 2006 Julian Dyke
juliandyke.com
V$SQL_SHARED_CURSOR
30

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
© 2006 Julian Dyke
juliandyke.com
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$kkscs
GROUP 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_columns
WHERE table_name = 'V_$SQL_SHARED_CURSOR'
AND data_type = 'VARCHAR2'
AND data_length = 1
ORDER BY column_id;
31
© 2006 Julian Dyke
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
32
ADDRESS
CHILD_ADDRESS
6B97E3C0
6BA0FC18
SELECT COUNT(*) FROM t1;
6B97E3C0
6BAE9904
SELECT COUNT(*) FROM t1;
© 2006 Julian Dyke
STATEMENT
juliandyke.com
Optimizer Mode

As SYSDBA
SELECT
child_number,
child_address,
optimizer_mode_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B97E3C0'
CHILD_NUMBER
CHILD_ADDRESS
0
1
6BA0FC18
6BAE9904
N
Y
OPTIMIZER_MODE_MISMATCH
Parent
6B97E3C0
optimizer_mode = CHOOSE
33
© 2006 Julian Dyke
Child
Child
6B9B6BE4
6BB158F0
optimizer_mode = ALL_ROWS
juliandyke.com
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
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

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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
35
ADDRESS
CHILD_ADDRESS
6B97E3C0
6BA937EC
SELECT COUNT(*) FROM t1;
6B97E3C0
6B855EB4
SELECT COUNT(*) FROM t1;
6B97E3C0
6BB1DE24
SELECT COUNT(*) FROM t1;
© 2006 Julian Dyke
STATEMENT
juliandyke.com
Supported Optimizer Parameters

As SYSDBA
SELECT
child_number,
child_address,
optimizer_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B97E3C0'
CHILD_NUMBER
CHILD_ADDRESS
0
1
2
6BA937EC
6B855EB4
6BB1DE24
N
Y
Y
OPTIMIZER_MISMATCH
Parent
6B97E3C0
optimizer_index_caching
36
© 2006 Julian Dyke
Child
Child
Child
6BA937EC
6B855EB4
6BB1DE24
20
40
0
juliandyke.com
Unsupported Optimizer Parameters

Optimizer environment parameter views are based on fixed
table views
 V$SYS_OPTIMIZER_ENV
X$QKSCESYS
 V$SES_OPTIMIZER_ENV
X$QKSCESES
 V$SQL_OPTIMIZER_ENV
X$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$qkscesys
WHERE SUBSTR (pname_qkscesyrow,1,1) = '_'
ORDER BY 1;
37
© 2006 Julian Dyke
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
38
ADDRESS
CHILD_ADDRESS
6B97E3C0
6B879828
SELECT COUNT(*) FROM t1;
6B97E3C0
6B976F20
SELECT COUNT(*) FROM t1;
© 2006 Julian Dyke
STATEMENT
juliandyke.com
Unsupported Optimizer Parameters

As SYSDBA
SELECT
child_number,
child_address,
optimizer_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B97E3C0'
CHILD_NUMBER
CHILD_ADDRESS
0
1
6B879828
6B976F20
N
Y
OPTIMIZER_MISMATCH
Parent
6B97E3C0
_unnest_subquery
39
© 2006 Julian Dyke
Child
Child
6B879828
6B976F20
TRUE
FALSE
juliandyke.com
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$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
40
ADDRESS
CHILD_ADDRESS
6B97E27C
6B99348C
SELECT COUNT(*) FROM t1;
6B97E27C
6B895F5C
SELECT COUNT(*) FROM t1;
© 2006 Julian Dyke
STATEMENT
juliandyke.com
Trace
SELECT
child_number,
child_address,
stats_row_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B97E27C'
CHILD_NUMBER
CHILD_ADDRESS
0
1
6B99348C
6B895F5C
N
Y
STATS_ROW_MISMATCH


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
6B97E3C0
Child
Child
6B99348C
6B895F5C
FALSE
TRUE
juliandyke.com
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);
CREATE TABLE t1
(c1 NUMBER);
SELECT c1 FROM t1;
SELECT c1 FROM t1;
The statement SELECT c1 FROM t1 will have a shared parent
cursor, but multiple child cursors
Parent
Cursor
Child
Cursor 1
42
© 2006 Julian Dyke
USER1.T1
SELECT c1
FROM t1;
Child
Cursor 2
USER2.T1 juliandyke.com
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_address
FROM v$sql
WHERE sql_text LIKE 'SELECT c1 FROM t1%';
43
ADDRESS
HASH_VALUE CHILD_NUMBER
CHILD_ADDRESS
6B8E5AEC
3805054639
0
6B8B6C30
6B8E5AEC
3805054639
1
6B8DA100
© 2006 Julian Dyke
juliandyke.com
Translations
SELECT
child_number,
child_address,
auth_check_mismatch,
translation_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B8E5AEC'
CHILD_NUMBER
0
1
6B8B6C30
6B8DA100
AUTH_CHECK_MISMATCH
N
Y
TRANSLATION_MISMATCH
N
Y
CHILD_ADDRESS
Parent
6B9B7F74
44
© 2006 Julian Dyke
Child
Child
6B91CA58
6BA1DB48
USER1
USER2
juliandyke.com
Translations

Determining translated objects
SELECT
kgltrorg,
kgltrfnl
FROM x$kgltr
WHERE kglhdadr = '6B8B6C30';
KGLTRORG
KGLTRFNL
KGLTRORG
KGLTRFNL
6BA68DAC
6BA68DAC
6B8F9220
6B8F9220
SELECT
kglnaown
kglnaobj
FROM x$kglob
WHERE kglhdadr = '6BA68DAC';
45
SELECT
kgltrorg,
kgltrfnl
FROM x$kgltr
WHERE kglhdadr = '6B8DA100';
SELECT
kglnaown
kglnaobj
FROM x$kglob
WHERE kglhdadr = '6B8F9220';
KGLNAOWN
KGLNAOBJ
KGLNAOWN
KGLNAOBJ
USER1
T1
USER2
T1
© 2006 Julian Dyke
juliandyke.com
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_address
FROM v$sql
WHERE sql_text LIKE 'SELECT c2 FROM t1 WHERE c1 = %';
46
ADDRESS
HASH_VALUE CHILD_NUMBER
CHILD_ADDRESS
6B9B6F74
357538776
0
6B91CA58
6B9B6F74
357538776
1
6BA1DB48
© 2006 Julian Dyke
juliandyke.com
Bind Variables
SELECT
child_number,
child_address,
bind_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B9B7F74'
CHILD_NUMBER
0
CHILD_ADDRESS
1
6B91CA58 6BA1DB48
BIND_MISMATCH
N
Y
Parent
6B9B7F74
Child
Child
6B91CA58
6BA1DB48
VARCHAR2(30)
47
© 2006 Julian Dyke
VARCHAR2(40)
juliandyke.com
V$SQL_BIND_METADATA

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
VARCHAR2(30)

Based on X$KKSBV

Note, in this case ADDRESS is the address of the child cursor
© 2006 Julian Dyke
juliandyke.com
Bind Variables

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 = '6B9B7F74'
);
49
ADDRESS
POSITION
DATATYPE
MAX_LENGTH
BIND_NAME
6B91CA58
1
1
32
V1
6BA1DB48
1
1
128
V1
© 2006 Julian Dyke
juliandyke.com
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';
50
© 2006 Julian Dyke
juliandyke.com
Bind Variables

51
For example, by default the following sessions will all
generate different child cursors:
VAR b1 VARCHAR2(30)
VAR b1 VARCHAR2(30)
VAR b2 VARCHAR2(30)
VAR b2 VARCHAR2(60)
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
VAR b1 VARCHAR2(60)
VAR b1 VARCHAR2(60)
VAR b2 VARCHAR2(30)
VAR b2 VARCHAR2(60)
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
© 2006 Julian Dyke
juliandyke.com
Bind Variables

52
On the other hand the following statements will all use the
same child cursor:
VAR b1 VARCHAR2(40)
VAR b1 VARCHAR2(40)
VAR b2 VARCHAR2(40)
VAR b2 VARCHAR2(60)
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
VAR b1 VARCHAR2(60)
VAR b1 VARCHAR2(60)
VAR b2 VARCHAR2(40)
VAR b2 VARCHAR2(60)
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
SELECT c1 FROM t1
WHERE :b1 = 100
AND
:b2 = 200;
© 2006 Julian Dyke
juliandyke.com
Internationalization
53

Internationalization affects child cursors

Only a subset of SQL statements are affected including
statements using:
 Dates
 Currency
 Ordering
© 2006 Julian Dyke
juliandyke.com
Internationalization

Example 1 - Dates
VAR b1 VARCHAR(30);
EXECUTE b1 := SYSDATE;
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
SELECT TO_CHAR (TO_DATE (:b1,'DD-MON-YYYY'),'yyyymmdd')
FROM dual;
ALTER SESSION SET NLS_LANGUAGE = 'GERMAN';
SELECT TO_CHAR (TO_DATE (:b1,'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 (:b1%';
54
ADDRESS
HASH_VALUE CHILD_NUMBER
CHILD_ADDRESS
6B94EA84
659654739
0
6BA268F0
6B94EA84
659654739
1
6B8E3B20
© 2006 Julian Dyke
juliandyke.com
Internationalization
SELECT
child_number,
child_address,
language_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6B94EA84'
CHILD_NUMBER
CHILD_ADDRESS
0
1
6BA268F0
6B8E3B20
N
Y
LANGUAGE_MISMATCH
Parent
6B94EA84
Child
Child
6BA268F0
6B8E3B20
AMERICAN
55
© 2006 Julian Dyke
GERMAN
juliandyke.com
Internationalization

Example 2 - Ordering
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(1));
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
SELECT c1 FROM t1 ORDER BY c2;
ALTER SESSION SET NLS_LANGUAGE = 'GERMAN';
SELECT c1 FROM t1 ORDER BY c2;
SELECT address,hash_value,child_number,child_address
FROM v$sql
WHERE sql_text LIKE 'SELECT c1 FROM t1 ORDER BY t2%';
56
ADDRESS
HASH_VALUE CHILD_NUMBER
CHILD_ADDRESS
6BA0358C
245919138
0
6BA09B74
6BA0358C
245919138
1
6B9D4560
© 2006 Julian Dyke
juliandyke.com
Internationalization
SELECT
child_number,
child_address,
language_mismatch
FROM v$sql_shared_cursor
WHERE address = ' 6BA0358C'
CHILD_NUMBER
CHILD_ADDRESS
0
1
6BA09B74
6B9D4560
N
Y
LANGUAGE_MISMATCH
Parent
6BA0358C
Child
Child
6BA09B74
6B9D4560
AMERICAN
57
© 2006 Julian Dyke
GERMAN
juliandyke.com
Public Synonyms


This example shows behaviour of public synonyms
As USER1
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
GRANT SELECT ON t1 TO PUBLIC;
CREATE PUBLIC SYNONYM t1 FOR user1.t1;

As USER2
SELECT COUNT (*) FROM t1;
SELECT address,hash_value,child_number,child_address
FROM v$sql
WHERE sql_text LIKE 'SELECT COUNT(*) FROM t1%';
58
ADDRESS
HASH_VALUE CHILD_NUMBER
CHILD_ADDRESS
6B96CB1C
2856096030
6B8959AC
© 2006 Julian Dyke
0
juliandyke.com
Public Synonyms
SELECT owner,namespace,type,sharable_mem
FROM v$db_object_cache
WHERE name = 'T1';
OWNER
NAMESPACE
TYPE
PUBLIC
TABLE/PROCEDURE
SYNONYM
345
USER1
TABLE/PROCEDURE
TABLE
343
USER2
TABLE/PROCEDURE
NOT LOADED

59
SHARABLE_MEM
0
USER2.T1 is a virtual object
 Created in library cache
 Requires KGLHD, KGLOB and KGLNA structures
 Does not use any subheaps
 If USER2 subsequently creates object called T1 then the
virtual object is used to identify statements which should
be invalidated
© 2006 Julian Dyke
juliandyke.com
Public Synonyms

Each user requires one additional library cache object for
each public synonym referenced
Parent

Although table USER2.T1 is not
loaded, memory is still required
for the handle, object and name
structures

If USER3.T1 executes the same
statement, an additional library
cache object will be created

Use fully qualified names
instead of public synonyms
6B96CB1C
Child
Table
6B91CA58
USER1.T1
Synonym
PUBLIC.T1
Table
USER2.N1
Table
USER3.N1
60
© 2006 Julian Dyke
SELECT COUNT (*)
FROM USER1.t1;
juliandyke.com
DBMS_SHARED_POOL



Reports the largest objects currently in the shared pool
Specify minimum size (in kilobytes)
For example to list all objects with size > 64KB use:
SET SERVEROUTPUT ON
EXECUTE dbms_shared_pool.sizes (64);

For example (output modified for readability):
SIZE(K) KEPT
NAME
------- ------ ------------------------------------------428
SYS.STANDARD
(PACKAGE)
200
SYS.ANYDATA
(TYPE)
167
SELECT COUNT(*) FROM t1,t2 WHERE..
(6B8551B8,593239587)

61
(CURSOR)
Specify size of 0 to return sizes of all objects in shared pool
© 2006 Julian Dyke
juliandyke.com
DBMS_SHARED_POOL


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>');


where <type> can be
 'P','p':
Package/procedure/function
 'T','t':
Type
 'R','r':
Trigger
 'Q','q':
Sequence
For example
EXECUTE dbms_shared_pool.keep ('SYS.STANDARD','P');


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

To keep a cursor, first obtain the address of the parent cursor
and the hash value
 V$SQL.ADDRESS (hexadecimal)
 V$SQL.HASH_VALUE (decimal)
EXECUTE dbms_shared_pool.keep ('<address>,<hash_value>','C');

For example:
EXECUTE dbms_shared_pool.keep ('6B8551B8,593239587','C');

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 ('6B8551B8,593239587','C');
63
© 2006 Julian Dyke
juliandyke.com
And Finally...

To flush the shared pool use:
ALTER SYSTEM FLUSH SHARED_POOL;

64
Flushes all objects which are not currently pinned
© 2006 Julian Dyke
juliandyke.com
Any Questions ?
[email protected]
65
© 2006 Julian Dyke
juliandyke.com
Conclusion
66

To avoid library cache invalidations
 Avoid unnecessary DDL statements in application

To avoid library cache reloads
 Increase size of shared pool
 Avoid multiple parent cursors
 Standardize SQL
 Use cursor sharing
 Avoid multiple child cursors caused by
 Optimizer mismatches
 Parameter mismatches
 Translation mismatches
 Bind variable mismatches
 Language mismatches
© 2006 Julian Dyke
juliandyke.com