Oracle Scratchpad

June 17, 2007

Hints – again

Filed under: Hints — Jonathan Lewis @ 8:03 pm BST Jun 17,2007

How well do you know your hints? When you see this one /*+ parallel(t1 4) */ what, exactly, does it mean ? The answer may not be what you think – and if you don’t know exactly what a hint means, how can you make it “work properly” ?

Here’s a little test case, taken from a 10g session, to help things along:

create table t1 as
select
	rownum	id,
	rpad(rownum,10)	small_vc,
	rpad('x',100)	padding
from
	all_objects
where
	rownum <= 10000
;

alter table t1 add constraint t1_pk primary key(id);

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => null,
		block_sample 	 => true,
		method_opt 	 => 'for all columns size 1',
		degree		 => null,
		granularity      => 'default',
		cascade		 => true
	);
end;
/

select /*+ parallel(t1 4) */ count(*) from t1;

select * from v$pq_tqstat;

The output looks like this:

Table created.

Table altered.

PL/SQL procedure successfully completed.

  COUNT(*)
----------
     10000

1 row selected.

no rows selected

The query against the view v$pq_tqstat shows you the involvement of parallel execution slaves in this query – and it didn’t run parallel.

Has Oracle ignored the hint ? No, because the optimizer does not ignore hints (unless you hit a bug, or get the syntax wrong, or use the hint incorrectly). Here’s the 10053 trace file that proves the point.

SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  48.26  Resp: 13.41  Degree: 0
      Cost_io: 48.00  Cost_cpu: 1500000
      Resp_io: 13.33  Resp_cpu: 416667
  Access Path: index (index (FFS))
    Index: T1_PK
    resc_io: 7.00  resc_cpu: 1342429
    ix_sel: 0.0000e+000  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  7.23  Resp: 7.23  Degree: 1
      Cost_io: 7.00  Cost_cpu: 1342429
      Resp_io: 7.00  Resp_cpu: 1342429
  Access Path: index (FullScan)
    Index: T1_PK
    resc_io: 21.00  resc_cpu: 2149550
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 21.37  Resp: 21.37  Degree: 1
  Best:: AccessPath: IndexFFS  Index: T1_PK
         Cost: 7.23  Degree: 1  Resp: 7.23  Card: 10000.00  Bytes: 0

The optimizer considers three possible ‘single table access’ options.  Tablescan, index full scan, and index fast full scan.

But the hint we supplied is only relevant to the tablescan – and we can see it being used in the arithmetic above: the cost_io is the serial I/O cost of the tablescan, the resp_io is the ‘parallel response’ time for the query which is (cost_io / (0.9 * degree)).  (Unfortunately the degree appears as zero in this example, rather than 4).

Note: there are variations in this formula – in 9i the 0.9 doesn’t appear; and if you have  system statistics relating to parallel execution throughput, then there may be an adjustment to the cost if those statistics show that the requested degree exceeds the capability of the machine.

So check what’s happened: the optimizer worked out the cost of the tablescan using my hint (so the hint was obeyed), then worked out the cost of a serial full scan and fast full scan. The cheapest option after all this is the index fast full scan.

The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen.

Bonus: The index() hint tells Oracle to use an index, but does not specify whether to use a full scan or a range scan. Because of some changes in costing in 10g, there have been cases reported where the optimizer – after hinting – picked a full scan when a range scan was the user’s intended choice (see for example Metalink bug 4323868). Consequently Oracle Corp. has introduced two new hints in 10.2.0.3 to “disambiguate” the index use. These are: index_rs_asc() and index_rs_desc(), which make explicit demands for an index range scan ascending and descending respectively. It’s probably a good idea to use this option in all future code.

Correction  [Jan 2010]: I had originally claimed that there was a third “index range scan” hint: index_rs(). There are only two such hints – index_rs_asc() and index_rs_desc(), I’m not sure where I got the third one from. It’s possible I was thinking of the “index skip scan” hints of which there really are three – index_ss(), index_ss_asc() and index_ss_desc(), or it could be that glancing through the list of hints v$sql_hint I read index_rrs() – the hint used internally for parallel index fast full scans – as index_rs(). On the other hand, I have a simple test case in 10.2.0.3 where index_rs() is treated as if it were index_rs_asc().

