|| at Sep 13, 2006 at 7:53 pm
You cannot relate writes to one particular SQL statement since they
are performed on a system-wide basis by dbwr and friends. Ditto with
redo. I think that the best you can do it to take a snapshot of
v$sesstat before and after your statement/package for the most relevant
statistics. And even so, I fear that for regular block-writes it would
not be very useful. Assume that you have several concurrent sessions
concurrently updating the same data block. How will you render unto
Caesar the things that are Caesar's?
How do we get solid numbers to determine how many physical writes a
specific sql or pl/sql package will perform? When we use 10046 I
believe it only gets physical reads because the dbwriter is
asynchronous? I have some concerns about using AWR for this.
Also, how do we get reliable redo numbers? When I use set autotrace on
and run the same insert multiple times the redo seems to vary by 20%