General PERFORMANCE TUNING scripts Using AWR and ASH Views

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Listed below are some SQL queries which are very useful for performance tuning. these are based on the ACTIVE SESSION HISTORY V$ VIEW to get a current perspective of performance and the DBA_HIST_* AWR HISTORY TABLES for obtaining performance data pertaining to a period of time in the past.

-- TOP RECENT WAIT EVENTS

SET LINESIZE 132 PAGESIZE 60

COL EVENT FORMAT A60

SELECT * FROM (
   SELECT ACTIVE_SESSION_HISTORY.EVENT,
          SUM(ACTIVE_SESSION_HISTORY.WAIT_TIME +
              ACTIVE_SESSION_HISTORY.TIME_WAITED) TTL_WAIT_TIME
   FROM V$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
/

-- TOP WAIT EVENTS SINCE INSTANCE STARTUP

SET LINESIZE 132 PAGESIZE 60

COL EVENT FORMAT A60

SELECT * FROM (
     SELECT  EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, TIME_WAITED
     FROM V$SYSTEM_EVENT
     WHERE EVENT NOT LIKE 'SQL*Net%'
     AND EVENT NOT IN ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
     ORDER BY TIME_WAITED DESC )
WHERE ROWNUM <= 10
/

-- LIST OF USERS CURRENTLY WAITING

SET LINESIZE 132 PAGESIZE 60

COL USERNAME FORMAT A12
COL SID FORMAT 999999
COL STATE FORMAT A20
COL EVENT FORMAT A50
COL WAIT_TIME FORMAT 99999999

SELECT S.SID, S.USERNAME, SE.EVENT, SE.STATE, SE.WAIT_TIME
FROM V$SESSION S, V$SESSION_WAIT SE
WHERE S.SID=SE.SID
  AND SE.EVENT NOT LIKE 'SQL*Net%'
  AND SE.EVENT NOT LIKE '%rdbms%'
  AND S.USERNAME IS NOT NULL
  -- AND SE.WAIT_TIME > 0
ORDER BY SE.WAIT_TIME
/

-- FIND THE MAIN DATABASE WAIT EVENTS IN A PARTICULAR TIME INTERVAL

/* FIRST DETERMINE THE SNAPSHOT ID VALUES FOR THE PERIOD IN QUESTION. */
/* IN THIS EXAMPLE WE NEED TO FIND THE SNAP_ID FOR THE PERIOD 10 PM TO 11 PM ON THE 11TH OF JANUARY, 2013. */

SET LINESIZE 132 PAGESIZE 60

COLUMN BEGIN_INTERVAL_TIME FORMAT A30
COLUMN END_INTERVAL_TIME FORMAT A30

SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'DD-MON-YYYY')='11-JAN-2013'
  AND EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME) BETWEEN 22 AND 23;

SET VERIFY OFF
SET LINESIZE 132 PAGESIZE 60

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 &SSNAPID AND &ESNAPID
  GROUP BY ACTIVE_SESSION_HISTORY.EVENT
  ORDER BY 2 DESC)
WHERE ROWNUM <= 10
/

-- TOP CPU CONSUMING SQL DURING A CERTAIN TIME PERIOD

/* NOTE – IN THIS CASE WE ARE FINDING THE TOP 10 CPU INTENSIVE SQL STATEMENTS EXECUTED BETWEEN 9.00 AM AND 11.00 AM */

SET LINESIZE 132 PAGESIZE 60

COLUMN "CPU TIME" FORMAT 9999999999999

SELECT * FROM (
     SELECT SQL_ID,
            SUM(CPU_TIME_DELTA) "CPU TIME",
            SUM(DISK_READS_DELTA) "DISK READS",
            COUNT(*)
     FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT S
     WHERE S.SNAP_ID = A.SNAP_ID
       AND S.BEGIN_INTERVAL_TIME > SYSDATE -1
       AND EXTRACT(HOUR FROM S.END_INTERVAL_TIME) BETWEEN 9 AND 11
     GROUP BY SQL_ID
     ORDER BY SUM(CPU_TIME_DELTA) DESC)
WHERE ROWNUM <= 10
/

/* TO FIND THE SQL TEXT FOR THE ABOVE SQL ID's */

SET LINESIZE 300 LONG 10000

COLUMN SQL_FULLTEXT FORMAT A150
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQLID';

-- WHICH DATABASE OBJECTS EXPERIENCED THE MOST NUMBER OF WAITS IN THE PAST ONE HOUR

SET LINESIZE 132 PAGESIZE 60

COL EVENT FORMAT A40
COL OBJECT_NAME FORMAT A40

