|
Neil, "Buffer busy waits" is contention for a buffer (representing a specific version of a database block) within the Buffer Cache. So, in essence it is block contention and thus it is most likely something to do with the design of the tables and indexes supporting the application. A built-in bottleneck. On indexes, it could be the age-old problem of insertions into an index on a column with a monotonically-ascending data value (i.e. timestamps or sequence numbers) which tends to cause contention on the highest leaf node of the index. On tables, it might have to do with many concurrent insertions into a table in a freelist-managed tablespace where the table has only one freelist. It could also be due to a home-grown implementation of sequence-number generators (i.e. small table with one row, one column in which contains the "last value" of a sequence, etc) which lots of people use to avoid not being "portable across databases" which they think means not using Oracle sequences (yadda yadda yadda). I'd look for any SQL statement in the "SQL sorted by Elapsed Time" section of the AWR report which exhibits high elapsed time but relatively low CPU time, indicating a lot of wait time. Of course, there are something like 800 possible wait events in current releases of Oracle, of which "buffer busy waits" is only one, so this is just inference and not a direct causal connection to your problem. But, once I find such statements I'd check to see if they are accessing/manipulating tables within the CUBS_DATA tablespace, and then use "select * from table(dbms_xplan.display_awr('sql-id'))" to get the execution plan(s), and then look for something ineffective within the execution plan. You might find the script "sqlhistory.sql" helpful here as well, to get a "historical perspective" on the execution of the SQL statements over time, in case the buffer busy waits peaked at some point in the past, for example... Hope this helps... Tim Gorman consultant -> Evergreen Database Technologies, Inc. postal => P.O. Box 630791, Highlands Ranch CO 80163-0791 website => http://www.EvDBT.com/ email => Tim@xxxxxxxxx mobile => +1-303-885-4526 fax => +1-303-484-3608 Lost Data? => http://www.ora600.be/ for info about DUDE... Neil Kodner wrote: I'm having a hard time interpreting the Buffer Waits section of my AWR reports. Looking at the example at-- //www.freelists.org/webpage/oracle-l |
