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

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 30 Jun 2004 15:25:13 +0100

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 latch-
unfriendly 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.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Optimising Oracle Seminar - schedule updated May 1st

----- Original Message ----- 
From: "Edgar Chupit" <edgar.chupit@xxxxx>
To: "Jonathan Lewis" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 30, 2004 10:31 AM
Subject: Re[2]: Physics of the FILTER operation within SQL_PLANE.

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:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: