SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS SQL> select /*+ CARDINALITY(outer 1)*/ outer.participantgroup I 2 from sourceprocresult outer 3 where sprid = 4 (select max(sprid) 5 from sourceprocresult inner 6 where spuid = 100000000 7 and sprid < 1521); Elapsed: 00:00:02.23 Execution Plan ---------------------------------------------------------- Plan hash value: 2489329025 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 8 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | SOURCEPROCRESULT | 1 | 10 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 14 | | | |* 4 | TABLE ACCESS BY INDEX ROWID| SOURCEPROCRESULT | 1 | 14 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SPRID"= (SELECT MAX("SPRID") FROM "SOURCEPROCRESULT" "INNER" WHERE "SPRID"<1521 AND "SPUID"=100000000)) 4 - filter("SPUID"=100000000) 5 - access("SPRID"<1521) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 69 consistent gets 0 physical reads 0 redo size 200 bytes sent via SQL*Net to client 240 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> EXPLAIN PLAN FOR select /*+ CARDINALITY(outer 1)*/ outer.participantgroup 2 from sourceprocresult outer 3 where sprid = 4 (select max(sprid) 5 from sourceprocresult inner 6 where spuid = 100000000 7 and sprid < 1521); Explained. Elapsed: 00:00:04.12 SQL> SQL> SELECT * FROM table( dbms_xplan.display); 20 rows selected. Elapsed: 00:00:02.34 Execution Plan ---------------------------------------------------------- Plan hash value: 3013799171 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | | | --------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 29 recursive calls 12 db block gets 176 consistent gets 0 physical reads 0 redo size 1708 bytes sent via SQL*Net to client 247 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed SQL> SQL> ALTER SESSION SET SQL_TRACE=TRUE; Session altered. Elapsed: 00:00:18.01 SQL> SQL> select /*+ CARDINALITY(outer 1)*/ outer.participantgroup 2 from sourceprocresult outer 3 where sprid = 4 (select max(sprid) 5 from sourceprocresult inner 6 where spuid = 100000000 7 and sprid < 1521); Elapsed: 00:00:02.08 Execution Plan ---------------------------------------------------------- Plan hash value: 2489329025 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 8 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | SOURCEPROCRESULT | 1 | 10 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 14 | | | |* 4 | TABLE ACCESS BY INDEX ROWID| SOURCEPROCRESULT | 1 | 14 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("SPRID"= (SELECT MAX("SPRID") FROM "SOURCEPROCRESULT" "INNER" WHERE "SPRID"<1521 AND "SPUID"=100000000)) 4 - filter("SPUID"=100000000) 5 - access("SPRID"<1521) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 69 consistent gets 0 physical reads 0 redo size 232 bytes sent via SQL*Net to client 240 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> ALTER SESSION SET SQL_TRACE=FALSE; Session altered. Elapsed: 00:00:00.76 SQL> TKPROF: Release 10.2.0.4.0 - Production on Thu Dec 3 05:29:37 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. obiiload@oxdora61: obiiload@oxdora61: cat trace_result.txt TKPROF: Release 10.2.0.4.0 - Production on Thu Dec 3 05:29:37 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Trace file: dobnkiib_ora_19419.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 3 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 3 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) ******************************************************************************** select /*+ CARDINALITY(outer 1)*/ outer.participantgroup from sourceprocresult outer where sprid = (select max(sprid) from sourceprocresult inner where spuid = 100000000 and sprid < 1521) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 64 0 0 Fetch 2 0.00 0.00 0 5 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 69 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=69 pr=0 pw=0 time=1322 us) 1 INDEX RANGE SCAN SPR_SPRID (cr=68 pr=0 pw=0 time=1325 us)(object id 1082550) 1 SORT AGGREGATE (cr=64 pr=0 pw=0 time=1301 us) 1 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=64 pr=0 pw=0 time=1282 us) 996 INDEX RANGE SCAN SPR_SPRID (cr=5 pr=0 pw=0 time=32 us)(object id 1082550) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'SOURCEPROCRESULT' (TABLE) 1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SPR_SPRID' (INDEX) 1 SORT (AGGREGATE) 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'SOURCEPROCRESULT' (TABLE) 996 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SPR_SPRID' (INDEX) ******************************************************************************** DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 6 6 6 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 6 6 6 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) Rows Row Source Operation ------- --------------------------------------------------- 0 DELETE PLAN_TABLE$ (cr=3 pr=0 pw=0 time=84 us) 0 TABLE ACCESS FULL PLAN_TABLE$ (cr=3 pr=0 pw=0 time=78 us) Rows Execution Plan ------- --------------------------------------------------- 0 DELETE STATEMENT MODE: ALL_ROWS 0 DELETE OF 'PLAN_TABLE$' 0 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP)) ******************************************************************************** EXPLAIN PLAN SET STATEMENT_ID='PLUS786352' FOR select /*+ CARDINALITY(outer 1)*/ outer.participantgroup from sourceprocresult outer where sprid = (select max(sprid) from sourceprocresult inner where spuid = 100000000 and sprid < 1521) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN SPR_SPRID (cr=0 pr=0 pw=0 time=0 us)(object id 1082550) 0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us) 0 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=0 pr=0 pw=0 time=0 us) 0 INDEX RANGE SCAN SPR_SPRID (cr=0 pr=0 pw=0 time=0 us)(object id 1082550) ******************************************************************************** insert into plan_table (statement_id, timestamp, operation, options, object_node, object_owner, object_name, object_instance, object_type, search_columns, id, parent_id, position, other,optimizer, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, distribution, cpu_cost, io_cost, temp_space, access_predicates, filter_predicates, projection, time, qblock_name, object_alias, plan_id, depth, remarks, other_xml ) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21, :22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 6 0.00 0.00 0 1 6 6 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 1 6 6 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 INSERT STATEMENT MODE: ALL_ROWS ******************************************************************************** SELECT ORA_PLAN_ID_SEQ$.NEXTVAL FROM DUAL call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=0 pr=0 pw=0 time=30 us) 1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us) ******************************************************************************** SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1)) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 0 0 20 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 0 0 20 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) Rows Row Source Operation ------- --------------------------------------------------- 20 COLLECTION ITERATOR PICKLER FETCH DISPLAY (cr=47 pr=0 pw=0 time=13240 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 20 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDURE) ******************************************************************************** select /*+ opt_param('parallel_execution_enabled', 'false') EXEC_FROM_DBMS_XPLAN */ * from PLAN_TABLE where 1=0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 FILTER (cr=0 pr=0 pw=0 time=2 us) 0 TABLE ACCESS FULL PLAN_TABLE$ (cr=0 pr=0 pw=0 time=0 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 0 FILTER 0 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP)) ******************************************************************************** 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, partition_start, partition_stop, object_node, other_tag, distribution, projection, access_predicates, filter_predicates , other, qblock_name, object_alias, nvl(other_xml, remarks), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null from PLAN_TABLE where plan_id = (select max(plan_id) from PLAN_TABLE where id=0 and statement_id = :stmt_id) order by id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 7 0.00 0.00 0 6 7 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.00 0.00 0 6 7 6 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 6 SORT ORDER BY (cr=6 pr=0 pw=0 time=478 us) 6 TABLE ACCESS FULL PLAN_TABLE$ (cr=6 pr=0 pw=0 time=173 us) 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=66 us) 1 TABLE ACCESS FULL PLAN_TABLE$ (cr=3 pr=0 pw=0 time=49 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 6 SORT (ORDER BY) 6 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP)) 1 SORT (AGGREGATE) 1 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP)) ******************************************************************************** SELECT PLAN_TABLE_OUTPUT FROM TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 ) AS SYS.DBMS_XPLAN_TYPE_TABLE)) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 5 5 20 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 5 5 20 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) (recursive depth: 1) error during execute of EXPLAIN PLAN statement ORA-06553: PLS-306: wrong number or types of arguments in call to 'PREPARE_RECORDS' parse error offset: 114 ******************************************************************************** SELECT /*+ opt_param('parallel_execution_enabled', 'false') */ extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval, '/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type = "row_shipping"]'), extractvalue(xmlval, '/*/info[@type = "index_size"]'), extractvalue(xmlval,'/*/info[@type = "plan_hash"]') from (select xmltype(:v_other_xml) xmlval from dual) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.01 0.00 0 36 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 36 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 1865 (OB2_TEST) (recursive depth: 2) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 0 FAST DUAL ******************************************************************************** ALTER SESSION SET SQL_TRACE=FALSE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Parsing user id: 1865 (OB2_TEST) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 8 0.00 0.00 0 0 0 0 Execute 8 0.01 0.00 0 73 6 8 Fetch 5 0.00 0.00 0 5 0 21 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 21 0.01 0.01 0 78 6 29 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.00 0.00 0 0 0 0 Execute 11 0.00 0.00 0 1 6 6 Fetch 10 0.01 0.01 0 47 12 28 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 26 0.01 0.01 0 48 18 34 Misses in library cache during parse: 0 13 user SQL statements in session. 1 internal SQL statements in session. 14 SQL statements in session. 7 statements EXPLAINed in this session. ******************************************************************************** Trace file: dobnkiib_ora_19419.trc Trace file compatibility: 10.01.00 Sort options: default 3 sessions in tracefile. 16 user SQL statements in trace file. 1 internal SQL statements in trace file. 14 SQL statements in trace file. 12 unique SQL statements in trace file. 7 SQL statements EXPLAINed using schema: OB2_TEST.prof$plan_table Default table was used. Table was created. Table was dropped. 166 lines in trace file. 21 elapsed seconds in trace file.