HelloDBA [中文]
Search Internet Search HelloDBA
  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 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|           |   308 | 44660 |   480   (1)| 00:00:01 |  
  62. |   7 |       TABLE ACCESS FULL  | T_IND     |  4713 |   138K|    49   (0)| 00:00:01 |  
  63. |   8 |       TABLE ACCESS FULL  | T_OBJ1    | 92299 |    10M|   431   (1)| 00:00:01 |  
  64. |   9 |     TABLE ACCESS FULL    | T_TAB     |  2426 |   613K|    29   (0)| 00:00:01 |  
  65. --------------------------------------------------------------------------------------  
  66.   
  67. Predicate Information (identified by operation id):  
  68. ---------------------------------------------------  
  69.   
  70.    3 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
  71.    4 - access("O"."OWNER"="U"."USERNAME")  
  72.    5 - filter("U"."DEFAULT_TABLESPACE"='SYSAUX')  
  73.    6 - access("O"."OWNER"="I"."OWNER" AND "O"."OBJECT_NAME"="I"."INDEX_NAME")  
  74.   
  75. Note  
  76. -----  
  77.    - statistics feedback used for this statement  

NO_AUTO_REOPTIMIZE
Syntax:AUTO_REOPTIMIZE
Description:The AUTO_REOPTIMIZE hint instructs the optimizer not to automatically change a plan on subsequent executions of a SQL statement.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_use_feedback"=true;  
  2. HelloDBA.com> alter system flush shared_pool;  
  3. HelloDBA.com> exec sql_explain('select /*+NO_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);  
  4. SQL_ID  0g6swgrm8tt0s, child number 0  
  5. -------------------------------------  
  6. select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
  7. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
  8. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
  9. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
  10.   
  11. Plan hash value: 2082622820  
  12.   
  13. --------------------------------------------------------------------------------------------  
  14. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  15. --------------------------------------------------------------------------------------------  
  16. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
  17. |*  1 |  FILTER                       |            |       |       |            |          |  
  18. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
  19. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
  20. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
  21. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
  22. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
  23. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
  24. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
  25. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
  26. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
  27. --------------------------------------------------------------------------------------------  
  28.   
  29. Predicate Information (identified by operation id):  
  30. ---------------------------------------------------  
  31.   
  32.    1 - filter( IS NOT NULL)  
  33.    5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
  34.    8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))  
  35.   10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))  
  36.   
  37. Note  
  38. -----  
  39.    - this is an adaptive plan  
  40.   
  41. HelloDBA.com> exec sql_explain('select /*+NO_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);  
  42. SQL_ID  0g6swgrm8tt0s, child number 0  
  43. -------------------------------------  
  44. select /*+NO_AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where  
  45. o.owner=t.owner and o.object_name=t.table_name and exists (select 1  
  46. from t_ind i, t_user u where o.owner=i.owner and o.object_name =  
  47. i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')  
  48.   
  49. Plan hash value: 2082622820  
  50.   
  51. --------------------------------------------------------------------------------------------  
  52. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  53. --------------------------------------------------------------------------------------------  
  54. |   0 | SELECT STATEMENT              |            |       |       |   484 (100)|          |  
  55. |*  1 |  FILTER                       |            |       |       |            |          |  
  56. |   2 |   NESTED LOOPS                |            |     1 |   374 |   432   (0)| 00:00:01 |  
  57. |   3 |    NESTED LOOPS               |            |     1 |   374 |   432   (0)| 00:00:01 |  
  58. |   4 |     TABLE ACCESS FULL         | T_OBJ1     |     1 |   115 |   430   (0)| 00:00:01 |  
  59. |*  5 |     INDEX RANGE SCAN          | T_TAB_IDX1 |     1 |       |     1   (0)| 00:00:01 |  
  60. |   6 |    TABLE ACCESS BY INDEX ROWID| T_TAB      |     1 |   259 |     2   (0)| 00:00:01 |  
  61. |   7 |   MERGE JOIN CARTESIAN        |            |     1 |    47 |    52   (0)| 00:00:01 |  
  62. |*  8 |    TABLE ACCESS FULL          | T_IND      |     1 |    30 |    49   (0)| 00:00:01 |  
  63. |   9 |    BUFFER SORT                |            |     1 |    17 |     3   (0)| 00:00:01 |  
  64. |* 10 |     TABLE ACCESS FULL         | T_USER     |     1 |    17 |     3   (0)| 00:00:01 |  
  65. --------------------------------------------------------------------------------------------  
  66.   
  67. Predicate Information (identified by operation id):  
  68. ---------------------------------------------------  
  69.   
  70.    1 - filter( IS NOT NULL)  
  71.    5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")  
  72.    8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))  
  73.   10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))  
  74.   
  75. Note  
  76. -----  
  77.    - this is an adaptive plan  

ADAPTIVE_PLAN
Syntax:ADAPTIVE_PLAN
Description:The ADAPTIVE_PLAN hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_adaptive_plans"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+ADAPTIVE_PLAN*/* from t_tab t where table_name = any (select max(object_name) from t_obj o where o.owner=t.owner group by owner)''TYPICAL');  
  3. Plan hash value: 3428189515  
  4.   
  5. --------------------------------------------------------------------------------------  
  6. | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. --------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT        |            |     1 |   337 |   468   (1)| 00:00:01 |  
  9. |*  1 |  HASH JOIN SEMI         |            |     1 |   337 |   468   (1)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS FULL     | T_TAB      |  2426 |   613K|    29   (0)| 00:00:01 |  
  11. |   3 |   VIEW                  | VW_SQ_1    | 15577 |  1186K|   438   (1)| 00:00:01 |  
  12. |*  4 |    HASH JOIN            |            | 15577 |  2296K|   438   (1)| 00:00:01 |  
  13. |   5 |     VIEW                | VW_GBD_4   |    26 |  3432 |   433   (1)| 00:00:01 |  
  14. |   6 |      HASH GROUP BY      |            |    26 |   806 |   433   (1)| 00:00:01 |  
  15. |   7 |       TABLE ACCESS FULL | T_OBJ      | 92299 |  2794K|   431   (1)| 00:00:01 |  
  16. |   8 |     INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     5   (0)| 00:00:01 |  
  17. --------------------------------------------------------------------------------------  
  18.   
  19. Predicate Information (identified by operation id):  
  20. ---------------------------------------------------  
  21.   
  22.    1 - access("TABLE_NAME"="MAX(OBJECT_NAME)" AND "ITEM_1"=ROWID)  
  23.    4 - access("ITEM_1"="T"."OWNER")  
  24.   
  25. Note  
  26. -----  
  27.    - this is an adaptive plan  

NO_ADAPTIVE_PLAN
Syntax:NO_ADAPTIVE_PLAN
Description:The NO_ADAPTIVE_PLAN hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_adaptive_plans"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_ADAPTIVE_PLAN*/* from t_tab t where table_name = any (select max(object_name) from t_obj o where o.owner=t.owner group by owner)''TYPICAL');  
  3. Plan hash value: 3428189515  
  4.   
  5. --------------------------------------------------------------------------------------  
  6. | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. --------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT        |            |     1 |   337 |   468   (1)| 00:00:01 |  
  9. |*  1 |  HASH JOIN SEMI         |            |     1 |   337 |   468   (1)| 00:00:01 |  
  10. |   2 |   TABLE ACCESS FULL     | T_TAB      |  2426 |   613K|    29   (0)| 00:00:01 |  
  11. |   3 |   VIEW                  | VW_SQ_1    | 15577 |  1186K|   438   (1)| 00:00:01 |  
  12. |*  4 |    HASH JOIN            |            | 15577 |  2296K|   438   (1)| 00:00:01 |  
  13. |   5 |     VIEW                | VW_GBD_4   |    26 |  3432 |   433   (1)| 00:00:01 |  
  14. |   6 |      HASH GROUP BY      |            |    26 |   806 |   433   (1)| 00:00:01 |  
  15. |   7 |       TABLE ACCESS FULL | T_OBJ      | 92299 |  2794K|   431   (1)| 00:00:01 |  
  16. |   8 |     INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     5   (0)| 00:00:01 |  
  17. --------------------------------------------------------------------------------------  

BATCH_TABLE_ACCESS_BY_ROWID
Syntax:BATCH_TABLE_ACCESS_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The BATCH_TABLE_ACCESS_BY_ROWID hint instructs the optimizer to retrieve a few rowids from the index, then access the rows in data block order, to reduce accessing data block times .

SQL??
  1. HelloDBA.com> alter session set "_optimizer_batch_table_access_by_rowid"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+BATCH_TABLE_ACCESS_BY_ROWID(o)*/* from t_obj1 o where owner=''SYS''''typical');  
  3. Plan hash value: 323892535  
  4.   
  5. ---------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ---------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                    |             |     1 |   115 |   120   (0)| 00:00:01 |  
  9. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |     1 |   115 |   120   (0)| 00:00:01 |  
  10. |*  2 |   INDEX RANGE SCAN                  | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
  11. ---------------------------------------------------------------------------------------------------  

NO_BATCH_TABLE_ACCESS_BY_ROWID
Syntax:BATCH_TABLE_ACCESS_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The BATCH_TABLE_ACCESS_BY_ROWID hint instructs the optimizer not use batch table access by rowid.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_batch_table_access_by_rowid"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_BATCH_TABLE_ACCESS_BY_ROWID(o)*/* from t_obj1 o where owner=''SYS''''typical');  
  3. Plan hash value: 1936150373  
  4.   
  5. ----------------------------------------------------------------------------  
  6. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT  |        | 42109 |  4729K|   431   (1)| 00:00:01 |  
  9. |*  1 |  TABLE ACCESS FULL| T_OBJ1 | 42109 |  4729K|   431   (1)| 00:00:01 |  
  10. ----------------------------------------------------------------------------  

CLUSTERING
Syntax:CLUSTERING ( [ @ queryblock ] )
Description: This hint is valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The CLUSTERING hint enables attribute clustering for direct-path inserts (serial or parallel). This results in partially-clustered data, that is, data that is clustered per each insert or merge operation. This hint overrides a NO ON LOAD setting in the DDL that created or altered the table. This hint has no effect on tables that are not enabled for attribute clustering.

SQL??
  1. HelloDBA.com> alter table t_obj1 add clustering by interleaved order (owner) no on load no on data movement;  
  2. HelloDBA.com> exec sql_explain('insert /*+APPEND CLUSTERING*/ into t_obj1 select * from t_obj t''TYPICAL');  
  3. Plan hash value: 761959232  
  4.   
  5. ---------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                        | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  7. ---------------------------------------------------------------------------------------------------  
  8. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
  9. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |       |            |          |  
  10. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
  11. |   3 |    SORT ORDER BY                 |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
  12. |   4 |     TABLE ACCESS FULL            | T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
  13. ---------------------------------------------------------------------------------------------------  

NO_CLUSTERING
Syntax:NO_CLUSTERING ( [ @ queryblock ] )
Description: This hint is valid only for INSERT and MERGE operations on tables that are enabled for attribute clustering. The NO_CLUSTERING hint disables attribute clustering for direct-path inserts (serial or parallel). This hint overrides a YES ON LOAD setting in the DDL that created or altered the table. This hint has no effect on tables that are not enabled for attribute clustering.

SQL??
  1. HelloDBA.com> alter table t_obj1 drop clustering;  
  2. HelloDBA.com> alter table t_obj1 add clustering by interleaved order (owner) yes on load yes on data movement;  
  3. HelloDBA.com> exec sql_explain('insert /*+APPEND NO_CLUSTERING*/ into t_obj1 select * from t_obj t''TYPICAL');  
  4. Plan hash value: 1252610668  
  5.   
  6. -------------------------------------------------------------------------------------------  
  7. | Id  | Operation                        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  8. -------------------------------------------------------------------------------------------  
  9. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|   431   (1)| 00:00:01 |  
  10. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |            |          |  
  11. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|   431   (1)| 00:00:01 |  
  12. |   3 |    TABLE ACCESS FULL             | T_OBJ  | 92299 |    10M|   431   (1)| 00:00:01 |  
  13. -------------------------------------------------------------------------------------------  

ZONEMAP
Syntax:ZONEMAP ( [ @ queryblock ] tablespec { SCAN | JOIN | PARTITION } )
Description: The ZONEMAP hint enables the use of a zone map for different types of pruning. This hint overrides an ENABLE PRUNING setting in the DDL that created or altered the zone map.
Specify one of the following options:
SCAN - Enables the use of a zone map for scan pruning.
JOIN - Enables the use of a zone map for join pruning.
PARTITION - Enables the use of a zone map for partition pruning.

NOTE: Target table should be stored in a tablespace residing on Oracle Exadata storage and then define a ZONEMAP on it.

NO_ZONEMAP
Syntax:NO_ZONEMAP ( [ @ queryblock ] tablespec { SCAN | JOIN | PARTITION } )
Description: The NO_ZONEMAP hint disables the use of a zone map for different types of pruning. This hint overrides an ENABLE PRUNING setting in the DDL that created or altered the zone map.
Specify one of the following options:
SCAN - Disables the use of a zone map for scan pruning.
JOIN - Disables the use of a zone map for join pruning.
PARTITION - Disables the use of a zone map for partition pruning.

CLUSTER_BY_ROWID
Syntax:CLUSTER_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CLUSTER_BY_ROWID hint instructs the optimizer enable the cluster by rowid feature.

SQL??
  1. HelloDBA.com> exec sql_explain('select /*+CLUSTER_BY_ROWID(t)*/* from t_obj1 t where owner = :A''TYPICAL');  
  2. Plan hash value: 2919409215  
  3.   
  4. ---------------------------------------------------------------------------------------------------  
  5. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  6. ---------------------------------------------------------------------------------------------------  
  7. |   0 | SELECT STATEMENT                    |             |  3550 |   398K|   120   (0)| 00:00:01 |  
  8. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |  3550 |   398K|   120   (0)| 00:00:01 |  
  9. |   2 |   SORT CLUSTER BY ROWID             |             |  3550 |       |     9   (0)| 00:00:01 |  
  10. |*  3 |    INDEX RANGE SCAN                 | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
  11. ---------------------------------------------------------------------------------------------------  

NO_CLUSTER_BY_ROWID
Syntax:CLUSTER_BY_ROWID ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CLUSTER_BY_ROWID hint instructs the optimizer disable the cluster by rowid feature.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_cluster_by_rowid"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_CLUSTER_BY_ROWID(t)*/* from t_obj1 t where owner = :A''TYPICAL');  
  3. Plan hash value: 323892535  
  4.   
  5. ---------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ---------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                    |             |  3550 |   398K|   120   (0)| 00:00:01 |  
  9. |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_OBJ1      |  3550 |   398K|   120   (0)| 00:00:01 |  
  10. |*  2 |   INDEX RANGE SCAN                  | T_OBJ1_IDX1 |  3550 |       |     9   (0)| 00:00:01 |  
  11. ---------------------------------------------------------------------------------------------------  

DECORRELATE
Syntax:DECORRELATE ( [ @ queryblock ] )
Description: The DECORRELATE hint instructs the optimizer to decorrelate the subquery.

SQL??
  1. HelloDBA.com> exec sql_explain('select /*+DECORRELATE(@iv)*/owner, subobject_name, v.last_analyzed from t_obj1 o cross apply (select /*+qb_name(iv)*/max(last_analyzed) last_analyzed from t_tab t where t.table_name=o.object_name) v''TYPICAL OUTLINE');  
  2. Plan hash value: 973442988  
  3.   
  4. -----------------------------------------------------------------------------------------  
  5. | Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  6. -----------------------------------------------------------------------------------------  
  7. |   0 | SELECT STATEMENT      |                 | 92299 |  9734K|   461   (1)| 00:00:01 |  
  8. |*  1 |  HASH JOIN RIGHT OUTER|                 | 92299 |  9734K|   461   (1)| 00:00:01 |  
  9. |   2 |   VIEW                | VW_DCL_922C9664 |  2420 |   177K|    30   (4)| 00:00:01 |  
  10. |   3 |    HASH GROUP BY      |                 |  2420 | 65340 |    30   (4)| 00:00:01 |  
  11. |   4 |     TABLE ACCESS FULL | T_TAB           |  2426 | 65502 |    29   (0)| 00:00:01 |  
  12. |   5 |   TABLE ACCESS FULL   | T_OBJ1          | 92299 |  2974K|   431   (1)| 00:00:01 |  
  13. -----------------------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       USE_HASH_AGGREGATION(@"SEL$ACECEEBF")  
  21.       FULL(@"SEL$ACECEEBF" "T"@"IV")  
  22.       SWAP_JOIN_INPUTS(@"SEL$C872D39E" "V"@"SEL$1")  
  23.       USE_HASH(@"SEL$C872D39E" "V"@"SEL$1")  
  24.       LEADING(@"SEL$C872D39E" "O"@"SEL$1" "V"@"SEL$1")  
  25.       NO_ACCESS(@"SEL$C872D39E" "V"@"SEL$1")  
  26.       FULL(@"SEL$C872D39E" "O"@"SEL$1")  
  27.       OUTLINE(@"SEL$1")  
  28.       OUTLINE(@"SEL$2")  
  29.       DECORRELATE(@"IV")  
  30.       OUTLINE(@"SEL$ACECEEBF")  
  31.       MERGE(@"SEL$1")  
  32.       OUTLINE(@"SEL$58A6D7F6")  
  33.       OUTLINE(@"IV")  
  34.       DECORRELATE(@"SEL$ACECEEBF")  
  35.       OUTLINE_LEAF(@"SEL$C872D39E")  
  36.       DECORRELATE(@"IV")  
  37.       OUTLINE_LEAF(@"SEL$ACECEEBF")  
  38.       ALL_ROWS  
  39.       OPT_PARAM('star_transformation_enabled' 'true')  
  40.       DB_VERSION('12.1.0.2')  
  41.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  42.       IGNORE_OPTIM_EMBEDDED_HINTS  
  43.       END_OUTLINE_DATA  
  44.   */  

NO_DECORRELATE
Syntax:NO_DECORRELATE ( [ @ queryblock ] )
Description: The NO_DECORRELATE hint instructs the optimizer not to decorrelate the subquery.

SQL??
  1. HelloDBA.com> exec sql_explain('select /*+NO_DECORRELATE(@iv)*/owner, subobject_name, v.last_analyzed from t_obj1 o cross apply (select /*+qb_name(iv)*/max(last_analyzed) last_analyzed from t_tab t where t.table_name=o.object_name) v''TYPICAL OUTLINE');  
  2. Plan hash value: 882957228  
  3.   
  4. ----------------------------------------------------------------------------------------  
  5. | Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  
  6. ----------------------------------------------------------------------------------------  
  7. |   0 | SELECT STATEMENT     |                 | 92299 |  3785K|  2679K  (1)| 00:01:45 |  
  8. |   1 |  NESTED LOOPS        |                 | 92299 |  3785K|  2679K  (1)| 00:01:45 |  
  9. |   2 |   TABLE ACCESS FULL  | T_OBJ1          | 92299 |  2974K|   431   (1)| 00:00:01 |  
  10. |   3 |   VIEW               | VW_LAT_922C9664 |     1 |     9 |    29   (0)| 00:00:01 |  
  11. |   4 |    SORT AGGREGATE    |                 |     1 |    27 |            |          |  
  12. |*  5 |     TABLE ACCESS FULL| T_TAB           |     1 |    27 |    29   (0)| 00:00:01 |  
  13. ----------------------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       FULL(@"IV" "T"@"IV")  
  21.       USE_NL(@"SEL$58A6D7F6" "V"@"SEL$1")  
  22.       LEADING(@"SEL$58A6D7F6" "O"@"SEL$1" "V"@"SEL$1")  
  23.       NO_ACCESS(@"SEL$58A6D7F6" "V"@"SEL$1")  
  24.       FULL(@"SEL$58A6D7F6" "O"@"SEL$1")  
  25.       OUTLINE(@"SEL$1")  
  26.       OUTLINE(@"SEL$2")  
  27.       OUTLINE(@"IV")  
  28.       MERGE(@"SEL$1")  
  29.       OUTLINE_LEAF(@"SEL$58A6D7F6")  
  30.       OUTLINE_LEAF(@"IV")  
  31.       ALL_ROWS  
  32.       OPT_PARAM('star_transformation_enabled' 'true')  
  33.       DB_VERSION('12.1.0.2')  
  34.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  35.       IGNORE_OPTIM_EMBEDDED_HINTS  
  36.       END_OUTLINE_DATA  
  37.   */  

GATHER_OPTIMIZER_STATISTICS
Syntax:GATHER_OPTIMIZER_STATISTICS
Description: The GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to enable statistics gathering during the following types of bulk loads:
CREATE TABLE ... AS SELECT
INSERT INTO ... SELECT into an empty table using a direct-path insert

SQL??
  1. HelloDBA.com> alter session set "_optimizer_gather_stats_on_load"=false;  
  2. HelloDBA.com> exec sql_explain('insert /*+GATHER_OPTIMIZER_STATISTICS append*/into t_obj1 select * from t_obj','typical');  
  3. Plan hash value: 761959232  
  4.   
  5. ---------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                        | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  7. ---------------------------------------------------------------------------------------------------  
  8. |   0 | INSERT STATEMENT                 |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
  9. |   1 |  LOAD AS SELECT                  | T_OBJ1 |       |       |       |            |          |  
  10. |   2 |   OPTIMIZER STATISTICS GATHERING |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
  11. |   3 |    SORT ORDER BY                 |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
  12. |   4 |     TABLE ACCESS FULL            | T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
  13. ---------------------------------------------------------------------------------------------------  

NO_GATHER_OPTIMIZER_STATISTICS
Syntax:NO_GATHER_OPTIMIZER_STATISTICS
Description: The NO_GATHER_OPTIMIZER_STATISTICS hint instructs the optimizer to disable statistics gathering during the following types of bulk loads:
CREATE TABLE ... AS SELECT
INSERT INTO ... SELECT into an empty table using a direct-path insert

SQL??
  1. HelloDBA.com> alter session set "_optimizer_gather_stats_on_load"=true;  
  2. HelloDBA.com> exec sql_explain('insert /*+NO_GATHER_OPTIMIZER_STATISTICS append*/into t_obj1 select * from t_obj','typical');  
  3. Plan hash value: 761959232  
  4.   
  5. --------------------------------------------------------------------------------------  
  6. | Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  7. --------------------------------------------------------------------------------------  
  8. |   0 | INSERT STATEMENT    |        | 92299 |    10M|       |  2824   (1)| 00:00:01 |  
  9. |   1 |  LOAD AS SELECT     | T_OBJ1 |       |       |       |            |          |  
  10. |   2 |   SORT ORDER BY     |        | 92299 |    10M|    13M|  2824   (1)| 00:00:01 |  
  11. |   3 |    TABLE ACCESS FULL| T_OBJ  | 92299 |    10M|       |   431   (1)| 00:00:01 |  
  12. --------------------------------------------------------------------------------------  

PARTIAL_JOIN
Syntax:PARTIAL_JOIN ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The PARTIAL_JOIN hint instructs the optimizer to evaluate partial join:

SQL??
  1. HelloDBA.com> alter session set "_optimizer_partial_join_eval"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+PARTIAL_JOIN(o)*/o.owner,o.object_name, max(t.last_analyzed) from t_tab t, t_obj o where t.owner=o.owner and t.table_name=o.object_name group by o.owner, o.object_name','typical outline');  
  3. Plan hash value: 1185907781  
  4.   
  5. -----------------------------------------------------------------------------  
  6. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. -----------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT    |       |  1017 | 66105 |   461   (1)| 00:00:01 |  
  9. |   1 |  HASH GROUP BY      |       |  1017 | 66105 |   461   (1)| 00:00:01 |  
  10. |*  2 |   HASH JOIN SEMI    |       |  1017 | 66105 |   460   (1)| 00:00:01 |  
  11. |   3 |    TABLE ACCESS FULL| T_TAB |  2426 | 82484 |    29   (0)| 00:00:01 |  
  12. |   4 |    TABLE ACCESS FULL| T_OBJ | 92299 |  2794K|   431   (1)| 00:00:01 |  
  13. -----------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       PARTIAL_JOIN(@"SEL$1" "O"@"SEL$1")  
  21.       USE_HASH_AGGREGATION(@"SEL$1")  
  22.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  23.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  24.       FULL(@"SEL$1" "O"@"SEL$1")  
  25.       FULL(@"SEL$1" "T"@"SEL$1")  
  26.       OUTLINE_LEAF(@"SEL$1")  
  27.       ALL_ROWS  
  28.       OPT_PARAM('star_transformation_enabled' 'true')  
  29.       OPT_PARAM('_optimizer_partial_join_eval' 'false')  
  30.       DB_VERSION('12.1.0.2')  
  31.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  32.       IGNORE_OPTIM_EMBEDDED_HINTS  
  33.       END_OUTLINE_DATA  
  34.   */  

NO_PARTIAL_JOIN
Syntax:NO_PARTIAL_JOIN ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The NO_PARTIAL_JOIN hint instructs the optimizer not to evaluate partial join:

SQL??
  1. HelloDBA.com> alter session set "_optimizer_partial_join_eval"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_PARTIAL_JOIN(o)*/o.owner,o.object_name, max(t.last_analyzed) from t_tab t, t_obj o where t.owner=o.owner and t.table_name=o.object_name group by o.owner, o.object_name','typical outline');  
  3. Plan hash value: 887456466  
  4.   
  5. -----------------------------------------------------------------------------  
  6. | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. -----------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT    |       |  2426 |   153K|   461   (1)| 00:00:01 |  
  9. |   1 |  HASH GROUP BY      |       |  2426 |   153K|   461   (1)| 00:00:01 |  
  10. |*  2 |   HASH JOIN         |       |  2426 |   153K|   460   (1)| 00:00:01 |  
  11. |   3 |    TABLE ACCESS FULL| T_TAB |  2426 | 82484 |    29   (0)| 00:00:01 |  
  12. |   4 |    TABLE ACCESS FULL| T_OBJ | 92299 |  2794K|   431   (1)| 00:00:01 |  
  13. -----------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       USE_HASH_AGGREGATION(@"SEL$1")  
  21.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  22.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  23.       FULL(@"SEL$1" "O"@"SEL$1")  
  24.       FULL(@"SEL$1" "T"@"SEL$1")  
  25.       OUTLINE_LEAF(@"SEL$1")  
  26.       ALL_ROWS  
  27.       OPT_PARAM('star_transformation_enabled' 'true')  
  28.       DB_VERSION('12.1.0.2')  
  29.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  30.       IGNORE_OPTIM_EMBEDDED_HINTS  
  31.       END_OUTLINE_DATA  
  32.   */  

PARTIAL_ROLLUP_PUSHDOWN
Syntax:PARTIAL_ROLLUP_PUSHDOWN ( [ @ queryblock ] )
Description: The PARTIAL_ROLLUP_PUSHDOWN hint instructs the optimizer to perform partial rollup pushdown for parallel execution

SQL??
  1. HelloDBA.com> alter session set "_px_partial_rollup_pushdown"=off;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel(8) PARTIAL_ROLLUP_PUSHDOWN*/ owner, status, count(object_name) from t_obj group by owner, rollup(status)''TYPICAL OUTLINE');  
  3. Plan hash value: 2217003864  
  4.   
  5. -------------------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. -------------------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                      |          |    37 |  1406 |    61   (2)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR                       |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)                 | :TQ10001 |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  11. |   3 |    SORT GROUP BY ROLLUP COLLECTOR     |          |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,01 | PCWP |            |  
  12. |   4 |     PX RECEIVE                        |          |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,01 | PCWP |            |  
  13. |   5 |      PX SEND HASH                     | :TQ10000 |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,00 | P->P | HASH       |  
  14. |   6 |       SORT GROUP BY ROLLUP DISTRIBUTOR|          |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,00 | PCWP |            |  
  15. |   7 |        HASH GROUP BY                  |          |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,00 | PCWP |            |  
  16. |   8 |         PX BLOCK ITERATOR             |          | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  17. |   9 |          TABLE ACCESS FULL            | T_OBJ    | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  18. -------------------------------------------------------------------------------------------------------------------------------  
  19.   
  20. Outline Data  
  21. -------------  
  22.   
  23.   /*+  
  24.       BEGIN_OUTLINE_DATA  
  25.       USE_HASH_AGGREGATION(@"SEL$1")  
  26.       FULL(@"SEL$1" "T_OBJ"@"SEL$1")  
  27.       OUTLINE_LEAF(@"SEL$1")  
  28.       ALL_ROWS  
  29.       OPT_PARAM('star_transformation_enabled' 'true')  
  30.       OPT_PARAM('_px_partial_rollup_pushdown' 'off')  
  31.       DB_VERSION('12.1.0.2')  
  32.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  33.       IGNORE_OPTIM_EMBEDDED_HINTS  
  34.       END_OUTLINE_DATA  
  35.   */  

NO_PARTIAL_ROLLUP_PUSHDOWN
Syntax:NO_PARTIAL_ROLLUP_PUSHDOWN ( [ @ queryblock ] )
Description: The NO_PARTIAL_ROLLUP_PUSHDOWN hint instructs the optimizer not to perform partial rollup pushdown for parallel execution

SQL??
  1. HelloDBA.com> alter session set "_px_partial_rollup_pushdown"=adaptive;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel(8) NO_PARTIAL_ROLLUP_PUSHDOWN*/ owner, status, count(object_name) from t_obj group by owner, rollup(status)''TYPICAL OUTLINE');  
  3. Plan hash value: 3061208371  
  4.   
  5. -----------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. -----------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT        |          |    37 |  1406 |    61   (2)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)   | :TQ10001 |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  11. |   3 |    SORT GROUP BY ROLLUP |          |    37 |  1406 |    61   (2)| 00:00:01 |  Q1,01 | PCWP |            |  
  12. |   4 |     PX RECEIVE          |          | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  13. |   5 |      PX SEND HASH       | :TQ10000 | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |  
  14. |   6 |       PX BLOCK ITERATOR |          | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  15. |   7 |        TABLE ACCESS FULL| T_OBJ    | 92299 |  3425K|    60   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  16. -----------------------------------------------------------------------------------------------------------------  
  17.   
  18. Outline Data  
  19. -------------  
  20.   
  21.   /*+  
  22.       BEGIN_OUTLINE_DATA  
  23.       FULL(@"SEL$1" "T_OBJ"@"SEL$1")  
  24.       OUTLINE_LEAF(@"SEL$1")  
  25.       ALL_ROWS  
  26.       OPT_PARAM('star_transformation_enabled' 'true')  
  27.       DB_VERSION('12.1.0.2')  
  28.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  29.       IGNORE_OPTIM_EMBEDDED_HINTS  
  30.       END_OUTLINE_DATA  
  31.   */  

PQ_CONCURRENT_UNION
Syntax:PQ_CONCURRENT_UNION [ ( @ queryblock ) ]
Description: The PQ_CONCURRENT_UNION hint instructs the optimizer to enable concurrent processing of UNION and UNION ALL operations.

SQL??
  1. HelloDBA.com> alter session set "_px_concurrent"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+PQ_CONCURRENT_UNION*/ * from (select * from t_obj union all select * from t_obj1)''TYPICAL OUTLINE');  
  3. Plan hash value: 1664138491  
  4.   
  5. ----------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ----------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT       |          |   184K|    64M|   863   (1)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)  | :TQ10000 |   184K|    64M|   863   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  11. |   3 |    VIEW                |          |   184K|    64M|   863   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  12. |   4 |     UNION-ALL          |          |       |       |            |          |  Q1,00 | PCWP |            |  
  13. |   5 |      PX SELECTOR       |          |       |       |            |          |  Q1,00 | PCWP |            |  
  14. |   6 |       TABLE ACCESS FULL| T_OBJ    | 92299 |    10M|   431   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  15. |   7 |      PX SELECTOR       |          |       |       |            |          |  Q1,00 | PCWP |            |  
  16. |   8 |       TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   431   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  17. ----------------------------------------------------------------------------------------------------------------  
  18.   
  19. Outline Data  
  20. -------------  
  21.   
  22.   /*+  
  23.       BEGIN_OUTLINE_DATA  
  24.       FULL(@"SEL$2" "T_OBJ"@"SEL$2")  
  25.       FULL(@"SEL$3" "T_OBJ1"@"SEL$3")  
  26.       PQ_CONCURRENT_UNION(@"SET$1")  
  27.       NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")  
  28.       OUTLINE_LEAF(@"SEL$1")  
  29.       OUTLINE_LEAF(@"SET$1")  
  30.       OUTLINE_LEAF(@"SEL$3")  
  31.       OUTLINE_LEAF(@"SEL$2")  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       OPT_PARAM('_px_concurrent' 'false')  
  35.       DB_VERSION('12.1.0.2')  
  36.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  37.       IGNORE_OPTIM_EMBEDDED_HINTS  
  38.       END_OUTLINE_DATA  
  39.   */  

NO_PQ_CONCURRENT_UNION
Syntax:NO_PQ_CONCURRENT_UNION [ ( @ queryblock ) ]
Description: The NO_PQ_CONCURRENT_UNION hint instructs the optimizer to disable concurrent processing of UNION and UNION ALL operations.

SQL??
  1. HelloDBA.com> alter session set "_px_concurrent"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel NO_PQ_CONCURRENT_UNION*/ * from (select * from t_obj union all select * from t_obj1)''TYPICAL OUTLINE');  
  3. Plan hash value: 496087642  
  4.   
  5. ----------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ----------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT       |          |   184K|    64M|   479   (1)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)  | :TQ10000 |   184K|    64M|   479   (1)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  11. |   3 |    VIEW                |          |   184K|    64M|   479   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  12. |   4 |     UNION-ALL          |          |       |       |            |          |  Q1,00 | PCWP |            |  
  13. |   5 |      PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWC |            |  
  14. |   6 |       TABLE ACCESS FULL| T_OBJ    | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  15. |   7 |      PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWC |            |  
  16. |   8 |       TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  17. ----------------------------------------------------------------------------------------------------------------  
  18.   
  19. Outline Data  
  20. -------------  
  21.   
  22.   /*+  
  23.       BEGIN_OUTLINE_DATA  
  24.       FULL(@"SEL$2" "T_OBJ"@"SEL$2")  
  25.       FULL(@"SEL$3" "T_OBJ1"@"SEL$3")  
  26.       NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")  
  27.       OUTLINE_LEAF(@"SEL$1")  
  28.       OUTLINE_LEAF(@"SET$1")  
  29.       OUTLINE_LEAF(@"SEL$3")  
  30.       OUTLINE_LEAF(@"SEL$2")  
  31.       SHARED(2)  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  

PQ_FILTER
Syntax:PQ_FILTER ( SERIAL | NONE | HASH | RANDOM )
Description: The PQ_FILTER hint instructs the optimizer on how to process rows when filtering correlated subqueries.
SERIAL: Process rows serially on the left and right sides of the filter. Use this option when the overhead of parallelization is too high for the query, for example, when the left side has very few rows.
NONE: Process rows in parallel on the left and right sides of the filter. Use this option when there is no skew in the distribution of the data on the left side of the filter and you would like to avoid distribution of the left side, for example, due to the large size of the left side.
HASH: Process rows in parallel on the left side of the filter using a hash distribution. Process rows serially on the right side of the filter. Use this option when there is no skew in the distribution of data on the left side of the filter.
RANDOM: Process rows in parallel on the left side of the filter using a random distribution. Process rows serially on the right side of the filter. Use this option when there is skew in the distribution of data on the left side of the filter.

SQL??
  1. HelloDBA.com> alter session set "_px_filter_parallelized"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel PQ_FILTER(HASH)*/ * from t_obj1 o where created in (select /*+no_unnest*/last_analyzed from t_tab t where tablespace_name like :A)''TYPICAL OUTLINE');  
  3. Plan hash value: 2440581449  
  4.   
  5. ------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT         |          |   110 | 12650 | 22159   (1)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)    | :TQ10001 | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  11. |   3 |    BUFFER SORT           |          |   110 | 12650 |            |          |  Q1,01 | PCWP |            |  
  12. |*  4 |     FILTER               |          |       |       |            |          |  Q1,01 | PCWP |            |  
  13. |   5 |      PX RECEIVE          |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,01 | PCWP |            |  
  14. |   6 |       PX SEND HASH       | :TQ10000 | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | P->P | HASH       |  
  15. |   7 |        PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWC |            |  
  16. |   8 |         TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  17. |*  9 |      TABLE ACCESS FULL   | T_TAB    |     2 |    30 |    26   (0)| 00:00:01 |        |      |            |  
  18. ------------------------------------------------------------------------------------------------------------------  
  19.   
  20. Outline Data  
  21. -------------  
  22.   
  23.   /*+  
  24.       BEGIN_OUTLINE_DATA  
  25.       FULL(@"SEL$2" "T"@"SEL$2")  
  26.       PQ_FILTER(@"SEL$1" HASH)  
  27.       FULL(@"SEL$1" "O"@"SEL$1")  
  28.       OUTLINE_LEAF(@"SEL$1")  
  29.       OUTLINE_LEAF(@"SEL$2")  
  30.       SHARED(2)  
  31.       ALL_ROWS  
  32.       OPT_PARAM('star_transformation_enabled' 'true')  
  33.       OPT_PARAM('_px_filter_parallelized' 'false')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  
  39.   
  40. Predicate Information (identified by operation id):  
  41. ---------------------------------------------------  
  42.   
  43.    4 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_TAB" "T" WHERE "LAST_ANALYZED"=:B1 AND  
  44.               "TABLESPACE_NAME" LIKE :A))  
  45.    9 - filter("LAST_ANALYZED"=:B1 AND "TABLESPACE_NAME" LIKE :A)  
  46.   
  47. HelloDBA.com> alter session set "_px_filter_parallelized"=true;  
  48. HelloDBA.com> exec sql_explain('select /*+parallel PQ_FILTER(SERIAL)*/ * from t_obj1 o where created in (select /*+no_unnest*/last_analyzed from t_tab t where tablespace_name like :A)''TYPICAL OUTLINE');  
  49. Plan hash value: 3734482086  
  50.   
  51. ---------------------------------------------------------------------------------------------------------------  
  52. | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  53. ---------------------------------------------------------------------------------------------------------------  
  54. |   0 | SELECT STATEMENT      |          |   110 | 12650 | 12409   (1)| 00:00:01 |        |      |            |  
  55. |*  1 |  FILTER               |          |       |       |            |          |        |      |            |  
  56. |   2 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |  
  57. |   3 |    PX SEND QC (RANDOM)| :TQ20000 | 92299 |    10M|   240   (1)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |  
  58. |   4 |     PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q2,00 | PCWC |            |  
  59. |   5 |      TABLE ACCESS FULL| T_OBJ1   | 92299 |    10M|   240   (1)| 00:00:01 |  Q2,00 | PCWP |            |  
  60. |   6 |   PX COORDINATOR      |          |       |       |            |          |        |      |            |  
  61. |   7 |    PX SEND QC (RANDOM)| :TQ10000 |     2 |    30 |    14   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  62. |   8 |     PX BLOCK ITERATOR |          |     2 |    30 |    14   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  63. |*  9 |      TABLE ACCESS FULL| T_TAB    |     2 |    30 |    14   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  64. ---------------------------------------------------------------------------------------------------------------  
  65.   
  66. Outline Data  
  67. -------------  
  68.   
  69.   /*+  
  70.       BEGIN_OUTLINE_DATA  
  71.       FULL(@"SEL$2" "T"@"SEL$2")  
  72.       PQ_FILTER(@"SEL$1" SERIAL)  
  73.       FULL(@"SEL$1" "O"@"SEL$1")  
  74.       OUTLINE_LEAF(@"SEL$1")  
  75.       OUTLINE_LEAF(@"SEL$2")  
  76.       SHARED(2)  
  77.       ALL_ROWS  
  78.       OPT_PARAM('star_transformation_enabled' 'true')  
  79.       DB_VERSION('12.1.0.2')  
  80.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  81.       IGNORE_OPTIM_EMBEDDED_HINTS  
  82.       END_OUTLINE_DATA  
  83.   */  

PQ_DISTRIBUTE_WINDOW
Syntax:PQ_DISTRIBUTE_WINDOW ( [ @ queryblock ] 1|2 )
Description: The PQ_DISTRIBUTE_WINDOW hint instructs the optimizer on how to distribute rows generated by window functions.

SQL??
  1. HelloDBA.com> exec sql_explain('select /*+parallel PQ_DISTRIBUTE_WINDOW(1)*/table_name, count(1) over (partition by table_name) cnt from t_tab t''TYPICAL OUTLINE');  
  2. Plan hash value: 4185789934  
  3.   
  4. ----------------------------------------------------------------------------------------------------------------------  
  5. | Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  6. ----------------------------------------------------------------------------------------------------------------------  
  7. |   0 | SELECT STATEMENT           |            |  2426 | 46094 |     4  (25)| 00:00:01 |        |      |            |  
  8. |   1 |  PX COORDINATOR            |            |       |       |            |          |        |      |            |  
  9. |   2 |   PX SEND QC (RANDOM)      | :TQ10001   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  10. |   3 |    WINDOW SORT             |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
  11. |   4 |     PX RECEIVE             |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  12. |   5 |      PX SEND HASH          | :TQ10000   |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |  
  13. |   6 |       PX BLOCK ITERATOR    |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  14. |   7 |        INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  15. ----------------------------------------------------------------------------------------------------------------------  
  16.   
  17. Outline Data  
  18. -------------  
  19.   
  20.   /*+  
  21.       BEGIN_OUTLINE_DATA  
  22.       PQ_DISTRIBUTE_WINDOW(@"SEL$1" 1)  
  23.       INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))  
  24.       OUTLINE_LEAF(@"SEL$1")  
  25.       SHARED(2)  
  26.       ALL_ROWS  
  27.       OPT_PARAM('star_transformation_enabled' 'true')  
  28.       DB_VERSION('12.1.0.2')  
  29.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  30.       IGNORE_OPTIM_EMBEDDED_HINTS  
  31.       END_OUTLINE_DATA  
  32.   */  
  33.   
  34. HelloDBA.com> exec sql_explain('select /*+parallel PQ_DISTRIBUTE_WINDOW(2)*/table_name, count(1) over (partition by table_name) cnt from t_tab t''TYPICAL OUTLINE');  
  35. Plan hash value: 1125815052  
  36.   
  37. ------------------------------------------------------------------------------------------------------------------------  
  38. | Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  39. ------------------------------------------------------------------------------------------------------------------------  
  40. |   0 | SELECT STATEMENT             |            |  2426 | 46094 |     4  (25)| 00:00:01 |        |      |            |  
  41. |   1 |  PX COORDINATOR              |            |       |       |            |          |        |      |            |  
  42. |   2 |   PX SEND QC (RANDOM)        | :TQ10001   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  43. |   3 |    WINDOW CONSOLIDATOR BUFFER|            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
  44. |   4 |     PX RECEIVE               |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,01 | PCWP |            |  
  45. |   5 |      PX SEND HASH            | :TQ10000   |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,00 | P->P | HASH       |  
  46. |   6 |       WINDOW SORT            |            |  2426 | 46094 |     4  (25)| 00:00:01 |  Q1,00 | PCWP |            |  
  47. |   7 |        PX BLOCK ITERATOR     |            |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  48. |   8 |         INDEX FAST FULL SCAN | T_TAB_IDX1 |  2426 | 46094 |     3   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  49. ------------------------------------------------------------------------------------------------------------------------  
  50.   
  51. Outline Data  
  52. -------------  
  53.   
  54.   /*+  
  55.       BEGIN_OUTLINE_DATA  
  56.       PQ_DISTRIBUTE_WINDOW(@"SEL$1" 2)  
  57.       INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))  
  58.       OUTLINE_LEAF(@"SEL$1")  
  59.       SHARED(2)  
  60.       ALL_ROWS  
  61.       OPT_PARAM('star_transformation_enabled' 'true')  
  62.       DB_VERSION('12.1.0.2')  
  63.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  64.       IGNORE_OPTIM_EMBEDDED_HINTS  
  65.       END_OUTLINE_DATA  
  66.   */  

PQ_REPLICATE
Syntax:PQ_REPLICATE ( [ @ queryblock ] tablespec )
Description: The PQ_REPLICATE hint instructs the optimizer to enables replication of small table scans.

SQL??
  1. HelloDBA.com> alter session set "_px_replication_enabled"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel PQ_REPLICATE(o)*/* from t_obj o, t_tab t where o.owner=t.owner and object_name like :A''TYPICAL OUTLINE');  
  3. Plan hash value: 2038113948  
  4.   
  5. ---------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ---------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT      |          |  2764K|   986M|   259   (2)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM) | :TQ10000 |  2764K|   986M|   259   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  11. |*  3 |    HASH JOIN          |          |  2764K|   986M|   259   (2)| 00:00:01 |  Q1,00 | PCWP |            |  
  12. |   4 |     PX BLOCK ITERATOR |          |  2426 |   613K|    16   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  13. |   5 |      TABLE ACCESS FULL| T_TAB    |  2426 |   613K|    16   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  14. |*  6 |     TABLE ACCESS FULL | T_OBJ    |  4615 |   518K|   239   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  15. ---------------------------------------------------------------------------------------------------------------  
  16.   
  17. Outline Data  
  18. -------------  
  19.   
  20.   /*+  
  21.       BEGIN_OUTLINE_DATA  
  22.       PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")  
  23.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" NONE BROADCAST)  
  24.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  25.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  26.       FULL(@"SEL$1" "O"@"SEL$1")  
  27.       FULL(@"SEL$1" "T"@"SEL$1")  
  28.       OUTLINE_LEAF(@"SEL$1")  
  29.       SHARED(2)  
  30.       ALL_ROWS  
  31.       OPT_PARAM('star_transformation_enabled' 'true')  
  32.       OPT_PARAM('_px_replication_enabled' 'false')  
  33.       DB_VERSION('12.1.0.2')  
  34.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  35.       IGNORE_OPTIM_EMBEDDED_HINTS  
  36.       END_OUTLINE_DATA  
  37.   */  

NO_PQ_REPLICATE
Syntax:NO_PQ_REPLICATE ( [ @ queryblock ] tablespec )
Description: The NO_PQ_REPLICATE hint instructs the optimizer to disables replication of small table scans.

SQL??
  1. HelloDBA.com> alter session set "_px_replication_enabled"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel NO_PQ_REPLICATE(o)*/* from t_obj o, t_tab t where o.owner=t.owner and object_name like :A''TYPICAL OUTLINE');  
  3. Plan hash value: 2305795279  
  4.   
  5. -----------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. -----------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT        |          |  2764K|   986M|   259   (2)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR         |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  2764K|   986M|   259   (2)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |  
  11. |*  3 |    HASH JOIN BUFFERED   |          |  2764K|   986M|   259   (2)| 00:00:01 |  Q1,01 | PCWP |            |  
  12. |   4 |     PX BLOCK ITERATOR   |          |  2426 |   613K|    16   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
  13. |   5 |      TABLE ACCESS FULL  | T_TAB    |  2426 |   613K|    16   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  14. |   6 |     PX RECEIVE          |          |  4615 |   518K|   239   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  15. |   7 |      PX SEND BROADCAST  | :TQ10000 |  4615 |   518K|   239   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |  
  16. |   8 |       PX BLOCK ITERATOR |          |  4615 |   518K|   239   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  17. |*  9 |        TABLE ACCESS FULL| T_OBJ    |  4615 |   518K|   239   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  18. -----------------------------------------------------------------------------------------------------------------  
  19.   
  20. Outline Data  
  21. -------------  
  22.   
  23.   /*+  
  24.       BEGIN_OUTLINE_DATA  
  25.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" NONE BROADCAST)  
  26.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  27.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  28.       FULL(@"SEL$1" "O"@"SEL$1")  
  29.       FULL(@"SEL$1" "T"@"SEL$1")  
  30.       OUTLINE_LEAF(@"SEL$1")  
  31.       SHARED(2)  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  

PQ_SKEW
Syntax:PQ_SKEW ( [ @ queryblock ] tablespec )
Description: The PQ_SKEW hint advises the optimizer that the distribution of the values of the join keys for a parallel join is highly skewed?hat is, a high percentage of rows have the same join key values. The table specified in tablespec is the probe table of the hash join.
Note: DOP should be larger than 2

SQL??
  1. HelloDBA.com> alter session set "_px_join_skew_handling"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel(8) PQ_SKEW(O) pq_distribute(O hash hash)*/* from t_obj o, t_tab t where o.owner=t.owner''TYPICAL OUTLINE');  
  3. Plan hash value: 1404870264  
  4.   
  5. ------------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. ------------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT               |          |    55M|    19G|    83  (23)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)          | :TQ10002 |    55M|    19G|    83  (23)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |  
  11. |*  3 |    HASH JOIN BUFFERED          |          |    55M|    19G|    83  (23)| 00:00:01 |  Q1,02 | PCWP |            |  
  12. |   4 |     PX RECEIVE                 |          |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  13. |   5 |      PX SEND HYBRID HASH       | :TQ10000 |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|  
  14. |   6 |       STATISTICS COLLECTOR     |          |       |       |            |          |  Q1,00 | PCWC |            |  
  15. |   7 |        PX BLOCK ITERATOR       |          |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  16. |   8 |         TABLE ACCESS FULL      | T_TAB    |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  17. |   9 |     PX RECEIVE                 |          | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  18. |  10 |      PX SEND HYBRID HASH (SKEW)| :TQ10001 | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|  
  19. |  11 |       PX BLOCK ITERATOR        |          | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
  20. |  12 |        TABLE ACCESS FULL       | T_OBJ    | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  21. ------------------------------------------------------------------------------------------------------------------------  
  22.   
  23. Outline Data  
  24. -------------  
  25.   
  26.   /*+  
  27.       BEGIN_OUTLINE_DATA  
  28.       PQ_SKEW(@"SEL$1" "O"@"SEL$1")  
  29.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" HASH HASH)  
  30.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  31.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  32.       FULL(@"SEL$1" "O"@"SEL$1")  
  33.       FULL(@"SEL$1" "T"@"SEL$1")  
  34.       OUTLINE_LEAF(@"SEL$1")  
  35.       ALL_ROWS  
  36.       OPT_PARAM('star_transformation_enabled' 'true')  
  37.       OPT_PARAM('_px_join_skew_handling' 'false')  
  38.       DB_VERSION('12.1.0.2')  
  39.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  40.       IGNORE_OPTIM_EMBEDDED_HINTS  
  41.       END_OUTLINE_DATA  
  42.   */  
  43.   
  44. Note  
  45. -----  
  46.    - Degree of Parallelism is 8 because of hint  

NO_PQ_SKEW
Syntax:NO_PQ_SKEW ( [ @ queryblock ] tablespec )
Description: The NO_PQ_SKEW hint advises the optimizer that the distribution of the values of the join keys for a parallel join is not skewed?hat is, a high percentage of rows do not have the same join key values. The table specified in tablespec is the probe table of the hash join.

SQL??
  1. HelloDBA.com> alter session set "_px_join_skew_handling"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+parallel(8) NO_PQ_SKEW(O) pq_distribute(O hash hash)*/* from t_obj o, t_tab t where o.owner=t.owner''TYPICAL OUTLINE');  
  3. Plan hash value: 772990711  
  4.   
  5. --------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  7. --------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT           |          |    55M|    19G|    83  (23)| 00:00:01 |        |      |            |  
  9. |   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |  
  10. |   2 |   PX SEND QC (RANDOM)      | :TQ10002 |    55M|    19G|    83  (23)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |  
  11. |*  3 |    HASH JOIN BUFFERED      |          |    55M|    19G|    83  (23)| 00:00:01 |  Q1,02 | PCWP |            |  
  12. |   4 |     PX RECEIVE             |          |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  13. |   5 |      PX SEND HYBRID HASH   | :TQ10000 |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | P->P | HYBRID HASH|  
  14. |   6 |       STATISTICS COLLECTOR |          |       |       |            |          |  Q1,00 | PCWC |            |  
  15. |   7 |        PX BLOCK ITERATOR   |          |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  16. |   8 |         TABLE ACCESS FULL  | T_TAB    |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  17. |   9 |     PX RECEIVE             |          | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,02 | PCWP |            |  
  18. |  10 |      PX SEND HYBRID HASH   | :TQ10001 | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | P->P | HYBRID HASH|  
  19. |  11 |       PX BLOCK ITERATOR    |          | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | PCWC |            |  
  20. |  12 |        TABLE ACCESS FULL   | T_OBJ    | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,01 | PCWP |            |  
  21. --------------------------------------------------------------------------------------------------------------------  
  22.   
  23. Outline Data  
  24. -------------  
  25.   
  26.   /*+  
  27.       BEGIN_OUTLINE_DATA  
  28.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" HASH HASH)  
  29.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  30.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  31.       FULL(@"SEL$1" "O"@"SEL$1")  
  32.       FULL(@"SEL$1" "T"@"SEL$1")  
  33.       OUTLINE_LEAF(@"SEL$1")  
  34.       ALL_ROWS  
  35.       OPT_PARAM('star_transformation_enabled' 'true')  
  36.       DB_VERSION('12.1.0.2')  
  37.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  38.       IGNORE_OPTIM_EMBEDDED_HINTS  
  39.       END_OUTLINE_DATA  
  40.   */  
  41.   
  42. Note  
  43. -----  
  44.    - Degree of Parallelism is 8 because of hint  

PX_FAULT_TOLERANCE
Syntax:PX_FAULT_TOLERANCE
Description: The PX_FAULT_TOLERANCE hint instructs the optimizer to enable fault-tolerance for parallel statement.

SQL??
  1. HelloDBA.com> @showpara  _parallel_fault_tolerance_enabled  
  2.   
  3. Inst Parameter                                                                               Parameter  
  4.   Id Name                                 Modifiable Description                             Value  
  5. ---- ------------------------------------ ---------- --------------------------------------- ------------------------------  
  6.    1 _parallel_fault_tolerance_enabled               enables or disables fault-tolerance for system: FALSE;  
  7.                                                       parallel statement                     session: FALSE;  
  8.   
  9. HelloDBA.com> exec sql_explain('select /*+parallel(8) PX_FAULT_TOLERANCE*/* from t_obj o, t_tab t where o.owner=t.owner''TYPICAL OUTLINE');  
  10. Plan hash value: 2603285128  
  11.   
  12. -----------------------------------------------------------------------------------------------------------------  
  13. | Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  14. -----------------------------------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT        |          |    55M|    19G|    83  (23)| 00:00:01 |        |      |            |  
  16. |   1 |  FAULT-TOLERANCE BUFFER |          |       |       |            |          |        |      |            |  
  17. |   2 |   PX COORDINATOR        |          |       |       |            |          |        |      |            |  
  18. |   3 |    PX SEND QC (RANDOM)  | :TQ10000 |    55M|    19G|    83  (23)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  19. |*  4 |     HASH JOIN           |          |    55M|    19G|    83  (23)| 00:00:01 |  Q1,00 | PCWP |            |  
  20. |   5 |      TABLE ACCESS FULL  | T_TAB    |  2426 |   613K|     4   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  21. |   6 |      PX BLOCK ITERATOR  |          | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,00 | PCWC |            |  
  22. |   7 |       TABLE ACCESS FULL | T_OBJ    | 92299 |    10M|    60   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  23. -----------------------------------------------------------------------------------------------------------------  
  24.   
  25. Outline Data  
  26. -------------  
  27.   
  28.   /*+  
  29.       BEGIN_OUTLINE_DATA  
  30.       PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")  
  31.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" BROADCAST NONE)  
  32.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  33.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  34.       FULL(@"SEL$1" "O"@"SEL$1")  
  35.       FULL(@"SEL$1" "T"@"SEL$1")  
  36.       OUTLINE_LEAF(@"SEL$1")  
  37.       PX_FAULT_TOLERANCE  
  38.       ALL_ROWS  
  39.       OPT_PARAM('star_transformation_enabled' 'true')  
  40.       DB_VERSION('12.1.0.2')  
  41.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  42.       IGNORE_OPTIM_EMBEDDED_HINTS  
  43.       END_OUTLINE_DATA  
  44.   */  

NO_PX_FAULT_TOLERANCE
Syntax:NO_PX_FAULT_TOLERANCE
Description: The NO_PX_FAULT_TOLERANCE hint instructs the optimizer to disable fault-tolerance for parallel statement.

SQL??
  1. HelloDBA.com> exec sql_explain('select /*+parallel(2) NO_PX_FAULT_TOLERANCE*/* from t_obj o, t_tab t where o.owner=t.owner''TYPICAL OUTLINE');  
  2. Plan hash value: 1979616763  
  3.   
  4. ---------------------------------------------------------------------------------------------------------------  
  5. | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |  
  6. ---------------------------------------------------------------------------------------------------------------  
  7. |   0 | SELECT STATEMENT      |          |    55M|    19G|   333  (24)| 00:00:01 |        |      |            |  
  8. |   1 |  PX COORDINATOR       |          |       |       |            |          |        |      |            |  
  9. |   2 |   PX SEND QC (RANDOM) | :TQ10000 |    55M|    19G|   333  (24)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |  
  10. |*  3 |    HASH JOIN          |          |    55M|    19G|   333  (24)| 00:00:01 |  Q1,00 | PCWP |            |  
  11. |   4 |     TABLE ACCESS FULL | T_TAB    |  2426 |   613K|    16   (0)| 00:00:01 |  Q1,00 | PCWP |            |  
  12. |   5 |     PX BLOCK ITERATOR |          | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWC |            |  
  13. |   6 |      TABLE ACCESS FULL| T_OBJ    | 92299 |    10M|   240   (1)| 00:00:01 |  Q1,00 | PCWP |            |  
  14. ---------------------------------------------------------------------------------------------------------------  
  15.   
  16. Outline Data  
  17. -------------  
  18.   
  19.   /*+  
  20.       BEGIN_OUTLINE_DATA  
  21.       PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")  
  22.       PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" BROADCAST NONE)  
  23.       USE_HASH(@"SEL$1" "O"@"SEL$1")  
  24.       LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")  
  25.       FULL(@"SEL$1" "O"@"SEL$1")  
  26.       FULL(@"SEL$1" "T"@"SEL$1")  
  27.       OUTLINE_LEAF(@"SEL$1")  
  28.       ALL_ROWS  
  29.       OPT_PARAM('star_transformation_enabled' 'true')  
  30.       DB_VERSION('12.1.0.2')  
  31.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  32.       IGNORE_OPTIM_EMBEDDED_HINTS  
  33.       END_OUTLINE_DATA  
  34.   */  

ANSI_REARCH
Syntax:ANSI_REARCH ( [ @ queryblock ] )
Description: The ANSI_REARCH hint instructs the optimizer to re-architecture of ANSI left, right, and full outer joins.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_ansi_rearchitecture"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+ANSI_REARCH*/t.tablespace_name from t_obj o left outer join t_tab t on t.owner like :A and o.owner=t.owner''TYPICAL OUTLINE');  
  3. Plan hash value: 220790772  
  4.   
  5. --------------------------------------------------------------------------------------------  
  6. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. --------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT              |            |  2824K|    53M|    41  (18)| 00:00:01 |  
  9. |*  1 |  HASH JOIN RIGHT OUTER        |            |  2824K|    53M|    41  (18)| 00:00:01 |  
  10. |*  2 |   TABLE ACCESS FULL           | T_TAB      |   122 |  1708 |    29   (0)| 00:00:01 |  
  11. |   3 |   BITMAP CONVERSION TO ROWIDS |            | 92299 |   540K|     5   (0)| 00:00:01 |  
  12. |   4 |    BITMAP INDEX FAST FULL SCAN| T_OBJ_IDX1 |       |       |            |          |  
  13. --------------------------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T"@"SEL$1")  
  21.       USE_HASH(@"SEL$2BFA4EE4" "T"@"SEL$1")  
  22.       LEADING(@"SEL$2BFA4EE4" "O"@"SEL$1" "T"@"SEL$1")  
  23.       FULL(@"SEL$2BFA4EE4" "T"@"SEL$1")  
  24.       INDEX_FFS(@"SEL$2BFA4EE4" "O"@"SEL$1" ("T_OBJ"."OWNER"))  
  25.       OUTLINE(@"SEL$1")  
  26.       OUTLINE(@"SEL$2")  
  27.       ANSI_REARCH(@"SEL$1")  
  28.       OUTLINE(@"SEL$8812AA4E")  
  29.       ANSI_REARCH(@"SEL$2")  
  30.       OUTLINE(@"SEL$948754D7")  
  31.       MERGE(@"SEL$8812AA4E")  
  32.       OUTLINE_LEAF(@"SEL$2BFA4EE4")  
  33.       ALL_ROWS  
  34.       OPT_PARAM('star_transformation_enabled' 'true')  
  35.       OPT_PARAM('_optimizer_ansi_rearchitecture' 'false')  
  36.       DB_VERSION('12.1.0.2')  
  37.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  38.       IGNORE_OPTIM_EMBEDDED_HINTS  
  39.       END_OUTLINE_DATA  
  40.   */  

NO_ANSI_REARCH
Syntax:NO_ANSI_REARCH ( [ @ queryblock ] )
Description: The NO_ANSI_REARCH hint instructs the optimizer not to re-architecture of ANSI left, right, and full outer joins.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_ansi_rearchitecture"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_ANSI_REARCH*/t.tablespace_name from t_obj o left outer join t_tab t on t.owner like :A and o.owner=t.owner''TYPICAL OUTLINE');  
  3. Plan hash value: 220790772  
  4.   
  5. --------------------------------------------------------------------------------------------  
  6. | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. --------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT              |            |  2824K|    53M|    41  (18)| 00:00:01 |  
  9. |*  1 |  HASH JOIN RIGHT OUTER        |            |  2824K|    53M|    41  (18)| 00:00:01 |  
  10. |*  2 |   TABLE ACCESS FULL           | T_TAB      |   122 |  1708 |    29   (0)| 00:00:01 |  
  11. |   3 |   BITMAP CONVERSION TO ROWIDS |            | 92299 |   540K|     5   (0)| 00:00:01 |  
  12. |   4 |    BITMAP INDEX FAST FULL SCAN| T_OBJ_IDX1 |       |       |            |          |  
  13. --------------------------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       SWAP_JOIN_INPUTS(@"SEL$9E43CB6E" "T"@"SEL$1")  
  21.       USE_HASH(@"SEL$9E43CB6E" "T"@"SEL$1")  
  22.       LEADING(@"SEL$9E43CB6E" "O"@"SEL$2" "T"@"SEL$1")  
  23.       FULL(@"SEL$9E43CB6E" "T"@"SEL$1")  
  24.       INDEX_FFS(@"SEL$9E43CB6E" "O"@"SEL$2" ("T_OBJ"."OWNER"))  
  25.       OUTLINE(@"SEL$1")  
  26.       OUTLINE(@"SEL$2")  
  27.       MERGE(@"SEL$1")  
  28.       OUTLINE(@"SEL$58A6D7F6")  
  29.       OUTLINE(@"SEL$3")  
  30.       MERGE(@"SEL$58A6D7F6")  
  31.       OUTLINE_LEAF(@"SEL$9E43CB6E")  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  

ELIM_GROUPBY
Syntax:ELIM_GROUPBY ( [ @ queryblock ] )
Description: The ELIM_GROUPBY hint instructs the optimizer to do query transformation to eliminate group-by and aggregation.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_aggr_groupby_elim"=false;  
  2. HelloDBA.com> exec sql_explain('select /*+ELIM_GROUPBY*/owner, count(*) from (select owner, sum(num_rows) from t_tab group by owner) group by owner''TYPICAL OUTLINE');  
  3. Plan hash value: 1150833919  
  4.   
  5. ------------------------------------------------------------------------------------  
  6. | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT      |            |    20 |   140 |     6  (17)| 00:00:01 |  
  9. |   1 |  HASH GROUP BY        |            |    20 |   140 |     6  (17)| 00:00:01 |  
  10. |   2 |   INDEX FAST FULL SCAN| T_TAB_IDX1 |  2426 | 16982 |     5   (0)| 00:00:01 |  
  11. ------------------------------------------------------------------------------------  
  12.   
  13. Outline Data  
  14. -------------  
  15.   
  16.   /*+  
  17.       BEGIN_OUTLINE_DATA  
  18.       USE_HASH_AGGREGATION(@"SEL$D07AE25A")  
  19.       INDEX_FFS(@"SEL$D07AE25A" "T_TAB"@"SEL$2" ("T_TAB"."OWNER"  
  20.               "T_TAB"."TABLE_NAME"))  
  21.       OUTLINE(@"SEL$1")  
  22.       OUTLINE(@"SEL$2")  
  23.       ELIM_GROUPBY(@"SEL$1")  
  24.       OUTLINE(@"SEL$47952E7A")  
  25.       MERGE(@"SEL$2")  
  26.       OUTLINE_LEAF(@"SEL$D07AE25A")  
  27.       ALL_ROWS  
  28.       OPT_PARAM('star_transformation_enabled' 'true')  
  29.       OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')  
  30.       DB_VERSION('12.1.0.2')  
  31.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  32.       IGNORE_OPTIM_EMBEDDED_HINTS  
  33.       END_OUTLINE_DATA  
  34.   */  

NO_ELIM_GROUPBY
Syntax:NO_ELIM_GROUPBY ( [ @ queryblock ] )
Description: The NO_ELIM_GROUPBY hint instructs the optimizer not to do query transformation to eliminate group-by and aggregation.

SQL??
  1. HelloDBA.com> alter session set "_optimizer_aggr_groupby_elim"=true;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_ELIM_GROUPBY*/owner, count(*) from (select owner, sum(num_rows) from t_tab group by owner) group by owner''TYPICAL OUTLINE');  
  3. Plan hash value: 4120618623  
  4.   
  5. ------------------------------------------------------------------------------  
  6. | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT     |       |    20 |   140 |    30   (4)| 00:00:01 |  
  9. |   1 |  HASH GROUP BY       |       |    20 |   140 |    30   (4)| 00:00:01 |  
  10. |   2 |   VIEW               |       |    20 |   140 |    30   (4)| 00:00:01 |  
  11. |   3 |    HASH GROUP BY     |       |    20 |   200 |    30   (4)| 00:00:01 |  
  12. |   4 |     TABLE ACCESS FULL| T_TAB |  2426 | 24260 |    29   (0)| 00:00:01 |  
  13. ------------------------------------------------------------------------------  
  14.   
  15. Outline Data  
  16. -------------  
  17.   
  18.   /*+  
  19.       BEGIN_OUTLINE_DATA  
  20.       USE_HASH_AGGREGATION(@"SEL$2")  
  21.       FULL(@"SEL$2" "T_TAB"@"SEL$2")  
  22.       USE_HASH_AGGREGATION(@"SEL$1")  
  23.       NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")  
  24.       OUTLINE_LEAF(@"SEL$1")  
  25.       OUTLINE_LEAF(@"SEL$2")  
  26.       ALL_ROWS  
  27.       OPT_PARAM('star_transformation_enabled' 'true')  
  28.       DB_VERSION('12.1.0.2')  
  29.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  30.       IGNORE_OPTIM_EMBEDDED_HINTS  
  31.       END_OUTLINE_DATA  
  32.   */  

INMEMORY
Syntax:INMEMORY ( [ @ queryblock ] tablespec )
Description: The INMEMORY hint enables in-memory queries.

SQL??
  1. HelloDBA.com> alter table t_obj1 inmemory;  
  2. HelloDBA.com> alter session set "inmemory_query"=disable;  
  3. HelloDBA.com> exec sql_explain('select /*+INMEMORY(o)*/* from t_obj1 o''TYPICAL OUTLINE');  
  4. Plan hash value: 1936150373  
  5.   
  6. -------------------------------------------------------------------------------------  
  7. | Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  8. -------------------------------------------------------------------------------------  
  9. |   0 | SELECT STATEMENT           |        | 92299 |    10M|    21  (24)| 00:00:01 |  
  10. |   1 |  TABLE ACCESS INMEMORY FULL| T_OBJ1 | 92299 |    10M|    21  (24)| 00:00:01 |  
  11. -------------------------------------------------------------------------------------  
  12.   
  13. Outline Data  
  14. -------------  
  15.   
  16.   /*+  
  17.       BEGIN_OUTLINE_DATA  
  18.       FULL(@"SEL$1" "O"@"SEL$1")  
  19.       OUTLINE_LEAF(@"SEL$1")  
  20.       ALL_ROWS  
  21.       OPT_PARAM('star_transformation_enabled' 'true')  
  22.       DB_VERSION('12.1.0.2')  
  23.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  24.       IGNORE_OPTIM_EMBEDDED_HINTS  
  25.       END_OUTLINE_DATA  
  26.   */  

NO_INMEMORY
Syntax:NO_INMEMORY ( [ @ queryblock ] tablespec )
Description: The NO_INMEMORY hint disables in-memory queries.

SQL??
  1. HelloDBA.com> alter session set "inmemory_query"=enable;  
  2. HelloDBA.com> exec sql_explain('select /*+NO_INMEMORY(o)*/* from t_obj1 o''TYPICAL OUTLINE');  
  3. Plan hash value: 1936150373  
  4.   
  5. ----------------------------------------------------------------------------  
  6. | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  7. ----------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT  |        | 92299 |    10M|   431   (1)| 00:00:01 |  
  9. |   1 |  TABLE ACCESS FULL| T_OBJ1 | 92299 |    10M|   431   (1)| 00:00:01 |  
  10. ----------------------------------------------------------------------------  
  11.   
  12. Outline Data  
  13. -------------  
  14.   
  15.   /*+  
  16.       BEGIN_OUTLINE_DATA  
  17.       FULL(@"SEL$1" "O"@"SEL$1")  
  18.       OUTLINE_LEAF(@"SEL$1")  
  19.       ALL_ROWS  
  20.       OPT_PARAM('star_transformation_enabled' 'true')  
  21.       DB_VERSION('12.1.0.2')  
  22.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  23.       IGNORE_OPTIM_EMBEDDED_HINTS  
  24.       END_OUTLINE_DATA  
  25.   */  

INMEMORY_PRUNING
Syntax:INMEMORY_PRUNING ( [ @ queryblock ] tablespec )
Description: The INMEMORY_PRUNING hint enables pruning of in-memory queries.

SQL??
  1. HelloDBA.com> alter table t_obj1 inmemory priority critical;  
  2. HelloDBA.com> select * from t_obj1;  
  3. HelloDBA.com> alter session set "_inmemory_pruning"=off;  
  4. HelloDBA.com> select name,value, sysdate cap_time from v$statname n, v$mystat s where name = 'IM scan CUs pruned' and n.statistic#=s.statistic#;  
  5.   
  6. NAME                                                                  VALUE CAP_TIME  
  7. ---------------------------------------------------------------- ---------- -------------------  
  8. IM scan CUs pruned                                                        0 2014-10-03 15:06:19  
  9. HelloDBA.com> select /*+INMEMORY_PRUNING(o)*/owner from t_obj1 o where object_id = 1;  
  10. HelloDBA.com> select name,value, sysdate cap_time from v$statname n, v$mystat s where name = 'IM scan CUs pruned' and n.statistic#=s.statistic#;  
  11.   
  12. NAME                                                                  VALUE CAP_TIME  
  13. ---------------------------------------------------------------- ---------- -------------------  
  14. IM scan CUs pruned                                                        1 2014-10-03 15:06:37  

NO_INMEMORY_PRUNING
Syntax:NO_INMEMORY_PRUNING ( [ @ queryblock ] tablespec )
Description: The NO_INMEMORY_PRUNING hint disables pruning of in-memory queries.

SQL??
  1. HelloDBA.com> alter session set "_inmemory_pruning"=on;  
  2. HelloDBA.com> select name,value, sysdate cap_time from v$statname n, v$mystat s where name = 'IM scan CUs pruned' and n.statistic#=s.statistic#;  
  3.   
  4. NAME                                                                  VALUE CAP_TIME  
  5. ---------------------------------------------------------------- ---------- -------------------  
  6. IM scan CUs pruned                                                        1 2014-10-03 15:06:37  
  7. HelloDBA.com> select /*+NO_INMEMORY_PRUNING(o)*/owner from t_obj1 o where object_id = 1;  
  8. HelloDBA.com> select name,value, sysdate cap_time from v$statname n, v$mystat s where name = 'IM scan CUs pruned' and n.statistic#=s.statistic#;  
  9.   
  10. NAME                                                                  VALUE CAP_TIME  
  11. ---------------------------------------------------------------- ---------- -------------------  
  12. IM scan CUs pruned                                                        1 2014-10-03 15:06:54  

USE_VECTOR_AGGREGATION
Syntax:USE_VECTOR_AGGREGATION ( [ @ queryblock ] tablespec )
Description: The USE_VECTOR_AGGREGATION hint enables group-by and aggregation using vector scheme.

SQL??
  1. HelloDBA.com> alter session set "_vector_aggregation_max_size"=8192;  
  2. HelloDBA.com> exec dbms_stats.gather_table_stats(user,'T_FACT', method_opt => 'for all columns size 1');  
  3. HelloDBA.com> exec dbms_stats.gather_table_stats(user,'CATEGORY_DIM', method_opt => 'for all columns size 1');  
  4. HelloDBA.com> exec dbms_stats.gather_table_stats(user,'DATE_DIM', method_opt => 'for all columns size 1');  
  5. HelloDBA.com> exec dbms_stats.gather_table_stats(user,'USER_DIM', method_opt => 'for all columns size 1');  
  6. HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=false;  
  7. HelloDBA.com> alter session set "_optimizer_vector_transformation"=false;  
  8. HelloDBA.com> alter session set "_always_vector_transformation"=false;  
  9. HelloDBA.com> exec sql_explain('select /*+VECTOR_TRANSFORM(@"SEL$1" FACT("F"@"SEL$1") DIMENSION("D"@"SEL$1") DIMENSION("U"@"SEL$1"))*/d.MONTH, u.CITY, max(f.SCORE) from T_FACT f, DATE_DIM d, USER_DIM u where f.COMPLETED_DATE_KEY=d.key and d.MONTH=:2 and f.MANAGER_KEY=u.key and u.IS_MANAGER=:1 group by d.MONTH, u.CITY''TYPICAL OUTLINE');  
  10. Plan hash value: 100339589  
  11.   
  12. -----------------------------------------------------------------------------------------------------------------------  
  13. | Id  | Operation                         | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  14. -----------------------------------------------------------------------------------------------------------------------  
  15. |   0 | SELECT STATEMENT                  |                           |  6944 |   637K|       |  2471   (2)| 00:00:01 |  
  16. |   1 |  TEMP TABLE TRANSFORMATION        |                           |       |       |       |            |          |  
  17. |   2 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6672_C96885 |       |       |       |            |          |  
  18. |   3 |    HASH GROUP BY                  |                           |     1 |    23 |       |    13  (16)| 00:00:01 |  
  19. |   4 |     KEY VECTOR CREATE BUFFERED    | :KV0000                   |    22 |   506 |       |    13  (16)| 00:00:01 |  
  20. |*  5 |      TABLE ACCESS INMEMORY FULL   | DATE_DIM                  |    22 |   418 |       |    11   (0)| 00:00:01 |  
  21. |   6 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D6673_C96885 |       |       |       |            |          |  
  22. |   7 |    HASH GROUP BY                  |                           |  9819 |   230K|  6712K|  2440   (2)| 00:00:01 |  
  23. |   8 |     KEY VECTOR CREATE BUFFERED    | :KV0001                   |   213K|  5006K|       |  2440   (2)| 00:00:01 |  
  24. |*  9 |      TABLE ACCESS INMEMORY FULL   | USER_DIM                  |   213K|  4172K|       |  1138   (3)| 00:00:01 |  
  25. |  10 |   HASH GROUP BY                   |                           |  6944 |   637K|       |    17  (12)| 00:00:01 |  
  26. |* 11 |    HASH JOIN                      |                           |  6944 |   637K|       |    16   (7)| 00:00:01 |  
  27. |  12 |     VIEW                          | VW_VT_AF278325            |  6944 |   318K|       |     4  (25)| 00:00:01 |  
  28. |  13 |      HASH GROUP BY                |                           |  6944 |   135K|       |     4  (25)| 00:00:01 |  
  29. |  14 |       KEY VECTOR USE              | :KV0001                   |  6944 |   135K|       |     4  (25)| 00:00:01 |  
  30. |  15 |        KEY VECTOR USE             | :KV0000                   |  6944 |   108K|       |     4  (25)| 00:00:01 |  
  31. |* 16 |         TABLE ACCESS INMEMORY FULL| T_FACT                    | 10000 |   117K|       |     3   (0)| 00:00:01 |  
  32. |  17 |     MERGE JOIN CARTESIAN          |                           |  9819 |   450K|       |    12   (0)| 00:00:01 |  
  33. |  18 |      TABLE ACCESS FULL            | SYS_TEMP_0FD9D6672_C96885 |     1 |    23 |       |     2   (0)| 00:00:01 |  
  34. |  19 |      BUFFER SORT                  |                           |  9819 |   230K|       |    10   (0)| 00:00:01 |  
  35. |  20 |       TABLE ACCESS FULL           | SYS_TEMP_0FD9D6673_C96885 |  9819 |   230K|       |    10   (0)| 00:00:01 |  
  36. -----------------------------------------------------------------------------------------------------------------------  
  37.   
  38. Outline Data  
  39. -------------  
  40.   
  41.   /*+  
  42.       BEGIN_OUTLINE_DATA  
  43.       USE_HASH_AGGREGATION(@"SEL$0333970C")  
  44.       FULL(@"SEL$0333970C" "D"@"SEL$1")  
  45.       USE_HASH_AGGREGATION(@"SEL$D0E111EF")  
  46.       FULL(@"SEL$D0E111EF" "U"@"SEL$1")  
  47.       USE_HASH_AGGREGATION(@"SEL$709E21E9")  
  48.       FULL(@"SEL$709E21E9" "F"@"SEL$1")  
  49.       USE_HASH_AGGREGATION(@"SEL$A6BF8214")  
  50.       SWAP_JOIN_INPUTS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  51.       USE_HASH(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  52.       USE_MERGE_CARTESIAN(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")  
  53.       LEADING(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9" "TT_VT_5"@"SEL$0CAF0D26" "VW_VT_AF278325"@"SEL$AF278325")  
  54.       NO_ACCESS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  55.       FULL(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")  
  56.       FULL(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9")  
  57.       OUTLINE(@"SEL$1")  
  58.       OUTLINE(@"SEL$AF278325")  
  59.       OUTLINE(@"SEL$6E7BB6AB")  
  60.       OUTLINE(@"SEL$3ACFCBE9")  
  61.       VECTOR_TRANSFORM(@"SEL$1" FACT("F"@"SEL$1") DIMENSION("D"@"SEL$1") DIMENSION("U"@"SEL$1"))  
  62.       OUTLINE_LEAF(@"SEL$A6BF8214")  
  63.       OUTLINE_LEAF(@"SEL$709E21E9")  
  64.       OUTLINE_LEAF(@"SEL$D0E111EF")  
  65.       OUTLINE_LEAF(@"SEL$0333970C")  
  66.       ALL_ROWS  
  67.       OPT_PARAM('star_transformation_enabled' 'true')  
  68.       OPT_PARAM('_gby_vector_aggregation_enabled' 'false')  
  69.       DB_VERSION('12.1.0.2')  
  70.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  71.       IGNORE_OPTIM_EMBEDDED_HINTS  
  72.       END_OUTLINE_DATA  
  73.   */  

NO_USE_VECTOR_AGGREGATION
Syntax:NO_USE_VECTOR_AGGREGATION ( [ @ queryblock ] tablespec )
Description: The NO_USE_VECTOR_AGGREGATION hint disnables group-by and aggregation using vector scheme.

SQL??
  1. HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;  
  2. HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;  
  3. HelloDBA.com> alter session set "_always_vector_transformation"=true;  
  4. HelloDBA.com> exec sql_explain('select /*+NO_VECTOR_TRANSFORM*/d.MONTH, u.CITY, max(f.SCORE) from T_FACT f, DATE_DIM d, USER_DIM u where f.COMPLETED_DATE_KEY=d.key and d.MONTH=:2 and f.MANAGER_KEY=u.key and u.IS_MANAGER=:1 group by d.MONTH, u.CITY''TYPICAL OUTLINE');  
  5. Plan hash value: 397045964  
  6.   
  7. --------------------------------------------------------------------------------------------  
  8. | Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. --------------------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT                |          |     1 |    51 |  1153   (3)| 00:00:01 |  
  11. |   1 |  HASH GROUP BY                  |          |     1 |    51 |  1153   (3)| 00:00:01 |  
  12. |*  2 |   HASH JOIN                     |          |   425 | 21675 |  1152   (3)| 00:00:01 |  
  13. |   3 |    JOIN FILTER CREATE           | :BF0000  |   424 | 13144 |    14   (0)| 00:00:01 |  
  14. |*  4 |     HASH JOIN                   |          |   424 | 13144 |    14   (0)| 00:00:01 |  
  15. |   5 |      JOIN FILTER CREATE         | :BF0001  |    22 |   418 |    11   (0)| 00:00:01 |  
  16. |*  6 |       TABLE ACCESS INMEMORY FULL| DATE_DIM |    22 |   418 |    11   (0)| 00:00:01 |  
  17. |   7 |      JOIN FILTER USE            | :BF0001  | 10000 |   117K|     3   (0)| 00:00:01 |  
  18. |*  8 |       TABLE ACCESS INMEMORY FULL| T_FACT   | 10000 |   117K|     3   (0)| 00:00:01 |  
  19. |   9 |    JOIN FILTER USE              | :BF0000  |   213K|  4172K|  1138   (3)| 00:00:01 |  
  20. |* 10 |     TABLE ACCESS INMEMORY FULL  | USER_DIM |   213K|  4172K|  1138   (3)| 00:00:01 |  
  21. --------------------------------------------------------------------------------------------  
  22.   
  23. Outline Data  
  24. -------------  
  25.   
  26.   /*+  
  27.       BEGIN_OUTLINE_DATA  
  28.       USE_HASH_AGGREGATION(@"SEL$1")  
  29.       PX_JOIN_FILTER(@"SEL$1" "U"@"SEL$1")  
  30.       PX_JOIN_FILTER(@"SEL$1" "F"@"SEL$1")  
  31.       USE_HASH(@"SEL$1" "U"@"SEL$1")  
  32.       USE_HASH(@"SEL$1" "F"@"SEL$1")  
  33.       LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")  
  34.       FULL(@"SEL$1" "U"@"SEL$1")  
  35.       FULL(@"SEL$1" "F"@"SEL$1")  
  36.       FULL(@"SEL$1" "D"@"SEL$1")  
  37.       OUTLINE_LEAF(@"SEL$1")  
  38.       ALL_ROWS  
  39.       OPT_PARAM('star_transformation_enabled' 'true')  
  40.       DB_VERSION('12.1.0.2')  
  41.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  42.       IGNORE_OPTIM_EMBEDDED_HINTS  
  43.       END_OUTLINE_DATA  
  44.   */  

VECTOR_TRANSFORM
Syntax:VECTOR_TRANSFORM ( [ @ queryblock ] [ FACT([ @ queryblock ] tablespec) ] [ DIMENSION([ @ queryblock ] tablespec) ... ] )
Description: VECTOR_TRANSFORM enables the vector transformation on the specified query block, regardless of costing.
Refer to the example of USE_VECTOR_AGGREGATION

NO_VECTOR_TRANSFORM
Syntax:NO_VECTOR_TRANSFORM ( [ @ queryblock ] )
Description: NO_VECTOR_TRANSFORM disables the vector transformation from engaging on the specified query block.
Refer to the example of NO_USE_VECTOR_AGGREGATION

VECTOR_TRANSFORM_FACT
Syntax:VECTOR_TRANSFORM_FACT ( [ @ queryblock ] tablespec )
Description: VECTOR_TRANSFORM_FACT includes the specified FROM expressions in the fact table generated by the vector transformation.

SQL??
  1. HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=false;  
  2. HelloDBA.com> alter session set "_optimizer_vector_transformation"=false;  
  3. HelloDBA.com> alter session set "_always_vector_transformation"=false;  
  4. HelloDBA.com> exec sql_explain('select /*+VECTOR_TRANSFORM VECTOR_TRANSFORM_FACT(F) VECTOR_TRANSFORM_DIMS(U) VECTOR_TRANSFORM_DIMS(D)*/d.MONTH, u.CITY, max(f.SCORE) from T_FACT f, DATE_DIM d, USER_DIM u where f.COMPLETED_DATE_KEY=d.key and d.MONTH=:2 and f.MANAGER_KEY=u.key and u.IS_MANAGER=:1 group by d.MONTH, u.CITY''TYPICAL OUTLINE');  
  5. Plan hash value: 100339589  
  6.   
  7. -------------------------------------------------------------------------------------------------------------------  
  8. | Id  | Operation                     | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT              |                           |  6944 |   637K|       | 31857   (1)| 00:00:02 |  
  11. |   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |       |            |          |  
  12. |   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6684_C96885 |       |       |       |            |          |  
  13. |   3 |    HASH GROUP BY              |                           |     1 |    23 |       |   289   (1)| 00:00:01 |  
  14. |   4 |     KEY VECTOR CREATE BUFFERED| :KV0000                   |    22 |   506 |       |   289   (1)| 00:00:01 |  
  15. |*  5 |      TABLE ACCESS FULL        | DATE_DIM                  |    22 |   418 |       |   287   (0)| 00:00:01 |  
  16. |   6 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6685_C96885 |       |       |       |            |          |  
  17. |   7 |    HASH GROUP BY              |                           |  9819 |   230K|  6712K| 31492   (1)| 00:00:02 |  
  18. |   8 |     KEY VECTOR CREATE BUFFERED| :KV0001                   |   213K|  5006K|       | 31492   (1)| 00:00:02 |  
  19. |*  9 |      TABLE ACCESS FULL        | USER_DIM                  |   213K|  4172K|       | 30189   (1)| 00:00:02 |  
  20. |  10 |   HASH GROUP BY               |                           |  6944 |   637K|       |    76   (4)| 00:00:01 |  
  21. |* 11 |    HASH JOIN                  |                           |  6944 |   637K|       |    74   (2)| 00:00:01 |  
  22. |  12 |     VIEW                      | VW_VT_AF278325            |  6944 |   318K|       |    62   (2)| 00:00:01 |  
  23. |  13 |      HASH GROUP BY            |                           |  6944 |   135K|       |    62   (2)| 00:00:01 |  
  24. |  14 |       KEY VECTOR USE          | :KV0001                   |  6944 |   135K|       |    62   (2)| 00:00:01 |  
  25. |  15 |        KEY VECTOR USE         | :KV0000                   |  6944 |   108K|       |    62   (2)| 00:00:01 |  
  26. |* 16 |         TABLE ACCESS FULL     | T_FACT                    | 10000 |   117K|       |    61   (0)| 00:00:01 |  
  27. |  17 |     MERGE JOIN CARTESIAN      |                           |  9819 |   450K|       |    12   (0)| 00:00:01 |  
  28. |  18 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6684_C96885 |     1 |    23 |       |     2   (0)| 00:00:01 |  
  29. |  19 |      BUFFER SORT              |                           |  9819 |   230K|       |    10   (0)| 00:00:01 |  
  30. |  20 |       TABLE ACCESS FULL       | SYS_TEMP_0FD9D6685_C96885 |  9819 |   230K|       |    10   (0)| 00:00:01 |  
  31. -------------------------------------------------------------------------------------------------------------------  
  32.   
  33. Outline Data  
  34. -------------  
  35.   
  36.   /*+  
  37.       BEGIN_OUTLINE_DATA  
  38.       USE_HASH_AGGREGATION(@"SEL$0333970C")  
  39.       FULL(@"SEL$0333970C" "D"@"SEL$1")  
  40.       USE_HASH_AGGREGATION(@"SEL$D0E111EF")  
  41.       FULL(@"SEL$D0E111EF" "U"@"SEL$1")  
  42.       USE_HASH_AGGREGATION(@"SEL$709E21E9")  
  43.       FULL(@"SEL$709E21E9" "F"@"SEL$1")  
  44.       USE_HASH_AGGREGATION(@"SEL$A6BF8214")  
  45.       SWAP_JOIN_INPUTS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  46.       USE_HASH(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  47.       USE_MERGE_CARTESIAN(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")  
  48.       LEADING(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9" "TT_VT_5"@"SEL$0CAF0D26" "VW_VT_AF278325"@"SEL$AF278325")  
  49.       NO_ACCESS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")  
  50.       FULL(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")  
  51.       FULL(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9")  
  52.       OUTLINE(@"SEL$1")  
  53.       OUTLINE(@"SEL$AF278325")  
  54.       OUTLINE(@"SEL$6E7BB6AB")  
  55.       OUTLINE(@"SEL$3ACFCBE9")  
  56.       VECTOR_TRANSFORM(@"SEL$1" FACT("F"@"SEL$1") DIMENSION("D"@"SEL$1") DIMENSION("U"@"SEL$1"))  
  57.       OUTLINE_LEAF(@"SEL$A6BF8214")  
  58.       OUTLINE_LEAF(@"SEL$709E21E9")  
  59.       OUTLINE_LEAF(@"SEL$D0E111EF")  
  60.       OUTLINE_LEAF(@"SEL$0333970C")  
  61.       ALL_ROWS  
  62.       OPT_PARAM('star_transformation_enabled' 'true')  
  63.       OPT_PARAM('_optimizer_vector_transformation' 'false')  
  64.       OPT_PARAM('_gby_vector_aggregation_enabled' 'false')  
  65.       DB_VERSION('12.1.0.2')  
  66.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  67.       IGNORE_OPTIM_EMBEDDED_HINTS  
  68.       END_OUTLINE_DATA  
  69.   */  

NO_VECTOR_TRANSFORM_FACT
Syntax:NO_VECTOR_TRANSFORM_FACT ( [ @ queryblock ] tablespec )
Description: NO_VECTOR_TRANSFORM_FACT excludes the specified FROM expressions from the fact table generated by the vector transformation.

SQL??
  1. HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;  
  2. HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;  
  3. HelloDBA.com> alter session set "_always_vector_transformation"=true;  
  4. HelloDBA.com> exec sql_explain('select /*+ NO_VECTOR_TRANSFORM_FACT(F) */d.MONTH, u.CITY, max(f.SCORE) from T_FACT f, DATE_DIM d, USER_DIM u where f.COMPLETED_DATE_KEY=d.key and d.MONTH=:2 and f.MANAGER_KEY=u.key and u.IS_MANAGER=:1 group by d.MONTH, u.CITY''TYPICAL OUTLINE');  
  5. Plan hash value: 762306316  
  6.   
  7. ---------------------------------------------------------------------------------  
  8. | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. ---------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT     |          |     1 |    51 | 30540   (1)| 00:00:02 |  
  11. |   1 |  HASH GROUP BY       |          |     1 |    51 | 30540   (1)| 00:00:02 |  
  12. |*  2 |   HASH JOIN          |          |   425 | 21675 | 30539   (1)| 00:00:02 |  
  13. |*  3 |    HASH JOIN         |          |   424 | 13144 |   349   (1)| 00:00:01 |  
  14. |*  4 |     TABLE ACCESS FULL| DATE_DIM |    22 |   418 |   287   (0)| 00:00:01 |  
  15. |   5 |     TABLE ACCESS FULL| T_FACT   | 10000 |   117K|    61   (0)| 00:00:01 |  
  16. |*  6 |    TABLE ACCESS FULL | USER_DIM |   213K|  4172K| 30189   (1)| 00:00:02 |  
  17. ---------------------------------------------------------------------------------  
  18.   
  19. Outline Data  
  20. -------------  
  21.   
  22.   /*+  
  23.       BEGIN_OUTLINE_DATA  
  24.       USE_HASH_AGGREGATION(@"SEL$1")  
  25.       USE_HASH(@"SEL$1" "U"@"SEL$1")  
  26.       USE_HASH(@"SEL$1" "F"@"SEL$1")  
  27.       LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")  
  28.       FULL(@"SEL$1" "U"@"SEL$1")  
  29.       FULL(@"SEL$1" "F"@"SEL$1")  
  30.       FULL(@"SEL$1" "D"@"SEL$1")  
  31.       OUTLINE_LEAF(@"SEL$1")  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  

VECTOR_TRANSFORM_DIMS
Syntax:VECTOR_TRANSFORM_DIMS ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: VECTOR_TRANSFORM_DIMS includes the specified FROM expressions in enabled dimensions generated by the vector transformation.
Refer to the example of VECTOR_TRANSFORM_FACT

NO_VECTOR_TRANSFORM_DIMS
Syntax:NO_VECTOR_TRANSFORM_DIMS ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: NO_VECTOR_TRANSFORM_DIMS excludes the specified from expressions from enabled dimensions generated by the vector transformation.

SQL??
  1. HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;  
  2. HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;  
  3. HelloDBA.com> alter session set "_always_vector_transformation"=true;  
  4. HelloDBA.com> exec sql_explain('select /*+ NO_VECTOR_TRANSFORM_DIMS(U) NO_VECTOR_TRANSFORM_DIMS(D)*/d.MONTH, u.CITY, max(f.SCORE) from T_FACT f, DATE_DIM d, USER_DIM u where f.COMPLETED_DATE_KEY=d.key and d.MONTH=:2 and f.MANAGER_KEY=u.key and u.IS_MANAGER=:1 group by d.MONTH, u.CITY''TYPICAL OUTLINE');  
  5. Plan hash value: 762306316  
  6.   
  7. ---------------------------------------------------------------------------------  
  8. | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. ---------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT     |          |     1 |    51 | 30540   (1)| 00:00:02 |  
  11. |   1 |  HASH GROUP BY       |          |     1 |    51 | 30540   (1)| 00:00:02 |  
  12. |*  2 |   HASH JOIN          |          |   425 | 21675 | 30539   (1)| 00:00:02 |  
  13. |*  3 |    HASH JOIN         |          |   424 | 13144 |   349   (1)| 00:00:01 |  
  14. |*  4 |     TABLE ACCESS FULL| DATE_DIM |    22 |   418 |   287   (0)| 00:00:01 |  
  15. |   5 |     TABLE ACCESS FULL| T_FACT   | 10000 |   117K|    61   (0)| 00:00:01 |  
  16. |*  6 |    TABLE ACCESS FULL | USER_DIM |   213K|  4172K| 30189   (1)| 00:00:02 |  
  17. ---------------------------------------------------------------------------------  
  18.   
  19. Outline Data  
  20. -------------  
  21.   
  22.   /*+  
  23.       BEGIN_OUTLINE_DATA  
  24.       USE_HASH_AGGREGATION(@"SEL$1")  
  25.       USE_HASH(@"SEL$1" "U"@"SEL$1")  
  26.       USE_HASH(@"SEL$1" "F"@"SEL$1")  
  27.       LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")  
  28.       FULL(@"SEL$1" "U"@"SEL$1")  
  29.       FULL(@"SEL$1" "F"@"SEL$1")  
  30.       FULL(@"SEL$1" "D"@"SEL$1")  
  31.       OUTLINE_LEAF(@"SEL$1")  
  32.       ALL_ROWS  
  33.       OPT_PARAM('star_transformation_enabled' 'true')  
  34.       DB_VERSION('12.1.0.2')  
  35.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  36.       IGNORE_OPTIM_EMBEDDED_HINTS  
  37.       END_OUTLINE_DATA  
  38.   */  

WITH_PLSQL
Syntax:WITH_PLSQL
Description: WITH_PLSQL instructs the optimizer to allow the DML statement contain PLSQL block.

SQL??
  1. HelloDBA.com> update t_obj1 o  
  2. set o.created=  
  3.       (with  
  4.        function f return date  
  5.        is  
  6.        begin  
  7.          return sysdate;  
  8.        end;  
  9.       select f from dual)  
  10. where rownum <=10;  
  11. /  
  12.       (with  
  13.        *  
  14. ERROR at line 3:  
  15. ORA-32034: unsupported use of WITH clause  
  16.   
  17. HelloDBA.com> update /*+WITH_PLSQL*/t_obj1 o  
  18. set o.created=  
  19.       (with  
  20.        function f return date  
  21.        is  
  22.        begin  
  23.          return sysdate;  
  24.        end;  
  25.       select f from dual)  
  26. where rownum <=10;  
  27. /  
  28.   
  29. 10 records updated.  

DATA_SECURITY_REWRITE_LIMIT
Syntax:DATA_SECURITY_REWRITE_LIMIT
Description: Enable XS Data Security Rewrite

NO_DATA_SECURITY_REWRITE
Syntax:NO_DATA_SECURITY_REWRITE
Description: Disable XS Data Security Rewrite

SQL??
  1. HelloDBA.com> conn demo/demo@plugdb1  
  2. HelloDBA.com> create table t_obj2 as select * from t_obj;  
  3.   
  4. Table created.  
  5. HelloDBA.com> conn sys/syspass@plugdb1 as sysdba   
  6. HelloDBA.com> grant dba, xs_session_admin to demoadm identified by demoadm;  
  7.   
  8. HelloDBA.com> connect demoadm/demoadm@plugdb1  
  9. HelloDBA.com> create role db_obj;  
  10. HelloDBA.com> grant selectinsertupdatedelete on demo.t_obj2 to db_obj;   
  11. HelloDBA.com> exec xs_principal.create_role(name => 'obj_role', enabled => true);  
  12. HelloDBA.com> grant db_obj to obj_role;  
  13. HelloDBA.com> exec  xs_principal.create_user(name => 'demoxs'schema => 'demo');  
  14. HelloDBA.com> exec  sys.xs_principal.set_password('demoxs''demoxs');  
  15. HelloDBA.com> exec  xs_principal.grant_roles('demoxs''obj_role');  
  16.   
  17. HelloDBA.com> declare  
  18. begin  
  19.   xs_security_class.create_security_class(  
  20.     name        => 'demoprivs',   
  21.     parent_list => xs$name_list('sys.dml'),  
  22.     priv_list   => xs$privilege_list(xs$privilege('view_objname')));  
  23. end;  
  24. /  
  25.   
  26. HelloDBA.com> declare    
  27.   aces xs$ace_list := xs$ace_list();    
  28. begin   
  29.   aces.extend(1);  
  30.    
  31.   aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_objname'),  
  32.                          principal_name => 'obj_role');  
  33.    
  34.   xs_acl.create_acl(name      => 'obj_acl',  
  35.                     ace_list  => aces,  
  36.                     sec_class => 'demoprivs');  
  37. end;  
  38. /  
  39.    
  40. HelloDBA.com> declare  
  41.   realms   xs$realm_constraint_list := xs$realm_constraint_list();        
  42.   cols     xs$column_constraint_list := xs$column_constraint_list();  
  43. begin    
  44.   realms.extend(1);  
  45.    
  46.   realms(1) := xs$realm_constraint_type(  
  47.     realm    => 'owner = xs_sys_context(''xs$session'',''username'')',  
  48.     acl_list => xs$name_list('obj_acl'));  
  49.    
  50.   cols.extend(1);  
  51.   cols(1) := xs$column_constraint_type(  
  52.     column_list => xs$list('OBJECT_NAME'),  
  53.     privilege   => 'view_objname');  
  54.    
  55.   xs_data_security.create_policy(  
  56.     name                   => 'obj_ds',  
  57.     realm_constraint_list  => realms,  
  58.     column_constraint_list => cols);  
  59. end;  
  60. /  
  61.    
  62. HelloDBA.com> begin  
  63.   xs_data_security.apply_object_policy(  
  64.     policy => 'obj_ds',   
  65.     schema => 'demo',  
  66.     object =>'t_obj2');  
  67. end;  
  68. /  
  69.   
  70. HelloDBA.com> set serveroutput on;  
  71. HelloDBA.com> begin  
  72.   if (xs_diag.validate_workspace()) then  
  73.     dbms_output.put_line('All configurations are correct.');  
  74.   else  
  75.     dbms_output.put_line('Some configurations are incorrect.');  
  76.   end if;  
  77. end;  
  78. /  
  79.   
  80. HelloDBA.com> select * from xs$validation_table order by 1, 2, 3, 4;  
  81.    
  82. HelloDBA.com> conn demo/demo@plugdb1  
  83.   
  84. HelloDBA.com> exec sql_explain('select /*+DATA_SECURITY_REWRITE_LIMIT*/* from t_obj2 t');  
  85. Plan hash value: 3489627480  
  86.   
  87. -----------------------------------------------------------------------------  
  88. | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  89. -----------------------------------------------------------------------------  
  90. |   0 | SELECT STATEMENT   |        |     1 |   115 |     0   (0)|          |  
  91. |*  1 |  FILTER            |        |       |       |            |          |  
  92. |   2 |   FAST DUAL        |        |     1 |       |     2   (0)| 00:00:01 |  
  93. |   3 |   FAST DUAL        |        |     1 |       |     2   (0)| 00:00:01 |  
  94. |*  4 |  FILTER            |        |       |       |            |          |  
  95. |   5 |   TABLE ACCESS FULL| T_OBJ2 | 92299 |    10M|   431   (1)| 00:00:01 |  
  96. -----------------------------------------------------------------------------  
  97.   
  98. Query Block Name / Object Alias (identified by operation id):  
  99. -------------------------------------------------------------  
  100.   
  101.    1 - SEL$5  
  102.    2 - SEL$5        / DUAL@SEL$5  
  103.    3 - SEL$6        / DUAL@SEL$6  
  104.    4 - SEL$88122447  
  105.    5 - SEL$88122447 / T_OBJ2@SEL$4  
  106.   
  107. Outline Data  
  108. -------------  
  109.   
  110.   /*+  
  111.       BEGIN_OUTLINE_DATA  
  112.       FULL(@"SEL$88122447" "T_OBJ2"@"SEL$4")  
  113.       OUTLINE(@"SEL$4")  
  114.       OUTLINE(@"SEL$3")  
  115.       MERGE(@"SEL$4")  
  116.       OUTLINE(@"SEL$07BDC5B4")  
  117.       OUTLINE(@"SEL$2")  
  118.       MERGE(@"SEL$07BDC5B4")  
  119.       OUTLINE(@"SEL$641071AC")  
  120.       OUTLINE(@"SEL$1")  
  121.       MERGE(@"SEL$641071AC")  
  122.       OUTLINE_LEAF(@"SEL$88122447")  
  123.       OUTLINE_LEAF(@"SEL$6")  
  124.       OUTLINE_LEAF(@"SEL$5")  
  125.       ALL_ROWS  
  126.       OPT_PARAM('star_transformation_enabled' 'true')  
  127.       DB_VERSION('12.1.0.2')  
  128.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  129.       IGNORE_OPTIM_EMBEDDED_HINTS  
  130.       END_OUTLINE_DATA  
  131.   */  
  132.   
  133. Predicate Information (identified by operation id):  
  134. ---------------------------------------------------  
  135.   
  136.    1 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1 AND  
  137.               SYS_FILTER_ACLS(HEXTORAW('0000000080002789'),2147493768) IS NOT NULL)  
  138.    4 - filter(NULL IS NOT NULL)  
  139.   
  140. HelloDBA.com> exec sql_explain('select /*+NO_DATA_SECURITY_REWRITE*/* from t_obj2 t');  
  141. Plan hash value: 3789235527  
  142.   
  143. -----------------------------------------------------------------------------  
  144. | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |  
  145. -----------------------------------------------------------------------------  
  146. |   0 | SELECT STATEMENT   |        |   923 |   103K|   538   (1)| 00:00:01 |  
  147. |*  1 |  FILTER            |        |       |       |            |          |  
  148. |   2 |   FAST DUAL        |        |     1 |       |     2   (0)| 00:00:01 |  
  149. |   3 |   FAST DUAL        |        |     1 |       |     2   (0)| 00:00:01 |  
  150. |*  4 |  FILTER            |        |       |       |            |          |  
  151. |   5 |   TABLE ACCESS FULL| T_OBJ2 | 92299 |    10M|   432   (1)| 00:00:01 |  
  152. |*  6 |   FILTER           |        |       |       |            |          |  
  153. |   7 |    FAST DUAL       |        |     1 |       |     2   (0)| 00:00:01 |  
  154. -----------------------------------------------------------------------------  
  155.   
  156. Query Block Name / Object Alias (identified by operation id):  
  157. -------------------------------------------------------------  
  158.   
  159.    1 - SEL$5  
  160.    2 - SEL$5        / DUAL@SEL$5  
  161.    3 - SEL$6        / DUAL@SEL$6  
  162.    4 - SEL$88122447  
  163.    5 - SEL$88122447 / T_OBJ2@SEL$4  
  164.    6 - SEL$7  
  165.    7 - SEL$7        / DUAL@SEL$7  
  166.   
  167. Outline Data  
  168. -------------  
  169.   
  170.   /*+  
  171.       BEGIN_OUTLINE_DATA  
  172.       PQ_FILTER(@"SEL$88122447" SERIAL)  
  173.       FULL(@"SEL$88122447" "T_OBJ2"@"SEL$4")  
  174.       OUTLINE(@"SEL$4")  
  175.       OUTLINE(@"SEL$3")  
  176.       MERGE(@"SEL$4")  
  177.       OUTLINE(@"SEL$07BDC5B4")  
  178.       OUTLINE(@"SEL$2")  
  179.       MERGE(@"SEL$07BDC5B4")  
  180.       OUTLINE(@"SEL$641071AC")  
  181.       OUTLINE(@"SEL$1")  
  182.       MERGE(@"SEL$641071AC")  
  183.       OUTLINE_LEAF(@"SEL$88122447")  
  184.       OUTLINE_LEAF(@"SEL$7")  
  185.       OUTLINE_LEAF(@"SEL$6")  
  186.       OUTLINE_LEAF(@"SEL$5")  
  187.       ALL_ROWS  
  188.       OPT_PARAM('star_transformation_enabled' 'true')  
  189.       DB_VERSION('12.1.0.2')  
  190.       OPTIMIZER_FEATURES_ENABLE('12.1.0.2')  
  191.       IGNORE_OPTIM_EMBEDDED_HINTS  
  192.       END_OUTLINE_DATA  
  193.   */  
  194.   
  195. Predicate Information (identified by operation id):  
  196. ---------------------------------------------------  
  197.   
  198.    1 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1 AND  
  199.               SYS_FILTER_ACLS(HEXTORAW('0000000080002789'),2147493768) IS NOT NULL)  
  200.    4 - filter(SYS_CHECK_PRIVILEGE(HEXTORAW('0000000080000001'),NULL,NULL  
  201.               ,2147493769, (SELECT 1 FROM "SYS"."DUAL" "DUAL" WHERE  
  202.               XS_SYS_CONTEXT('xs$session','username')=:B1))=1)  
  203.    6 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1)  

USE_HIDDEN_PARTITIONS
Syntax:USE_HIDDEN_PARTITIONS
Description: USE_HIDDEN_PARTITIONS enables using hidden partitions.

BITMAP_AND
Syntax:BITMAP_AND
Description: Bitmap tree access path

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat