Mining AWR

In the previous post I queried AWR one table at the time. In this post I will share a query combining several AWR tables. The idea came from post Workload characterization using DBA_HIST tables and kSar by Karl Rao; I’m fan. Combining AWR data from several tables over time can show trends and it can help to correlate metrics. I may refer back to this data in the future – it has disk latency, parsing, and OS load, which will come in handy.

For today’s post I selected these metrics:

  • app_tx/s number of execution of the main sql in batch
  • DBCPU_sec  dba_hist_sys_time_model ‘DB CPU’
  • BUSY_TIME_S  dba_hist_osstat ‘BUSY_TIME’
  • PARSE_CPU_S  dba_hist_sysstat ‘parse time cpu’
  • SQL_CPU_SEC  dba_hist_sqlstat ‘CPU_TIME_DELTA’, CPU by executing sql-s…not all sql-s, future post candidate, remind me

mining_awr
You may need to zoom in to see the details in mining_awr.jpg or in excel mining_awr.xls
Here is the query mining_awr.txt

Observations:

  • The periodic bumps before 4/23 for both DB and OS CPU were from overnight batches – CPU calmed down at business hours
  • The sum of CPU and parse CPU by executing SQLs flattens out at around 10000 seconds, each data point is 15 minutes (we have 15 min snaps), therefore we are using around 11 CPU seconds each second (=10000/15/60)
  • There are periods on the graph when we have no parse related CPU and executing SQLs use all available CPU capacity
  • But CPU used by the DB never goes higher than 11 seconds even when we have batch, parsing, and other SQLs…do we have a CPU bottleneck?
  • CPU used by the DB is relatively small compared to the available OS CPU capacity: we have 128 CPUs, the biggest spike during batches is around 90000 seconds…for 15 minutes that translates to 100 CPU seconds used and at the OS level may look as if the CPU is 78% busy (=100/128).
  • Whenever DB CPU spikes there is a magnified similar spike  on OS CPU – we have a clear correlation between the two
What do you think?
What is using the rest of the CPU the OS is showing?
How is it possible the machine is only 50% busy and the DB can’t seem to get more CPU?
So far we have more questions than answers…read on!
Advertisements