######################################################################################
0.DISK ERRORS :
######################################################################################
errpt
######################################################################################
1. Check the database AND LISTENER status
######################################################################################
ps -ef|grep pmon |grep -v
set linesize 200
col HOST_NAME for a20
select NAME,INSTANCE_NAME,LOG_MODE,OPEN_MODE,INSTANCE_NAME,HOST_NAME,STATUS,LOGINS,STARTUP_TIME from gv$instance, gv$database;
select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from gv$database;
select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from v$database;
select INSTANCE_NAME,HOST_NAME,status from gv$instance;
##############################################################################################
CHECK ANY ALERTS
##############################################################################################
select object_name, reason, metric_value, message_level,creation_time from sys.dba_outstanding_alerts;
###########################################################################################
2. Check listener status
###########################################################################################
lsnrctl status <LISTENERNAME>
###########################################################################################
3. Check alert log files
###########################################################################################
4.Check the database statistics
######################################################################################
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED,STATTYPE_LOCKED from dba_tab_statistics where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name' order by 4
exec dbms_stats.gather_table_stats(ownname =>'ST_APP_1', tabname =>'TBF0_RX',estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
exec dbms_stats.gather_schema_stats(ownname => 'SYS', estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
select owner,table_name,last_analyzed from dba_tables where table_name='DEST';
select table_name,last_analyzed from dba_tables where owner='ABC' and last_analyzed like '18-NOV-14%';
######################################################################################
5. INVALID OBJECTS
######################################################################################
set linesize 200
col owner for a25
col OBJECT_NAME for a35
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by OWNER,object_type order by 1,2;
SELECT OWNER,OBJECT_NAME,STATUS FROM dba_objects WHERE STATUS='INVALID';
######################################################################################
5. Check the status and no of connections present in the database
######################################################################################
Log on Time:
============
select STATTYPE_LOCKED from dba_tab_statistics where STATTYPE_LOCKED='ALL';
select table_name,last_analyzed from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%';
select count(table_name) from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%'
select count(*) from dba_tables where owner='SYS';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr
and a.status='INACTIVE'
and a.logon_time < sysdate -8
order by 4;
SET LINESIZE 200
COL MACHINE FOR A20
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.status='INACTIVE' and a.username not in ('SYS','SYSTEM','DBSNMP');
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' and sid=
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy') logon,count(*),a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' group by a.username,to_char(a.logon_time,'dd:mm:yyyy'),a.status order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.sql_id='
select count(*) "Total Sessions" from v$session;
select username,status,count(*) from v$session group by username,status where status='INACTIVE';
select username,status from v$session group by username,status where status='INACTIVE';
select username,ACCOUNT_STATUS FROM dba_users
select username,ACCOUNT_STATUS FROM dba_users
select username,status,count(*) from v$session group by username,status;
select INST_ID,status,count(*) from gv$session group by INST_ID,status order by 1;
select INST_ID,status,username,count(*) from gv$session group by INST_ID,status,username order by 1;
select INST_ID,username,status,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP')
group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
set linesize 300
col USERNAME for a12
col status for a15
select INST_ID,username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine,sql_id
ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE' group by
INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;
select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 2;
select username,status,count(*) from v$session group by username,status ORDER BY 2;
select inst_id,count(*) from gv$session group by inst_id;
select username,status,machine,count(*) from v$session where status='ACTIVE' group by username,status,machine ORDER BY 3;
select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 3;
select username,status,count(*) from v$session where status='ACTIVE' group by username,status ORDER BY 3;
select username,status,machine,count(*) from v$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') group by username,status,machine ORDER BY 4;
select INST_ID,username,status,count(*) from gv$session group by INST_ID,username,status ORDER BY 2;
select username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') and status='ACTIVE' group by username,status ORDER BY 3;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,LOGON_TIME from gv$session where username='ABALASUBRA';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE' ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine order by 1;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
select username,sql_id,status,machine,count(*) from gv$session group by username,status,machine,sql_id ORDER BY 2 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select INST_ID,username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine ORDER BY 1,5 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='INACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select owner,table_name,last_analyzed from dba_tables where owner not in ('SYS','SYSTEM','DBSNMP',
'TEMPDBA','SYSMAN','OUTLN','APPQOSSYS','WMSYS','CTXSYS','EXFSYS','XDB','ORDDATA','MDSYS','ORDSYS','OLAPSYS','APEX_030200',');
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
SELECT SID,SERIAL#,STATUS,MACHINE FROM V$SESSION WHERE SQL_ID='fwbqur34qynp3';
####################################################################################################################################
query to find the event
select SID,SERIAL# ,USERNAME,STATUS,machine,SQL_ID,LOGON_TIME,EVENT,PREV_SQL_ID from v$session where SID in ('5377','1138');
##############################################################################################################
Long running queries:
######################
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='RPTUSER' order by 7;
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.sql_id='8fkjugyagf3m7';
SELECT s.username,
sl.sid,
sl.last_update_time,
sl.sql_id,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
##############################################################################################################
ACTIVE SESSIONS:
###################################################################################################################################################
set linesize 200
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
set long 50000
select SQL_FULLTEXT from gv$sqlarea where SQL_ID='&sql_id';
ORDER BY SQL_ID
===============
select sql_id,username,status,count(*) from v$session group by sql_id,username,status;
select sql_id,username,status,count(*) from v$session where status='ACTIVE' group by sql_id,username,status;
########################################################################################################
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid from v$session a, v$process b
where a.paddr=b.addr and a.process='20';
##############################################################################################################
To find SPID:
##############################################################################################################
PROMPT ENTER SID
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='ACTIVE';
where a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&spid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='INACTIVE';
#####################################################################################################################################################################
BLOCKING SESSION HISTORY:
##############################################################################################################
set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct
a.sql_id ,
a.inst_id,
a.blocking_session blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,
a.SAMPLE_TIME
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time > sysdate - 1
/
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');
####################################################################################################################################
TO FIND SID:
##############################################################################################################
PROMPT "ENTER SPID"
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&SPID';
select sid,SQL_ID,SQL_HASH_VALUE from v$session where SID=291;
sql id coint:
=============
select sql_id,count(*) from v$session where status='ACTIVE' group by sql_id;
select sql_id,count(*) from v$session group by sql_id;
select sql_id,MACHINE,count(*) from v$session where status='ACTIVE' group by sql_id,machine;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.username='HAIDB';
order by 3,2;
##############################################################################################################
kILL SESSION:
##############################################################################################################
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ARCH_ONLINE' and status='ACTIVE' and SQL_ID='dvfsf0b47w6nb';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where SQL_ID='1fjaxytc89h8n';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ABC' and status='ACTIVE';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='HAIDB' and status='ACTIVE';
1) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='ACTIVE';
2) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';
Kill the sessions older than 2 days:
##############################################################################################################
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session a
where a.status='INACTIVE' and a.username='HAIDB' and a.last_call_et > 48*60*60;
##############################################################################################################
3) select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' from v$session a, v$process b where a.username='ABC' and status='INACTIVE'
and a.paddr=b.addr AND b.spid in
(
'36503568',
'25886892',
'47775850'
);
##############################################################################################################
4) To find all the sessions that have been inactive for more than 2 hours, your query would look something like:
##############################################################################################################
SELECT * FROM v$session WHERE STATUS != 'ACTIVE' AND last_call_et > 2*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' order by 3;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 48*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 24*60*60 order by 3;
select count(*) from v$session where username='HAIDB';
########################################################################################################
6. Max session is allowed in the database
show parameter session
7. Check for Blocking sessions
8. Check for any long running jobs
9. CPU usage
top
topas
##############################################################################################################
10 .Check any mount point is reached 100%
df -k|grep 100
df -g|grep 100
df -h|grep 100
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';
######################################################################################
11. Locked object details
######################################################################################
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
######################################################################################
Session,locks and tables and sql information:
##############################################################################################################
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE
L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;
######################################################################################
12. Recently modified objects
######################################################################################
13. Tablespaces reached 100%
######################################################################################
set pagesize 300
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100) > 0
order by 5;
select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 90;
######################################################################################
14. Wait events
######################################################################################
Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v$system_wait_class
Group by wait_class Order by 3 desc;
######################################################################################
15.INVALID OBJECTS
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
######################################################################################
15. unusable indexes -- completed
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND status != 'VALID'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'
)
)
)
);
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
##############################################################################################################
16. recently modified objects
######################################################################################
17. FRA - completed
set linesize 200
col FILE_TYPE for a25
select * from v$flash_recovery_area_usage;
######################################################################################
select * from v$resource_limit;
######################################################################################
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.INST_ID,a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from gv$session a , gv$sql b, gv$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and a.INST_ID=c.INST_ID
and a.INST_ID=b.INST_ID
and status = 'ACTIVE';
select SQL_FULLTEXT from v$sqlarea where SQL_ID='2j802qu4vj37x';
18.
##############################################################################################################
select INDEX_NAME,STATUS,PARTITION_NAME,INDEX_OWNER from dba_ind_partitions where INDEX_NAME='UK_FACT_COMMUNICATION_01' order by 3;
##############################################################################################################
19.
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;
##############################################################################################################
20.
set linesize 200
col REASON for a65
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
col MESSAGE_LEVEL for 9999
col OWNER for a10
select OWNER,OBJECT_NAME,REASON ,OBJECT_TYPE,MESSAGE_TYPE,METRIC_VALUE,MESSAGE_LEVEL from dba_outstanding_alerts;
set linesize 200
select
to_char(creation_time, 'dd-mm-yyyy hh24:mi') crt,
instance_name,
object_type,
message_type,
message_level,
reason,
suggested_action
from
dba_outstanding_alerts
order by
creation_time;
select
object_type,
object_name,
reason,
suggested_action,
time_suggested,
resolution,
advisor_name,
metric_value,
message_type,
message_group,
message_level
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
col REASON for a55
set linesize 200
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
select
object_type,
object_name,
reason,
suggested_action
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
######################################################################################
Identify database idle sessions
######################################################################################
set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;
########################################################################################################################################
CHECK INVALIDS:
########################################################################################################################################
set linesize 300
set pause off
set pagesize 1000
column owner format a20
column object_name format a40
column index_name format a40
column object_type format a10
column status format a10
prompt OBJECTS INVALID:
select substr(owner,1,20) owner, substr(object_name,1,30) object_name,
substr(object_type,1,10) object_type, status
from dba_objects
where status<>'VALID'
and owner not in ('SYS','SYSTEM')
order by 1,2,3;
prompt
prompt
prompt INDEXES INVALID:
select substr(owner,1,20) owner, substr(index_name,1,40) index_name,
status from dba_indexes
where status not in ('VALID','N/A')
and owner not in ('SYS','SYSTEM')
order by 1,2,3;
########################################################################################################################
Hang Aaalyze :
http://www.dbi-services.com/index.php/blog/entry/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate
########################################################################################################################
WAIT EVENTS:
============
1)WITH SQL ID
select
swh.seq#,
sess.sid,
sess.username username,
swh.event event,
swh.p1,
swh.p2
from
v$session sess,
v$session_wait_history swh
where
sess.sid = 74
and
sess.sid = swh.sid
order by
swh.seq#;
######################################################################################
Check for Fragmentation in table:
######################################################################################
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size
we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables
where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
/
######################################################################################
A. Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
B. List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, s.program,s.module,se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.program like '%rman%'
and s.username is not null
order by se.wait_time;
######################################################################################
c. Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum
######################################################################################
D. Top CPU Consuming SQL During A Certain Time Period
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < =5;
######################################################################################
E. Which Database Objects Experienced the Most Number of Waits in the Past One Hour
######################################################################################
set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.owner,dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.owner,dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
E. Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum
######################################################################################
E. Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum <=5;
######################################################################################
F. Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='09-DEC-2014';
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
G.Analyse a particular SQL ID and see the trends for the past day
######################################################################################
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
####################################################################################################################################################################
H.Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#####################################################################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in ('&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
######################################################################################
Script – What Wait Events Are Sessions Waiting On
######################################################################################
set linesize 120
col username format a10
col event format a30
select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
######################################################################################
Script – Sessions Waiting On A Particular Wait Event
######################################################################################
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
######################################################################################
Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
Top Wait Events Since Instance Startup
######################################################################################
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
######################################################################################
List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
######################################################################################
Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum;
######################################################################################
Top CPU Consuming SQL During A Certain Time Period:
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
#Which Database Objects Experienced the Most Number of Waits in the Past One Hour#####
######################################################################################
set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum < 6;
######################################################################################
Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum;
######################################################################################
SQL with the highest I/O in the past one day
######################################################################################
select * from
(
SELECT /*+LEADING(x h) USE_NL(h)*/
h.sql_id
, SUM(10) ash_secs
FROM dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE x.begin_interval_time > sysdate -1
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.event in ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum;
######################################################################################
#Top CPU consuming queries since past one day
######################################################################################
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
Analyse a particular SQL ID and see the trends for the past day
######################################################################################
analysis_sql_past_1day.sql
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
#######################################################################################################################
Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#######################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
#######################################################################################################################
Top 5 Queries for past week based on ADDM recommendations
#######################################################################################################################
/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
#######################################################################################################################
-- Top 10 CPU consumers in last 5 minutes
#######################################################################################################################
SQL> select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
#######################################################################################################################
Script – Segments with highest I/O activity
#######################################################################################################################
This script will list the top 10 segments in the database that have the most number of
physical reads against them.
Script can also be changed to query on 'physical writes' instead.
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
#######################################################################################################################
Script – Monitor sessions with high Physical Reads
#######################################################################################################################
set linesize 120
col osuser format a10
col username format 10
select
OSUSER osuser,
username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS;
#######################################################################################################################
Script – Latch Contention (top 5 latches)
#######################################################################################################################
This script will display the top 5 latches with the most sleeps.
Script can be changed to even sort the display on misses instead.
set linesize 120
col name format a30
select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;
#######################################################################################################################
BACKUP CHECKS:
#######################################################################################################################
1.Script – Check RMAN Backup Status
#######################################################################################################################
Scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.
Login as sysdba -
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
#######################################################################################################################
2.This script will report all on full and incremental backups, not archivelog backups -
#######################################################################################################################
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;
#######################################################################################################################
Script – Query the RMAN catalog to list backup completion status
#######################################################################################################################
Note – run this query connected as the owner of the RMAN catalog
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
#######################################################################################################################
Description : Displays a list of tablespaces that are nearly full.
#######################################################################################################################
SET PAGESIZE 100
PROMPT Tablespaces utilization is more than 90%
PROMPT ********************************************************
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
WHERE ROUND((max_free_mb/max_size_mb) * 100,2) < 30;
#############################################################################################
Details for count of connections per service on each node
#############################################################################################
select ash.instance_number, network_name, count(*)
from dba_hist_active_sess_history ash, gv$active_services ase
where sample_time > to_date('25-AUG-2014 00:00:00','DD-MON-YYYY HH24:MI:SS')
and sample_time < to_date('25-AUG-2014 23:59:00','DD-MON-YYYY HH24:MI:SS')
and service_hash=name_hash
and ash.instance_number=ase.inst_id
group by ash.instance_number,network_name
order by ash.instance_number;
WAIT EVENTS:
############################################################################################################################
Script – Sessions Waiting On A Particular Wait Event
############################################################################################################################
set pagesize 200
set linesize 300
col event for a55
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
Script – What Wait Events Are Sessions Waiting On
###############################################################
set linesize 120
col username format a10
col event format a30
select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
col "Description" format a50
select USERNAME,sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session where status='ACTIVE';
############################################################################################################################
SQL> set linesize 200
SQL> SELECT * FROM
(SELECT
sql_fulltext,
sql_id,?
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ;
#######################################################################################
FIND THE QUERY
SET PAGES 5000 TERM OFF FEEDBACK OFF MARKUP HTML ON PREFORMAT OFF ENTMAP ON
set verify off
spool 20th_report_sql_detail.html
set lines 300
set long 9999999
select a.SESSION_ID, a.SAMPLE_TIME, a.USER_ID, (select username from dba_users where a.user_id=user_id) as username, b.SQL_ID, b.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b
where a.sql_id=b.sql_id and upper(b.sql_text) like '%FROM INVOICES%'
and a.snap_id>=53363 and a.snap_id<=53386;
spool off
set markup html off
EOF
SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT;
Check in OEM -> FIND SQL
#######################################################################################
Recipe #1 - Get details about long running operations
Run the following query and you'll get most information you need:
#######################################################################################
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0Recipe #2 - Show long running SQL Statements
#######################################################################################
If you also want to see the SQL query causing the long running operation, just join v$sql like show below:
#######################################################################################
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
######################################################################
LONG RUNNING QUERIES - FINAL
######################################################################
break on s.sql_id ON s.username DUPLICATES
select s.sql_id,s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by s.sql_id,sid,serial#,q.piece;
######################################################################################
CHANGE BASE LINE
######################################################################################
1)
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans added by the optimizer to the
plan history of existing plan baselines. If the function can verify that
the new plan performs better than a plan chosen from the corresponding SQL plan baseline, the new plan is added as an accepted plan.
==============================================================================================================================================
2)
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_794f2b0bd2ff2fa6');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
3)
To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:
==============================================================================================================================================
select * from table(
dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_794f2b0bd2ff2fa6',format=>'basic'));
######################################################################################
The Number of ASM Disks and their Ownerships (grid, asmadmin) should be Correct.
The Major and Minor Numbers should be the same as Primary. Kfod command can be used to validate the disks.
If the disks are not listed in kfod, please check with Server team to rediscover the disks.
kfod asm_diskstring='/dev/oradisk/ASM/vot*' disks=all
######################################################################################
FAQ
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
http://select-star-from.blogspot.com/2013/07/oracle-dba-interview-questions-answers_24.html
######################################################################################
0.DISK ERRORS :
######################################################################################
errpt
######################################################################################
1. Check the database AND LISTENER status
######################################################################################
ps -ef|grep pmon |grep -v
set linesize 200
col HOST_NAME for a20
select NAME,INSTANCE_NAME,LOG_MODE,OPEN_MODE,INSTANCE_NAME,HOST_NAME,STATUS,LOGINS,STARTUP_TIME from gv$instance, gv$database;
select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from gv$database;
select NAME,LOG_MODE,OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from v$database;
select INSTANCE_NAME,HOST_NAME,status from gv$instance;
##############################################################################################
CHECK ANY ALERTS
##############################################################################################
select object_name, reason, metric_value, message_level,creation_time from sys.dba_outstanding_alerts;
###########################################################################################
2. Check listener status
###########################################################################################
lsnrctl status <LISTENERNAME>
###########################################################################################
3. Check alert log files
###########################################################################################
4.Check the database statistics
######################################################################################
select OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED,STATTYPE_LOCKED from dba_tab_statistics where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name';
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE from dba_tab_PARTITIONs where TABLE_NAME='&Table_Name' order by 4
exec dbms_stats.gather_table_stats(ownname =>'ST_APP_1', tabname =>'TBF0_RX',estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
exec dbms_stats.gather_schema_stats(ownname => 'SYS', estimate_percent =>20, method_opt => 'for all indexed columns size auto', cascade =>TRUE);
select owner,table_name,last_analyzed from dba_tables where table_name='DEST';
select table_name,last_analyzed from dba_tables where owner='ABC' and last_analyzed like '18-NOV-14%';
######################################################################################
5. INVALID OBJECTS
######################################################################################
set linesize 200
col owner for a25
col OBJECT_NAME for a35
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by OWNER,object_type order by 1,2;
SELECT OWNER,OBJECT_NAME,STATUS FROM dba_objects WHERE STATUS='INVALID';
######################################################################################
5. Check the status and no of connections present in the database
######################################################################################
Log on Time:
============
select STATTYPE_LOCKED from dba_tab_statistics where STATTYPE_LOCKED='ALL';
select table_name,last_analyzed from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%';
select count(table_name) from dba_tables where owner='SYS' and last_analyzed like '27-NOV-14%'
select count(*) from dba_tables where owner='SYS';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr
and a.status='INACTIVE'
and a.logon_time < sysdate -8
order by 4;
SET LINESIZE 200
COL MACHINE FOR A20
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.status='INACTIVE' and a.username not in ('SYS','SYSTEM','DBSNMP');
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' and sid=
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy') logon,count(*),a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='HAI' group by a.username,to_char(a.logon_time,'dd:mm:yyyy'),a.status order by 2;
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status,a.MACHINE,a.PROGRAM,a.SQL_ID from gv$session a, gv$process b
where a.sql_id='
select count(*) "Total Sessions" from v$session;
select username,status,count(*) from v$session group by username,status where status='INACTIVE';
select username,status from v$session group by username,status where status='INACTIVE';
select username,ACCOUNT_STATUS FROM dba_users
select username,ACCOUNT_STATUS FROM dba_users
select username,status,count(*) from v$session group by username,status;
select INST_ID,status,count(*) from gv$session group by INST_ID,status order by 1;
select INST_ID,status,username,count(*) from gv$session group by INST_ID,status,username order by 1;
select INST_ID,username,status,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP')
group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where status='ACTIVE' and username not in ('SYS','SYSTEM','DBSNMP') group by
INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
set linesize 300
col USERNAME for a12
col status for a15
select INST_ID,username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status ORDER BY 4;
select INST_ID,username,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') group by INST_ID,username,status,machine,sql_id
ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP') and status='ACTIVE' group by
INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;
select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 2;
select username,status,count(*) from v$session group by username,status ORDER BY 2;
select inst_id,count(*) from gv$session group by inst_id;
select username,status,machine,count(*) from v$session where status='ACTIVE' group by username,status,machine ORDER BY 3;
select username,status,machine,count(*) from v$session group by username,status,machine ORDER BY 3;
select username,status,count(*) from v$session where status='ACTIVE' group by username,status ORDER BY 3;
select username,status,machine,count(*) from v$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') group by username,status,machine ORDER BY 4;
select INST_ID,username,status,count(*) from gv$session group by INST_ID,username,status ORDER BY 2;
select username,status,count(*) from gv$session where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA') and status='ACTIVE' group by username,status ORDER BY 3;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,LOGON_TIME from gv$session where username='ABALASUBRA';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE';
select INST_ID,username,status,machine,LOGON_TIME from gv$session where status='INACTIVE' ORDER BY 5;
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine order by 1;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 6 DESC;
select username,sql_id,status,machine,count(*) from gv$session group by username,status,machine,sql_id ORDER BY 2 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by username,status,machine ORDER BY 4 DESC;
select INST_ID,username,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine ORDER BY 1,5 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 1,6 DESC;
select INST_ID,username,sql_id,status,machine,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='INACTIVE'
group by INST_ID,username,status,machine,sql_id ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select owner,table_name,last_analyzed from dba_tables where owner not in ('SYS','SYSTEM','DBSNMP',
'TEMPDBA','SYSMAN','OUTLN','APPQOSSYS','WMSYS','CTXSYS','EXFSYS','XDB','ORDDATA','MDSYS','ORDSYS','OLAPSYS','APEX_030200',');
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
SELECT SID,SERIAL#,STATUS,MACHINE FROM V$SESSION WHERE SQL_ID='fwbqur34qynp3';
####################################################################################################################################
query to find the event
select SID,SERIAL# ,USERNAME,STATUS,machine,SQL_ID,LOGON_TIME,EVENT,PREV_SQL_ID from v$session where SID in ('5377','1138');
##############################################################################################################
Long running queries:
######################
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.username='RPTUSER' order by 7;
select a.INST_ID,a.sid,a.serial#,a.username,a.process,a.LAST_CALL_ET,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b
where a.paddr=b.addr and a.sql_id='8fkjugyagf3m7';
SELECT s.username,
sl.sid,
sl.last_update_time,
sl.sql_id,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
##############################################################################################################
ACTIVE SESSIONS:
###################################################################################################################################################
set linesize 200
select INST_ID,username,status,machine,count(*) from gv$session group by INST_ID,username,status,machine ORDER BY 5 DESC;
select INST_ID,username,status,machine,count(*) from gv$session where status='ACTIVE' group by INST_ID,username,status,machine ORDER BY 5 DESC;
set long 50000
select SQL_FULLTEXT from gv$sqlarea where SQL_ID='&sql_id';
ORDER BY SQL_ID
===============
select sql_id,username,status,count(*) from v$session group by sql_id,username,status;
select sql_id,username,status,count(*) from v$session where status='ACTIVE' group by sql_id,username,status;
########################################################################################################
select a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid from v$session a, v$process b
where a.paddr=b.addr and a.process='20';
##############################################################################################################
To find SPID:
##############################################################################################################
PROMPT ENTER SID
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='ACTIVE';
where a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and a.sid='&sid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&spid';
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.username='IEMPHSYS' and a.status='INACTIVE';
#####################################################################################################################################################################
BLOCKING SESSION HISTORY:
##############################################################################################################
set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct
a.sql_id ,
a.inst_id,
a.blocking_session blocker_ses,
a.blocking_session_serial# blocker_ser,
a.user_id,
s.sql_text,
a.module,
a.SAMPLE_TIME
FROM GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.sample_time > sysdate - 1
/
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');
####################################################################################################################################
TO FIND SID:
##############################################################################################################
PROMPT "ENTER SPID"
select a.sid,a.serial#,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon, a.status,a.paddr,b.addr,b.spid,a.module from v$session a, v$process b where
a.paddr=b.addr and b.spid='&SPID';
select sid,SQL_ID,SQL_HASH_VALUE from v$session where SID=291;
sql id coint:
=============
select sql_id,count(*) from v$session where status='ACTIVE' group by sql_id;
select sql_id,count(*) from v$session group by sql_id;
select sql_id,MACHINE,count(*) from v$session where status='ACTIVE' group by sql_id,machine;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.username='HAIDB';
order by 3,2;
##############################################################################################################
kILL SESSION:
##############################################################################################################
select INST_ID,username,sql_id,status,count(*) from gv$session
where username not in ('SYS','SYSTEM','DBSNMP','TEMPDBA')
and status='ACTIVE'
group by INST_ID,username,sql_id,status ORDER BY 5 DESC;
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ARCH_ONLINE' and status='ACTIVE' and SQL_ID='dvfsf0b47w6nb';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where SQL_ID='1fjaxytc89h8n';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='ABC' and status='ACTIVE';
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='HAIDB' and status='ACTIVE';
1) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='ACTIVE';
2) select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';
Kill the sessions older than 2 days:
##############################################################################################################
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session a
where a.status='INACTIVE' and a.username='HAIDB' and a.last_call_et > 48*60*60;
##############################################################################################################
3) select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' from v$session a, v$process b where a.username='ABC' and status='INACTIVE'
and a.paddr=b.addr AND b.spid in
(
'36503568',
'25886892',
'47775850'
);
##############################################################################################################
4) To find all the sessions that have been inactive for more than 2 hours, your query would look something like:
##############################################################################################################
SELECT * FROM v$session WHERE STATUS != 'ACTIVE' AND last_call_et > 2*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' order by 3;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 48*60*60;
select a.INST_ID,a.username,to_char(a.logon_time,'dd:mm:yyyy hh24:mi:ss') logon,a.status from gv$session a, gv$process b where a.paddr=b.addr
and a.status='INACTIVE' and a.username='HAIDB_MDM_OWNER' and a.last_call_et > 24*60*60 order by 3;
select count(*) from v$session where username='HAIDB';
########################################################################################################
6. Max session is allowed in the database
show parameter session
7. Check for Blocking sessions
8. Check for any long running jobs
9. CPU usage
top
topas
##############################################################################################################
10 .Check any mount point is reached 100%
df -k|grep 100
df -g|grep 100
df -h|grep 100
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';
select * from v$session_event where sid= (SELECT sid from v$session where audsid=USERENV('SESSIONID')) and event='virtual circuit wait';
######################################################################################
11. Locked object details
######################################################################################
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON
######################################################################################
Session,locks and tables and sql information:
##############################################################################################################
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE
L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;
######################################################################################
12. Recently modified objects
######################################################################################
13. Tablespaces reached 100%
######################################################################################
set pagesize 300
select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp and round((1-(b.fsz/a.tsz))*100) > 0
order by 5;
select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 90;
######################################################################################
14. Wait events
######################################################################################
Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v$system_wait_class
Group by wait_class Order by 3 desc;
######################################################################################
15.INVALID OBJECTS
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
######################################################################################
15. unusable indexes -- completed
######################################################################################
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where STATUS='!VALID' group by OWNER,OBJECT_TYPE,status;
select OWNER,OBJECT_TYPE,status,count(*) from dba_objects where status='INVALID' group by OWNER,OBJECT_TYPE,status;
SELECT owner,index_name,TABLE_OWNER,TABLE_NAME,PARTITIONED,TABLESPACE_NAME,STATUS
FROM all_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM')
AND status != 'VALID'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_partitions
WHERE status != 'USABLE'
AND
(
status != 'N/A'
OR index_name IN
(
SELECT index_name
FROM all_ind_subpartitions
WHERE status != 'USABLE'
)
)
)
);
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from USER_IND_PARTITIONS;
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
Select 'ALTER INDEX '||INDEX_OWNER||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME || ' TABLESPACE ' || tablespace_name ||';' from DBA_IND_PARTITIONS
where INDEX_OWNER='ERXUATOWNER' and status='UNUSABLE';
##############################################################################################################
16. recently modified objects
######################################################################################
17. FRA - completed
set linesize 200
col FILE_TYPE for a25
select * from v$flash_recovery_area_usage;
######################################################################################
select * from v$resource_limit;
######################################################################################
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from v$session a , v$sql b, v$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and status = 'ACTIVE';
col SID for 9999
col TILLNOW for 99999
col SQL_TEXT for a85
col TARGET for a21
set linesize 200 pages 200
select a.INST_ID,a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow from gv$session a , gv$sql b, gv$session_longops c
where a.sid=c.sid
and a.sql_address = b.address
and a.sql_address = c.sql_address
and a.INST_ID=c.INST_ID
and a.INST_ID=b.INST_ID
and status = 'ACTIVE';
select SQL_FULLTEXT from v$sqlarea where SQL_ID='2j802qu4vj37x';
18.
##############################################################################################################
select INDEX_NAME,STATUS,PARTITION_NAME,INDEX_OWNER from dba_ind_partitions where INDEX_NAME='UK_FACT_COMMUNICATION_01' order by 3;
##############################################################################################################
19.
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;
##############################################################################################################
20.
set linesize 200
col REASON for a65
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
col MESSAGE_LEVEL for 9999
col OWNER for a10
select OWNER,OBJECT_NAME,REASON ,OBJECT_TYPE,MESSAGE_TYPE,METRIC_VALUE,MESSAGE_LEVEL from dba_outstanding_alerts;
set linesize 200
select
to_char(creation_time, 'dd-mm-yyyy hh24:mi') crt,
instance_name,
object_type,
message_type,
message_level,
reason,
suggested_action
from
dba_outstanding_alerts
order by
creation_time;
select
object_type,
object_name,
reason,
suggested_action,
time_suggested,
resolution,
advisor_name,
metric_value,
message_type,
message_group,
message_level
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
col REASON for a55
set linesize 200
col OBJECT_TYPE for a20
col OBJECT_NAME for a20
select
object_type,
object_name,
reason,
suggested_action
from
dba_alert_history
where
-- creation_time <= sysdate-1 and
resolution = 'cleared' order by creation_time desc;
######################################################################################
Identify database idle sessions
######################################################################################
set linesize 140
col username format a15
col idle format a15
col program format a30
PROMPT Enter the number of minutes for which the sessions should have been idle:
PROMPT
select
sid,username,status,
to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "IDLE",
program
from
v$session
where
type='USER'
and (LAST_CALL_ET / 60) > &minutes
order by last_call_et;
########################################################################################################################################
CHECK INVALIDS:
########################################################################################################################################
set linesize 300
set pause off
set pagesize 1000
column owner format a20
column object_name format a40
column index_name format a40
column object_type format a10
column status format a10
prompt OBJECTS INVALID:
select substr(owner,1,20) owner, substr(object_name,1,30) object_name,
substr(object_type,1,10) object_type, status
from dba_objects
where status<>'VALID'
and owner not in ('SYS','SYSTEM')
order by 1,2,3;
prompt
prompt
prompt INDEXES INVALID:
select substr(owner,1,20) owner, substr(index_name,1,40) index_name,
status from dba_indexes
where status not in ('VALID','N/A')
and owner not in ('SYS','SYSTEM')
order by 1,2,3;
########################################################################################################################
Hang Aaalyze :
http://www.dbi-services.com/index.php/blog/entry/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate
########################################################################################################################
WAIT EVENTS:
============
1)WITH SQL ID
select
swh.seq#,
sess.sid,
sess.username username,
swh.event event,
swh.p1,
swh.p2
from
v$session sess,
v$session_wait_history swh
where
sess.sid = 74
and
sess.sid = swh.sid
order by
swh.seq#;
######################################################################################
Check for Fragmentation in table:
######################################################################################
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size
we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables
where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
/
######################################################################################
A. Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
B. List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, s.program,s.module,se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.program like '%rman%'
and s.username is not null
order by se.wait_time;
######################################################################################
c. Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum
######################################################################################
D. Top CPU Consuming SQL During A Certain Time Period
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < =5;
######################################################################################
E. Which Database Objects Experienced the Most Number of Waits in the Past One Hour
######################################################################################
set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.owner,dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.owner,dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
E. Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum
######################################################################################
E. Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum <=5;
######################################################################################
F. Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='09-DEC-2014';
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
G.Analyse a particular SQL ID and see the trends for the past day
######################################################################################
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
####################################################################################################################################################################
H.Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#####################################################################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in ('&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
######################################################################################
Script – What Wait Events Are Sessions Waiting On
######################################################################################
set linesize 120
col username format a10
col event format a30
select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
######################################################################################
Script – Sessions Waiting On A Particular Wait Event
######################################################################################
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
######################################################################################
Top Recent Wait Events
######################################################################################
col EVENT format a60
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/
######################################################################################
Top Wait Events Since Instance Startup
######################################################################################
col event format a60
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
where wait_class !='Idle'
group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;
######################################################################################
List Of Users Currently Waiting
######################################################################################
col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120
select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;
######################################################################################
Find The Main Database Wait Events In A Particular Time Interval
######################################################################################
First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum;
######################################################################################
Top CPU Consuming SQL During A Certain Time Period:
######################################################################################
Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
#Which Database Objects Experienced the Most Number of Waits in the Past One Hour#####
######################################################################################
set linesize 120
col event format a40
col object_name format a40
select * from
(
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc)
where rownum < 6;
######################################################################################
Top Segments ordered by Physical Reads
######################################################################################
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum < 6;
######################################################################################
Top 5 SQL statements in the past one hour
######################################################################################
select * from (
select active_session_history.sql_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
order by 4 desc )
where rownum;
######################################################################################
SQL with the highest I/O in the past one day
######################################################################################
select * from
(
SELECT /*+LEADING(x h) USE_NL(h)*/
h.sql_id
, SUM(10) ash_secs
FROM dba_hist_snapshot x
, dba_hist_active_sess_history h
WHERE x.begin_interval_time > sysdate -1
AND h.SNAP_id = X.SNAP_id
AND h.dbid = x.dbid
AND h.instance_number = x.instance_number
AND h.event in ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum;
######################################################################################
#Top CPU consuming queries since past one day
######################################################################################
select * from (
select
SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum < 6;
######################################################################################
Find what the top SQL was at a particular reported time of day
######################################################################################
First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.
select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;
select * from
(
select
sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and
s.snap_id= &snapid
order by
c3 desc)
where rownum < 6
/
######################################################################################
Analyse a particular SQL ID and see the trends for the past day
######################################################################################
analysis_sql_past_1day.sql
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/
#######################################################################################################################
Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance
#######################################################################################################################
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
#######################################################################################################################
Top 5 Queries for past week based on ADDM recommendations
#######################################################################################################################
/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
#######################################################################################################################
-- Top 10 CPU consumers in last 5 minutes
#######################################################################################################################
SQL> select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time > sysdate - interval '5' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;
#######################################################################################################################
Script – Segments with highest I/O activity
#######################################################################################################################
This script will list the top 10 segments in the database that have the most number of
physical reads against them.
Script can also be changed to query on 'physical writes' instead.
set pagesize 200
setlinesize 120
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads desc)
where rownum <=10;
#######################################################################################################################
Script – Monitor sessions with high Physical Reads
#######################################################################################################################
set linesize 120
col osuser format a10
col username format 10
select
OSUSER osuser,
username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS;
#######################################################################################################################
Script – Latch Contention (top 5 latches)
#######################################################################################################################
This script will display the top 5 latches with the most sleeps.
Script can be changed to even sort the display on misses instead.
set linesize 120
col name format a30
select * from
(select name, gets,misses, sleeps
from v$latch
order by sleeps desc)
where rownum < 6;
#######################################################################################################################
BACKUP CHECKS:
#######################################################################################################################
1.Script – Check RMAN Backup Status
#######################################################################################################################
Scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.
Login as sysdba -
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
#######################################################################################################################
2.This script will report all on full and incremental backups, not archivelog backups -
#######################################################################################################################
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;
#######################################################################################################################
Script – Query the RMAN catalog to list backup completion status
#######################################################################################################################
Note – run this query connected as the owner of the RMAN catalog
set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE
is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'))
/
#######################################################################################################################
Description : Displays a list of tablespaces that are nearly full.
#######################################################################################################################
SET PAGESIZE 100
PROMPT Tablespaces utilization is more than 90%
PROMPT ********************************************************
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
WHERE ROUND((max_free_mb/max_size_mb) * 100,2) < 30;
#############################################################################################
Details for count of connections per service on each node
#############################################################################################
select ash.instance_number, network_name, count(*)
from dba_hist_active_sess_history ash, gv$active_services ase
where sample_time > to_date('25-AUG-2014 00:00:00','DD-MON-YYYY HH24:MI:SS')
and sample_time < to_date('25-AUG-2014 23:59:00','DD-MON-YYYY HH24:MI:SS')
and service_hash=name_hash
and ash.instance_number=ase.inst_id
group by ash.instance_number,network_name
order by ash.instance_number;
WAIT EVENTS:
############################################################################################################################
Script – Sessions Waiting On A Particular Wait Event
############################################################################################################################
set pagesize 200
set linesize 300
col event for a55
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager',
'pmon timer','rdbms ipc message', 'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
Script – What Wait Events Are Sessions Waiting On
###############################################################
set linesize 120
col username format a10
col event format a30
select sid, serial#,username, event,
seconds_in_wait, wait_time
from v$session where state = 'WAITING'
and wait_class != 'Idle'
order by event
;
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
col "Description" format a50
select USERNAME,sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session where status='ACTIVE';
############################################################################################################################
SQL> set linesize 200
SQL> SELECT * FROM
(SELECT
sql_fulltext,
sql_id,?
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ;
#######################################################################################
FIND THE QUERY
SET PAGES 5000 TERM OFF FEEDBACK OFF MARKUP HTML ON PREFORMAT OFF ENTMAP ON
set verify off
spool 20th_report_sql_detail.html
set lines 300
set long 9999999
select a.SESSION_ID, a.SAMPLE_TIME, a.USER_ID, (select username from dba_users where a.user_id=user_id) as username, b.SQL_ID, b.sql_text
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b
where a.sql_id=b.sql_id and upper(b.sql_text) like '%FROM INVOICES%'
and a.snap_id>=53363 and a.snap_id<=53386;
spool off
set markup html off
EOF
SELECT SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT;
Check in OEM -> FIND SQL
#######################################################################################
Recipe #1 - Get details about long running operations
Run the following query and you'll get most information you need:
#######################################################################################
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0Recipe #2 - Show long running SQL Statements
#######################################################################################
If you also want to see the SQL query causing the long running operation, just join v$sql like show below:
#######################################################################################
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0;
######################################################################
LONG RUNNING QUERIES - FINAL
######################################################################
break on s.sql_id ON s.username DUPLICATES
select s.sql_id,s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by s.sql_id,sid,serial#,q.piece;
######################################################################################
CHANGE BASE LINE
######################################################################################
1)
SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
The PL/SQL function DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE tries to evolve new plans added by the optimizer to the
plan history of existing plan baselines. If the function can verify that
the new plan performs better than a plan chosen from the corresponding SQL plan baseline, the new plan is added as an accepted plan.
==============================================================================================================================================
2)
SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_794f2b0bd2ff2fa6');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
3)
To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package:
==============================================================================================================================================
select * from table(
dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_794f2b0bd2ff2fa6',format=>'basic'));
######################################################################################
The Number of ASM Disks and their Ownerships (grid, asmadmin) should be Correct.
The Major and Minor Numbers should be the same as Primary. Kfod command can be used to validate the disks.
If the disks are not listed in kfod, please check with Server team to rediscover the disks.
kfod asm_diskstring='/dev/oradisk/ASM/vot*' disks=all
######################################################################################
FAQ
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
http://select-star-from.blogspot.com/2013/07/oracle-dba-interview-questions-answers_24.html
######################################################################################
No comments