Script Request -- Specific Buffer Gets / Disk Reads

Listers,

For version 7.3.4.3, and with regards to shared SQL, does anyone have a script that that shows the specific, not average, number of buffer gets and disks reads for each user and their execution of the SQL statement?

For example, an SQL statement is executed every 30 minutes extracting SQL statements, the user(s) executing it, physical reads, buffer gets, etc for SQL statements exceeding 500,000 buffer gets. In cases of commonly shared and used SQL statements, you may see it show up, for example, in the output 3 times with 3 different user names, each reporting the same number of buffer gets and physical reads. They told me the SQL statement hits a couple of V$ views and a client created view, owned by sys, hitting various X$ objects. The buffer gets and physical reads are *averages*, computed by dividing the total by the executions.

Well, they asked me if I had a script that would break out the "real" numbers for each user. It has been years since I've been a DBA (V5, some V6); so, I don't really know my way around the V$ and X$ stuff like a DBA would. But, I told them I would look into it and ask around.

FWIW, SQL statements seldom exceed the limit; but, on occasion, they will see where one "pig" execution of a statement "lifts" other executions above the threshold due to the averaging. The inverse could also be true. Due to using an average, you could have resource un-intensive executions bring down and hide a "pig" execution. This is what they are wanting to work around.

Regards, Received on Sat Oct 21 2000 - 09:09:35 CDT