Every so often a DSS query that usually takes 10 minutes ends up taking over an hour. (or one that takes an hour never seems to finish)
Why would this happen?
When investigating the DSS query, perhaps with wait event tracing, one finds that the query which is doing full table scans and should be doing large multi-block reads and waiting for “db file scattered read” is instead waiting for single block reads, ie “db file sequential read”. What the heck is going on?
Sequential reads during a full table scan scattered read query is a classic sign of reading rollback and reading rollback can make that minute(s) full table scan take hours.
What can happen especially after over night jobs, is that if an overnight job fails to finished before the DSS query is run and if that overnight job does massive updates without committing till then end, then the DSS query will have to rollback any changes made by the updates to the tables the DSS query is accessing.
How do we quickly identify if this our issue?
ASH is good at identify it. On the other hand it’s often impractical to whip up from scratch an ASH query and that’s where ashmasters on Github comes in. This ASH query and others are on Github under ashmasters.
For this case specifically see:
Here is the output (slight different format than in the github repository) of a query I used in my Oracle Performance classes
AAS SQL_ID % OBJ TABLESPACE
----- ------------- --- --------------- ----------
.18 0yas01u2p9ch4 6 ITEM_PRODUCT_IX SOEINDEX
6 ORDER_ITEMS_UK SOEINDEX
88 ITEM_ORDER_IX SOEINDEX
.32 6v6gm0fd1rgrz 6 MY_BIG_Table SOEDATA
94 UNDO UNDOTBS1
i.e. 95% of the second SQL_ID’s i/o was coming from UNDO. The reads will be single block reads and tremendously slow down the full table scans.
Comments