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: Re[2]: Physics of the FILTER operation within SQL_PLANE.

Re: Re[2]: Physics of the FILTER operation within SQL_PLANE.

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Jun 2004 15:25:13 +0100
Message-ID: <019101c45eae$1064abd0$7102a8c0@Primary>

10g does have v$sys_time_model, and v$sess_time_model, which is a step in that sort of direction. These views give summarised figures of the total time break down - but there aren't many CPU figures in there.

The problem with CPU is how often and how accurately you measure it. It is done for each step of an SQL execution, and you can see this in v$sql_plan_statistics (which is available in 9.2)- but the overhead of collection is large. Oracle (notionally) has to collect the cpu time for every execution of every step of the plan. You can also play around with v$sql_workarea to get some idea of what big operations have used a lot of CPU (rather than hammering v$sql - which is a bit labour intensive and latchunfriendly  in a highly concurrent system).

In the 10g beta, I recall seeing a couple of queries going from a few seconds to several minutes of CPU when I enabled full CPU collection. I think the strategy in the production version is to sample, as the overhead is much smaller.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Hello Jonathan,

One thing that bothers me, is don't you think that in future version of Oracle, it should provide finer granularity of 'cpu service' event?

Wouldn't it be nice, if we'd have cpu service wait events similar to 'db file sequential read', but for cpu service, like 'sorting'/'merging', now we can see this kind of information in v$session_longops, but unfortunately this information is not logged in trace files.

What are list member's opinion about it?



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Jun 30 2004 - 09:22:10 CDT

Original text of this message

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