Corrected 29 September 2003
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
Requires Free Membership to View
- (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 can use the v $event_name view to quickly locate all new system events in Oracle9i. 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.
In our next installment we will take a look at how the experienced Oracle professional can use undocumented features to improve Oracle performance.
References
- "Mike Ault's Oracle Internals Monitoring & Tuning Scripts." Mike Ault, 2003, Rampant TechPress
- "Oracle Utilities: Using Hidden Programs, Import/Export, SQL*Loader, Oradebug, Dbverify, Tkprof." Dave Moore, 2003, Rampant TechPress
About the Author
Don Burleson has more than 20 years of full-time DBA experience, and has written 32 books, including "Oracle high-performance SQL tuning" and most recently "Creating a Self-Tuning Oracle Database" published by Rampant TechPress. He has also published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals magazine, and is owner of BEI Oracle Consulting.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
This was first published in September 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation