Oracle Database Performance Issue Troubleshooting and Tuning Part – I

“Database or query is performing very slow”. This is a very common complain for DBA. Database performance is an art as there is always have an option to tune. There is no limit to tuning the database performance but DBA need to meet the company’s threshold or SLA. There are lots of factors involved to get database response slow.

Network Issue.
Application Server Issue.
Storage Issue.
Users might run a query without a proper WHERE clause.
Major changes from application end like inserted/deleted bulk data without DBA notice.
Database Upgradation or Migration.

Few cases, DBA need to engage different teams to check from their end like the Application team, Network team or Storage team.

In this article, I will discuss how dba can begin troubleshooting performance issues and tune/resolve.

To identify the issue, we will check following options step by step:

Find the issue for a particular user/session:

SQL> SELECT SID, STATE, EVENT
FROM V$SESSION
WHERE USERNAME = 'SCOTT'; 
# QUERY TO FIND THE WAIT DETAILS - 
SQL> COL "DESCRIPTION" FORMAT A50
SQL> SELECT SID,
     DECODE (STATE, 'WAITING', 'WAITING', 'WORKING')    STATE,
           DECODE (STATE,
                  'WAITING', 'SO FAR ' || SECONDS_IN_WAIT,
                  'LAST WAITED ' || WAIT_TIME / 100)
       || ' SECS FOR '
       || EVENT                                   "DESCRIPTION"
  FROM V$SESSION
 WHERE USERNAME = 'SCOTT';
# FINDING BLOCKING/LOCKING SESSION:
SQL> SELECT BLOCKING_SESSION B_SID, BLOCKING_INSTANCE B_INST
FROM V$SESSION
WHERE SID = &SID;
#Getting row lock information
SQL> SELECT ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# FROM V$SESSION 
WHERE SID = &SID;
ROW_WAIT_OBJ#  ROW_WAIT_FILE#  ROW_WAIT_BLOCK#  ROW_WAIT_ROW#
—————————————  ——————————————  ———————————————— ——————————————
241876         1024            2307623          0
#To get the object information:
SQL> SELECT OWNER, OBJECT_TYPE, OBJECT_NAME, DATA_OBJECT_ID
FROM DBA_OBJECTS
WHERE OBJECT_ID = 241876;
OWNER  OBJECT_TYPE  OBJECT_NAME   DATA_OBJECT_ID
—————  ———————————— ————————————  ——————————————
SCOTT   TABLE        TEST                    241877
SQL> SELECT *
FROM SCOTT.TEST
WHERE ROWID = DBMS_ROWID.ROWID_CREATE ( ROWID_TYPE      =>  1, 
                OBJECT_NUMBER   => &OBJECT_NUMBER,
                RELATIVE_FNO    => &ROW_WAIT_FILE,
                BLOCK_NUMBER    => &ROW_WAIT_BLOCK,
                ROW_NUMBER      => &ROW_NUMBER
        );
		
Enter Value for OBJECT_NUMBER: 241877
Enter Value for ROW_WAIT_FILE: 1024
Enter Value for ROW_WAIT_BLOCK: 2307623
Enter Value for ROW_NUMBER: 0
COL1  C
————— —
  1   x

# Sessions from a specific user
SQL> SELECT SID, OSUSER, MACHINE, TERMINAL, SERVICE_NAME, LOGON_TIME, LAST_CALL_ET FROM V$SESSION
 WHERE USERNAME = 'SCOTT';
# Session waits for a specific machine/Client
SQL> COL USERNAME FORMAT A5
COL PROGRAM FORMAT A10
COL STATE FORMAT A10
COL LAST_CALL_ET HEAD 'CALLED|SECS AGO' FORMAT 999999
COL SECONDS_IN_WAIT HEAD 'WAITING|FOR SECS' FORMAT 999999
COL EVENT FORMAT A50
SQL> SELECT SID, USERNAME, PROGRAM, DECODE (STATE, 'WAITING', 'WAITING', 'WORKING') STATE, LAST_CALL_ET, SECONDS_IN_WAIT, EVENT
FROM V$SESSION
WHERE MACHINE = 'OLE17DB19';
SQL> SELECT SQL_ID, EVENT
FROM V$SESSION
WHERE SID = &SID;
SQL> SET LONG 99999
SQL> SELECT SESS.SQL_ID, EVENT, SQL_FULLTEXT
FROM V$SESSION SESS, V$SQL S
WHERE SESS.SQL_ID = S.SQL_ID AND SESS.SID = &SID;
SQL> SELECT SID, STATE, EVENT, P1, P2
FROM V$SESSION
WHERE USERNAME = 'SCOTT'; 
SID  STATE     EVENT                   P1 P2
———— ———————   ——————————————————————— —— ————
1308 WAITING   db file sequential read  8 1092
# Find the object 
SQL> SELECT OWNER, SEGMENT_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = 8 AND 1092 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS;
OWNER    SEGMENT_NAME
———————  ———————————————————————
SCOTT    TEST

This shows that the TEST table, owned by SCOTT, is being selected from by the disk in the session. You should direct your attention to this table for tuning. You can move the table to a high-speed disk for faster I/O, or, alternatively, you can focus on making I/O in this table faster by making changes that affect this table, such as creating new indexes, creating materialized views, or building a result cache.

Database Performing Very Slow (lots of users complain):

To Find Top Elapsed Time SQL:

 SELECT ST.SQL_TEXT,
         SUB.SQL_ID,
         SUB.ELAPSED_TIME     PER_EXEC_ELAPSED_TIME_MINUTES
    FROM DBA_HIST_SQLTEXT ST,
         (  SELECT T.SQL_ID,
                   ROUND (
                         SUM (T.ELAPSED_TIME_DELTA / 60000000)
                       / SUM (T.EXECUTIONS_DELTA))    ELAPSED_TIME
              FROM DBA_HIST_SQLSTAT T, DBA_HIST_SNAPSHOT S, DBA_HIST_SQLTEXT ST
             WHERE     T.SNAP_ID = S.SNAP_ID
                   AND T.DBID = S.DBID
                   AND T.INSTANCE_NUMBER = S.INSTANCE_NUMBER
                   AND T.EXECUTIONS_DELTA > 0
                   AND S.BEGIN_INTERVAL_TIME >
                       TO_DATE ('11/13/2020 11:00:00', 'MM/DD/YYYY HH24:MI:SS')
                   AND END_INTERVAL_TIME <
                       TO_DATE ('11/13/2020 12:0:00', 'MM/DD/YYYY HH24:MI:SS')
          GROUP BY T.SQL_ID
          ORDER BY 2 DESC) SUB
   WHERE SUB.SQL_ID = ST.SQL_ID
ORDER BY 3 DESC;

To Find TOP CPU Consuming SQL Statements:

SELECT *
  FROM (  SELECT SS.SQL_TEXT,
                 A.SQL_ID,
                 SUM (CPU_TIME_DELTA),
                 SUM (DISK_READS_DELTA),
                 COUNT (*)
            FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT S, V$SQL SS
           WHERE     S.SNAP_ID = A.SNAP_ID
                 AND A.SQL_ID = SS.SQL_ID
                 AND S.BEGIN_INTERVAL_TIME > SYSDATE - 1
        GROUP BY SS.SQL_TEXT, A.SQL_ID
        ORDER BY SUM (CPU_TIME_DELTA) DESC)
 WHERE ROWNUM < 20;

To Find Top IO Consuming SQL Statement:

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;

To Find Top 10 SQL Statement:

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 < 11;

Shared Pool Utilization: A quick method of seeing whether code is being reused (a key indicator of proper bind variable usage) is to look at the values of reusable and non-reusable memory in the shared pool.

SELECT 1                                                                   NOPR,
       TO_CHAR (A.INST_ID)                                                 INST_ID,
       A.USERS                                                             USERS,
       TO_CHAR (A.GARBAGE, '9,999,999,999')                                GARBAGE,
       TO_CHAR (B.GOOD, '9,999,999,999')                                   GOOD,
       TO_CHAR ((B.GOOD / (B.GOOD + A.GARBAGE)) * 100, '9,999,999.999')    GOOD_PERCENT
  FROM (  SELECT A.INST_ID,
                 B.USERNAME                                  USERS,
                 SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                 TO_NUMBER (NULL)                            GOOD
            FROM SYS.GV_$SQLAREA A, DBA_USERS B
           WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
        GROUP BY A.INST_ID, B.USERNAME
        UNION
          SELECT DISTINCT C.INST_ID,
                          B.USERNAME                                  USERS,
                          TO_NUMBER (NULL)                            GARBAGE,
                          SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
            FROM DBA_USERS B, SYS.GV_$SQLAREA C
           WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
        GROUP BY C.INST_ID, B.USERNAME) A,
       (  SELECT A.INST_ID,
                 B.USERNAME                                  USERS,
                 SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                 TO_NUMBER (NULL)                            GOOD
            FROM SYS.GV_$SQLAREA A, DBA_USERS B
           WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
        GROUP BY A.INST_ID, B.USERNAME
        UNION
          SELECT DISTINCT C.INST_ID,
                          B.USERNAME                                  USERS,
                          TO_NUMBER (NULL)                            GARBAGE,
                          SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
            FROM DBA_USERS B, SYS.GV_$SQLAREA C
           WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
        GROUP BY C.INST_ID, B.USERNAME) B
 WHERE     A.USERS = B.USERS
       AND A.INST_ID = B.INST_ID
       AND A.GARBAGE IS NOT NULL
       AND B.GOOD IS NOT NULL
UNION
SELECT 2                    NOPR,
       '-------'            INST_ID,
       '-------------'      USERS,
       '--------------'     GARBAGE,
       '--------------'     GOOD,
       '--------------'     GOOD_PERCENT
  FROM DUAL
UNION
  SELECT 3                                             NOPR,
         TO_CHAR (A.INST_ID, '999999'),
         TO_CHAR (COUNT (A.USERS))                     USERS,
         TO_CHAR (SUM (A.GARBAGE), '9,999,999,999')    GARBAGE,
         TO_CHAR (SUM (B.GOOD), '9,999,999,999')       GOOD,
         TO_CHAR (((SUM (B.GOOD) / (SUM (B.GOOD) + SUM (A.GARBAGE))) * 100),
                  '9,999,999.999')                     GOOD_PERCENT
    FROM (  SELECT A.INST_ID,
                   B.USERNAME                                  USERS,
                   SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                   TO_NUMBER (NULL)                            GOOD
              FROM SYS.GV_$SQLAREA A, DBA_USERS B
             WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
          GROUP BY A.INST_ID, B.USERNAME
          UNION
            SELECT DISTINCT C.INST_ID,
                            B.USERNAME                                  USERS,
                            TO_NUMBER (NULL)                            GARBAGE,
                            SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
              FROM DBA_USERS B, SYS.GV_$SQLAREA C
             WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
          GROUP BY C.INST_ID, B.USERNAME) A,
         (  SELECT A.INST_ID,
                   B.USERNAME                                  USERS,
                   SUM (A.SHARABLE_MEM + A.PERSISTENT_MEM)     GARBAGE,
                   TO_NUMBER (NULL)                            GOOD
              FROM SYS.GV_$SQLAREA A, DBA_USERS B
             WHERE (A.PARSING_USER_ID = B.USER_ID AND A.EXECUTIONS <= 1)
          GROUP BY A.INST_ID, B.USERNAME
          UNION
            SELECT DISTINCT C.INST_ID,
                            B.USERNAME                                  USERS,
                            TO_NUMBER (NULL)                            GARBAGE,
                            SUM (C.SHARABLE_MEM + C.PERSISTENT_MEM)     GOOD
              FROM DBA_USERS B, SYS.GV_$SQLAREA C
             WHERE (B.USER_ID = C.PARSING_USER_ID AND C.EXECUTIONS > 1)
          GROUP BY C.INST_ID, B.USERNAME) B
   WHERE     A.USERS = B.USERS
         AND A.INST_ID = B.INST_ID
         AND A.GARBAGE IS NOT NULL
         AND B.GOOD IS NOT NULL
GROUP BY A.INST_ID
ORDER BY 1, 2 DESC;

SQL to Get Full Table Scan Data from Database:

REM FTS REPORT
REM BASED ON V$SQL_PLAN TABLE
COL OPERATION FORMAT A15
COL OBJECT_NAME FORMAT A32
COL OBJECT_OWNER FORMAT A15
COL OPTIONS FORMAT A20
COL EXECUTIONS FORMAT 999,999,999
SET PAGES 55 LINES 132 TRIMS ON
SQL>SELECT A.OBJECT_OWNER,
         A.OBJECT_NAME,
         RTRIM (A.OPERATION)     OPERATION,
         A.OPTIONS,
         B.EXECUTIONS
    FROM V$SQL_PLAN A, V$SQLAREA B
   WHERE     A.SQL_ID = B.SQL_ID
         AND A.OPERATION IN ('TABLE ACCESS', 'INDEX')
         AND A.OPTIONS IN ('FULL',
                           'FULL SCAN',
                           'FAST FULL SCAN',
                           'SKIP SCAN',
                           'SAMPLE FAST FULL SCAN')
         AND A.OBJECT_OWNER NOT IN ('SYS', 'SYSTEM', 'PERFSTAT')
GROUP BY OBJECT_OWNER,
         OBJECT_NAME,
         OPERATION,
         OPTIONS,
         B.EXECUTIONS
ORDER BY OBJECT_OWNER,
         OPERATION,
         OPTIONS,
         OBJECT_NAME;
SPOOL OFF
SET PAGES 20
TTITLE OFF

Find the SQL which are not using Bind Variable:

SELECT *
  FROM (WITH
            SUBS
            AS
                (SELECT /*+ MATERIALIZE */
                        M.SQL_ID,
                        K.*,
                        M.SQL_TEXT,
                        M.SQL_FULLTEXT
                   FROM (  SELECT INST_ID,
                                  PARSING_SCHEMA_NAME                              AS USER_NAME,
                                  MODULE,
                                  PLAN_HASH_VALUE,
                                  COUNT (0)                                        COPIES,
                                  SUM (EXECUTIONS)                                 EXECUTIONS,
                                  SUM (ROUND (SHARABLE_MEM / (1024 * 1024), 2))    SHARABLE_MEM_MB
                             FROM GV$SQLAREA
                            WHERE EXECUTIONS < 5 AND KEPT_VERSIONS = 0
                         GROUP BY INST_ID,
                                  PARSING_SCHEMA_NAME,
                                  MODULE,
                                  PLAN_HASH_VALUE
                           HAVING COUNT (0) > 10
                         ORDER BY COUNT (0) DESC) K
                        LEFT JOIN GV$SQLAREA M
                            ON K.PLAN_HASH_VALUE = M.PLAN_HASH_VALUE
                  WHERE K.PLAN_HASH_VALUE > 0)
          SELECT DISTINCT KI.INST_ID,
                          T.SQL_ID,
                          KI.SQL_TEXT,
                          T.PLSQL_PROCEDURE,
                          KI.USER_NAME,
                          SUM (KI.COPIES)              COPIES,
                          SUM (KI.EXECUTIONS)          EXECUTIONS,
                          SUM (KI.SHARABLE_MEM_MB)     SHARABLE_MEM_MB
            FROM (SELECT SQL_ID,
                         PROGRAM_ID,
                         PROGRAM_LINE#,
                         ACTION,
                         MODULE,
                         SERVICE,
                         PARSING_SCHEMA_NAME,
                         ROUND (
                               BUFFER_GETS
                             / DECODE (EXECUTIONS, 0, 1, EXECUTIONS))
                             BUFFER_PER_EXEC,
                         ROW_NUMBER ()
                             OVER (PARTITION BY SQL_ID
                                   ORDER BY PROGRAM_ID DESC, PROGRAM_LINE#)
                             SIRA,
                         DECODE (
                             PROGRAM_ID,
                             0, NULL,
                                OWNER
                             || '.'
                             || OBJECT_NAME
                             || '('
                             || PROGRAM_LINE#
                             || ')')
                             PLSQL_PROCEDURE
                    FROM GV$SQL A, DBA_OBJECTS B
                   WHERE A.PROGRAM_ID = B.OBJECT_ID(+)) T,
                 SUBS KI
           WHERE KI.SQL_ID = T.SQL_ID AND T.SIRA = 1
        GROUP BY KI.INST_ID,
                 T.SQL_ID,
                 KI.SQL_TEXT,
                 T.PLSQL_PROCEDURE,
                 KI.USER_NAME
        ORDER BY SUM (KI.EXECUTIONS) DESC)
 WHERE ROWNUM < 51;

Full Table Scan Reports:

COL SID FORMAT 99999
COL OWNER FORMAT A15
COL SEGMENT_NAME FORMAT A30
COL TD NEW_VALUE REP_DATE NOPRINT
SQL> SELECT DISTINCT A.SID, C.OWNER, C.SEGMENT_NAME
  FROM SYS.V_$SESSION_WAIT A, SYS.V_$DATAFILE B, SYS.DBA_EXTENTS C
 WHERE     A.P1 = B.FILE#
       AND B.FILE# = C.FILE_ID
       AND A.P2 BETWEEN C.BLOCK_ID AND (C.BLOCK_ID + C.BLOCKS)
       AND A.EVENT = 'DB FILE SCATTERED READ';
	   
SPOOL OFF
TTITLE OFF

When to Look For Resource Intensive SQL Statements:

Response Time = Service Time + Wait Time

Service Time = CPU Parse + CPU Recursive + CPU Other

Service Time – “CPU used by this session”
CPU Parse – “parse time cpu”
CPU Recursive -” recursive cpu usage”

CPU Other = CPU used by this session – parse time cpu – recursive cpu usage

How to Identify Resource Intensive SQL (“TOP SQL”) (Doc ID 232443.1)

Database Performance Overview – Master Note – Doc ID – 402983.1

Oracle Database Performance Issue Troubleshooting and Tuning Part – I

2 comments

Comments have been disabled.

About Me

Categories

Archives

VISITORS