Site Summary

Oracle Scripts, Books and White Papers

Included below are a number of useful SQL scripts, white papers and books on Oracle, performance tuning and database analysis. I've tried to test as many of these out as possible, and where applicable I've also included the kind of output you can expect.
This section will continue to grow as I come across, use or develop new scripts.

Bookmarks on this Page

- Oracle and Unix Performance Tuning Books

- Database Analysis Scripts

- Database Reporting Scripts

- White Papers

Oracle and Unix Performance Tuning Books

Oracle Performance Tuning

Oracle Press

ISDN 0-07-882434-6

This is in my opinion the bible for performance tuning Oracle and covers real-world examples, insider secrets from TUSC and Oracle User Groups. Written by the Executive Vice President of TUSC, Richard Niemiec.

Oracle & Unix Performance Tuning

Prentice Hall

ISDN 0-13-907676-X

Highly recommended book for tuning Oracle on the UNIX platform. To quote the author, tuning Oracle without tuning UNIX is like tuning half the cylinders on your car. Written by the Oracle Senior Performance Engineer Ahmed Alomari.

Oracle 8 DBA Handbook

Oracle Press

ISDN 0-07-882406-0

Provides detailed explanations of what is going on behind the scenes in Oracle and introduces many of the concepts for non-DBAs. This revision also includes Oracle 8 and is often considered as the DBA bible.

Database Analysis Scripts

stats_before_and_after.ksh

This script is designed to be run before and after a run of your application to enable you to determine the key areas impacted. This version is a UNIX script which requires the SYS password and database name but can quickly be changed into an SQL*Plus script.

Usage:

stats_before_and_after.ksh <sys password> <db>

stats_before.log

Example output from stats_before_and_after.ksh showing the kind of information provided. This includes the number of reads and writes in each logical file system, number and type of system waits, rollback statistics and the Oracle parameter setting. Note that this log would normally be compared to the log generated after the execution run.

Database Reporting Scripts

buffer_cache.sql	Report the current buffer cache hit ratio
data_dictionary_cache.sql	Report the data dictionary cache hit ratio
free_memory.sql	Report the shared pool free memory
idxchk.sql	Report any INVALID indexes on database
info.sql	Dump out table, column and index information
last_analyze.sql	List when the tables where last analyzed
latch_hit_ratio.sql	Report the latch hit ratio on the database
sid_sql.sql	Using the SID (via su.sql) display their current SQL
su.sql	Who is logged on and what is their SID is
whoami.sql	Report who you are logged on as

White Papers

Array Fetching With Method/4	This Oracle white paper describes how to use array fetching with Dynamic SQL Method/4.It includes a sample program which is a modified version of the SAMPLE9.pc from the Pro*C Supplement manual.
Error Handling in Array Processing	This Oracle white paper describes some of the errors that you can receive when using array processing and how to handle them so that suitable information is provided to the user.