Oracle 12c 新SQL提示
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
Oracle 12c中引入了许多新特性，其中部分是和SQL相关的特性．而一些新的SQL提示也随着这些新特性被引入．
enable_parallel_dml
Syntax: enable_parallel_dml
Description: Enable parallel dml. Same effect as "alter session enable parallel dml"SQL??
- HelloDBA.com> create table t_obj as select * from dba_objects;
- HelloDBA.com> create table t_tab as select * from dba_tables;
- HelloDBA.com> exec sql_explain('delete /*+enable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');
- Plan hash value: 409221376
- ----------------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
- ----------------------------------------------------------------------------------------------------------------------------
- | 0 | DELETE STATEMENT | | 3160 | 175K| 44 (0)| 00:00:01 | | | |
- | 1 | PX COORDINATOR | | | | | | | | |
- | 2 | PX SEND QC (RANDOM) | :TQ10003 | 3160 | 175K| 44 (0)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
- | 3 | DELETE | T_OBJ | | | | | Q1,03 | PCWP | |
- | 4 | PX RECEIVE | | 3160 | 175K| 44 (0)| 00:00:01 | Q1,03 | PCWP | |
- | 5 | PX SEND HASH (BLOCK ADDRESS) | :TQ10002 | 3160 | 175K| 44 (0)| 00:00:01 | Q1,02 | P->P | HASH (BLOCK|
- |* 6 | HASH JOIN RIGHT SEMI BUFFERED| | 3160 | 175K| 44 (0)| 00:00:01 | Q1,02 | PCWP | |
- | 7 | PX RECEIVE | | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,02 | PCWP | |
- | 8 | PX SEND HASH | :TQ10000 | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | S->P | HASH |
- | 9 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
- | 10 | TABLE ACCESS FULL | T_TAB | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | SCWP | |
- | 11 | PX RECEIVE | | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,02 | PCWP | |
- | 12 | PX SEND HASH | :TQ10001 | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | P->P | HASH |
- | 13 | PX BLOCK ITERATOR | | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | PCWC | |
- | 14 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | PCWP | |
- ----------------------------------------------------------------------------------------------------------------------------
disable_parallel_dml
Syntax: disable_parallel_dml
Description: Disable parallel dml. Same effect as "alter session disable parallel dml"SQL??
- HelloDBA.com> alter session enable parallel dml;
- HelloDBA.com> exec sql_explain('delete /*+disable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');
- Plan hash value: 1357806520
- ------------------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
- ------------------------------------------------------------------------------------------------------------------
- | 0 | DELETE STATEMENT | | 3160 | 175K| 460 (1)| 00:00:01 | | | |
- | 1 | DELETE | T_OBJ | | | | | | | |
- | 2 | PX COORDINATOR | | | | | | | | |
- | 3 | PX SEND QC (RANDOM) | :TQ10001 | 3160 | 175K| 460 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
- |* 4 | HASH JOIN RIGHT SEMI | | 3160 | 175K| 460 (1)| 00:00:01 | Q1,01 | PCWP | |
- | 5 | PX RECEIVE | | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,01 | PCWP | |
- | 6 | PX SEND BROADCAST | :TQ10000 | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
- | 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
- | 8 | TABLE ACCESS FULL| T_TAB | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | SCWP | |
- | 9 | PX BLOCK ITERATOR | | 92299 | 2794K| 430 (0)| 00:00:01 | Q1,01 | PCWC | |
- | 10 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 430 (0)| 00:00:01 | Q1,01 | PCWP | |
- ------------------------------------------------------------------------------------------------------------------
USE_CUBE
Sytntax: USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... )
Description:When the right-hand side of the join is a cube, the USE_CUBE hint instructs the optimizer to join each specified table with another row source using a cube join. If the optimizer decides not to use the cube join based on statistical analysis, then you can use USE_CUBE to override that decision.
Note: Sample schema comes from oracle 11.2 BI sample codes.SQL??
- HelloDBA.com> create view cube_view as select * from table(cube_table('GLOBAL.UNITS_CUBE'));
- HelloDBA.com> create view time_dim_view as select * from global.time_dim;
- HelloDBA.com> create view channel_dim_view as select * from global.channel_dim;
- HelloDBA.com> create view customer_dim_view as select * from global.customer_dim;
- HelloDBA.com> create view product_dim_view as select * from global.product_dim;
- HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
- HelloDBA.com> exec sql_explain('select /*+use_cube(t cn cm cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');
- Plan hash value: 1674841133
- ------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 316K| 401M| 19 (48)| 00:00:01 |
- |* 1 | HASH JOIN | | 316K| 401M| 19 (48)| 00:00:01 |
- | 2 | TABLE ACCESS FULL | TIME_DIM | 120 | 28680 | 3 (0)| 00:00:01 |
- |* 3 | CUBE JOIN | | 585K| 609M| 14 (50)| 00:00:01 |
- | 4 | MERGE JOIN CARTESIAN | | 183 | 71919 | 6 (0)| 00:00:01 |
- | 5 | TABLE ACCESS FULL | CHANNEL_DIM | 3 | 237 | 2 (0)| 00:00:01 |
- | 6 | BUFFER SORT | | 61 | 19154 | 4 (0)| 00:00:01 |
- | 7 | TABLE ACCESS FULL | CUSTOMER_DIM | 61 | 19154 | 1 (0)| 00:00:01 |
- | 8 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 896M| 5 (80)| 00:00:01 |
- ------------------------------------------------------------------------------------------
NO_USE_CUBE
Syntax:NO_USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The NO_USE_CUBE hint instructs the optimizer to exclude cube joins when joining each specified table to another row source using the specified table as the inner table.
Note: Sample schema comes from oracle 11.2 BI sample codes.SQL??
- HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=true;
- HelloDBA.com> exec sql_explain('select /*+no_use_cube(cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');
- Plan hash value: 2364382396
- ------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 316K| 401M| 21 (48)| 00:00:01 |
- |* 1 | HASH JOIN | | 316K| 401M| 21 (48)| 00:00:01 |
- | 2 | TABLE ACCESS FULL | TIME_DIM | 120 | 28680 | 3 (0)| 00:00:01 |
- |* 3 | HASH JOIN | | 585K| 609M| 16 (50)| 00:00:01 |
- | 4 | MERGE JOIN CARTESIAN | | 183 | 71919 | 6 (0)| 00:00:01 |
- | 5 | TABLE ACCESS FULL | CHANNEL_DIM | 3 | 237 | 2 (0)| 00:00:01 |
- | 6 | BUFFER SORT | | 61 | 19154 | 4 (0)| 00:00:01 |
- | 7 | TABLE ACCESS FULL | CUSTOMER_DIM | 61 | 19154 | 1 (0)| 00:00:01 |
- | 8 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 896M| 7 (72)| 00:00:01 |
- ------------------------------------------------------------------------------------------
CUBE_AJ
Syntax:CUBE_AJ ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CUBE_AJ hint instructs the optimizer to clude cube anti joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.SQL??
- HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
- HelloDBA.com> exec sql_explain('select * from product_dim_view pd where not exists (select /*+cube_aj*/1 from cube_view cb where cb.product=pd.item_id)','typical');
- Plan hash value: 2520340146
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 36 | 8208 | 11 (64)| 00:00:01 |
- |* 1 | CUBE JOIN ANTI | | 36 | 8208 | 11 (64)| 00:00:01 |
- | 2 | TABLE ACCESS FULL | PRODUCT_DIM | 36 | 7488 | 2 (0)| 00:00:01 |
- | 3 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 25M| 5 (60)| 00:00:01 |
- ----------------------------------------------------------------------------------------
CUBE_SJ
Syntax:CUBE_SJ ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CUBE_SJ hint instructs the optimizer to clude cube semi joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.SQL??
- HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
- HelloDBA.com> exec sql_explain('select * from product_dim_view pd where exists (select /*+cube_sj*/1 from cube_view cb where cb.product=pd.item_id)','typical');
- Plan hash value: 2914263671
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 36 | 8604 | 13 (62)| 00:00:01 |
- |* 1 | HASH JOIN SEMI | | 36 | 8604 | 13 (62)| 00:00:01 |
- | 2 | TABLE ACCESS FULL | PRODUCT_DIM | 36 | 7884 | 3 (0)| 00:00:01 |
- | 3 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 25M| 7 (72)| 00:00:01 |
- ----------------------------------------------------------------------------------------
AUTO_REOPTIMIZE
Syntax:AUTO_REOPTIMIZE
Description:The AUTO_REOPTIMIZE hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.
Note: The "automatic reoptimization" is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement. Automatic reoptimization can fix any suboptimal plan chosen due to incorrect optimizer estimates, from a suboptimal distribution method to an incorrect choice of degree of parallelism.SQL??
- HelloDBA.com> create table t_user as select * from dba_users;
- HelloDBA.com> create table t_ind as select * from dba_indexes;
- HelloDBA.com> create index t_tab_idx1 on t_tab(owner, table_name);
- HelloDBA.com> create table t_obj1 as select * from t_obj;
- HelloDBA.com> create index t_obj1_idx1 on t_obj1(owner);
- HelloDBA.com> create index t_obj1_idx2 on t_obj1(owner, object_name);
- HelloDBA.com> exec dbms_stats.set_table_stats(user,'T_OBJ1', numrows=>1);
- HelloDBA.com> alter session set "_optimizer_use_feedback"=false;
- HelloDBA.com> alter system flush shared_pool;
- -- First running
- HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);
- SQL_ID 5a4p65yb97cs6, child number 0
- -------------------------------------
- select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where
- o.owner=t.owner and o.object_name=t.table_name and exists (select 1
- from t_ind i, t_user u where o.owner=i.owner and o.object_name =
- i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')
- Plan hash value: 2082622820
- --------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 484 (100)| |
- |* 1 | FILTER | | | | | |
- | 2 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
- | 3 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
- | 4 | TABLE ACCESS FULL | T_OBJ1 | 1 | 115 | 430 (0)| 00:00:01 |
- |* 5 | INDEX RANGE SCAN | T_TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
- | 6 | TABLE ACCESS BY INDEX ROWID| T_TAB | 1 | 259 | 2 (0)| 00:00:01 |
- | 7 | MERGE JOIN CARTESIAN | | 1 | 47 | 52 (0)| 00:00:01 |
- |* 8 | TABLE ACCESS FULL | T_IND | 1 | 30 | 49 (0)| 00:00:01 |
- | 9 | BUFFER SORT | | 1 | 17 | 3 (0)| 00:00:01 |
- |* 10 | TABLE ACCESS FULL | T_USER | 1 | 17 | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------
- Note
- -----
- - this is an adaptive plan
- -- Second running
- HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);
- SQL_ID 5a4p65yb97cs6, child number 1
- -------------------------------------
- select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where
- o.owner=t.owner and o.object_name=t.table_name and exists (select 1
- from t_ind i, t_user u where o.owner=i.owner and o.object_name =
- i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')
- Plan hash value: 1348485435
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 514 (100)| |
- | 1 | VIEW | VM_NWVW_2 | 300 | 362K| 514 (1)| 00:00:01 |
- | 2 | HASH UNIQUE | | 300 | 123K| 514 (1)| 00:00:01 |
- |* 3 | HASH JOIN | | 300 | 123K| 513 (1)| 00:00:01 |
- |* 4 | HASH JOIN RIGHT SEMI | | 300 | 48600 | 483 (1)| 00:00:01 |
- |* 5 | TABLE ACCESS FULL | T_USER | 10 | 170 | 3 (0)| 00:00:01 |
- |* 6 | HASH JOIN RIGHT SEMI| | &nbs