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

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

Share to

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      |             |       |       |       |        |      |            |