Oracle 12c 新SQL提示（hint）

原创

小麦苗123 2021-04-16 21:53:42 ©著作权

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

Oracle 12c 新SQL提示（hint）

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

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

SQL??

HelloDBA.com> create table t_obj as select * from dba_objects;
HelloDBA.com> create table t_tab as select * from dba_tables;
HelloDBA.com> exec sql_explain('delete /*+enable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');
Plan hash value: 409221376
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 3160 | 175K| 44 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 3160 | 175K| 44 (0)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 3 | DELETE | T_OBJ | | | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 3160 | 175K| 44 (0)| 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND HASH (BLOCK ADDRESS) | :TQ10002 | 3160 | 175K| 44 (0)| 00:00:01 | Q1,02 | P->P | HASH (BLOCK|
|* 6 | HASH JOIN RIGHT SEMI BUFFERED| | 3160 | 175K| 44 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,02 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | S->P | HASH |
| 9 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
| 10 | TABLE ACCESS FULL | T_TAB | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | SCWP | |
| 11 | PX RECEIVE | | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 13 | PX BLOCK ITERATOR | | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 15 (0)| 00:00:01 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------

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

SQL??

HelloDBA.com> alter session enable parallel dml;
HelloDBA.com> exec sql_explain('delete /*+disable_parallel_dml parallel(o)*/from t_obj o where exists (select 1 from t_tab t where t.owner=o.owner and t.table_name = o.object_name)','TYPICAL');
Plan hash value: 1357806520
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 3160 | 175K| 460 (1)| 00:00:01 | | | |
| 1 | DELETE | T_OBJ | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 3160 | 175K| 460 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 4 | HASH JOIN RIGHT SEMI | | 3160 | 175K| 460 (1)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | S->P | BROADCAST |
| 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
| 8 | TABLE ACCESS FULL| T_TAB | 2426 | 63076 | 29 (0)| 00:00:01 | Q1,00 | SCWP | |
| 9 | PX BLOCK ITERATOR | | 92299 | 2794K| 430 (0)| 00:00:01 | Q1,01 | PCWC | |
| 10 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 430 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------

USE_CUBE
Sytntax: USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... )
Description:When the right-hand side of the join is a cube, the USE_CUBE hint instructs the optimizer to join each specified table with another row source using a cube join. If the optimizer decides not to use the cube join based on statistical analysis, then you can use USE_CUBE to override that decision.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

HelloDBA.com> create view cube_view as select * from table(cube_table('GLOBAL.UNITS_CUBE'));
HelloDBA.com> create view time_dim_view as select * from global.time_dim;
HelloDBA.com> create view channel_dim_view as select * from global.channel_dim;
HelloDBA.com> create view customer_dim_view as select * from global.customer_dim;
HelloDBA.com> create view product_dim_view as select * from global.product_dim;
HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
HelloDBA.com> exec sql_explain('select /*+use_cube(t cn cm cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');
Plan hash value: 1674841133
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 316K| 401M| 19 (48)| 00:00:01 |
|* 1 | HASH JOIN | | 316K| 401M| 19 (48)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TIME_DIM | 120 | 28680 | 3 (0)| 00:00:01 |
|* 3 | CUBE JOIN | | 585K| 609M| 14 (50)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 183 | 71919 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CHANNEL_DIM | 3 | 237 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 61 | 19154 | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CUSTOMER_DIM | 61 | 19154 | 1 (0)| 00:00:01 |
| 8 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 896M| 5 (80)| 00:00:01 |
------------------------------------------------------------------------------------------

NO_USE_CUBE
Syntax:NO_USE_CUBE ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The NO_USE_CUBE hint instructs the optimizer to exclude cube joins when joining each specified table to another row source using the specified table as the inner table.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=true;
HelloDBA.com> exec sql_explain('select /*+no_use_cube(cb)*/* from cube_view cb, time_dim_view t, channel_dim_view cn, customer_dim_view cm where cb.time=t.month_id and cb.channel=cn.channel_id and cb.customer=cm.account_id','typical');
Plan hash value: 2364382396
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 316K| 401M| 21 (48)| 00:00:01 |
|* 1 | HASH JOIN | | 316K| 401M| 21 (48)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TIME_DIM | 120 | 28680 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 585K| 609M| 16 (50)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 183 | 71919 | 6 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CHANNEL_DIM | 3 | 237 | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 61 | 19154 | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | CUSTOMER_DIM | 61 | 19154 | 1 (0)| 00:00:01 |
| 8 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 896M| 7 (72)| 00:00:01 |
------------------------------------------------------------------------------------------

CUBE_AJ
Syntax:CUBE_AJ ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CUBE_AJ hint instructs the optimizer to clude cube anti joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
HelloDBA.com> exec sql_explain('select * from product_dim_view pd where not exists (select /*+cube_aj*/1 from cube_view cb where cb.product=pd.item_id)','typical');
Plan hash value: 2520340146
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 8208 | 11 (64)| 00:00:01 |
|* 1 | CUBE JOIN ANTI | | 36 | 8208 | 11 (64)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PRODUCT_DIM | 36 | 7488 | 2 (0)| 00:00:01 |
| 3 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 25M| 5 (60)| 00:00:01 |
----------------------------------------------------------------------------------------

CUBE_SJ
Syntax:CUBE_SJ ( [ @ queryblock ] tablespec [ tablespec ]... )
Description: The CUBE_SJ hint instructs the optimizer to clude cube semi joins when joining each specified table to another row source.
Note: Sample schema comes from oracle 11.2 BI sample codes.

SQL??

HelloDBA.com> alter session set "_optimizer_cube_join_enabled"=false;
HelloDBA.com> exec sql_explain('select * from product_dim_view pd where exists (select /*+cube_sj*/1 from cube_view cb where cb.product=pd.item_id)','typical');
Plan hash value: 2914263671
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 8604 | 13 (62)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 36 | 8604 | 13 (62)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PRODUCT_DIM | 36 | 7884 | 3 (0)| 00:00:01 |
| 3 | CUBE SCAN PARTIAL OUTER| UNITS_CUBE | 1342K| 25M| 7 (72)| 00:00:01 |
----------------------------------------------------------------------------------------

AUTO_REOPTIMIZE
Syntax:AUTO_REOPTIMIZE
Description:The AUTO_REOPTIMIZE hint instructs the optimizer to automatically change a plan on subsequent executions of a SQL statement.
Note: The "automatic reoptimization" is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement. Automatic reoptimization can fix any suboptimal plan chosen due to incorrect optimizer estimates, from a suboptimal distribution method to an incorrect choice of degree of parallelism.

SQL??

HelloDBA.com> create table t_user as select * from dba_users;
HelloDBA.com> create table t_ind as select * from dba_indexes;
HelloDBA.com> create index t_tab_idx1 on t_tab(owner, table_name);
HelloDBA.com> create table t_obj1 as select * from t_obj;
HelloDBA.com> create index t_obj1_idx1 on t_obj1(owner);
HelloDBA.com> create index t_obj1_idx2 on t_obj1(owner, object_name);
HelloDBA.com> exec dbms_stats.set_table_stats(user,'T_OBJ1', numrows=>1);
HelloDBA.com> alter session set "_optimizer_use_feedback"=false;
HelloDBA.com> alter system flush shared_pool;
-- First running
HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);
SQL_ID 5a4p65yb97cs6, child number 0
-------------------------------------
select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where
o.owner=t.owner and o.object_name=t.table_name and exists (select 1
from t_ind i, t_user u where o.owner=i.owner and o.object_name =
i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')
Plan hash value: 2082622820
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 484 (100)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_OBJ1 | 1 | 115 | 430 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_TAB | 1 | 259 | 2 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 47 | 52 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T_IND | 1 | 30 | 49 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | T_USER | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
-- Second running
HelloDBA.com> exec sql_explain('select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where o.owner=t.owner and o.object_name=t.table_name and exists (select 1 from t_ind i, t_user u where o.owner=i.owner and o.object_name = i.index_name and o.owner=u.username and u.default_tablespace=''SYSAUX'')', 'typical', false);
SQL_ID 5a4p65yb97cs6, child number 1
-------------------------------------
select /*+AUTO_REOPTIMIZE*/* from t_tab t, t_obj1 o where
o.owner=t.owner and o.object_name=t.table_name and exists (select 1
from t_ind i, t_user u where o.owner=i.owner and o.object_name =
i.index_name and o.owner=u.username and u.default_tablespace='SYSAUX')
Plan hash value: 1348485435
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 514 (100)| |
| 1 | VIEW | VM_NWVW_2 | 300 | 362K| 514 (1)| 00:00:01 |
| 2 | HASH UNIQUE | | 300 | 123K| 514 (1)| 00:00:01 |
|* 3 | HASH JOIN | | 300 | 123K| 513 (1)| 00:00:01 |
|* 4 | HASH JOIN RIGHT SEMI | | 300 | 48600 | 483 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T_USER | 10 | 170 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT SEMI| | 308 | 44660 | 480 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_IND | 4713 | 138K| 49 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T_OBJ1 | 92299 | 10M| 431 (1)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 29 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")
4 - access("O"."OWNER"="U"."USERNAME")
5 - filter("U"."DEFAULT_TABLESPACE"='SYSAUX')
6 - access("O"."OWNER"="I"."OWNER" AND "O"."OBJECT_NAME"="I"."INDEX_NAME")
Note
-----
- 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??

HelloDBA.com> alter session set "_optimizer_use_feedback"=true;
HelloDBA.com> alter system flush shared_pool;
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);
SQL_ID 0g6swgrm8tt0s, child number 0
-------------------------------------
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')
Plan hash value: 2082622820
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 484 (100)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_OBJ1 | 1 | 115 | 430 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_TAB | 1 | 259 | 2 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 47 | 52 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T_IND | 1 | 30 | 49 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | T_USER | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")
8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))
10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))
Note
-----
- this is an adaptive plan
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);
SQL_ID 0g6swgrm8tt0s, child number 0
-------------------------------------
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')
Plan hash value: 2082622820
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 484 (100)| |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 374 | 432 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_OBJ1 | 1 | 115 | 430 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_TAB_IDX1 | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_TAB | 1 | 259 | 2 (0)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 47 | 52 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T_IND | 1 | 30 | 49 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | T_USER | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
5 - access("O"."OWNER"="T"."OWNER" AND "O"."OBJECT_NAME"="T"."TABLE_NAME")
8 - filter(("I"."INDEX_NAME"=:B1 AND "I"."OWNER"=:B2))
10 - filter(("U"."USERNAME"=:B1 AND "U"."DEFAULT_TABLESPACE"='SYSAUX'))
Note
-----
- 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??

HelloDBA.com> alter session set "_optimizer_adaptive_plans"=false;
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');
Plan hash value: 3428189515
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 337 | 468 (1)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 337 | 468 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 29 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 15577 | 1186K| 438 (1)| 00:00:01 |
|* 4 | HASH JOIN | | 15577 | 2296K| 438 (1)| 00:00:01 |
| 5 | VIEW | VW_GBD_4 | 26 | 3432 | 433 (1)| 00:00:01 |
| 6 | HASH GROUP BY | | 26 | 806 | 433 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 431 (1)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN| T_TAB_IDX1 | 2426 | 46094 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TABLE_NAME"="MAX(OBJECT_NAME)" AND "ITEM_1"=ROWID)
4 - access("ITEM_1"="T"."OWNER")
Note
-----
- 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??

