For example, the following syntax could be used to get a text dump on the controlfile in the trace file of the current process showing all the controlfile record types but only the oldest and most recent of the circular reuse records.

oradebug setmypid oradebug dump controlf 3

alter session set events 'immediate trace name controlf level 3';

 

Oracle Advanced Performance Tuning Scripts

Dump and Tracing Scripts

This script reports the pathname to the trace file for the current session. The trace file name is remembered by SQL*Plus for use by other APT scripts.

Note: The format of the trace file name is platform specific. This script contains three separate queries for different platforms. It is the user's responsibility to comment out those that do not apply.

Download script for:

This is a simple script to set a trace event in the current session. Trace information is written to the process trace file in the user_dump_dest directory. The trace events are normally numbers in the range 10000 to 10999. For example, event 10053 can be used to trace cost-based optimizer execution path evaluation, and event 10104 can be used to trace hash join processing. Other event numbers are mentioned elsewhere on this site. For most events, the level is merely 1. However, multiple levels are defined for some events to control the level of detail returned.

Warning: Setting events changes the behavior of the Oracle server, and can destroy databases. Do not set events on production instances unless you have checked with Oracle Support.

Download script for:

This is a simple script to stop a trace in the current session.

Download script for:

This script checks to see which events are set in the current session.

Download script for:

This script can be used to set an event in another session. To select a session before running this script, please see the first three scripts on the page. To turn an event off, use level 0.

Download script for:

This is a simple script to dump an internal structure to the trace file for the current process. The systemstate and library_cache dumps are the most frequently used, at levels 10 and 4 respectively.

Warning: These dumps can produce VERY large trace files. They can also have a noticeable effect on system performance. There are other dumps that can damage databases. As always, don't use these things on production instances unless you have checked with Oracle Support.

Download script for:

This script dumps the contents of a redo log file to the trace file for the current process in human (?) readable format. One nice thing about this script is that it can remind you about the syntax for the options.

Warning: Log file dumps can produce VERY large trace files. As always, don't do this on production instances unless you have checked with Oracle Support.

Download script for:

Two of the options for dumping logfiles are to specify minimum and maximum time boundaries for the redo to be dumped. Those times need to be expressed in decimal using a special formula, which is what this script does.

Download script for:

This script can be used to dynamically zip the current process's trace file, prior to taking a large dump or trace, like a redo log file dump or systemstate dump. It creates a named pipe in place of the process's trace file, and spawns a gzip process to compress it. This slows down the taking dumps and traces significantly, but it is sometimes necessary if disk space is short.

This script is Unix specific, and uses the script above.

Download script for:

This script deletes the named pipe created by trace_zip.sql.

Download script for:

This is one of the most important script for learning about Oracle internals. It dumps data blocks in a human readable form to the process' trace file. The Oracle8 facility allows for a range of blocks to be dumped, but in Oracle7 you can only dump one block at a time. Another important difference to be aware of is that Oracle8 does consistent gets on the blocks to be dumped, whereas Oracle7 dumps the current mode buffer.

Download script for: ,

In Oracle7 you can only dump one block at a time, but this script can be used to dump consecutive blocks.

Download script for:

This script does blockdumps in hex, rather than in human-readable format.

Download script for: ,

And this is the Oracle7 follow-on script.

Download script for:

This script dumps a segment header block by name, rather than by numbers.

Download script for: ,

This script dumps the structure of an index tree. The dump file contains one line for each block in the tree, indented to show its level, together with a count of the number of index entries in the block.

Download script for: ,

This script is intended for researching where new blocks are introduced to each buffer pool, and how the buffer aging algorithm works. It reads a trace file containing a dump of the buffer headers at level 4, and prints out the file and block number of each buffer in LRU order. The buffers dump can be taken as follows:

oradebug dump buffers 4

or

alter session set events 'immediate trace name buffers level 4';

The trace file must not contain more than one dump, so each dump must be taken from a new session.

Download script for:   -- It probably works back to 7.3 also, but has not been tested.

This script identifies the shared pool chunk containing a specific memory address. The address must already be defined to SQL*Plus in decimal as &Addr. This script is dependent on the function below.

Download script for:

This script creates a function that converts RAW values to NUMBERs. This is useful for calculating offsets from memory addresses.

Download script for:

This script creates a view over V$SESSION_EVENT that selects just the rows for the current session.

Download script for:

---