TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 19 17:41:35 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Trace file: orcl_ora_2091.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 -------------------------------------------------------------------------------- *** SESSION ID:(141.94) 2012-12-19 16:33:21.746 ******************************************************************************** SQL ID: Plan Hash: 643331428 alter session set sql_trace=true 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 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 3.66 3.57 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 3.66 3.57 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=3576410 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000072 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=42040 us)(object id 58410) ******************************************************************************** select --+ index(test) count(pad) from test 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 2 3.49 3.40 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 3.49 3.40 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=3401530 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000043 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=41119 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 650348864 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=8 pr=0 pw=0 time=23417 us) ******************************************************************************** SQL ID: Plan Hash: 650326191 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=184 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=193 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=9 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=194 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=172 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 732099378 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 1 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 8 0.02 0.00 0 0 0 80 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 17 0.02 0.00 0 0 0 80 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 732098606 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 8 0.00 0.00 0 0 0 0 Fetch 8 0.05 0.03 0 6 0 8 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 17 0.05 0.03 0 6 0 8 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 650332107 select metadata from kopm$ where name='DB_FDO' 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 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 1 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 3) Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS BY INDEX ROWID KOPM$ (cr=2 pr=0 pw=0 time=48 us) 1 INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0 pw=0 time=11 us)(object id 365) -------------------------------------------------------------------------------- select /* EXEC_FROM_DBMS_XPLAN */ rtrim(sql_text) from v$sql where SQL_ID=:v_sql_id and child_number=:v_child_number call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 8 0.00 0.00 0 0 0 0 Fetch 8 0.00 0.00 0 0 0 8 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 17 0.00 0.00 0 0 0 8 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 650355235 alter session set "_rowsource_statistics_sampfreq"=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 Parsing user id: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 3.28 3.20 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 3.28 3.20 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=0 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=0 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=0 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 653577300 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** SQL ID: Plan Hash: 653572034 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=0 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=0 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=0 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 653583149 alter session set "_rowsource_statistics_sampfreq"=128 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 0 0 0 Misses in library cache during parse: 0 Parsing user id: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 3.52 3.45 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 3.52 3.45 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=3450042 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000044 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=41654 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 657044488 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=4837 us) ******************************************************************************** SQL ID: Plan Hash: 657039510 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=122 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=137 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=4 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=78 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=57 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 657049274 alter session set "_rowsource_statistics_sampfreq"=16 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: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 5.24 5.12 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.24 5.12 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=5122530 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000059 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=318373 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 662184698 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=5087 us) ******************************************************************************** SQL ID: Plan Hash: 662178468 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=125 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=139 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=4 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=83 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=63 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 662190272 alter session set "_rowsource_statistics_sampfreq"=8 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: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 7.18 7.02 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 7.18 7.02 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=7020028 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000055 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=634778 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 669221762 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=4314 us) ******************************************************************************** SQL ID: Plan Hash: 669217370 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=119 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=149 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=4 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=80 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=60 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 669227428 alter session set "_rowsource_statistics_sampfreq"=4 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: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 11.25 10.99 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 11.25 10.99 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=10991175 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000047 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=1286545 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 680231843 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=4813 us) ******************************************************************************** SQL ID: Plan Hash: 680227036 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=128 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=141 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=5 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=97 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=65 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 680237016 alter session set "_rowsource_statistics_sampfreq"=2 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: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 19.01 18.56 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 19.01 18.56 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=18566649 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=20000057 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=2567322 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 698816061 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.00 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 15 COLLECTION ITERATOR PICKLER FETCH DISPLAY_CURSOR (cr=0 pr=0 pw=0 time=5115 us) ******************************************************************************** SQL ID: Plan Hash: 698811249 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=143 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=158 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=4 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=178 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=104 us) -------------------------------------------------------------------------------- SQL ID: Plan Hash: 698821678 alter session set "_rowsource_statistics_sampfreq"=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 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: 62 ******************************************************************************** select --+ index(test) count(pad) from test 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 2 34.03 33.26 0 41405 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 34.03 33.26 0 41405 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=41405 pr=0 pw=0 time=33265938 us) 10000000 TABLE ACCESS BY INDEX ROWID TEST (cr=41405 pr=0 pw=0 time=30000065 us) 10000000 INDEX FULL SCAN IDX (cr=23560 pr=0 pw=0 time=5055078 us)(object id 58410) ******************************************************************************** SQL ID: Plan Hash: 732100277 select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) 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 2 0.01 0.00 0 0 0 15 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 0 0 15 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 ******************************************************************************** SQL ID: Plan Hash: 732094651 select /* EXEC_FROM_DBMS_XPLAN */ 1, prev_sql_id, prev_child_number from v$session where sid=userenv('sid') and username is not null and prev_hash_value <> 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 2 0.00 0.00 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 NESTED LOOPS (cr=0 pr=0 pw=0 time=145 us) 1 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=137 us) 1 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=4 us) -------------------------------------------------------------------------------- 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, null, access_predicates, filter_predicates, null, null, null, other_xml, starts, outrows, crgets, cugets, reads, writes, etime, mem_opt, mem_one, last_mem_used, last_mem_usage, opt_cnt, one_cnt, multi_cnt, max_tmp, last_tmp from (select /*+ no_merge */ id, depth, position, operation, options, cost, time, cardinality, bytes, object_node, object_name, other_tag, partition_start, partition_stop, distribution, temp_space, io_cost, cpu_cost, filter_predicates, access_predicates, other, projection, qblock_name, object_alias, other_xml, last_starts starts, last_output_rows outrows, last_cr_buffer_gets crgets, last_cu_buffer_gets cugets, last_disk_reads reads, last_disk_writes writes, last_elapsed_time etime, estimated_optimal_size mem_opt, estimated_onepass_size mem_one, last_memory_used last_mem_used, last_execution last_mem_usage, optimal_executions opt_cnt, onepass_executions one_cnt, multipasses_executions multi_cnt, max_tempseg_size max_tmp, last_tempseg_size last_tmp from V$SQL_PLAN_STATISTICS_ALL vp where SQL_ID = '9pzmsyh5t14bb' and child_number=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 5 0.00 0.00 0 0 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.00 0.00 0 0 0 4 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 62 (recursive depth: 2) Rows Row Source Operation ------- --------------------------------------------------- 4 VIEW (cr=0 pr=0 pw=0 time=203 us) 4 FIXED TABLE FIXED INDEX X$QESRSTATALL (ind:4) (cr=0 pr=0 pw=0 time=133 us) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 24 0.00 0.00 0 0 0 0 Execute 25 0.01 0.00 0 0 0 0 Fetch 34 90.67 88.61 0 372645 0 129 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 83 90.68 88.61 0 372645 0 129 Misses in library cache during parse: 0 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 20 0.00 0.00 0 0 0 0 Execute 41 0.00 0.00 0 0 0 0 Fetch 81 0.07 0.04 0 8 0 137 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 142 0.07 0.05 0 8 0 137 Misses in library cache during parse: 0 44 user SQL statements in session. 1 internal SQL statements in session. 45 SQL statements in session. ******************************************************************************** Trace file: orcl_ora_2091.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 44 user SQL statements in trace file. 1 internal SQL statements in trace file. 45 SQL statements in trace file. 16 unique SQL statements in trace file. 724 lines in trace file. 17911 elapsed seconds in trace file.