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().