Database Tuning‎ > ‎

DB Health Reports SQL


Schema Check sum report

posted Feb 22, 2013, 2:49 PM by Sachchida Ojha

set serveroutput on
set feedback off
set verify off
set linesize 10000

create or replace type chksumType as VARRAY(3000) of NUMBER;
/
declare
  cursor c0 is
    select OWNER, TABLE_NAME from ALL_TABLES where OWNER='&1' ORDER BY TABLE_NAME;
  TYPE refType is REF CURSOR;
  tbCursor refType;
  chksumCursor refType;
  dtCursor refType;
  tableName VARCHAR(64);
  owner VARCHAR(64);
  COLUMN_NAME VARCHAR(64);
  DATA_TYPE VARCHAR(64);
  NULLABLE VARCHAR(64);
  colArray chksumType;
  sqlStatement CLOB;
  TYPE clobArray is VARRAY(10) of CLOB;
  selectListArray clobArray;
  colNum NUMBER;
  qryNum INTEGER;
  i INTEGER;
  j INTEGER;
  outLine CLOB;
  dt VARCHAR2(12);
  numDt NUMBER;
begin
  for tab_rec in c0 loop
    begin
    tableName := tab_rec.TABLE_NAME;
owner := tab_rec.OWNER;

    -- Build the query for "non-DATE and NULL" --
qryNum := 1;
    selectListArray := clobArray('count(*)');
    colNum := 2;
