Oracle Advanced Performance Tuning Scripts

Session Scripts

my_sid.sql

This script reports the SID and SERIAL# for the current session. The SID is remembered by SQL*Plus for use by other APT scripts.
Download script for: 7.3, 8.0 or 8.1

set_sid.sql

This script sets the SID that SQL*Plus will remember for use by other APT scripts.
Download script for: 7.3, 8.0 or 8.1

set_background.sql

This script sets the SID that SQL*Plus will remember for use by other APT scripts to the SID of a named background process.
Download script for: 7.3, 8.0 or 8.1

session_times.sql

This script reports the breakdown of elapsed time for the session selected using one of the preceding three scripts. When a user reports an episode of poor performance, this script can be used to determine whether their session has been working (say on an inefficient query) or waiting for resources, and if so, which resources they have been waiting for. Idle waits are not excluded.
Download script for: 7.3, 8.0 or 8.1

post_process.sql

This script posts the Oracle process selected using one of the above three scripts. This is mainly done for testing purposes. However, see the script post_smon.sql as well. The 7.3/8.0 version is Unix specific, and the user must be able to connect internal without giving a password. The 8.1 version is generic, and requires that the user be connected as SYSDBA.
Download script for: 7.3 or 8.0, 8.1

suspend_process.sql

This script suspends an Oracle process indefinitely. See the tip on how to use it. The 7.3/8.0 version is Unix specific, and the user must be able to connect internal without giving a password. The 8.1 version is generic, and requires that the user be connected as SYSDBA. However, it should not be used on NT before release 8.1.6, because it suspends the current thread instead.
Download script for: 7.3 or 8.0, 8.1

resume_process.sql

This script is the complement to the previous one. It resumes a suspended Oracle process. The comments made above apply here too.
Download script for: 7.3 or 8.0, 8.1