Transcript 101 Ways to get SQL Execution Plan. What is the best option to use? By: Paul Guerin (Originenergy), Serge Stadnichenko (Originenergy), Konrad Dear (Telstra), Yury Velikanov.

101 Ways to get SQL Execution Plan.
What is the best option to use?
By:
Paul Guerin (Originenergy),
Serge Stadnichenko (Originenergy),
Konrad Dear (Telstra),
Yury Velikanov (Pythian),
& All of you
Introductions
Typical responses to poor performance:
"We need to buy faster hardware!!!"
"Someone has been dropping indexes!!!"
"We need to gather stats on the whole database every 5 minutes!!!"
"Lets create more materialised views!!!“
"We develop the application for optimal performance!!!“
"Lets use partitioning option!!!"
"Lets use parllel option!!!"
Inefficient execution plans lead to poor performance and poor
scalability....
Few words about - "What is an execution plan?“
Execution plans
>
>
>
>
>
>
>
AUTOTRACE (SQL*Plus)
EXPLAN PLAN FOR & DBMS_XPLAN
SQL TRACE aka event 10046
event 10053
Tanel's Dtrace method
V$SQL_PLAN & DBMS_XPLAN.DISPLAY_CURSOR
SQLT (SQLTXPLAIN) tool from Oracle Support
Execution plans - SET AUTOTRACE – the simplest option
19:38:52 SYSTEM:MEGA> set autotrace helpmefindparameters
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]]
[STAT[ISTICS]]
SET AUTOTRACE ON
<statement>;
SET AUTOTRACE OFF
21:14:15 IMTDB:SYSTEM> set autotrace on
21:15:29 IMTDB:SYSTEM> select id from t1 t where n1 > 0;
ID
---------1
2
3
4
...
998
999
1000
1000 rows selected.
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1000 Bytes=7000)
1
0
TABLE ACCESS (FULL) OF 'T1' (Cost=4 Card=1000 Bytes=7000)
Statistics
---------------------------------------------------------0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
8516 bytes sent via SQL*Net to client
706 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
21:17:47 IMTDB:SYSTEM>
21:27:26 SYSTEM:MEGA> set autotrace on
21:27:48 SYSTEM:MEGA> select id from t1 t where n1 > 0;
ID
---------1
2
3
4
...
998
999
1000
1000 rows selected.
Execution Plan
---------------------------------------------------------Plan hash value: 3617692013
-------------------------------------------------------------------------| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------|
0 | SELECT STATEMENT |
| 1000 | 7000 |
6
(0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1
| 1000 | 7000 |
6
(0)| 00:00:01 |
-------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - filter("N1">0)
Statistics
---------------------------------------------------------1 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
11113 bytes sent via SQL*Net to client
1137 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000 rows processed
21:28:37 SYSTEM:MEGA>
Execution plans - SET AUTOTRACE – the simplest option
+ It is most probably the simplest possibe option
+ Oracle executes an SQL (true execution plan for given session)
+ Oracle executes an SQL (statistics are reflected)
- Oracle executes an SQL (results are reflected, what is thousands rows?)
SET AUTOTRACE TRACEONLY
- Oracle executes an SQL (results are fetched to client side)
- Oracle executes an SQL (what if it takes hours before is starts fetching?)
- It doens't provide advantacges that other options have
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON STATISTICS
To use this feature, you must create a PLAN_TABLE table in your schema and
then have the PLUSTRACE role granted to you.
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
Execution plans - EXPLAIN PLAN FOR – plan w/t execution
-- No statement execution, displays predicted plan only
EXPLAIN PLAN FOR <statement>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
21:51:00 IMTDB:SYSTEM> explain plan for select id from t1 t where n1 > 0;
Explained.
21:51:04 IMTDB:SYSTEM> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost |
-------------------------------------------------------------------|
0 | SELECT STATEMENT
|
| 1000 | 7000 |
4 |
|* 1 | TABLE ACCESS FULL
| T1
| 1000 | 7000 |
4 |
-------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - filter("T"."N1">0)
Note: cpu costing is off
14 rows selected.
21:51:08 IMTDB:SYSTEM>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------Plan hash value: 3739423728
-----------------------------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
74 |
| 34309
(1)|305:38:23 |
|
1 | SORT AGGREGATE
|
|
1 |
74 |
|
|
|
|
2 |
NESTED LOOPS SEMI
|
| 2768 |
200K|
| 34309
(1)|305:38:23 |
|
3 |
NESTED LOOPS
|
| 2768 |
173K|
| 28773
(1)|256:19:22 |
|* 4 |
HASH JOIN
|
|
521 | 22924 |
| 26777
(1)|238:32:29 |
|
5 |
VIEW
|
|
499 | 4491 |
| 4139
(1)| 36:52:20 |
|* 6 |
COUNT STOPKEY
|
|
|
|
|
|
|
|
7 |
VIEW
|
|
255K| 2243K|
| 4139
(1)| 36:52:20 |
|* 8 |
SORT ORDER BY STOPKEY |
|
255K| 4736K| 8048K| 4139
(1)| 36:52:20 |
|* 9 |
TABLE ACCESS FULL
| PATIENT_VISIT
|
255K| 4736K|
| 1618
(0)| 14:24:50 |
|* 10 |
HASH JOIN RIGHT SEMI
|
|
292K|
9M| 6416K| 22638
(1)|201:39:53 |
|* 11 |
TABLE ACCESS FULL
| LAB_RESULT_FILE_UPLOAD
|
285K| 3068K|
| 8781
(0)| 78:13:30 |
|* 12 |
TABLE ACCESS FULL
| STUDY_INV_PATIENT_VISIT_STAGE | 1545K|
35M|
| 10655
(0)| 94:55:11 |
|* 13 |
TABLE ACCESS BY INDEX ROWID| TEST_RESULT_STAGE
|
5 |
100 |
|
4
(0)| 00:02:09 |
|* 14 |
INDEX RANGE SCAN
| IDX_TRS_PF04
|
6 |
|
|
3
(0)| 00:01:37 |
|* 15 |
TABLE ACCESS BY INDEX ROWID | TEST_ORDER
| 7739K|
73M|
|
2
(0)| 00:01:05 |
|* 16 |
INDEX UNIQUE SCAN
| TEST_ORDER_PK
|
1 |
|
|
1
(0)| 00:00:33 |
-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------4
6
8
9
10
11
12
13
14
-
access("SIPVS"."KIT_BARCODE_ID"="KITS_AVAILABLE"."KIT_BARCODE_ID")
filter(ROWNUM<500)
filter(ROWNUM<500)
filter("FINAL_REPORT_DATE" IS NOT NULL AND "ACTIVE"=10)
access("LRFU"."UPLOAD_ID"="SIPVS"."UPLOAD_ID")
filter("LRFU"."LAB_ID"=10034)
filter("SIPVS"."KIT_BARCODE_ID" IS NOT NULL AND "SIPVS"."ACTIVE"=10)
filter("TRS"."TEST_ORDER_ID" IS NOT NULL)
access("SIPVS"."STDINV_PATIENT_VST_STAGE_NF_ID"="TRS"."STDINV_PATIENT_VST_STAGE_NF_ID" AND
"TRS"."RESULT_STATUS"=4 AND "TRS"."ACTIVE"=10)
filter("TRS"."ACTIVE"=10)
15 - filter("ACTIVE"=10)
16 - access("TOR"."TEST_ORDER_ID"="TRS"."TEST_ORDER_ID")
40 rows selected.
Execution plans - EXPLAIN PLAN FOR – plan w/t execution
+
+
+
+
It retrives executiion plan witout an SQL execution
It shows predicates for each step (access/filer)
It reflects prallel execution and partitioning related infromation automagicly
It gives you some Hints about the execution (Note: cpu costing is off)
- Oracle makes assumptions on bind variables data types (wrong execution plan)
Execution plans - EXPLAIN PLAN FOR – plan w/t execution
DBMS_XPLAN.DISPLAY(
table_name
IN
statement_id IN
format
IN
filter_preds IN
VARCHAR2 DEFAULT 'PLAN_TABLE',
VARCHAR2 DEFAULT NULL,
VARCHAR2 DEFAULT 'TYPICAL',
VARCHAR2 DEFAULT NULL);
format
Controls the level of details for the plan. It accepts four values:
* BASIC: Displays the minimum information in the plan—the
operation ID, the operation name and its option.
* TYPICAL: This is the default. Displays the most relevant
information in the plan (operation id, name and option, #rows,
#bytes and optimizer cost). Pruning, parallel and predicate
information are only displayed when applicable. Excludes only
PROJECTION, ALIAS and REMOTE SQL information (see below).
* SERIAL: Like TYPICAL except that the parallel information is
not displayed, even if the plan executes in parallel.
* ALL: Maximum user level. Includes information displayed with
the TYPICAL level with additional information (PROJECTION, ALIAS
and information about REMOTE SQL if the operation is distributed).
21:50:30 IMTDB:prodds2> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'BASIC'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------Plan hash value: 3739423728
-----------------------------------------------------------------------| Id | Operation
| Name
|
-----------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
|
1 | SORT AGGREGATE
|
|
|
2 |
NESTED LOOPS SEMI
|
|
|
3 |
NESTED LOOPS
|
|
|
4 |
HASH JOIN
|
|
|
5 |
VIEW
|
|
|
6 |
COUNT STOPKEY
|
|
|
7 |
VIEW
|
|
|
8 |
SORT ORDER BY STOPKEY |
|
|
9 |
TABLE ACCESS FULL
| PATIENT_VISIT
|
| 10 |
HASH JOIN RIGHT SEMI
|
|
| 11 |
TABLE ACCESS FULL
| LAB_RESULT_FILE_UPLOAD
|
| 12 |
TABLE ACCESS FULL
| STUDY_INV_PATIENT_VISIT_STAGE |
| 13 |
TABLE ACCESS BY INDEX ROWID| TEST_RESULT_STAGE
|
| 14 |
INDEX RANGE SCAN
| IDX_TRS_PF04
|
| 15 |
TABLE ACCESS BY INDEX ROWID | TEST_ORDER
|
| 16 |
INDEX UNIQUE SCAN
| TEST_ORDER_PK
|
-----------------------------------------------------------------------23 rows selected.
22:27:03 IMTDB:prodds2>
22:29:16 IMTDB:prodds2> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3739423728
-----------------------------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
74 |
| 34309
(1)|305:38:23 |
|
1 | SORT AGGREGATE
|
|
1 |
74 |
|
|
|
|
2 |
NESTED LOOPS SEMI
|
| 2768 |
200K|
| 34309
(1)|305:38:23 |
|
3 |
NESTED LOOPS
|
| 2768 |
173K|
| 28773
(1)|256:19:22 |
|* 4 |
HASH JOIN
|
|
521 | 22924 |
| 26777
(1)|238:32:29 |
|
5 |
VIEW
|
|
499 | 4491 |
| 4139
(1)| 36:52:20 |
|* 6 |
COUNT STOPKEY
|
|
|
|
|
|
|
|
7 |
VIEW
|
|
255K| 2243K|
| 4139
(1)| 36:52:20 |
|* 8 |
SORT ORDER BY STOPKEY |
|
255K| 4736K| 8048K| 4139
(1)| 36:52:20 |
|* 9 |
TABLE ACCESS FULL
| PATIENT_VISIT
|
255K| 4736K|
| 1618
(0)| 14:24:50 |
|* 10 |
HASH JOIN RIGHT SEMI
|
|
292K|
9M| 6416K| 22638
(1)|201:39:53 |
|* 11 |
TABLE ACCESS FULL
| LAB_RESULT_FILE_UPLOAD
|
285K| 3068K|
| 8781
(0)| 78:13:30 |
|* 12 |
TABLE ACCESS FULL
| STUDY_INV_PATIENT_VISIT_STAGE | 1545K|
35M|
| 10655
(0)| 94:55:11 |
|* 13 |
TABLE ACCESS BY INDEX ROWID| TEST_RESULT_STAGE
|
5 |
100 |
|
4
(0)| 00:02:09 |
|* 14 |
INDEX RANGE SCAN
| IDX_TRS_PF04
|
6 |
|
|
3
(0)| 00:01:37 |
|* 15 |
TABLE ACCESS BY INDEX ROWID | TEST_ORDER
| 7739K|
73M|
|
2
(0)| 00:01:05 |
|* 16 |
INDEX UNIQUE SCAN
| TEST_ORDER_PK
|
1 |
|
|
1
(0)| 00:00:33 |
-----------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------1
5
6
7
8
9
11
12
13
14
15
16
-
SEL$F2CA64D4
SEL$2
SEL$2
SEL$3
SEL$3
SEL$3
SEL$F2CA64D4
SEL$F2CA64D4
SEL$F2CA64D4
SEL$F2CA64D4
SEL$F2CA64D4
SEL$F2CA64D4
/ KITS_AVAILABLE@SEL$1
/ from$_subquery$_004@SEL$2
/
/
/
/
/
/
/
PATIENT_VISIT@SEL$3
LRFU@SEL$5
SIPVS@SEL$1
TRS@SEL$1
TRS@SEL$1
TOR@SEL$4
TOR@SEL$4
Predicate Information (identified by operation id):
--------------------------------------------------4
6
8
9
10
11
12
13
14
-
access("SIPVS"."KIT_BARCODE_ID"="KITS_AVAILABLE"."KIT_BARCODE_ID")
filter(ROWNUM<500)
filter(ROWNUM<500)
filter("FINAL_REPORT_DATE" IS NOT NULL AND "ACTIVE"=10)
access("LRFU"."UPLOAD_ID"="SIPVS"."UPLOAD_ID")
filter("LRFU"."LAB_ID"=10034)
filter("SIPVS"."KIT_BARCODE_ID" IS NOT NULL AND "SIPVS"."ACTIVE"=10)
filter("TRS"."TEST_ORDER_ID" IS NOT NULL)
access("SIPVS"."STDINV_PATIENT_VST_STAGE_NF_ID"="TRS"."STDINV_PATIENT_VST_STAGE_NF_ID" AND
"TRS"."RESULT_STATUS"=4 AND "TRS"."ACTIVE"=10)
filter("TRS"."ACTIVE"=10)
15 - filter("ACTIVE"=10)
16 - access("TOR"."TEST_ORDER_ID"="TRS"."TEST_ORDER_ID")
Column Projection Information (identified by operation id):
----------------------------------------------------------1
2
3
4
5
6
7
8
9
-
(#keys=0) COUNT(*)[22]
(#keys=0)
(#keys=0) "TRS"."TEST_ORDER_ID"[NUMBER,22]
(#keys=1) "SIPVS"."STDINV_PATIENT_VST_STAGE_NF_ID"[NUMBER,22]
"KITS_AVAILABLE"."KIT_BARCODE_ID"[VARCHAR2,30]
"KIT_BARCODE_ID"[VARCHAR2,30]
"KIT_BARCODE_ID"[VARCHAR2,30]
(#keys=1) INTERNAL_FUNCTION("FINAL_REPORT_DATE")[7], "KIT_BARCODE_ID"[VARCHAR2,30]
"KIT_BARCODE_ID"[VARCHAR2,30], "FINAL_REPORT_DATE"[DATE,7]
Execution plans - SET AUTOTRACE – might lie !
REF: Jonathan Lewis
http://jonathanlewis.wordpress.com/testing-autotrace/
set autotrace traceonly explain – doens’t execute an SQL
Konrad
> SQL TRACE aka event 10046
> event 10053
> Tanel's Dtrace method
Execution plans - v$sql_plan
Determine the session and cursor
v$session, v$sql:
hash_value, address, child_number (9i)
sql_id, child_number (11g)
Session activity gives clues to what is in the execution plan…
v$session_event: db file sequential read, db file scattered read
v$session_wait:
v$session_longops: Table Scan
Execution plans - v$sql_plan
Actual execution plan (9i)
truncate table plan_table;
-- Following is equivalent to EXPLAIN PLAN FOR <statement>
insert into plan_table (statement_id, timestamp, OPERATION, OPTIONS, OBJECT_NODE,
OBJECT_OWNER, OBJECT_NAME, OPTIMIZER, ID, PARENT_ID, POSITION, SEARCH_COLUMNS,
COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP,
PARTITION_ID, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE,
ACCESS_PREDICATES, FILTER_PREDICATES)
select 0, sysdate, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,
OPTIMIZER, ID, PARENT_ID, POSITION, SEARCH_COLUMNS, 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 hash_value=&hsh and address='&add' and child_number=&chld;
-- Displays the actual plan instead of the predicted plan
select * from table(dbms_xplan.display);
Execution plans - v$sql_plan
Actual execution plan (10g/11g)
-- execution plan for last cursor of session
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
-- execution plan for a cached cursor (need sql_id + child_number from v$sql)
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>',<child#>));
-- execution plan for an AWR cursor (need sql_id from DBA_HIST_SQLTEXT)
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('<sql_id>'));
-- Plan related dynamic views
V$SQL_PLAN
V$SQL_PLAN_MONITOR
Plan level monitoring statistics.
V$SQL_BIND_CAPTURE
Bind variables used for a cursor.
V$SQL_BIND_DATA
As above, except for current session.
Execution plans
11g features:
DBMS_SQLTUNE.REPORT_SQL_MONITOR();
Create a report that can include explain plan and binds.
SQL Plan Baselines
Storage of past plans that are considered efficient.
Execution plans - SQLT (SQLTXPLAIN) tool from Oracle
Support
DEMO
SQLT (SQLTXPLAIN) –
Tool that helps to diagnose SQL statements performing poorly
[ID 215187.1]
+ It is most complete and comprehansive option
+ Support 9.2.0.1 to 11.2.0.1 RDBMS versions
+ Can use many different sources as input
- Needs additional instaltion in the production DB
+ Install in totaly isolated schema (SQLTXPLAIN)
- A report generating process is relativly complex
(as application owner with additional privileges, multiple stages)
- Sometimes generating process is resources intensive