Hints – again

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:

rem
rem     Script:         ignore_hint_03.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2006
rem

create table t1 as
select
        rownum  id,
        rpad(rownum,10) small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 10000i -- > comment to avoid wordpress format issue
;

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

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1',
                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.

Lagniappe

The index() hint tells Oracle to use an index but does not specify whether to use a full scan, a range scan or an index skip 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 these 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().