4) PERFORMANCE TUNING
1)
Description: Anything appear in the result will be a locked object .
Code:
select o.object_name,l.oracle_username,l.os_user_name,l.session_id,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
2)
Description: Anything appear in the result will be a locked object .
Code:
select o.object_name,l.oracle_username,l.os_user_name,l.session_id,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
3)
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive
Code:
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4) Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.
select file#, PHYBLKRD, PHYBLKWRT from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
prompt NOTE: Only the top 10 files...
select * from ( select df.name, fs.phyblkrd - t.phyblkrd "Reads",fs.PHYBLKWRT - t.PHYBLKWRT "Writes",(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file# and t.file# = fs.file# and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10 ;
5) UNIDEXED TABLES
Description: This script will display the table name and column name of the tables that need to be indexed.
SELECT distinct t.owner,t.table_name,c.constraint_name,c.table_name table2,acc.column_name
FROM all_constraints t, all_constraints c, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name AND c.table_name =acc.table_name AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid WHERE aid.table_name = acc.table_name AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
6) UNINDEXED FOREIGN KEYS IN YOUR SCHEMA
select decode( b.table_name, NULL, 'UNINDEXED FK', 'INDEXED FK' ) Status,a.constraint_name,a.table_name, a.columns, b.columns,B.index_name
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%'
ORDER BY 2;
OR
SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position,c.constraint_name
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position, ic.COLUMN_NAME
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;
7) TABLE INDEX REPORT
Description: Table to Index Cross Reference Report
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;
OR
select ai.OWNER,ai.table_name, ai.index_name,ai.uniqueness, aic.column_name
from all_ind_columns aic , all_indexes ai
where aic.index_name = ai.index_name and aic.table_name = ai.table_name and ai.OWNER not in ('SYS','SYSTEM','SYSMAN','WMSYS','XDB')
order by ai.table_type, ai.table_name,ai.index_name, aic.column_position;
8) FOREIGN KEY WITHOUT INDEXES
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.
--ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name||'['||acc.position||'])'||' ***** Missing Index' "Missing Index" , acc.table_name
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN ( SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,acc.column_name, acc.position;
9) A common cause of deadlocks are missing indexes on foreign keys. You can use following query to check for these:
select c.owner, c.table_name, c.constraint_name
from dba_constraints c
where not exists ( select 1 from dba_cons_columns cc, dba_ind_columns ic
where cc.owner=c.owner and cc.table_name=c.table_name and cc.constraint_name=c.constraint_name and cc.owner=ic.table_owner
and cc.table_name=ic.table_name and cc.column_name = ic.column_name
)
and c.constraint_type = 'R'
10) Also, Arup Nanda has scripts for detecting ITL(Interested Transaction List) waits:
Select s.sid SID,s.serial# Serial#,l.type type,' ' object_name,lmode held,request request
from v$lock l,v$session s, v$process p
where s.sid = l.sid and s.username <> ' ' and s.paddr = p.addr and l.type <> 'TM' and (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,s.serial# Serial#,l.type type,object_name object_name,lmode held,request request
from v$lock l,v$session s,v$process p,sys.dba_objects o
where s.sid = l.sid and o.object_id = l.id1 and l.type = 'TM' and s.username <> ' ' and s.paddr = p.addr
union
select s.sid SID,s.serial# Serial#,l.type type,'(Rollback='||rtrim(r.name)||')' object_name,lmode held,request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and l.type = 'TX' and l.lmode = 6 and trunc(l.id1/65536) = r.usn and s.username <> ' ' and s.paddr = p.addr
order by 5, 6;
1)
Description: Anything appear in the result will be a locked object .
Code:
select o.object_name,l.oracle_username,l.os_user_name,l.session_id,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
2)
Description: Anything appear in the result will be a locked object .
Code:
select o.object_name,l.oracle_username,l.os_user_name,l.session_id,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
3)
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive
Code:
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4) Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.
select file#, PHYBLKRD, PHYBLKWRT from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
prompt NOTE: Only the top 10 files...
select * from ( select df.name, fs.phyblkrd - t.phyblkrd "Reads",fs.PHYBLKWRT - t.PHYBLKWRT "Writes",(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file# and t.file# = fs.file# and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10 ;
5) UNIDEXED TABLES
Description: This script will display the table name and column name of the tables that need to be indexed.
SELECT distinct t.owner,t.table_name,c.constraint_name,c.table_name table2,acc.column_name
FROM all_constraints t, all_constraints c, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name AND c.table_name =acc.table_name AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid WHERE aid.table_name = acc.table_name AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
6) UNINDEXED FOREIGN KEYS IN YOUR SCHEMA
select decode( b.table_name, NULL, 'UNINDEXED FK', 'INDEXED FK' ) Status,a.constraint_name,a.table_name, a.columns, b.columns,B.index_name
from
( select substr(a.table_name,1,30) table_name,
substr(a.constraint_name,1,30) constraint_name,
max(decode(position, 1, substr(column_name,1,30),NULL)) ||
max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name and b.constraint_type = 'R'
group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||
max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
from user_ind_columns
group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%'
ORDER BY 2;
OR
SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position,c.constraint_name
FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name
AND c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position, ic.COLUMN_NAME
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name
)
ORDER BY table_name, column_position;
7) TABLE INDEX REPORT
Description: Table to Index Cross Reference Report
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;
OR
select ai.OWNER,ai.table_name, ai.index_name,ai.uniqueness, aic.column_name
from all_ind_columns aic , all_indexes ai
where aic.index_name = ai.index_name and aic.table_name = ai.table_name and ai.OWNER not in ('SYS','SYSTEM','SYSMAN','WMSYS','XDB')
order by ai.table_type, ai.table_name,ai.index_name, aic.column_position;
8) FOREIGN KEY WITHOUT INDEXES
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.
--ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name||'['||acc.position||'])'||' ***** Missing Index' "Missing Index" , acc.table_name
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R' and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN ( SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,acc.column_name, acc.position;
9) A common cause of deadlocks are missing indexes on foreign keys. You can use following query to check for these:
select c.owner, c.table_name, c.constraint_name
from dba_constraints c
where not exists ( select 1 from dba_cons_columns cc, dba_ind_columns ic
where cc.owner=c.owner and cc.table_name=c.table_name and cc.constraint_name=c.constraint_name and cc.owner=ic.table_owner
and cc.table_name=ic.table_name and cc.column_name = ic.column_name
)
and c.constraint_type = 'R'
10) Also, Arup Nanda has scripts for detecting ITL(Interested Transaction List) waits:
Select s.sid SID,s.serial# Serial#,l.type type,' ' object_name,lmode held,request request
from v$lock l,v$session s, v$process p
where s.sid = l.sid and s.username <> ' ' and s.paddr = p.addr and l.type <> 'TM' and (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,s.serial# Serial#,l.type type,object_name object_name,lmode held,request request
from v$lock l,v$session s,v$process p,sys.dba_objects o
where s.sid = l.sid and o.object_id = l.id1 and l.type = 'TM' and s.username <> ' ' and s.paddr = p.addr
union
select s.sid SID,s.serial# Serial#,l.type type,'(Rollback='||rtrim(r.name)||')' object_name,lmode held,request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and l.type = 'TX' and l.lmode = 6 and trunc(l.id1/65536) = r.usn and s.username <> ' ' and s.paddr = p.addr
order by 5, 6;
No comments :
Post a Comment