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 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 tell 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 tell Oracle to use an index, but does not specify whether that the usage should be 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 would have been a more intelligent choice. Consequently Oracle Corp. has introduced three new hints in 10.2.0.3 to “disambiguate” the index use. These are: index_rs(), index_rs_asc(), index_rs_desc(), which make explicit demands for an index range scan. It’s probably a good idea to use this option in all future code.