Mar 10, 2018 8:38:00 PM by JP Vijaykumar
How are the sqls performing today? Often, I face this question. First I will try to parse the question into simple terms.
their current performance with their historica performance.
This script gives, current run times, historical min & max run times.
It is beyond the scope of this document to narrate the methods of tuning various sqls, to fix the slow performaning sqls.
It only gives a somewhat rough ideas, as to how the regular sql in the database are performing. Any degredation/deviation in their elapsed times, does the situation need further action/tuning of these sqls.
Some of the exceptions:
Happy scripting.
References:
https://stackoverflow.com/questions/22328550/dba-hist-sqlstat-non-zero-elapsed-time-while-execution-number-equals-zero
http://snisaac.blogspot.com/2014/09/a-simple-monitor-for-long-running-sqls.html
https://www.databasejournal.com/features/oracle/real-time-sql-monitoring-in-oracle-database-11g.html
http://kerryosborne.oracle-guy.com/2008/10/oracle-11g-real-time-sql-monitoring/
https://vishaldesai.wordpress.com/2012/12/07/monitoring-long-running-query-using-gvsql_monitor/
column rows_prsd_per_exec format 999,999,999,999 heading "ROWS|PRSC PER EXEC"
column cpu_time_per_exec_secs format 999,999,999.99 heading "CPU TIME|PER EXEC SECS"
column elap_time_per_exec_secs format 999,999,999.99 heading "ELAP TIME|PER EXEC SECS"
column lio_per_exec formt 999,999,999,999
column phyio_per_exec format 999,999,999,999
column plan_hash_value format a25
column sql_profile format a30
set pagesize 100 linesize 200 timing on
with q as ( -----------FETCHING RESOURCE INTENSIVE SQL QUERIES
select sql_id from(
select q.sql_id,sum(disk_reads),sum(buffer_gets),sum(cpu_time),sum(elapsed_time)
from v$sqlarea q, v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null
group by q.sql_id
order by 5 desc,4 desc,2 desc,3 desc)
where rownum<21 -----------FETCHING RESOURCE INTENSIVE SQL QUERIES
--https://samadhandba.wordpress.com/2011/03/15/finding-currently-running-sql/
),
t as (
select snap_id,instance_number inst_id,sql_id,plan_hash_value,sql_profile,sum(executions_delta) sum_executions,
round(sum(rows_processed_delta)/(sum(executions_delta) + .0001),2) avg_rows_prsd_per_exec,
round(sum(disk_reads_delta)/(sum(executions_delta) + .0001),2) avg_phyio_per_exec,
round(sum(buffer_gets_delta)/(sum(executions_delta) + .0001),2) avg_lio_per_exec,
round(sum(cpu_time_delta)/1000000/(case when sum(executions_delta) =0 then 1 else sum(executions_delta) end),2) avg_cpu_time_per_exec_secs,
round(sum(elapsed_time_delta)/1000000/(case when sum(executions_delta) =0 then 1 else sum(executions_delta) end),2) avg_elap_time_per_exec_secs
from dba_hist_sqlstat
where sql_id in (
select sql_id from q
)
group by snap_id,instance_number,sql_id,plan_hash_value, sql_profile
order by snap_id)
select sql_id,lpad(plan_hash_value,15)||' MIN ' plan_hash_value, min(sum_executions) executions,round(min(avg_rows_prsd_per_exec),2)
rows_prsd_per_exec,
round(min(avg_phyio_per_exec),2) phyio_per_exec,
round(min(avg_lio_per_exec),2) lio_per_exec,
round(min(avg_cpu_time_per_exec_secs),2) cpu_time_per_exec_secs,
round(min(avg_elap_time_per_exec_secs),2) elap_time_per_exec_secs,sql_profile
from t group by sql_id,plan_hash_value,sql_profile
union all
select sql_id,lpad(plan_hash_value,15)||' MAX ' plan_hash_value,max(sum_executions) executions,round(max(avg_rows_prsd_per_exec),2)
rows_prsd_per_exec,
round(max(avg_phyio_per_exec),2) phyio_per_exec,
round(max(avg_lio_per_exec),2) lio_per_exec,
round(max(avg_cpu_time_per_exec_secs),2) cpu_time_per_exec_secs,
round(max(avg_elap_time_per_exec_secs),2) elap_time_per_exec_secs,sql_profile
from t group by sql_id,plan_hash_value,sql_profile
union all
SELECT sql_id,lpad(plan_hash_value,15)||'-CURRENT' plan_hash_value,sum(executions) executions,
round(sum(rows_processed)/(sum(executions) + .0001),2) rows_prsd_per_exec,
round(sum(disk_reads)/(sum(executions) + .0001),2) phyio_per_exec,
round(sum(buffer_gets)/(sum(executions) + .0001),2) lio_per_exec,
round(sum(cpu_time)/1000000/(case when sum(executions) =0 then 1 else sum(executions) end),2) cpu_time_per_exec_secs,
round(SUM(elapsed_time)/1000000/(case when sum(executions) =0 then 1 else sum(executions) end),2) elap_time_per_exec_secs,sql_profile
FROM gv$sql where sql_id in (
select sql_id from q
)
group by sql_id,plan_hash_value,sql_profile
order by 1,2;
Tags: Oracle SQL Development
Written by JP Vijaykumar
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.