Category Archives: Db Performance

PL/SQL check blocking sessions

—Recipie #1 – find blocking sessions with v$session
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;
—Recipie #2 – find blocking sessions using v$lock
SELECT
   l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
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;
/*Recipie #3 – blocking sessions with all available information
The next query prints a few more information, it let’s you quickly see who’s blocking who. Run this query and you can immediately call the colleague who’s locking your table:
*/

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 ;
–Recipie #4 – identifying blocked objects

SELECT sid, id1 FROM v$lock WHERE TYPE=’TM’;

 
use this scripts i hope its it will help u to get blocking locks

set echo off
set line 10000
column blocker format a11;
column blockee format a10;
column sid format 99999;
column blocker_module format a15 trunc;
column blockee_module format a15 trunc;

alter session set optimizer_mode=rule;

select a.inst_id,
(select username from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker,
a.sid,
(select module from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker_module ,
‘ is blocking ‘ “IS BLOCKING”,
b.inst_id,
(select username from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee,
b.sid ,
(select module from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee_module
from gv$lock a, gv$lock b
where
–Modified to take care of issue where sid blocked on difft instances are not showing
— Original Script
/* a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.inst_id = b.inst_id
and a.id2 = b.id2
*/
— Modified script
a.block <>0 and
b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and a.sid <> b.sid
order by 1, 2
/
alter session set optimizer_mode=choose;

 

—————–==============================
—————–==============================

 

 

 

 

 

 
select * from ec_branch_hurdles
where book_size is not null

select count(*)
–delete
from ec_branch_hurdles where report_id>20130101;
commit;

select * from ec_report_date_details order by batch_id desc;

–delete from ec_employee_branches where report_id =20130426;
–delete from ec_et_employees where report_id =20130426 ;
select * from ec_acct_credits where report_id =20130426 ;
select * from ec_employee_hurdles  where report_id =20130426 ;
select * from ec_branch_costs  where report_id =20130426 ;
select * from ec_branch_hurdles  where report_id =20130426 ;

set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

Check free tablespace

select b.tablespace_name, tbs_size SizeMb, to_char(a.free_space/tbs_size * 100,’99.99′) || ‘%’ FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
column owner format A7
column bytes format 99999999999
break ON owner  ON report
compute SUM  OF bytes ON owner report
 
SELECT owner, table_name, NVL(num_rows*avg_row_len,0) bytes
FROM dba_tables
WHERE tablespace_name=’OWB_DATA’  and NVL(num_rows*avg_row_len,0) >10000
ORDER BY NVL(num_rows*avg_row_len,0) desc
/
select distinct(a.TABLESPACE_NAME) TABLESPACE_NAME,NVL(a.AUTOEXTENSIBLE,’NO’) AUTOEXTD,
(a.INCREMENT_BY*(select value from v$parameter where lower(name)=’db_block_size’))/1024/1024 NEXT_SIZE,
a.bytes/1024/1024 SIZE_MB,
nvl(a.MAXBYTES/1024/1024,’0′) MAXSIZE,a.file_id,
REPLACE(
REPLACE(a.file_name,’/etrade/pkgs/linux/intel/oracle’,’${ORACLE_BASE}’)
,’/etrade/pkgs/oracle’,’${ORACLE_BASE}’)
as DATA_FILE_NAME
from dba_data_files a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.tablespace_name like ‘SPOTFIRE_DATA_500′
order by NVL(a.AUTOEXTENSIBLE,’NO’);

for the shrink (which has datafile number listed) :

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

see if this helps:
alter table <tbl name> enable row movement;
alter table <tbl name> shrink space;

Db Performance check

1) Get the Process ID (PID) from TOP command which consume high CPU Usages.

So the query to get the session details (SID) from OS PID (SPID) will be as per

following.

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = (PID) ;

3) Once we get the session ID, base on this information we can get the actual SQL

statement which is causing
HIGH CPU usage on database server.

We can use the following query to get the actual SQL STATEMENT.

SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ;

–# from below query you will findout sid:-

SELECT se.username, ss.sid, ROUND (value/100) “CPU Usage”
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE ‘%CPU used by this session%’
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like ‘%CPU used by this session%’
and
se.SID = ss.SID
and
ss.status=’ACTIVE’
and
ss.username is not null
order by VALUE desc;

select sql_hash_value, count(*) from v$session
where status = ‘ACTIVE’ group by sql_hash_value order by 2 desc;

select sql_text,users_executing from v$sql where hash_value = <give hash value here

which is out put of above query>;