Troubleshooting Oracle Performance! Choi Jin Soo
1
www.unioneinc.co.kr
© 2008 Version 1
Agenda
2
© 2008 Version 1
Introduction Key Concepts Identification Tracing Database Calls DBMS_STATS Configuring Query Optimizer Execution Plans SQL Techniques Optimizing Data Access Optimizing Joins Others
www.unioneinc.co.kr
1
Configuring Query Optimizer 3
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer
The Query Optimizer is directly responsible for the performance of SQL Statements
Initialization Parameter Statistics
4
SYSTEM OBJECT
“Configuring the Query Optimizer is COSTLY, but it is worth the EXPENSE” a MAGIC configuration DON’T exist!
© 2008 Version 1
www.unioneinc.co.kr
2
Configuration Road Map optimizer_mode db_file_multiblcok_read_count
workarea_size_policy
optimizer_dynamic_sampling optimizer_features_enable optimizer_secure_view_merging query_rewrite_enabled query_rewrite_integrity star_transformation_enabled
auto
manual
pga_aggregate_target
bitmap_merge_area_size hash_area_size sort_area_size sort_area_retained_size
Test the application
gather system statistics gather object statistics
acceptable performance no
yes
optimizer_index_caching optimizer_index_cost_adj adjust histograms
5
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_MODE
ALL_ROWS
FIRST_ROWS_n
n is 1,10,100,100 1 10 100 100
HINTS
6
Default value in Oracle10g
/*+ all_rows */ /*+ first_rows(n) */
© 2008 Version 1
www.unioneinc.co.kr
3
Query Optimizer Parameters
OPTIMIZER_FEATURES_ENABLE
If you are upgrading to a new database version and want to keep the old behavior of the query optimizer, use it
not all new features are disabled by the initialization parameters! Changed at the Instance and Session level Statement level
Short-Tem Workround.
optimizer_features_enable(default) optimizer_features_enable(‘9.2.0’)
Disables not only features but bug-fixes as well V$PARAMETER_VALID_VALUES
SELECT value FROM v$parameter_valid_values WHERE name = 'optimizer_features_enable'; 2
3
VALUE --------------------------8.0.0 8.0.3 …… 7
www.unioneinc.co.kr
© 2008 Version 1
Query Optimizer Parameters
DB_FILE_MULTIBLOCK_READ_COUNT
The Imaximum I/O size during Multiblock Reads
Common situations leading to multibock reads that are smaller than DB DB_FILE_MULTIBLOCK_READ_COUNT FILE MULTIBLOCK READ COUNT
DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE
Segment Headers Physical Reads never span extent boundaries Blocks already cached in the buffer cache
Singleblock Reads ? Multiblock Reads ? Average MBRC ? segment header
extent1
cached block extent2
no-cached block
Structure of a data segment 8
© 2008 Version 1
www.unioneinc.co.kr
4
Query Optimizer Parameters
DB_FILE_MULTIBLOCK_READ_COUNT
To configure automatic DB_FILE_MULTIBLOCK_READ_COUNT
Simply DON’T set it
db_file_multiblock_size ≈ min
1048576
,
db_block_size
9
© 2008 Version 1
db_cache_size sessions * db_block_size
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING
The statistics is dynamically gathered during parse phase The statistics gathered by dynamic sampling are neither stored in the data dictionary nor stored elsewhere Only way to virtually reuse them is to reuse the shared cursor itself The default value depends on OPTIMIZER_FEATURES_ENABLE
HINTS
10
If it is set to 10.0.0 or higher, the default is level 2 If it is set to 9.2.0 or higher, the default is level 1 If it is set to 9.0.1 or lower, dynamic sampling is disabled /*+ dynamic_sampling(level) */ /*+ dynamic y _sampling(table p g( _name level)) */
© 2008 Version 1
www.unioneinc.co.kr
5
Query Optimizer Parameters
11
OPTIMIZER_DYNAMIC_SAMPLING Level
When is dynamic sampling used?
0
dynamic sampling is disabled
0
1
Dynamic sampling is used for tables without object statistics. However, this occurs only if the following three conditions are met: the table has no index, it is part of a join (also subquery or nonmergeable view), and it has more blocks than the number of blocks used for the sampling.
32
2
Dynamic sampling is used for all tables without object statistics.
32
3
Dynamic D i sampling li iis used d ffor all ll ttables bl fulfilling f lfilli th the level-2 criterion and, in addition, for which a guess is used to estimate the selectivity of a predicate.
32
4
Dynamic sampling is used for all tables fulfilling the level-3 criterion and, in addition, having two or more columns referenced in the WHERE clause.
32
© 2008 Version 1
number of blocks
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING SQL>exec dbms_stats.delete_table_stats(user,'T1'); SQL>EXPLAIN PLAN FOR SELECT * FROM t1 WHERE C1=10; SQL>SELECT * FROM table(DBMS_XPLAN.DISPLAY); ( _ ); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------Plan hash value: 838529891 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 26 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 26 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------1 - filter("C1"=10) Note ----- dynamic sampling used for this statement
12
© 2008 Version 1
www.unioneinc.co.kr
6
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1',cascade=>T RUE); SQL> EXPLAIN PLAN FOR SELECT * FROM t1 WHERE C1 C1=10; 10 SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------Plan hash value: 838529891 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 6 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------1 - filter("C1"=10)
13
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING SQL> EXPLAIN PLAN FOR SELECT /*+ dynamic_sampling(3) */ * FROM t1 WHERE C1=10; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------Plan hash value: 838529891 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 6 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------1 - filter("C1"=10)
14
© 2008 Version 1
www.unioneinc.co.kr
7
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING SQL> EXPLAIN PLAN FOR SELECT /*+ dynamic_sampling(3) */ * FROM t1 WHERE round(C1)=10; SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------Plan hash value: 838529891 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 6 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------1 - filter("C1"=10) Note ----- dynamic sampling used for this statement
15
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_DYNAMIC_SAMPLING SQL> EXPLAIN PLAN FOR SELECT /*+ dynamic_sampling(4) */ * FROM t1 WHERE C1=10 AND C2=111; SELECT * FROM table(DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------Plan hash value: 838529891 -------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 6 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------1 - 1 - filter("C2"=1111 AND "C1"=1) Note ----- dynamic sampling used for this statement
16
© 2008 Version 1
www.unioneinc.co.kr
8
Query Optimizer Parameters
OPTIMIZER_INDEX_COST_ADJ
To change the cost of table access through index scans. Valid values go from 1 to 10000 The default value is 100 Data Segment
Index Segment
S
D
AUS BEL CAN
17
E
DEN
U
ENG
SCO SPA
USA
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_INDEX_COST_ADJ
If optimizer_index_cost_adj = 100
io_cost ≈ (blevel + (leaf_blocks + clustering_factor) * selectivity)
If optimizer_index_cost_adj 100
ti i i d t dj io_costoptimizer_index_cost_adj ≈ io_cost y optimizer_index_cost_adj 100
18
© 2008 Version 1
www.unioneinc.co.kr
9
Query Optimizer Parameters
OPTIMIZER_INDEX_CACHING
To specify the expected amount of index blocks cached in the buffer cache during the execution of IN-LIST iterators and NESTED LOOP JOINS Valid values range from 0 to 100 The default is 0 If optimizer_index_caching > 0
io_costoptimizer_index_caching ≈ (blevel + (leaf_blocks * selectivity) *
19
1-
optimizer_index_cost_adj optimizer index cost adj 100
© 2008 Version 1
www.unioneinc.co.kr
Query Optimizer Parameters
OPTIMIZER_SECURE_VIEW_MERGING
Introduced in Oracle 10g R2 Avaiable value is TRUE | FALSE The default is TRUE
20
FALSE allows allo s the q query er optimi optimizer er to always al a s do View Vie Merging TRUE allows the query optimizer to do View Mering so will not lead to SECURITY Issues
© 2008 Version 1
www.unioneinc.co.kr
10
Automatic PGA Management
WORKAREA_SIZE_POLICY
Introduced in Oracle 9i Avaiable value is AUTO | MANUAL
PGA AGGREGATE TARGET PGA_AGGREGATE_TARGET
Specifies the total mount of PGA dedicated to specific Oracle Instance
Not a hard limit but is rather a target V$PGASTAT
21
In Oracle9i, the default value is 0 In Oracle10g, the default value is 20% of the size of SGA
NAME in (‘aggregate PGA target parameter’,’maximum PGA allocated’)) allocated
Can be changed only at the Instance level
© 2008 Version 1
www.unioneinc.co.kr
Manual PGA Management
SORT_AREA_SIZE
SORT_AREA_RETAINED_SIZE
The default value is 2 * SORT_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
22
Specifies the amount of memory retained for READ Buffer The default value is 0
HASH_AREA_SIZE
The default value is 64KB
The default value is 1MB
© 2008 Version 1
www.unioneinc.co.kr
11
EXECUTION PLANS 23
www.unioneinc.co.kr
© 2008 Version 1
Execution Plan
Describe the operations carried out by the SQL Engine(Query Optimizer) to execute a SQL Statement Input
SELECT * FROM t1 WHERE C1=10;
Query Optimizer
Output ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 6 | 8 (13) | 00:00:01 | |* 1 | TABLE ACCESS FULL | T1 | 1 | 6 | 8 (13) | 00:00:01 | -------------------------------------------------------------------------------------------------------------------24
© 2008 Version 1
www.unioneinc.co.kr
12
Obtaining Execution Plans
Basically, Oracle provides Four methods to obtain an execuion plan
25
EXPLAIN PLAN Library Cache Automatic Workload Repository or Statspack table Tracing facility
www.unioneinc.co.kr
© 2008 Version 1
EXECUTION PLAN
Take a SQL Statement as input and provide its execution plan and some infomation as output, in the PLAN TABLE Create PLAN_TABLE
$ORACLE_HOME/rdbms/admin/utlxplan.sql $ In Oracle 10g
SYS.PLAN_TABLE IS GLOBAL TEMPORARY TABLE
Usually don’t need to query it directly Use DBMS_XPLAN.DISPLAY SET STATEMENT_ID = id EXECUTION PLAN
26
© 2008 Version 1
INTO table FOR statement
www.unioneinc.co.kr
13
EXECUTION PLAN
The most common mistake in using EXPLAIN PLAN is to specify a SQL Statement that is diffenrent from the one to be Analyzed
By replacing BIND VARIABLES to LITERAL
The correct approach is to use the COMPLTE SAME SQL Statement
EXPLAIN PLAN’s Problems
By default, bind-variables are declared as VARCHAR2
NO bind variable peeking
Automatically adds EXPLICT conversions
Whenever bind Wh bi d variables i bl are involved, i l d the output generated by the SQL Statement EXPLAIN PLAN is UNRELIABLE !!!!!
27
© 2008 Version 1
www.unioneinc.co.kr
In the LIBRARY CACHE
Show information about the cursors present in the LIBRARY CACHE
V$SQL_PLAN V$SQL_PLAN_STATISTICS
28
SQL_ADDRESS + SQL_HASH_VALUE SQL_ID
Child Cursor
V$SQL_WORKAREA V$SQL_PLAN_STATISTICS_ALL
Parent Cursor
STATISTICS_LEVEL STATISTICS LEVEL is ALL HINT : /*+ gather_plan_statistics */
Parent Cursor + CHILD_NUMBER
V$SQL
© 2008 Version 1
www.unioneinc.co.kr
14
In the LIBRARY CACHE
In Oracle9i, the package DBMS_XPLAN don’t provide the function DISPLAY_CURSOR Alternative Way is
Extract the information about the execution plan from the library cache Insert it into a plan table Use DBMS_XPLAN.DISPLAY
SQL>SELECT address, hash_value, child_number, sql_text FROM V$SQL WHERE sql_text LIKE ‘%search_string%’ AND sql_text NOT LIKE ‘%v$sql%’; SQL>DELETE FROM plan_table; SQL>INSERT INTO plan_table (operation, options, object_node, object_owner, object_name, optimizer, search_columns, id, parent_id, position, cost, cardinality, bytes,other_tag, partition_start, partition_stop, partition id, other, distribution, cpu_cost, partition_id, cpu cost, io_cost, io cost, temp_space, temp space, access_predicates, access predicates, filter_predicates) filter predicates) SELECT operation, options, object_node, object_owner, object_name, optimizer, search_columns, id, parent_id, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates FROM v$sql_plan WHERE address = '0000000055DCD888‘ AND hash_value = 4132422484 AND child_number = 0; SQL> SELECT * FROM table(dbms_xplan.display);
29
© 2008 Version 1
www.unioneinc.co.kr
AWR or Statspack
AWR
St t Statspack k
DBA_HIST_SQL_PLAN DBMS_XPLAN.DISPLAY_AWR
STATS$SQL
Provide useful reports for highlighting execution plan changes and resource resumption variation during a period of time for a specific SQL Statement
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql $ORACLE_HOME/rdbms/admin/sprepsql.sql _ p p q q
30
When a level equal to or greater than 6 is used for taking snapshots
© 2008 Version 1
www.unioneinc.co.kr
15
Tracing Facilities
Event 10046
SQL_TRACE
Event 10053
This Event is not officially supported only if you are really interested in the INTERNAL workings of the query optimizer only when a HARD parse is performed ALTER SESSION SET events ‘10053 trace name context forever’; DO SOMETHING ! ALTER SESSION SET events ‘10053 10053 trace name context off’; off ;
Event 10132
31
Generate the execution plan related to every HARD parse List of parameters and bug-fixes A significant OVERHEAD !
© 2008 Version 1
www.unioneinc.co.kr
10053 EVENT Sample
To analyze the change in the execution plan for specific SQL Statement
SINGLE TABLE ACCESS PATH SINGLE TABLE ACCESS PATH Access Path: TableScan Access Path: TableScan C t 64581 Cost: 64581.50 50 R Resp: 64581 64581.50 50 D Degree: 0 Cost: 73563.45 Resp: 73563.45 Degree: 0 Cost_io: 64345.00 Cost_cpu: 3321006607 Cost_io: 73296.00 Cost_cpu: 3755578731 Resp_io: 64345.00 Resp_cpu: 3321006607 Resp_io: 73296.00 Resp_cpu: 3755578731 Access Path: index (RangeScan) Access Path: index (RangeScan) Index: TRANS_SUM_BK_IDX Index: TRANS_SUM_BK_IDX resc_io: 4.00 resc_cpu: 26520 resc_io: 4.00 resc_cpu: 26519 ix_sel: 9.1044e-08 ix_sel_with_filters: 9.1044e-08 ix_sel: 7.9343e-08 ix_sel_with_filters: 7.9343e-08 Cost: 4.00 Resp: 4.00 Degree: 1 Cost: 4.00 Resp: 4.00 Degree: 1 Access Path: index (RangeScan) Access Path: index (RangeScan) Index: TRANS_SUM_PK Index: TRANS_SUM_PK resc_io: 4.00 resc_cpu: 26494 resc io: 5 resc_io: 5.00 00 resc_cpu: resc cpu: 33616 ix_sel: 2.9359e-14 ix_sel_with_filters: 2.9359e-14 ix_sel: 2.3574e-14 ix_sel_with_filters: 2.3574e-14 Cost: 4.00 Resp: 4.00 Degree: 1 Cost: 5.00 Resp: 5.00 Degree: 1
“Cost-Based Oracle Fundaments” written by Jonathan’Lewis 32
© 2008 Version 1
www.unioneinc.co.kr
16
10053 EVENT Sample
To analyze the change in the execution plan for specific SQL Statement
Table Stats:: Table: TRANS_SUM Alias: TRANS_SUM Table Partition [1] Stats:: #Rows: 5574118 #Blks: 294140 AvgRowLen: Table:368.00 TRANS_SUM Alias: TRANS_SUM Partition [1] #Rows: 5574118 #Blks: 294140 AvgRowLen: 368.00 #Rows: 6224858 #Blks: 335060 AvgRowLen: 366.00 Index Stats:: #Rows: 6224858 #Blks: 335060 AvgRowLen: 366.00 Index: TRANS_SUM_BK_IDX Col#: 1 4 5 Index PARTITION Stats::[1] LVLS: 2 #LB: 38857 #DK: 2773524 LB/K:Index: 1.00 DB/K: TRANS_SUM_BK_IDX Col#: 1 4 5 PARTITION [1] 1.00 CLUF: 3305375.00 LVLS: 2 #LB: 44109 #DK: 3252032 LB/K: 1.00 DB/K: LVLS: 2 #LB: 38857 #DK: 2773524 LB/K: 1.001.00 DB/K: CLUF: 3834427.00 1.00 CLUF: 3305375.00 LVLS: 2 #LB: 44109 #DK: 3252032 LB/K: 1.00 DB/K: Index: TRANS_SUM_PK Col#: 1 2 3 PARTITION [1] CLUF: 3834427.00 1.00 LVLS: 2 #LB: 61270 #DK: 5620445 LB/K:Index: 1.00 DB/K: TRANS_SUM_PK Col#: 1 2 3 PARTITION [1] 1.00 CLUF: 4031524.00 LVLS: 3 #LB: 65579 #DK: 6022558 LB/K: 1.00 DB/K: LVLS: 2 #LB: 61270 #DK: 5620445 LB/K: 1.001.00 DB/K: CLUF: 4303431.00 1.00 CLUF: 4031524.00 LVLS: 3 #LB: 65579 #DK: 6022558 LB/K: 1.00 DB/K: 1.00 CLUF: 4303431.00
“Cost-Based Oracle Fundaments” written by Jonathan’Lewis 33
© 2008 Version 1
www.unioneinc.co.kr
V$SQL_PLAN
Introduced in Oracle 9.0.1
Shows actual(run-time) execution plan in memory
Enhanced in Oracle 9.2 to include
Related views include
34
Access Predicates (Joins) Filter Predicates
V$SQL_PLAN_WORKAREA V$SQL_PLAN_STATISTICS V$SQL_PLAN_STATISTICS_ALL
© 2008 Version 1
www.unioneinc.co.kr
17
V$SQL_PLAN ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER CHILD ADDRESS CHILD_ADDRESS TIMESTAMP OPERATION OPTIONS OBJECT_NODE OBJECT# OBJECT_OWNER OBJECT_NAME OPTIMIZER ID PARENT_ID DEPTH POSITION COST CARDINALITY
35
RAW(4) NUMBER VARCHAR2(13) NUMBER RAW(8) DATE VARCHAR2(30) VARCHAR2(30) VARCHAR2(10) NUMBER VARCHAR2(30) VARCHAR2(64) VARCHAR2(20) NUMBER U NUMBER NUMBER NUMBER NUMBER NUMBER
BYTES OTHER_TAG PARTITION_START PARTITION_STOP PARTITION_ID OTHER DISTRIBUTION CPU_COST IO_COST TEMP_SPACE ACCESS PREDICATES ACCESS_PREDICATES FILTER_PREDICATES PROJECTION TIME QBLOCK_NAME REMARKS
NUMBER VARCHAR(35) VARCHAR2(5) VARCHAR2(5) NUMBER VARCHAR2(4000) VARCHAR2(20) NUMBER NUMBER NUMBER VARCHAR2(4000) VARCHAR2(4000) VARCHAR2(4000) NUMBER VARCHAR2(31) VARCHAR2(4000)
www.unioneinc.co.kr
© 2008 Version 1
Optimizer Environment Variables
In Oracle 10.1 and above, optimizer environment variables are externalized at :
36
instance level session level statement level
- V$SYS_OPTIMIZER_ENV _ _ - V$SES_OPTIMIZER_ENV - V$SQL_OPTIMIZER_ENV
Use the values in these views when determining why execution plans differ
© 2008 Version 1
www.unioneinc.co.kr
18
Optimizer Environment Variables
Optimizer Environment Variable values reported by the dynamic performance views include: active_instance_count
parallel_dml_mode
bitmap merge area size bitmap_merge_area_size
parallel execution enabled parallel_execution_enabled
cpu_count
parallel_query_mode
cursor_sharing
parallel_threads_per_cpu
db_file_multiblock_read_count
pga_aggregate_target
hash_area_size
query_rewrite_enabled
optimizer_dynamic_sampling
query_rewrite_integrity
optimizer_features_enable
skip_unusable_indexes
optimizer_index_caching
sort_area_retained_size
optimizer_index_cost_adj
sort_area_size
optimizer_mode
star_transformation_enabled
optimizer_secure_view_merging
statistics_level
optimizer_mode_hinted
workarea_size_policy
parallel_ddl_mode 37
www.unioneinc.co.kr
© 2008 Version 1
DBMS_XPLAN
38
Introduced in Oracle 9.2 Display execution plans stored in three diffenrent places FUNCTION
Where
Dictionary y
DISPLAY
EXPLAIN PLAN
PLAN_TABLE
DISPLAY_CURSOR
LIBRARY CACHE
V$SQL_PLAN
DISPLAY_AWR
AWR
DBA_HIST_SQL_PLAN
© 2008 Version 1
www.unioneinc.co.kr
19
DISPLAY Function
Introduced in Oracle 9.2 Formats PLAN_TABLE contents generated by EXPLAIN PLAN SELECT * FROM TABLE (dbms_xplan.display);
DISPLAY function parameters include TABLE_NAME – name of plan table
39
The default is PLAN_TABLE
STATEMENT_ID – statement ID in plan table The default is NULL
FORMAT – information in the output
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY Function
FORMAT
Primitive values accepted by the FORMAT parameter
Value
Description
BASIC
Operation ID, object name and operation/option only
TYPICAL
(Default) Most relevant information including partition pruning, parallelism and predicates where appropriate
ALL
As TYPICAL plus parallel execution server statements
SERIAL
As TYPICAL without parallel execution server statements
ADVANCED
All available information
Modifiers values accepted by the FORMAT parameter
Value
40
Description
Value
ALIAS
PARTITION
BYTES
PEEKED_BINDS
COST
PREDICATE
NOTE
PREJECTION
OUTLINE
REMOTE
PARALLEL
ROWS
© 2008 Version 1
Description p
www.unioneinc.co.kr
20
DISPLAY Function
For example explain a query EXPLAIN PLAN FOR SET STATEMENT_ID = 'STATEMENT1' FOR SELECT t1.c2, t2.c2 FROM t1 t1, t2 WHERE t1.c1 = t2.c1 AND t1.c2 = 10;
The plan table can be queried using SELECT * FROM TABLE ( dbms_xplan.display ('PLAN_TABLE','STATEMENT1') );
41
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY Function
Example output with predicates Plan hash value: 3693069535 Id Operation O ti
N Name
0 SELECT STATEMENT *1
HASH JOIN
R Rows
B t Bytes
C t Cost
10
590
66
10
590
66
*2
TABLE ACCESS FULL
T1
10
60
58
3
TABLE ACCESS FULL
T2
1000
53000
7
Predicate Information (identified by operation id): 1 - access("T1"."C1"="T2"."C1") 2 - filter("T1"."C2"=10)
42
© 2008 Version 1
www.unioneinc.co.kr
21
DISPLAY Function
Parallel execution queries are automatically formatted e.g. EXPLAIN PLAN FOR SELECT /*+ ORDERED PARALLEL (t1 2) USE_MERGE (t1 t2) */ t1.c2, t2.c2 FROM t1, t2 WHERE t1.c1 = t2.c1 AND t1.c2 = 10;
The plan table can be queried using SELECT * FROM TABLE (dbms_xplan.display);
43
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY Function
Example output for parallel execution
Id Operation
Name Rows Bytes Cost
TQ
IN-OUT PQ Distrib
0 SELECT STATEMENT
10
590
79
1
MERGE JOIN
10
590
79 78,02
2
SORT JOIN
10
60
33 78,02 PCWP
10
60
29 78,01
*3 *4 5
TABLE ACCESS FULL
T1
SORT JOIN TABLE ACCESS FULL
1000 53000 T2
1000 53000
P->S QC(RANDOM) P->P HASH
47 78,02 PCWP 7 78,00
S->P HASH
Predicate Information ((identified by y operation p id): ) 34-
44
filter("T1"."C2"=10) access("T1"."C1"="T2"."C1") filter("T1"."C1"="T2"."C1")
© 2008 Version 1
www.unioneinc.co.kr
22
DISPLAY Function
Partition pruning information can also be included e.g. for a range partitioned table CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 CHAR(50)) PARTITION BY RANGE ((c1)) ( PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (300), PARTITION p4 VALUES LESS THAN (400) ); EXPLAIN PLAN FOR SELECT c2 FROM t1 WHERE c1 >= 150 AND c1 < 250; SELECT * FROM TABLE (dbms_xplan.display);
45
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY Function
Example output for partition pruning
Id Operation
Name Rows Bytes Cost
0 SELECT STATEMENT 1 *2
1
26
PStart PStop
2
PARTITION RANGE ITERATOR TABLE ACCESS FULL
T1
1
26
2
2
3
2
3
Predicate Information (identified by operation id): 2-
46
filter("T1"."C1">=150 AND "T1"."C1" SELECT COUNT(*) FROM t1; SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor); SQL ID gnsdc08rf7y1c SQL_ID gnsdc08rf7y1c, child number 0 ------------------------------------------------------SELECT COUNT(*) FROM t1 Plan hash value: 3693069535 Id Operation
Name
Rows Cost
0 SELECT STATEMENT 1 2
49
(%CPU)
Time
160
(100)
51809 160
(2)
SORT AGGREGATE
1
TABLE ACCESS FULL
T1
00:00:02
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY_CURSOR Function SELECT /*+ gather_plan_statistics */ COUNT(*) FROM t1; SELECT * FROM TABLE (dbms_xplan.display_cursor(null,null,’iostats last’); SQL ID gnsdc08rf7y1c SQL_ID gnsdc08rf7y1c, child number 0 ------------------------------------------------------SELECT COUNT(*) FROM t1 Plan hash value: 3693069535 Id 1 2
50
Operation
Na me
SORT AGGREGATE TABLE ACCESS FULL
© 2008 Version 1
T1
Starts E-Rows A-Rows A-Time
buffers
reads
1
1
1
00:00.04
42
40
1
1
0
00 00 04 00:00:04
42
40
www.unioneinc.co.kr
25
DISPLAY_AWR Function
In Oracle 10.1 and above New DISPLAY_AWR function DISPLAY_AWR function parameters include SQL_ID SQL ID – parent cursor identifier
PLAN_HASH_VALUE – hash value of the execution plan
DB_ID – which database
The default is NULL The default is NULL
FORMAT – Information in the ouput
51
NO default value
The default is TYPICAL
www.unioneinc.co.kr
© 2008 Version 1
DISPLAY_AWR Function SQL> SELECT COUNT(*) FROM t1; SQL > SELECT * FROM TABLE (dbms xplan.display awr(‘gnsdc08rf7y1c’,null,null,’basic’); (dbms_xplan.display_awr( gnsdc08rf7y1c ,null,null, basic );
Id Operation
Name
0 SELECT STATEMENT 1 2
52
© 2008 Version 1
SORT AGGREGATE TABLE ACCESS FULL
T1
www.unioneinc.co.kr
26
Interpreting Execution Plans
Many people are unable to correctly read EXECUTION PLAN Understand how to walk-through execution plans An EXECUTION PLAN is a TREE describing g not only in which ORDER the SQL Engine executes OPERATIONS but also the RELATIONSHIP between operations !!!!!
OPERATION
OPERATION
RELATIONSHIP
SORT AGGREGATION Parent
TABLE ACCESS Child
NODE
53
© 2008 Version 1
NODE
www.unioneinc.co.kr
Interpreting Execution Plans
OPERATIONS are following :
PARENT-CHILD RELATIONSHIP’s RULES
54
TABLE ACCESS INDEX ACCESS JOIN SORT
A parent has one child or multiple children A child has a single parent Only operation without parent is the root of the tree The children are indented to RIGHT All children of a specific parent have the very same indentation A parent is placed before its children If there are several preceding operations for a child, with the same indentation as the parent, the nearest operatin is the parent
© 2008 Version 1
www.unioneinc.co.kr
27
Interpreting Execution Plans Id Operation
Name
Relationship
0 SELECT STATEMENT 1
SORT AGGREGATE
2
TABLE ACCESS FULL
Parent T1
Child
1
55
2
www.unioneinc.co.kr
© 2008 Version 1
Interpreting Execution Plans Id Operation
Name
Relationship
0 SELECT STATEMENT 1
SORT AGGREGATION
2
Parent
NESTED LOOPS
Child, Parent(2)
3
TABLE ACCESS FULL
T1
Child
4
TABLE ACESS BY INDEX ROWID
T2
Child, Parent(1)
T2_IDX_01
Child
5
INDEX RANGE SCAN
3 1
2 4
56
© 2008 Version 1
5 www.unioneinc.co.kr
28
Interpreting Execution Plans
The number of possible operations is very HIGH(about 200)
Three major types of operations:
57
Stand-Alone Operations Unrelated-Combine operations Related-Combine operations
www.unioneinc.co.kr
© 2008 Version 1
Interpreting Execution Plans
Stand-Alone operation
All operations having at most one child The workings of Stand-Alone operations are following :
Children are executed before their parents Every child is executed at most ONCE Every child feeds its parent
1
58
© 2008 Version 1
2
3
www.unioneinc.co.kr
29
Interpreting Execution Plans
Stand-Alone operation sample
SELECT c1, count(*) FROM t1 WHERE c1 >= 505 and c2=5;
Id Operation
Name
Starts
A-Rows
0 SELECT STATEMENT 1
SORT AGGREGATE
2
TABLE ACCESS FULL
3
TABLE ACESS BY INDEX ROWID
1
59
1
100
T1
1
500
T1_IDX_C1
1
505
2
3
www.unioneinc.co.kr
© 2008 Version 1
Interpreting Execution Plans
Unrelated-Combine operation
All operations having multiple children that are INDEPENDENTLY executed The workings of Unrelated-Combine operations are following :
Children are executed before their parents Children are executed sequentially, starting from the one with smallest ID and going to the one with the highest ID Before starting the processing of a subsequent child, the current child must be completely executed Every child is executed at most ONCE and INDEPENTLY from all other children Every child feeds its parent
2
1
3
4 60
© 2008 Version 1
www.unioneinc.co.kr
30
Interpreting Execution Plans
Unrelated-Combine operation sample Unrelated-Combind Operation AND-EQUAL BITMAP AND BITMAP OR BITMAP MINUS CONCATENATION CONNECT BY WITHOUT FILTERING HASH JOIN INTERSECTION MINUS MERGE JOIN MULTI-TABLE SQL MODEL TEMP TABLE TRANSFORMATION UNION ALL
61
www.unioneinc.co.kr
© 2008 Version 1
Interpreting Execution Plans
Unrelated-Combine operation sample
SELECT c1 FROM t1 UNION ALL SELECT c2 FROM t2 UNIONALL SELECT ‘%’ % FROM dual; Id Operation
Name
Starts
A-Rows
0 SELECT STATEMENT 1
1
19
2
UNION ALL TABLE ACCESS FULL
T1
1
14
3
TABLE ACCESS FULL
T2
1
4
4
FAST DUAL
1
1
2 1
3 4
62
© 2008 Version 1
www.unioneinc.co.kr
31
Interpreting Execution Plans
Related-Combine operation
All operations having multiple children that one of children controls the execution of all other children The workings of Related-Combine operations are following :
Children are executed before their parents The child with the smalllest ID controls the execution of the other children Children are executed going from the one one with smallest ID to the one with the highest ID Only the first child is executed at most once. All other children may be executed several times or not executed at all Not Every child feeds its parent. Some children are used apply restrictions only
2 1 3
63
© 2008 Version 1
4
www.unioneinc.co.kr
Interpreting Execution Plans
Unrelated-Combine operation sample Unrelated-Combind Operation NESTED LOOPS UPDATE FILTER CONNECT BY WITH FILTERING BITMAP KE ITERATION
64
© 2008 Version 1
www.unioneinc.co.kr
32
Interpreting Execution Plans
Related-Combine operation sample
SELECT t1.c1, t2.c2 FROM t1, t2 WHERE t1.c2=5 and t1.c1=t2.c1; Id Operation
Name
Starts
A-Rows
0 SELECT STATEMENT 1
NESTED LOOPS
1
8
1
10
2
TABLE ACCESS FULL
T1
3
TABLE ACESS BY INDEX ROWID
T1_IDX_C1
10
8
T2_C1_PK
10
10
4
INDEX UNIQUE SCAN
2 1 3 65
4 www.unioneinc.co.kr
© 2008 Version 1
WHY Inefficient Execution Plans
Wrong Estimations
The Query Optimizer computes costs to decide which access paths, join orders, and join methods should be used to get an efficient plan If the computation of the cost is WRONG : Checking the estimated cardinality with DBMS_XPLAN.DISPLAY_CURSOR !!!!! Compare it with the actual data !!!!!
66
© 2008 Version 1
www.unioneinc.co.kr
33
Interpreting Execution Plans
Wrong Estimation Sample
SELECT count(t2.c2) FROM t1, t2 WHERE t1.id = t2.id AND t1.c1=666; Id Operation 1 2 3 4 5 6
Name
SORT AGGREGATE NESTED LOOPS TABLE ACCESS BY INDEX ROWID INDEX RANGE SCAN TABLE ACESS BY INDEX ROWID INDEX UNIQUE SCAN
Starts
E-Rows A-Rows
1
1
1
1
32
75808
T1
1
32
80016
T1_C1
1
32
80016
T2
80016
1
75808
T2_PK
80016
1
75808
SELECT num_rows, distinct_keys, num_rows/distinct_keys “avg_rows_per_key” FROM user_ind_statistics WHERE index_name = ‘T1_C1’; NUM_ROWS : 160000 DISTINCT_KEYS : 5000 AVG_ROWS_PER_KEY : 32
67
www.unioneinc.co.kr
© 2008 Version 1
Interpreting Execution Plans
Wrong Estimation Sample
SELECT count(*) num_rows, count(distinct c1) distinct_keys, count(decode(c1,’666’,1,null)) rows_key_666 FROM T1; NUM_ROWS : 160000 DISTINCT_KEYS : 5000 ROWS_KEY_666 : 80016
SELECT histogram, num_buckets FROM user_tab_col_statistics WHER table_name = ‘T1’ AND column_name = ‘C1’; HISTOGRAM : NONE NUM_BUCKETS : 1
GATHER COLUMN HISOGRAM !!!!!
68
© 2008 Version 1
www.unioneinc.co.kr
34
Interpreting Execution Plans
Wrong Estimation Sample Id Operation 1 2
69
Name
SORT AGGREGATE HASH JOIN
Starts
E-Rows A-Rows
1
1
1
1
1
75808
3
TABLE ACCESS FULL
T1
1
80000
80016
4
TABLE ACCESS FULL
T2
1
151K
151K
© 2008 Version 1
www.unioneinc.co.kr
SQL Tuning Techniques 70
© 2008 Version 1
www.unioneinc.co.kr
35
SQL Tuning Techniques and Impact
Available SQL Tuning Techniques and Their Impacts Technique Altering Alt i the th access structure
SYSTEM
SESSION
Y
Altering the execution environment SQL Outlines SQL Profiles SQL Plan Baselines
71
© 2008 Version 1
Availability ALL Releases
Y
Altering the SQL Statement Hints
SQL Statement
Y
Y
ALL Releases
Y
ALL Releases
Y
ALL Releases
Y
ALL R l Releases
Y
Enterprise Edition
Y
Enterprise Edition
www.unioneinc.co.kr
SQL Tuning Techniques and Impact
Altering the Access Structrues Techniques Adding Index Dropping Index Modifying Index Partitioning Gathering SYSTEM or OBJECT Statistics
When altering the access structure, it is essential to carefully consider possible side effects. Every altered access structure introduces both POSITIVE and NEGATIVE consequences Altering the ACCESS Structure ONLY When the pros of it outweigh the cons of it !!!!!
72
© 2008 Version 1
www.unioneinc.co.kr
36
SQL Tuning Techniques and Impact
Altering the SQL Statement
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); SELECT deptno FROM dep WHERE NOT EXISTS (SELECT 1 FROM emp WHERE emp.deptno=dept.deptno);
SELECT deptno FROM dept MINUS SELECT deptno FROM emp; SELECT dept.deptno FROM dept, emp WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL;
The Purpose of these SQL Statements is the SAME and The Result they return are the SAME as well The Execution Plans of these SQL Statements may be DIFFERENT !!!!! 73
© 2008 Version 1
www.unioneinc.co.kr
SQL Tuning Techniques and Impact
HINTs
Directives added to SQL Statement to influence the query optimizer’s decisions Whenenver you specify a hint, your goal is to reduce the number of execution plans considered by the query optimizer Basically, you tell the query optimizer which operations should or should not be considered for a specific SQL Statement A complete list of all HINTS in Oracle11g
74
V$SQL_HINT
© 2008 Version 1
www.unioneinc.co.kr
37
SQL Tuning Techniques and Impact
HINTs
Initialization parameter hints are valid for the whole SQL Statement All other hints are valid for a single query block only
E Exeptions i to this hi rule l are the h G GLOBAL OBA HINTS
75
Level
Hints
Initialization parameter hints
all_rows, cursor_sharing_exact, dynamic_sampling, first_rows, gather_plan_statistics, no_cpu_costing, optimizer_features_enable, opt_param
Query transformation hints
(no_)rewrite, (no_)unnest, use_concat, (no_)eliminate_join, no_expand, (no_)merge, (no_)push_pred, (no_)push_subq, no_query_transformation
Access path hints
full, (no_)index, index_asc, index_combine, index_desc, (no_)index_ffs, (no_)index_ss, index_ss_asc, index_ss_desc
Join hints
(no_)use_hash, (no_)use_merge, (no_)use_nl, leading, use_nl_with_index
Parallel processing hints
(no_)parallel, (no_)parallel_index, pq_distribute
Other hints
(no_)append, (no_)cache, driving_site, qb_name
© 2008 Version 1
www.unioneinc.co.kr
SQL Tuning Techniques and Impact
HINTs
EXPLAIN PLAN FOR SELECT dept.dname, emps.cnt FROM dept, (SELECT deptno, count(*) AS cnt FROM emp GROUP BY deptno) emps WHERE dept.deptno = emps.deptno; SELECT * FROM table(DBMS_XPLAN.DISPLAY(NULL,NULL,'basic +alias'));
--------------------------------------------------------------------------- Query Block Name / Object Alias (identified | Id | Operation | Name | by operation id): ---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | 1 - SEL$1 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 - SEL$1 / DEPT@SEL$1 | 3 | INDEX FULL SCAN | DEPT_PK | | 4 | SORT JOIN | | 3 - SEL$1 / DEPT@SEL$1 | 5 | VIEW | | 5 - SEL$2 / EMPS@SEL$1 | 6 | HASH GROUP BY | | 6 - SEL$2 | 7| TABLE ACCESS FULL | EMP | 7 - SEL$2 / EMP@SEL$2 ---------------------------------------------------------------------------
76
© 2008 Version 1
www.unioneinc.co.kr
38
SQL Tuning Techniques and Impact
HINTs
EXPLAIN PLAN FOR SELECT /*+ qb_name(main_qb) */ dept.dname, emps.cnt FROM dept, (SELECT /*+ qb_name(sub_qb) */ deptno, count(*) AS cnt FROM emp GROUP BY deptno) emps WHERE dept.deptno = emps.deptno; SELECT * FROM table(DBMS_XPLAN.DISPLAY(NULL,NULL,'basic +alias'));
--------------------------------------------------------------------------- Query Block Name / Object Alias (identified | Id | Operation | Name | by operation id): ---------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | 1 - MAIN_QB MAIN QB | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 - MAIN_QB / DEPT@MAIN_QB | 3 | INDEX FULL SCAN | DEPT_PK | | 4 | SORT JOIN | | 3 - MAIN_QB / DEPT@MAIN_QB | 5 | VIEW | | 5 - SUB_QB / EMPS@MAIN_QB | 6 | HASH GROUP BY | | 6 - SUB_QB | 7| TABLE ACCESS FULL | EMP | 7 - SUB_QB / EMP@SUB_QB ---------------------------------------------------------------------------
77
© 2008 Version 1
www.unioneinc.co.kr
SQL Tuning Techniques and Impact
HINTs
EXPLAIN PLAN FOR SELECT /*+ qb_name(main_qb) full(@main_qb dept) full(@sub_qb emp) */ dept.dname, emps.cnt FROM dept, (SELECT //*+ qb_name(sub_qb) qb name(sub qb) *// deptno, count( count(*)) AS cnt FROM emp GROUP BY deptno) emps WHERE dept.deptno = emps.deptno; SELECT * FROM table(DBMS_XPLAN.DISPLAY(NULL,NULL,'basic +alias')); ---------------------------------------------------------| Id | Operation | Name | ----------------------------------------------------------| 0 | SELECT STATEMENT | | | 1 | HASH JOIN | | | 2 | TABLE ACCESS FULL | DEPT | | 3 | VIEW | | | 4 | HASH GROUP BY | | | 5 | TABLE ACCESS FULL | EMP |
78
© 2008 Version 1
Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------1 - MAIN_QB MAIN QB 2 - MAIN_QB / DEPT@MAIN_QB 3 - SUB_QB / EMPS@MAIN_QB 4 - SUB_QB 5 - SUB_QB / EMP@SUB_QB
www.unioneinc.co.kr
39
SQL Tuning Techniques and Impact
Altering the Execution Environment
79
Dynamic Performance Views displaying the environment related instance, a single session, or a child cursor
V$ View
Level
V$SYS_OPTIMIZER_ENV
INSTANCE
V$SES_OPTIMIZER_ENV
SESSION
V$SQL_OPTIMIZER_ENV
CURSOR
© 2008 Version 1
www.unioneinc.co.kr
SQL Tuning Techniques and Impact
SQL PROFILE
An extension of the Query Optimizer
80
Automatic Tuning Advisor DBMS_SQLTUNE Package DBA SQL PROFILES DBA_SQL_PROFILES SYS.SQLPROF$ / SYS.SQLPROF$ATTR
© 2008 Version 1
www.unioneinc.co.kr
40
Optimizing Data Access 81
© 2008 Version 1
www.unioneinc.co.kr
Optimizing Data Access
Describe how to recognize inefficient access paths by looking at runtime statistics provied by SQL Trace or Dynamic Performance Views
Describe available access methods When you should take advantage of them
Basically only TWO ways to locate data in a table
Scan the whole table Do lookup based on a redundant access stucture
Index Cluster Partitioning
The MOST efficient access path is able to process the data by consuming the LEAST amount of resources !!!!!
82
© 2008 Version 1
www.unioneinc.co.kr
41
Optimizing Data Access
Efficiency Identification of Data Access The check should be based on the amount of resources used to return a single row
A Single Database Metric : LOGICAL READS CPU-bounded operation Reduce phycial reads Operation subject to serialization Available at the SQL Statement and execution plan levels
Criterion
83
Average Logical Reads Per Returned rows
