How are the sqls performing today? Often, I face this question. First I will try to parse the question into simple terms.
- The sqls – what sqls? On any given day, the top 10 resource intensive sqls. Or may be top 20 resource intensive sqls. You can take any sql from the web and pull the top n resource intenvise sqls. In some applications, there might be a set sqls that are very crucial to day to day activities. Any such set of sql or top sql will be sufficient here to monitor for performance variations.
- Next part of the questions – how are ……. performing today? Compared to what? What ever is available on these sql from the past executions need to be compared with current performance to further investigate as may be necessary.
On a given day, I need to compare the top n sqls performance and compare with their previous/historical executions' performance and take remedial measures, if the performance is slower than their historical run times. The following script takes the top 20 ressounce intensive sql from v$sql and compare
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:
- When the executions value is 0, that sql is not complete within that snapshot interval period.
- There seems to be some difference/overlap in the data between v$sql/dba_hist_sqlstat. As such, some sql were not displayed in currently executed section but present in min & max runs.
- Some sqls were introduced very recently in the db, and their historical data is not displayed.
- This script was tested on Oracle 11.2.0.4 version stand alone database. This script need to be modified for using in RAC and or in other versions of Oracle. Readers' descretion is advised in making suitable changes to the script as may be required to suit your db's version and environments.
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;