Showing posts with label hints. Show all posts
Showing posts with label hints. Show all posts

Wednesday, February 20, 2013

QB_NAME hint query block name length limitation

Oracle 10g introduced the QB_NAME hint that can come handy in case hints need to be applied to more complex statements, in particular when possibly multiple layers of views / subqueries are involved.

Jonathan Lewis has a older blog post that describes more details.

Just in case you wonder why sometimes apparently the QB_NAME hint - along with all other hints that refer to the assigned query block name - seems to be ignored: One possible reason is that it looks like there is an undocumented length limitation of the query block names that can be assigned - 20 characters seem to be the maximum possible (I haven't checked the effect of multi-byte database character sets).

Consider this simple example:

drop table t1;

purge table t1;

create table t1
as
select
        rownum as id
      , rpad('x', 100) as filler
from
        dual
connect by
        level <= 100
;

exec dbms_stats.gather_table_stats(null, 't1')

By default Oracle 11.2.0.1 unnests the correlated subquery and runs an anti-join for the following simple query:

select 
       *
from
       t1
where
       not exists
       (
         select 
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| T1   |
|   3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$1
   3 - SEL$5DA710D3 / T1_INNER@SEL$2

Notice the aliases and query block names assigned, like SEL$1, SEL$2 etc.

Using the QB_NAME hint the NO_UNNEST hint can be applied to the correlated subquery from the outer query block:

select /*+ no_unnest(@nested_query) */
       *
from
       t1
where
       not exists
       (
         select /*+ qb_name(nested_query) */
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  FILTER            |      |
|   2 |   TABLE ACCESS FULL| T1   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1       
   2 - SEL$1        / T1@SEL$1
   3 - NESTED_QUERY / T1_INNER@NESTED_QUERY

The correlated subquery is now executed using a FILTER operator as requested. Notice the aliases and query block names, in particular for the instance of T1 at operation id = 3

Now if I repeat the same query, but use a longer query block name, the hints are effectively ignored and the default unnesting takes place again:

select /*+ no_unnest(@nested_query_longer_name) */
       *
from
       t1
where
       not exists
       (
         select /*+ qb_name(nested_query_longer_name) */
                null
         from
                t1 t1_inner
         where
                t1_inner.id = t1.id
       );

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN ANTI    |      |
|   2 |   TABLE ACCESS FULL| T1   |
|   3 |   TABLE ACCESS FULL| T1   |
-----------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T1@SEL$1
   3 - SEL$5DA710D3 / T1_INNER@SEL$2

Notice how the default aliases and query block names get used although explicitly hinted.

It is probably rather uncommon to use such lengthy query block names, nevertheless it can be puzzling when hitting such an undocumented limitation.

Saturday, April 30, 2011

Things worth to mention and remember (III) - Parallel Execution Control 3

Continuing from the previous part of this series I'll cover in this post some further basics about parallel execution control:

- Keep in mind that there are two classes of parallel hints: PARALLEL and PARALLEL_INDEX. One is about the costing of parallel full table / index fast full scans, the other one about costing (driving) parallel index scans, which are only possible with partitioned indexes (PX PARTITION granule vs. PX BLOCK granule)

- The same applies to the opposite, NO_PARALLEL (or NOPARALLEL in older releases) and NO_PARALLEL_INDEX. It is in particular important to realize that specifying a NO_PARALLEL hint only tells the optimizer to not evaluate a parallel full table scan / index fast full scan, however it might still evaluate a parallel index scan if feasible and therefore still might go parallel. This is particularly important if you are under the impression that using the NO_PARALLEL hint will ensure that no parallel slaves will be used by the statement at execution time. This is not entirely correct - it still might go for a parallel index scan. You would need to use different means, either use a NO_PARALLEL_INDEX hint in addition to NO_PARALLEL, use the OPT_PARAM hint to set the optimizer parameter PARALLEL_EXECUTION_ENABLED to FALSE at statement level or disabling PARALLEL QUERY on session level.

- Furthermore note that the PARALLEL and PARALLEL_INDEX hints merely tell the optimizer to evaluate the costing of a plan with the determined parallel degree. If the optimizer however finds a serial plan with a lower cost, it will prefer that serial execution plan despite the usage of the parallel hints. This even applies to a session "forced" to use parallel query via ALTER SESSION FORCE PARALLEL QUERY.

- Remember that with DML / CTAS DDL there are actually (at least) two potential parallel parts involved: The Create Table/DML part and the query part (except for a single row INSERT INTO...VALUES DML). Both parts independently from each other can be performed in parallel or serial, so you could end up with:

- Serial DDL/DML + serial query
- Serial DDL/DML + parallel query
- Parallel DDL/DML + serial query
- Parallel DDL/DML + parallel query

Whether some combinations make sense or not is a different question, however from a technical point of view all of them are possible.

You therefore need to carefully check which part you want to execute in parallel and control it accordingly (and remember the fact that parallel DML needs to be explicitly enabled in the session). Check the (actual) execution plan to ensure you get the desired parallel execution.

Friday, March 11, 2011

Things worth to mention and remember (II) - Parallel Execution Control 2

Continuing from the previous installment of this series I'll cover in this post some of the inevitable classics regarding Parallel Execution Control. So forgive me if you're bored by the repetition of known facts - however I still see these things too often used incorrectly, therefore I decided: This is worth to mention and remember!

- Up to and including version 10.2 PARALLEL (without any parameters) and NOLOGGING are valid keywords only in DDL commands

- Applies to all versions: NOLOGGING can not be used as a hint. It can only be specified as part of DDL, for example ALTER INDEX ... REBUILD PARALLEL NOLOGGING.
The only DML operation that can be performed as NOLOGGING operation is a direct-path insert (that can be enabled for parallel DML). For that to work you need to set the NOLOGGING attribute on table / partition level independently from the direct-path insert, in can not be specified as part of the direct-path insert DML command.

- Applies to all versions: For NOLOGGING DDL operations the NOLOGGING keyword needs to be be used at the correct position in the DDL statement:


CREATE TABLE T PARALLEL NOLOGGING AS SELECT ...


A mistake often seen, because it doesn't raise an error in many cases:


CREATE TABLE T PARALLEL AS SELECT ... FROM TAB NOLOGGING


Here NOLOGGING is merely an alias for TAB therefore syntactically correct but not a NOLOGGING operation

Note that most production systems deemed to hold critical data nowadays run in FORCE LOGGING mode anyway (for example due to standby database(s) requiring the full generation of redo), so you probably don't want to bother too much with NOLOGGING anyway.

- Up to and including version 10.2: If you want to use PARALLEL as a hint as part of query / DML statements you need to specify an alias/object_name as parameter, otherwise the hint is invalid. So this is invalid and therefore does nothing:


SELECT /*+ PARALLEL */ ... FROM TABLE T


This is a valid PARALLEL hint for alias T:


SELECT /*+ PARALLEL(T) */ ... FROM TABLE T


You can optionally specify the parallel degree after the alias: PARALLEL(T 16) or PARALLEL(T DEFAULT). If you don't specify a degree DEFAULT will be used which is PARALLEL_THREADS_PER_CPU * CPU_COUNT * INSTANCE_COUNT (not considering additional rules like adaptive multi-user limitations, resource manager plans etc.) and not the (parallel) degree specified on object level (that gets used without any explicit PARALLEL hint).

- Starting with version 11.1 (undocumented) and 11.2 (documented): Release 11g supports now so-called "statement-level" PARALLEL (and NO_PARALLEL) hints, so above invalid syntax IS actually valid from version 11g on which might lead to some interesting side-effects for those that used the hint incorrectly so far and migrate to 11g.

The following rules for statement level PARALLEL hints seem to apply:

- /*+ PARALLEL */ seems to effectively mean PARALLEL(AUTO) (see below) but use at least a degree of 2 for costing and do not consider serial costs for operations that can be run in parallel. The description in the manual seems to be wrong that says that the statement is always executed in parallel. The usual rule applies that a serial plan with a lower cost will be favored if available

- /*+ PARALLEL(DEFAULT) is mentioned in the manual but seems not to be valid in my tests

- /*+ PARALLEL(<degree>) */ Use the defined DOP, where <degree> is integer

- From 11.2 on: /*+ PARALLEL(MANUAL) */ Use the degree defined on object level and therefore seems to be a bit redundant - why use a hint that results in the same as not using the hint (apart from the DISABLE PARALLEL QUERY / PARALLEL hint quirk mentioned here)

- From 11.2 on: /*+ PARALLEL(AUTO) */ Use the auto DOP policy introduced in 11.2 including serial costing of operations that can be run in parallel (the only difference to the "statement level" PARALLEL hint without a parameter I can see)

By the way, the NO_PARALLEL hint can now also be used on "statement level" and seems to effectively prevent PARALLEL and PARALLEL_INDEX costing. It seems to correspond to the (undocumented) hint OPT_PARAM('parallel_execution_enabled', 'false'). By undocumented I mean that this parameter to the recently documented OPT_PARAM hint is not documented officially.

Finally the "statement level" (no)parallel hints override any "object-level" parallel (or no_parallel...) hints.