Sql Queries for Finding Bottleneck on your Oracle Database

SQL Queries for Finding Bottleneck on your Oracle Database

1. Stalestats

set long 10000
set serveroutput on

declare
  a dbms_stats.objecttab;
begin
   dbms_stats.gather_database_stats
     (OPTIONS=>'LIST STALE',OBJLIST=>a);

   for i in 1 .. a.count
   loop
           dbms_output.put_line( a(i).ownname );
           dbms_output.put_line( a(i).objType );
           dbms_output.put_line( a(i).objName );
           dbms_output.put_line( a(i).PartName );
           dbms_output.put_line( a(i).subPartName );
           dbms_output.put_line( a(i).Confidence );
           dbms_output.put_line
                 ( '-------------------------' );
   end loop;
end;
/

2. System Stats
select pname, pval1 from sys.aux_stats$;

select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 scatterr,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 seqr,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';

3. Tail Indexes
select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3;

4.  Table Space Usuage

col tablespace_name format a15
col alloc_size format 999999.999
col pct_used format 9999999.999
col free_space format 9999999.999
col maxnext format 999999.999
col definitsz format 999999.999
col defnextsz format 9999999.999

SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
  SELECT a.tablespace_name, SUM (b.bytes)/1024/1024
  DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
    (
  SELECT a.tablespace_name, SUM (c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;

5.  Find Missing Foreign Keys
rem -----------------------------------------------------------------------
rem   Shows the foreign keys without appropiate index
rem -----------------------------------------------------------------------
rem
SET echo off
SET verify off
--
COLUMN OWNER noprint new_value own
COLUMN TABLE_NAME format a32 wrap heading "Table Name"
COLUMN CONSTRAINT_NAME format a32 wrap heading "Constraint Name"
COLUMN CONSTRAINT_TYPE format a3 heading "Typ"
COLUMN COLUMN_NAME format a32 wrap heading "Column"
BREAK ON OWNER skip page
--
SET TERMOUT ON
TTITLE  CENTER 'Unindexed Foreign Keys owned by Owner: ' own SKIP 2
PROMPT
PROMPT Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
PROMPT
PROMPT eg.:  SCOTT, S% OR %
PROMPT eg.:  EMP, E% OR %
PROMPT
--
ACCEPT vOwner prompt "Owner  <%>: " DEFAULT %
ACCEPT vTable prompt "Tables <%>: " DEFAULT %
--
SELECT OWNER, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
  FROM DBA_CONS_COLUMNS c
  WHERE position=1 AND
   (OWNER, TABLE_NAME, COLUMN_NAME) IN
   (SELECT c.OWNER, c.TABLE_NAME,cc.COLUMN_NAME
      FROM DBA_CONSTRAINTS  c, DBA_CONS_COLUMNS cc
     WHERE c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
       AND c.TABLE_NAME      = cc.TABLE_NAME
       AND c.OWNER           = cc.OWNER
       AND c.CONSTRAINT_TYPE = 'R'
       AND cc.POSITION       = 1
       AND c.OWNER           LIKE UPPER('&vOwner')
       AND c.TABLE_NAME      LIKE UPPER('&vTable')
     MINUS
    SELECT table_owner, table_name, column_name
      FROM DBA_IND_COLUMNS
     WHERE COLUMN_POSITION = 1
       AND TABLE_OWNER LIKE UPPER('&vOwner')
       AND TABLE_NAME  LIKE UPPER('&vTable')
  )
  ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME;
--
ttitle off
SET pause off
clear breaks

6.  Large Indexes

SELECT dbi.owner||'.'||dbi.index_name, dbi.owner||'.'||dbi.table_name, dbi.blevel BLVL, db1.blocks TABBLKS, db1.blocks*8192 TBLSZ, db2.blocks INDBLKS, db2.blocks*8192 INDSZ, db2.blocks/db1.blocks RATIO
  FROM dba_segments db1, dba_segments db2, dba_indexes dbi, dba_tables dbt
  WHERE dbt.table_name = dbi.table_name
  AND dbt.table_name = db1.segment_name
  AND dbi.index_name = db2.segment_name
  AND db1.blocks < db2.blocks
  AND ( (db2.blocks/db1.blocks > 64) or (db1.blocks > 10000))
ORDER BY 8;

7.  Redo Logs

col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
col 24 for a4


set line 300
set pages 180


col day for a10
select * from (select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')) order by 1
/

8. Index Fragmentation

select t.table_owner||'.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from analyzedb.table_modifications t,
all_tables a
where t.timestamp >= to_date('&from_date','dd-mon-yyyy') and
t.table_owner = a.owner and t.table_owner not in ('SYS','SYSTEM') and
t.table_name=a.table_name
having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
group by t.table_owner, t.table_name, a.num_rows
order by num_rows desc, t.table_owner, t.table_name;

9. Archive Daily Logs

select decode(grouping (trunc(COMPLETION_TIME)),1,'TOTAL',TRUNC(COMPLETION_TIME)) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by cube (trunc (COMPLETION_TIME)) order by 1;

10. Busy Buffers

col "Object" format a30
set numwidth 12
set lines 132
set pages 50
select * from(
select DECODE(GROUPING(a.object_name), 1, 'All Objects',
a.object_name) AS "Object",
sum(case when a.statistic_name = 'buffer busy waits'
then a.value else null end) "Buffer Busy Waits",
sum(case when a.statistic_name = 'physical reads'
then a.value else null end) "Physical_Reads",
sum(case when a.statistic_name = 'physical writes'
then a.value else null end) "Physical_writes",
sum(case when a.statistic_name = 'logical reads'
then a.value else null end) "Logical Reads"
from v$segment_statistics a
where a.owner like upper('%')
group by rollup(a.object_name)) b
where b."Buffer Busy Waits">0
order by 2
/
clear columns
ttitle off


11. Empty Stats

set long 10000
set serveroutput on

declare
  a dbms_stats.objecttab;
begin
   dbms_stats.gather_database_stats
     (OPTIONS=>'LIST EMPTY',OBJLIST=>a);

   for i in 1 .. a.count
   loop
           dbms_output.put_line( a(i).ownname );
           dbms_output.put_line( a(i).objType );
           dbms_output.put_line( a(i).objName );
           dbms_output.put_line( a(i).PartName );
           dbms_output.put_line( a(i).subPartName );
           dbms_output.put_line( a(i).Confidence );
           dbms_output.put_line
                 ( '-------------------------' );
   end loop;
end;
/

12.  File I/O

set pages 99
set lines 200

column name format a50

column sum_io1 new_value st1 noprint
column sum_io2 new_value st2 noprint
column sum_io new_value divide_by noprint
Select  nvl(sum(a.phyrds+a.phywrts),0) sum_io1 from     sys.v_$filestat a;
/
select  nvl(sum(b.phyrds+b.phywrts),0) sum_io2 From            sys.v_$tempstat b;
/
select &st1+&st2 sum_io from dual;
/
ttitle 'File IO Statistics Report'

spool fileio

select file#, name, PHYRDS "PhysReads",    PHYWRTS "PhysWrites",    round(PERCENT,2) "%ofIO",   PHYBLKRD "PhysBlockReads",  PHYBLKWRT "PhysBlockWrites",    round(BRRATIO,2) "Block Read Ratio",    round(BWRATIO,2) "Block Write Ratio" from (
select  a.file#,b.name, a.phyrds, a.phywrts,
        (100*(a.phyrds+a.phywrts)/&divide_by) Percent,
        a.phyblkrd, a.phyblkwrt, (a.phyblkrd/greatest(a.phyrds,1)) brratio,
      (a.phyblkwrt/greatest(a.phywrts,1)) bwratio
from    sys.v_$filestat a, sys.v_$dbfile b
where   a.file#=b.file#
union
select  c.file#,d.name, c.phyrds, c.phywrts,
        (100*(c.phyrds+c.phywrts)/&divide_by) Percent,
        c.phyblkrd, c.phyblkwrt,(c.phyblkrd/greatest(c.phyrds,1)) brratio,
      (c.phyblkwrt/greatest(c.phywrts,1)) bwratio
from    sys.v_$tempstat c, sys.v_$tempfile d
where   c.file#=d.file# )
order by        PERCENT desc ;

spool off