Old but still valuable
check_tab_stats.sql
REM ———————————————————————— REM REQUIREMENTS: REM SELECT on DBA_TABLES REM ———————————————————————— REM PURPOSE: REM This script pulls the statistics for a table from DBA_TABLES and REM presents them in a formatted report. It will only report correctly REM for tables which have been ANALYZEd. REM ———————————————————————— REM Main text of script follows: set echo off set […]
findlocks.sql
column username format a10 column sid format 999 column spid format 999999 column lock_type format a15 column mode_held format a11 column mode_requested format a10 column lock_id1 format a8 column lock_id2 format a8 select a.sid sid, p.spid spid, a.type lock_type, to_char(a.lmode) mode_held, to_char(a.request) mode_requested, to_char(a.id1) lock_id1, to_char(a.id2) lock_id2 from v$lock a, v$process p, v$session s where […]
create_statspack.sql
create tablespace perfstat datafile ‘perfstat_01.dbf’ size 10M autoextend on next 10M maxsize 2000M extent management local uniform size 128k segment space management auto / define perfstat_password=’CHANGEME’ define default_tablespace=’perfstat’ define temporary_tablespace=’temp’ @$ORACLE_HOME/rdbms/admin/spcreate.sql ———- Once installed, connect as perfstat. Run the following every 5-15 mins: SQL> execute statspack.snap; After collecting snapshots, create a report: @?/rdbms/admin/spreport – will […]
performance_check.sql
spool tuning_stats.txt set heading off ttitle ‘SYSTEM STATISTICS’ select ‘LIBRARY CACHE STATISTICS:’ from dual; ttitle off select ‘PINS – # of times an item in the library cache was executed – ‘|| sum(pins), ‘RELOADS – # of library cache misses on execution steps – ‘|| sum (reloads), ‘RELOADS / PINS * 100 = ‘||round((sum(reloads) / […]
system_snapshot
echo “SYSTEM PERFORMANCE SNAPSHOT” date echo “\n\nLooking at CPU Utilization and WIO” sar -u 5 2 echo “\n\nLooking at dIsk Utilizations” sar -d 5 2 echo “\n\nLooking at memory stats” vmstat 5 3 echo “\n\nQuick snapshot of system resources” top -d1 echo “\n\nLooking for defunct processes” ps -ef | grep defu | grep -v grep […]
DBA Best Practices
Daily Checklists 1. Check the alert.log for any errors or warnings. Please pay special attention to any ORA-600 or ORA-7445 errors. Also, look for any dead lock errors or warnings. If there are many (>10) such error messages, please contact Oracle support services. 2. Make sure daily database backups have run successfully by looking into […]