From oracle-l-bounce@freelists.org Wed Feb 2 05:26:33 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j12BQSoR022861 for ; Wed, 2 Feb 2005 05:26:28 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j12BQOem022850 for ; Wed, 2 Feb 2005 05:26:25 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B2F0A6C49F; Wed, 2 Feb 2005 05:25:29 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27995-09; Wed, 2 Feb 2005 05:25:29 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2C57C6C479; Wed, 2 Feb 2005 05:25:29 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: cpu time and query column in tkprof output Date: Wed, 2 Feb 2005 12:24:17 +0200 Message-ID: <083667B535F3464CA0DD0D1DAFA4E37604D5059A@camexc1.kfs.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: cpu time and query column in tkprof output Thread-Index: AcUJEVmqvE1tek+/RqeS0MroQ0tC/w== From: "Yasin Baskan" To: Content-Transfer-Encoding: 8bit X-archive-position: 15654 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: yasbs@kocbank.com.tr Precedence: normal Reply-To: yasbs@kocbank.com.tr X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.60 X-Spam-Level: Hi, I want to ask two questions about the following 2 sql statements. The below tkprof output shows 2 executes for each of them although i executed each once. Is it because of the select statements inside the main select? My main question is; for the second query it shows far less buffer gets than the first one, but the second one has more cpu time and elapsed time. Is this meaningful? I am looking at the buffer gets and saying that the second query is better but it shows more elapsed time? How should i comment on the following output? SELECT COUNT (*) FROM faxutil.t_kfs_jobs j, faxutil.t_kfs_actions a WHERE j.NO = a.jobno AND a.pool_id = :poolname AND (a.jobno, a.iteration) IN (SELECT jobno, MAX (iteration) FROM faxutil.t_kfs_actions WHERE pool_id = :poolname GROUP BY jobno) AND EXISTS ( SELECT pool_status, job_status FROM faxutil.t_kfs_tabstatuses WHERE tabno = :ptabno AND pool_status = a.status AND job_status = j.status) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.18 0.31 14 11677 58 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.18 0.31 14 11677 58 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 46 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 14 FILTER 2768 NESTED LOOPS 2768 HASH JOIN 2767 VIEW VW_NSO_1 2767 SORT GROUP BY 2875 INDEX RANGE SCAN (object id 34317) 2875 INDEX RANGE SCAN (object id 34317) 5534 TABLE ACCESS BY INDEX ROWID T_KFS_JOBS 5534 INDEX UNIQUE SCAN (object id 31338) 14 TABLE ACCESS FULL T_KFS_TABSTATUSES select /*+ ordered */ count(*) from faxutil.t_kfs_tabstatuses c, faxutil.t_kfs_jobs j, faxutil.t_kfs_actions a, ( select jobno, max(iteration) iteration from faxutil.t_kfs_actions where pool_id = :poolname group by jobno ) sqry1 where j.no = a.jobno and a.pool_id = :poolname and a.jobno = sqry1.jobno and a.iteration = sqry1.iteration and c.tabno = :ptabno and c.pool_status = a.status and c.job_status = j.status call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.82 1.44 31 4565 58 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.82 1.44 31 4565 58 1 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 46 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 14 HASH JOIN 14 HASH JOIN 2875 INDEX RANGE SCAN (object id 34317) 8282 NESTED LOOPS 14 TABLE ACCESS FULL T_KFS_TABSTATUSES 8282 INDEX FAST FULL SCAN (object id 34320) 2767 VIEW 2767 SORT GROUP BY 2875 INDEX RANGE SCAN (object id 34317) -- http://www.freelists.org/webpage/oracle-l