SELECT * FROM
(
  SELECT DBA_OBJECTS.OBJECT_NAME,
         DBA_OBJECTS.OBJECT_TYPE,
         ACTIVE_SESSION_HISTORY.EVENT,
         SUM(ACTIVE_SESSION_HISTORY.WAIT_TIME +
             ACTIVE_SESSION_HISTORY.TIME_WAITED) TTL_WAIT_TIME
  FROM V$ACTIVE_SESSION_HISTORY ACTIVE_SESSION_HISTORY, DBA_OBJECTS
  WHERE ACTIVE_SESSION_HISTORY.SAMPLE_TIME BETWEEN SYSDATE - 1/24 AND SYSDATE
    AND ACTIVE_SESSION_HISTORY.CURRENT_OBJ# = DBA_OBJECTS.OBJECT_ID
  GROUP BY DBA_OBJECTS.OBJECT_NAME, DBA_OBJECTS.OBJECT_TYPE, ACTIVE_SESSION_HISTORY.EVENT
  ORDER BY 4 DESC)
WHERE ROWNUM <= 10
/

-- TOP SEGMENTS ORDERED BY PHYSICAL READS

SET LINESIZE 132 PAGESIZE 60

COL SEGMENT_NAME FORMAT A40
COL OWNER FORMAT A10

SELECT SEGMENT_NAME,OBJECT_TYPE,TOTAL_PHYSICAL_READS
FROM ( SELECT OWNER||'.'||OBJECT_NAME AS SEGMENT_NAME, OBJECT_TYPE, VALUE AS TOTAL_PHYSICAL_READS
       FROM V$SEGMENT_STATISTICS
       WHERE STATISTIC_NAME IN ('physical reads')
       ORDER BY TOTAL_PHYSICAL_READS DESC)
WHERE ROWNUM <= 10
/

-- TOP 10 SQL STATEMENTS IN THE PAST ONE HOUR

SET LINESIZE 300 PAGESIZE 60

COL SQL_TEXT FORMAT A100

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 <= 10
/

/* TO FIND THE SQL TEXT FOR THE ABOVE SQL ID's */

SET LINESIZE 300 LONG 10000

COLUMN SQL_FULLTEXT FORMAT A150
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQLID';

-- SQL WITH THE HIGHEST I/O IN THE PAST ONE DAY

SET LINESIZE 132 PAGESIZE 60

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
/

-- TOP CPU CONSUMING QUERIES SINCE PAST ONE DAY

SET LINESIZE 132 PAGESIZE 60

COLUMN "CPU TIME" FORMAT 9999999999999

SELECT * FROM (
     SELECT SQL_ID,
            SUM(CPU_TIME_DELTA) "CPU TIME",
            SUM(DISK_READS_DELTA) "DISK READS",
            COUNT(*)
     FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT S
     WHERE S.SNAP_ID = A.SNAP_ID
       AND S.BEGIN_INTERVAL_TIME > SYSDATE -1
     GROUP BY SQL_ID
     ORDER BY SUM(CPU_TIME_DELTA) DESC)
WHERE ROWNUM <= 10
/

/* TO FIND THE SQL TEXT FOR THE ABOVE SQL ID's */

SET LINESIZE 300 LONG 10000

COLUMN SQL_FULLTEXT FORMAT A150
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQLID';

-- FIND WHAT THE TOP SQL WAS AT A PARTICULAR REPORTED TIME OF DAY

/* FIRST DETERMINE THE SNAPSHOT ID VALUES FOR THE PERIOD IN QUESTION. */
/* IN THIS EXAMPLE WE NEED TO FIND THE SNAP_ID FOR THE PERIOD 10 PM TO 11 PM ON THE 11TH OF JANUARY, 2013. */

SET LINESIZE 132 PAGESIZE 60

COLUMN BEGIN_INTERVAL_TIME FORMAT A30
COLUMN END_INTERVAL_TIME FORMAT A30

SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
WHERE TO_CHAR(BEGIN_INTERVAL_TIME,'DD-MON-YYYY')='11-JAN-2013'
  AND EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME) BETWEEN 22 AND 23;

COLUMN "BUFFER GETS" FORMAT 9999999999999
COLUMN "DISK READS" FORMAT 9999999999999
COLUMN "IO WAITS" FORMAT 9999999999999

SELECT * FROM
 ( SELECT SQL.SQL_ID "SQL ID",
          SQL.BUFFER_GETS_DELTA "BUFFER GETS",
          SQL.DISK_READS_DELTA "DISK READS",
          SQL.IOWAIT_DELTA "IO WAITS"
   FROM DBA_HIST_SQLSTAT SQL, DBA_HIST_SNAPSHOT S
   WHERE S.SNAP_ID = SQL.SNAP_ID
     AND S.SNAP_ID BETWEEN &SSNAPID AND &ESNAPID
   ORDER BY SQL.DISK_READS_DELTA DESC)
