Tuesday, 10 June 2014
Sequence to analysis database level performance
Sequence to analysis database level performance
1st we need to check current wait event running on the database,
So that we can get clear idea of suspicious event or any bug repated event that hand or slows the database or process.
Once you identify the event,we need to find the sql of that event.To find the sql we need to know the SID or sql_hash_value
or we can diretly query v$sql. Below is the flow where you can do the intial diag of performance issue in database.
Find the wait event:
==================
select event ,count(*) from v$session_wait where event not like 'Strea%' group by event order by 2
/
Find the SID and Hashvalue of the wait events by entering the wait event:
==========================================================================
select sid,sql_hash_value,action,module from v$session where sid in (select sid from v$session_wait where event like '%&evnt%') order by 2
/
Get the SQL of the wait event by entring the hash value:
=======================================================
select sql_text from v$sqltext_with_newlines where HASH_VALUE=&HASH_VALUE order by piece
/
SQL_TEXT from events:
========================
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select sid from v$session_wait where event like '&event'))
/
Get SID from PID:
=================
select sid from v$session ,v$process where addr=paddr and spid=&spid
/
SQL-text running with SID:
=======================
SELECT s.sid, s.status, q.sql_text FROM v$session s, v$sqltext q WHERE s.sql_hash_value = q.hash_value AND s.sql_address = q.address
AND s.sid = &sid order by q.piece
/
SQL_test for PID:
==================
set lines 1000
set pages 1000
set timing on
column sql_test format a980 tru
select 'PROC '||p.spid||' - '||sa.sql_text sql_text from v$process p, v$session s, v$sqlarea sa where sa.address = s.sql_address and s.paddr = p.addr and sa.users_executing > 0
and p.spid = '&pid';
SQL-text of ALL running sessions:
================================
select distinct spid, s.sid,s.serial#,to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",logon_time,
osuser,s.program,schemaname,sql_text from v$session s,v$process p,v$sql t where s.paddr=p.addr and t.hash_value(+)=s.sql_hash_value and s.type !='BACKGROUND'
/
find "db file sequential read " runing or hangs: p1 and p2 changes then its running
===============================================
select event,p1,p2,p3 from v$session_wait where sid=&entersid
/
Labels:DATABASE,PERFORMANCE
PERFORMANCE
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment