DW jobs running long ....
I'm seeing rather lengthy run of the Star DW job. We have JTS which started with 2 applications (CCM/QM) but several new CCM have been added over the last month.
Run time has gone from < 1h to almost 7h. I'm looking at db2 performance (db2 top) and I'm seeing poor access plans on some queries (which unfortunately is querying what appears to be one of the largest (cardinality-wise) tables in the
database.
Any suggestions ?
Run time has gone from < 1h to almost 7h. I'm looking at db2 performance (db2 top) and I'm seeing poor access plans on some queries (which unfortunately is querying what appears to be one of the largest (cardinality-wise) tables in the
database.
Estimated Cost = 3978.298584
Estimated Cardinality = 0.000000
( 2) Access Table Name = RIDW.F_REQUEST_TPLN_METRICS ID = 7,116
| #Columns = 3
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 17
( 1) | | Return Data to Application
| | | #Columns = 3
( 1) Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1.53802e-24
n/a
RETURN
( 1)
3978.3
|
1.53802e-24
n/a
TBSCAN
( 2)
3978.3
|
811513
n/a
Table:
RIDW
F_REQUEST_TPLN_METRICS
Any suggestions ?
2 answers
Almost 1h increase in last 5 runs...
Status
Data Collection Job
Start Time
End Time
Time Taken
Failed Star Nov 20, 2011 3:22 AM Nov 20, 2011 10:28 AM 7 hours, 5 minutes
Succeeded Star Nov 19, 2011 3:36 AM Nov 19, 2011 10:31 AM 6 hours, 55 minutes
Succeeded Star Nov 18, 2011 3:52 AM Nov 18, 2011 10:30 AM 6 hours, 37 minutes
Succeeded Star Nov 17, 2011 4:13 AM Nov 17, 2011 10:37 AM 6 hours, 23 minutes
Succeeded Star Nov 16, 2011 4:27 AM Nov 16, 2011 10:38 AM 6 hours, 10 minutes
Who knows anything about this ? What would I assign as Filed Against on a Work Item to get this some attention ?
Status
Data Collection Job
Start Time
End Time
Time Taken
Failed Star Nov 20, 2011 3:22 AM Nov 20, 2011 10:28 AM 7 hours, 5 minutes
Succeeded Star Nov 19, 2011 3:36 AM Nov 19, 2011 10:31 AM 6 hours, 55 minutes
Succeeded Star Nov 18, 2011 3:52 AM Nov 18, 2011 10:30 AM 6 hours, 37 minutes
Succeeded Star Nov 17, 2011 4:13 AM Nov 17, 2011 10:37 AM 6 hours, 23 minutes
Succeeded Star Nov 16, 2011 4:27 AM Nov 16, 2011 10:38 AM 6 hours, 10 minutes
Who knows anything about this ? What would I assign as Filed Against on a Work Item to get this some attention ?
I'm seeing rather lengthy run of the Star DW job. We have JTS which started with 2 applications (CCM/QM) but several new CCM have been added over the last month.
Run time has gone from < 1h to almost 7h. I'm looking at db2 performance (db2 top) and I'm seeing poor access plans on some queries (which unfortunately is querying what appears to be one of the largest (cardinality-wise) tables in the
database.
Estimated Cost = 3978.298584
Estimated Cardinality = 0.000000
( 2) Access Table Name = RIDW.F_REQUEST_TPLN_METRICS ID = 7,116
| #Columns = 3
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 17
( 1) | | Return Data to Application
| | | #Columns = 3
( 1) Return Data Completion
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
1.53802e-24
n/a
RETURN
( 1)
3978.3
|
1.53802e-24
n/a
TBSCAN
( 2)
3978.3
|
811513
n/a
Table:
RIDW
F_REQUEST_TPLN_METRICS
Any suggestions ?