dbms plan - a swiss army knife for performance engineers
DESCRIPTION
A deep discussion about dbms_xplan and usage.TRANSCRIPT
![Page 1: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/1.jpg)
dbms_xplan: A Swiss army knife for performance engineers
Sahil Thapar
Riyaj Shamsudeen
![Page 2: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/2.jpg)
2
Disclaimer
These slides and materials represent the work and opinions of the author and do not constitute official positions of my current or past employer or any other organization. This material has been peer reviewed, but author assume no responsibility whatsoever for the test cases.
If you corrupt your databases by running my scripts, you are solely responsible for that.
![Page 3: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/3.jpg)
3
Options
EXPLAIN PLAN (Version 7 or older)
AUTOTRACE (Version 8i +)
SQL_TRACE (Version 7)
DBMS_XPLAN
Estimated, not always reliable
Actual plan; but must enable trace before the run.
Actual plan, after the run, always available.
Actual plan, buggy.
![Page 4: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/4.jpg)
PARSING IN CURSOR #1844674 len=1000 dep=0 uid=173 oct=3 lid=173 tim=7861072491388 hv=2701626457 ad='498d8f1d0' sqlid='9sup9gfhhg32t'SELECT RECID, TABLENAME, PROJECT_NUMBER, PROJECT_NAME, CARRYING_OUT_ORGANIZATION, ...FROM ( SELECT STGTBL_RECID...WHERE PROCESSED_FLAG='U' ) ORDER BY TABLENAME, CREATION_DATEPARSE:#1844674:c=10000,e=10162,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3298327531,tim=7861072491386EXEC:#1844674:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3298327531,tim=7861072491723EXEC:#1844674:c=0,e=139,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3298327531,tim=7861072491723..WAIT #1844674: nam='Disk file operations I/O' ela= 2013 FileOperation=2 fileno=811 filetype=2 obj#=706379 tim=786107FETCH#1844674:c=18020000,e=59603730,p=199262,cr=199331,cu=0,mis=0,r=0,dep=0,og=1,plh=3298327531,tim=7861…STAT #1844674 id=3 cnt=0 pid=2 pos=1 obj=0 op='SORT UNIQUE (cr=199331 pr=199262 pw=0 time=59603707 us cost=56817 size=126622640 card=2146146)'STAT #1844674 id=4 cnt=0 pid=3 pos=1 obj=0 op='UNION-ALL (cr=199331 pr=199262 pw=0 time=59603638 us)'
4
Raw trace fileContent of a sql trace file:
![Page 5: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/5.jpg)
5
tkprof (1)
SELECT RECID, TABLENAME, PROJECT_NUMBER, PROJECT_NAME, CARRYING_OUT_ORGANIZATION, COMPANY,CHARGEABLE_FLAG, START_DATE, END_DATE, CREATION_DATE, LAST_UPDATE_DATE, PROCESSED_FLAG,……………………………….……………………………..WHERE PROCESSED_FLAG='U' ) ORDER BY TABLENAME, CREATION_DATE
call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.01 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 18.02 59.60 199262 199331 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 18.03 59.61 199262 199331 0 0
![Page 6: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/6.jpg)
6
tkprof (2)
Rows (max) Row Source Operation---------- --------------------------------------------------- 0 SORT ORDER BY (cr=199331 pr=199262 pw=0 time=59603767 us cost=180933 size=641697654 card=2146146) 0 VIEW (cr=199331 pr=199262 pw=0 time=59603722 us cost=56817 size=641697654 card=2146146) 0 SORT UNIQUE (cr=199331 pr=199262 pw=0 time=59603707 us cost=56817 size=126622640 card=2146146) 0 UNION-ALL (cr=199331 pr=199262 pw=0 time=59603638 us) 0 TABLE ACCESS FULL ORD_AP_IF_PROJECTS (cr=46025 pr=45963 pw=0 time=17088312 us cost=6568 size=85 card=1) 0 TABLE ACCESS FULL ORD_AP_IF_TASKS (cr=153306 pr=153299 pw=0 time=42515291 us cost=21894 size=126622555
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 1 0.00 0.00 Disk file operations I/O 129 0.01 0.26
![Page 7: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/7.jpg)
7
Usage Displays execution plan stored in three different
places
1) PLAN_TABLE dbms_xplan.diplay (estimated plan)
2) SGA of instance dbms_xplan.display_cursor (real plan from
memory)
3) AWR dbms_xplan.display_awr (real,history plan from AWR)
![Page 8: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/8.jpg)
8
Comparison
Sl Tkprof Dbms_xplan
1 Trace must be enabled. Basic statistics are available in memory and AWR
2 CBO notes are not visible Note section is very useful
3 No predicate Predicates printed from memory.
4 Bind values can be retrieved with ease
5 Plan history can be retrieved easily.
![Page 9: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/9.jpg)
9
dbms_xplan format arguments
Class level alias bytes cost note predicate parallel partition projection outlines peeked_binds remote rows
BASIC
null X X X X X
TYPICAL X X X X X X X X
SERIAL X X X X X X
ALL X X X X X X X X X X X
ADVANCED X X X X X X X X X X X X
Formatselect * from table(dbms_xplan.display_cursor(‘sql_id’,child_number,’format’));
Examples: select * from table( dbms_xplan.display_cursor()); select * from table ( dbms_xplan.display_cursor (null,null, 'ADVANCED')); select * from table ( dbms_xplan.display_cursor ('811a0zb26fw0x',0, 'BASIC')); select * from table ( dbms_xplan.display_cursor ('811a0zb26fw0x',0, ‘ALL -projection')); select * from table ( dbms_xplan.display_cursor ('811a0zb26fw0x',0, ‘ALL +peeked_binds'));
Format arguments:' ADVANCED'' –PREDICATE' ' –PREDICATE +OUTLINE'
Format arguments combination:TYPICAL is the default class
![Page 10: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/10.jpg)
SQL_ID 9sup9gfhhg32t, child number 0-------------------------------------SELECT RECID, TABLENAME, PROJECT_NUMBER, PROJECT_NAME,……………………………………………………………………………………………LEDIT_CLASS_CODE, ALC, FRC FROM CCW_PA_IF_TASKS WHERE PROCESSED_FLAG='U') ORDER BY TABLENAME, CREATION_DATE
Plan hash value: 3298327531---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | | 180K(100)| || 1 | SORT ORDER BY | | 2146K| 611M| 698M| 180K (5)| 00:09:03 || 2 | VIEW | | 2146K| 611M| | 56817 (10)| 00:02:51 || 3 | SORT UNIQUE | | 2146K| 120M| 172M| 56817 (91)| 00:02:51 || 4 | UNION-ALL | | | | | | ||* 5 | TABLE ACCESS FULL| ORD_AP_IF_PROJECTS | 1 | 85 | | 6568 (14)| 00:00:20 ||* 6 | TABLE ACCESS FULL| ORD_AP_IF_TASKS | 2146K| 120M| | 21894 (14)| 00:01:06 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
5 - filter("PROCESSED_FLAG"='U') 6 - filter("PROCESSED_FLAG"='U')
Note----- - SQL profile not used for this statement
10
dbms_xplan.display
Optimizer estimates
![Page 11: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/11.jpg)
11
Display_cursor
select * from table(dbms_xplan.display_cursor('9yx90c0nwv9tk',0,'allstats'));
SQL_ID 9yx90c0nwv9tk, child number 0-------------------------------------SELECT /* gather_plan_statistics */ RECID, TABLENAME,...
Plan hash value: 3298327531---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:32.50 | 199K| 199K|| 1 | SORT ORDER BY | | 1 | 2146K| 0 |00:00:32.50 | 199K| 199K|| 2 | VIEW | | 1 | 2146K| 0 |00:00:32.50 | 199K| 199K|| 3 | SORT UNIQUE | | 1 | 2146K| 0 |00:00:32.50 | 199K| 199K|| 4 | UNION-ALL | | 1 | | 0 |00:00:32.50 | 199K| 199K||* 5 | TABLE ACCESS FULL| ORD_AP_IF_PROJECTS | 1 | 1 | 0 |00:00:07.90 | 46025 | 45963 ||* 6 | TABLE ACCESS FULL| ORD_AP_IF_TASKS | 1 | 2146K| 0 |00:00:24.60 | 153K| 153K|---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):--------------------------------------------------- 5 - filter("PROCESSED_FLAG"='U') 6 - filter("PROCESSED_FLAG"='U')
Note, output formatted to fit screen
![Page 12: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/12.jpg)
12
Easily quantify the solution given..
select * from table(dbms_xplan.display_cursor('9yx90c0nwv9tk',0,'allstats'));
Plan hash value: 932773089
----------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 6 | | | || 1 | SORT ORDER BY | | 1 | 2146K| 0 |00:00:00.01 | 6 | 1024 | 1024 | || 2 | VIEW | | 1 | 2146K| 0 |00:00:00.01 | 6 | | | || 3 | SORT UNIQUE | | 1 | 2146K| 0 |00:00:00.01 | 6 | 1024 | 1024 | || 4 | UNION-ALL | | 1 | | 0 |00:00:00.01 | 6 | | | || 5 | TABLE ACCESS BY INDEX ROWID| ORD_PA_IF_PROJECTS | 1 | 1 | 0 |00:00:00.01 | 3 | | | ||* 6 | INDEX RANGE SCAN | ORD_PA_IF_PROJECTS_N1 | 1 | 1 | 0 |00:00:00.01 | 3 | | | || 7 | TABLE ACCESS BY INDEX ROWID| ORD_PA_IF_TASKS | 1 | 2146K| 0 |00:00:00.01 | 3 | | | ||* 8 | INDEX RANGE SCAN | ORD_PA_IF_TASKS_N1 | 1 | 2173K| 0 |00:00:00.01 | 3 | | | |----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
6 - access("PROCESSED_FLAG"='U') 8 - access("PROCESSED_FLAG"='U')
Note-----
![Page 13: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/13.jpg)
13
Privileges
For dbms_xplan.display_awrgrant select on dba_hist_sqltext to scottgrant select on dba_hist_sql_plan to scott;grant select on v_$database to scott;ORSELECT_CATALOG role
Privileges needed to execute dbms_xplan
For dbms_xplan.display_cursorgrant select on v_$session to scott;grant select on v_$SQL_PLAN_STATISTICS_ALL to scott;grant select on v_$sql to scott;grant select on v_$sql_plan to scott;ORSELECT_CATALOG role or select any dictionary system privilege
![Page 14: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/14.jpg)
14
Internals of dbms_xplan
select * from (table(dbms_xplan.display_cursor('8gzkcsvw362s3',2,'allstats'));
select /* EXEC_FROM_DBMS_XPLAN */ case when upper(sql_text) like '%DBMS_XPLAN%' then 0 else 1 end case, SQL_ID, child_numberfrom v$sql where SQL_ID ='8gzkcsvw362s3' and child_number =2
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval, '/*/info[@type = "sql_patch"]'), extractvalue(xmlval, '/*/info[@type = "baseline"]'), extractvalue(xmlval, '/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type = "dop"]'), extractvalue(xmlval, '/*/info[@type = "dop_reason"]'), extractvalue(xmlval, '/*/info[@type = "queuing_reason"]'), extractvalue(xmlval, '/*/info[@type = "row_shipping"]') , extractvalue(xmlval, '/*/info[@type = "index_size"]'), extractvalue(xmlval, '/*/info[@type = "result_checksum"]'), extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]'), extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]'), extractvalue(xmlval,'/*/info[@type = "plan_hash"]')from (select xmltype(:v_other_xml) xmlval from dual)
SELECT PLAN_TABLE_OUTPUTFROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 ) AS SYS.DBMS_XPLAN_TYPE_TABLE))
Really complicated recursive SQL statements!
![Page 15: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/15.jpg)
15
More internals
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ /* EXEC_FROM_DBMS_XPLAN */ id, position, depth , operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost, time,….
vp.optimal_executions opt_cnt, vp.onepass_executions one_cnt, vp.multipasses_executions multi_cnt, vp.max_tempseg_size max_tmp, vp.last_tempseg_size last_tmpfrom V$SQL_PLAN_STATISTICS_ALL vp, v$sql where vp.SQL_ID = '8gzkcsvw362s3' and vp.child_number=2 and vp.SQL_ID = v$sql.SQL_ID and v$sql.child_number=2) order by id
![Page 16: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/16.jpg)
16
How do I tune?
my_sql.sqlSet linesize 300;set pagesize 1000;--Declare variable and assign valuesvar order_id number;execute :order_id:=53759119SELECT oeh.header_id, oel.line_id FROM oe_order_headers_all oeh, oe_order_lines_all oel WHERE oeh.header_id = :order_id AND oeh.header_id = oel.header_id/select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'))/
Prepare the sql to be run for performance analysis.
![Page 17: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/17.jpg)
17
Do’s & Don’ts Do’s
1. Current execution plan and runtime statistics are the first step.
2. Execute a test case first to reproduce.
Don’t
1. Try to use same bind type as application uses.
- Literal vs bind variables
- number vs varchar2 data type etc
2. Try to use format same as the application for your test case.
- CTAS vs INSERT
![Page 18: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/18.jpg)
PLAN_TABLE_OUTPUTSQL_ID cttafpfjwqjdm, child number 0-------------------------------------select oeh.header_id,oel.line_id from oe_order_headers_all oeh,oe_order_lines_all oel where oeh.header_id=:order_id and oeh.header_id=oel.header_id
Plan hash value: 4180984038
---------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 11 (100)| | | || 1 | NESTED LOOPS | | 6 | 114 | 11 (0)| 00:00:01 | | || 2 | PARTITION HASH SINGLE | | 1 | 6 | 2 (0)| 00:00:01 | KEY | KEY ||* 3 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | 6 | 2 (0)| 00:00:01 | KEY | KEY || 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| OE_ORDER_LINES_ALL | 6 | 78 | 9 (0)| 00:00:01 | ROWID | ROWID ||* 5 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 6 | | 3 (0)| 00:00:01 | | |---------------------------------------------------------------------------------------------------------------------------
18
dbms_xplan.display_cursor
![Page 19: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/19.jpg)
...
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("OEH"."HEADER_ID"=:ORDER_ID) 5 - access("OEL"."HEADER_ID"=:ORDER_ID)
Note----- - Warning These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level * Cardinality feedback used * Dynamic sampling used
19
dbms_xplan.display_cursor..2
![Page 20: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/20.jpg)
--------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 11200 (100)| | | || 1 | NESTED LOOPS | | 6 | 114 | 11 (0)| 00:10:01 | | || 2 | PARTITION HASH SINGLE | | 1 | 6 | 2 (0)| 00:00:01 | KEY | KEY ||* 3 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | 6 | 2 (0)| 00:00:01 | KEY | KEY ||* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| OE_ORDER_LINES_ALL | 6 | 78 | 9000 (0)| 00:07:01 | ROWID | ROWID ||* 5 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 60000 | | 300 (0)| 00:03:01 | | |---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("OEH"."HEADER_ID"=:ORDER_ID) 4 – filter ("OEL".“OPEN_FLAG=’Y’) 5 - access("OEL"."HEADER_ID"=:ORDER_ID)
20
dbms_xplan: access and filter predicatesAdded a predicate oel.open_flag=‘Y’
![Page 21: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/21.jpg)
21
Detailed execution statistics v$sql_plan_statistics_all collects row source level
execution statistics ROWS CPU Physical IO Logical IO Elapsed time
To enable at session level set statistics_level=‘ALL’
To enable for all sessions set at system level statistics_level=‘ALL’
To set a SQL statement level add the hint /*+ gather_plan_statistics */ in the
statement.
Setting statistics_level=‘ALL’ at system level can be resource intensive and should preferably not be enabled in busy PRODUCTION SYSTEM
![Page 22: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/22.jpg)
22
dbms_xplan: script
--my_sql.sql script_nameset linesize 300;set pagesize 1000;--Declare variable and assign valuesvar order_id number;execute order_id:=53759119alter session set statistics_level=‘ALL’; --OR ADD HINTSELECT /*+ gather_plan_statistics */ oeh.header_id, oel.line_idFROM oe_order_headers_all oeh, oe_order_lines_all oelWHERE oeh.header_id = :order_id AND oeh.header_id = oel.header_id/
Modify the script to enable extended execution statistics
![Page 23: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/23.jpg)
23
Execution statistics
SQL_ID 7gftgf917tg2x, child number 0-------------------------------------select /*+ gather_plan_statistics */ oeh.header_id,oel.line_id fromoe_order_headers_all oeh,oe_order_lines_all oel whereoeh.header_id=:order_id and oeh.header_id=oel.header_id
Plan hash value: 4180984038
------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.03 | 12 | 11 || 1 | NESTED LOOPS | | 1 | 6 | 4 |00:00:00.03 | 12 | 11 || 2 | PARTITION HASH SINGLE | | 1 | 1 | 1 |00:00:00.01 | 3 | 3 ||* 3 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | 1 | 1 |00:00:00.01 | 3 | 3 || 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| OE_ORDER_LINES_ALL | 1 | 6 | 4 |00:00:00.02 | 9 | 8 ||* 5 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 1 | 6 | 4 |00:00:00.01 | 5 | 4 |------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
3 - access("OEH"."HEADER_ID"=:ORDER_ID) 5 - access("OEL"."HEADER_ID"=:ORDER_ID)
25 rows selected.
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'))/
![Page 24: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/24.jpg)
24
Alias
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST +alias -predicate'));
SELECT /*+ gather_plan_statistics */ oel.line_id FROMoe_order_lines_all oel WHERE oel.open_flag = 'Y' andexists (select 1 from oe_order_headers_all oeh whereoeh.header_id = oel.header_id and oeh.open_flag='Y' and oeh.last_update_date > sysdate -5)
Plan hash value: 746840967--------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 16 | | | || 1 | NESTED LOOPS | | 1 | | 3 |00:00:00.01 | 16 | | | || 2 | NESTED LOOPS | | 1 | 1 | 3 |00:00:00.01 | 10 | | | || 3 | SORT UNIQUE | | 1 | 1 | 1 |00:00:00.01 | 5 | 2048 | 2048 | 2048 (0)|| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| OE_ORDER_HEADERS_ALL | 1 | 1 | 1 |00:00:00.01 | 5 | | | || 5 | INDEX RANGE SCAN | CCW_OE_ORD_HEADERS_ALL_D1 | 1 | 7 | 1 |00:00:00.01 | 4 | | | || 6 | INDEX RANGE SCAN | OE_ORDER_LINES_N1 | 1 | 6 | 3 |00:00:00.01 | 5 | | | || 7 | TABLE ACCESS BY GLOBAL INDEX ROWID | OE_ORDER_LINES_ALL | 3 | 1 | 3 |00:00:00.01 | 6 | | | |--------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$5DA710D3 4 - SEL$5DA710D3 / OEH@SEL$2 5 - SEL$5DA710D3 / OEH@SEL$2 6 - SEL$5DA710D3 / OEL@SEL$1 7 - SEL$5DA710D3 / OEL@SEL$1
![Page 25: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/25.jpg)
SQL_ID cu2f3xhzy75vc, child number 0-------------------------------------
SELECT /*+ gather_plan_statistics qb_name(outer_block) */ oel.line_id FROM oe_order_lines_all oel WHERE oel.open_flag = 'Y' and exists (select /*+ qb_name(innner_block) */ 1 from oe_order_headers_all oeh where oeh.header_id = oel.header_id and oeh.open_flag='Y' and oeh.last_update_date > sysdate -5);
Plan hash value: 746840967--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | ..|--------------------------------------------------------------------------------------------------------------------..| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 |
…
Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$8E94A21F 4 - SEL$8E94A21F / OEH@INNNER_BLOCK 5 - SEL$8E94A21F / OEH@INNNER_BLOCK 6 - SEL$8E94A21F / OEL@OUTER_BLOCK 7 - SEL$8E94A21F / OEL@OUTER_BLOCK
25
Query Block name
![Page 26: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/26.jpg)
26
Default QB Name
Prefix Row Source operation
CRI$ Create index statement
DEL$ DELETE statement
INS$ Insert Statement
MISC$ Miesc SQL like lock table
MRG$ Merge Statement
SEL$ SELECT statement
SET$ Set operators
UPD$ Update statement
![Page 27: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/27.jpg)
27
Hints or Outlines
set linesize 300set pagesize 300alter session set statistics_level='ALL'; SELECT /* outline query */ RECID, ALC, FRC...FROM ORD_AP_IF_TASK WHERE PROCESSED_FLAG = 'U')ORDER BY TABLENAME, CREATION_DATE;
select * from table( dbms_xplan.display_cursor
(null,null,'ALLSTATS LAST +outline'))/
![Page 28: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/28.jpg)
Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$3" "ORD_AP_IF_TASKS"@"SEL$3") INDEX_RS_ASC(@"SEL$2" "ORD_AP_IF_PROJECTS"@"SEL$2“ ("ORD_AP_IF_PROJECTS"."PROCESSED_FLAG")) END_OUTLINE_DATA */
28
Outline---------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | …-------------------------------------------------------------------------------------------------------------------…| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:27.40 | 153K| ..| 1 | SORT ORDER BY | | 1 | 2146K| 0 |00:00:27.40 | 153K| …| 2 | VIEW | | 1 | 2146K| 0 |00:00:27.40 | 153K| …| 3 | SORT UNIQUE | | 1 | 2146K| 0 |00:00:27.40 | 153K| …| 4 | UNION-ALL | | 1 | | 0 |00:00:27.40 | 153K| …| 5 | TABLE ACCESS BY INDEX ROWID| ORD_AP_IF_PROJECTS | 1 | 1 | 0 |00:00:00.01 | 3 | …| 6 | INDEX RANGE SCAN | ORD_AP_IF_PROJECTS_N1 | 1 | 1 | 0 |00:00:00.01 | 3 || 7 | TABLE ACCESS FULL | ORD_AP_IF_TASKS | 1 | 2146K| 0 |00:00:27.40 | 153K|-------------------------------------------------------------------------------------------------------------------
![Page 29: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/29.jpg)
Predicate Information (identified by operation id):---------------------------------------------------
6 - access("PROCESSED_FLAG"='U') 7 - filter("PROCESSED_FLAG"='U')
Outline Data------------- /*+... FULL(@"SEL$3" "ORD_AP_IF_TASKS"@"SEL$3") INDEX_RS_ASC(@"SEL$2" "ORD_AP_IF_PROJECTS "@"SEL$2" ("ORD_AP_IF_PROJECTS"."PROCESSED_FLAG")) END_OUTLINE_DATA */
29
Tuning SQL
SQLPLUS> select count(1) from ORD_AP_IF_TASKS where PROCESSED_FLAG='U'; COUNT(1)---------- 0
There is an index on ORD_AP_IF_TASKS.PROCESSED_FLAG
![Page 30: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/30.jpg)
set linesize 300set pagesize 300alter session set statistics_level='ALL'; SELECT /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.2') DB_VERSION('11.2.0.2') OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$2") OUTLINE_LEAF(@"SEL$3") OUTLINE_LEAF(@"SET$1") OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") INDEX_RS_ASC(@"SEL$3" “ORD_AP_IF_TASKS"@"SEL$3" (“ORD_AP_IF_TASKS"."PROCESSED_FLAG")) INDEX_RS_ASC(@"SEL$2" “ORD_AP_IF_PROJECTS"@"SEL$2" (“ORD_AP_IF_PROJECTS"."PROCESSED_FLAG")) END_OUTLINE_DATA */ WHERE PROCESSED_FLAG = 'U')ORDER BY TABLENAME, CREATION_DATE/
select * from table(dbms_xplan.display_cursor
(null,null,'ALLSTATS LAST +outline'))/
30
Bob the Builder
FULL(@"SEL$3" "ORD_AP_IF_TASKS"@"SEL$3")
Copying outline as hint; Altering Full Table Scan
to Index Range Scan.
![Page 31: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/31.jpg)
31
Tuned Plan---------------------------------------------------------------------------------------------------------…| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 || 1 | SORT ORDER BY | | 1 | 2146K| 0 |00:00:00.01 || 2 | VIEW | | 1 | 2146K| 0 |00:00:00.01 || 3 | SORT UNIQUE | | 1 | 2146K| 0 |00:00:00.01 || 4 | UNION-ALL | | 1 | | 0 |00:00:00.01 || 5 | TABLE ACCESS BY INDEX ROWID| ORD_AP_IF_PROJECTS | 1 | 1 | 0 |00:00:00.01 ||* 6 | INDEX RANGE SCAN | ORD_AP_IF_PROJECTS_N1 | 1 | 1 | 0 |00:00:00.01 || 7 | TABLE ACCESS BY INDEX ROWID| ORD_AP_IF_TASKS | 1 | 2146K| 0 |00:00:00.01 ||* 8 | INDEX RANGE SCAN | ORD_AP_IF_TASKS_N1 | 1 | 2173K| 0 |00:00:00.01 |---------------------------------------------------------------------------------------------------------…Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPT_PARAM('_b_tree_bitmap_plans' 'false') OPT_PARAM('_fast_full_scan_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1") INDEX_RS_ASC(@"SEL$3" “ORD_AP_IF_TASKS"@"SEL$3" (“ORD_A__IF_TASKS"."PROCESSED_FLAG")) INDEX_RS_ASC(@"SEL$2" “ORD_AP_IF_PROJECTS"@"SEL$2" (“ORD_AP_IF_PROJECTS"."PROCESSED_FLAG")) END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 6 - access("PROCESSED_FLAG"='U') 8 - access("PROCESSED_FLAG"='U’)
![Page 32: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/32.jpg)
32
So many details..So little space..Column DescriptionAlways available columns
idIdentified of each step in the execution plan; if prefixed with * then predicate information available in predicate section
Operation Row source operationName The database object in the operationCBO parse time information
RowsEstimated number of rows returned by the row source operation; derived from statistics on names object in the operation
Bytes Estimated amount of data returned by the operationTempSpc The estimated amount of temp space usage by the operationCost(%CPU)
The estimated cost of the row source operation. The percentage CPU cost is given in () for individual row source operation
Time Estimated amount of time needed to execute that operation in HH:MM:SSPartitioningPstart Number of first partition to be accessedPstop The number of last partition to be accessedParallel ProcessingTQ Table queue used for processingIN-OUT The relation between parallel operationsPQ Distrib The distribution used by producers to send data to consumersRuntime StatisticsStarts The number of times operation was executed (check more nested loop joins operation)A-Rows Actual number of rows returnedA-Time Actual amount of time spent executing the operationI/O Statistics Buffers Logical reads operations performed during execution of each stepRead Physical reads performed for each operationWrite Physical writes performed
![Page 33: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/33.jpg)
33
dbms_xplan.display_awr
Formatselect * from table(dbms_xplan.display_awr(‘sql_id’,plan_hash_value,db_id,’format’));
-plan_hash_value: to select a specific plan -dbid : specifies which database to select from
Displays plans stored in AWR;
Example script:SELECT * FROM TABLE( dbms_xplan.display_awr('&&sql_id', '&plan_hash_value',
NULL, 'ALL +peeked_binds') );
![Page 34: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/34.jpg)
34
dbms_xplan.display_awr
SQL_ID 4jtbgawt37mcd--------------------Plan hash value: 327243540
---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------
1 - :B3 (NUMBER): 7394 2 - :B2 (VARCHAR2(30), CSID=871): 'AAL' 3 - :B1 (VARCHAR2(30), CSID=871): '73057'
SQL_ID 4jtbgawt37mcd--------------------Plan hash value: 2294473928---------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):-------------------------------------- 1 - :B3 (NUMBER): 9649 2 - :B2 (VARCHAR2(30), CSID=871): 'ZXU' 3 - :B1 (VARCHAR2(30), CSID=871): 'U5152'
select * from table(dbms_xplan.display_awr('4jtbgawt37mcd',null, null, 'ALL +peeked_binds'));
![Page 35: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/35.jpg)
©OraInternals Riyaj Shamsudeen 35
Cardinality feedback
Demo @demo_oh1.sql
select /*+ gather_plan_statistics */ max(length(v1)) fromorder_headers_all where processed_flag='N'
Plan hash value: 872302569
---------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 || 1 | SORT AGGREGATE | | 1 | 1 | 1 ||* 2 | TABLE ACCESS FULL| ORDER_HEADERS_ALL | 1 | 50000 | 10 |---------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - filter("PROCESSED_FLAG"='N')
![Page 36: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/36.jpg)
©OraInternals Riyaj Shamsudeen 36
Starts
Demo @demo_oh2.sql
SELECT /*+ no_rewrite gather_plan_statistics */ MAX(LENGTH(oh.v1)), MAX(LENGTH(oh.v1))FROM order_headers_all ohWHERE EXISTS ( SELECT 1 FROM order_lines_all ol WHERE oh.order_id = ol.order_id AND line_processed_flag = 'Y' AND rownum >=1 )AND oh.processed_flag='N' ;
How many times did the EXISTS Subquery was executed?
![Page 37: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/37.jpg)
©OraInternals Riyaj Shamsudeen 37
ALLSTATS LAST
Demo @demo_oh2.sql
----------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 || 1 | SORT AGGREGATE | | 1 | 1 | 1 ||* 2 | FILTER | | 1 | | 10 ||* 3 | TABLE ACCESS FULL | ORDER_HEADERS_ALL | 1 | 50000 | 10 || 4 | COUNT | | 10 | | 10 ||* 5 | FILTER | | 10 | | 10 ||* 6 | TABLE ACCESS BY INDEX ROWID| ORDER_LINES_ALL | 10 | 1 | 10 ||* 7 | INDEX RANGE SCAN | OL_N1 | 10 | 1 | 10 |----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - filter( IS NOT NULL) 3 - filter("OH"."PROCESSED_FLAG"='N') 5 - filter(ROWNUM>=1) 6 - filter("LINE_PROCESSED_FLAG"='Y') 7 - access("OL"."ORDER_ID"=:B1)
select * from table (dbms_xplan.display_cursor ('', '','ALLSTATS LAST'))
![Page 38: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/38.jpg)
©OraInternals Riyaj Shamsudeen 38
Starts..2
SELECT 1 FROM PA_TASKS WHERE TASK_ID = :B1
Plan hash value: 357090087
--------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |--------------------------------------------------------------------| 0 | SELECT STATEMENT | | 256K| | 246K||* 1 | INDEX UNIQUE SCAN| PA_TASKS_U1 | 256K| 1 | 246K|--------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
1 - access("TASK_ID"=:B1)
select * from table (dbms_xplan.display_cursor ('', '','ALLSTATS'))
How many times did we execute this statement?
![Page 39: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/39.jpg)
©OraInternals Riyaj Shamsudeen 39
Cardinality feedback..2
------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7822 | | 0 || 1 | UPDATE | ENI_OLTP_ITEM_STAR | 7822 | | 0 ||* 2 | FILTER | | 7824 | | 7254K||* 3 | TABLE ACCESS BY ROWID RANGE| ENI_OLTP_ITEM_STAR | 7823 | 69121 | 7256K|| 4 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 7171K| 1 | 7167K||* 5 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 7176K| 1 | 7172K|------------------------------------------------------------------------------------------
select * from table (dbms_xplan.display_cursor ('', '','ALLSTATS'))
![Page 40: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/40.jpg)
©OraInternals Riyaj Shamsudeen 40
Cardinality feedback..3
-------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | A-Time | Buffers | Reads |-------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 7822 |00:47:24.86 | 56M| 1082K|| 1 | UPDATE | ENI_OLTP_ITEM_STAR | 7822 |00:47:24.86 | 56M| 1082K||* 2 | FILTER | | 7824 |00:01:27.46 | 23M| 868 ||* 3 | TABLE ACCESS BY ROWID RANGE| ENI_OLTP_ITEM_STAR | 7823 |00:01:23.30 | 23M| 868 || 4 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 7171K|00:41:09.13 | 25M| 1028K||* 5 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 7176K|00:01:17.74 | 17M| 488 |-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - filter(CHARTOROWID(:B2)<=CHARTOROWID(:B1)) 3 - access(ROWID>=CHARTOROWID(:B2) AND ROWID<=CHARTOROWID(:B1)) 5 - access("MTI"."INVENTORY_ITEM_ID"=:B1 AND "MTI"."ORGANIZATION_ID"=:B2)
select * from table (dbms_xplan.display_cursor ('', '','ALLSTATS'))
![Page 41: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/41.jpg)
©OraInternals Riyaj Shamsudeen 41
SQL - remote
Demo @demo_oh3.sql
WITH rem1 AS (SELECT /*+ materialize */ order_id FROM oli_table@orcl2 ol WHERE line_processed_flag='Y' )SELECT /*+ no_rewrite gather_plan_statistics */ MAX(LENGTH(oh.v1)) FROM order_headers_all ohWHERE EXISTS (SELECT 1 FROM rem1 WHERE oh.order_id = rem1.order_id )AND oh.processed_flag='N' ;
![Page 42: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/42.jpg)
©OraInternals Riyaj Shamsudeen 42
Remote queries
Demo @demo_oh3.sql
---------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 || 1 | TEMP TABLE TRANSFORMATION | | 1 | | 1 || 2 | LOAD AS SELECT | | 1 | | 0 || 3 | REMOTE | OLI_TABLE | 1 | 100K| 100K|| 4 | SORT AGGREGATE | | 1 | 1 | 1 ||* 5 | HASH JOIN RIGHT SEMI | | 1 | 50000 | 10 || 6 | VIEW | | 1 | 100K| 100K|| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9FC884_E8A367F2 | 1 | 100K| 100K||* 8 | TABLE ACCESS FULL | ORDER_HEADERS_ALL | 1 | 50000 | 10 |---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
5 - access("OH"."ORDER_ID"="REM1"."ORDER_ID") 8 - filter("OH"."PROCESSED_FLAG"='N')
…Remote SQL Information (identified by operation id):----------------------------------------------------
3 - SELECT /*+ */ "ORDER_ID","LINE_PROCESSED_FLAG" FROM "OLI_TABLE" "OL" WHERE LINE_PROCESSED_FLAG"='Y' (accessing ‘ORCL2.ORACLE.com' )
ADVANCED option provides remote query output
![Page 43: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/43.jpg)
©OraInternals Riyaj Shamsudeen 43
SQL – remote ..2
Demo @demo_oh3.sql
SELECT /*+ no_rewrite gather_plan_statistics use_nl (oh ol) */ MAX(LENGTH(oh.v1))FROM order_headers_all oh, (SELECT order_id FROM oli_table@orcl2 ol WHERE line_processed_flag='Y' ) olWHERE oh.order_id = ol.order_id AND oh.processed_flag='N' ;
![Page 44: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/44.jpg)
©OraInternals Riyaj Shamsudeen 44
Remote queries (2)
Demo @demo_oh5.sql
------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|IN-OUT|-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 100K(100)| || 1 | SORT AGGREGATE | | 1 | 223 | | || 2 | NESTED LOOPS | | 50000 | 10M| 100K (1)| ||* 3 | TABLE ACCESS FULL| ORDER_HEADERS_ALL | 50000 | 9M| 403 (7)| || 4 | REMOTE | OLI_TABLE | 1 | 15 | 2 (0)| R->S |-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("OH"."PROCESSED_FLAG"='N')
Remote SQL Information (identified by operation id):----------------------------------------------------
4 - SELECT /*+ USE_NL ("OL") */ "ORDER_ID", "LINE_PROCESSED_FLAG" FROM "OLI_TABLE" "OL" WHERE "LINE_PROCESSED_FLAG"='Y' AND :1="ORDER_ID“ (accessing ‘ORCL2.ORCL.COM' )
![Page 45: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/45.jpg)
©OraInternals Riyaj Shamsudeen 45
Parallel Queries----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:08.83 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:08.83 ||* 2 | HASH JOIN | | 1 | 999K| 260K|00:00:08.76 || 3 | VIEW | | 1 | 999K| 999K|00:00:02.11 ||* 4 | COUNT STOPKEY | | 1 | | 999K|00:00:01.52 || 5 | PX COORDINATOR | | 1 | | 999K|00:00:00.84 || 6 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 11M| 0 |00:00:00.01 ||* 7 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 || 8 | PX PARTITION HASH ALL| | 0 | 11M| 0 |00:00:00.01 || 9 | INDEX FULL SCAN | OE_ORDER_HEADERS_U1 | 0 | 11M| 0 |00:00:00.01 || 10 | VIEW | | 1 | 999K| 999K|00:00:02.28 ||* 11 | COUNT STOPKEY | | 1 | | 999K|00:00:01.68 || 12 | INDEX FULL SCAN | OE_ORDER_LINES_N1 | 1 | 57M| 999K|00:00:00.98 |----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OH"."HEADER_ID"="OL"."HEADER_ID") 4 - filter(ROWNUM<1000000) 7 - filter(ROWNUM<1000000) 11 - filter(ROWNUM<1000000)
Note-----
- Degree of Parallelism is 8 because of hint
ALLSTATS LAST
Also, notice the broken cardinalityFeedback.
![Page 46: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/46.jpg)
©OraInternals Riyaj Shamsudeen 46
Parallel Queries---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:08.83 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:08.83 ||* 2 | HASH JOIN | | 1 | 999K| 260K|00:00:08.76 || 3 | VIEW | | 1 | 999K| 999K|00:00:02.11 ||* 4 | COUNT STOPKEY | | 1 | | 999K|00:00:01.52 || 5 | PX COORDINATOR | | 1 | | 999K|00:00:00.84 || 6 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 11M| 0 |00:00:00.01 ||* 7 | COUNT STOPKEY | | 8 | | 1072K|00:00:03.83 || 8 | PX PARTITION HASH ALL| | 8 | 11M| 1072K|00:00:03.17 || 9 | INDEX FULL SCAN | OE_ORDER_HEADERS_U1 | 8 | 11M| 1072K|00:00:02.56 || 10 | VIEW | | 1 | 999K| 999K|00:00:02.28 ||* 11 | COUNT STOPKEY | | 1 | | 999K|00:00:01.68 || 12 | INDEX FULL SCAN | OE_ORDER_LINES_N1 | 1 | 57M| 999K|00:00:00.98 |----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------
2 - access("OH"."HEADER_ID"="OL"."HEADER_ID") 4 - filter(ROWNUM<1000000) 7 - filter(ROWNUM<1000000) 11 - filter(ROWNUM<1000000)
Note-----
- Degree of Parallelism is 8 because of hint
ALLSTATS
After 1 execution with DOP=8
![Page 47: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/47.jpg)
©OraInternals Riyaj Shamsudeen 47
Parallel Queries
----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 4 | | 4 |00:00:38.25 || 1 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:38.25 ||* 2 | HASH JOIN | | 4 | 999K| 418K|00:00:38.16 || 3 | VIEW | | 4 | 999K| 3999K|00:00:07.67 ||* 4 | COUNT STOPKEY | | 4 | | 3999K|00:00:05.53 || 5 | PX COORDINATOR | | 4 | | 3999K|00:00:03.12 || 6 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 11M| 0 |00:00:00.01 ||* 7 | COUNT STOPKEY | | 64 | | 4779K|00:00:56.27 || 8 | PX PARTITION HASH ALL| | 64 | 11M| 4779K|00:00:53.19 || 9 | INDEX FULL SCAN | OE_ORDER_HEADERS_U1 | 64 | 11M| 4779K|00:00:50.33 || 10 | VIEW | | 4 | 999K| 3999K|00:00:14.59 ||* 11 | COUNT STOPKEY | | 4 | | 3999K|00:00:12.32 || 12 | PX COORDINATOR | | 4 | | 3999K|00:00:09.35 || 13 | PX SEND QC (RANDOM) | :TQ20000 | 0 | 57M| 0 |00:00:00.01 ||* 14 | COUNT STOPKEY | | 64 | | 4169K|00:05:45.40 || 15 | PX BLOCK ITERATOR | | 64 | 57M| 4169K|00:05:42.68 ||* 16 | TABLE ACCESS FULL | OE_ORDER_LINES_ALL | 233 | 57M| 4169K|00:05:26.98 |----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):---------------------------------------------------..
Note-----
- Degree of Parallelism is 16 because of hint
ALLSTATS
After 4 executions with DOP=16
![Page 48: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/48.jpg)
©OraInternals Riyaj Shamsudeen 48
Note section
Note----- - dynamic sampling used for this statement (level=2)
Note----- - SQL profile “SQL_PROF_4awcsbtwr8ksz" used for this statement
Note----- - Cardinality feedback used
![Page 49: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/49.jpg)
©OraInternals Riyaj Shamsudeen 49
Few limitations
Display_awr does not store predicates.
Of course , not all SQL statements are stored in AWR repository (color important statements).
Display_cursor is not RAC aware. You can query the plans only from the local instances using display_cursor.
Run time statistics are printed only after the completion of SQL statements (Think production long running queries).
![Page 50: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/50.jpg)
©OraInternals Riyaj Shamsudeen 50
SQL monitor
SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFFSELECT DBMS_SQLTUNE.report_sql_monitor( sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual/
![Page 51: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/51.jpg)
©OraInternals Riyaj Shamsudeen 51
Output
![Page 52: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/52.jpg)
©OraInternals Riyaj Shamsudeen 52
Output ..2
![Page 53: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/53.jpg)
53
A Presentation sponsored by
![Page 54: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/54.jpg)
©OraInternals Riyaj Shamsudeen 54
Riyaj Shamsudeen
19 years using Oracle products/DBA OakTable member Oracle ACE Director Certified DBA versions 7.0,7.3,8,8i,9i &10g Specializes in RAC, performance tuning,
Internals and E-business suite Chief DBA with OraInternals Co-author few books Email: [email protected] Blog : orainternals.wordpress.com Web: www.orainternals.com
![Page 55: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/55.jpg)
©OraInternals Riyaj Shamsudeen 55
Thank you for attending!
If you like this presentation, you will love my
2-part intensive, online RAC webinar.
http://www.orainternals.com/training/advanced-rac-training
Email: [email protected] Dates Time
Week1 TBA 8AM – 2PM PDT
Week 2 TBA 8AM - 2PM PDT
![Page 56: Dbms plan - A swiss army knife for performance engineers](https://reader035.vdocuments.us/reader035/viewer/2022062418/5561aacdd8b42ae9708b50ea/html5/thumbnails/56.jpg)
©OraInternals Riyaj Shamsudeen 56
References
• Oracle support site. Metalink.oracle.com. Various documents
• Troubleshooting Oracle performance - Christian Antongini
• http://www.orainternals.com/papers - Riyaj Shamsudeen
• Various article from web led through on google search
• Tom Kyte’s website Asktom.oracle.com