RE: v$sql 'db block changes'
- From: "Laimutis Nedzinskas" <lnd@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Dec 2005 10:38:11 -0000
tracking db block changes would give some idea but there are so many
things to consider:
- block getters can be quite responsible for block writes just because
they need db cache space.
- to change a block it is necessary to get a block. Which means that
again there is quite a chance that top block getter is top block
changer.
- 2 or more sql stms changing the same block would not need much space
in the db cache nor generate many disk writes. We are more interested in
statistics physical writes.
All this leads to the idea that the calculation should start from
another end:
find most write-loaded segments,
then find what sql is working on those segments.
Brgds,
Laimis
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: 15. desember 2005 17:51
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
