HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle SQL提示含义与示例 --- 分布式查询和并行提示

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2013-01-08 05:14:54

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

《Oracle 高性能SQL引擎剖析:Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充,帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。

分布式查询提示

DRIVING_SITE

语法:DRIVING_SITE([<远程表>])

描述:指示优化器选择那个数据库作为分布式查询中的驱动站点,即将语句放在该站点上执行。未指定参数时,采用本地数据库。

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

语法:REMOTE_MAPPED([<远程数据库链接>])

描述:在分布式查询中,指示优化器选择那个数据库的进行远程映射。作用和DRIVING_SITE类似。未指定参数时,采用本地数据库。

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

语法:OPAQUE_TRANSFORM            

描述:在分布式查询中,使用INSERT ... SELECT ... FROM语句从远程数据库查询数据插入本地数据库时,在远程数据库上执行的递归查询语句上会加上该提示,使得分布式数据库之间的兼容类型数据被透明传输。

(本地数据库,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.

 

(远程数据库,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"

并行查询提示

STATEMENT_QUEUING

语法:STATEMENT_QUEUING

描述:在自动并行度模式(11gR2特性)下,使语句在并行资源不足时,进入等待队列,等到能获取到资源时继续运行;

自动并行度模式下,当存在多个并行查询同时在运行时,如果并行资源不足那么加了该提示的语句就会进入队列等待,此时,通过监控视图V$SQL_MONITOR可以看到其状态为QUEUE

NO_STATEMENT_QUEUING

语法:NO_STATEMENT_QUEUING

描述:在自动并行度模式(11gR2特性)下,即使在并行资源不足时,也继续运行语句,这就可能会导致其它资源(如CPU)的争用与等待;

当参数“_parallel_statement_queuing”被设置为TRUE(默认为FALSE),只有加上该提示的语句在并行资源紧张时不会进入队列。

GBY_PUSHDOWN

语法:GBY_PUSHDOWN([<@查询块>])

描述:指示优化器在对并行查询进行代价估算时,考虑将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

语法:NO_GBY_PUSHDOWN([<@查询块>])

描述:禁止优化器在对并行查询进行代价估算时,将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

语法:HWM_BROKERED                

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

示例(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

语法:NO_QKN_BUFF                 

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

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

语法:PARALLEL_INDEX([查询块] <> <索引1> [<索引2> ...] <并行度>) 或者 PARALLEL_INDEX([查询块] <> (<索引字段列表1>) [(<索引字段列表2>) ...] <并行度>)

描述:指示优化器选择并行方式访问本地分区索引。并行度可以为数字,也可以为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

语法:NO_PARALLEL_INDEX([查询块] <> <索引1> [<索引2> ...]) 或者 NO_PARALLEL_INDEX([查询块] <> (<索引字段列表1>) [(<索引字段列表2>) ...])

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

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

语法:PQ_DISTRIBUTE([<@查询块>] <> <分发方式>) 或者 PQ_DISTRIBUTE([<@查询块>] <> <内分发方式> <外分发方式>)

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

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