Re: v$sql 'db block changes'
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Dec 2005 10:54:09 -0000
If you are thinking about which statements
result in the most redo and undo being generated,
then it might help to look at statements which
are inserts, updates, and deletes, checking the
number of rows processed.
This isn't a perfect check, of course, because it
doesn't differentiate the statement that modifies
10 indexes for an insert from the statement that
modifies one index on the insert. But at least is
narrows down the set you have to examine by
eye.
select
rows_processed, buffer_gets, sql_text
from
v$sql
where
command in (
2, /* inserts */
6, /* updates */
7 /* deletes */
)
and rows_processed > {something}
;
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005
----- Original Message -----
From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, December 15, 2005 5:51 PM
Subject: v$sql 'db block changes'
Anybody have a good method for tracking top SQL by 'db block changes'? It
seems this statistic is only tracked by session and instance, but not by SQL
statement. The 'disk_reads' are tracked in v$sql and although it wouldn't
make sense to track 'disk_writes' (since physical writes are performed in
the background as needed by DBWR, not as a direct result of any particular
SQL statement), it seems like it would make sense to track 'db block
changes' by SQL, doesn't it? I've got heavy writes occurring on a RAID 5
system and it's killing performance, but I'm having trouble identifying the
offending SQL. Any ideas?
Thanks,
Brandon
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions
and other information in this message that do not relate to the official
business of this company shall be understood as neither given nor endorsed
by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
