Script to Review Oracle Database (RAC)

-- Get some DB informations --

set lines 100

set pages 30

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';

select name, dbid, created, flashback_on,instance_number, log_mode ,instance_name, startup_time, status from gv$instance a,gv$database b where a.inst_id = b.inst_id order by instance_number;

-- Buffer Cache Hit ratio > 98 --

select inst_id, (1 - (sum(decode(name, 'physical reads',value,0)) /

       (sum(decode(name, 'db block gets',value,0)) +

       sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"

from   gv$sysstat group by inst_id;

In Oracle8.0 onwards:

SELECT inst_id, name, (1-(physical_reads / (consistent_gets + db_block_gets ) ) ) * 100 "Hit Ratio"

FROM GV$BUFFER_POOL_STATISTICS 

WHERE (consistent_gets + db_block_gets ) !=0  order by inst_id,name;

Change to Some Idea(8i - 9i):

      1 -  ( physical reads - (physical reads direct + physical reads direct (lob)) )

           -------------------------------------------------------------------------- 

     ( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )

column  "Hit Ratio" format 990.9 heading 'Buffer Cache|Hit Ratio'

select inst_id,

  (1 - (

       ( sum(decode(name,'physical reads',value,0)) -

         ( sum(decode(name,'physical reads direct',value,0)) +

           sum(decode(name,'physical reads direct (lob)',value,0))

         )

       ) /

       (

        ( sum(decode(name,'db block gets',value,0)) +

          sum(decode(name,'consistent gets',value,0))

        ) -

        ( sum(decode(name,'physical reads direct',value,0)) +

          sum(decode(name,'physical reads direct (lob)',value,0))

        )

       )

     )) * 100 as  "Hit Ratio"

from

  gv$sysstat group by inst_id;

/

-- Validate Physical I/O --

COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size in MB'

COLUMN buffers_for_estimate      FORMAT 999,999,999 heading 'Buffers'

COLUMN estd_physical_read_factor FORMAT 999.99 heading 'Estd Phys Read Fctr'

COLUMN estd_physical_reads       FORMAT 999,999,999 heading 'Estd Phys Reads'

SELECT inst_id, size_for_estimate,

       buffers_for_estimate,

       estd_physical_read_factor,

       estd_physical_reads

FROM GV$DB_CACHE_ADVICE

WHERE name = 'DEFAULT'

  AND block_size = (SELECT value FROM V$PARAMETER

                    WHERE name = 'db_block_size')

  AND advice_status = 'ON' order by inst_id, estd_physical_read_factor

/

-- Dictionary Hit Ratio > 98 --

select inst_id, (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from   gv$rowcache group by inst_id;

-- Library Cache Hit Ratio > 98 --

select inst_id, Sum(Pins) / (Sum(Pins) + Sum(Reloads)) * 100 from   gv$LibraryCache group by inst_id;

-- Review Automatic Shared Memory Management 10g (each of node) --

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';

SET PAGESIZE 900

SET LINESIZE 255

COL COMPONENT FORMAT A25

COL INITIAL_SIZE FORMAT A10

COL TARGET_SIZE FORMAT A10

COL FINAL_SIZE FORMAT A10

COL OPER_TYPE FORMAT A10

select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIME

from v$sga_resize_ops

order by start_time, component;

select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components;

select sum(value)/1024/1024 from v$sga;

select sum(bytes)/1024/1024  from v$sgastat;

select sum(current_size)/1024/1024  from v$sga_dynamic_components;

select * from v$sga_dynamic_free_memory;

-- PGA memory sort ratio > 98 --

select a.inst_id, a.value "Disk Sorts", b.value "Memory Sorts",

       round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)

       "Pct Memory Sorts"

from   gv$sysstat a, gv$sysstat b

where  a.inst_id = b.inst_id and a.name = 'sorts (disk)'

and    b.name = 'sorts (memory)' order by inst_id ;

--pga stats--

set lines 130

set pages 1000

select * from gv$pgastat;

-- check pga each session

set pages500 lines110 trims on

clear col

col name format a30

col username format a20

break on username nodup skip 1

select vses.username||':'||vsst.sid||','||vses.serial# username, vstt.name, max(vsst.value) value

from v$sesstat vsst, v$statname vstt, v$session vses

where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and vstt.name in

('session pga memory','session pga memory max','session uga memory','session uga memory max',

'session cursor cache count','session cursor cache hits','session stored procedure space',

'opened cursors current','opened cursors cumulative') and vses.username is not null

group by vses.username, vsst.sid, vses.serial#, vstt.name order by vses.username, vsst.sid, vses.serial#, vstt.name;

-- OR

set lines 110

col unm format a30 hea "USERNAME (SID,SERIAL#)"

col pus format 999,990.9 hea "PROC KB|USED"

col pal format 999,990.9 hea "PROC KB|MAX ALLOC"

col pgu format 99,999,990.9 hea "PGA KB|USED"

col pga format 99,999,990.9 hea "PGA KB|ALLOC"

col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"

select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,

round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,

round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm

from v$process_memory m, v$session s, v$process p

where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and

s.username is not null group by s.username, s.sid, s.serial# order by unm;

-- Free Data Buffer --

column  status format a10

select   inst_id, status, count(1) from   gv$bh group by inst_id,status order by inst_id, status;

-- check free data buffers each node.

select decode(state,0, 'FREE',

1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),

3, 'BEING USED', state) "BLOCK STATUS",

count(*)

from x$bh

group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',

'BEING USED'),3, 'BEING USED', state);

