RE: cpu time and query column in tkprof output

  • From: "Yasin Baskan" <yasbs@xxxxxxxxxxxxxx>
  • To: <cary.millsap@xxxxxxxxxx>, <zoran_martic@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 3 Feb 2005 15:43:32 +0200

I totally agree with that but the first sql is faster and it also has
better latch statistics, so i conclude that it is also more scalable.
The only problem with it is that it has more number of logical reads.

RUN2 is the faster sql. As seen it has better latch numbers. But it has
more logical reads.

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

=20
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap
Sent: Thursday, February 03, 2005 3:02 PM
To: zoran_martic@xxxxxxxxx; Yasin Baskan; oracle-l@xxxxxxxxxxxxx
Subject: RE: cpu time and query column in tkprof output

> Your first SQL can be faster but it is less scalable
> and can cause ELAPSED time to be worse if you have
> very busy system.

Absolutely true. To be really picky with the words, it can cause CPU =3D
time to
be worse, too, if you have a busy system. It's because Oracle's =3D
busy-wait
strategy for latch acquisition is very CPU intensive. The longer a =3D
process
has to wait for a latch to become available, the more CPU it will burn =
=3D
while
busy-waiting.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 2/23 Houston, 3/16 Salt Lake City
- SQL Optimization 101: 2/7 Dallas
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Martic Zoran
Sent: Thursday, February 03, 2005 4:55 AM
To: yasbs@xxxxxxxxxxxxxx; cary.millsap@xxxxxxxxxx; =3D
oracle-l@xxxxxxxxxxxxx
Subject: RE: cpu time and query column in tkprof output

Hi Yasin,

These are LATCH stats and not STAT stats.

Latch statistics are very important to predict which
SQL will behave better when running in multiuser
environment, with many processes doing the same or
similar SQL's causing them to wait/lock/block on these
latches.=3D20

Stats are showing more about what you needed to
perform and partially on what you spend your pure CPU
time.

Your first SQL can be faster but it is less scalable
and can cause ELAPSED time to be worse if you have
very busy system.

You should test it in real case scenario while having
other things running (ideally the production :)

Regards,
Zoran


--- Yasin Baskan <yasbs@xxxxxxxxxxxxxx> wrote:

> 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.
>=3D20
> 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.
>=3D20
> NAME                                               =3D20
>     RUN1       RUN2
> DIFF
> --------------------------------------------------
> ---------- ----------
> ----------
> LATCH.job_queue_processes parameter latch          =3D20
>        1          0
> -1
> LATCH.ktm global data                              =3D20
>        0          1
> 1
> LATCH.ncodef allocation latch                      =3D20
>        1          0
> -1



        =3D09
__________________________________=3D20
Do you Yahoo!?=3D20
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
--
//www.freelists.org/webpage/oracle-l

--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l

Other related posts: