Site icon IT Tutorial

Performance Tuning and SQL Monitoring Scripts | Oracle DBA Scripts All in One -4

Mehmet Salih Deveci

Hi,

I will share all Oracle DBA Scripts ( Oracle RAC, Dataguard, Performance Tuning, Monitoring and etc.. ) in this tutorial series.

 

 

SQL Performance Tuning Scripts

Oracle DBA ( Database Administrator ) needs useful scripts to monitor, analyze and check Oracle database for routine database operations and monitoring.

 

 

Before Oracle DBA Scripts, If you don’t know Oracle database and want to learn, click following link to start learning Oracle database with this tutorial.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 

 

 

SQL Monitoring Scripts in Oracle

Oracle DBA should monitor database everytime and if there are many sqls which is running long execution time then it should be reported to the developer and developer and dba should examine these sqls.

You can find TOP Elapsed time SQL in certain hours with below script.

 

SELECT st.sql_text, sub.sql_id, sub.ELAPSED_TIME PER_EXEC_ELAPSED_TIME_MINUTES
FROM DBA_HIST_SQLTEXT st,
( SELECT t.sql_id,
ROUND (
SUM (t.elapsed_time_delta / 60000000)
/ SUM (t.executions_delta))
ELAPSED_TIME
FROM dba_hist_sqlstat t, dba_hist_snapshot s, DBA_HIST_SQLTEXT st
WHERE t.snap_id = s.snap_id
AND t.dbid = s.dbid
AND t.instance_number = s.instance_number
AND t.executions_delta > 0
AND s.BEGIN_INTERVAL_TIME >
TO_DATE ('11/15/2018 13:00:00',
'mm/dd/yyyy hh24:mi:ss')
AND END_INTERVAL_TIME <
TO_DATE ('11/15/2018 16:01:00',
'mm/dd/yyyy hh24:mi:ss')
GROUP BY t.sql_id
ORDER BY 2 DESC) sub
WHERE sub.sql_id = st.sql_id
ORDER BY 3 DESC;

TOP CPU SQL Statements

You can find TOP CPU SQL for last 24 hours with following script.

 

select * from (
select ss.sql_text,
    a.SQL_ID, 
    sum(CPU_TIME_DELTA), 
    sum(DISK_READS_DELTA),
    count(*)
from 
    DBA_HIST_SQLSTAT a, dba_hist_snapshot s,v$sql ss
where
 s.snap_id = a.snap_id and a.sql_id=ss.sql_id
 and s.begin_interval_time > sysdate -1
    group by 
    ss.sql_text,a.SQL_ID
order by 
    sum(CPU_TIME_DELTA) desc)
where rownum<20;

TOP IO SQL

You can find TOP IO SQL for last 24 hours with following script.

 

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum<10;

You can find TOP 10 SQL for last 1 hour with following script.

 

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  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.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum <11;

 

 

 

You can check Top Oracle database wait events in Cache which is v$ queries with below script.

select  wait_class,
       sum(total_waits), sum(time_waited)
    from gv$session_wait_class
    where wait_class !='Idle'
    group by wait_class
    order by 3 desc;

You can check Top Oracle database wait events from Active session history which is v$active_session_history queries with below script.

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from gv$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum <= 10;

 

 

You can check Top Oracle database wait events at specific intervals. You should type date and SNAP_ID

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='20-FEB-2019'
and EXTRACT(HOUR FROM begin_interval_time) between 8 and 10;

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between 34411 and 34431
group by active_session_history.event
order by 2 desc)
where rownum<10;

 

 

 

To find any SQL’s SQL_ID and other SQL informations in the cache, use following SQL. Following query will find if related SQL exists in the cache, if not exists it will not find.

select * from gv$sql where sql_text like '%DEVECI%';

 

To search any SQL historical in Oracle database you may use following SQL. Just change begin Interval time column and SQL_TEXT column.

 

SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA, 
STAT.SNAP_ID, SS.END_INTERVAL_TIME, EXECUTIONS_DELTA FROM DBA_HIST_SQLSTAT STAT, 
DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND
STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER 
AND STAT.SNAP_ID = SS.SNAP_ID AND
SS.BEGIN_INTERVAL_TIME >= sysdate-31 AND
UPPER(SQL_TEXT) LIKE '%DEVECI%' ORDER BY ELAPSED_TIME_DELTA DESC;

 

You can find average Active session of database with following script.

 