HelloDBA.com> alter session set "_optimizer_adaptive_plans"=true;
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');
Plan hash value: 3428189515
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 337 | 468 (1)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 337 | 468 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 29 (0)| 00:00:01 |
| 3 | VIEW | VW_SQ_1 | 15577 | 1186K| 438 (1)| 00:00:01 |
|* 4 | HASH JOIN | | 15577 | 2296K| 438 (1)| 00:00:01 |
| 5 | VIEW | VW_GBD_4 | 26 | 3432 | 433 (1)| 00:00:01 |
| 6 | HASH GROUP BY | | 26 | 806 | 433 (1)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T_OBJ | 92299 | 2794K| 431 (1)| 00:00:01 |
| 8 | INDEX FAST FULL SCAN| T_TAB_IDX1 | 2426 | 46094 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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??

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

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??

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

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??

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

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??

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

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??

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

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??

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

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

SQL??

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');
Plan hash value: 973442988
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92299 | 9734K| 461 (1)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 92299 | 9734K| 461 (1)| 00:00:01 |
| 2 | VIEW | VW_DCL_922C9664 | 2420 | 177K| 30 (4)| 00:00:01 |
| 3 | HASH GROUP BY | | 2420 | 65340 | 30 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T_TAB | 2426 | 65502 | 29 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T_OBJ1 | 92299 | 2974K| 431 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$ACECEEBF")
FULL(@"SEL$ACECEEBF" "T"@"IV")
SWAP_JOIN_INPUTS(@"SEL$C872D39E" "V"@"SEL$1")
USE_HASH(@"SEL$C872D39E" "V"@"SEL$1")
LEADING(@"SEL$C872D39E" "O"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$C872D39E" "V"@"SEL$1")
FULL(@"SEL$C872D39E" "O"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
DECORRELATE(@"IV")
OUTLINE(@"SEL$ACECEEBF")
MERGE(@"SEL$1")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"IV")
DECORRELATE(@"SEL$ACECEEBF")
OUTLINE_LEAF(@"SEL$C872D39E")
DECORRELATE(@"IV")
OUTLINE_LEAF(@"SEL$ACECEEBF")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

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');
Plan hash value: 882957228
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92299 | 3785K| 2679K (1)| 00:01:45 |
| 1 | NESTED LOOPS | | 92299 | 3785K| 2679K (1)| 00:01:45 |
| 2 | TABLE ACCESS FULL | T_OBJ1 | 92299 | 2974K| 431 (1)| 00:00:01 |
| 3 | VIEW | VW_LAT_922C9664 | 1 | 9 | 29 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 27 | | |
|* 5 | TABLE ACCESS FULL| T_TAB | 1 | 27 | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"IV" "T"@"IV")
USE_NL(@"SEL$58A6D7F6" "V"@"SEL$1")
LEADING(@"SEL$58A6D7F6" "O"@"SEL$1" "V"@"SEL$1")
NO_ACCESS(@"SEL$58A6D7F6" "V"@"SEL$1")
FULL(@"SEL$58A6D7F6" "O"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"IV")
MERGE(@"SEL$1")
OUTLINE_LEAF(@"SEL$58A6D7F6")
OUTLINE_LEAF(@"IV")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

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

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??

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

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

SQL??

HelloDBA.com> alter session set "_optimizer_partial_join_eval"=false;
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');
Plan hash value: 1185907781
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1017 | 66105 | 461 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 1017 | 66105 | 461 (1)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1017 | 66105 | 460 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_TAB | 2426 | 82484 | 29 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T_OBJ | 92299 | 2794K| 431 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PARTIAL_JOIN(@"SEL$1" "O"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_optimizer_partial_join_eval' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_optimizer_partial_join_eval"=true;
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');
Plan hash value: 887456466
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2426 | 153K| 461 (1)| 00:00:01 |
| 1 | HASH GROUP BY | | 2426 | 153K| 461 (1)| 00:00:01 |
|* 2 | HASH JOIN | | 2426 | 153K| 460 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_TAB | 2426 | 82484 | 29 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T_OBJ | 92299 | 2794K| 431 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_partial_rollup_pushdown"=off;
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');
Plan hash value: 2217003864
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1406 | 61 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 37 | 1406 | 61 (2)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | SORT GROUP BY ROLLUP COLLECTOR | | 37 | 1406 | 61 (2)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 37 | 1406 | 61 (2)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 37 | 1406 | 61 (2)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | SORT GROUP BY ROLLUP DISTRIBUTOR| | 37 | 1406 | 61 (2)| 00:00:01 | Q1,00 | PCWP | |
| 7 | HASH GROUP BY | | 37 | 1406 | 61 (2)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL | T_OBJ | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
FULL(@"SEL$1" "T_OBJ"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_px_partial_rollup_pushdown' 'off')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_partial_rollup_pushdown"=adaptive;
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');
Plan hash value: 3061208371
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 1406 | 61 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 37 | 1406 | 61 (2)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | SORT GROUP BY ROLLUP | | 37 | 1406 | 61 (2)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T_OBJ | 92299 | 3425K| 60 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T_OBJ"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_concurrent"=false;
HelloDBA.com> exec sql_explain('select /*+PQ_CONCURRENT_UNION*/ * from (select * from t_obj union all select * from t_obj1)', 'TYPICAL OUTLINE');
Plan hash value: 1664138491
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184K| 64M| 863 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 184K| 64M| 863 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | VIEW | | 184K| 64M| 863 (1)| 00:00:01 | Q1,00 | PCWP | |
| 4 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 5 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
| 6 | TABLE ACCESS FULL| T_OBJ | 92299 | 10M| 431 (1)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
| 8 | TABLE ACCESS FULL| T_OBJ1 | 92299 | 10M| 431 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "T_OBJ"@"SEL$2")
FULL(@"SEL$3" "T_OBJ1"@"SEL$3")
PQ_CONCURRENT_UNION(@"SET$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_px_concurrent' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_concurrent"=true;
HelloDBA.com> exec sql_explain('select /*+parallel NO_PQ_CONCURRENT_UNION*/ * from (select * from t_obj union all select * from t_obj1)', 'TYPICAL OUTLINE');
Plan hash value: 496087642
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184K| 64M| 479 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 184K| 64M| 479 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | VIEW | | 184K| 64M| 479 (1)| 00:00:01 | Q1,00 | PCWP | |
| 4 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_OBJ | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T_OBJ1 | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "T_OBJ"@"SEL$2")
FULL(@"SEL$3" "T_OBJ1"@"SEL$3")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$2")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_filter_parallelized"=false;
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');
Plan hash value: 2440581449
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110 | 12650 | 22159 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 92299 | 10M| 240 (1)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 110 | 12650 | | | Q1,01 | PCWP | |
|* 4 | FILTER | | | | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 92299 | 10M| 240 (1)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T_OBJ1 | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWP | |
|* 9 | TABLE ACCESS FULL | T_TAB | 2 | 30 | 26 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "T"@"SEL$2")
PQ_FILTER(@"SEL$1" HASH)
FULL(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_px_filter_parallelized' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_TAB" "T" WHERE "LAST_ANALYZED"=:B1 AND
"TABLESPACE_NAME" LIKE :A))
9 - filter("LAST_ANALYZED"=:B1 AND "TABLESPACE_NAME" LIKE :A)
HelloDBA.com> alter session set "_px_filter_parallelized"=true;
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');
Plan hash value: 3734482086
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 110 | 12650 | 12409 (1)| 00:00:01 | | | |
|* 1 | FILTER | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ20000 | 92299 | 10M| 240 (1)| 00:00:01 | Q2,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 92299 | 10M| 240 (1)| 00:00:01 | Q2,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_OBJ1 | 92299 | 10M| 240 (1)| 00:00:01 | Q2,00 | PCWP | |
| 6 | PX COORDINATOR | | | | | | | | |
| 7 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 30 | 14 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 8 | PX BLOCK ITERATOR | | 2 | 30 | 14 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| T_TAB | 2 | 30 | 14 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$2" "T"@"SEL$2")
PQ_FILTER(@"SEL$1" SERIAL)
FULL(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

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');
Plan hash value: 4185789934
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2426 | 46094 | 4 (25)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | WINDOW SORT | | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | INDEX FAST FULL SCAN| T_TAB_IDX1 | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE_WINDOW(@"SEL$1" 1)
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))
OUTLINE_LEAF(@"SEL$1")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
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');
Plan hash value: 1125815052
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2426 | 46094 | 4 (25)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | WINDOW CONSOLIDATOR BUFFER| | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | WINDOW SORT | | 2426 | 46094 | 4 (25)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | INDEX FAST FULL SCAN | T_TAB_IDX1 | 2426 | 46094 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE_WINDOW(@"SEL$1" 2)
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_TAB"."OWNER" "T_TAB"."TABLE_NAME"))
OUTLINE_LEAF(@"SEL$1")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

