HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Distributed Query And Parallel Query Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 05:14:54

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

Distributed Query Hints

DRIVING_SITE

Usage: DRIVING_SITE([<Remote Table>])

Description: Instructs the optimizer to choose the site of remote table as the driving site to execute the query.

HELLODBA.COM>exec sql_explain('select /*+ driving_site(rt) */count(*) from t_tables lt, t_tables@ora11r2 rt where lt.owner = rt.owner and lt.table_name = rt.table_name', 'TYPICAL NOTE');

... ...

 

Remote SQL Information (identified by operation id):

----------------------------------------------------

 

   3 - SELECT "OWNER","TABLE_NAME" FROM "T_TABLES" "A2" (accessing '!' )

 

Note

-----

   - fully remote statement

REMOTE_MAPPED

Usage: REMOTE_MAPPED([<Remote Database Link >])

Description: Instruct the optimizer to map the query via the database link.

HELLODBA.COM>exec sql_explain('select /*+ remote_mapped(ORA11R2) */count(*) from T_USERS@ORA11R2 u, t_tables t where t.owner=u.username', 'TYPICAL');

 

Remote SQL Information (identified by operation id):

----------------------------------------------------

 

   3 - SELECT "OWNER" FROM "T_TABLES" "A1" (accessing '!' )

 

Note

-----

   - fully remote statement

OPAQUE_TRANSFORM

Usage: OPAQUE_TRANSFORM            

Description: It appears in the internal SQL in remote server generated by the distributed query using the format of INSERT ... SELECT ... FROM.

Demo (Local DB, 10.2.0.4):

HELLODBA.COM>create table t_objects_dummy2 as select * from t_objects@ora11r2 where 1=2;

 

Table created.

 

HELLODBA.COM>exec sql_explain('insert into t_objects_dummy2 select * from t_objects@ora11r2','TYPICAL');

… …

 

Remote SQL Information (identified by operation id):

----------------------------------------------------

 

   1 - SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_I

       D","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMP

       ORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","LIO" FROM "T_OBJECTS"

       "T_OBJECTS" (accessing 'ORA11R2' )

 

HELLODBA.COM>insert into t_objects_dummy2 select * from t_objects@ora11r2;

 

72116 rows created.

 

(Remote DB, 11.2.0.1)

HELLODBA.COM>select sql_text from v$sqlarea where sql_text like '%OPAQUE_TRANSFORM%' and sql_text not like '%v$sqlarea%';

 

SQL_TEXT

-------------------------------------------------------------------------------------------------------

SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATE

D","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","NAMESPACE","EDITION_NAME","LIO" FROM "T_OBJECTS" "T_OBJECTS"

Parallel Query Hints

STATEMENT_QUEUING

Usage: STATEMENT_QUEUING

Description: In the auto parallel degree mode (11gR2 feature), if there is insufficient resource for parallel query, it will be pushed into waiting queue. We can query V$SQL_MONITOR to check the status of the statement.

NO_STATEMENT_QUEUING

Usage: NO_STATEMENT_QUEUING

Description: In the auto parallel degree mode (11gR2 feature), even if there is insufficient resource for parallel query, it will still runing.

Parameter “_parallel_statement_queuing” controls the parallel statement queuing feature.

GBY_PUSHDOWN

Usage: GBY_PUSHDOWN([<@Block>])

Description: 指示优化器在对并行查询进行代价估算时,考虑将GROUP BY操作推入并行服务进程的情况;

HELLODBA.COM>exec sql_explain('SELECT /*+ FULL(T) parallel(T DEFAULT) GBY_PUSHDOWN */ owner, table_name, COUNT (status) cnt FROM t_tables t GROUP BY owner, table_name', 'BASIC OUTLINE');

 

---------------------------------------------

| Id  | Operation                | Name     |

---------------------------------------------

|   0 | SELECT STATEMENT         |          |

|   1 |  PX COORDINATOR          |          |

|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |

|   3 |    HASH GROUP BY         |          |

|   4 |     PX RECEIVE           |          |

|   5 |      PX SEND HASH        | :TQ10000 |

|   6 |       HASH GROUP BY      |          |

|   7 |        PX BLOCK ITERATOR |          |

|   8 |         TABLE ACCESS FULL| T_TABLES |

---------------------------------------------

NO_GBY_PUSHDOWN

Usage: NO_GBY_PUSHDOWN([<@Block>])

Description: 禁止优化器在对并行查询进行代价估算时,将GROUP BY操作推入并行服务进程;

HELLODBA.COM>exec sql_explain('SELECT /*+ FULL(T) parallel(T DEFAULT) NO_GBY_PUSHDOWN */ owner, table_name, COUNT (status) cnt FROM t_tables t GROUP BY owner, table_name', 'BASIC OUTLINE');

 

--------------------------------------------

| Id  | Operation               | Name     |

--------------------------------------------

|   0 | SELECT STATEMENT        |          |

|   1 |  PX COORDINATOR         |          |

|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |

|   3 |    HASH GROUP BY        |          |

|   4 |     PX RECEIVE          |          |

|   5 |      PX SEND HASH       | :TQ10000 |

|   6 |       PX BLOCK ITERATOR |          |

|   7 |        TABLE ACCESS FULL| T_TABLES |

--------------------------------------------

HWM_BROKERED

Usage: HWM_BROKERED                

Description: 提示语句执行器在执行并行插入数据(或从其它表获取数据创建新表)时,使用高水位线查封器拆分高水位线,使得多个并行服务进程能共用一个扩展段。

示例(9i):

HELLODBA.COM>alter session enable parallel dml;

 

Session altered.

 

HELLODBA.COM>explain plan for insert /*+ append */ into t_objects_dummy select /*+ full(o) parallel(o 2)*/* from t_objects o;

 

Explained.

 

