Here are some good scripts for Oracle tuning.
REM This SQL script will produce a current listing of
REM suspect SQL statements based on a specified
REM level of disk reads against the database instance.
SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS >= &DISK_READS
ORDER BY DISK_READS DESC
/
REM This SQL script will display all current terminal
REM sessions attached to a specific database instance.
spool c:\temp\session.lis
SELECT a.sid sessionno,
substr(a.username,1,10) user_name,
substr(a.schemaname,1,10) schema,
substr(a.osuser,1,15) client_user,
substr(a.client_info,1,25) dbms_client_info,
substr(a.machine,1,15) wk_station,
substr(a.program,1,31) program_name,
a.status,
to_char(a.logon_time,'DD-MON-YYYY HH24:MM:SS') logontime,
b.name command_action
FROM v$session a, sys.audit_actions b
WHERE a.command = b.action
/
spool off
REM This SQL script will produce a current listing of
REM column statistics for a specific table.
col num_distinct format 999,999,999
col num_nulls format 999,999,999
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = upper('&TABLE_NAME')
ORDER BY COLUMN_NAME
/
REM This SQL script will change the SQL prompt to
REM refer to the instance being used at the time.
REM
REM This will eliminate the need to execute the
REM statement:
REM
REM SELECT name from v$database;
set termout off
column x new_value y
SELECT rtrim(instance, chr(0)) x
FROM v$thread
/
set sqlprompt '&Y>'
set termout on
REM
REM Encountering: SP2-0110: Cannot create save file "afiedt.buf"
REM
REM The user may not have permission to edit in that
REM specific directory.
REM
set editfile C:\TEMP\AFIEDT.BUF
REM
REM Set line and page size lengths
REM
set linesize 100
set pagesize 80
