Tag Archives: Statspack

Statspack quiz

I’ve a level 5 Statspack report from a real production 11.2.0.2 database with the following Top 5 Timed events section:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log buffer space                                    28       1,396  49870   41.0
buffer busy waits                                   51       1,049  20567   30.8
CPU time                                                       320           9.4
log file parallel write                          4,011         159     40    4.7
log file sync                                    3,275         153     47    4.5

The complete report (without SQL and several other sections) is here. Can you explain what & why is wrong in this Top?
I must admit I don’t have the definitive answer (probably yet), but I have partial explanation of the issue as well as several ideas.

Load profile

I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers for last 60 or 15 seconds. I wanted to write a script to do this for a long time. Here it is.

Continue reading