OracleExecutionPlan笔记

-- example select trs.name, rt.* from rps_transaction rt, rps_transaction_status trs where rt.status_id = trs.id and

-- example
select trs.name, rt.*
from rps_transaction rt, rps_transaction_status trs
where rt.status_id = trs.id
and rt.original_rps_batch_id = 2790000
order by rt.id;

SQL> explain plan for
2 -- example
3 select trs.name, rt.*
4 from rps_transaction rt, rps_transaction_status trs
5 where rt.status_id = trs.id
6 and rt.original_rps_batch_id = 2790000
7 order by rt.id;

SQL> set lines 180
SQL> set pages 999
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1655554624

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 852 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 852 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 6 | 852 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| RPS_TRANSACTION | 6 | 762 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RPS_TRANSACTION_N1 | 6 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| RPS_TRANSACTION_STATUS | 1 | 15 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | RPS_TRANSACTION_STATUS_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("RT"."ORIGINAL_RPS_BATCH_ID"=2790000)
6 - access("RT"."STATUS_ID"="TRS"."ID")

SQL> set autot
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
SQL> set autotrace traceonly explain
SQL> -- example
SQL> select trs.name, rt.*
2 from rps_transaction rt, rps_transaction_status trs
3 where rt.status_id = trs.id
and rt.original_rps_batch_id = 2790000
4 and rt.original_rps_batch_id = 2790000
5 order by rt.id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1655554624

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 852 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 852 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | 6 | 852 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| RPS_TRANSACTION | 6 | 762 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RPS_TRANSACTION_N1 | 6 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| RPS_TRANSACTION_STATUS | 1 | 15 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | RPS_TRANSACTION_STATUS_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("RT"."ORIGINAL_RPS_BATCH_ID"=2790000)
6 - access("RT"."STATUS_ID"="TRS"."ID")

SQL> select s.address, s.hash_value, substr(s.sql_text,1,100)
2 from v$sqlarea s
3 where lower(s.SQL_TEXT) like '-- example select trs.name,%';

ADDRESS HASH_VALUE SUBSTR(S.SQL_TEXT,1,100)
-------- ---------- ----------------------------------------------------------------------------------------------------
44C7B5A0 1654873186 -- example select trs.name, rt.* from rps_transaction rt, rps_transaction_status trs where rt.sta

-- use this sql statement to display execution play in v$sql_plan
select /*+ rule */
lpad(' ', p.depth, ' ') || p.operation || ' ' || p.options as operation,
p.object_name,
p.cardinality,
p.cost
from v$sql_plan p
where p.address = '&addr'
and p.hash_value = '&hash'
order by p.id;


SQL> col operation for a50
SQL> /
Enter value for addr: 44C7B5A0
old 7: where p.address = '&addr'
new 7: where p.address = '44C7B5A0'
Enter value for hash: 1654873186
old 8: and p.hash_value = '&hash'
new 8: and p.hash_value = '1654873186'

OPERATION OBJECT_NAME CARDINALITY COST
-------------------------------------------------- ------------------------------ ----------- ----------
SELECT STATEMENT 4
SORT ORDER BY 6 4
NESTED LOOPS 6 3
TABLE ACCESS BY INDEX ROWID RPS_TRANSACTION 6 2
INDEX RANGE SCAN RPS_TRANSACTION_N1 6 1
TABLE ACCESS BY INDEX ROWID RPS_TRANSACTION_STATUS 1 1
INDEX UNIQUE SCAN RPS_TRANSACTION_STATUS_PK 1 0

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> -- example
SQL> select trs.name, rt.*
2 from rps_transaction rt, rps_transaction_status trs
3 where rt.status_id = trs.id
4 and rt.original_rps_batch_id = 2790000
5 order by rt.id;

SQL> alter session set events '10046 trace name context off';

Session altered.