28 Comments »

  1. Jonathan,

    So the proper index in your example would be “/*+ full(t1) parallel(t1 4) */”. Right?

    Since you mentioned different index hints and fast full index scan, it might be helpful for readers to point to the “index_ffs” hint as well.

    And one question if I may… Could you please elaborate on index_rs_asc(), index_rs_desc() and where there are useful? Index scan with sorted output?

    Thanks,
    Alex

    Comment by Alex Gorbachev — June 18, 2007 @ 3:06 am BST Jun 18,2007 | Reply

  2. [in response to Alex' comment]

    Just to clarify…I think you mean proper “hint” not “index” right Alex?

    Also, given the costs identified in the 10053 trace, I’d imagine the CBO is doing the “proper” thing by ignoring the parallel hint and going for the Index fast full scan route since it’s actually cheapest to do that.

    The example is a good one to illustrate a number of the points that Jonathan often makes…

    The CBO doesn’t ignore your hints – but you better be sure you understand the hints you’re giving it.

    Most people don’t understand hints – I certainly include myself of course!

    You need more hints than you think, e.g. also using the FULL one as you suggest would persuade the CBO to only consider the full table scan access path…but as I said, that doesn’t mean you will get the optmial execution plan.

    I wasn’t aware of those new hints – it certainly would be interesting to hear more on them from Jonathan…lets hope he can find some time to enlighten us.

    Cheers
    Jeff

    Comment by Jeff Moss — June 18, 2007 @ 6:38 am BST Jun 18,2007 | Reply

  3. Hi Jonathan,

    I see you are using hints in your selects very frequently. In this context I have one simple question:
    is it recommended to use hints or to let oracle do it self the best explain plan it can elaborate based on the current statistics
    and histograms?

    When every one knows that only an order by can guarantee an order of the output could you please let me know why Oracle is still adding hints like index_rs_asc() and index_rs_desc

    Thanks very much

    Mohamed

    Comment by Houri Mohamed — June 18, 2007 @ 6:54 am BST Jun 18,2007 | Reply

  4. Hi jonathan,

    If I may ask, how did you discover the 10.2.0.3 new hints index_rs etc. Is it documented?

    amit

    Comment by amit poddar — June 18, 2007 @ 12:11 pm BST Jun 18,2007 | Reply

  5. Thanks Jeff. The first line should have been “So the proper hints in your example would be “/*+ full(t1) parallel(t1 4) */”. Right?.

    Comment by Alex Gorbachev — June 18, 2007 @ 12:28 pm BST Jun 18,2007 | Reply

  6. I’m sitting in an airport waiting for a ‘plane, so this seems to be a good time for answering a few questions.
    Alex, yes, a full() or no_index() hint would be needed to stop the optimizer finding the alternative path. Of course it gets harder with multi-table joins.

    Mohamed, in the blog I show examples with hints quite frequently because I want to make sure that I can demonstrate a point consistently and easily. But if you read this entry and related articles you will notice that I don’t think it is a good idea to use hints in a production system unless there is no alternative. I would prefer to see, first, if a bad plan is a result of misleading statistics or an unlucky form of statement before slotting in some hints.

    Amit, I have hundreds of test cases for analysing particular problems. Some of them require me to review 10053 traces. The index_rs() hints just happened to show up in one such trace, and then I checked Metalink to see if it had any comment on why the new set had been created.

    Mohamed, although you can only guarantee the final order of the results by using an “order by” clause, it is still the case that the fastest way of generating the result set may include a specific strategy for using a specific index – hence the need for the hints. The thing that puzzles me about these hints is that (the last time I checked) index()and index_asc() were synonyms – they always did the same thing; similarly it looks like index_rs() and index_rs_asc() are also synonyms.

    Comment by Jonathan Lewis — June 18, 2007 @ 6:35 pm BST Jun 18,2007 | Reply

  7. Perhaps the reason for the index() and index_asc() hints is that there are cases where an index() hint could switch to doing a descending scan, if the optimizer thinks it’s a good idea? Or perhaps the names are just kept distinct in case something like that is implemented in the future?

    Comment by Mark J. Bobak — June 19, 2007 @ 7:30 am BST Jun 19,2007 | Reply

  8. Mark, I investigated this some time ago, and it did not seem to be the case. An index() hint always resulted in an ascending use of the index even when it was ridiculously expensive and a descending use was much cheaper. Hence my comment about synonyms.

    Comment by Jonathan Lewis — June 19, 2007 @ 6:57 pm BST Jun 19,2007 | Reply

  9. Great discussion.

    Comment by Mirjana — June 22, 2007 @ 11:58 am BST Jun 22,2007 | Reply

  10. [...] Lewis at Oracle Scratchpad asks “How well do you know your hints?” and illustrates with a 10g test case exactly what a hint [...]

    Pingback by Log Buffer #50: A Carnival of the Vanities for DBAs — Eye on Oracle — June 22, 2007 @ 3:42 pm BST Jun 22,2007 | Reply

  11. [...] degree and query plans Filed under: Uncategorized — tanelp @ 4:54 pm Reading the following article about PARALLEL hint by Jonathan Lewis made me remember a somewhat related gotcha with [...]

    Pingback by A gotcha with parallel index builds, parallel degree and query plans « Tanel Poder’s blog: Core IT for geeks and pros — June 23, 2007 @ 8:54 am BST Jun 23,2007 | Reply

  12. Hi Jonathan,
    Can you explain or point me to a source which explains more on this sentence
    “The parallel() hint does not tell Oracle to run a query in parallel, it tell the optimizer to use a certain scale factor in certain areas of its calculation”.
    What did you mean by certain scale factor in certain areas of its calcuations? How is the scale factor determined and what are the certain areas of calucation?

    Thank you
    – Kumar

    Comment by Kumar — April 1, 2008 @ 4:24 am BST Apr 1,2008 | Reply

  13. Kumar,

    As far as I know, there is no public documentation about this. To strip the content of my example to the minimum – assume you run a simple query in 9i that could use an index or do a tablescan against a single non-partitioned table, and include the hint /*+ parallel (tableX 6) */ in the query:

    The optimizer will calculate (a) the cost of a serial indexed acces path and (b) the cost of the tablescan divided by 6. The plan chosen would be whichever plan was the cheaper.

    So the optimizer has used my parallel value in the one area of the calculations where it’s legal to do so.

    Comment by Jonathan Lewis — April 1, 2008 @ 4:44 pm BST Apr 1,2008 | Reply

  14. [...] Hints again: do you know what they really mean. [...]

    Pingback by Rules for Hinting « Oracle Scratchpad — May 2, 2008 @ 8:13 am BST May 2,2008 | Reply

  15. Jonathan, your test case is only replayable when using “primary key” constraint. Parallel query plan is chosen with normal index or even with unique index. How do you interpret it? In this case, Oracle totally ignores the cost of index scan. Is this connected to consideration for “constraint”? or what else?

    Comment by Dion Cho — May 8, 2008 @ 7:02 am BST May 8,2008 | Reply

  16. Dion Cho,

    Primary key means – unique and not null.
    If you don’t have a not null constraint on the column the optimizer can’t assume that every row in the table appears in the index, so its only access path is a tablescan.

    Comment by Jonathan Lewis — May 8, 2008 @ 7:36 am BST May 8,2008 | Reply

  17. Thanks for the quick response. I can’t believe how i missed that. :-(

    Comment by Dion Cho — May 8, 2008 @ 7:44 am BST May 8,2008 | Reply

  18. [...] about INDEX_RS_ASC (from jonathanlewis’s blog) [...]

    Pingback by eagle’s home » Blog Archives » Outline不能正确使用的问题 — October 13, 2008 @ 3:20 pm BST Oct 13,2008 | Reply

  19. Jonathan,

    From your 10053 trace above, there are 2 index FFS access paths reported:

     Access Path: index (index (FFS))
       Index: T1_PK
       resc_io: 7.00  resc_cpu: 1342429
       ix_sel: 0.0000e+000  ix_sel_with_filters: 1
     Access Path: index (FFS)
       Cost:  7.23  Resp: 7.23  Degree: 1
         Cost_io: 7.00  Cost_cpu: 1342429
         Resp_io: 7.00  Resp_cpu: 1342429

    The one reported as index (index (FFS)) has the index and selectivities
    indicated. The one reported as index (FFS) doesn’t. What is the difference
    between these 2 access paths? I am also seeing similar index FFS access
    paths reported in my 10053 traces.

    Many thanks,
    Darian

    Comment by Darian — January 15, 2009 @ 1:22 am BST Jan 15,2009 | Reply

  20. Darian,
    I’ve just discoverd your comment – so I hope a late reply is better than none.

    The answer to your question is: I don’t know why there are two calculatons for the index fast full scan, but I think (or, rather, I am prepared to guess) that one of them is dumped by the code path that does the original serial cost calculation, and the other is dumped by the code path that does the parallel costing.

    In this case, the parallel costing calculation seems to be redundant because the degree stored in the data dictionary for the index is one, and there is no hint to tell Oracle to use parallelism on the index.

    There is an anomaly in using parallel_index hints – or declaring indexes parallel, by the way: unless the table has also been declared parallel (default, or a value other than 1) then the cost of the parallel index_ffs is not adjusted by the degree of parallelism specified (even in 11g)

    Comment by Jonathan Lewis — May 30, 2009 @ 9:59 am BST May 30,2009 | Reply

  21. [...] postings on hints: Hints – again Ignoring Hints What is a hint ? Taking a hint Comments [...]

    Pingback by Quiz Night « Oracle Scratchpad — October 3, 2009 @ 4:40 am BST Oct 3,2009 | Reply

  22. [...] Hints – Again “But the hint we supplied is only relevant to the tablescan – and we can see it being used in the arithmetic above: the cost_io is the serial I/O cost of the tablescan, the resp_io is the ‘parallel response’ time for the query which is (cost_io / (0.9 * degree)).” [...]

    Pingback by Parallel Reasoning « Charles Hooper's Oracle Notes — February 17, 2010 @ 6:04 am BST Feb 17,2010 | Reply

  23. [...] And perhaps that’s not surprising if you know what the PARALLEL hint does – it doesn’t force parallelism? [...]

    Pingback by SQL Patch III plus Parallel Baselines « OraStory — March 9, 2012 @ 12:09 pm BST Mar 9,2012 | Reply

  24. [...] Lewis has already written about this behavior from the angle of PARALLEL [...]

    Pingback by Alter session force parallel query doesn’t really force anything | Tanel Poder's blog: IT & Mobile for Geeks and Pros — March 20, 2013 @ 4:07 pm BST Mar 20,2013 | Reply

  25. [...] well do you know your hints? When Jonathan asks, it stays [...]

    Pingback by Latest data Industry news round up, Log Buffer #312 — March 22, 2013 @ 5:32 pm BST Mar 22,2013 | Reply

  26. Jonathan,

    Thanks for another good observation. However, when it comes to “hints”. my understanding has always been that it just a “suggestion” to the optimizer and, of course, it can an choose to ignore it. This is weather you know how to use it or not. There are a lot factors that are involved in the optimizer choosing a path.

    Comment by Lou — March 23, 2013 @ 6:22 pm BST Mar 23,2013 | Reply

    • Lou,

      It’s a common misconception; but from a simple philosophical point of view the optimizer can’t “choose” to do anything. If you want to suggest that the optimizer can “choose” to ignore a hint that means that given the same query, data, stats, and hints, it would use one execution path one day and a different execution path another day. It doesn’t.

      The optimizer may appear to ignore a hint, but the reason for that will be one of
      (a) you don’t know the rules that make the hint relevant, or
      (b) you’ve found a bug in the code, or
      (c) the access path analysis code took a path through its decision tree that didn’t pass through the code where your hint was relevant (which means you didn’t hint the query properly, of course).

      Comment by Jonathan Lewis — March 24, 2013 @ 8:01 am BST Mar 24,2013 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers