SQL scripts

To know SID and other information from sqlplus session
======================================================

SELECT userenv('SID') SID,sys_context('USERENV', 'CURRENT_SCHEMA') SCHEMA,sys_context('USERENV', 'OS_USER') OSUSER,sys_context('USERENV', 'HOST') HOSTNAME FROM DUAL;


Tracing
=======

At session level
================

exec dbms_system.set_sql_trace_in_session(1473, 32781, TRUE);

select 'exec dbms_system.set_sql_trace_in_session('||s.sid||','||s.serial#||', TRUE);' from v$session s,v$process p
where s.paddr = p.addr and
s.username='SCOTT';



At DB level
===========

ALTER SYSTEM SET trace_enabled = TRUE;

Execute below if trace shows bind variables and wait events

ALTER SYSTEM SET EVENT=’10046 trace name context forever, level 12’ scope=memory;

tkprof dbname_ora_17776.trc testtrace.txt explain=dev/dev@dbname sys=no waits=yes sort=exeela,userid

ls -l dbname_ora*.trc | awk '{print "tkprof "$9" "$9".txt explain=dev/dev@dbname sys=no waits=yes sort=exeela,userid"}'


User session as per Logon time
==============================

set pages 50000;
set linesize 160;
col username for a15
col osuser for a10
col machine for a20
col program for a35
select s.sid,s.serial#,p.spid,s.status,s.username,s.osuser,s.machine,s.program,to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS') logged_on from v$session s,v$process p where s.paddr = p.addr and s.username='SCOTT'
order by s.osuser;

To kill all Session for user
============================

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username in ('SCOTT');

Current active session's and sql they are running
================================================

set pages 10000
col username form a20
col machine form a30
col osuser form a10
select s.username,s.sid,s.serial#,s.osuser,p.spid,s.machine,
sql_text,buffer_gets,executions
from v$sqlarea , v$session s,v$process p
where s.sql_address = address
and s.status = 'ACTIVE'
and s.paddr = p.addr
and s.username is not null
order by buffer_gets desc;

List all open cursor
====================

SELECT SID,USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR ORDER BY 1;


Tablespace Usage and freespace
==============================

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 160

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,40) "File Name",
Round(df.bytes/1024/1024,2) "Size (M)",
Round(e.used_bytes/1024/1024,2) "Used (M)",
Round(f.free_bytes/1024/1024,2) "Free (M)"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
Sum(Decode(bytes,NULL,0,bytes)) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name;


Undo Information
================

column "UserName" format a8
column "DB Sid" format 999999
column "Unix Pid" format 99999999
column "Trnx_start_time" format a19
column "Current Time" format a19
column "Elapsed(mins)" format 999999999.99
column "Undo Name" format a09
column "Used Undo Blks" format a13
column "Used Undo Size(Kb)" format a17
column "Logical I/O(Blks)" format 99999999999999999
column "Logical I/O(Kb)" format 999999999999999
column "Physical I/O(Blks)" format 999999999999999999
column "Physical I/O(Kb)" format 999999999999999999

SELECT
a.username "UserName"
, a.sid "DB Sid"
, e.spid "Unix Pid"
, TO_CHAR(TO_DATE(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time"
, TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time"
, ROUND(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)"
, c.segment_name "Undo Name"
, TO_CHAR(b.used_ublk*d.value/1024) "Used Undo Size(Kb)"
, TO_CHAR(b.used_ublk) "Used Undo Blks"
, b.log_io "Logical I/O(Blks)"
, b.log_io*d.value/1024 "Logical I/O(Kb)"
, b.phy_io "Physical I/O(Blks)"
, b.phy_io*d.value/1024 "Physical I/O(Kb)"
, a.program
FROM
v$session a
, v$transaction b
, dba_rollback_segs c
, v$parameter d
, v$process e
WHERE
b.ses_addr = a.saddr
AND b.xidusn = c.segment_id
AND d.name = 'db_block_size'
AND e.ADDR = a.PADDR
ORDER BY 4
/

select e.sid,e.serial#, substr(a.os_user_name,1,8) "OS User"
, substr(a.oracle_username,1,8) "DB User"
, substr(b.owner,1,8) "Schema"
, substr(b.object_name,1,20) "Object Name"
, substr(b.object_type,1,10) "Type"
, substr(c.segment_name,1,15) "RBS"
, substr(d.used_urec,1,12) "# of Records"
,e.program
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
/

select e.sid
, oracle_username
, b.owner
, b.object_name
, b.object_type
, segment_name
, d.used_urec
, sysdate
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
/

TEMP Tablespace Usage
=====================

SELECT b.sid,b.serial#,a.sql_text
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid in (select se.sid
from v$sort_usage so,v$session se
where so.session_addr = se.saddr
and se.USERNAME not in ('SYSTEM')
and so.blocks*8/1024 >100)
ORDER BY b.sid,a.piece;

Active session
==============

select s.sid,s.serial#,p.spid,s.username,s.osuser,s.machine, sql_text,v$sqlarea.hash_value,
logon_time,last_call_et,sysdate
from v$sqlarea , v$session s,v$process p
where s.sql_address = address
and s.status = 'ACTIVE'
and s.paddr = p.addr
and s.username is not null
and last_call_et > 0
order by last_call_et
/

select s.username,s.sid,s.serial#,s.osuser,p.spid,s.machine, sql_text,buffer_gets,executions,buffer_gets/executions "gets_per_exec" ,v$sqlarea.hash_value
from v$sqlarea , v$session s,v$process p
where s.sql_address = address
and s.status = 'ACTIVE'
and s.paddr = p.addr
and s.username is not null
and executions > 0
order by buffer_gets desc
/



Locks and Blocking information
==============================

SET LINESIZE 145
SET PAGESIZE 66

COLUMN owner FORMAT a5 HEADING 'Owner'
COLUMN object_type FORMAT a10 HEADING 'Type'
COLUMN object_name FORMAT a25 HEADING 'Name'
COLUMN locked_mode FORMAT a9 HEADING 'Locked Mode'
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN username FORMAT a15 HEADING 'Database User'
COLUMN osuser FORMAT a10 HEADING 'O/S User'
COLUMN logon_time HEADING 'Login Time'

SELECT
SUBSTR(b.owner, 1, 8) owner
, b.object_type object_type
, SUBSTR(b.object_name, 1, 18) object_name
, DECODE(a.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S'
, 3, 'Row-X'
, 4, 'Share'
, 5, 'S/Row-X'
, 6, 'Exclusive') locked_mode
, a.session_id sid
,a.oracle_username username
, a.os_user_name osuser
, TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time
FROM
v$locked_object a
, dba_objects b
, v$session c
WHERE
a.object_id = b.object_id
AND a.session_id = c.sid
ORDER BY
b.owner
, b.object_type
, b.object_name
/

col USERNAME for a20

select
s.sid,s.serial#,s.username, s.status,
s.osuser,o.object_name, q.sql_text
from v$session s, v$sqlarea q,v$locked_object l,dba_objects o
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and l.object_id=o.object_id
and l.session_id=s.sid
and s.username = 'SCOTT';

select
s.sid,s.serial#,s.username, s.status,
s.osuser,o.object_name
from v$session s,v$locked_object l,dba_objects o
where
l.object_id=o.object_id
and l.session_id=s.sid
and s.username = 'SCOTT';

Few scripts to identify Blocking locks.
=======================================

$ORACLE_HOME/rdbms/admin/utllockt.sql

The above script provided by oracle will give you a "tree" structure of
which sessions are holding locks that are affecting other users.


select * from v$lock ;

To identify the blocking/blocked sessions.
==========================================
select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

or

Same in a readable format.
============================
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

To Identify the locked object and row
======================================
select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do where sid=122 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;


Identify deadlock
=================

select sid,DECODE(block,0,'NO','YES') BLOCKER,
DECODE(request,0,'NO','YES') WAITER
from v$lock
Where request > 0 or block > 0
order by block desc;


Session Wait
============

col sid form 9999
col osuser form a14
col username form a15
col wait form 9999999
col event form a30
select w.sid,s.username,s.osuser,w.seconds_in_wait wait,
w.event
from v$session_wait w,v$session s
where lower(w.event) not in (
'null event',
'client message',
'pmon timer',
'rdbms ipc message',
'rdbms ipc reply',
'smon timer',
lower('WMON goes to sleep'),
'virtual circuit status',
'dispatcher timer',
lower('SQL*Net message from client'),
'parallel query dequeue wait',
lower('Parallel Query Idle Wait - Slaves'),
lower('KXFX: Execution Message Dequeue - Slave'),
'slave wait')
and s.sid = w.sid
order by wait;


MTS
===

select paddr,type,queued,wait,totalq, decode(totalq,0,0,(wait/totalq)) "AVG WAIT" from v$queue
/

select name "NAME", substr(network,1,15) "PROTOCOL",status "STATUS", (busy/(busy + idle)) * 100 "% TIME BUSY" from v$dispatcher
/

select name, status, requests, (busy/(busy+idle)) * 100 "% TIME BUSY" from v$shared_server
/



Performance tuninng
===================

#REM#Buffer cache hit ratio

/***********************************************
This script is used to calculate hit ratio of data buffer.
If the hit ratio is less than 80%-90%, increase the db_buffer_buffers by using
alter system set db_cache_size=”bigger size” scope=both;

************************************************/

column xn1 format a50
column xn2 format a50
column xn3 format a50
column xv1 new_value xxv1 noprint
column xv2 new_value xxv2 noprint
column xv3 new_value xxv3 noprint
column d1 format a50
column d2 format a50

prompt HIT RATIO:
prompt
prompt Values Hit Ratio is calculated against:
prompt

select lpad(name,20,' ')||' = '||value xn1, value xv1
from v$sysstat
where name = 'db block gets'
/

select lpad(name,20,' ')||' = '||value xn2, value xv2
from v$sysstat
where name = 'consistent gets'
/

select lpad(name,20,' ')||' = '||value xn3, value xv3
from v$sysstat b
where name = 'physical reads'
/

set pages 60

select 'Logical reads = db block gets + consistent gets ',
lpad ('Logical Reads = ',24,' ')||to_char(&xxv1+&xxv2) d1
from dual
/

select 'Hit Ratio = (logical reads - physical reads) / logical reads',
lpad('Hit Ratio = ',24,' ')||
round( (((&xxv2+&xxv1) - &xxv3) / (&xxv2+&xxv1))*100,2 )||'%' d2
from dual
/


column "Consistent Gets" format 9,999,999,999,999
column "DB Block Gets" format 9,999,999,999,999
column "Hit Ratio" format 999.99

SELECT (1 - (phys.value / (db.value + cons.value))) * 100
FROM v$sysstat phys,
v$sysstat db,
v$sysstat cons
WHERE phys.name = 'physical reads'
AND db.name = 'db block gets'
AND cons.name = 'consistent gets';

set serveroutput on;
variable g char(10);
variable d number;
variable c number;
variable r number;
begin
select substr(global_name,1,10) into :g
from global_name;
select value into :c
from v$sysstat
where name = 'consistent gets';
select value into :d
from v$sysstat
where name = 'db block gets';
select to_number(substr((1- value/( :c + :d)),1,5))*100 into :r
from v$sysstat
where name in ( 'physical reads');
dbms_output.put_line('Buffer cache hit ratio for '||:g||' is '||:r||'%');
end;
/


#REM#Dict. Cache Hit Ratio

SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
FROM v$rowcache;

#REM#Library Cache Hit Ratio

SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 from v$librarycache;


#REM#Latch Hit Ratio

SELECT (1 - (Sum(misses) / Sum(gets))) * 100 FROM v$latch;

#REM# Sort Ratio

select (disk.value/mem.value) * 100 from v$sysstat disk, v$sysstat mem where disk.name='sorts(disk)'
and mem.name='sorts(memory)';

#REM#Rollback segment Waits

select (sum(waits)/sum(gets)) * 100 from v$rollstat;

#REM#Shared Pool Size (Execution Misses)

select sum(pins) "Executions" , sum(reloads) "Cache Misses Executing", (sum(reloads)/sum(pins) * 100) "% Ratio"
from v$librarycache;

#REM#Shared Pool Size (Dictionary Gets)

select sum(gets) "Data Dictonary Gets", sum(getmisses) "Get Misses" , 100*(sum(getmisses)/sum(gets)) "% Ratio"
from v$rowcache;


#REM#If % Ratio is above 1%, increase SHARED_POOL_SIZE

select sum(pins) "Executions",
sum(reloads) "Cache Misses Executing",
(sum(reloads)/sum(pins)*100) "% Ratio"
from v$librarycache

#REM#If % Ratio is above 12%, increase SHARED_POOL_SIZE


select sum(gets) "Data Dictionary Gets",
sum(getmisses) "Get Misses",
100*(sum(getmisses)/sum(gets)) "% Ratio"
from v$rowcache
/

#REM#If the Value is not near 0, increase LOG_BUFFER

select substr(name,1,25) Name,
substr(value,1,15) "VALUE (Near 0?)"
from v$sysstat
where name = 'redo log space requests';


#REM# Physical read and write (IO)

SET PAGESIZE 1000

SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

#REM# Session IO

SELECT * FROM v$sess_io WHERE SID=117;


#REM#Pls substitute the value for no of top SQL's consuming resources

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
PROMPT

DECLARE

CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC;

BEGIN

Dbms_Output.Enable(1000000);

Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') ||
Lpad('Reads/Execution',16,' ') ||
Lpad('Buffer Gets',12,' ') ||
Lpad('Disk Reads',12,' ') ||
Lpad('Executions',12,' ') ||
Lpad('Sorts',12,' ') ||
Lpad('Address',10,' '));
Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' ||
Lpad('-',15,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',11,'-') || ' ' ||
Lpad('-',9,'-'));

<< top_sql >>
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') ||
Lpad(cur_rec.reads_per_execution,16,' ') ||
Lpad(cur_rec.buffer_gets,12,' ') ||
Lpad(cur_rec.disk_reads,12,' ') ||
Lpad(cur_rec.executions,12,' ') ||
Lpad(cur_rec.sorts,12,' ') ||
Lpad(cur_rec.address,10,' '));

IF c_sql%ROWCOUNT = &&1 THEN
EXIT top_sql;
END IF;
END LOOP;

END;
/

OVERALL CPU USAGE
=================

select distinct a.name, a.value "Total CPU",
b.value "Parse CPU",
c.value "Recursive CPU",
(a.value - b.value - c.value) "Other"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.name = 'CPU used by this session'
and b.name = 'parse CPU time'
and c.name = 'recursive CPU';


SID which has taken maximum CPU
===============================

col username form a15
set pages 10000
col name form a30
select s.username,se.sid,st.name,se.value/(60*100) "In Minutes"
from v$sesstat se,v$statname st,v$session s
where se.STATISTIC#=st.STATISTIC#
and st.name like '%CPU%'
and se.value > 12000
and s.sid=se.sid
order by se.value/(60*100);


CPU USAGE PER SESSION
====================

select distinct a.name, a.value "Total CPU",
b.value "Parse CPU",
c.value "Recursive CPU",
(a.value - b.value - c.value) "Other CPU"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic#=12
and b.statistic#=150
and c.statistic#=8;

Memory used by non applicaton user
==================================

SELECT A.username, COUNT(*), SUM (B.sharable_mem) sharable_mem,
SUM (B.persistent_mem) persistent_mem,
SUM (B.runtime_mem) runtime_mem,
SUM (B.sharable_mem + B.persistent_mem + B.runtime_mem) total_mem
FROM dba_users A, v$sql B
WHERE A.username IN ('DBSNMP', 'SYS', 'SYSTEM', 'SYSMAN')
AND B.parsing_user_id = A.user_id
GROUP BY A.username;

Latch Contention
================

col name form A25
col gets form 999,999,999
col misses form 999.99
col spins form 999.99
col igets form 999,999,999
col imisses form 999.99

select name,gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets
,immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets
/


Analyze
=======

Validate structure

Select 'Analyze '||object_type||' '||object_name||' validate structure;'
from dba_objects
where owner='SYS'
and object_type in ('INDEX','TABLE','CLUSTER');

Gather stats with Histograms
============================

begin
dbms_stats.gather_schema_stats(
ownname => 'HR',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
cascade => true,
degree => 2
);
end;
/

Script to do collect statistics for HR schema at 9 PM
=====================================================

BEGIN
DBMS_SCHEDULER.DROP_JOB (
job_name => 'HR_STATS_REFRESH'
,force => TRUE);
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'HR_STATS_REFRESH'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR'');END;'
,start_date => '09/01/2004 09:00:00.000000'
,repeat_interval => 'FREQ=DAILY'
,enabled => FALSE
,comments => 'Refreshes the HR Schema every night at 9 PM'
);
END;
/

Archiver
========

#REM# show db in archive log mode or not

archive log list


#REM Arciver gap

SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES'
/


Hanganalyze
===========

SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3


Usefull SQL's
=============

#REM# delete duplicate rows from table

delete from dummy where rowid not in (select min(rowid) from dummy group by n1);


#REM Total db size

select sum(bytes)/1024/1024 allocated_space from dba_data_files;
select name from v$database;
select sum(bytes)/1024/1024 from dba_temp_files;

Find the primary key column
===========================

set linesize 160;
set pages 50000;
col table_name for a45;
col column_name for a60;
col constraint_name for a45;
select t.TABLE_NAME,t.COLUMN_NAME,t.CONSTRAINT_NAME,c.CONSTRAINT_TYPE from USER_CONSTRAINTS c,user_cons_columns t where t.CONSTRAINT_NAME=c.CONSTRAINT_NAME and t.TABLE_NAME=c.TABLE_NAME and constraint_type='P' order by t.COLUMN_NAME;

SELECT CON.TABLE_NAME,COL.COLUMN_NAME,CON.CONSTRAINT_TYPE FROM DBA_CONSTRAINTS CON,DBA_CONS_COLUMNS COL
WHERE CON.TABLE_NAME=COL.TABLE_NAME AND CON.CONSTRAINT_NAME=COL.CONSTRAINT_NAME AND CON.OWNER='SCOTT' AND COL.OWNER='SCOTT' AND CON.CONSTRAINT_TYPE IN ('P','U') ORDER BY CON.TABLE_NAME;