How are the sqls performing today?

    Mar 10, 2018 9: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.

    1.  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.
    2. 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:

    1.  When the executions value is 0, that sql is not complete within that snapshot interval period.
    2. 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. 
    3. Some sqls were introduced very recently in the db, and their historical data is not displayed.
    4. 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://oracledoug.com/serendipity/index.php?/archives/1506-Real-Time-SQL-Monitoring-in-SQL-Developer.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

    JP Vijaykumar

    Written by JP Vijaykumar