Re: v$sql 'db block changes'

I don't disagree that the info could be useful, but since this is a problem that is likely occurring pretty regularly all you would need to do is get the information from v$sesstat and take a peek at the SQL the top writer sessions are issuing. You could also narrow down the suspects objects with table monitoring and seeing what your top insert/updater's are. I usually concentrate on top redo generators as my suspects in this case.

I suspect it will turn out that SQL issuing the most writes will also be doing the most of other attributes in V$SQL_AREA, be it CPU time, buffer gets etc...

In my case I take a snapshot with a script that (I think) does a better job than statspack. The top inserted/updated objects will come out in one section and then by looking at a snapshot of v$sql_area I can quickly see the SQL related to the inserts/updates. This is a little quicker and easier than kicking off a sql trace.

• Ethan

On 12/15/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> Yes, and they actually added that to v$sql in 10g in a new column "direct_writes", but I don't see why they don't track "db block changes" as well - seems like it would be sensible and useful.
>
> Thanks,
> Brandon
>

--
http://www.freelists.org/webpage/oracle-l