Oracle dba/monitoring/tuning.sql

Oracle dba/monitoring/tuning.sql

SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF

SELECT *
FROM   v$database;
PROMPT

DECLARE
  v_value  NUMBER;

  FUNCTION Format(p_value  IN  NUMBER) 
    RETURN VARCHAR2 IS
  BEGIN
    RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || '  ';
  END;

BEGIN

  -- --------------------------
  -- Dictionary Cache Hit Ratio
  -- --------------------------
  SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
  INTO   v_value
  FROM   v$rowcache;

  DBMS_Output.Put('Dictionary Cache Hit Ratio       : ' || Format(v_value));
  IF v_value < 90 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -----------------------
  -- Library Cache Hit Ratio
  -- -----------------------
  SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
  INTO   v_value
  FROM   v$librarycache;

  DBMS_Output.Put('Library Cache Hit Ratio          : ' || Format(v_value));
  IF v_value < 99 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -------------------------------
  -- DB Block Buffer Cache Hit Ratio
  -- -------------------------------
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  INTO   v_value
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

  DBMS_Output.Put('DB Block Buffer Cache Hit Ratio  : ' || Format(v_value));
  IF v_value < 89 THEN
    DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ---------------
  -- Latch Hit Ratio
  -- ---------------
  SELECT (1 - (Sum(misses) / Sum(gets))) * 100
  INTO   v_value
  FROM   v$latch;

  DBMS_Output.Put('Latch Hit Ratio                  : ' || Format(v_value));
  IF v_value < 98 THEN
    DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -----------------------
  -- Disk Sort Ratio
  -- -----------------------
  SELECT (disk.value/mem.value) * 100
  INTO   v_value
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

  DBMS_Output.Put('Disk Sort Ratio                  : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ----------------------
  -- Rollback Segment Waits
  -- ----------------------
  SELECT (Sum(waits) / Sum(gets)) * 100
  INTO   v_value
  FROM   v$rollstat;

  DBMS_Output.Put('Rollback Segment Waits           : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -------------------
  -- Dispatcher Workload
  -- -------------------
  SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
  INTO   v_value
  FROM   v$dispatcher;

  DBMS_Output.Put('Dispatcher Workload              : ' || Format(v_value));
  IF v_value > 50 THEN
    DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;
  
END;
/

PROMPT
SET FEEDBACK ON

Script to find Oracle database performance

Script to find Oracle database performance

Just the below line up to last / copy and paste to sqlplus using / as sysdba

SET SERVEROUTPUT ON

DECLARE
   CURSOR c1
   IS
      SELECT version FROM v$instance;

   CURSOR c2
   IS
      SELECT host_name,
             instance_name,
             TO_CHAR (SYSDATE, 'HH24:MI:SS DD-MON-YY') currtime,
             TO_CHAR (startup_time, 'HH24:MI:SS DD-MON-YY') starttime
        FROM v$instance;

   CURSOR c4
   IS
      SELECT *
        FROM (  SELECT COUNT (*) cnt, SUBSTR (event, 1, 50) event
                  FROM v$session_wait
                 WHERE wait_time = 0
                       AND event NOT IN
                              ('smon timer',
                               'pipe get',
                               'wakeup time manager',
                               'pmon timer',
                               'rdbms ipc message',
                               'SQL*Net message from client')
              GROUP BY event
              ORDER BY 1 DESC)
       WHERE ROWNUM < 6;

   CURSOR c5
   IS
      SELECT ROUND (SUM (VALUE) / 1048576) AS sgasize FROM v$sga;

   CURSOR c6
   IS
      SELECT ROUND (SUM (bytes) / 1048576) AS dbsize FROM v$datafile;

   CURSOR c7
   IS
      SELECT 'top physical i/o process' category,
             sid,
             username,
             total_user_io amt_used,
             ROUND (100 * total_user_io / total_io, 2) pct_used
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_io
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN
                              ('physical reads',
                               'physical writes',
                               'physical reads direct',
                               'physical reads direct (lob)',
                               'physical writes direct',
                               'physical writes direct (lob)')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_io
                FROM v$statname c, v$sesstat a
               WHERE a.statistic# = c.statistic#
                     AND c.name IN
                            ('physical reads',
                             'physical writes',
                             'physical reads direct',
                             'physical reads direct (lob)',
                             'physical writes direct',
                             'physical writes direct (lob)'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top logical i/o process',
             sid,
             username,
             total_user_io amt_used,
             ROUND (100 * total_user_io / total_io, 2) pct_used
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_io
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN ('consistent gets', 'db block gets')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_io
                FROM v$statname c,
                     v$sesstat a,
                     v$session b,
                     v$bgprocess p
               WHERE     a.statistic# = c.statistic#
                     AND p.paddr(+) = b.paddr
                     AND b.sid = a.sid
                     AND c.name IN ('consistent gets', 'db block gets'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top memory process',
             sid,
             username,
             total_user_mem,
             ROUND (100 * total_user_mem / total_mem, 2)
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_mem
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name IN
                              ('session pga memory', 'session uga memory')
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_mem
                FROM v$statname c, v$sesstat a
               WHERE a.statistic# = c.statistic#
                     AND c.name IN
                            ('session pga memory', 'session uga memory'))
       WHERE ROWNUM < 2
      UNION ALL
      SELECT 'top cpu process',
             sid,
             username,
             total_user_cpu,
             ROUND (100 * total_user_cpu / GREATEST (total_cpu, 1), 2)
        FROM (  SELECT b.sid sid,
                       NVL (b.username, p.name) username,
                       SUM (VALUE) total_user_cpu
                  FROM v$statname c,
                       v$sesstat a,
                       v$session b,
                       v$bgprocess p
                 WHERE     a.statistic# = c.statistic#
                       AND p.paddr(+) = b.paddr
                       AND b.sid = a.sid
                       AND c.name = 'CPU used by this session'
                       AND b.username NOT IN
                              ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
              GROUP BY b.sid, NVL (b.username, p.name)
              ORDER BY 3 DESC),
             (SELECT SUM (VALUE) total_cpu
                FROM v$statname c,
                     v$sesstat a,
                     v$session b,
                     v$bgprocess p
               WHERE     a.statistic# = c.statistic#
                     AND p.paddr(+) = b.paddr
                     AND b.sid = a.sid
                     AND c.name = 'CPU used by this session')
       WHERE ROWNUM < 2;


   CURSOR c8
   IS
        SELECT username, SUM (VALUE / 100) cpu_usage_sec
          FROM v$session ss, v$sesstat se, v$statname sn
         WHERE     se.statistic# = sn.statistic#
               AND name LIKE '%CPU used by this session%'
               AND se.sid = ss.sid
               AND username IS NOT NULL
               AND username NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      GROUP BY username
      ORDER BY 2 DESC;
BEGIN
   DBMS_OUTPUT.put_line ('Database Version');
   DBMS_OUTPUT.put_line ('-----------------');

   FOR rec IN c1
   LOOP
      DBMS_OUTPUT.put_line (rec.version);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Hostname');
   DBMS_OUTPUT.put_line ('----------');

   FOR rec IN c2
   LOOP
      DBMS_OUTPUT.put_line (rec.host_name);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('SGA Size (MB)');
   DBMS_OUTPUT.put_line ('-------------');

   FOR rec IN c5
   LOOP
      DBMS_OUTPUT.put_line (rec.sgasize);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Database Size (MB)');
   DBMS_OUTPUT.put_line ('-----------------');

   FOR rec IN c6
   LOOP
      DBMS_OUTPUT.put_line (rec.dbsize);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Instance start-up time');
   DBMS_OUTPUT.put_line ('-----------------------');

   FOR rec IN c2
   LOOP
      DBMS_OUTPUT.put_line (rec.starttime);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));

   FOR b IN (SELECT total,
                    active,
                    inactive,
                    SYSTEM,
                    killed
               FROM (SELECT COUNT (*) total FROM v$session),
                    (SELECT COUNT (*) SYSTEM
                       FROM v$session
                      WHERE username IS NULL),
                    (SELECT COUNT (*) active
                       FROM v$session
                      WHERE status = 'ACTIVE' AND username IS NOT NULL),
                    (SELECT COUNT (*) inactive
                       FROM v$session
                      WHERE status = 'INACTIVE'),
                    (SELECT COUNT (*) killed
                       FROM v$session
                      WHERE status = 'KILLED'))
   LOOP
      DBMS_OUTPUT.put_line ('Active Sessions');
      DBMS_OUTPUT.put_line ('---------------');
      DBMS_OUTPUT.put_line (
            b.total
         || ' sessions: '
         || b.inactive
         || ' inactive,'
         || b.active
         || ' active, '
         || b.SYSTEM
         || ' system, '
         || b.killed
         || ' killed ');
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Sessions Waiting');
   DBMS_OUTPUT.put_line (CHR (13));
   DBMS_OUTPUT.put_line ('Count      Event Name');
   DBMS_OUTPUT.put_line (
      '-----      -----------------------------------------------------');

   FOR rec IN c4
   LOOP
      DBMS_OUTPUT.put_line (rec.cnt || '          ' || rec.event);
   END LOOP;

   DBMS_OUTPUT.put_line (CHR (13));


   DBMS_OUTPUT.put_line (
      '-----      -----------------------------------------------------');


   DBMS_OUTPUT.put_line (
      'TOP Physical i/o, logical i/o, memory and CPU processes');
   DBMS_OUTPUT.put_line ('---------------');

   FOR rec IN c7
   LOOP
      DBMS_OUTPUT.put_line (
            rec.category
         || ': SID '
         || rec.sid
         || ' User : '
         || rec.username
         || ': Amount used : '
         || rec.amt_used
         || ': Percent used: '
         || rec.pct_used);
   END LOOP;


   DBMS_OUTPUT.put_line (
      '------------------------------------------------------------------');


   DBMS_OUTPUT.put_line ('TOP CPU users by usage');
   DBMS_OUTPUT.put_line ('---------------');

   FOR rec IN c8
   LOOP
      DBMS_OUTPUT.put_line (rec.username || '--' || rec.cpu_usage_sec);
      DBMS_OUTPUT.put_line ('---------------');
   END LOOP;
END;
/