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)/÷_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)/÷_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
Saturday, February 15, 2014
Tuesday, February 11, 2014
Snap Mirror Progress Monitor Tool
Snap Mirror Progress Monitor Tool
Great tool if really need to calculate your snap delta , snap mirror and time taken .
SnapMirror Progress Monitor reports and estimates the time to completion of volume SnapMirror update transfers. The tool is a JAR file. It probes the NetApp storage system and provides time to completion estimates such as percent complete and time remaining output of all active SnapMirror update transfers.
You can Download it from Here
Monday, February 10, 2014
Thursday, February 6, 2014
Subscribe to:
Posts (Atom)