SQL Performance Problem, AWR Reports Query is #1 in Elapsed Time, #2 in CPU Time – How Would You Help?

6 02 2011

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.








Follow

Get every new post delivered to your Inbox.

Join 143 other followers