1-Check VMSTAT status.
vmstat (virtual memory statistics) is a computer system monitoring tool that collects and displays summary information about operating system memory, processes, interrupts, paging and block I/O.
vmstat 2 6
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 2536 21496 185684 1353000 0 0 0 14 1 2 0 0 100 0
0 0 2536 21496 185684 1353000 0 0 0 28 1030 145 0 0 100 0
0 0 2536 21496 185684 1353000 0 0 0 0 1026 132 0 0 100 0
0 0 2536 21520 185684 1353000 0 0 0 0 1033 186 1 0 99 0
0 0 2536 21520 185684 1353000 0 0 0 0 1024 141 0 0 100 0
0 0 2536 21584 185684 1353000 0 0 0 0 1025 131 0 0 100 0
2-Check server memory usages using top command.
3-Check System activity report using sar command.
sar -u Displays CPU usage for the current day that was collected until that point.
sar -u 1 3 Displays real time CPU usage every 1 second for 3 times.
$ sar -u 1 3
Linux 2.6.18-194.el5PAE (dev-db) 03/26/2011 _i686_ (8 CPU)
01:27:32 PM CPU %user %nice %system %iowait %steal %idle
01:27:33 PM all 0.00 0.00 0.00 0.00 0.00 100.00
01:27:34 PM all 0.25 0.00 0.25 0.00 0.00 99.50
01:27:35 PM all 0.75 0.00 0.25 0.00 0.00 99.00
Average: all 0.33 0.00 0.17 0.00 0.00 99.50
4-Check Alert log for any ORA- errors
5-Run AWR(automatic workload repository) report for specified time period.
sql> @?/rdbms/admin/awrrpt.sql
6-Run ADDM recommendation for specified time period.
sql> @?/rdbms/admin/addmrpt.sql
7-Check Long running sessions.
#LONG RUNNING SESSION#
-------------------------------------------------------
SELECT
SQL_ID,
SQL_ADDRESS,
SQL_HASH_VALUE
OPNAME,
TARGET,
TARGET_DESC,
TIME_REMAINING,
SID,
SERIAL#
FROM
V$SESSION_LONGOPS
WHERE
TIME_REMAINING>0;
//
Long running session: (RAC)
=======================
Select username, sid, serial#, command, sql_id, last_call_et/60 running_minutes
From gv$session
Where status = 'ACTIVE'
And last_call_et/60 > 30
and username is not null
;
Long Running (Pay attention on the RUN_MINUTES)
===========================================
select ses.username, ses.status, ses.sid, ses.serial#,
ses.osuser, ses.process, to_char(ses.logon_time,'hh24:mi:ss'),
s.disk_reads, s.buffer_gets, s.sorts, last_call_et/60 run_minutes,s.SQL_ID,s.plan_hash_value,
sql_text
from v$session ses, v$sql s
where ses.username is not null
and ses.username not in ('SYS') and ses.sql_address = s.address
order by run_minutes desc;
Check long running session status
# Long Session '%' completed
==========================
select sid,serial#,opname,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sofar<totalwork;
select sid,serial#,opname,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sql_hash_value='1474587846';
select sid,serial#,opname,message,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sql_id='gj9qpg6p71206';
//
## "%" completed using SID ##
========================
COLUMN opname FORMAT A30
COLUMN target_desc FORMAT A30
COLUMN units FORMAT A10
col username for a10
SELECT
sid,
opname,
target_desc,
sofar,
totalwork,
time_remaining,
units,username,elapsed_seconds,time_remaining
FROM
v$session_longops
where sid='&1'
order by 10;
//
Please use below sql to monitor number of rows committed or deleted. Column to monitor is rows_processed and executions.
===========================================================================================================
select SQL_ID, ROWS_PROCESSED,to_char(LAST_ACTIVE_TIME,' dd-Mon-YYYY HH24:mi:ss'),EXECUTIONS,Elapsed_time from v$sql where sql_id in('7h7t58m07pdgw');
8-Check for any blocking sessions in database.
select 'SID '||l1.sid||'--'||s1.username || ' IS BLOCKING SID ' || l2.sid||'--'||s2.username blocking_report from gv$lock l1, gv$lock l2,v$session s1,v$session s2
where l1.block =1 and l2.request > 0 and
l1.id1=l2.id1 and l1.id2=l2.id2 and s1.sid=l1.sid and s2.sid=l2.sid;
Session Using Table :
======================
SELECT * FROM v$lock WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='owner_name' AND object_name ='table_name');
SELECT * FROM v$session WHERE sid =<above query result>
Kill all blocking sessions sid's
----------------------------------
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE ;' from v$session where username='
9-Check Stale stats ,last analyzed stats,stats locks.
# DML counts #
----------------------------------
//
select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications where table_name='PO_VERSIONS';
select owner,table_name,last_analyzed,stale_stats from dba_tab_statistics where table_name='
select table_name,partition_name,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where owner='HR';
select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, last_analyzed from dba_TABLES where owner='PAY';
10-If there is stale stats or if table last analyzed is not up to date gather the stats accordingly.
**STATISTIC GATHERING**
==========================
Table stats gathering
=================
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'PRICE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => dbms_stats.default_degree, no_invalidate => FALSE, granularity => 'ALL', block_sample=>FALSE, cascade => TRUE);
##When granularity => 'AUTO' oracle decides which segment to collect stats depending on whether the stats are important for the optimizer, whether they are stale etc. Currently we don't collect stats for hash sub partitions since it is not going to help a lot in optimizer cost estimation per Bug 6918217 .##
Database Stats gathering
=====================
EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => dbms_stats.default_degree, no_invalidate => FALSE, granularity => 'ALL', block_sample=>FALSE, cascade => TRUE);
Schema Stats Gather
==================
set time on
BEGIN
dbms_stats.GATHER_SCHEMA_STATS
(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => dbms_stats.default_degree,
no_invalidate => FALSE,
granularity => 'ALL',
block_sample=>FALSE,
cascade => TRUE
);
END;
/
Index Gather Stats
================
exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'PRC_TRADE',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.AUTO_DEGREE);
11-Check for any sql plan change for the long running sql_id's
sql plan change:-
==================
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
12-Check top wait events in database .
TOP WAIT EVENTS ##
====================
set linesize 120
col event format a30
select * from (
select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 6;
//
//
set lines 200
set pages 2000
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
//
13-Look for sql execution plan.
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('7d85p8rb39hg0',174907159,NULL,'ALL'));
select * from table(dbms_xplan.display_awr('0pnkz8nrwgvw5'));
14-Checking Table Fragmentation.
set linesize 800
set pages 1000
select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
and owner not in('SYS','SYSTEM') and table_name=upper('&table_name')
order by 5 desc;
//
select table_name,round((blocks*8),2) "size (kb)" from dba_tables where owner='SCOTT';
*If table is highly fragmented then perform table defragmentation using below steps
15-Table defragmentation steps
## De_FRAGMENT Table ##
=========================
Step 1:
alter index CUSTOMER.BAM_MFRS_TX_IDX shrink space cascade;
Step 2:
alter index CUSTOMER.BAM_MFRS_TX_IDX deallocate unused;
Step 3:
alter table CUSTOMER.BAM_MFRS_TX enable row movement;
Step 4:
alter table CUSTOMER.BAM_MFRS_TX shrink space cascade;
Step 5:
alter table CUSTOMER.BAM_MFRS_TX deallocate unused;
Step 6:
alter tablespace CUST_DEFAULT coalesce;
16-
vmstat (virtual memory statistics) is a computer system monitoring tool that collects and displays summary information about operating system memory, processes, interrupts, paging and block I/O.
vmstat 2 6
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 2536 21496 185684 1353000 0 0 0 14 1 2 0 0 100 0
0 0 2536 21496 185684 1353000 0 0 0 28 1030 145 0 0 100 0
0 0 2536 21496 185684 1353000 0 0 0 0 1026 132 0 0 100 0
0 0 2536 21520 185684 1353000 0 0 0 0 1033 186 1 0 99 0
0 0 2536 21520 185684 1353000 0 0 0 0 1024 141 0 0 100 0
0 0 2536 21584 185684 1353000 0 0 0 0 1025 131 0 0 100 0
2-Check server memory usages using top command.
3-Check System activity report using sar command.
sar -u Displays CPU usage for the current day that was collected until that point.
sar -u 1 3 Displays real time CPU usage every 1 second for 3 times.
$ sar -u 1 3
Linux 2.6.18-194.el5PAE (dev-db) 03/26/2011 _i686_ (8 CPU)
01:27:32 PM CPU %user %nice %system %iowait %steal %idle
01:27:33 PM all 0.00 0.00 0.00 0.00 0.00 100.00
01:27:34 PM all 0.25 0.00 0.25 0.00 0.00 99.50
01:27:35 PM all 0.75 0.00 0.25 0.00 0.00 99.00
Average: all 0.33 0.00 0.17 0.00 0.00 99.50
4-Check Alert log for any ORA- errors
5-Run AWR(automatic workload repository) report for specified time period.
sql> @?/rdbms/admin/awrrpt.sql
6-Run ADDM recommendation for specified time period.
sql> @?/rdbms/admin/addmrpt.sql
7-Check Long running sessions.
#LONG RUNNING SESSION#
-------------------------------------------------------
SELECT
SQL_ID,
SQL_ADDRESS,
SQL_HASH_VALUE
OPNAME,
TARGET,
TARGET_DESC,
TIME_REMAINING,
SID,
SERIAL#
FROM
V$SESSION_LONGOPS
WHERE
TIME_REMAINING>0;
//
Long running session: (RAC)
=======================
Select username, sid, serial#, command, sql_id, last_call_et/60 running_minutes
From gv$session
Where status = 'ACTIVE'
And last_call_et/60 > 30
and username is not null
;
Long Running (Pay attention on the RUN_MINUTES)
===========================================
select ses.username, ses.status, ses.sid, ses.serial#,
ses.osuser, ses.process, to_char(ses.logon_time,'hh24:mi:ss'),
s.disk_reads, s.buffer_gets, s.sorts, last_call_et/60 run_minutes,s.SQL_ID,s.plan_hash_value,
sql_text
from v$session ses, v$sql s
where ses.username is not null
and ses.username not in ('SYS') and ses.sql_address = s.address
order by run_minutes desc;
Check long running session status
# Long Session '%' completed
==========================
select sid,serial#,opname,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sofar<totalwork;
select sid,serial#,opname,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sql_hash_value='1474587846';
select sid,serial#,opname,message,sofar,totalwork,((sofar/totalwork)*100) "Completed %",sql_address from v$session_longops where sql_id='gj9qpg6p71206';
//
## "%" completed using SID ##
========================
COLUMN opname FORMAT A30
COLUMN target_desc FORMAT A30
COLUMN units FORMAT A10
col username for a10
SELECT
sid,
opname,
target_desc,
sofar,
totalwork,
time_remaining,
units,username,elapsed_seconds,time_remaining
FROM
v$session_longops
where sid='&1'
order by 10;
//
Please use below sql to monitor number of rows committed or deleted. Column to monitor is rows_processed and executions.
===========================================================================================================
select SQL_ID, ROWS_PROCESSED,to_char(LAST_ACTIVE_TIME,' dd-Mon-YYYY HH24:mi:ss'),EXECUTIONS,Elapsed_time from v$sql where sql_id in('7h7t58m07pdgw');
8-Check for any blocking sessions in database.
select 'SID '||l1.sid||'--'||s1.username || ' IS BLOCKING SID ' || l2.sid||'--'||s2.username blocking_report from gv$lock l1, gv$lock l2,v$session s1,v$session s2
where l1.block =1 and l2.request > 0 and
l1.id1=l2.id1 and l1.id2=l2.id2 and s1.sid=l1.sid and s2.sid=l2.sid;
Session Using Table :
======================
SELECT * FROM v$lock WHERE id1 = (SELECT object_id FROM all_objects WHERE owner ='owner_name' AND object_name ='table_name');
SELECT * FROM v$session WHERE sid =<above query result>
Kill all blocking sessions sid's
----------------------------------
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE ;' from v$session where username='
9-Check Stale stats ,last analyzed stats,stats locks.
# DML counts #
----------------------------------
//
select table_owner,table_name,inserts,updates,deletes,timestamp from dba_tab_modifications where table_name='PO_VERSIONS';
select owner,table_name,last_analyzed,stale_stats from dba_tab_statistics where table_name='
select table_name,partition_name,last_analyzed,stattype_locked,stale_stats from dba_tab_statistics where owner='HR';
select table_name, tablespace_name, num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len, last_analyzed from dba_TABLES where owner='PAY';
10-If there is stale stats or if table last analyzed is not up to date gather the stats accordingly.
**STATISTIC GATHERING**
==========================
Table stats gathering
=================
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'PRICE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => dbms_stats.default_degree, no_invalidate => FALSE, granularity => 'ALL', block_sample=>FALSE, cascade => TRUE);
##When granularity => 'AUTO' oracle decides which segment to collect stats depending on whether the stats are important for the optimizer, whether they are stale etc. Currently we don't collect stats for hash sub partitions since it is not going to help a lot in optimizer cost estimation per Bug 6918217 .##
Database Stats gathering
=====================
EXEC DBMS_STATS.gather_database_stats(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => dbms_stats.default_degree, no_invalidate => FALSE, granularity => 'ALL', block_sample=>FALSE, cascade => TRUE);
Schema Stats Gather
==================
set time on
BEGIN
dbms_stats.GATHER_SCHEMA_STATS
(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => dbms_stats.default_degree,
no_invalidate => FALSE,
granularity => 'ALL',
block_sample=>FALSE,
cascade => TRUE
);
END;
/
Index Gather Stats
================
exec dbms_stats.gather_index_stats(ownname=>'SCOTT',indname=>'PRC_TRADE',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.AUTO_DEGREE);
11-Check for any sql plan change for the long running sql_id's
sql plan change:-
==================
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
12-Check top wait events in database .
TOP WAIT EVENTS ##
====================
set linesize 120
col event format a30
select * from (
select event, total_waits, total_timeouts, time_waited
from v$system_event
where event not like 'SQL*Net%'
and event not in ('pmon timer','rdbms ipc message','dispatcher timer','smon timer')
order by time_waited desc )
where rownum < 6;
//
//
set lines 200
set pages 2000
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
//
13-Look for sql execution plan.
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_AWR('7d85p8rb39hg0',174907159,NULL,'ALL'));
select * from table(dbms_xplan.display_awr('0pnkz8nrwgvw5'));
14-Checking Table Fragmentation.
set linesize 800
set pages 1000
select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
and owner not in('SYS','SYSTEM') and table_name=upper('&table_name')
order by 5 desc;
//
select table_name,round((blocks*8),2) "size (kb)" from dba_tables where owner='SCOTT';
*If table is highly fragmented then perform table defragmentation using below steps
15-Table defragmentation steps
## De_FRAGMENT Table ##
=========================
Step 1:
alter index CUSTOMER.BAM_MFRS_TX_IDX shrink space cascade;
Step 2:
alter index CUSTOMER.BAM_MFRS_TX_IDX deallocate unused;
Step 3:
alter table CUSTOMER.BAM_MFRS_TX enable row movement;
Step 4:
alter table CUSTOMER.BAM_MFRS_TX shrink space cascade;
Step 5:
alter table CUSTOMER.BAM_MFRS_TX deallocate unused;
Step 6:
alter tablespace CUST_DEFAULT coalesce;
16-
