Oracle Scratchpad

February 3, 2016

Hinting

Filed under: Hints,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 1:04 pm GMT Feb 3,2016

This is just a little example of thinking about hinting for short-term hacking requirements. It’s the answer to a question that came up on the Oracle-L listserver  a couple of months ago (Oct 2015) and is a convenient demonstration of a principle that can often (not ALWAYS) be applied as a response to the problem: “I can make this query work quickly once, how do I make it work quickly when I make it part of a join ?”

The question starts with this query, which returns “immediately” for any one segment:


SELECT DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,
       MAX(DE.BYTES) LARGEST_EXTENT_BYTES
FROM dba_extents DE
WHERE 1=1
  AND DE.OWNER           = <owner>
  AND DE.SEGMENT_NAME    = <segment_name>
  AND DE.segment_type    = <segment_type>
  AND DE.tablespace_name = <tablespace_name>
  AND DE.partition_name  = <max_partition_name>
GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME
;

But the email then goes on to explain: “I’ve got a table of values that I need to use as a list of inputs for this query, but when I do the join it takes ages to complete; how do I make the join run quickly?”

Here’s the initial code:


WITH SEGMENT_LIST AS
(
  select * from (
   SELECT /*+ materialize cardinality(100) */
           owner, segment_name, segment_type, tablespace_name,
           MAX(partition_name) MAX_PARTITION_NAME
   FROM my_custom_table
   GROUP BY owner, segment_name, segment_type, tablespace_name
  ) where rownum < 2
)
SELECT
       DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,
       MAX(DE.BYTES) LARGEST_EXTENT_BYTES
FROM SEGMENT_LIST SL, dba_extents DE
WHERE 1=1
  AND DE.OWNER           = SL.OWNER
  AND DE.SEGMENT_NAME    = SL.SEGMENT_NAME
  AND DE.segment_type    = SL.segment_type
  AND DE.tablespace_name = SL.tablespace_name
  AND DE.partition_name  = SL.max_partition_name
GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME

What we’ve got is a query where the user’s reference table generates a list of segments (the rownum < 2 was a temporary test) and we want the detail query to run for each segment identified. The “for each segment” gives us a clue that what we want to see is a simple nested loop join, driven by the factored subquery, with the very efficient query above running as the “second table of the nested loop”.

What I failed to notice at the time is that the /*+ materialize */ hint was in the wrong place, it should have been placed after the outer (i.e. very first) select, and it’s possible that if it had been in the right place the user would have got the plan they wanted – especially in the later versions of Oracle. As it was I suggested that we merely need to tell the optimizer to:

Visit the “tables” in the order (segment_list, dba_extents), and do a nested loop into (dba_extents), but since both segment_list and dba_extents were views we needed to stop Oracle from trying to merge them and play silly games with distinct aggregate placement, etc. by including directives that the views should not be merged, but then we might need to explain to Oracle that it would have to push the join predicate between segment_list and dba_extents inside the dba_extents view.

In other words, a list of 4 hints, as shown below:


WITH SEGMENT_LIST AS
(
  select * from (
   SELECT /*+ materialize cardinality(100) */
           owner, segment_name, segment_type, tablespace_name,
           MAX(partition_name) MAX_PARTITION_NAME
   FROM my_custom_table
   GROUP BY owner, segment_name, segment_type, tablespace_name
  ) where rownum < 2
)
SELECT /*+
        leading(sl de)
        no_merge(sl)
        no_merge(de)
        push_pred(de)
        */
       DE.TABLESPACE_NAME, DE.OWNER,DE.SEGMENT_NAME,
       MAX(DE.BYTES) LARGEST_EXTENT_BYTES
FROM SEGMENT_LIST SL, dba_extents DE
WHERE 1=1
  AND DE.OWNER           = SL.OWNER
  AND DE.SEGMENT_NAME    = SL.SEGMENT_NAME
  AND DE.segment_type    = SL.segment_type
  AND DE.tablespace_name = SL.tablespace_name
  AND DE.partition_name  = SL.max_partition_name
GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME

According to a follow-up email, this was sufficient.  The OP had actually tried variations on the leading() and use_nl() hints – but without the no_merge() hint the optimizer was probably rewriting the SQL in a way that put the hints out of context. It’s worth noting that the /*+ materialize */ hint is in the wrong place – it should be after the first appearance of the SELECT keyword in the factored subquery – and that probably added to the difficulty of getting the desired execution plan.

For a production system I’d probably want to do something a little more sophisticated in terms of stability once I’d got the plan I wanted – but this looks like a DBA query used to run an ad hoc report, so perhaps this solution is good enough for the current requirement.

 

4 Comments »

  1. I notice you’re using old-fashioned, Ansi 89 style table joins which have been deprecated for a long time. Is there a reason you don’t using modern join syntax/

    Comment by Iain — February 4, 2016 @ 12:20 pm GMT Feb 4,2016 | Reply

  2. Iain,

    In this particular case I was simply copying the supplied code and making a minimal edit. In general I write to whatever standard the client uses.

    For many blog notes I use old style because one or more of the following apply:

    (a) many of my examples are cut-n-paste from OTN – where lots of people still use it
    (b) many of my examples were written initially to demonstrate a point to clients who still use it
    (c) many of my examples are variations of models created before the newer style because popular (i.e. another source of cut-n-paste)
    (d) habit – my fingers type faster than my brain can intercept.
    (e) Oracle still transforms (most) ANSI-new to Oracle-original before optimising and there’s little point in introducing an extra level of uncertainty when investigating an anomly. (One of my standard checks, that gets applied fairly early on in the sequence, is that a problem persists even after rewriting using the original Oracle syntax.)

    Comment by Jonathan Lewis — February 5, 2016 @ 12:22 pm GMT Feb 5,2016 | Reply

  3. Sorry for being ignorant possibly, but i dont understand the usage of materialize together with rownum and the need for scalar subquery factoring. My first thought would be a simple rewrite:

    SELECT
           DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME,
           MAX(DE.BYTES) LARGEST_EXTENT_BYTES
    FROM  dba_extents DE
    WHERE (DE.tablespace_name, DE.OWNER, DE.SEGMENT_NAME, DE.segment_type, DE.partition_name)
    in (-- may be select * from rownum < 2 outside
       select
          SL.tablespace_name, SL.OWNER, SL.SEGMENT_NAME, SL.segment_type,  MAX(sl.partition_name) max_partition_name
       FROM my_custom_table sl
       GROUP BY owner, segment_name, segment_type, tablespace_name
    )
    GROUP BY DE.TABLESPACE_NAME, DE.OWNER, DE.SEGMENT_NAME
    

    Am i totally wrong?

    Comment by chris — February 5, 2016 @ 12:36 pm GMT Feb 5,2016 | Reply

  4. Chris,

    No, you’re not totally wrong.

    We can ignore the rownum bit – as it said in the post, that was there as a temporary test.

    Subquery factoring is often just a cosmetic thing that might make a complex query easier to comprehend, and that’s particularly true when (as in this case) the subquery that has been factored out is used only once. It’s then a potential benefit that the result set from the subquery can be generated and written to disc, though that’s pointless in this case, and I guess the OP probably tried materializing the subquery to work around the problems of the way Oracle tried to transform his query.

    Your rewrite, of course, is just another way of expressing the requirement – but it’s likely that the optimizer would simply unnest your IN subquery to produce the join that was giving the OP a problem; and even if that doesn’t happen on your version of Oracle with your data set it might still happen on the OP’s version with the OP’s data set.

    The point of the post was that IF you have that type of problem then isolating the two pieces with no_merge hints then dictating a nested loop join with join predicate pushdown could be an effective and robust strategy for addressing the problem.

    Comment by Jonathan Lewis — February 5, 2016 @ 1:06 pm GMT Feb 5,2016 | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.