Oracle Advanced Performance Tuning Scripts

Prerequisite Scripts

create_xviews.sql

Some APT scripts are based directly on the X$ tables, rather than the V$ views. This is necessary where the V$ views do not expose the required information, or where it would be inefficient to query them. Because the X$ tables are only visible to the SYS schema, and because it would be bad practice to do anything as SYS unnecessarily, these scripts require that one create a set of X_$ views that expose the X$ tables to other DBA schemata.

This script creates an X_$ view for every X$ table. It must be run as SYS from SQL*Plus.

Download script for: 7.3, 8.0 or 8.1

drop_xviews.sql

This script drops the X_$ views created by create_xviews.sql. It must be run as SYS from SQL*Plus.

Why would you want to drop the X_$ views? To install StatsPack! StatsPack also uses some X_$ views, but the StatsPack install scripts fail if these views already exist. Therefore, to install StatsPack, we recommend that you first drop all the X_$ views created for APT using this script, then install StatsPack (which will create a few of the X_$ views), and then restore the remaining X_$ views by re-running create_xviews.sql once StatsPack has been successfully installed.

Download script for: 7.3, 8.0 or 8.1

accept.sql

This script is called by many of the other APT scripts to prompt for script parameters. It uses the SQL*Plus ACCEPT command, but allows for a default value to be accepted.

Download script for: 7.3, 8.0 or 8.1

save_sqlplus_settings.sql

Many APT scripts use this script and the next one to save and restore the SQL*Plus parameter settings in cases where they need to be temporarily changed.

Download script for: 7.3, 8.0 or 8.1

restore_sqlplus_settings.sql

Many APT scripts use this script and the previous one to save and restore the SQL*Plus parameter settings in cases where they need to be temporarily changed.

Download script for: 7.3, 8.0 or 8.1

apt_format.sql

This script creates a function to do a few things that to_char() does not do. It recognizes format masks ending with 'K', 'k' and '%'. If the format ends with 'K', then the value is returned in kilobytes, megabytes or gigabytes with 'K', 'M' or 'G' appended, depending on the scale of the value. Similarly, if the format ends with 'k', then the value is returned in units, thousands or millions with 'k' or 'm' appended in the latter cases. If the format ends with '%', the value is returned as a percentage with '%' appended. In each case variable precision is used, depending on the scale of the value. Several APT scripts use this function to render values with a large potential variability in scale with greater brevity and perspicuity than would otherwise be possible.

Download script for: 7.3, 8.0 or 8.1

next_prime.sql

This script creates a function to return the next prime number greater than or equal to a given value. This function is used by several APT scripts that need to suggest sizes for hash based data structures. In general, the performance of hashing mechanisms is optimal if the number of hash buckets is prime.

Download script for: 7.3, 8.0 or 8.1

as.sql

This is a generic script to execute a DDL statement on behalf of another user. It is used to grant execute permissions on the various functions and procedures that APT creates, normally in the SYSTEM schema. Please see the synopsis and notes in the script header for more information.

Download script for: 7.3, 8.0 or 8.1


Ixora Pty Ltd.   All rights reserved.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home