Tag Archives: useful

Books: Oracle Data Guard 11gR2 Administration…

Useful scripts: Get DDL for jobs, created by DBMS_JOB…

Here is a script, which is dedicated to generate DDL commands to recreate jobs, created by DBMS_JOB package.

DECLARE
 CURSOR job_list
 IS
 SELECT JOB FROM dba_jobs;
mysql VARCHAR2 (32767);
BEGIN
 FOR job_id IN job_list
 LOOP
 mysql := '';
 DBMS_OUTPUT.put_line ('BEGIN');
 DBMS_OUTPUT.put_line ('DBMS_JOB.REMOVE(' || job_id.job || ');');
 DBMS_JOB.user_export (job_id.job, mysql);
 DBMS_OUTPUT.put_line (mysql);
 DBMS_OUTPUT.put_line ('END;');
 DBMS_OUTPUT.put_line ('/');
 END LOOP;
END;
/

Useful scripts: Log file switch frequency statistsic

Very nice and easy to use query.

SELECT trunc(first_time) "Date",
 to_char(first_time, 'Dy') "Day",
 count(1) "Total",
 SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) "h0",
 SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) "h1",
 SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) "h2",
 SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) "h3",
 SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) "h4",
 SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) "h5",
 SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) "h6",
 SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) "h7",
 SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) "h8",
 SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) "h9",
 SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) "h10",
 SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) "h11",
 SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) "h12",
 SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) "h13",
 SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) "h14",
 SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) "h15",
 SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) "h16",
 SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) "h17",
 SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) "h18",
 SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) "h19",
 SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) "h20",
 SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) "h21",
 SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) "h22",
 SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) "h23",
 round(count(1) / 24, 2) "Avg"
 FROM V$log_history
 group by trunc(first_time), to_char(first_time, 'Dy')
 Order by 1

Useful scripts: Catch a session, causing ‘cursor: pin S wait on X’…

For sure, every network from time to time aborts connection. Especially this commonly could be seen in WANs.

When this happens while a session is fetching data or using some other object through db-link, PMON for some reason (from time to time) is unable to kill this session, so session stucks, leading to ‘cursor: pin S wait on X’ wait event.

It’s quite difficult to find out, which session you need to kill to release memory lock, so here is the query for this:

SELECT *
 FROM v$session
 WHERE sid IN
 (SELECT TO_NUMBER (SUBSTR (TO_CHAR (RAWTOHEX (p2raw)), 1, 8),
 'XXXXXXXX') sid
 FROM v$session
 WHERE event = 'cursor: pin S wait on X')

The following screenshot shows reducing Latch/mutex waits:

Useful scripts: Get a list of objects to be moved for a data file resize

Every DBA from time to time wants to reduce a size of a data file, thinking ‘There is a lot of free space in datafile, why don’t to resize it?’ But the secret is that shrinking tables is not very helpful in this case — datafile least size will be limited to the block, where the last table’s/index’s block resides.

So, the solution is to relocate used blocks to free gaps between blocks.

We can achieve this by several ways:

  • ALTER TABLE …. MOVE or INDEX REDUILD;
  • use DBMS_REDIFINITION package;
  • Export table, purge it and import again with the help of DataPump.

But prior we need to get a list of objects, which need to be relocated to make datafile reduce possible. The following query show such objects:

DECLARE
   V_FILE_ID       NUMBER;
   V_BLOCK_SIZE    NUMBER;
   V_RESIZE_SIZE   NUMBER;
BEGIN
   V_FILE_ID := &FILE_ID;
   V_RESIZE_SIZE := &RESIZE_FILE_TO;
   SELECT BLOCK_SIZE
     INTO V_BLOCK_SIZE
     FROM V$DATAFILE
    WHERE FILE# = V_FILE_ID;

   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE ('.');
   DBMS_OUTPUT.PUT_LINE (
         'OBJECTS IN FILE '
      || V_FILE_ID
      || ' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '
      || V_RESIZE_SIZE
      || ' BYTES');
   DBMS_OUTPUT.PUT_LINE (
      '=======================================================================');
   FOR my_record
      IN (  SELECT DISTINCT
                   (   OWNER
                    || '.'
                    || SEGMENT_NAME
                    || ' - OBJECT TYPE = '
                    || SEGMENT_TYPE)
                      ONAME
              FROM DBA_EXTENTS
             WHERE (block_id + blocks - 1) * V_BLOCK_SIZE > V_RESIZE_SIZE
                   AND FILE_ID = V_FILE_ID
          ORDER BY 1)
   LOOP
      DBMS_OUTPUT.PUT_LINE (my_record.ONAME);
   END LOOP;
END;
/

Useful scripts: Table list with stale statistics

This script is intended for daily use to get tables where percentage of changed records is above 10%:

SELECT DT.OWNER,
       DT.TABLE_NAME,
       ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) PERCENTAGE
FROM   dba_tables dt, ALL_TAB_MODIFICATIONS atm
WHERE      DT.OWNER = ATM.TABLE_OWNER
       AND DT.TABLE_NAME = ATM.TABLE_NAME
       AND NUM_ROWS > 0
       AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
ORDER BY 3 desc

Also you can avoid system schemas by adding:

AND OWNER NOT IN ('SYS','SYSTEM','DBSNMP','OSMMON','PERFSTAT')

Useful scripts: Gather some session statistics…

RDBMS Version: 10g.
Script shows following session statistic values:
  • PGA Memory, in MB;
  • CPU, used by session;
  • Hard Parse, %;
  • Physical read bytes, in MB;
  • Physical write bytes, in MB;
  • Redo size, in MB;
  • Received from client, in MB;
  • Sent to client, in MB.
  SELECT Logon_time,
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
            AS "PGA Memory, in MB",
         (SELECT VALUE
            FROM v$sesstat
           WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
            AS "CPU, used by session",
         ROUND ( (SELECT VALUE
                    FROM v$sesstat
                   WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
                / (SELECT DECODE (VALUE, 0, 1, VALUE)
                     FROM v$sesstat
                    WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
                2)
            AS "Hard Parse, %",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
            AS "Physical read bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
            AS "Physical write bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
            AS "Redo size, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
            AS "Received from client, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
            AS "Sent to client, in MB",
         SID,
         SERIAL#,
         v$session.STATUS,
         PROGRAM,
         USER#,
         USERNAME,
         COMMAND,
         OWNERID,
         OSUSER,
         PROCESS,
         MACHINE,
         OBJECT_NAME
    FROM    v$session
         LEFT OUTER JOIN
            DBA_OBJECTS
         ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
   WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
         --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC

Oracle 11g have changed statistac ID’s there for I put the same script for 11g:

SELECT Logon_time,
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
          AS "PGA Memory, in MB",
       (SELECT VALUE
          FROM v$sesstat
         WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
          AS "CPU, used by session",
       ROUND ( (SELECT VALUE
                  FROM v$sesstat
                 WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
              / (SELECT DECODE (VALUE, 0, 1, VALUE)
                   FROM v$sesstat
                  WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
              2)
          AS "Hard Parse, %",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
          AS "Physical read bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
          AS "Physical write bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
          AS "Redo size, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
          AS "Received from client, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
          AS "Sent to client, in MB",
       SID,
       SERIAL#,
       v$session.STATUS,
       PROGRAM,
       USER#,
       USERNAME,
       COMMAND,
       OWNERID,
       OSUSER,
       PROCESS,
       MACHINE,
       OBJECT_NAME
  FROM    v$session
       LEFT OUTER JOIN
          DBA_OBJECTS
       ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
 WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
       --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC
%d такие блоггеры, как: