Oracle DBA script: Monitor user activity on the DB

Code :


set linesize 80
set verify off message off echo off pause off timing off time off
set feedback off
column o format a8 heading 'O/S|User'
column u format a10 heading 'Oracle|Userid'
column s format a12 heading 'R-S|Name'
column txt format a45 heading 'Current Statement' word

Oracle DBA script: Display hit ratio on Dictionary Cache

Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column

Code: 
select
parameter,gets,Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;

Oracle DBA script: tablespace usage in %, blocks used etc

Code :
SELECT RPAD(t.name,18,' ') tablespace ,
LPAD(TO_CHAR(CEIL( (SUM(s.blocks)*COUNT(*)) / (SUM(f.blocks) *
POWER(COUNT(DISTINCT(f.file#)),2)) * 100 )),3) Pct ,
LPAD(TO_CHAR(TRUNC(SUM(f.blocks) * t.blocksize *
COUNT(DISTINCT(f.file#)) /
( COUNT(*) * 1024 * 1024 * 1024 ),2)),6) vol_G ,
LPAD(TO_CHAR(SUM(f.blocks) * t.blocksize * COUNT(DISTINCT(f.file#)) /
( COUNT(*) * 1024 * 1024 )),8) volume_M ,
TRUNC(SUM(s.blocks) * t.blocksize /
( 1024 * 1024 * COUNT(DISTINCT(f.file#))),2) taken_M ,
TRUNC( ( SUM(f.blocks) * t.blocksize * COUNT(DISTINCT(f.file#))
/ ( COUNT(*) * 1024 * 1024 ) )
- ( NVL(suM(s.blocks),0) * t.blocksize
/ ( 1024 * 1024 * COUNT(DISTINCT(f.file#)) ) ),2) remain_M
FROM sys.seg$ s, sys.ts$ t, sys.file$ f
WHERE s.ts# (+) = t.ts#
AND f.ts# = t.ts#
AND f.status$ = 2
GROUP BY t.name, t.blocksize
ORDER BY 1;

Oracle DBA script: Display Quota Allocated

Code:

select * from DBA_TS_QUOTAS

order by Tablespace_Name, Username;

Oracle DBA script: List the UGA and PGA used by each session

column name format a25

column total format 999 heading 'Cnt'

column bytes format 9999,999,999 heading 'Total Bytes'

column avg format 99,999,999 heading 'Avg Bytes'

column min format 99,999,999 heading 'Min Bytes'

column max format 9999,999,999 heading 'Max Bytes'

ttitle 'PGA = dedicated server processes - UGA = Client machine process'

compute sum of minmem on report

compute sum of maxmem on report

break on report

select se.sid,n.name, 

max(se.value) maxmem

from v$sesstat se,

v$statname n

where n.statistic# = se.statistic#

and n.name in ('session pga memory','session pga memory max',

'session uga memory','session uga memory max')

group by n.name,se.sid

order by 3

/

Oracle DBA Script : Monitor and veryfy deadlocks

Monitor and Verify DEAD Locks: Holding and Waiting Sessions

Code: 

set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST

col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25

Check DirectX version with DirectX Diagnostic Tools in Windows

How to detect DirectX version installed in Windows.

Microsoft DirectX is a group of technologies designed to make Windows-based PCs an ideal platform for running and displaying applications rich in multimedia elements such as full- color graphics, 3D animation, video and rich audio.

DirectX includes security and performance updates, along with many new features across all technologies, which can be accessed by applications using the DirectX APIs.

Oracle DBA Script : Keep tablespace usage less than 80%

Keep tablespace usage < 80% Keep contiguous free space

Code:
prompt ** Tablespace Space Usage **
prompt
prompt Goal: Keep tablespace usage < 80%
prompt . Keep contiguous free space
prompt
prompt Corrective Action: Add more space to prompt tablespaces
prompt Coalesce fragmented free space.
prompt
compute sum LABEL 'TOTAL' of SUM_B1 on report
compute sum LABEL 'TOTAL' of SUM_B2 on report
compute sum LABEL 'TOTAL' of b3 on report
BREAK ON REPORT
Column TS1 Format A25 Heading 'TS Name'
Column SUM_B1 Format 999,999,999,999 Heading 'Total Space|Allocated'
Column SUM_B2 Format 999,999,999,999 Heading 'Total Free|Space'
Column b3 Format 999,999,999,999 Heading 'Total Space|Used'
Column b4 Format 90.99 Heading 'Percent|Used'
Column b5 Format 990.99 Heading 'Percent|Free'
Column b6 Format 99,999,999,999 Heading 'Max Single|Free Space'
Column b7 Format 999,999 Heading 'Count|Free|Space'

Oracle Find Top 20 Longest Running Process in UNIX

Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.

Shell Script:
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username
# and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp

Oracle DBA Script : Free Space left in the tablespaces

col tablespace_name format a20 heading 'Tablespace'

col allocated format 99,999,999,999 heading 'Allocated'

col used format 99,999,999,999 heading 'Used'

col unused format 99,999,999,999 heading 'Unused'

col pct_used format 99.99 heading 'PCT Used'

spool tbsp_free.lst

select rtrim(a.tblsp) tablespace,

sum(to_use) allocated,

sum(to_use - nvl(free,0)) used,

sum(nvl(free,0)) unused,

sum(((to_use-(to_use - nvl(free,0)))*100)/to_use) pct_used

from tspace_alc a,

tspace_free b

where a.tblsp = b.tblsp (+)

group by a.tblsp;

Oracle DBA Script : This PL/SQL script reports the %space left on each tablespace

DECLARE
CURSOR cur_ts IS
SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name;

CURSOR cur_ts_fs(p_ts VARCHAR2) IS
SELECT SUM(BYTES) fs,
MAX(bytes) mx
FROM dba_free_space
WHERE tablespace_name = p_ts;

lv_fs NUMBER := 0;
percent_fs NUMBER := 0;
large_fs NUMBER := 0;
lv_name V$database.name%TYPE;

BEGIN
DBMS_OUTPUT.enable(1000000);
SELECT name into lv_name from v$database;

Oracle DBA Script : Calculate buffer cache hit ratio in DB

Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value.

Code:
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where

DBA Script: Look for number of extents

Look for the number of chunks which are free,look for also the number of extents being used by objects..primarily for indexes

Code:
set pagesize 300
spool frag.rpt
Prompt Fragmentation Report for the tablespaces
start ./title132.sql "Tablespace Fragmentation report" "frag.sql"
column sumbytes format 999,999,999,9999
column extents forma 999999999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999,999,999
column Tot_Free format 999,999,999,999
column Pct_Free format 999
column Chunks_Free format 9999
column Max_Free format 9999999999
set echo off

PROMPT SPACE AVAILABLE IN TABLESPACES

select a.tablespace_name,sum(a.tots) Tot_Size,
sum(a.sumb) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
(sum(a.largest)/1024)/1024 Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name;

column owner format a15
column segment_name format a30

Oracle DBA Script : Full Table Scan SQL Statement

The output of this script will display all sql statements in the shared pool that are doing full table scans.

Code:
--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as

v_csr number;
v_rc number;
v_string varchar2(2000);

v_count number;

cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';
begin
for x1 in c1 loop
delete from plan_table ;
Begin

Oracle DBA Script: Display IO by file

Display IO by file

Code:
select
substr(a.file#,1,2) "#",
substr(a.name,1,30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

Oracle DBA: SET AUTOTRACE Command

Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!

Code:
The SET AUTOTRACE Command

In SQL*Plus 3.3 there is a little known command (at least I didn't know about it until recently) called SET AUTOTRACE. It is documented in the newest SQL*Plus document set, but who really reads the whole document set for changes? Well I did not. It is very simple to use. Just type the command:

SET AUTOTRACE ON

And then run your select statement. Example:

SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 /

DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
30 SALES 7900 JAMES
30 SALES 7521 WARD

14 rows selected.

DBA Script: Track the space utilization of database over time

A script to track the space utilization of your database over time

Code:
you must create a table and run this script first

insert into monitor.space_check select a.tablespace_name, sum(a.bytes) free_space,
sysdate timestamp , sum(b.bytes) total_space
from dba_data_files a, dba_free_space b
where a.tablespace_name = b.tablespace_name group by a.tablespace_name;

exit
===============================================
Then run this script

===============================================

Oracle DBA Script : Calculating DB_Block Buffer Efficiency

This script uses the new way of calculating DB_Block Buffer Efficiency.
Notable Points : ( Performance Tuning )
Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes

Code:
select name, ((consistent_gets + db_block_gets) - physical_reads) /
(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;

Oracle DBA Script : Generate Database Report

The script generates a report of tablesapces, data files, roll back segments, control files, log files, objects (user wise, valid and invalid)

Script:
set long 132
set serveroutput on
declare
cursor tablespaces is
select t.tablespace_name,
round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
cursor datafiles is
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

cursor rollbacksegments is
select segment_name,
tablespace_name, r.status,
(initial_extent/1024) InitialExtent,
(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

cursor controlfiles is select name from v$controlfile;

cursor logfiles is select member from v$logfile;

Oracle DBA Script : Show data files usage

Script : 
set linesize 188
set pagesize 26
SET FEEDBACK OFF
set heading on
set echo off
set timing off
variable xxx char(150)

column "Tablespace" format a16
COLUMN "File ID" FORMAT 9999999
column "Data file" format a43
column "Mb" format 9,999,999
column "Blocks" format 9,999,999
column "Status" format a10
COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "USED BLOCKS" FORMAT 999,999,999
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "FREE BLOCKS" FORMAT 999,999,999
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999

break on "Tablespace"

Oracle DBA Script : Best Tablespace utilization report

select
f.tablespace_name,
a.total,u.used,f.free,round((u.used/a.total)*100)"% used",
round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name