Oracle DBA scripts - Performance related

1. identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
2. identify heavy SQL (Get the SQL with heavy DISK_READS)
3. Last 30 minutes result those resources that are in high demand on your system.
4. What user is waiting the most?
5. What SQL is currently using the most resources?
6. What object is currently causing the highest resource waits?
7. Wait related.
8. From a given Time range.
9. How many Times a query executed?


1.Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)

select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions,0,buffer_gets,buffer_gets/executions)
> (select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions))
from v$sqlarea) and parsing_user_id !=3D;

2.Identify heavy SQL (Get the SQL with heavy DISK_READS)

select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3D;

3. Last 30 minutes result those resources that are in high demand on your system.

select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2;

4.What user is waiting the most?

select sesion.sid,

sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;

5. What SQL is currently using the most resources?

select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4 desc;

6. What object is currently causing the highest resource waits?
column OBJECT_NAME format a30
column EVENT format a30

select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc;

7.Wait related..

SELECT distinct wait_class#, wait_class
FROM v$event_name ORDER BY wait_class#;

SELECT wait_class_id, wait_class#, wait_class, total_waits, time_waited
FROM v$system_wait_class
Order by time_waited desc;


8. Top SQLs Elaps time and CPU time in a given time range..

SELECT SQL_TEXT,X.CPU_TIME
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('10/16/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('10/18/2008','MM/DD/YYYY')) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC; More.. --X.ELAPSED_TIME/1000000 => From Micro second to second
--X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA => How many times the sql ran

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000/X.EXECUTIONS_DELTA,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000/X.EXECUTIONS_DELTA,3) CPU_TIME_SEC
, EXECUTIONS_DELTA
,X.ELAPSED_TIME
,X.CPU_TIME
,X.EXECUTIONS_DELTA
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME >= TO_DATE('17-feb-2009 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('17-feb-2009 16:00', 'dd-mon-yyyy hh24:mi')) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY ELAPSED_TIME_SEC DESC;

For specific owner..

SELECT SQL_TEXT
,ROUND(X.ELAPSED_TIME/1000000,3) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,3) CPU_TIME_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME >= TO_DATE('01-feb-2009 08:00', 'dd-mon-yyyy hh24:mi')
AND END_INTERVAL_TIME <= TO_DATE('17-feb-2009 16:00', 'dd-mon-yyyy hh24:mi')) AND DHSS.parsing_schema_name='PROD8' GROUP BY DHSS.SQL_ID ) X WHERE X.SQL_ID = DHST.SQL_ID ORDER BY ELAPSED_TIME_SEC DESC;

--Latest

SELECT dbms_lob.substr(SQL_TEXT,4000,1) as SQL
,ROUND(X.ELAPSED_TIME/1000000,2) "ELAPSED TIME (sec)" --From Micro second to second
,ROUND(X.CPU_TIME /1000000,2) "CPU TIME (sec)"
,X.EXECUTIONS_DELTA as "TOTAL NO OF EXECUTIONS"
,ROUND(((X.ELAPSED_TIME/1000000) /X.EXECUTIONS_DELTA),2) as "Execution Time Per Query (sec)"
FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DHSS.SQL_ID SQL_ID, SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
, SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME
, SUM (DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA --DHSS.EXECUTIONS_DELTA = No of queries execution (per hour)
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN
(
SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > TO_DATE('20-dec-10 23:59', 'dd-mon-yy hh24:mi')
AND END_INTERVAL_TIME < TO_DATE('28-dec-10 00:01', 'dd-mon-yy hh24:mi')
)
AND DHSS.parsing_schema_name='PROD7' GROUP BY DHSS.SQL_ID
) X
WHERE X.SQL_ID = DHST.SQL_ID
ORDER BY "ELAPSED TIME (sec)" DESC;


9. How many Times a query executed?

Per Hour sql execution growth:
Begin_interval_time means snapshot interval time.
As we fixed this 60 min so here you can see the execution growth (per hour).
The execution delta will show you the no of queries execution (per hour).

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL
from dba_hist_sqlstat sql, dba_hist_snapshot s
where sql_id='b4q5gbua0dzy3'
and s.snap_id = SQL.snap_id
and s.begin_interval_time> TO_date('18-oct-2008 14:00', 'dd-mon-yyyy hh24:mi')
and s.begin_interval_time< TO_date('21-oct-2008 18:30', 'dd-mon-yyyy hh24:mi') order by s.begin_interval_time;

Oracle DBA scripts - Bind variable related

Bind variable

1. Find the value of Bind variable
select NAME,POSITION,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where sql_id='d9kf91muzy2wq';

v$sql_bind_capture Details: http://youngcow.net/doc/oracle10g/server.102/b14237/dynviews_2114.htm

To get data from history:
DBA_HIST_SQLBIND
DBA_HIST_SQL_BIND_METADATA

Oracle DBA scripts - Session related

1. Find my ORACLE SID
echo $ORACLE_SID
or,    less .profile 


2. Find my SID

SELECT sys_context('USERENV','SID') from dual;

3. Find others SID

SELECT a.sid,a.serial#,b.spid,b.pid,a.username,a.osuser,a.machine
FROM v$session a,v$process b
WHERE a.username IS NOT NULL
AND a.paddr=b.addr
Order by a.sid,a.username;

4. Find connected session,login_time in a given time

SELECT a.sid,a.serial#,a.username,a.osuser,a.machine,a.service_name
,to_char(a.logon_time, 'hh24:mi dd/mm/yy') login_time
FROM v$session a,v$process b
WHERE a.username IS NOT NULL
AND a.paddr=b.addr AND a.type='USER'
--AND a.logon_time> = sysdate- 1/48 --last 30 min
AND a.logon_time > to_date ('2008-10-18 15:00:00','YYYY-MM-DD HH24:MI:SS')
AND a.logon_time <> to_date ('2008-10-18 15:00:00','YYYY-MM-DD HH24:MI:SS')
Order by a.sid,a.username;