General PERFORMANCE TUNING scripts Using AWR and ASH Views

-

 

Note:  Proof read 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';

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the ...
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify th...
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12      ...
Read more