RE: cpu time and query column in tkprof output

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Thu, 3 Feb 2005 05:21:00 -0800 (PST)
Message-ID: <20050203132100.67080.qmail@web52607.mail.yahoo.com>

That is true.

I tested ones Oracle with Solid state disk. It was no I/O waits at all. It was worse performances then with normal disk until I changed the spin_count to the proper value (Steve Adams said to be used only if nothing can help, this was one special case).

I said earlier "forget latches for the pure CPU usage".
I should correct myself, proper latch aquisition is pure CPU usage, also probably some other activities like spinning (not sure anymore).

The thing with fast SQL statements is that a little disturbance can cause one SQL to be faster then the other.

You need to have the perfect test conditions and the best stats collection strategy to find out why. Neither timing or statistic collection is perfect (latches are global statistics for example).

In this case you have this: logical reads, latch aquisition, different sorts, even redo generation (probably caused by dirty block inspection), different memory consumed steps apart from sorts, ...

Both SQL's are good from some perspectives. In your production environment they will behave better or worse dependant on many things.
This is why tuning SQL is not of LOCAL character. Only if you know the overall system, what is he doing, behaving you will know the proper answer.

That is causing impossible to answer on your main question: which SQL is better?

To not ask you what are your criteria for something be better at all :)

Regards,
Zoran

Cary Millsap <cary.millsap_at_hotsos.com> wrote:

> > 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 time to
> be worse, too, if you have a busy system. It's
> because Oracle's busy-wait
> strategy for latch acquisition is very CPU
> intensive. The longer a process
> has to wait for a latch to become available, the
> more CPU it will burn 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...

Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250

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

Received on Thu Feb 03 2005 - 08:23:48 CST