Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
ORACLE DATABASE ADMINISTRATOR
 

Hidden Oracle, part I: Exploring undocumented utilities


Donald K. Burleson
18 Sep 2003

To learn more about these techniques, see the book "Advanced Oracle Utilities: The Definitive Reference". 

You can buy it directly from the publisher and get instant access to the code depot of utilities scripts.


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.

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.",  Rampant TechPress
  • "Oracle Utilities: Using Hidden Programs, Import/Export, SQL*Loader, Oradebug, Dbverify, Tkprof." Dave Moore, 2003, Rampant TechPress


For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress


 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational