Skip to main content

How to get SQL Standard Deviation information of SQL ID in Oracle 12c

 How to get SQL Standard Deviation information of SQL ID in Oracle Database.

Please user the below SQL to get the information of SQL Standard Deviation information. Don't forget to remove comment lines before running and formatting the sql.


WITH sqlstat

AS

(

SELECT TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) snap_time  -- Change to MI, HH , or DD for a individual sql statement to checking performance over time

      ,s.instance_number inst_num

      --,MIN(h.begin_interval_time) min_start_date

      --,MAX(h.begin_interval_time) max_end_date

      --,MAX(s.snap_id) max_snap_id

      ,s.sql_id

      ,s.plan_hash_value

      ,parsing_schema_name schema

      ,module

      ,ROUND((SUM(s.elapsed_time_delta)/1000000)/60,2) elapsed_time_minutes

      ,ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_time_seconds

      ,SUM(s.executions_delta) executions_total

      ,SUM(s.rows_processed_delta) rows_processed_total

      ,ROUND(SUM(s.ELAPSED_TIME_DELTA)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/10000002) avg_elap_per_stmt_seconds

      ,ROUND(SUM(s.ELAPSED_TIME_DELTA)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_elap_stmt_ms

      --/****

      ,ROUND(SUM(apwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2)  avg_apwait_ms

      ,ROUND(SUM(clwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2)  avg_cl_wait_ms

      ,ROUND(SUM(iowait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2)  avg_io_wait_ms

      ,ROUND(SUM(ccwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2)  avg_cc_wait_ms

      ,ROUND(SUM(cpu_time_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_cpu_per_ms

      --,ROUND(SUM(s.cpu_time_delta)/1000000,2) cpu_time_total_seconds

      --,SUM(s.px_servers_execs_delta) px_servers_execs_total

      ,ROUND(SUM(s.cpu_time_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2)  avg_cpu_time

      ,ROUND(SUM(s.buffer_gets_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2)  avg_buffer_gets

      ,ROUND(SUM(s.direct_writes_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2)  avg_direct_writes

      ,ROUND(SUM(s.disk_reads_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2)  avg_disk_reads

  FROM dba_hist_sqlstat s

       JOIN dba_hist_snapshot h

         ON s.snap_id = h.snap_id 

        AND s.instance_number = h.instance_number

 WHERE s.elapsed_time_delta > 0  

   AND (h.begin_interval_time > SYSDATE -:NUMBER_OF_DAYS OR :NUMBER_OF_DAYS IS NULL)

   AND (s.module LIKE '%'||:MODULE||'%' OR :MODULE IS NULL)

   AND (h.begin_interval_time >= SYSDATE -:NUMBER_OF_HOURS/24 OR :NUMBER_OF_HOURS IS NULL)

   AND (h.begin_interval_time >= SYSDATE -:NUMBER_OF_HOURS/24 OR :NUMBER_OF_HOURS IS NULL)

   AND (h.begin_interval_time BETWEEN TO_DATE(:START_DATE,:DATE_FORMAT) AND TO_DATE(:END_DATE,:DATE_FORMAT) OR :START_DATE IS NULL OR :END_DATE IS NULL)

   AND parsing_schema_name LIKE '%'||:SCHEMA_NAME_LIKE||'%'

 GROUP BY TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT)  -- Change to HH or DD for a individual sql statement to checking performance over time

        ,s.instance_number

        --,s.snap_id

        ,s.sql_id

        ,s.plan_hash_value 

        ,s.parsing_schema_name

        ,s.module

 ORDER BY 1 DESC, 2

 ), sqlstat_stddev AS

 (

SELECT 

       snap_time

      ,inst_num

      --,min_start_date

      --,max_end_date

      --,max_snap_id

      ,sql_id

      ,plan_hash_value

      ,SCHEMA

      ,module

      ,elapsed_time_minutes

      ,elapsed_time_seconds

      ,executions_total

      ,rows_processed_total

      ,avg_elap_per_stmt_seconds

      ,avg_elap_stmt_ms

      ,ROUND(STDDEV(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) stddev_elap_ms

      ,ROUND(AVG(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) avg_elap_ms

      ,ROUND(MIN(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) min_elap_ms

      ,ROUND(MAX(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) max_elap_ms

      --,avg_elap_per_stmt_ms

      ,avg_apwait_ms

      ,avg_cl_wait_ms

      ,avg_io_wait_ms

      ,avg_cc_wait_ms

      ,avg_cpu_per_ms

      --,cpu_time_total_seconds

      --,px_servers_execs_total

      ,avg_cpu_time

      ,avg_buffer_gets

      ,avg_direct_writes

      ,avg_disk_reads

      ,RANK() OVER (PARTITION BY sql_id ORDER BY avg_elap_stmt_ms, snap_time DESC ) avg_elap_ms_fastest

      ,RANK() OVER (PARTITION BY sql_id ORDER BY avg_elap_stmt_ms DESC,snap_time DESC) avg_elap_ms_slowest

  FROM sqlstat s

 WHERE s.executions_total > :MIN_EXECUTIONS

), sql_stat_stdfinal AS

(

SELECT s.*

      ,(CASE WHEN (avg_elap_ms_slowest = 1 OR avg_elap_ms_fastest=1) AND MIN(plan_hash_value) OVER (PARTITION BY sql_id ) <> MAX(plan_hash_value) OVER (PARTITION BY sql_id ) THEN 'Y'

             ELSE 'N' END) plan_change

  FROM sqlstat_stddev s

-- WHERE ((s.avg_elap_stmt_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_slowest =1)

--    OR  (s.max_elap_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_fastest =1))

 WHERE ((s.avg_elap_stmt_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_slowest =1)

    OR  (s.max_elap_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_fastest =1)

    OR  s.stddev_elap_ms  > s.min_elap_ms

    OR  s.stddev_elap_ms > :MAX_STDDEV_MS OR :MAX_STDDEV_MS IS NULL)    

   AND s.stddev_elap_ms > :MIN_STDDEV_MS      -- Min STDDEV required

   AND (s.stddev_elap_ms > (s.max_elap_ms*(:PCT_VARIANCE/100)) OR :PCT_VARIANCE IS NULL)  --STDDEV Greater than 10% of the max . 

   AND (s.avg_elap_ms_fastest = 1 OR s.avg_elap_ms_slowest =1)

)

SELECT  s.*

      ,t.sql_text

  FROM sql_stat_stdfinal s

  JOIN dba_hist_sqltext t

    ON s.sql_id = t.sql_id 

 WHERE plan_change = :PLAN_CHANGE_Y_OR_N_OR_NULL OR :PLAN_CHANGE_Y_OR_N_OR_NULL IS NULL

 ORDER BY stddev_elap_ms DESC, avg_elap_ms_fastest 

--**/

;

#####################################################################
Best to run in toad for better outputs.

#####################################################################

Comments

Popular posts from this blog

How to unmount and mount ACFS FILESYSTEM

How to unmount and mount ACFS FileSystem in Linux Unmounting ACFS File Systems ACFS file systems can be used by Oracle RAC for hosting software files for the database. It can also be used as a general purpose file system for non-database files. The ACFS file system is managed and administered by the Oracle Grid Infrastructure, So ACFS file systems will be impacted when shutting down the GI stack for patching GI homes. Shut down the processes (including non-Oracle processes) that use the files on ACFS, then unmount the ACFS file system. If the ACFS file system is used by Oracle database software, then perform Steps 1 and 2. 1.      Execute the following command to find the names of the CRS managed ACFS file system resource.  # crsctl stat res -w "TYPE = ora.acfs.type" -p | grep VOLUME 2.      Execute the following command to stop the CRS managed ACFS file system resource with the resource name found from Step 1. ...

How to perform GRID/RDBMS patching of DB systems deployed in OCI using dbcli command line utility

This procedure covers OS, Grid and RDBMS home patching for Oracle Cloud Infrastructure (OCI) DB systems using the dbcli utility. Run every step as root . Advantages of dbcli patching Simple command line, with no manual patch download or copy to the nodes. Patches are downloaded automatically if the service gateway can reach Oracle Object Storage and the YUM repos. Applied in a rolling fashion, so no downtime is needed. Disadvantages You can't choose the patch version; the latest available in OCI is applied. Rollback isn't possible from the console, and dbcli support is limited (see the note at the end). Steps 1. Backup Make sure a recent full backup exists before patching. 2. Blackout Create a blackout for both nodes (console or emctl ) to avoid unnecessary alerts. 3. Check patch availability # dbcli describe-component 4. Pre-checks Grid home: # dbcli update-server -p # dbcli describe-job -i <jobId> RDBMS home: # dbcli list-dbhomes...

To fix huge lag between primary and DR using 12c feature: 12c Recover from service

Scope If transport or apply lag is huge difference, then we can fix using 12c new feature “Recover from Service”. Prerequisites : Oracle Net connectivity is established between the physical standby database and the primary database. You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. The password files on the primary database and the physical standby database are the same. The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0 or higher. Start RMAN and connect as target to the physical standby database. Detailed Steps: Step 1: Stop the MRP process in standby database. If dg broker: Edit database <dbname> set state=’APPLY-OFF’; Step 2:   Shut down the standby database and mount the same. Shut immediate;   (srvctl stop database –d <dbname>)  ...