SELECT 'Load',
CASE
WHEN ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60) = 0
THEN
0
ELSE
ROUND (
( COUNT (ash.sample_id)
/ ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60)),
2)
END
AS Average_Active_Session
FROM (SELECT MIN (sample_time) sample_time
FROM v$active_session_history ash
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) start_time,
(SELECT MAX (sample_time) sample_time
FROM gv$active_session_history
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) end_time,
gv$active_session_history ash
WHERE ash.sample_time BETWEEN start_time.sample_time
AND end_time.sample_time
GROUP BY end_time.sample_time, start_time.sample_time;

To list all User Sessions not Background, use following scripts. This script will list you just only User type sessions and their detais.

select * FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS';

 

You can list how many Active and Inactive User sessions are in the Oracle database with following script.

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS';

You can list only Active User sessions without sys user with following script

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE';

 

You can list only Inactive User sessions without sys user with following script

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='INACTIVE';

You can list all user sessions which are ACTIVE state more than 600 Second with following script.

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE' and last_call_et > 600



 


If you don’t use bind variables in the SQL queries then Oracle will know similar sqls differently like following.


select * from customer where id=63;

select * from customer where id=34;


Above queries are almost same, just id variable is different. But Oracle optimizer will evaluate these SQLs like different SQL.


If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle.


 


variable SYS_B_0 number;
exec :SYS_B_0:= 63

select * from customer where id= :SYS_B_0;






You can find out all queries which are not using bind variables with following script. You can see more queries with changing row nums of script.


 


Select * 
from (
With subs as
(SELECT /*+ materialize */
m.sql_id, k.*, m.SQL_TEXT, m.SQL_FULLTEXT
FROM (SELECT inst_id,
parsing_schema_name AS user_name,
module,
plan_hash_value,
COUNT(0) copies,
SUM(executions) executions,
SUM(round(sharable_mem / (1024 * 1024), 2)) sharable_mem_mb
FROM gv$sqlarea
WHERE executions < 5
AND kept_versions = 0
GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
HAVING COUNT(0) > 10
ORDER BY COUNT(0) DESC) k
LEFT JOIN gv$sqlarea m
ON k.plan_hash_value = m.plan_hash_value
WHERE k.plan_hash_value > 0)
select distinct ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name,
sum(ki.copies) copies,
sum(ki.executions) executions,
sum(ki.sharable_mem_mb) sharable_mem_mb
from (select sql_id,
program_id,
program_line#,
action,
module,
service,
parsing_schema_name,
round(buffer_gets / decode(executions, 0, 1, executions)) buffer_per_Exec,
row_number() over(partition by sql_id order by program_id desc, program_line#) sira,
decode(program_id,
0,
null,
owner || '.' || object_name || '(' || program_line# || ')') plsql_procedure
from gv$sql a, dba_objects b
where a.program_id = b.object_id(+)) t,
subs ki
where ki.sql_id = t.sql_id
and t.sira = 1
group by ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name
order by sum(ki.executions) desc
)
where rownum < 51;







 


 


You will find object detail of queries which is not using bind variables.


 


With subs as
(SELECT /*+ materialize */
   m.sql_id, k.*, m.SQL_TEXT, m.SQL_FULLTEXT
    FROM (SELECT inst_id,
                 parsing_schema_name AS user_name,
                 module,
                 plan_hash_value,
                 COUNT(0) copies,
                 SUM(executions) executions,
                 SUM(round(sharable_mem / (1024 * 1024), 2)) sharable_mem_mb
            FROM gv$sqlarea
           WHERE executions < 5
             AND kept_versions = 0
           GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
          HAVING COUNT(0) > 10
           ORDER BY COUNT(0) DESC) k
    LEFT JOIN gv$sqlarea m
      ON k.plan_hash_value = m.plan_hash_value
   WHERE k.plan_hash_value > 0)
select *
  from (select sql_id,
               program_id,
               program_line#,
               action,
               module,
               service,
               parsing_schema_name,
               round(buffer_gets / decode(executions, 0, 1, executions)) buffer_per_Exec,
               row_number() over(partition by sql_id order by program_id desc, program_line#) lines,
               decode(program_id,
                      0,
                      null,
                      owner || '.' || object_name || '(' || program_line# || ')') plsql_procedure
          from gv$sql a, dba_objects b
         where a.program_id = b.object_id(+)) t,
       subs ki
where ki.sql_id = t.sql_id
   and lines = 1;







You can access the third Oracle DBA scripts related to Blocking Sessions and Lock Kill Scripts with following link.



Find Blocking Sessions and Kill Locked Session Scripts & Locked Objects | Oracle DBA Scripts All in One -3


 


 


Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.



Performance Tuning and SQL Tuning Tutorial in the Oracle Database


 

	


	






		

			
				Exit mobile version