HelloDBA [中文]
Search Internet Search HelloDBA
Home
Tech Documents
Products & Download
Subscribe
  • To Google
  • To Yahoo
  • To zhuxia
  • To xianguo
  • To QQ email
  • To YouDao
About
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle 12c new SQL Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2016-05-05 04:26:44

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

Oracle introduced many new features in 12c, some of which are related to SQL optimization. Quite a few SQL hints come along with these new features.

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