Oracle Disk Reads Script / Oracle Tuning Efforts

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