February 6, 2011 (Updated February 25, 2011)
I occasionally read various forums on the Internet including a couple of Usenet groups (comp.databases.oracle.server, comp.databases.oracle.misc), OTN forums (Database – General, SQL and PL/SQL), Oracle-L, AskTom, and a handful of other forums. I don’t participate on all of the forums, but I try to help when possible on a couple of those forums.
I saw a SQL tuning request on one of the forums that caught my eye. The original poster (OP) identified a SQL statement with the help of an AWR report, and he needed to tune that SQL statement. The identified SQL statement was consuming the most elapsed time (19,307 seconds total, 1.90 seconds per execution, 48.30% of DB time), and the same SQL statement was also identified as consuming the second highest amount of CPU time (1,063 seconds). A summarization of the advice provided by responders in the thread includes:
- Check out these links (one of which is for a book that I reviewed a couple of months ago).
- Start with hints, including those that change the OPTIMIZER_MODE, those that adjust the dynamic sampling, those that change the join type, and those that change which indexes are used.
- Analyze the data dictionary.
I think that I would first start by trying to determine the intended result of the query. What is that query supposed to show, and why are we executing it 10,176 times in the AWR reporting period? To begin that process, I might try to reformat the SQL statement into my standardized format, something like this (changing upper/lower case, spacing, and aliases):
SELECT /*NORULE */ 'DATAPOINT EXTENTS_LEFT ' || ' ' || NVL(MIN(DSE.MAXEXTS - DSE.EXTENTS), 111) || CHR(10) || 'DATAPOINT EXTENTS_LEFT_PCT' || ' ' || ROUND(NVL(MIN(ROUND(DSE.MAXEXTS - DSE.EXTENTS) * 100 / DSE.MAXEXTS), 100),0) BPB FROM (SELECT DS.HEADER_FILE FILE#, DS.HEADER_BLOCK BLOCK#, DS.EXTENTS, DS.MAX_EXTENTS MAXEXTS, ST.TS#, SU.USER# FROM DBA_SEGMENTS DS, SYS.TS$ ST, SYS.USER$ SU WHERE ST.NAME=DS.TABLESPACE_NAME AND SU.NAME=DS.OWNER AND SEGMENT_TYPE NOT IN ('SPACE HEADER','CACHE')) DSE, (SELECT NAME, TS#, ONLINE$ FROM SYS.TS$) TS, (SELECT TABLESPACE_NAME, CONTENTS FROM DBA_TABLESPACES) DT, (SELECT TS#, FILE#, BLOCK# FROM P$OBJ_EXCLUSION WHERE TS# IS NOT NULL AND FILE# IS NOT NULL AND BLOCK# IS NOT NULL) OEB, (SELECT TS# FROM P$OBJ_EXCLUSION WHERE OBJECT_TYPE = 'TABLE' AND FILE# IS NULL AND BLOCK# IS NULL AND USER# IS NULL) OETS, (SELECT USER# FROM P$OBJ_EXCLUSION WHERE USER# IS NOT NULL) OEU WHERE DSE.MAXEXTS > 0 -- CACHE SEGMENT HAS MAXEXTS = 0 AND TS.TS# > 0 AND DSE.TS# = TS.TS# AND TS.ONLINE$ = 1 AND DSE.TS# = OEB.TS#(+) AND OEB.TS# IS NULL AND DSE.FILE# = OEB.FILE#(+) AND OEB.FILE# IS NULL AND DSE.BLOCK# = OEB.BLOCK#(+) AND OEB.BLOCK# IS NULL AND DSE.TS# = OETS.TS#(+) AND OETS.TS# IS NULL AND DSE.USER# = OEU.USER#(+) AND OEU.USER# IS NULL AND TS.NAME = DT.TABLESPACE_NAME AND DT.CONTENTS = 'PERMANENT';
While the query begins with an interesting comment, we can ignore that for now. What is the next step:
- Why is the query accessing both SYS.TS$ and DBA_TABLESPACES?
- Why are some of the restrictions (such as TS.TS# and TS.ONLINE, DT.CONTENTS) that are placed on the tablespaces not included in the WHERE clauses in the inline views?
- Why does the query specify MIN(DSE.MAXEXTS – DSE.EXTENTS) with no GROUP BY clause?
- Why does the query perform an implicit NUMBER to VARCHAR conversion in the data returned to the client?
- The inline views that I aliased with names beginning with OE are apparently designed to provide exclusion lists for the objects in DBA_SEGMENTS, excluding by TS#, BLOCK# (HEADER_BLOCK), and USER#. Why is the query not retrieving a distinct list of each type of item to be excluded, possibly from a materialized view?
What steps would you take to help the OP? Would you just tell the OP that in a “Top 5” type of report that there will always be something in the top two spots?
Edit February 25, 2011:
Donald K. Burleson, apparently realizing what it means to file a false DMCA claim under penalty of perjury, did not file a lawsuit to back up his DMCA claim to copyright ownership of the modified SQL statement that I posted on February 6, 2011 in this article. His false DMCA claim held hostage this article for a total of 17 days, during which time the article was not available for readers of this blog (for the record, I completely understand and agree with WordPress’ handing of this matter, where their processes require taking DMCA challenged articles offline for two weeks to allow the true copyright holder sufficient time to file a lawsuit). False DMCA claims from Donald K. Burleson against my blog articles will not be tolerated, and this article will serve as evidence of past abuse, if necessary.