Hidden Oracle, part II: Using undocumented parameters

In part 1 of this series we explored ways to extract undocumented packages, hints, utilities and executables from Oracle. Now we are ready to explore some ways that the undocumented Oracle parameters are used to aid the Oracle professional in managing and tuning their systems.

Oracle makes a huge disclaimer that the undocumented initialization parameters are usually only used in emergencies. However, those who want to manipulate the internal mechanisms of Oracle to customize the behavior to their systems find the undocumented parameters very useful.

WARNING! Using undocumented parameters without the consent of Oracle can make your system un-supported and you may be on your own if you experience data corruption. Don't say I didn't warn you!

With over 100 undocumented parameters, it is impossible to cover them all in this short article. Let's look at performance-related parameters first.

Many savvy Oracle professionals commonly adjust the hidden parameters to improve the overall performance of their systems. However, because these are "undocumented" parameters, most Oracle professionals rely on publications such as "Oracle Internals" to get insights into the proper setting for the hidden parameters.

Oracle latch parameters: Whenever index contention is experienced (as evidenced by process waits), adjusting the following parameters may be helpful.

Oracle parallel query parameters: OPQ is an amazing facility for improving the speed of large-table full-table scans, and some DBAs are not aware that there are a dozen hidden parameters that can be changed to affect the behavior of parallel queries. Here is a query to display the parameters (Note that this script must be run from the SYS user as only the SYS user can access the X$ internal tables):

The most important of these hidden parallel parameters is the _parallelism_cost_fudge_factor. This parameter governs the invocation of OPQ by t

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle Database Administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

he cost-based SQL optimizer when parallel_automatic_tuning=true. By adjusting this parameters you can control the threshold for invoking parallel queries.

Now let's look at some common DBA undocumented parameters. The following undocumented parameters are the most commonly-used in Oracle administration.

Corruption undocumented parameters - These parameters allow you to ignore corrupt data blocks when your database is corrupted. These should only be used in emergencies.

SQL Optimizer undocumented parameters: These parameters control the internal behavior of the cost-based SQL optimizer (CBO).

Data Buffer behavior parameters: For the very brave DBA, you can change the caching and aging rules within the Oracle db_cache_size and change the way that Oracle keeps data blocks in RAM memory. While these parameters are somewhat dangerous, some savvy DBAs have been able to get more efficient data caching by adjusting these values:

Conclusion

These Oracle undocumented parameters are especially useful to the senior Oracle DBA who needs to go beyond the recommended level of detail and wants to change the internal behavior of their SGA. The undocumented parameters are also a lifesaver for performing re-starts of corrupted databases, but we must always remember that these parameters are hidden for a reason. They are very powerful and undocumented, so you should only play with them if you have a clear understanding about how they change the internal behavior of Oracle.

About the Author

Don Burleson is one of the world's top Oracle Database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world's most powerful and complex systems. A former Adjunct Professor, Don Burleson has written 32 books, published more than 100 articles in national magazines, serves as Editor-in-Chief of Oracle Internals and edits for Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Don's Web sites include DBA-Oracle, Remote-DBA, Oracle-training, remote support and remote DBA.

References

For More Information