Oracle hidden utilities undocumented parameters

Each new release of Oracle brings new hidden utilities. They are sometimes used by internal Oracle development staff and left in Oracle for emergency support. Many of these undocumented utilities are very powerful but can be complex.

A hidden utility is not a utility that is physically hidden in the Oracle software. Rather, a hidden utility is an executable or PL/SQL package that is either undocumented or where the documentation is difficult to find. For example, some PL/SQL packages are never loaded by the Oracle installer, yet their definitions remain in the operating system files.

The main directories of interest regarding Oracle utilities are the following:

• $ORACLE_HOME/bin -- This contains the binary executables used by the Oracle server. Most of the tools discussed in this book reside here.
• $ORACLE_HOME/plsql/demo -- This contains a useful collection of SQL scripts related to many utilities, including dbms_profiler.
• $ORACLE_HOME/rdbms/admin -- This contains many SQL scripts used for creating PL/SQL packages and their required environments.
• $ORACLE_HOME/sqlplus/admin -- This contains scripts used with autotrace and other utilities.
• $ORACLE_HOME/otrace/admin -- This is the administration directory for the Oracle Trace diagnostic tool.
• $ORACLE_HOME/otrace/sysman -- This is used by utilities such as oemctl and the Oracle Management Server (OMS).
• $ORACLE_HOME/otrace/lib -- This contains facility files used with the oerr utility.

Many of these undocumented utilities such as TKPROF have surfaced from the obscure and entered mainstream Oracle toolkits while others remain hidden inside the O/S.

Finding hidden utilities

The easiest way to find hidden utilities is to look for new packages within Oracle. The following query compares the packages in Oracle8i with new packages in Oracle9i, using a database link:

select 
   object_name 
from 
   dba_objects@oracle9i 
where 
   owner      = 'SYS' 
and 
   object_type = 'PACKAGE BODY' 
minus
select 
   object_name 
from  
   dba_objects@oracle8i
where 
   owner     = 'SYS'
and 
   object_type = 'PACKAGE BODY';

This query will quickly display all package bodies owned by SYS that exist in Oracle9i but not in Oracle8i.

Finding hidden executables

Discovering new binaries entails comparing two directories in the operating system and ignoring the duplicates entries. The dircmp UNIX command can be used to find only new entries within two directories:

dircmp -- s 
/u01/aoracle/product/8.1.7/bin 
/u01/oracle/product/9.0.3/bin

Here we compare the bin directory of 8.1.7 with Oracle 9.0.2. Note that the -- s option of the dircmp command tells UNIX to eliminate the matches from the output.

Hidden initialization parameters

Hidden initialization parameters are very dangerous because their use is undocumented, but they can be very valuable if you are careful. We can use a SQL*Plus query to quickly find any new initialization parameters in a new version of Oracle:

select 
   name
from 
    v$parameter@oracle9i 
minus 
select 
   name
from 
   v$parameter@oracle8i
; 

Every version of Oracle has special undocumented initialization parameters. These undocumented initialization parameters are usually only used in emergencies and only under the direction of a senior DBA or Oracle support. Because the undocumented parameters begin with an underscore "_", we can write a query against the X$ foxed tables to easily extract them:

COLUMN parameter           FORMAT a37
COLUMN description         FORMAT a30 WORD_WRAPPED
COLUMN "Session Value"     FORMAT a10
COLUMN "Instance Value"    FORMAT a10
SET LINES 100
SET PAGES 0
SPOOL undoc.lis

SELECT  
   a.ksppinm  "Parameter",  
   a.ksppdesc "Description", 
   b.ksppstvl "Session Value",
   c.ksppstvl "Instance Value"
FROM 
   x$ksppi a, 
   x$ksppcv b, 
   x$ksppsv c
WHERE 
   a.indx = b.indx 
   AND 
   a.indx = c.indx
   AND 
   a.ksppinm LIKE '/_%' escape '/'
/

Remember, not everyone knows about the undocumented parameters, and few know how or when to use them. Oracle does not allow DBAs to use many of these parameters unless specifically directed by Oracle support. DBAs should be aware that use of certain undocumented parameters will result in an unsupported system.

In many cases, the undocumented parameters were either documented in previous releases or will be in future releases. Of course, it is difficult to use the undocumented parameters that have never been documented, and never will be, safely. When in doubt, get guidance from Oracle support. We will take a closer look at some of these parameters in our next installment.

Hidden v$ views

New V$ views indicate a new functionality within the database, and not all of the views make it into the Oracle documentation. Here is a quick query to locate new views:

select 
   view_name
from 
    v$fixed_view_description@oracle9i 
minus 
select 
   view_name
from 
   v$fixed_view_description@oracle8i
; 

Hidden system events

With each new release of Oracle, system events and system statistics are changed. For example, here is a query to show changed system statistics for the database writer process between Oracle8i and Oracle9i:

sql> select distinct name from v$sysstat where name like 'DBWR%'

NAME
----------------------------------------------------------------
DBWR Flush object call found no dirty buffers
DBWR Flush object cross instance calls
DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoint write requests
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR incr. ckpt. write requests
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buffer
DBWR skip hot writes
DBWR summed scan depth
DBWR timeouts
DBWR transaction table writes
DBWR undo block writes

Here is the listing from Oracle9i.  Note the changed statistics.

SQL> select distinct name from v$sysstat where name like 'DBWR%';

NAME
----------------------------------------------------------------
DBWR buffers scanned
DBWR checkpoint buffers written
DBWR checkpoints
DBWR cross instance writes
DBWR free buffers found
DBWR fusion writes
DBWR lru scans
DBWR make free requests
DBWR revisited being-written buffer
DBWR summed scan depth
DBWR transaction table writes
DBWR undo block writes

By the same token, we van use the v $event_name view to quickly locate all new system events in Oracle19i.  Here is a quick query to locate new views:

select 
   name
from 
    v$event_name@oracle9i 
minus 
select 
   name
from 
   v$event_name@oracle8i
; 

Undocumented SQL hints

Getting a list of Oracle hints for SQL tuning is often difficult. The Oracle hint list is inside the Oracle executable and you can extract the Oracle hint list easily with UNIX commands. In UNIX, you can use grep and strings to get them directly from the Oracle executable:

   strings $ORACLE_HOME/bin/oracle > hints.lst

Here are all of the 124 Oracle9i hints on release 9.0.2:

ALL_ROWS	AND_EQUAL	ANTIJOIN
APPEND	BITMAP	BUFFER
BYPASS_RECURSIVE_CHECK	BYPASS_UJVC	CACHE
CACHE_CB	CACHE_TEMP_TABLE	CARDINALITY
CHOOSE	CIV_GB	COLLECTIONS_GET_REFS
CPU_COSTING	CUBE_GB	CURSOR_SHARING_EXACT
DEREF_NO_REWRITE	DML_UPDATE	DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT	DRIVING_SITE	DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN	EXPAND_GSET_TO_UNION	FACT
FIRST_ROWS	FORCE_SAMPLE_BLOCK	FULL
GBY_CONC_ROLLUP	GLOBAL_TABLE_HINTS	HASH
HASH_AJ	HASH_SJ	HWM_BROKERED
IGNORE_ON_CLAUSE	IGNORE_WHERE_CLAUSE	INDEX_ASC
INDEX_COMBINE	INDEX_DESC	INDEX_FFS
INDEX_JOIN	INDEX_RRS	INDEX_SS
INDEX_SS_ASC	INDEX_SS_DESC	INLINE
LEADING	LIKE_EXPAND	LOCAL_INDEXES
MATERIALIZE	MERGE	MERGE_AJ
MERGE_SJ	MV_MERGE	NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS	NESTED_TABLE_SET_SETID	NL_AJ
NL_SJ	NO_ACCESS	NO_BUFFER
NO_EXPAND	NO_EXPAND_GSET_TO_UNION	NO_FACT
NO_FILTERING	NO_INDEX	NO_MERGE
NO_MONITORING	NO_ORDER_ROLLUPS	NO_PRUNE_GSETS
NO_PUSH_PRED	NO_PUSH_SUBQ	NO_QKN_BUFF
NO_SEMIJOIN	NO_STATS_GSETS	NO_UNNEST
NOAPPEND	NOCACHE	NOCPU_COSTING
NOPARALLEL	NOPARALLEL_INDEX	NOREWRITE
OR_EXPAND	ORDERED	ORDERED_PREDICATES
OVERFLOW_NOMOVE	PARALLEL	PARALLEL_INDEX
PIV_GB	PIV_SSF	PQ_DISTRIBUTE
PQ_MAP	PQ_NOMAP	PUSH_PRED
PUSH_SUBQ	REMOTE_MAPPED	RESTORE_AS_INTERVALS
REWRITE	RULE	SAVE_AS_INTERVALS
SCN_ASCENDING	SELECTIVITY	SEMIJOIN
SEMIJOIN_DRIVER	SKIP_EXT_OPTIMIZER	SQLLDR
STAR	STAR_TRANSFORMATION	SWAP_JOIN_INPUTS
SYS_DL_CURSOR	SYS_PARALLEL_TXN	SYS_RID_ORDER
TIV_GB	TIV_SSF	UNNEST
USE_ANTI	USE_CONCAT	USE_HASH
USE_MERGE	USE_NL	USE_SEMI
USE_TTT_FOR_GSETS

In this output we see 57 undocumented SQL hints:

BYPASS_RECURSIVE_CHECK	BYPASS_UJVC	CACHE_CB
CACHE_TEMP_TABLE	CIV_GB	COLLECTIONS_GET_REFS
CUBE_GB	CURSOR_SHARING_EXACT	DEREF_NO_REWRITE
DML_UPDATE	DOMAIN_INDEX_NO_SORT	DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING	DYNAMIC_SAMPLING_EST_CDN	EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK	GBY_CONC_ROLLUP	GLOBAL_TABLE_HINTS
HWM_BROKERED	IGNORE_ON_CLAUSE	IGNORE_WHERE_CLAUSE
INDEX_RRS	INDEX_SS	INDEX_SS_ASC
INDEX_SS_DESC	LIKE_EXPAND	LOCAL_INDEXES
MV_MERGE	NESTED_TABLE_GET_REFS	NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID	NO_EXPAND_GSET_TO_UNION	NO_FACT
NO_FILTERING	NO_ORDER_ROLLUPS	NO_PRUNE_GSETS
NO_STATS_GSETS	NO_UNNEST	NOCPU_COSTING
OVERFLOW_NOMOVE	PIV_GB	PIV_SSF
PQ_MAP	PQ_NOMAP	REMOTE_MAPPED
RESTORE_AS_INTERVALS	SAVE_AS_INTERVALS	SCN_ASCENDING
SKIP_EXT_OPTIMIZER	SQLLDR	SYS_DL_CURSOR
SYS_PARALLEL_TXN	SYS_RID_ORDER	TIV_GB
TIV_SSF	UNNEST	USE_TTT_FOR_GSETS

These Oracle SQL hints can be extremely useful for solving complex SQL execution problems, and that is why Oracle created them. They generally remain undocumented because Oracle does not want someone with inadequate knowledge of optimizer internals using them because they could lead to confusing results. However, for the experienced SQL tuning professional these undocumented hints are a godsend.

Conclusion

In this first installment we have shown you how to extract powerful (but potentially dangerous) undocumented utilities, parameters, hints, packages and executables. Remember, many of these undocumented features have unknown effects and should be treated with great care.

Oracle Optimizer hint tips

With each new release of Oracle, there are more tuning controls and opportunities for improving the execution speed of SQL. In an article about speeding up SQL execution in Oracle, I discussed some common SQL tuning hints and, in another article, I warned about performance issues when using SQL hints inside views. 


   - Download the Oracle hint quick reference

   - Also note these important notes on Oracle hint tuning.