open tbCursor for 'select COLUMN_NAME, DATA_TYPE, NULLABLE from ALL_TAB_COLUMNS where OWNER='''|| owner ||''' and TABLE_NAME=''' || tableName || ''' order by COLUMN_NAME';
    loop
 fetch tbCursor into COLUMN_NAME, DATA_TYPE, NULLABLE;
 exit when tbCursor%NOTFOUND;

 -- Check whether the number of columns is more than max allowed
 if colNum > 950
 then
   qryNum := qryNum + 1;
colNum := 1;

selectListArray.EXTEND;
selectListArray(qryNum) := '';
 end if;

      case
 when DATA_TYPE in ('NUMBER', 'FLOAT', 'DOUBLE') then
        if colNum > 1
   then
     selectListArray(qryNum) := selectListArray(qryNum) || ', ';
   end if;
        selectListArray(qryNum) := selectListArray(qryNum) || 'min(' || COLUMN_NAME || ')' || ', max(' || COLUMN_NAME || ')' || ', avg(' || COLUMN_NAME || ')';
        colNum := colNum + 3;
 when DATA_TYPE in ('VARCHAR', 'VARCHAR2', 'NVARCHAR', 'NVARCHAR2') then
        if colNum > 1
   then
     selectListArray(qryNum) := selectListArray(qryNum) || ', ';
   end if;
        selectListArray(qryNum) := selectListArray(qryNum) || 'min(nvl(length(' || COLUMN_NAME || '),0))' || ', max(nvl(length(' || COLUMN_NAME || '),0))' || ', avg(nvl(length(' || COLUMN_NAME || '),0))';
        colNum := colNum + 3;
 when DATA_TYPE in ('CHAR', 'NCHAR') then
        if colNum > 1
   then
     selectListArray(qryNum) := selectListArray(qryNum) || ', ';
   end if;
        selectListArray(qryNum) := selectListArray(qryNum) || 'min(nvl(length(trim(' || COLUMN_NAME || ')),0))' || ', max(nvl(length(trim(' || COLUMN_NAME || ')),0))' || ', avg(nvl(length(trim(' || COLUMN_NAME || ')),0))';
        colNum := colNum + 3;
 when DATA_TYPE in ('CLOB', 'BLOB', 'NCLOB', 'LONG', 'BFILE', 'RAW') then
        if colNum > 1
   then
     selectListArray(qryNum) := selectListArray(qryNum) || ', ';
   end if;
        selectListArray(qryNum) := selectListArray(qryNum) || 'min(nvl(dbms_lob.getlength(' || COLUMN_NAME || '),0))' || ', max(nvl(dbms_lob.getlength(' || COLUMN_NAME || '),0))' || ', avg(nvl(dbms_lob.getlength(' || COLUMN_NAME || '),0))';
        colNum := colNum + 3;
 else
   selectListArray(qryNum) := selectListArray(qryNum);
 end case;
 -- Add the NULL count
 if NULLABLE = 'Y'
 then
        if colNum > 1
   then
     selectListArray(qryNum) := selectListArray(qryNum) || ', ';
   end if;
        selectListArray(qryNum) := selectListArray(qryNum) || 'sum(nvl2(' || COLUMN_NAME || ',0,1))';
        colNum := colNum + 1;
 end if;
    end loop;
close tbCursor;

j := 951;
i := 0;
open tbCursor for 'select COLUMN_NAME, DATA_TYPE, NULLABLE from ALL_TAB_COLUMNS where OWNER='''|| owner ||''' and TABLE_NAME=''' || tableName || ''' order by COLUMN_NAME';
    loop
 fetch tbCursor into COLUMN_NAME, DATA_TYPE, NULLABLE;
 exit when tbCursor%NOTFOUND;

 if j > 950
 then
   i := i + 1;
        sqlStatement := 'select chksumType(' || selectListArray(i) || ') from ' || owner || '.' || tableName;
        --  dbms_output.put_line(sqlStatement);

-- Execute the "non-DATE and NULL" query --
   open chksumCursor for sqlStatement;

-- Output the "non-DATE and NULL" query results
   fetch chksumCursor into colArray;
close chksumCursor;

if i = 1
then
          -- Output the number of records
          dbms_output.put_line(tableName || ',*,' || colArray(1));
  
          j := 2;
else
 j := 1;
end if;
 end if;

 -- Output the rest of the data
      case
 when DATA_TYPE in ('NUMBER', 'FLOAT', 'DOUBLE', 'VARCHAR', 'VARCHAR2', 'NVARCHAR', 'NVARCHAR2', 'CHAR', 'NCHAR', 'CLOB', 'BLOB', 'NCLOB', 'LONG', 'BFILE', 'RAW') then
   outLine := tableName || ',' || COLUMN_NAME || ',' || DATA_TYPE || ',' || NULLABLE || ',' || colArray(j) || ',' || colArray(j+1) || ',' || colArray(j+2);
   j := j + 3;

        -- Add the NULL count
   if NULLABLE = 'Y'
   then
     outLine := outLine || ',' || colArray(j);
 j := j + 1;
   end if;
   dbms_output.put_line(outLine);
      else
   if NULLABLE = 'Y'
   then
     dbms_output.put_line(tableName || ',' || COLUMN_NAME || ',' || DATA_TYPE || ',' || NULLABLE || ',' || colArray(j));
 j := j + 1;
   end if;
 end case;
end loop;
close tbCursor;

    -- Loop through date/time column
open tbCursor for 'select COLUMN_NAME, DATA_TYPE, NULLABLE from ALL_TAB_COLUMNS where OWNER='''|| owner ||''' and TABLE_NAME=''' || tableName || ''' order by COLUMN_NAME';
    loop
 fetch tbCursor into COLUMN_NAME, DATA_TYPE, NULLABLE;
 exit when tbCursor%NOTFOUND;
      if DATA_TYPE in ('DATE', 'TIMESTAMP')
 then
   -- Create the query to get the distribution of dates
        sqlStatement := 'select to_char(' || COLUMN_NAME || ',''YYYYMMDD''), count(*) from ' || owner || '.' || tableName || ' group by to_char(' || COLUMN_NAME || ',''YYYYMMDD'')';
-- dbms_output.put_line(sqlStatement);

   -- Execute the date distribution query
   open dtCursor for sqlStatement;
   loop
     fetch dtCursor into dt, numDt;
 exit when dtCursor%NOTFOUND;
 dbms_output.put_line(tableName || ',' || COLUMN_NAME || ',' || DATA_TYPE || ',*,' || dt || ',' || numDt);
   end loop;
        close dtCursor;
 end if;
    end loop;
close tbCursor;

  exception
  when others then
    dbms_output.put_line(tableName || ',#ERR#,' || SQLCODE || ':' || SQLERRM);
if dtCursor%isopen
then
 close dtCursor;
end if;
if tbCursor%isopen
then
 close tbCursor;
end if;
if chksumCursor%isopen
then
 close chksumCursor;
end if;
  end;
  end loop;
end;
/
exit;

Here is a description of the output that's generated by the script.  For each table that the script runs the following content is generated in a text file.

1. The SQL query that was used to genarate the counts

2. One line that contains the tablename and total record count in the following format
[tablename],*,[row count]

3. For each non-date column in the table there is a line containing the following information depending on the datatype.
  if datatype is a int, float, numeric type:
[tablename], [column name], [data type], [Nullable Y/N], [Minimum value], [Maximum value], [Average value], [Count of Nulls]
  if datatype is a character or bytea type:
[tablename], [column name], [data type], [Nullable Y/N], [Minimum value length], [Maximum value length], [Average value length], [Count of Nulls]

4. For each date or timestamp column there will be a distribution of all the date values in the table.
[tablename], [column name], [data type],*, [yyyymmdd], [row count] 

User resource usage

posted Sep 27, 2010, 6:37 AM by Sachchida Ojha   [ updated Sep 27, 2010, 6:38 AM ]

SELECT   DECODE(ses.username, NULL, 'SYS', ses.username) username,
         TO_CHAR(ses.SID) SID, sn.NAME, sest.VALUE, ins.instance_name,
         DECODE(ses.username, NULL, 'SYS', ses.username) || TO_CHAR(ses.SID) usersid
    FROM gv$session ses, gv$statname sn, gv$sesstat sest, gv$instance ins
   WHERE ses.SID = sest.SID
     AND sn.statistic# = sest.statistic#
     AND sest.VALUE IS NOT NULL
     AND sest.VALUE != 0
     AND ses.INST_ID = sn.INST_ID
     AND ses.INST_ID = sest.INST_ID
     AND ses.INST_ID = ins.INST_ID
ORDER BY ses.username, ses.SID, upper(sn.NAME);

User hit ratios

posted Sep 27, 2010, 6:36 AM by Sachchida Ojha

SELECT   (DECODE ((INITCAP (se.username)), NULL, 'SYS', se.username)
          ) username,
          se.SID usersession,
          SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0)) consisgets,
          SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0)) dbblkgets,
          SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0)) physreads,
          ROUND(  (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             - SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0))
            )
          / (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             + 1
            ), 6)
          * 100 hitratio,
          MAX (UPPER(ins.instance_name)) instancename
    FROM v$sesstat ss, v$statname sn, v$session se, v$instance ins
   WHERE ss.SID = se.SID
     AND sn.statistic# = ss.statistic#
     AND ss.VALUE != 0
     AND sn.NAME IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY se.username, se.SID
ORDER BY 1,2

Unusable Indexes

posted Sep 27, 2010, 6:35 AM by Sachchida Ojha

SELECT owner, index_name, table_owner, table_name, NULL partition_name,
       NULL subpartition_name, TO_CHAR(num_rows) num_rows, last_analyzed
FROM   DBA_INDEXES
WHERE  tablespace_name IS NOT NULL
AND    status = 'UNUSABLE'
UNION ALL
SELECT i.owner, i.index_name, i.table_owner, i.table_name,  p.partition_name,
       NULL subpartition_name, TO_CHAR(p.num_rows), p.last_analyzed
FROM   DBA_IND_PARTITIONS p,DBA_INDEXES i
WHERE  p.index_owner = i.owner
AND    p.index_name = i.index_name
AND    i.partitioned = 'YES'
AND    p.subpartition_count = 0
AND    P.STATUS = 'UNUSABLE'
UNION ALL
SELECT i.owner, i.index_name, i.table_owner, i.table_name,  p.partition_name,
       p.subpartition_name, TO_CHAR(p.num_rows), p.last_analyzed
FROM   DBA_IND_SUBPARTITIONS p, DBA_INDEXES i
WHERE  p.index_owner = i.owner
AND    p.index_name = i.index_name
AND    i.partitioned = 'YES'
AND    P.STATUS = 'UNUSABLE'
ORDER BY 1,2

Total shared pool reload stats

posted Sep 27, 2010, 6:34 AM by Sachchida Ojha

SELECT l.namespace, l.reloads, UPPER(i.instance_name) instance_name
FROM   v$librarycache l, v$instance i
ORDER BY 1

Tablespace near 90% capacity

posted Sep 27, 2010, 6:32 AM by Sachchida Ojha

SELECT   *
    FROM (SELECT c.tablespace_name,
                 ROUND (A.BYTES / 1048576, 2) megs_allocacted,
                 ROUND (b.BYTES / 1048576, 2) megs_free,
                 ROUND ((A.BYTES - b.BYTES) / 1048576, 2) megs_used,
                 ROUND (b.BYTES / A.BYTES * 100, 2) pct_free,
                 ROUND ((A.BYTES - b.BYTES) / A.BYTES, 2) * 100 pct_used
            FROM (SELECT   tablespace_name, SUM (A.BYTES) BYTES,
                           MIN (A.BYTES) minbytes, MAX (A.BYTES) maxbytes
                      FROM SYS.dba_data_files A
                  GROUP BY tablespace_name) A,
                 (SELECT   A.tablespace_name, NVL (SUM (b.BYTES), 0) BYTES
                      FROM SYS.dba_data_files A, SYS.dba_free_space b
                     WHERE A.tablespace_name = b.tablespace_name(+)
                           AND A.file_id = b.file_id(+)
                  GROUP BY A.tablespace_name) b,
                 SYS.dba_tablespaces c
           WHERE A.tablespace_name = b.tablespace_name(+)
             AND A.tablespace_name = c.tablespace_name
             )
   WHERE pct_used >= 90
ORDER BY tablespace_name

Oracle Instance Initialization parameters

posted Sep 27, 2010, 6:31 AM by Sachchida Ojha

SELECT   a.NAME, a.VALUE, upper(i.instance_name) instance_name
FROM     v$parameter a, v$instance i
ORDER BY UPPER(NAME)

Non system objects in System Tablespace

posted Sep 27, 2010, 6:29 AM by Sachchida Ojha

SELECT owner, segment_name, partition_name,
       segment_type, ROUND(bytes / 1048576, 2) size_in_mb
FROM   dba_segments
WHERE  owner NOT IN ('SYS', 'SYSTEM')
AND    tablespace_name = 'SYSTEM'
ORDER BY owner, segment_type, segment_name

Shared pool loads

posted Sep 27, 2010, 6:29 AM by Sachchida Ojha

SELECT   doc.owner, doc.NAME, doc.TYPE, doc.loads, doc.sharable_mem,
         upper(ins.instance_name) instance_name
FROM     v$db_object_cache doc, v$instance ins
WHERE    doc.loads > 2
AND      doc.TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
ORDER BY doc.loads DESC

Invalid Objects

posted Sep 27, 2010, 6:27 AM by Sachchida Ojha

SELECT o.owner, o.object_name, o.object_type, o.LAST_DDL_TIME,
       DECODE(NVL(e.SEQUENCE, 0), 0, 'No', 'Yes') Has_errors
FROM   DBA_OBJECTS o, DBA_errors e
WHERE  o.status <> 'VALID'
AND    o.object_type <> 'SYNONYM'
AND    o.owner = e.owner (+)
AND    o.object_name = e.NAME (+)
AND    o.OBJECT_TYPE = e.TYPE (+)
AND    e.SEQUENCE (+) = 1
ORDER BY 1, 3, 2

1-10 of 10