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.


Actions

Information

8 responses

6 02 2011
Martin Preiss

Charles,
perhaps the “forst_rows_10″ hint suggested by Mr. B. would help … (or should it be “frost_rows_10″?)
Indeed a strange query: even the string concatenation is weird:

'DATAPOINT EXTENTS_LEFT '  || ' ' ||

And I have no idea how MIN(DSE.MAXEXTS – DSE.EXTENTS) could be of any interest.

(Sorry, I had to edit your comment – the original comment was blocked by the Akismet spam filter.)

6 02 2011
Charles Hooper

That reminds me. Before closing that thread, Mr. B. should have told the OP to add the FAST PERFORMANCE hint in front of the other hints, like this:

/*+ FAST PERFORMANCE  FROST_ROWS_10 */

As demonstrated here:

http://hoopercharles.wordpress.com/2011/01/15/adding-comments-to-sql-statements-improves-performance/#comment-2680

There is a very recent thread on the comp.databases.oracle.server Usenet group about hints, but it is too bad that not all RDBMS platforms support the FAST PERFORMANCE hints :-)

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6ba1d443103eabcd/4219a0a25edf1677

6 02 2011
Martin Preiss

a reasonable decision of the spam filter

6 02 2011
Niall Litchfield

Hi Charles

I too would be asking the purpose of this. It rather looks like do it yourself space management (am I about to run out of extents.) But I can’t see a) why you would do this and b) why you would do this 10k times.

Niall

6 02 2011
Charles Hooper

Niall,

You might be correct regarding the intended use of this SQL statement.

Something possibly interesting, an extension of my third bullet point at the end of this article. What if the segment described by this (returned by the main SELECT clause):

NVL(MIN(DSE.MAXEXTS - DSE.EXTENTS), 111)

Is not the same object as described by this (also returned by the main SELECT clause):

NVL(MIN(ROUND(DSE.MAXEXTS - DSE.EXTENTS) * 100 / DSE.MAXEXTS), 100)

This is definitely one of those SQL statements that makes people (at least me) stop and think for a couple of minutes. I am beginning to wonder if the real task is *not* to improve the performance of this SQL statement.

6 02 2011
Gary

A Google search throws up P$OBJ_EXCLUSION as part of a BMC monitoring tool. As such any changes to the SQL might be impractical.

I’d start by looking at how frequently this job is scheduled to run.

6 02 2011
Charles Hooper

Gary,

Thanks for performing the Google search – I tried, but initially did not search on that keyword. Good idea to check the scheduled run frequency – if this AWR report covers a one hour time period, the above SQL statement is executed an average of three times per second (by different sessions, obviously because of the 1.90 second average elapsed time for the SQL statement).

It appears that the OP is not alone, as the Google search found another company experiencing similar problems with a query that appears to be quite similar:

http://www.freelists.org/post/oracle-l/DB-Performance-Issue

If we ignore the fact that the other database has CURSOR_SHARING set to either FORCE or SIMILAR, it appears that BMC attempted to modify the program’s queries since the time of the post on the Oracle L list in 2007, so that the queries no longer specify the deprecated RULE hint.

The Google search also apparently found the manual for the product:
ftp://ftp.bmc.com/pub/patrol/patches/PKM_ORACLE/NT/8.9.00/PATROL%20for%20Oracle%20User%20Guide.pdf
On page 332 of the manual, we see that the programmers of this product are a bit too creative with extent size definitions:

28 02 2011
Burleson buys BMC ? « Oracle Scratchpad

[...] original article is back – with a note about the false DMCA claim. I can’t help noticing that the [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: