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

Oracle 12c 新SQL提示

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2016-05-05 04:26:44

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

Oracle 12c中引入了许多新特性,其中部分是和SQL相关的特性.而一些新的SQL提示也随着这些新特性被引入.

enable_parallel_dml
Syntax: enable_parallel_dml
Description: Enable parallel dml. Same effect as "alter session enable parallel dml"

SQL??
  1. HelloDBA.com> create table t_obj as select * from dba_objects;  
  2. HelloDBA.com> create table t_tab as select * from dba_tables;  
  3. 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');  
  4. Plan hash value: 409221376  
  5.   
  6. ----------------------------------------------------------------------------------------------------------------------------  
  7. | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  8. ----------------------------------------------------------------------------------------------------------------------------  
  9. |   0 | DELETE STATEMENT                   |          |  3160 |   175K|    44   (0)| 00:00:01 |        |      |            |  
  10. |   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |  
  11. |   2 |   PX SEND QC (RANDOM)              | :TQ10003 |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |  
  12. |   3 |    DELETE                          | T_OBJ    |       |       |            |          |  Q1,03 | PCWP |            |  
  13. |   4 |     PX RECEIVE                     |          |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,03 | PCWP |            |  
  14. |   5 |      PX SEND HASH (BLOCK ADDRESS)  | :TQ10002 |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|  
  15. |*  6 |       HASH JOIN RIGHT SEMI BUFFERED|          |  3160 |   175K|    44   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  16. |   7 |        PX RECEIVE                  |          |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  17. |   8 |         PX SEND HASH               | :TQ10000 |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |  
  18. |   9 |          PX SELECTOR               |          |       |       |            |          |  Q1,00 | SCWC |            |  
  19. |  10 |           TABLE ACCESS FULL        | T_TAB    |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | SCWP |            |  
  20. |  11 |        PX RECEIVE                  |          | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  21. |  12 |         PX SEND HASH               | :TQ10001 | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |  
  22. |  13 |          PX BLOCK ITERATOR         |          | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
  23. |  14 |           TABLE ACCESS FULL        | T_OBJ    | 92299 |  2794K|    15   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  24. ----------------------------------------------------------------------------------------------------------------------------  

disable_parallel_dml
Syntax: disable_parallel_dml
Description: Disable parallel dml. Same effect as "alter session disable parallel dml"

SQL??
  1. HelloDBA.com> alter session enable parallel dml;  
  2. 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');  
  3. Plan hash value: 1357806520  
  4.   
  5. ------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ------------------------------------------------------------------------------------------------------------------  
  8. |   0 | DELETE STATEMENT         |          |  3160 |   175K|   460   (1)| 00:00:01 |        |      |            |  
  9. |   1 |  DELETE                  | T_OBJ    |       |       |            |          |        |      |            |  
  10. |   2 |   PX COORDINATOR         |          |       |       |            |          |        |      |            |  
  11. |   3 |    PX SEND QC (RANDOM)   | :TQ10001 |  3160 |   175K|   460   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  12. |*  4 |     HASH JOIN RIGHT SEMI |          |  3160 |   175K|   460   (1)| 00:00:01 |  Q1,01 | PCWP |            |  
  13. |   5 |      PX RECEIVE          |          |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  14. |   6 |       PX SEND BROADCAST  | :TQ10000 |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | S->P | BROADCAST  |  
  15. |   7 |        PX SELECTOR       |          |       |       |            |          |  Q1,00 | SCWC |            |  
  16. |   8 |         TABLE ACCESS FULL| T_TAB    |  2426 | 63076 |    29   (0)| 00:00:01 |  Q1,00 | SCWP |            |  
  17. |   9 |      PX BLOCK ITERATOR   |          | 92299 |  2794K|   430   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
  18. |  10 |       TABLE ACCESS FULL  | T_OBJ    | 92299 |  2794K|   430   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  19. ------------------------------------------------------------------------------------------------------------------  

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??
  1. HelloDBA.com> create view cube_view as select * from table(cube_table('GLOBAL.UNITS_CUBE'));  
  2. HelloDBA.com> create view time_dim_view as select * from global.time_dim;  
  3. HelloDBA.com> create view channel_dim_view as select * from global.channel_dim;  
  4. HelloDBA.com> create view customer_dim_view as select * from global.customer_dim;  
  5. HelloDBA.com> create view product_dim_view as select * from global.product_dim;  
  6. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
  7. 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');  
  8. Plan hash value: 1674841133  
  9.   
  10. ------------------------------------------------------------------------------------------  
  11. | Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
  12. ------------------------------------------------------------------------------------------  
  13. |   0 | SELECT STATEMENT          |              |   316K|   401M|    19  (48)| 00:00:01 |  
  14. |*  1 |  HASH JOIN                |              |   316K|   401M|    19  (48)| 00:00:01 |  
  15. |   2 |   TABLE ACCESS FULL       | TIME_DIM     |   120 | 28680 |     3   (0)| 00:00:01 |  
  16. |*  3 |   CUBE JOIN               |              |   585K|   609M|    14  (50)| 00:00:01 |  
  17. |   4 |    MERGE JOIN CARTESIAN   |              |   183 | 71919 |     6   (0)| 00:00:01 |  
  18. |   5 |     TABLE ACCESS FULL     | CHANNEL_DIM  |     3 |   237 |     2   (0)| 00:00:01 |  
  19. |   6 |     BUFFER SORT           |              |    61 | 19154 |     4   (0)| 00:00:01 |  
  20. |   7 |      TABLE ACCESS FULL    | CUSTOMER_DIM |    61 | 19154 |     1   (0)| 00:00:01 |  
  21. |   8 |    CUBE SCAN PARTIAL OUTER| UNITS_CUBE   |  1342K|   896M|     5  (80)| 00:00:01 |  
  22. ------------------------------------------------------------------------------------------  

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??
  1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=true;  
  2. 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');  
  3. Plan hash value: 2364382396  
  4.   
  5. ------------------------------------------------------------------------------------------  
  6. | Id  | Operation                 | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT          |              |   316K|   401M|    21  (48)| 00:00:01 |  
  9. |*  1 |  HASH JOIN                |              |   316K|   401M|    21  (48)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS FULL       | TIME_DIM     |   120 | 28680 |     3   (0)| 00:00:01 |  
  11. |*  3 |   HASH JOIN               |              |   585K|   609M|    16  (50)| 00:00:01 |  
  12. |   4 |    MERGE JOIN CARTESIAN   |              |   183 | 71919 |     6   (0)| 00:00:01 |  
  13. |   5 |     TABLE ACCESS FULL     | CHANNEL_DIM  |     3 |   237 |     2   (0)| 00:00:01 |  
  14. |   6 |     BUFFER SORT           |              |    61 | 19154 |     4   (0)| 00:00:01 |  
  15. |   7 |      TABLE ACCESS FULL    | CUSTOMER_DIM |    61 | 19154 |     1   (0)| 00:00:01 |  
  16. |   8 |    CUBE SCAN PARTIAL OUTER| UNITS_CUBE   |  1342K|   896M|     7  (72)| 00:00:01 |  
  17. ------------------------------------------------------------------------------------------  

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??
  1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
  2. 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');  
  3. Plan hash value: 2520340146  
  4.   
  5. ----------------------------------------------------------------------------------------  
  6. | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT         |             |    36 |  8208 |    11  (64)| 00:00:01 |  
  9. |*  1 |  CUBE JOIN ANTI          |             |    36 |  8208 |    11  (64)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS FULL      | PRODUCT_DIM |    36 |  7488 |     2   (0)| 00:00:01 |  
  11. |   3 |   CUBE SCAN PARTIAL OUTER| UNITS_CUBE  |  1342K|    25M|     5  (60)| 00:00:01 |  
  12. ----------------------------------------------------------------------------------------  

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??
  1. HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;  
  2. 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');  
  3. Plan hash value: 2914263671  
  4.   
  5. ----------------------------------------------------------------------------------------  
  6. | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT         |             |    36 |  8604 |    13  (62)| 00:00:01 |  
  9. |*  1 |  HASH JOIN SEMI          |             |    36 |  8604 |    13  (62)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS FULL      | PRODUCT_DIM |    36 |  7884 |     3   (0)| 00:00:01 |  
  11. |   3 |   CUBE SCAN PARTIAL OUTER| UNITS_CUBE  |  1342K|    25M|     7  (72)| 00:00:01 |  
  12. ----------------------------------------------------------------------------------------  

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??
  1. HelloDBA.com> create table t_user as select * from dba_users;  
  2. HelloDBA.com> create table t_ind as select * from dba_indexes;  
  3. HelloDBA.com> create index t_tab_idx1 on t_tab(owner, table_name);  
  4. HelloDBA.com> create table t_obj1 as select * from t_obj;  
  5. HelloDBA.com> create index t_obj1_idx1 on t_obj1(owner);  
  6. HelloDBA.com> create index t_obj1_idx2 on t_obj1(owner, object_name);  
  7. HelloDBA.com> exec dbms_stats.set_table_stats(user,'T_OBJ1', numrows=>1);  
  8. HelloDBA.com> alter session set "_optimizer_use_feedback"=false;  
  9. HelloDBA.com> alter system flush shared_pool;  
  10. -- First running  
  11. 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);  
  12. SQL_ID  5a4p65yb97cs6, child number 0  
  13. -------------------------------------  
  14. select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
  15. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
  16. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
  17. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
  18.   
  19. Plan hash value: 2082622820  
  20.   
  21. --------------------------------------------------------------------------------------------  
  22. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  23. --------------------------------------------------------------------------------------------  
  24. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
  25. |*  1 |  FILTER                       |            |       |       |            |          |  
  26. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
  27. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
  28. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
  29. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
  30. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
  31. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
  32. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
  33. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
  34. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
  35. --------------------------------------------------------------------------------------------  
  36.   
  37. Note  
  38. -----  
  39.    - this is an adaptive plan  
  40.   
  41. -- Second running  
  42. 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);  
  43. SQL_ID  5a4p65yb97cs6, child number 1  
  44. -------------------------------------  
  45. select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
  46. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
  47. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
  48. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
  49.   
  50. Plan hash value: 1348485435  
  51.   
  52. --------------------------------------------------------------------------------------  
  53. | Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  54. --------------------------------------------------------------------------------------  
  55. |   0 | SELECT STATEMENT         |           |       |       |   514 (100)|          |  
  56. |   1 |  VIEW                    | VM_NWVW_2 |   300 |   362K|   514   (1)| 00:00:01 |  
  57. |   2 |   HASH UNIQUE            |           |   300 |   123K|   514   (1)| 00:00:01 |  
  58. |*  3 |    HASH JOIN             |           |   300 |   123K|   513   (1)| 00:00:01 |  
  59. |*  4 |     HASH JOIN RIGHT SEMI |           |   300 | 48600 |   483   (1)| 00:00:01 |  
  60. |*  5 |      TABLE ACCESS FULL   | T_USER    |    10 |   170 |     3   (0)| 00:00:01 |  
  61. |*  6 |      HASH JOIN RIGHT SEMI|           | &nbs