PARALLEL hint

Hints – again

Filed under: Hints — Jonathan Lewis @ 8:03 pm GMT 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:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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:

1

2

3

4

5

6

7

8

9

10

11

12

13

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

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 scanor 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 whereindex_rs() is treated as if it were index_rs_asc().

Related

Alter session force parallel query doesn’t really force anything

by TANEL PODER posted on MARCH 20, 2013

Jonathan Lewis has already written about this behavior from the angle of PARALLEL hints.

I’m writing a similar article just because the word FORCE in the ALTER SESSION FORCE PARALLEL QUERY syntax. Force should mean that some behavior would always happen (when possible), right? Let’s test:

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;Table created.SQL> CREATE INDEX i ON t(owner);Index created.SQL> @gts tGather Table Statistics for table t...PL/SQL procedure successfully completed.

Now let’s “force” the parallel query in my session, run the query and check the execution plan:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;Session altered.SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';SUM(OBJECT_ID)--------------     979900956SQL> @x---------------------------------------------------------------------------| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)|---------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |        |       |   186 (100)||   1 |  SORT AGGREGATE              |      |      1 |    12 |            ||   2 |   TABLE ACCESS BY INDEX ROWID| T    |   6741 | 80892 |   186   (0)||*  3 |    INDEX RANGE SCAN          | I    |   6741 |       |    18   (0)|---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("OWNER" LIKE 'S%')       filter("OWNER" LIKE 'S%')

The output shows a regular, serial execution plan!

Hmm, let’s increase the “forced” parallelism from 2 to 3 and run exactly the same query again:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;Session altered.SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';SUM(OBJECT_ID)--------------     979900956SQL> @x------------------------------------------------------------------------------------------------------| Id  | Operation              | Name     | E-Rows |E-Bytes| Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT       |          |        |       |   128 (100)|        |      |            ||   1 |  SORT AGGREGATE        |          |      1 |    12 |            |        |      |            ||   2 |   PX COORDINATOR       |          |        |       |            |        |      |            ||   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |    12 |            |  Q1,00 | P->S | QC (RAND)  ||   4 |     SORT AGGREGATE     |          |      1 |    12 |            |  Q1,00 | PCWP |            ||   5 |      PX BLOCK ITERATOR |          |   6741 | 80892 |   128   (0)|  Q1,00 | PCWC |            ||*  6 |       TABLE ACCESS FULL| T        |   6741 | 80892 |   128   (0)|  Q1,00 | PCWP |            |------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   6 - access(:Z>=:Z AND :Z<=:Z)       filter("OWNER" LIKE 'S%')

Now the query will get a parallel plan!

The reason for this behavior is that the FORCE parallel query syntax doesn’t really force Oracle to use a parallel plan, but rather just reduces optimizer cost estimates for full table scans (the higher the parallelism, the lower the FTS costs – Jonathan already has details about this in his blog entry, so I won’t replicate this). But the optimizer is still free to choose some other, non-parallel execution plan if that has a lower cost than the best parallel one!

So what happened above is that with “forced” parallel degree 2, the parallel full table scan plan must have had a higher cost than the serial index range scan (186), but once I increased the parallelism “factor” to 3, then the final cost of the parallel full table scan plan ended up being lower (128) than the best serial plan found.

This is a good example showing that both the PARALLEL hints and the FORCE PARALLEL session settings really just adjust a narrow set of optimizer cost computation inputs and don’t really fix the resulting execution plan. If you really want to fix an execution plan, you need to tie optimizer “hands” in every aspect with a full set of hints just like the stored profiles do. That way, even if there is a lower cost plan available, the optimizer doesn’t know about it as you’ve prohibited it from doing any calculations other than your hints direct it to.

Note that when testing this, your mileage may vary, depending on how much data you have in your test table (or rather in the optimizer stats for that table) plus system stats.

NB! After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 16-20 November & 14-18 December 2015, so sign up now if you plan to attend this year!

related posts