TKPROF: Release 10.2.0.1.0 - Production on Sáb Ago 18 02:27:46 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Trace file: cm3b_ora_1004.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 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: 39 (CM) ******************************************************************************** select condition from cdef$ where rowid=:1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.00 0.00 0 0 0 0 Execute 32 0.00 0.00 0 0 0 0 Fetch 32 0.00 0.00 0 64 0 32 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96 0.00 0.00 0 64 0 32 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 ------- --------------------------------------------------- 1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=27 us) ******************************************************************************** SELECT m.codartigo, m.codalmoxarifado, MAX(idmov) AS idmov FROM moviment m, almox a, (SELECT m.codartigo, m.codalmoxarifado, MAX(m.datamov) AS datamov FROM moviment m, almox a WHERE(m.idpessoa = 3) AND(m.codalmoxarifado IN(9)) AND(m.codalmoxarifado = a.codalmoxarifado) AND(a.codcusteio = 3) AND(m.datamov < to_date('01/08/2007', 'DD/MM/YYYY')) AND(m.datamov >= (SELECT to_date(nvl(s.ultimadata, p.dataimplanta), 'DD/MM/YYYY') FROM paralmox p, (SELECT MAX(ultimadata) AS ultimadata FROM ultdatarepresa WHERE ultimadata < to_date('01/08/2007', 'DD/MM/YYYY') AND idpessoa = 3) s WHERE p.idpessoa = 3)) GROUP BY m.codartigo, m.codalmoxarifado) md WHERE(md.datamov = m.datamov) AND(md.codartigo = m.codartigo) AND(md.codalmoxarifado = m.codalmoxarifado) AND(m.codalmoxarifado = a.codalmoxarifado) AND(a.codcusteio = 3) AND(m.datamov < to_date('01/08/2007', 'DD/MM/YYYY')) AND(m.datamov >= (SELECT to_date(nvl(s.ultimadata, p.dataimplanta), 'DD/MM/YYYY') FROM paralmox p, (SELECT MAX(ultimadata) AS ultimadata FROM ultdatarepresa WHERE ultimadata < to_date('01/08/2007', 'DD/MM/YYYY') AND idpessoa = 3) s WHERE p.idpessoa = 3) ) GROUP BY m.codartigo, m.codalmoxarifado call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.05 0 0 0 0 Execute 1 0.00 0.00 0 10 0 0 Fetch 159 20.64 21.14 735 8910 0 2370 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 161 20.70 21.19 735 8920 0 2370 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 39 (CM) Rows Row Source Operation ------- --------------------------------------------------- 2370 HASH GROUP BY (cr=8920 pr=735 pw=735 time=21143294 us) 3367 VIEW (cr=8920 pr=735 pw=735 time=20897991 us) 3367 FILTER (cr=8920 pr=735 pw=735 time=20887885 us) 80134 HASH GROUP BY (cr=8920 pr=735 pw=735 time=24185932 us) 12936656 FILTER (cr=8920 pr=0 pw=0 time=77840993 us) 12936656 HASH JOIN (cr=8910 pr=0 pw=0 time=26094090 us) 80134 TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=4453 pr=0 pw=0 time=320651 us) 99457 INDEX RANGE SCAN XIE1MOVIMENT (cr=265 pr=0 pw=0 time=201276 us)(object id 43797) 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=146 us) 1 TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=40 us) 1 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=20 us)(object id 44082) 1 VIEW (cr=3 pr=0 pw=0 time=91 us) 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=80 us) 5 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=58 us) 80134 NESTED LOOPS (cr=4457 pr=0 pw=0 time=721414 us) 1 NESTED LOOPS (cr=4 pr=0 pw=0 time=97 us) 1 TABLE ACCESS BY INDEX ROWID ALMOX (cr=2 pr=0 pw=0 time=54 us) 1 INDEX UNIQUE SCAN IDXALMOXA (cr=1 pr=0 pw=0 time=26 us)(object id 41109) 1 TABLE ACCESS BY INDEX ROWID ALMOX (cr=2 pr=0 pw=0 time=27 us) 1 INDEX UNIQUE SCAN IDXALMOXA (cr=1 pr=0 pw=0 time=12 us)(object id 41109) 80134 TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=4453 pr=0 pw=0 time=480927 us) 99457 INDEX RANGE SCAN XIE1MOVIMENT (cr=265 pr=0 pw=0 time=201373 us)(object id 43797) 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=93 us) 1 TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=24 us) 1 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=11 us)(object id 44082) 1 VIEW (cr=3 pr=0 pw=0 time=55 us) 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=45 us) 5 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=34 us) 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=146 us) 1 TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=40 us) 1 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=20 us)(object id 44082) 1 VIEW (cr=3 pr=0 pw=0 time=91 us) 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=80 us) 5 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=58 us) 1 NESTED LOOPS (cr=5 pr=0 pw=0 time=93 us) 1 TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=24 us) 1 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=11 us)(object id 44082) 1 VIEW (cr=3 pr=0 pw=0 time=55 us) 1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=45 us) 5 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=34 us) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT MODE: ALL_ROWS 2370 HASH (GROUP BY) 3367 VIEW 3367 FILTER 80134 HASH (GROUP BY) 12936656 FILTER 12936656 HASH JOIN 80134 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MOVIMENT' (TABLE) 99457 INDEX MODE: ANALYZED (RANGE SCAN) OF 'XIE1MOVIMENT' (INDEX) 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PARALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'XPKPARALMOX' (INDEX (UNIQUE)) 1 VIEW 1 SORT (AGGREGATE) 5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ULTDATAREPRESA' (TABLE) 80134 NESTED LOOPS 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'ALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'IDXALMOXA' (INDEX (UNIQUE)) 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'ALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'IDXALMOXA' (INDEX (UNIQUE)) 80134 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'MOVIMENT' (TABLE) 99457 INDEX MODE: ANALYZED (RANGE SCAN) OF 'XIE1MOVIMENT' (INDEX) 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PARALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'XPKPARALMOX' (INDEX (UNIQUE)) 1 VIEW 1 SORT (AGGREGATE) 5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ULTDATAREPRESA' (TABLE) 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PARALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'XPKPARALMOX' (INDEX (UNIQUE)) 1 VIEW 1 SORT (AGGREGATE) 5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ULTDATAREPRESA' (TABLE) 1 NESTED LOOPS 1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'PARALMOX' (TABLE) 1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'XPKPARALMOX' (INDEX (UNIQUE)) 1 VIEW 1 SORT (AGGREGATE) 5 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ULTDATAREPRESA' (TABLE) ******************************************************************************** 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: 1 Optimizer mode: ALL_ROWS Parsing user id: 39 (CM) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.06 0.06 0 0 0 0 Execute 3 0.00 0.00 0 10 0 0 Fetch 159 20.64 21.14 735 8910 0 2370 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 164 20.70 21.20 735 8920 0 2370 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 32 0.00 0.00 0 0 0 0 Execute 32 0.00 0.00 0 0 0 0 Fetch 32 0.00 0.00 0 64 0 32 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 96 0.00 0.00 0 64 0 32 Misses in library cache during parse: 1 Misses in library cache during execute: 1 3 user SQL statements in session. 32 internal SQL statements in session. 35 SQL statements in session. 1 statement EXPLAINed in this session. ******************************************************************************** Trace file: cm3b_ora_1004.trc Trace file compatibility: 10.01.00 Sort options: default 1 session in tracefile. 3 user SQL statements in trace file. 32 internal SQL statements in trace file. 35 SQL statements in trace file. 4 unique SQL statements in trace file. 1 SQL statements EXPLAINed using schema: CM.prof$plan_table Default table was used. Table was created. Table was dropped. 539 lines in trace file. 73 elapsed seconds in trace file.