Re: V$SQLAREA to determine most disk intensive SQL

Danisment,
Comments embedded.
"danisment" <danisment_at_yahoo.com> wrote in message news:2c78cfac.0403040119.6e9d2807_at_posting.google.com...
> Hi,
>
> I think you should not use V$ views in performance diagnosis.
> Because, many performance statistics of child(recursive) statements
> are already included in their parent statement. That means they are
> duplicated in V$ views.
>

Disagree. Armed with this knowledge, you know that recursive SQL may be included in parent SQL count - and thus the figures for a procedure DO include the SQL within the stored procedures.

This just tells not to assume that the overall load on the system is the simple
total of all statements - even ignoring expiration of the stats data.

It is perfectly valid as an approach to finding the most expensive SQL.

> I recommend you using event 10046. Because, it's possible to compute
> exclusive statistics in event 10046 traces.
>

This will give a better result for an individual execution - but if you've only got V$SQLAREA, it's way better than nothing - and will give an indication over
all activity on the database.

So I think that you're starting from performance diagnosis of a specific job, however the OP may trying to find were there are heavy usages of the system.

> regards...
>
> dani&#351;ment...
>
> vivek.gopalakrishnan_at_concert.com (gslman) wrote in message
news:<668c2610.0403010731.13174eed_at_posting.google.com>...
> > Hi,
> >
> > I am attempting to find the most expensive disk SQL using this query -
> >
> > SELECT executions*disk_reads||','||executions||','||disk_reads||','||
> > '"'||REPLACE(RTRIM(LTRIM(sql_text)),'"','')||'",'||first_load_time
> > FROM V$SQLAREA
> > where disk_reads > 10000
> > order by executions*disk_reads desc
> >
> > What does it mean when I sometimes get back a call to a stored
> > procedure such as - ie. do the figures relate to the individual SQL
> > within the stored procedure ??
> >
> > BEGIN P_SPROCEDURE(:1,:2,:3); END;
> >
> > Thanks
> > Viv

Regards,
Neil Received on Sat Mar 06 2004 - 16:23:41 CST