HelloDBA.com> alter session set "_px_replication_enabled"=false;
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');
Plan hash value: 2038113948
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2764K| 986M| 259 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 2764K| 986M| 259 (2)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 2764K| 986M| 259 (2)| 00:00:01 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 2426 | 613K| 16 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| T_TAB | 2426 | 613K| 16 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 6 | TABLE ACCESS FULL | T_OBJ | 4615 | 518K| 239 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" NONE BROADCAST)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_px_replication_enabled' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_replication_enabled"=true;
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');
Plan hash value: 2305795279
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2764K| 986M| 259 (2)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 2764K| 986M| 259 (2)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 2764K| 986M| 259 (2)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 2426 | 613K| 16 (0)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 16 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 4615 | 518K| 239 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 4615 | 518K| 239 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 4615 | 518K| 239 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| T_OBJ | 4615 | 518K| 239 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" NONE BROADCAST)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
SHARED(2)
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_px_join_skew_handling"=false;
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');
Plan hash value: 1404870264
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 19G| 83 (23)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 55M| 19G| 83 (23)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 55M| 19G| 83 (23)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 2426 | 613K| 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HYBRID HASH | :TQ10000 | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | P->P | HYBRID HASH|
| 6 | STATISTICS COLLECTOR | | | | | | Q1,00 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | 92299 | 10M| 60 (0)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX SEND HYBRID HASH (SKEW)| :TQ10001 | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | P->P | HYBRID HASH|
| 11 | PX BLOCK ITERATOR | | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | T_OBJ | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_SKEW(@"SEL$1" "O"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" HASH HASH)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_px_join_skew_handling' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Note
-----
- 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??

HelloDBA.com> alter session set "_px_join_skew_handling"=true;
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');
Plan hash value: 772990711
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 19G| 83 (23)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 55M| 19G| 83 (23)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 55M| 19G| 83 (23)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 2426 | 613K| 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HYBRID HASH | :TQ10000 | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | P->P | HYBRID HASH|
| 6 | STATISTICS COLLECTOR | | | | | | Q1,00 | PCWC | |
| 7 | PX BLOCK ITERATOR | | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | 92299 | 10M| 60 (0)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX SEND HYBRID HASH | :TQ10001 | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | P->P | HYBRID HASH|
| 11 | PX BLOCK ITERATOR | | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | T_OBJ | 92299 | 10M| 60 (0)| 00:00:01 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" HASH HASH)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Note
-----
- 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??

HelloDBA.com> @showpara _parallel_fault_tolerance_enabled
Inst Parameter Parameter
Id Name Modifiable Description Value
---- ------------------------------------ ---------- --------------------------------------- ------------------------------
1 _parallel_fault_tolerance_enabled enables or disables fault-tolerance for system: FALSE;
parallel statement session: FALSE;
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');
Plan hash value: 2603285128
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 19G| 83 (23)| 00:00:01 | | | |
| 1 | FAULT-TOLERANCE BUFFER | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 55M| 19G| 83 (23)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 4 | HASH JOIN | | 55M| 19G| 83 (23)| 00:00:01 | Q1,00 | PCWP | |
| 5 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 4 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 92299 | 10M| 60 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | T_OBJ | 92299 | 10M| 60 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" BROADCAST NONE)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
PX_FAULT_TOLERANCE
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

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');
Plan hash value: 1979616763
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55M| 19G| 333 (24)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 55M| 19G| 333 (24)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 55M| 19G| 333 (24)| 00:00:01 | Q1,00 | PCWP | |
| 4 | TABLE ACCESS FULL | T_TAB | 2426 | 613K| 16 (0)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_OBJ | 92299 | 10M| 240 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_REPLICATE(@"SEL$1" "O"@"SEL$1")
PQ_DISTRIBUTE(@"SEL$1" "O"@"SEL$1" BROADCAST NONE)
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "T"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_optimizer_ansi_rearchitecture"=false;
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');
Plan hash value: 220790772
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2824K| 53M| 41 (18)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER | | 2824K| 53M| 41 (18)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T_TAB | 122 | 1708 | 29 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 92299 | 540K| 5 (0)| 00:00:01 |
| 4 | BITMAP INDEX FAST FULL SCAN| T_OBJ_IDX1 | | | | |
--------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$2BFA4EE4" "T"@"SEL$1")
USE_HASH(@"SEL$2BFA4EE4" "T"@"SEL$1")
LEADING(@"SEL$2BFA4EE4" "O"@"SEL$1" "T"@"SEL$1")
FULL(@"SEL$2BFA4EE4" "T"@"SEL$1")
INDEX_FFS(@"SEL$2BFA4EE4" "O"@"SEL$1" ("T_OBJ"."OWNER"))
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$8812AA4E")
ANSI_REARCH(@"SEL$2")
OUTLINE(@"SEL$948754D7")
MERGE(@"SEL$8812AA4E")
OUTLINE_LEAF(@"SEL$2BFA4EE4")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_optimizer_ansi_rearchitecture' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_optimizer_ansi_rearchitecture"=true;
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');
Plan hash value: 220790772
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2824K| 53M| 41 (18)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER | | 2824K| 53M| 41 (18)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | T_TAB | 122 | 1708 | 29 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | | 92299 | 540K| 5 (0)| 00:00:01 |
| 4 | BITMAP INDEX FAST FULL SCAN| T_OBJ_IDX1 | | | | |
--------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
SWAP_JOIN_INPUTS(@"SEL$9E43CB6E" "T"@"SEL$1")
USE_HASH(@"SEL$9E43CB6E" "T"@"SEL$1")
LEADING(@"SEL$9E43CB6E" "O"@"SEL$2" "T"@"SEL$1")
FULL(@"SEL$9E43CB6E" "T"@"SEL$1")
INDEX_FFS(@"SEL$9E43CB6E" "O"@"SEL$2" ("T_OBJ"."OWNER"))
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$3")
MERGE(@"SEL$58A6D7F6")
OUTLINE_LEAF(@"SEL$9E43CB6E")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_optimizer_aggr_groupby_elim"=false;
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');
Plan hash value: 1150833919
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 140 | 6 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 20 | 140 | 6 (17)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_TAB_IDX1 | 2426 | 16982 | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$D07AE25A")
INDEX_FFS(@"SEL$D07AE25A" "T_TAB"@"SEL$2" ("T_TAB"."OWNER"
"T_TAB"."TABLE_NAME"))
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
ELIM_GROUPBY(@"SEL$1")
OUTLINE(@"SEL$47952E7A")
MERGE(@"SEL$2")
OUTLINE_LEAF(@"SEL$D07AE25A")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_optimizer_aggr_groupby_elim"=true;
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');
Plan hash value: 4120618623
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 140 | 30 (4)| 00:00:01 |
| 1 | HASH GROUP BY | | 20 | 140 | 30 (4)| 00:00:01 |
| 2 | VIEW | | 20 | 140 | 30 (4)| 00:00:01 |
| 3 | HASH GROUP BY | | 20 | 200 | 30 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T_TAB | 2426 | 24260 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$2")
FULL(@"SEL$2" "T_TAB"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

HelloDBA.com> alter table t_obj1 inmemory;
HelloDBA.com> alter session set "inmemory_query"=disable;
HelloDBA.com> exec sql_explain('select /*+INMEMORY(o)*/* from t_obj1 o', 'TYPICAL OUTLINE');
Plan hash value: 1936150373
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92299 | 10M| 21 (24)| 00:00:01 |
| 1 | TABLE ACCESS INMEMORY FULL| T_OBJ1 | 92299 | 10M| 21 (24)| 00:00:01 |
-------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

HelloDBA.com> alter session set "inmemory_query"=enable;
HelloDBA.com> exec sql_explain('select /*+NO_INMEMORY(o)*/* from t_obj1 o', 'TYPICAL OUTLINE');
Plan hash value: 1936150373
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 92299 | 10M| 431 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T_OBJ1 | 92299 | 10M| 431 (1)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "O"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

HelloDBA.com> alter table t_obj1 inmemory priority critical;
HelloDBA.com> select * from t_obj1;
HelloDBA.com> alter session set "_inmemory_pruning"=off;
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#;
NAME VALUE CAP_TIME
---------------------------------------------------------------- ---------- -------------------
IM scan CUs pruned 0 2014-10-03 15:06:19
HelloDBA.com> select /*+INMEMORY_PRUNING(o)*/owner from t_obj1 o where object_id = 1;
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#;
NAME VALUE CAP_TIME
---------------------------------------------------------------- ---------- -------------------
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??

HelloDBA.com> alter session set "_inmemory_pruning"=on;
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#;
NAME VALUE CAP_TIME
---------------------------------------------------------------- ---------- -------------------
IM scan CUs pruned 1 2014-10-03 15:06:37
HelloDBA.com> select /*+NO_INMEMORY_PRUNING(o)*/owner from t_obj1 o where object_id = 1;
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#;
NAME VALUE CAP_TIME
---------------------------------------------------------------- ---------- -------------------
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??

HelloDBA.com> alter session set "_vector_aggregation_max_size"=8192;
HelloDBA.com> exec dbms_stats.gather_table_stats(user,'T_FACT', method_opt => 'for all columns size 1');
HelloDBA.com> exec dbms_stats.gather_table_stats(user,'CATEGORY_DIM', method_opt => 'for all columns size 1');
HelloDBA.com> exec dbms_stats.gather_table_stats(user,'DATE_DIM', method_opt => 'for all columns size 1');
HelloDBA.com> exec dbms_stats.gather_table_stats(user,'USER_DIM', method_opt => 'for all columns size 1');
HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=false;
HelloDBA.com> alter session set "_optimizer_vector_transformation"=false;
HelloDBA.com> alter session set "_always_vector_transformation"=false;
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');
Plan hash value: 100339589
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6944 | 637K| | 2471 (2)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6672_C96885 | | | | | |
| 3 | HASH GROUP BY | | 1 | 23 | | 13 (16)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | 22 | 506 | | 13 (16)| 00:00:01 |
|* 5 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 22 | 418 | | 11 (0)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6673_C96885 | | | | | |
| 7 | HASH GROUP BY | | 9819 | 230K| 6712K| 2440 (2)| 00:00:01 |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | 213K| 5006K| | 2440 (2)| 00:00:01 |
|* 9 | TABLE ACCESS INMEMORY FULL | USER_DIM | 213K| 4172K| | 1138 (3)| 00:00:01 |
| 10 | HASH GROUP BY | | 6944 | 637K| | 17 (12)| 00:00:01 |
|* 11 | HASH JOIN | | 6944 | 637K| | 16 (7)| 00:00:01 |
| 12 | VIEW | VW_VT_AF278325 | 6944 | 318K| | 4 (25)| 00:00:01 |
| 13 | HASH GROUP BY | | 6944 | 135K| | 4 (25)| 00:00:01 |
| 14 | KEY VECTOR USE | :KV0001 | 6944 | 135K| | 4 (25)| 00:00:01 |
| 15 | KEY VECTOR USE | :KV0000 | 6944 | 108K| | 4 (25)| 00:00:01 |
|* 16 | TABLE ACCESS INMEMORY FULL| T_FACT | 10000 | 117K| | 3 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 9819 | 450K| | 12 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6672_C96885 | 1 | 23 | | 2 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 9819 | 230K| | 10 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6673_C96885 | 9819 | 230K| | 10 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$0333970C")
FULL(@"SEL$0333970C" "D"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$D0E111EF")
FULL(@"SEL$D0E111EF" "U"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$709E21E9")
FULL(@"SEL$709E21E9" "F"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$A6BF8214")
SWAP_JOIN_INPUTS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
USE_HASH(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
USE_MERGE_CARTESIAN(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")
LEADING(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9" "TT_VT_5"@"SEL$0CAF0D26" "VW_VT_AF278325"@"SEL$AF278325")
NO_ACCESS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
FULL(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")
FULL(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$AF278325")
OUTLINE(@"SEL$6E7BB6AB")
OUTLINE(@"SEL$3ACFCBE9")
VECTOR_TRANSFORM(@"SEL$1" FACT("F"@"SEL$1") DIMENSION("D"@"SEL$1") DIMENSION("U"@"SEL$1"))
OUTLINE_LEAF(@"SEL$A6BF8214")
OUTLINE_LEAF(@"SEL$709E21E9")
OUTLINE_LEAF(@"SEL$D0E111EF")
OUTLINE_LEAF(@"SEL$0333970C")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_gby_vector_aggregation_enabled' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;
HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;
HelloDBA.com> alter session set "_always_vector_transformation"=true;
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');
Plan hash value: 397045964
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 1153 (3)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 51 | 1153 (3)| 00:00:01 |
|* 2 | HASH JOIN | | 425 | 21675 | 1152 (3)| 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 424 | 13144 | 14 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 424 | 13144 | 14 (0)| 00:00:01 |
| 5 | JOIN FILTER CREATE | :BF0001 | 22 | 418 | 11 (0)| 00:00:01 |
|* 6 | TABLE ACCESS INMEMORY FULL| DATE_DIM | 22 | 418 | 11 (0)| 00:00:01 |
| 7 | JOIN FILTER USE | :BF0001 | 10000 | 117K| 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS INMEMORY FULL| T_FACT | 10000 | 117K| 3 (0)| 00:00:01 |
| 9 | JOIN FILTER USE | :BF0000 | 213K| 4172K| 1138 (3)| 00:00:01 |
|* 10 | TABLE ACCESS INMEMORY FULL | USER_DIM | 213K| 4172K| 1138 (3)| 00:00:01 |
--------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
PX_JOIN_FILTER(@"SEL$1" "U"@"SEL$1")
PX_JOIN_FILTER(@"SEL$1" "F"@"SEL$1")
USE_HASH(@"SEL$1" "U"@"SEL$1")
USE_HASH(@"SEL$1" "F"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "F"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=false;
HelloDBA.com> alter session set "_optimizer_vector_transformation"=false;
HelloDBA.com> alter session set "_always_vector_transformation"=false;
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');
Plan hash value: 100339589
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6944 | 637K| | 31857 (1)| 00:00:02 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6684_C96885 | | | | | |
| 3 | HASH GROUP BY | | 1 | 23 | | 289 (1)| 00:00:01 |
| 4 | KEY VECTOR CREATE BUFFERED| :KV0000 | 22 | 506 | | 289 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DATE_DIM | 22 | 418 | | 287 (0)| 00:00:01 |
| 6 | LOAD AS SELECT | SYS_TEMP_0FD9D6685_C96885 | | | | | |
| 7 | HASH GROUP BY | | 9819 | 230K| 6712K| 31492 (1)| 00:00:02 |
| 8 | KEY VECTOR CREATE BUFFERED| :KV0001 | 213K| 5006K| | 31492 (1)| 00:00:02 |
|* 9 | TABLE ACCESS FULL | USER_DIM | 213K| 4172K| | 30189 (1)| 00:00:02 |
| 10 | HASH GROUP BY | | 6944 | 637K| | 76 (4)| 00:00:01 |
|* 11 | HASH JOIN | | 6944 | 637K| | 74 (2)| 00:00:01 |
| 12 | VIEW | VW_VT_AF278325 | 6944 | 318K| | 62 (2)| 00:00:01 |
| 13 | HASH GROUP BY | | 6944 | 135K| | 62 (2)| 00:00:01 |
| 14 | KEY VECTOR USE | :KV0001 | 6944 | 135K| | 62 (2)| 00:00:01 |
| 15 | KEY VECTOR USE | :KV0000 | 6944 | 108K| | 62 (2)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | T_FACT | 10000 | 117K| | 61 (0)| 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 9819 | 450K| | 12 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6684_C96885 | 1 | 23 | | 2 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 9819 | 230K| | 10 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6685_C96885 | 9819 | 230K| | 10 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$0333970C")
FULL(@"SEL$0333970C" "D"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$D0E111EF")
FULL(@"SEL$D0E111EF" "U"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$709E21E9")
FULL(@"SEL$709E21E9" "F"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$A6BF8214")
SWAP_JOIN_INPUTS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
USE_HASH(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
USE_MERGE_CARTESIAN(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")
LEADING(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9" "TT_VT_5"@"SEL$0CAF0D26" "VW_VT_AF278325"@"SEL$AF278325")
NO_ACCESS(@"SEL$A6BF8214" "VW_VT_AF278325"@"SEL$AF278325")
FULL(@"SEL$A6BF8214" "TT_VT_5"@"SEL$0CAF0D26")
FULL(@"SEL$A6BF8214" "TT_VT_4"@"SEL$DED1BBE9")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$AF278325")
OUTLINE(@"SEL$6E7BB6AB")
OUTLINE(@"SEL$3ACFCBE9")
VECTOR_TRANSFORM(@"SEL$1" FACT("F"@"SEL$1") DIMENSION("D"@"SEL$1") DIMENSION("U"@"SEL$1"))
OUTLINE_LEAF(@"SEL$A6BF8214")
OUTLINE_LEAF(@"SEL$709E21E9")
OUTLINE_LEAF(@"SEL$D0E111EF")
OUTLINE_LEAF(@"SEL$0333970C")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
OPT_PARAM('_optimizer_vector_transformation' 'false')
OPT_PARAM('_gby_vector_aggregation_enabled' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;
HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;
HelloDBA.com> alter session set "_always_vector_transformation"=true;
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');
Plan hash value: 762306316
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 30540 (1)| 00:00:02 |
| 1 | HASH GROUP BY | | 1 | 51 | 30540 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 425 | 21675 | 30539 (1)| 00:00:02 |
|* 3 | HASH JOIN | | 424 | 13144 | 349 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DATE_DIM | 22 | 418 | 287 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T_FACT | 10000 | 117K| 61 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | USER_DIM | 213K| 4172K| 30189 (1)| 00:00:02 |
---------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
USE_HASH(@"SEL$1" "U"@"SEL$1")
USE_HASH(@"SEL$1" "F"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "F"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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??

HelloDBA.com> alter session set "_gby_vector_aggregation_enabled"=true;
HelloDBA.com> alter session set "_optimizer_vector_transformation"=true;
HelloDBA.com> alter session set "_always_vector_transformation"=true;
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');
Plan hash value: 762306316
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 51 | 30540 (1)| 00:00:02 |
| 1 | HASH GROUP BY | | 1 | 51 | 30540 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 425 | 21675 | 30539 (1)| 00:00:02 |
|* 3 | HASH JOIN | | 424 | 13144 | 349 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DATE_DIM | 22 | 418 | 287 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T_FACT | 10000 | 117K| 61 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | USER_DIM | 213K| 4172K| 30189 (1)| 00:00:02 |
---------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH_AGGREGATION(@"SEL$1")
USE_HASH(@"SEL$1" "U"@"SEL$1")
USE_HASH(@"SEL$1" "F"@"SEL$1")
LEADING(@"SEL$1" "D"@"SEL$1" "F"@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "U"@"SEL$1")
FULL(@"SEL$1" "F"@"SEL$1")
FULL(@"SEL$1" "D"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

SQL??

HelloDBA.com> update t_obj1 o
set o.created=
(with
function f return date
is
begin
return sysdate;
end;
select f from dual)
where rownum <=10;
/
(with
*
ERROR at line 3:
ORA-32034: unsupported use of WITH clause
HelloDBA.com> update /*+WITH_PLSQL*/t_obj1 o
set o.created=
(with
function f return date
is
begin
return sysdate;
end;
select f from dual)
where rownum <=10;
/
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??

HelloDBA.com> conn demo/demo@plugdb1
HelloDBA.com> create table t_obj2 as select * from t_obj;
Table created.
HelloDBA.com> conn sys/syspass@plugdb1 as sysdba
HelloDBA.com> grant dba, xs_session_admin to demoadm identified by demoadm;
HelloDBA.com> connect demoadm/demoadm@plugdb1
HelloDBA.com> create role db_obj;
HelloDBA.com> grant select, insert, update, delete on demo.t_obj2 to db_obj;
HelloDBA.com> exec xs_principal.create_role(name => 'obj_role', enabled => true);
HelloDBA.com> grant db_obj to obj_role;
HelloDBA.com> exec xs_principal.create_user(name => 'demoxs', schema => 'demo');
HelloDBA.com> exec sys.xs_principal.set_password('demoxs', 'demoxs');
HelloDBA.com> exec xs_principal.grant_roles('demoxs', 'obj_role');
HelloDBA.com> declare
begin
xs_security_class.create_security_class(
name => 'demoprivs',
parent_list => xs$name_list('sys.dml'),
priv_list => xs$privilege_list(xs$privilege('view_objname')));
end;
/
HelloDBA.com> declare
aces xs$ace_list := xs$ace_list();
begin
aces.extend(1);
aces(1) := xs$ace_type(privilege_list => xs$name_list('select','view_objname'),
principal_name => 'obj_role');
xs_acl.create_acl(name => 'obj_acl',
ace_list => aces,
sec_class => 'demoprivs');
end;
/
HelloDBA.com> declare
realms xs$realm_constraint_list := xs$realm_constraint_list();
cols xs$column_constraint_list := xs$column_constraint_list();
begin
realms.extend(1);
realms(1) := xs$realm_constraint_type(
realm => 'owner = xs_sys_context(''xs$session'',''username'')',
acl_list => xs$name_list('obj_acl'));
cols.extend(1);
cols(1) := xs$column_constraint_type(
column_list => xs$list('OBJECT_NAME'),
privilege => 'view_objname');
xs_data_security.create_policy(
name => 'obj_ds',
realm_constraint_list => realms,
column_constraint_list => cols);
end;
/
HelloDBA.com> begin
xs_data_security.apply_object_policy(
policy => 'obj_ds',
schema => 'demo',
object =>'t_obj2');
end;
/
HelloDBA.com> set serveroutput on;
HelloDBA.com> begin
if (xs_diag.validate_workspace()) then
dbms_output.put_line('All configurations are correct.');
else
dbms_output.put_line('Some configurations are incorrect.');
end if;
end;
/
HelloDBA.com> select * from xs$validation_table order by 1, 2, 3, 4;
HelloDBA.com> conn demo/demo@plugdb1
HelloDBA.com> exec sql_explain('select /*+DATA_SECURITY_REWRITE_LIMIT*/* from t_obj2 t');
Plan hash value: 3489627480
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL| T_OBJ2 | 92299 | 10M| 431 (1)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5
2 - SEL$5 / DUAL@SEL$5
3 - SEL$6 / DUAL@SEL$6
4 - SEL$88122447
5 - SEL$88122447 / T_OBJ2@SEL$4
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$88122447" "T_OBJ2"@"SEL$4")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
MERGE(@"SEL$4")
OUTLINE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$2")
MERGE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$641071AC")
OUTLINE(@"SEL$1")
MERGE(@"SEL$641071AC")
OUTLINE_LEAF(@"SEL$88122447")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$5")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1 AND
SYS_FILTER_ACLS(HEXTORAW('0000000080002789'),2147493768) IS NOT NULL)
4 - filter(NULL IS NOT NULL)
HelloDBA.com> exec sql_explain('select /*+NO_DATA_SECURITY_REWRITE*/* from t_obj2 t');
Plan hash value: 3789235527
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 923 | 103K| 538 (1)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL| T_OBJ2 | 92299 | 10M| 432 (1)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5
2 - SEL$5 / DUAL@SEL$5
3 - SEL$6 / DUAL@SEL$6
4 - SEL$88122447
5 - SEL$88122447 / T_OBJ2@SEL$4
6 - SEL$7
7 - SEL$7 / DUAL@SEL$7
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_FILTER(@"SEL$88122447" SERIAL)
FULL(@"SEL$88122447" "T_OBJ2"@"SEL$4")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
MERGE(@"SEL$4")
OUTLINE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$2")
MERGE(@"SEL$07BDC5B4")
OUTLINE(@"SEL$641071AC")
OUTLINE(@"SEL$1")
MERGE(@"SEL$641071AC")
OUTLINE_LEAF(@"SEL$88122447")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$5")
ALL_ROWS
OPT_PARAM('star_transformation_enabled' 'true')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1 AND
SYS_FILTER_ACLS(HEXTORAW('0000000080002789'),2147493768) IS NOT NULL)
4 - filter(SYS_CHECK_PRIVILEGE(HEXTORAW('0000000080000001'),NULL,NULL
,2147493769, (SELECT 1 FROM "SYS"."DUAL" "DUAL" WHERE
XS_SYS_CONTEXT('xs$session','username')=:B1))=1)
6 - filter(XS_SYS_CONTEXT('xs$session','username')=:B1)