WHERE ROWNUM <= 10
/

/* TO FIND THE SQL TEXT FOR THE ABOVE SQL ID's */

SET LINESIZE 300 LONG 10000

COLUMN SQL_FULLTEXT FORMAT A150
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQLID';

-- ANALYSE A PARTICULAR SQL ID AND SEE THE TRENDS FOR THE PAST DAY

SET LINESIZE 132 PAGESIZE 60

COLUMN "DATE TIME" FORMAT A20
COLUMN "EXECUTIONS" FORMAT 9999999999999
COLUMN "BUFFER GETS" FORMAT 9999999999999
COLUMN "DISK READS" FORMAT 9999999999999
COLUMN "IO WAITS" FORMAT 9999999999999
COLUMN "CPU TIME" FORMAT 9999999999999
COLUMN "ELAPSED TIME" FORMAT 9999999999999

SELECT S.SNAP_ID,
       TO_CHAR(S.BEGIN_INTERVAL_TIME,'DD-MON-YYYY HH24:MI') "DATE TIME",
       SQL.EXECUTIONS_DELTA "EXECUTIONS",
       SQL.BUFFER_GETS_DELTA "BUFFER GETS",
       SQL.DISK_READS_DELTA "DISK READS",
       SQL.IOWAIT_DELTA "IO WAITS",
       SQL.CPU_TIME_DELTA "CPU TIME",
       SQL.ELAPSED_TIME_DELTA "ELAPSED TIME"
FROM DBA_HIST_SQLSTAT SQL, DBA_HIST_SNAPSHOT S
WHERE S.SNAP_ID = SQL.SNAP_ID
  AND S.BEGIN_INTERVAL_TIME > SYSDATE -1
  AND SQL.SQL_ID='&SQLID'
ORDER BY S.SNAP_ID, SQL.ELAPSED_TIME_DELTA
/

-- DO WE HAVE MULTIPLE PLAN HASH VALUES FOR THE SAME SQL ID – IN THAT CASE MAY BE CHANGED PLAN IS CAUSING BAD PERFORMANCE

SET LINESIZE 132 PAGESIZE 60

SELECT SQL_ID,
       PLAN_HASH_VALUE,
       SUM(EXECUTIONS_DELTA) EXECUTIONS,
       SUM(ROWS_PROCESSED_DELTA) ROWS_PROCESSED,
       TRUNC(SUM(CPU_TIME_DELTA)/1000000/60) CPU_MINS,
       TRUNC(SUM(ELAPSED_TIME_DELTA)/1000000/60)  ELAPSED_MINS
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID IN ('&SQLID')
GROUP BY SQL_ID, PLAN_HASH_VALUE
ORDER BY SQL_ID, CPU_MINS
/

-- TOP 10 QUERIES FOR PAST WEEK BASED ON ADDM RECOMMENDATIONS

/* TOP 10 SQL_ID'S FOR THE LAST 7 DAYS AS IDENTIFIED BY ADDM FROM DBA_ADVISOR_RECOMMENDATIONS AND DBA_ADVISOR_LOG */

SET LINESIZE 300 PAGESIZE 60

COLUMN SQL_ID FORMAT A16
COLUMN "BENEFIT" FORMAT 9999999999999

SELECT * FROM
  (SELECT B.ATTR1 AS SQL_ID, MAX(A.BENEFIT) AS "BENEFIT"
   FROM DBA_ADVISOR_RECOMMENDATIONS A, DBA_ADVISOR_OBJECTS B
   WHERE A.REC_ID = B.OBJECT_ID
     AND A.TASK_ID = B.TASK_ID
     AND A.TASK_ID IN (SELECT DISTINCT B.TASK_ID
                       FROM DBA_HIST_SNAPSHOT A, DBA_ADVISOR_TASKS B, DBA_ADVISOR_LOG L
                       WHERE A.BEGIN_INTERVAL_TIME > SYSDATE - 7
                         AND  A.DBID = (SELECT DBID FROM V$DATABASE)
                         AND A.INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM V$INSTANCE)
                         AND TO_CHAR(A.BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24') = TO_CHAR(B.CREATED, 'YYYYMMDDHH24')
                         AND B.ADVISOR_NAME = 'ADDM'
                         AND B.TASK_ID = L.TASK_ID
                         AND L.STATUS = 'COMPLETED')
     AND LENGTH(B.ATTR4) > 1
   GROUP BY B.ATTR1
   ORDER BY MAX(A.BENEFIT) DESC)
WHERE ROWNUM <= 10
/

/* TO FIND THE SQL TEXT FOR THE ABOVE SQL ID's */

SET LINESIZE 300 LONG 10000

COLUMN SQL_FULLTEXT FORMAT A150
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID='&SQLID';