-- use this sql statement to display own trace file name
SELECT udd.udd || '/' || iname.iname || '_ora_' || sp.spid || '.trc' trcname
FROM (SELECT lower(VALUE) iname
FROM v$parameter
WHERE NAME = 'instance_name') iname,
(SELECT VALUE udd FROM v$parameter WHERE NAME = 'user_dump_dest') udd,
(SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p, v$mystat m
WHERE s.PADDR = p.ADDR
AND m.sid = s.sid
AND m.statistic# = 1) sp;
SQL> /
TRCNAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/home/Oracle/product/10.2.0/db_1/admin/data/udump/data_ora_19366.trc

SQL> ! cp /home/oracle/product/10.2.0/db_1/admin/data/udump/data_ora_19366.trc /tmp/.

SQL> ! tkprof /tmp/data_ora_19366.trc /tmp/data_ora_19366.rpt sort=exeela

TKPROF: Release 10.2.0.1.0 - Production on Tue Apr 29 17:13:41 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> !cat /tmp/data_ora_19366.rpt
...

select trs.name, rt.*
from rps_transaction rt, rps_transaction_status trs
where rt.status_id = trs.id
and rt.original_rps_batch_id = 2790000
order by rt.id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.04 0 10 0 6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 268

Rows Row Source Operation
------- ---------------------------------------------------
6 SORT ORDER BY (cr=10 pr=0 pw=0 time=470 us)
6 NESTED LOOPS (cr=10 pr=0 pw=0 time=453 us)
6 TABLE ACCESS BY INDEX ROWID RPS_TRANSACTION (cr=2 pr=0 pw=0 time=157 us)
6 INDEX RANGE SCAN RPS_TRANSACTION_N1 (cr=1 pr=0 pw=0 time=73 us)(object id 837086)
6 TABLE ACCESS BY INDEX ROWID RPS_TRANSACTION_STATUS (cr=8 pr=0 pw=0 time=206 us)
6 INDEX UNIQUE SCAN RPS_TRANSACTION_STATUS_PK (cr=2 pr=0 pw=0 time=93 us)(object id 835587)

PLAN and Collection

-- prepare table and collection
create table ta as select * from dba_objects;

-- use function to return collection
drop function f_c;
create or replace function f_c return int_tab_type
is
l_tab int_tab_type := int_tab_type();
begin
select object_id bulk collect into l_tab from ta sample (10);
return l_tab;
end;
/

-- collection没有准确的统计信息，可以检查E-Rows/A-Rows
select /*+ gather_plan_statistics */
ta.object_name
from ta, table(f_c()) tb
where ta.object_id = tb.column_value;

SQL> select t.plan_table_output
2 from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) t;
SQL_ID 5hjq4794jwgy9, child number 0
-------------------------------------
select /*+ gather_plan_statistics*/ ta.object_name from ta, table(f_c()) tb where ta.object_id = tb.column_value

Plan hash value: 2098580674

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 8168 | 7333 |00:00:00.15 | 2562 | 921K| 921K| 1282K (0)|
| 2 | COLLECTION ITERATOR PICKLER FETCH| F_C | 1 | | 7334 |00:00:00.05 | 1040 | | | |
| 3 | TABLE ACCESS FULL | TA | 1 | 76919 | 75049 |00:00:00.23 | 1522 | | | |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TA"."OBJECT_ID"=VALUE(KOKBF$))

Note
-----
- dynamic sampling used for this statement

-- 使用hint提供一个估计值给CBO，，我这里提供了一个很大的值，导致plan改变
select /*+ gather_plan_statistics cardinality(tb 99999999) */
ta.object_name
from ta, table(f_c()) tb
where ta.object_id = tb.column_value；

SQL> select t.plan_table_output
2 from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) t;
SQL_ID 2mgcxsqg8pu8w, child number 0
-------------------------------------
select /*+ gather_plan_statistics cardinality(tb 99999999) */ ta.object_name from ta, table(f_c()) tb where
ta.object_id = tb.column_value

Plan hash value: 1609288054

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100M| 7545 |00:00:00.20 | 2080 | 3851K| 1063K| 5029K (0)|
| 2 | TABLE ACCESS FULL | TA | 1 | 76919 | 75049 |00:00:00.23 | 1040 | | | |
| 3 | COLLECTION ITERATOR PICKLER FETCH| F_C | 1 | | 7545 |00:00:00.04 | 1040 | | | |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TA"."OBJECT_ID"=VALUE(KOKBF$))