TKPROF: Release 10.1.0.2.0 - Production on Tue Nov 10 18:19:48 2009 Copyright (c) 1982, 2004, Oracle. All rights reserved. Trace file: orcl_ora_3428.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 ******************************************************************************** ALTER SESSION SET EVENTS '10046 trace name context forever,level 12' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.19 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.19 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: SYS 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 SQL*Net message from client 1 0.00 0.00 ******************************************************************************** select condition from cdef$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.02 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.03 0 2 0 1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY USER ROWID CDEF$ (cr=0 pr=0 pw=0 time=0 us) ******************************************************************************** SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0) FROM (SELECT /*+ NO_PARALLEL("E") FULL("E") NO_PARALLEL_INDEX("E") */ 1 AS C1, 1 AS C2, "E"."JOB_ID" AS C3 FROM "ORACLE"."EMPLOYEES" "E") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.00 0 7 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT GROUP BY (cr=7 pr=0 pw=0 time=284 us) 107 TABLE ACCESS FULL EMPLOYEES (cr=7 pr=0 pw=0 time=823 us) ******************************************************************************** SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0), NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0 END),0) FROM (SELECT /*+ NO_PARALLEL("J") FULL("J") NO_PARALLEL_INDEX("J") */ 1 AS C1, 1 AS C2, "J"."JOB_ID" AS C3 FROM "ORACLE"."JOBS" "J") SAMPLESUB call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.48 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.48 0 7 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 SORT GROUP BY (cr=7 pr=0 pw=0 time=278 us) 19 TABLE ACCESS FULL JOBS (cr=7 pr=0 pw=0 time=209 us) ******************************************************************************** SELECT e.last_name, j.job_title from oracle.employees e JOIN oracle.jobs j ON (e.job_id=j.job_id) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.13 0 4 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 15 0 107 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.14 0 19 0 107 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 107 HASH JOIN (cr=15 pr=0 pw=0 time=5798 us) 19 TABLE ACCESS FULL JOBS (cr=7 pr=0 pw=0 time=187 us) 107 TABLE ACCESS FULL EMPLOYEES (cr=8 pr=0 pw=0 time=796 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 0.14 0.15 ******************************************************************************** ALTER SESSION SET EVENTS '10046 trace name context off' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.09 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.01 0.09 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.03 0.22 0 4 0 0 Execute 3 0.00 0.19 0 0 0 0 Fetch 2 0.00 0.00 0 15 0 107 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 7 0.03 0.42 0 19 0 107 Misses in library cache during parse: 2 Misses in library cache during execute: 1 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 3 0.00 0.00 SQL*Net message from client 3 0.14 0.15 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 3 0.03 0.49 0 0 0 0 Execute 3 0.00 0.02 0 0 0 0 Fetch 3 0.00 0.00 0 16 0 3 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9 0.03 0.52 0 16 0 3 Misses in library cache during parse: 3 Misses in library cache during execute: 1 3 user SQL statements in session. 3 internal SQL statements in session. 6 SQL statements in session. ******************************************************************************** Trace file: orcl_ora_3428.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 3 internal SQL statements in trace file. 6 SQL statements in trace file. 6 unique SQL statements in trace file. 85 lines in trace file. 0 elapsed seconds in trace file.