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.

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.

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.

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.

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.

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

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

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');

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

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.