-
Title
Active Time Workload metrics for SQL Server and Oracle agents (using SQL PI) -
Description
How is the Active Time metric calculated when SQL PI is enabled?
.png )
-
Resolution
This is a complex set of calculations derived from numerous tables in the SQL PI repository and calculated from within the product.
The Active Time chart is calculated using the Active Time, CPU waits, and CPU time from the instance waits event summary. This is described on page sample queries in the SQL PI reference guide. These queries are similar to the manner of the queries that are used to build the Active Time chart (cpu_time, wait_log, wait_lock, etc)
The data is then combined from the historical data which is spread across 30 tables for each rollup depending on the range displayed in the page
For SQL Server
pass_instance_stat_fact_1m_YYYYMMDDHHHH (4 of these for 6 hours)
pass_instance_stat_fact_1h_pXX (30 of these)
pass_instance_stat_fact_6h_pXX (30 of these)
pass_instance_stat_fact_1d_pXX (30 of these)
- pass_instance_stat_fact_1w_pXX (30 of these)
For Oracle
spiora_instance_stat_fact_1m_YYYYMMDDHHHH (4 of these for 6 hours)
spiora_instance_stat_fact_1h_pXX (30 of these)
spiora_instance_stat_fact_6h_pXX (30 of these)
spiora_instance_stat_fact_1d_pXX (30 of these)
- spiora_instance_stat_fact_1w_pXX (30 of these)