Oracle tuning with hints has always been complex, and the Oracle Technical Network has a great overview into the processes of using hints to tune Oracle SQL. With the introduction of 10g database, there are many new optimizer hints available to control the optimizer behavior. Let’s take a quick tour of these powerful new hints:

spread_min_analysis:  With this hint, you can omit some of the compile time optimizations of the rules on spreadsheets, such as detailed dependency graph analysis. Other optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used.

This hint helps to reduce compilation time because spreadsheet analysis may be lengthy if the number of rules is significantly large, such as more than several hundred.

For example:

SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

spread_no_analysis:  With this hint, it's possible to have no spreadsheet analysis. Rule-pruning and filter generation are also omitted when this hint is used. If there is spreadsheet analysis, the compilation time is minimized.

For example:

SELECT /*+ SPREAD_NO_ANALYSIS */ ...

use_nl_with_index:  This hint causes the CBO to join the specified table to another row source with a nested loops join. It uses the specified table as the inner table, but only under this condition: If no index is specified, the CBO must be able to use some index with at least one join predicate as the index key. If an index is specified, the CBO must be able to use that index with at least one join predicate as the index key.

For example:

SELECT /*+ USE_NL_WITH_INDEX (polrec polrind) */ ...
 

CARDINALITY:  This hint specifies the estimated cardinality returned by a query or portions of the query. Note that if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:

SELECT /*+ CARDINALITY ( [tablespec] card ) */

SELECTIVITY:  This hint specifies the estimated selectivity of a query or portions of the query. If only one table is specified, the selectivity is the fraction of rows of that table that satisfies all single-table predicates on the specified table. If a list of tables is specified, the selectivity is the fraction of rows of the result of joining those tables in any order that satisfies all applicable predicates.

For example:

SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

However, note that if both CARDINALITY and SELECTIVITY hints are specified on the same set of tables, both hints will be ignored.

no_use_nl:  The no_use_nl hint causes the CBO to exclude nested loops to join each specified table to another row source, using the specified table as the inner table. With this hint, only the hash join and the sort-merge joins will be considered for the specified tables.

For example:

SELECT /*+ NO_USE_NL ( employees ) */ ...

no_use_merge:  This hint causes the CBO to exclude sort-merge to join each specified table to another row source, using the specified table as the inner table.

For example:

SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

no_use_hash:  This hint causes the CBO to exclude hash joins to join each specified table to another row source, using the specified table as the inner table.

For example:

SELECT /*+ NO_USE_HASH ( employees dept ) */ ...

no_index_ffs:  This hint causes the CBO to exclude a fast full-index scan of the specified indexes on the specified table.

Syntax: /*+ NO_INDEX_FFS ( tablespec indexspec ) */

no_index_ss:  This hint causes the CBO to exclude a skip scan of the specified indexes on the specified table.

Syntax: /*+ NO_INDEX_SS (tablespec indexspec ) */

no_star_transformation:  This hint causes the CBO to omit star query transformation.

Syntax: /*+ NO_STAR_TRANSFORMATION */

index_ss:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

Syntax: /*+ INDEX_SS (tablespec indexspec) */

index_ss_asc:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. In a partitioned index, the results are in ascending order within each partition.

Syntax: /*+ INDEX_SS_ASC (tablespec indexspec) */

index_ss_desc:  This hint explicitly chooses an index skip scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

Syntax: /*+ INDEX_SS_DESC (tablespec indexspec) */

cpu_costing:  This hint turns CPU costing on for the SQL statement. This is the default cost model for the optimizer. The optimizer estimates the number and type of IO operations and the number of CPU cycles the database will perform during execution of the given query. It uses system statistics to convert the number of CPU cycles and number of IO(s) to the estimated query execution time. The CPU_COST column of the PLAN_TABLE stores the CPU cost.

Syntax: /*+ CPU_COSTING (tablespec indexspec) */

no_cpu_costing:  This hint turns off CPU costing for the SQL statement. CBO then uses the IO cost model, which measures everything in single-block reads and ignores CPU cost.

Syntax: /*+ NO_CPU_COSTING */

As the Oracle optimizer becomes more sophisticated, Oracle professionals must constantly add to their arsenal of tools for tuning SQL statements. While it is far beyond the scope of this article to discuss all of the intricacies of the new Oracle10g SQL hints

Oracle leading vs. ordered hints

The "ordered" hint is extremely useful for cutting-down query parse time and ensuring proper table join order for static tables and queries.

• Good article on using the ordered hint
• Ault on the ordered hint

/* LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.

• The "leading" hint is ignored if the tables specified cannot be joined first in the order specified
• If you specify two or more conflicting LEADING hints – all of them are ignored
• The ORDERED hint overrides all LEADING hints

/* ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause

• The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)
• You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not

The Oracle 10g documentation notes the difference between the "ordered" and "leading" hints:

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

Guard against performance issues when using Oracle9i hints and views

Used separately, hints and views are terrific tools for Oracle tuning. But used together, they can cause severe performance problems. Let’s look at what hints, views, and materialized views can do for you. Then, we'll consider when and how to use them. Hints Hints are compiler directives that change the execution plan for SQL statements. Of course, if the Oracle cost-based optimizer (CBO) and rule-based optimizer (RBO) always made optimal execution plans, hints would not be necessary. There are two classes of hints:   General mode hints—This class of hints changes the overall execution mode for Oracle queries. These hints include /*+ rule */ and /*+ first_rows */. Detailed directive hints—This class of hints directs specific access paths, such as the use of a hash join over a nested loop join, /*+ use_hash */, and the use of a specific index to access a table. In sum, hints are a necessary and useful tool for tuning SQL statements. Views An Oracle view is the encapsulation of a complex query into a single pseudotable that behaves like a single table. For example, here we create a view: create or replace view   cust_view as select   customer_name,    order_nbr,    item_desc from    customer     c,    order        o,    item         i, where    c.cust_nbr = o.cust_nbr and    o_item_nbr = i.item_nbr; The pseudotable in the following query hides the complexity of the underlying query and has no effect on the performance of the underlying SQL: select * from cust_view where cust_nbr = 123; In this example, every time the cust_view is queried, Oracle will join the three tables at runtime. Since views don't improve performance, why use them? Most Oracle shops that employ views do so for end-user queries or for queries where they want to hide complexity and ensure uniform join methods. Materialized views Oracle’s solution to improving performance of standard views is the materialized view. When you create a material view, it prejoins all of the tables in a complex query. Since all of the query joins have been done, running SQL against the materialized view will be far faster than with a standard view. However, materialized views have some shortcomings: More storage is required—Since the materialized view actually performs the query, extra disk space is required to store the result table. Materialized views become stale—From the moment the materialized view is created, the view may become out-of-date. To periodically refresh a materialized view, you can use a mechanism that's similar to an Oracle snapshot. The danger of using views Some shops create complex views to represent large subsets of their schema and allow developers and end users to access these views. This approach often leads to poor performance. Here are some situations to avoid if you work with complex views:   Querying subsets—Developers will often query a subset of the complex view, not realizing that all tables in the view will be joined. Adding complex WHERE clauses—Queries against views with complex WHERE clauses will often override any tuning hints that are placed within the view, causing suboptimal execution plans. Hinting the view—A view can't be treated as a finite table, and adding SQL hints to view queries will often result in suboptimal execution plans. Remember, any time the optimizer gets “confused,” it will perform an unnecessary full-table scan. While hints can be used for specific SQL optimization, the use of views is strongly discouraged with hints because they can be invoked in many contexts. To summarize, Oracle views are an encapsulation of a complex query and must be used with care. Here are the key facts to remember: Views are not intended to improve SQL performance. When you need to encapsulate SQL, you should place it inside a stored procedure rather than use a view. Views hide the complexity of the underlying query, making it easier for inexperienced programmers and end users to formulate queries. Views can be used to tune queries with hints, provided that the view is always used in the proper context. Combining hints and views Although you must be careful when using hints against a view, here are two ways you can use them without creating performance problems: You can embed hints inside the view definition. This is useful for cases where a view will be called without a WHERE clause, but it can be quite damaging to performance when the view result set is altered by calling the view with a complex WHERE clause. You can add hints in the calling query. The danger with using hints in views is that the context of the query may change. When this happens, any existing hints within the view definition may be ignored, which can confuse the SQL optimizer and result in an unnecessary full-table scan. When views are invoked with certain WHERE clauses, the context of the view may change, as will the functionality of any SQL hints that may be embedded inside the view. This simple example shows how such a context change can occur: select    cust_name,    cust_address from    cust_view where    cust_nbr = 123; We've invoked a view that performs a three-way table join on execution, but the WHERE clause in the SQL indicates that the user is interested only in data within a single table. Any SQL hints that might be embedded inside the view may be ignored. Conclusion By themselves, Oracle views, materialized views, and SQL query hints are useful tools for Oracle tuning. However, special care must be taken when implementing views to ensure that developers and end users don't misuse them and create performance problems.

Using Hints to Change Execution Plans and Undocumented Oracle Hints

As the optimizer becomes more sophisticated with each release, Oracle provides an increasing number of methods for changing the execution plans for your SQL. The most common use for Oracle hints is as a debugging tool. You can use the hints to determine the optimal execution plan, and then work backward, adjusting the statistics to make the vanilla SQL simulate the hinted query.

Using Oracle hints can be very complicated and Oracle developers only use hints as a last resort, preferring to alter the statistics to change the execution plan. Oracle contains more than 124 hints, and many of them are not found in the Oracle documentation. (See Listing 2)

Listing 2: Documented Oracle Hints:

ALL_ROWS AND_EQUAL ANTIJOIN APPEND BITMAP BUFFER BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE CACHE_CB CACHE_TEMP_TABLE CARDINALITY CHOOSE CIV_GB COLLECTIONS_GET_REFS CPU_COSTING CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DRIVING_SITE DYNAMIC_SAMPLING DYNAMIC_SAMPLING_EST_CDN EXPAND_GSET_TO_UNION FACT FIRST_ROWS FORCE_SAMPLE_BLOCK FULL GBY_CONC_ROLLUP GLOBAL_TABLE_HINTS HASH HASH_AJ HASH_SJ HWM_BROKERED IGNORE_ON_CLAUSE IGNORE_WHERE_CLAUSE INDEX_ASC INDEX_COMBINE INDEX_DESC INDEX_FFS INDEX_JOIN INDEX_RRS INDEX_SS

INDEX_SS_ASC INDEX_SS_DESC INLINE LEADING LIKE_EXPAND LOCAL_INDEXESMATERIALIZE MERGE MERGE_AJ MERGE_SJ MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NL_AJ NL_SJ NO_ACCESS NO_BUFFER NO_EXPAND NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_INDEX NO_MERGE NO_MONITORING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_PUSH_PRED NO_PUSH_SUBQ NO_QKN_BUFF NO_SEMIJOIN NO_STATS_GSETS NO_UNNEST NOAPPEND NOCACHE NOCPU_COSTING NOPARALLEL NOPARALLEL_INDEX NOREWRITE OR_EXPAND ORDERED ORDERED_PREDICATES OVERFLOW_NOMOVE

PARALLEL PARALLEL_INDEX PIV_GB PIV_SSF PQ_DISTRIBUTE PQ_MAP PQ_NOMAP PUSH_PRED PUSH_SUBQ REMOTE_MAPPED RESTORE_AS_INTERVALS REWRITE RULE SAVE_AS_INTERVALS SCN_ASCENDING SELECTIVITY SEMIJOIN SEMIJOIN_DRIVER SKIP_EXT_OPTIMIZER SQLLDR STAR STAR_TRANSFORMATION SWAP_JOIN_INPUTS SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_ANTI USE_CONCAT USE_HASH USE_MERGE USE_NL USE_SEMI USE_TTT_FOR_GSETS

Undocumented Hints:

BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE_CB CACHE_TEMP_TABLE CIV_GB COLLECTIONS_GET_REFS CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DYNAMIC_SAMPLING DYNAMIC_SAMPLING_EST_CDN EXPAND_GSET_TO_UNION FORCE_SAMPLE_BLOCK GBY_CONC_ROLLUP GLOBAL_TABLE_HINTS HWM_BROKERED

IGNORE_ON_CLAUSE IGNORE_WHERE_CLAUSE INDEX_RRS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC LIKE_EXPAND LOCAL_INDEXES MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_STATS_GSETS NO_UNNEST NOCPU_COSTING

OVERFLOW_NOMOVE PIV_GB PIV_SSF PQ_MAP PQ_NOMAP REMOTE_MAPPED RESTORE_AS_INTERVALS SAVE_AS_INTERVALS SCN_ASCENDING SKIP_EXT_OPTIMIZER SQLLDR SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_TTT_FOR_GSETS

Let's take a quick look at how hints are used to alter optimizer execution plans: A optimizer hint is an optimizer directive placed inside comments inside your SQL statement and used in those rare cases where the optimizer makes an incorrect decision about the execution plan. Because hints are inside comments, it is important to ensure that the hint name is spelled correctly and that the hint is appropriate to the query.

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query:

Oracle full-table scan Illustration

Oracle Index access illustration

For example, the following hint is invalid because first_rows_n access and parallel access are mutually exclusive. That's because parallel always assumes a full-table scan and first_rows_nfavors index access.

-- An invalid hint
select /*+ first_rows_100 parallel(emp,8)*/ 
   emp_name 
from 
  emp 
order by 
   ename;

Some Oracle professionals will place hints together to reinforce their wishes. For example, if we have an SMP server with eight or more CPUs, we may want to use Oracle Parallel Query to speed-up legitimate full-table scans. When using parallel query, we seldom want to turn-on parallelism at the table level (alter table customer parallel 35;) because the setting of parallelism for a table influences the optimizer, causing the optimizer to see full-table scan is inexpensive. Hence, most Oracle professionals specify parallel query on a   query-by-query basis, combining the full hint with the parallel hint to ensure a fast parallel full-table scan:

-- A valid hint
select /*+ full(emp) parallel(emp,35)*/ 
   emp_name 
from 
   emp 
order by 
   ename;

Now that we have the general concept of hints, let's take a look at one of the most important hints for optimizer tuning.

The ordered hint determines the driving table for the query execution and also specifies the order that tables are joined together. The ordered hint requests that the tables should be joined in the order that they are specified in the from clause, with the first table in the from clause specifying the driving table. Using the ordered hint can save a huge amount of parse time and speed SQL execution because you are telling the optimizer the best order to join the tables.

For example, the following query uses the ordered hint to join the tables in their specified order in thefrom clause. In this example, we further refine the execution plan by specifying that the emp to dept join use a hash join and the sal to bonus join use a nested loop join:

select 
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
from
   emp,
   dept,
   sal,
   bon
where . . .

Of course, the ordered hint is most commonly used in data warehouse queries or in SQL that joins more than five tables.

Oracle SQL Hints Tuning

There are many Oracle hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL.

You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, Oracle hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ Oracle hints can tame a wild optimizer and give you optimal performance.

Oracle hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.

SELECT   /*+ hint --or-- text */  statement body   -- or --  SELECT   --+ hint --or-- text  statement body

Where:

• /*, */  -  These are the comment delimiters for multi-line comments
• --  -  This is the comment delimiter for a single line comment (not usually used for hints)
• +  -  This tells Oracle a hint follows, it must come immediately after the /*
• hint  -  This is one of the allowed hints
• text  -  This is the comment text

Oracle Hint	Meaning
+	Must be immediately after comment indicator, tells Oracle this is a list of hints.
ALL_ROWS	Use the cost based approach for best throughput.
CHOOSE	Default, if statistics are available will use cost, if not, rule.
FIRST_ROWS	Use the cost based approach for best response time.
RULE	Use rules based approach; this cancels any other hints specified for this statement.
Access Method Oracle Hints:
CLUSTER(table)	This tells Oracle to do a cluster scan to access the table.
FULL(table)	This tells the optimizer to do a full scan of the specified table.
HASH(table)	Tells Oracle to explicitly choose the hash access method for the table.
HASH_AJ(table)	Transforms a NOT IN subquery to a hash anti-join.
ROWID(table)	Forces a rowid scan of the specified table.
INDEX(table [index])	Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.
INDEX_ASC (table [index])	Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.
INDEX_DESC(table [index])	Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index)	Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.
INDEX_FFS(table index)	Perform a fast full index scan rather than a table scan.
MERGE_AJ (table)	Transforms a NOT IN subquery into a merge anti-join.
AND_EQUAL(table index index [index index index])	This hint causes a merge on several single column indexes. Two must be specified, five can be.
NL_AJ	Transforms a NOT IN subquery into a NL anti-join (nested loop).
HASH_SJ(t1, t2)	Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
MERGE_SJ (t1, t2)	Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
NL_SJ	Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.
Oracle Hints for join orders and transformations:
ORDERED	This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.
STAR	Forces the largest table to be joined last using a nested loops join on the index.
STAR_TRANSFORMATION	Makes the optimizer use the best plan in which a start transformation is used.
FACT(table)	When performing a star transformation use the specified table as a fact table.
NO_FACT(table)	When performing a star transformation do not use the specified table as a fact table.
PUSH_SUBQ	This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.
REWRITE(mview)	If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.
NOREWRITE	Turns off query rewrite for the statement, use it for when data returned must be concurrent and can't come from a materialized view.
USE_CONCAT	Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNIONALL set operator.
NO_MERGE (table)	This causes Oracle to join each specified table with another row source without a sort-merge join.
NO_EXPAND	Prevents OR and IN processing expansion.
Oracle Hints for Join Operations:
USE_HASH (table)	This causes Oracle to join each specified table with another row source with a hash join.
USE_NL(table)	This operation forces a nested loop using the specified table as the controlling table.
USE_MERGE(table,[table, - ])	This operation forces a sort-merge-join operation of the specified tables.
DRIVING_SITE	The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
LEADING(table)	The hint causes Oracle to use the specified table as the first table in the join order.
Oracle Hints for Parallel Operations:
[NO]APPEND	This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.
NOPARALLEL (table	This specifies the operation is not to be done in parallel.
PARALLEL(table, instances)	This specifies the operation is to be done in parallel.
PARALLEL_INDEX	Allows parallelization of a fast full index scan on any index.
Other Oracle Hints:
CACHE	Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
NOCACHE	Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.
[NO]APPEND	For insert operations will append (or not append) data at the HWM of table.
UNNEST	Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.
NO_UNNEST	Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.
PUSH_PRED	Pushes the join predicate into the view.

As you can see, a dilemma with a stubborn index can be easily solved using FULL or NO_INDEX Oracle hints. You must know the application to be tuned. The DBA can provide guidance to developers but in all but the smallest development projects, it will be nearly impossible for a DBA to know everything about each application. It is clear that responsibility for application tuning rests solely on the developer's shoulders with help and guidance from the DBA. 

Using Global Hints

While Oracle hints normally refer to table in the query it is possible to specify a hint for a table within a view through the use of what are known as Oracle GLOBAL HINTS. This is done using the Oracle global hint syntax. Any table hint can be transformed into an Oracle global hint.

The syntax is:

/*+ hint(view_name.table_in_view) */

For example:

/*+ full(sales_totals_vw.s_customer)*/

If the view is an inline view, place an alias on it and then use the alias to reference the inline view in the Oracle global hint.