Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: cpu time and query column in tkprof output

RE: cpu time and query column in tkprof output

From: Yasin Baskan <yasbs_at_kocbank.com.tr>
Date: Thu, 3 Feb 2005 12:16:29 +0200
Message-ID: <083667B535F3464CA0DD0D1DAFA4E37604D86DBA@camexc1.kfs.local>


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_at_yahoo.com]=20 Sent: Thursday, February 03, 2005 12:08 PM To: Yasin Baskan; cary.millsap_at_hotsos.com; oracle-l_at_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

> 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 Received on Thu Feb 03 2005 - 05:19:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US