HELLODBA.COM>select plan_table_output from table(dbms_xplan.display(null, null, 'ALL'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT     |             | 32435 |  2945K|    22 |        |      |            |

|   1 |  LOAD AS SELECT      |             |       |       |       |        |      |            |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 63,00  | P->S | QC (RAND)  |

-------------------------------------------------------------------------------------------------

 

HELLODBA.COM>explain plan for insert /*+ append HWM_BROKERED */ into t_objects_dummy select /*+ full(o) parallel(o 2)*/* from t_objects o;

 

Explained.

 

HELLODBA.COM>select plan_table_output from table(dbms_xplan.display(null, null, 'ALL'));

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT     |             | 32435 |  2945K|    22 |        |      |            |

|   1 |  LOAD AS SELECT      |             |       |       |       |        |      |            |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 64,00  | P->S | QC (RAND)  |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 63,00  | P->S | QC (RAND)  |

|   1 |  LOAD AS SELECT      |             |       |       |       |        |      |            |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 64,00  | P->S | QC (RAND)  |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 63,00  | P->S | QC (RAND)  |

|   0 | INSERT STATEMENT     |             | 32435 |  2945K|    22 |        |      |            |

|   1 |  LOAD AS SELECT      |             |       |       |       |        |      |            |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 64,00  | P->S | QC (RAND)  |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 63,00  | P->S | QC (RAND)  |

|   1 |  LOAD AS SELECT      |             |       |       |       |        |      |            |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 64,00  | P->S | QC (RAND)  |

|   2 |   TABLE ACCESS FULL  | T_OBJECTS   | 32435 |  2945K|    22 | 63,00  | P->S | QC (RAND)  |

-------------------------------------------------------------------------------------------------

NO_QKN_BUFF

Usage: NO_QKN_BUFF                 

Description: 禁止优化器使用动态分配的内存

HELLODBA.COM>exec sql_explain('select /*+parallel(t 8) parallel(o 8) leading(t o) pq_distribute(o hash hash) NO_QKN_BUFF*/* from t_tables t, t_objects o where t.owner=o.owner and t.table_name=o.object_name and o.status=:A','BASIC');

 

---------------------------------------------

| Id  | Operation               | Name      |

---------------------------------------------

|   0 | SELECT STATEMENT        |           |

|   1 |  PX COORDINATOR         |           |

|   2 |   PX SEND QC (RANDOM)   | :TQ10002  |

|   3 |    HASH JOIN            |           |

|   4 |     PX RECEIVE          |           |

|   5 |      PX SEND HASH       | :TQ10000  |

|   6 |       PX BLOCK ITERATOR |           |

|   7 |        TABLE ACCESS FULL| T_TABLES  |

|   8 |     PX RECEIVE          |           |

|   9 |      PX SEND HASH       | :TQ10001  |

|  10 |       PX BLOCK ITERATOR |           |

|  11 |        TABLE ACCESS FULL| T_OBJECTS |

---------------------------------------------

PARALLEL_INDEX

Usage: PARALLEL_INDEX([查询块] <Table> <Index1> [<Index2> ...] <并行度>) or PARALLEL_INDEX([查询块] <Table> (<Index字段列表1>) [(<Index字段列表2>) ...] <并行度>)

Description: 指示优化器选择并行方式访问本地分区索引。并行度可以为数字,也可以为DEFAULT,使用系统默认并行度。

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) parallel_index(o t_objects_list_IDX1 2) */* from t_objects_list o where object_name like :A', 'BASIC');

 

--------------------------------------------------------------------

| Id  | Operation                            | Name                |

--------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                     |

|   1 |  PX COORDINATOR                      |                     |

|   2 |   PX SEND QC (RANDOM)                | :TQ10000            |

|   3 |    PX PARTITION LIST ALL             |                     |

|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_OBJECTS_LIST      |

|   5 |      INDEX RANGE SCAN                | T_OBJECTS_LIST_IDX1 |

--------------------------------------------------------------------

NO_PARALLEL_INDEX

Usage: NO_PARALLEL_INDEX([查询块] <Table> <Index1> [<Index2> ...]) or NO_PARALLEL_INDEX([查询块] <Table> (<Index字段列表1>) [(<Index字段列表2>) ...])

Description: 禁止优化器选择并行方式访问本地分区索引

HELLODBA.COM>alter index t_objects_list_IDX1 parallel(degree 2);

 

Index altered.

 

HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) no_parallel_index(o t_objects_list_IDX1)*/* from t_objects_list o where object_name like :A', 'BASIC');

 

------------------------------------------------------------------

| Id  | Operation                          | Name                |

------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                     |

|   1 |  PARTITION LIST ALL                |                     |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_OBJECTS_LIST      |

|   3 |    INDEX RANGE SCAN                | T_OBJECTS_LIST_IDX1 |

------------------------------------------------------------------

PQ_DISTRIBUTE

Usage: PQ_DISTRIBUTE([<@Block>] <Table> <分发方式>) or PQ_DISTRIBUTE([<@Block>] <Table> <内分发方式> <外分发方式>)

Description: 指定并行查询中并行收、发进程直接的分发方式;

HELLODBA.COM>exec sql_explain('select /*+parallel(o 2)*/* from t_objects o where exists (select /*+hash_sj PQ_DISTRIBUTE(t HASH HASH)*/1 from t_tables t where o.owner = t.owner and o.object_name = t.table_name)', 'BASIC');

 

-------------------------------------------------------

| Id  | Operation                       | Name        |

-------------------------------------------------------

|   0 | SELECT STATEMENT                |             |

|   1 |  PX COORDINATOR                 |             |

|   2 |   PX SEND QC (RANDOM)           | :TQ10002    |

|   3 |    HASH JOIN RIGHT SEMI BUFFERED|             |

|   4 |     BUFFER SORT                 |             |

|   5 |      PX RECEIVE                 |             |

|   6 |       PX SEND HASH              | :TQ10000    |

|   7 |        INDEX FULL SCAN          | T_TABLES_PK |

|   8 |     PX RECEIVE                  |             |

|   9 |      PX SEND HASH               | :TQ10001    |

|  10 |       PX BLOCK ITERATOR         |             |

|  11 |        TABLE ACCESS FULL        | T_OBJECTS   |

-------------------------------------------------------

PARALLEL

Usage: PARALLE([[<@Block>] <Table>] [<并行度>])

Description: 指示优化器对查询块或者对象使用并行查询。其中,当中指定整条语句为并行查询(未指定查询块和表)时,并行度可以为MANUAL,AUTO,DEFAULT或者指定数字;指定某个表时,并行度可以为DEFAULT或者指定数字,

HELLODBA.COM>exec sql_explain('SELECT /*+ parallel(u default) */* from t_users u', 'BASIC');

 

-----------------------------------------

| Id  | Operation            | Name     |

-----------------------------------------

|   0 | SELECT STATEMENT     |          |

|   1 |  PX COORDINATOR      |          |

|   2 |   PX SEND QC (RANDOM)| :TQ10000 |

|   3 |    PX BLOCK ITERATOR |          |

|   4 |     TABLE ACCESS FULL| T_USERS  |

-----------------------------------------

NO_PARALLEL

Usage: NO_PARALLEL(<Table>)

Description: 禁止优化器并行查询表

HELLODBA.COM>alter table t_objects_dummy parallel(degree 2);

 

Table altered.

 

HELLODBA.COM>exec sql_explain('SELECT /*+ no_parallel */* from t_objects_dummy o', 'BASIC');

Plan hash value: 2093122083

 

---------------------------------------------

| Id  | Operation         | Name            |

---------------------------------------------

|   0 | SELECT STATEMENT  |                 |

|   1 |  TABLE ACCESS FULL| T_OBJECTS_DUMMY |

---------------------------------------------

SHARED

Usage: SHARED(<Table> [并行度])

Description: 指示优化器共享指定表的并行度。如果指定并行度,则和PARALLEL提示作用相同。

HELLODBA.COM>alter table t_objects_dummy parallel(degree 2);

 

Table altered.

 

HELLODBA.COM>alter table t_objects parallel(degree 8);

 

Table altered.

 

HELLODBA.COM>exec sql_explain('select /*+ full(o) full(d) shared(d) */count(*) from t_objects_dummy d, t_objects o where o.owner=d.owner and o.object_name = d.object_name', 'BASIC COST');

 

------------------------------------------------------------------

| Id  | Operation                 | Name            | Cost (%CPU)|

------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                 |   719   (1)|

|   1 |  SORT AGGREGATE           |                 |            |

|   2 |   PX COORDINATOR          |                 |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10002        |            |

|   4 |     SORT AGGREGATE        |                 |            |

|   5 |      HASH JOIN            |                 |   719   (1)|

|   6 |       PX RECEIVE          |                 |   231   (0)|

|   7 |        PX SEND HASH       | :TQ10000        |   231   (0)|

|   8 |         PX BLOCK ITERATOR |                 |   231   (0)|

|   9 |          TABLE ACCESS FULL| T_OBJECTS       |   231   (0)|

|  10 |       PX RECEIVE          |                 |   486   (0)|

|  11 |        PX SEND HASH       | :TQ10001        |   486   (0)|

|  12 |         PX BLOCK ITERATOR |                 |   486   (0)|

|  13 |          TABLE ACCESS FULL| T_OBJECTS_DUMMY |   486   (0)|

------------------------------------------------------------------

 

HELLODBA.COM>exec sql_explain('select /*+ full(o) full(d) shared(o) */count(*) from t_objects_dummy d, t_objects o where o.owner=d.owner and o.object_name = d.object_name', 'BASIC COST');

 

------------------------------------------------------------------

| Id  | Operation                 | Name            | Cost (%CPU)|

------------------------------------------------------------------

|   0 | SELECT STATEMENT          |                 |  1437   (1)|

|   1 |  SORT AGGREGATE           |                 |            |

|   2 |   PX COORDINATOR          |                 |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10002        |            |

|   4 |     SORT AGGREGATE        |                 |            |

|   5 |      HASH JOIN            |                 |  1437   (1)|

|   6 |       PX RECEIVE          |                 |   463   (1)|

|   7 |        PX SEND HASH       | :TQ10000        |   463   (1)|

|   8 |         PX BLOCK ITERATOR |                 |   463   (1)|

|   9 |          TABLE ACCESS FULL| T_OBJECTS       |   463   (1)|

|  10 |       PX RECEIVE          |                 |   973   (1)|

|  11 |        PX SEND HASH       | :TQ10001        |   973   (1)|

|  12 |         PX BLOCK ITERATOR |                 |   973   (1)|

|  13 |          TABLE ACCESS FULL| T_OBJECTS_DUMMY |   973   (1)|

------------------------------------------------------------------

NOPARALLEL

Usage: NOPARALLEL([[<@Block>] <Table>])

Description: 禁止优化器对查询块或者对象使用并行查询。和NO_PARALLEL作用基本相同。

参见NO_PARALLEL示例。

PQ_MAP

Usage: PQ_MAP(<Table>)

Description: 未知。可能是用于并行查询的提示。

PQ_NOMAP

Usage: PQ_NOMAP(<Table>)

Description: 未知。可能是用于并行查询的提示。

PRESERVE_OID

Usage: PRESERVE_OID                

Description: 未知。可能是用于并行查询的提示。

SYS_PARALLEL_TXN

Usage: SYS_PARALLEL_TXN            

Description: 未知。可能是用于并行查询的递归调用语句上的。

CUBE_GB

Usage: CUBE_GB                     

Description: 未知。可能是用于GROUP BY CUBE并行查询的内部递归查询

该提示直接使用会导致10g10.2.0.4)在解析提示时在后台发生ORA-00600错误,但不会终止语句运行。

ORA-600: internal error code, arguments: [prsHintQbLevel-1], [890], [], [], [], [], [], []

发生类似情况的提示还有:CUBE_GB/GBY_CONC_ROLLUP/PIV_GB/PIV_SSF/RESTORE_AS_INTERVALS/SAVE_AS_INTERVALS/SCN_ASCENDING/MODEL_DONTVERIFY_UNIQUENESS/TIV_GB/TIV_SSF

GBY_CONC_ROLLUP

Usage: GBY_CONC_ROLLUP             

Description: 未知。可能是用于GROUP BY ROLLUP并行查询的内部递归查询

PIV_GB

Usage: PIV_GB                      

Description: 未知。出现在GROUP BY并行查询的内部递归查询语句上

示例(9i):

HELLODBA.COM>select /*+qb_name(Q2) full(o2) parallel(o2 2)*/ owner, status, count(object_name) from t_objects o2 where owner like 'D%' group by owner, status;

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=3 Bytes=42)

   1    0   SORT* (GROUP BY) (Cost=11 Card=3 Bytes=42)                              :Q17865001

   2    1     SORT* (GROUP BY) (Cost=11 Card=3 Bytes=42)                            :Q17865000

   3    2       TABLE ACCESS* (FULL) OF 'T_OBJECTS' (Cost=6 Card=3379 Bytes=47306)  :Q17865000

   1 PARALLEL_TO_SERIAL            SELECT /*+ CIV_GB */ A1.C0,A1.C1,COUNT(SYS_O

                                   P_CSR(A1.C2,0)) FROM :Q17865000 A1 GROUP BY

                                   A1.C0,A1.C1

   2 PARALLEL_TO_PARALLEL          SELECT /*+ PIV_GB */ A1.C0 C0,A1.C1 C1,SYS_O

                                   P_MSR(COUNT(*)) C2 FROM (SELECT /*+ NO_EXPAN

                                   D ROWID(A2) */ A2."OWNER" C0,A2."STATUS" C1

                                   FROM "T_OBJECTS" PX_GRANULE(0, BLOCK_RANGE,

                                   DYNAMIC)  A2 WHERE A2."OWNER" LIKE 'D%') A1

                                   GROUP BY A1.C0,A1.C1

   3 PARALLEL_COMBINED_WITH_PARENT

TIV_GB

Usage: TIV_GB                      

Description: 未知。出现在并行查询的内部递归查询语句上

TIV_SSF

Usage: TIV_SSF                     

Description: 未知。出现在并行查询的内部递归查询语句上

PIV_SSF

Usage: PIV_SSF                     

Description: 未知。出现在并行查询的内部递归查询语句上

RESTORE_AS_INTERVALS

Usage: RESTORE_AS_INTERVALS        

Description: 未知。出现在并行查询的内部递归查询语句上

SAVE_AS_INTERVALS

Usage: SAVE_AS_INTERVALS           

Description: 未知。出现在并行查询的内部递归查询语句上

SCN_ASCENDING

Usage: SCN_ASCENDING               

Description: 未知。出现在并行查询的内部递归查询语句上

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat