From oracle-l-bounce@freelists.org Thu Feb 3 05:19:21 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j13BJBQh000549 for ; Thu, 3 Feb 2005 05:19:16 -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 j13BJBem000466 for ; Thu, 3 Feb 2005 05:19:11 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DBB8169FB7; Thu, 3 Feb 2005 05:18:04 -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 06747-02; Thu, 3 Feb 2005 05:18:04 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 597DB6893A; Thu, 3 Feb 2005 05:18:04 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Subject: RE: cpu time and query column in tkprof output Date: Thu, 3 Feb 2005 12:16:29 +0200 Message-ID: <083667B535F3464CA0DD0D1DAFA4E37604D86DBA@camexc1.kfs.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: cpu time and query column in tkprof output Thread-Index: AcUJ2GmfQwdhGf17Qq2uOfFUYAmtNQAAJPEQ From: "Yasin Baskan" To: "Martic Zoran" , , X-archive-position: 15736 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=AWL autolearn=ham version=2.60 X-Spam-Level: Here are all the stats. Yes the execution plans are different. What i am trying to do is to re-write the sql for better performance. I re-write the sql ang get lower number of logical reads, but lose in other areas as i mentioned. So, by looking at the following stats and cpu time and elapsed time, i understand that i should stick with the original sql as the new sql performs worse. NAME RUN1 RUN2 DIFF -------------------------------------------------- ---------- ---------- ---------- LATCH.job_queue_processes parameter latch 1 0 -1 LATCH.ktm global data 0 1 1 LATCH.ncodef allocation latch 1 0 -1 LATCH.transaction branch allocation 1 0 -1 LATCH.session switching 1 0 -1 LATCH.loader state object freelist 204 202 -2 LATCH.longop free list 2 0 -2 LATCH.virtual circuit queues 3 0 -3 LATCH.event group latch 12 4 -8 LATCH.process allocation 12 4 -8 LATCH.channel handle pool latch 24 8 -16 NAME RUN1 RUN2 DIFF -------------------------------------------------- ---------- ---------- ---------- LATCH.process group creation 24 8 -16 LATCH.session timer 19 3 -16 LATCH.library cache load lock 20 0 -20 LATCH.Token Manager 23 0 -23 LATCH.channel operations parent latch 36 12 -24 LATCH.sequence cache 36 12 -24 LATCH.user lock 48 16 -32 LATCH.active checkpoint queue latch 40 6 -34 LATCH.multiblock read objects 200 2 -198 LATCH.sort extent pool 402 203 -199 LATCH.session allocation 322 82 -240 NAME RUN1 RUN2 DIFF -------------------------------------------------- ---------- ---------- ---------- LATCH.redo writing 712 351 -361 LATCH.checkpoint queue latch 598 164 -434 LATCH.session idle bit 746 166 -580 LATCH.messages 1292 590 -702 LATCH.list of block allocation 2254 1254 -1000 LATCH.transaction allocation 2871 1573 -1298 LATCH.dml lock allocation 6306 3506 -2800 LATCH.undo global data 7316 4231 -3085 LATCH.redo allocation 7445 4253 -3192 LATCH.shared pool 9310 4959 -4351 LATCH.enqueue hash chains 12333 6826 -5507 NAME RUN1 RUN2 DIFF -------------------------------------------------- ---------- ---------- ---------- LATCH.cache buffers lru chain 32883 25895 -6988 LATCH.enqueues 16234 8912 -7322 LATCH.row cache objects 57411 32360 -25051 LATCH.library cache 100992 55926 -45066 LATCH.cache buffers chains 811950 595768 -216182 -----Original Message----- From: Martic Zoran [mailto:zoran_martic@yahoo.com]=20 Sent: Thursday, February 03, 2005 12:08 PM To: Yasin Baskan; cary.millsap@hotsos.com; oracle-l@freelists.org Subject: RE: cpu time and query column in tkprof output Hi Yasin, As I said, it is about the whole execution plan. To say you can have the different type of joins, more execution steps like filtering, ... I wiped out the excution plan of your two SQL'a and cannot remember fully, but they were different. CPU time is not only and always about buffer gets and sorts. In many cases it is, but for short SQL's buffer gets can be the minority of overall work (you can do around 10,000 simple buffer gets per 100MHz CPU). It may not even be covered with other statistics differences you have got. Show us all major STAT differences, please? Regards, Zoran --- Yasin Baskan wrote: > I have the following stats about sorts. >=20 > NAME =20 > RUN1 RUN2 > DIFF > -------------------------------------------------- > ---------- ---------- > ---------- > STAT...sorts (rows) =20 > 183031 182958 > -73 >=20 > RUN1 is also worse in sorts. I get the query to get > lower number of > blocks but i get an increase in other areas (pysical > reads, latches, > sorts). So the elapsed time increases. >=20 >=20 =09 __________________________________=20 Do you Yahoo!?=20 All your favorites on one personal page - Try My Yahoo! http://my.yahoo.com=20 -- http://www.freelists.org/webpage/oracle-l