Exception Advisor provides graphical comparisons of elapsed and CPU times.
Exception Advisor also compares other performance indicators if they are present in current or historical records for the exception statement.
The following example shows CPU time and elapsed time comparisons:
Example
Historical comparison of CPU time with Elapsed time
CPU time Elapsed time Average History level Statement Type Calls Opens
+==================+ +==================+ ======== =================== ============== ===== =====
|> | |>>>>> | 3.794222 MONTHLY 2009-07 CURSOR 56 8
|>> | |>>>>> | 3.866916 WEEKLY 2009-11-29 CURSOR 54 9
|>> | |>>>>> | 3.866916 MONTHLY 2009-12 CURSOR 54 9
|>>> | |> | 2.275612 MONTHLY 2010-01 CURSOR 60 10
|>>> | |> | 2.275612 WEEKLY 2010-01-17 CURSOR 60 10
|>>>>>>>>>>>>>>>>>>| |>>>>>>>>>>>>>>>>>>| 9.851790 XCPT: 10 occurrences OPEN 10 10
+==================+ +==================+
The following example shows a CPU time exception comparison of historical CPU time with SYNCIO WAIT PERCENTAGE and GETPAGE COUNT. Note the correlation between high CPU time and high GETPAGE COUNT.
Example
Historical comparison of CPU time with SYNCIO WAIT PERCENTAGE
CPU time SYNCIO WAIT PERCENTA Value History level Statement Type Calls Opens
+==================+ +==================+ ===== ==================== ============== ===== =====
|> | |>> | 1.484 MONTHLY 2009-07 CURSOR 56 8
|>> | |>>>> | 2.924 WEEKLY 2009-11-29 CURSOR 54 9
|>> | |>>>> | 2.924 MONTHLY 2009-12 CURSOR 54 9
|>>> | |> | 1.226 MONTHLY 2010-01 CURSOR 60 10
|>>> | |> | 1.226 WEEKLY 2010-01-17 CURSOR 60 10
|>>>>>>>>>>>>>>>>>>| |>>>>>>>>>>>>>>>>>>| 10.109 XCPT: 10 occurrences OPEN 10 10
+==================+ +==================+
Historical comparison of CPU time with GETPAGE COUNT
CPU time GETPAGE COUNT Average History level Statement Type Calls Opens
+==================+ +==================+ =========== ==================== ============== ===== =====
|> | |> | 3894.53571 MONTHLY 2009-07 CURSOR 56 8
|>> | |>>> | 7730.83333 WEEKLY 2009-11-29 CURSOR 54 9
|>> | |>>> | 7730.83333 MONTHLY 2009-12 CURSOR 54 9
|>>> | |>>> | 7730.73333 MONTHLY 2010-01 CURSOR 60 10
|>>> | |>>> | 7730.73333 WEEKLY 2010-01-17 CURSOR 60 10
|>>>>>>>>>>>>>>>>>>| |>>>>>>>>>>>>>>>>>>|46355.00000 XCPT: 10 occurrences OPEN 10 10
+==================+ +==================+
The following example shows a historical comparison of CPU time with LOCK WAIT PERCENTAGE.
Tip
This example shows no correlation between CPU time and LOCK WAIT PERCENTAGE. That would suggest that locking problems are not a significant contributor to the statement exception CPU usage.
Example
Historical comparison of CPU time with LOCK WAIT PERCENTAGE
CPU time LOCK WAIT PERCENTAGE Value History level Statement Type Calls Deletes
+==================+ +==================+ ===== ======================== ============== ===== =======
|> | |> | 0.000 MONTHLY 2009-09 DELETE 9 3
|> | |> | 0.000 WEEKLY 2009-09-27 DELETE 3 1
|> | |>>>>>>>>>>> | 0.005 MONTHLY 2009-10 DELETE 63 21
|> | |> | 0.000 WEEKLY 2009-10-04 DELETE 12 4
|> | |> | 0.000 BASELINE 2009-10-10-1300 DELETE 3 1
|> | |> | 0.000 WEEKLY 2009-10-11 DELETE 18 6
|> | |> | 0.000 WEEKLY 2009-10-18 DELETE 21 7
|>> | |>>>>>>>>>>>>>>>>>>| 0.008 WEEKLY 2009-10-25 DELETE 21 7
|>>>> | |> | 0.007 WEEKLY 2009-11-01 DELETE 20 6
|>>>> | |>>>>> | 0.002 MONTHLY 2009-11 DELETE 59 19
|>>>> | |>>>>>>>>>> | 0.004 WEEKLY 2009-11-08 DELETE 12 4
|>>>> | |>>>>> | 0.002 WEEKLY 2009-11-15 DELETE 18 6
|>>>>> | |> | 0.000 WEEKLY 2009-11-22 DELETE 9 3
|>>>>> | |>>>>> | 0.002 WEEKLY 2009-11-29 DELETE 6 2
|>>>>> | |>>> | 0.001 MONTHLY 2009-12 DELETE 9 3
|>>>>> | |> | 0.000 WEEKLY 2009-12-06 DELETE 3 1
|>>>>>>> | |> | 0.000 MONTHLY 2010-01 DELETE 3 1
|>>>>>>>>>>>>>>>>>>| |> | 0.000 XCPT:01-21-08.40.28.9272 DELETE 1 1
+==================+ +==================+