This page outlines the WA to deactivate some of the metrics that use this SP, DIAG_ALL_ANA_XLSQL_TOTAL in the local base.
When you are facing a performance problem while running snapshot, and looking at the DSS_History table for local you find that the snapshot is stuck at executing DIAG_ALL_ANA_XLSQL_TOTAL which is still being executed or has taken the maximum time to execute
For example:
1)The below shows the it took 4 days to execute the SP .
"End DIAG_ALL_ANA_XLSQL_TOTAL -3 61019 7660 2";"2014-01-21 20:21:12.508";2402
"Start DIAG_ALL_ANA_XLSQL_TOTAL -3 61019 7660 2";"2014-01-17 14:49:28.344";2401
2)The below example shows that the last execution in the DSS_History table is stuck at this particular SP and has never ended
"Start DIAG_ALL_ANA_XLSQL_TOTAL -3 61019 7660 2";"2014-01-17 14:49:28.344";2401
Release | Yes/No |
|---|---|
| 8.3.x |  |
| 8.2.x | |
| 8.1.x | |
| 8.0.x | |
| 7.3.x | |
| 7.2.x (x<=4) |  |
| 7.0.x (x<=21) | |
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server | |
| CSS3 | |
| CSS2 | |
| CSS1 | |
- Run the snapshot.
- Observe that it is stuck for unacceptable time (say 4 days as in the example)
- Run the below query on the local base to find out the step being executed or stuck is at this SP.
select * from <LOCAL>.DSS_HISTORY order by ACTION_ID
The WA is to disable the below metrics using the particular SP and causing the performance problem when computing metric
- "Never use SQL queries with a cartesian product on XXL tables"
- "Avoid SQL queries on XXL tables with implicit conversions in the WHERE clause"
- "Avoid SQL queries on XXL tables that no index can support (AllTechno)"
- These metrics should be disabled from assessment model and then KB and CB to be synchronized to run the snapshot again.
These metrics will not be executed and will be missing from the dashboard.
This SP has been optimized and the performance problem is planned to be fixed in 7.2.5 & 7.0.22 version.
This was initially reported in 7.0.19.