-- TOP 25 Statements Get READ (buffer) -- Each of nodes --

set serverout on size 1000000

set lines 200

declare

 top25 number;

 text1 varchar2(4000);

cursor c1 is

  select buffer_gets, substr(sql_text,1,4000)

  from v$sqlarea

  order by buffer_gets desc;

begin

 dbms_output.put_line('Gets'||'    '||'Text');

 dbms_output.put_line('----------'||' '||'----------------------');

 open c1;

 for i in 1..25 loop

  fetch c1 into top25, text1;

   dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,150));

  end loop;

end;

/

-- TOP 25 worst read disk -- Each of nodes --

set serverout on size 1000000

set lines 200

declare

  top25 number;

  text1 varchar2(4000);

cursor c1 is

   select disk_reads, substr(sql_text,1,4000)

   from v$sqlarea

   order by disk_reads desc;

begin

  dbms_output.put_line('Reads'||'   '||'Text');

  dbms_output.put_line('----------'||' '||'----------------------');

  open c1;

  for i in 1..25 loop

   fetch c1 into top25, text1;

   dbms_output.put_line(rpad(to_char(top25),9)||' '||substr(text1,1,150));

  end loop;

end;

/

-- Get Number of Undo Blocks (second) --

SELECT inst_id, (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM gv$undostat group by inst_id;

-- Get Undo space --

SELECT b.inst_id, (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

FROM   (SELECT value AS UR

        FROM   v$parameter

        WHERE name = 'undo_retention') a,

       (SELECT inst_id, (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS

        FROM   gv$undostat group by inst_id) b,

       (SELECT value AS DBS

        FROM   v$parameter

WHERE name = 'db_block_size') c order by b.inst_id;

-- Query rollback segment --

set lines 130

set pages 1000

column segment_name format a20

column  file_name  format a60

select segment_name, file_name from   dba_data_files, dba_rollback_segs where  dba_data_files.file_id = dba_rollback_segs.file_id;

-- Query tablespaces--

set linesize 130

set pages 1000

column dummy noprint

column  pct_used format 999.9       heading "%|Used"

column  name    format a19      heading "Tablespace Name"

column  Kbytes   format 999,999,999    heading "KBytes"

column  used    format 999,999,999   heading "Used"

column  free    format 999,999,999  heading "Free"

column  largest    format 999,999,999  heading "Largest"

column  max_size format 999,999,999 heading "MaxPoss|Kbytes"

column  pct_max_used format 999.9       heading "%|Max|Used"

break   on report

compute sum of kbytes on report

compute sum of free on report

compute sum of used on report                                                                                                       

select (select decode(extent_management,'LOCAL','*',' ') ||

               decode(segment_space_management,'AUTO','a ','m ')

          from dba_tablespaces where tablespace_name = b.tablespace_name) ||

nvl(b.tablespace_name,

             nvl(a.tablespace_name,'UNKOWN')) name,

       kbytes_alloc kbytes,

       kbytes_alloc-nvl(kbytes_free,0) used,

       nvl(kbytes_free,0) free,

       ((kbytes_alloc-nvl(kbytes_free,0))/

                          kbytes_alloc)*100 pct_used,

       nvl(largest,0) largest,

       nvl(kbytes_max,kbytes_alloc) Max_Size,

       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used

from ( select sum(bytes)/1024 Kbytes_free,

              max(bytes)/1024 largest,

              tablespace_name

       from  sys.dba_free_space

       group by tablespace_name ) a,

     ( select sum(bytes)/1024 Kbytes_alloc,

              sum(maxbytes)/1024 Kbytes_max,

              tablespace_name

       from sys.dba_data_files

       group by tablespace_name

       union all

      select sum(bytes)/1024 Kbytes_alloc,

              sum(maxbytes)/1024 Kbytes_max,

              tablespace_name

       from sys.dba_temp_files

       group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name;

-- Get users + quotas --

set lines 130

set pages 1000

column username format a30

column tablespace_name format a30

select username, tablespace_name, (bytes/1024/1024) used_MByte, decode (max_bytes,-1,'unlimited',(max_bytes/1024/1024)) max_MByte from dba_ts_quotas;

-- Invalid Objects --

set lines 100

set pages 1000

column owner format a30

column object_type format a15

column object_name format a30

select OWNER, OBJECT_TYPE ,OBJECT_NAME ,STATUS from dba_objects where status='INVALID' order by 1,2,3;

-- TABLES + INDEXES on SYSTEM + SYSAUX TBS --

set lines 130

set pages 100

select owner, table_name, tablespace_name from dba_tables where owner  in  (select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')) and tablespace_name in ('SYSTEM','SYSAUX');

select owner, index_name, tablespace_name from dba_indexes where owner  in  (select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP')) and tablespace_name in ('SYSTEM','SYSAUX');

by: surachart