Who caused more I/O in the Oracle database server?

Huge archive log generation .. what to do? what is causing it?

Step 1:

Know which table / object has more number of changes during the problematic window:

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
        dhsso.object_name,
        sum(db_block_changes_delta) as maxchages
  FROM dba_hist_seg_stat dhss,
         dba_hist_seg_stat_obj dhsso,
         dba_hist_snapshot dhs
  WHERE dhs.snap_id = dhss.snap_id
    AND dhs.instance_number = dhss.instance_number
    AND dhss.obj# = dhsso.obj#
    AND dhss.dataobj# = dhsso.dataobj#
    AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
                                           AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
  GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
           dhsso.object_name order by maxchages asc;
    
The below objects have max number of changes in them during the high archive generation period.

SNAP_TIME        OBJECT_NAME                     MAXCHAGES
---------------- ------------------------------ ----------
2013_05_22 17:00 MGMT_CURRENT_AVAILABILITY_PK         7104
2013_05_22 17:00 MGMT_AVAIL_MARKER_PK                 7312
2013_05_22 18:00 MGMT_METRICS_1HOUR_PK_INT           20112
2013_05_22 17:00 MGMT_METRICS_1HOUR_PK_INT           20864
2013_05_22 20:00 MGMT_METRICS_1HOUR_PK_INT           21152
2013_05_22 19:01 MGMT_METRICS_1HOUR_PK_INT           23088
2013_05_22 17:00 MGMT_COLLECTIONS                    95392
2013_05_22 20:00 MGMT_COLLECTIONS                   110080
2013_05_22 17:00 MGMT_CURRENT_METRICS_PK            110512
2013_05_22 20:00 MGMT_CURRENT_METRICS_PK            110992
2013_05_22 18:00 MGMT_CURRENT_METRICS_PK            112304
2013_05_22 19:01 MGMT_CURRENT_METRICS_PK            113584
2013_05_22 19:01 MGMT_COLLECTIONS                   114656
2013_05_22 18:00 MGMT_COLLECTIONS                   114752
2013_05_22 20:00 MGMT_METRICS_RAW_PK_INT            127744
2013_05_22 18:00 MGMT_METRICS_RAW_PK_INT            129392
2013_05_22 17:00 MGMT_METRICS_RAW_PK_INT            129888
2013_05_22 19:01 MGMT_METRICS_RAW_PK_INT            130512
2013_05_22 17:00 MGMT_POLICY_ASSOC                  292576
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG              294128
2013_05_22 18:00 MGMT_POLICY_ASSOC                  367312
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG              368544
2013_05_22 20:00 MGMT_POLICY_ASSOC                  368560
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG              370432
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG              371504
2013_05_22 19:01 MGMT_POLICY_ASSOC                  377728
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PARAMS       434720
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PARAMS       551968
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PARAMS       554816
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PARAMS       560944
2013_05_22 17:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       587168
2013_05_22 19:01 MGMT_POLICY_ASSOC_CFG_PK_IDX       737296
2013_05_22 20:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       739120
2013_05_22 18:00 MGMT_POLICY_ASSOC_CFG_PK_IDX       742656

Step 2:

Once you know the objects ,get the SQL information realted to those objects:

The below are the SQLs causing more changes to MGMT_POLICY_ASSOC_CFG%.

SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
         dbms_lob.substr(sql_text,4000,1),
         dhss.instance_number,
         dhss.sql_id,executions_delta,rows_processed_delta
  FROM dba_hist_sqlstat dhss,
         dba_hist_snapshot dhs,
         dba_hist_sqltext dhst
  WHERE upper(dhst.sql_text) LIKE '%MGMT_POLICY_ASSOC_CFG%'
    AND dhss.snap_id=dhs.snap_id
    AND dhss.instance_Number=dhs.instance_number
 AND begin_interval_time BETWEEN to_date('2013_05_22 17','YYYY_MM_DD HH24')
                                           AND to_date('2013_05_22 21','YYYY_MM_DD HH24')
    AND dhss.sql_id = dhst.sql_id;
 
 
 

INSERT INTO MGMT_POLICY_ASSOC_CFG(CLEAR_MESSAGE, CLEAR_MESSAGE_NLSID, COLL_NAME, CONDITION_OPERATOR, EVAL_ORDER, FIXIT_J
OB, IS_PUSH, KEY_OPERATOR, KEY_VALUE, MESSAGE, MESSAGE_NLSID, NO_CLEAR_ON_NULL, NUM_OCCURRENCES, OBJECT_GUID, POLICY_GUI
D) VALUES ( :1, :2, NVL(:3, ' '), :4, :5, :6, :7, NVL(:8, 0), NVL(:9, ' '), :10, :11, :12, :13, :14, :15)
              1 71t5d196acgr9           186462                    0

2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG SET CLEAR_MESSAGE= :1 , CLEAR_MESSAGE_NLSID= :2 , CONDITION_OPERATOR= :3 , EVAL_ORDER= :4 ,
 FIXIT_JOB= :5 , IS_PUSH= :6 , MESSAGE= :7 , MESSAGE_NLSID= :8 , NO_CLEAR_ON_NULL= :9 , NUM_OCCURRENCES= :10  WHERE COLL
_NAME= NVL(:11, ' ') AND KEY_VALUE= NVL(:12, ' ') AND POLICY_GUID= HEXTORAW(:13) AND KEY_OPERATOR= NVL(:14, 0) AND OBJEC
T_GUID= HEXTORAW(:15)
              1 5xmfm787py0a3           186462               186462

2013_05_22 18:00
UPDATE MGMT_POLICY_ASSOC_CFG_PARAMS SET CRIT_THRESHOLD= :1 , WARN_THRESHOLD= :2  WHERE COLL_NAME= NVL(:3, ' ') AND KEY_V
ALUE= NVL(:4, ' ') AND POLICY_GUID= HEXTORAW(:5) AND KEY_OPERATOR= NVL(:6, 0) AND OBJECT_GUID= HEXTORAW(:7) AND PARAM_NA
ME= NVL(:8, ' ')
              1 dxdzv7ymv7vrk           186487               186487



2013_05_22 17:00
INSERT INTO MGMT_POLICY_ASSOC_CFG_PARAMS(COLL_NAME, CRIT_THRESHOLD, KEY_OPERATOR, KEY_VALUE, OBJECT_GUID, POLICY_GUID, W
ARN_THRESHOLD) VALUES ( NVL(:1, ' '), :2, NVL(:3, 0), NVL(:4, ' '), :5, :6, :7)
              1 a8d5br4z9m251           147609                    0


Step 3: Once you know the SQL ID , get the program and userid who is running it and intimate the user to take care of this query / Program.

As a DBA , you can increase the archive log backup frequency of the database / once you get the permission to kill the program /sql you can kill them as well.

Know which program is causing huge redo using the SQL IDs.
    
 SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
         user_id,
         program
  FROM dba_hist_active_sess_history
  WHERE sql_id in ('5xmfm787py0a3','bq28gpz5w5qqg','71t5d196acgr9','a8d5br4z9m251')
    AND snap_id BETWEEN 31856 AND 31860  2    3    4    5    6  ;

INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME    USER_ID PROGRAM
--------------- ------------------- ---------- ----------------------------------------------------------------
              1 2013_05_22 17:26:22         25 OMS
              1 2013_05_22 18:20:22         25 OMS
              1 2013_05_22 18:40:31         25 OMS
              1 2013_05_22 19:56:41         25 OMS
              1 2013_05_22 19:34:41         25 OMS
              1 2013_05_22 19:14:11         25 OMS
              1 2013_05_22 20:49:00         25 OMS
              1 2013_05_22 20:36:20         25 OMS
              1 2013_05_22 20:25:50         25 OMS

Oracle schema statistics gathering

set echo on;                                                                                                                                                                                                                                                                                              

set feedback on;                                                                                                                                                                                                                                                                                          

spool /export/home/gather_schema_stats.log;                                                                                                                                                                                                                    

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'MYNATRACE_PROD',CASCADE=>TRUE, ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>8);                                                                                                                                                                      

spool off;                                                                                                                                                         

ENABLING NOPARALLEL for Oracle table

alter table PROD4.MEASUREMENT_HIGH logging noparallel;

running oracle sql scripts in nohup mode in unix os

ias_rest.sql

set heading on;

set feedback on;

alter session enable parallel dml;

spool ias_rest.log;

set time on;

set timing on;

INSERT /*+APPEND PARALLEL(X,4)*/INTO abc.PROD4.MEASUREMENT_HIGH X SELECT /*+PARALLEL(Y,4)*/* FROM abc_PROD4.MEASUREMENT_HIGH@to_src Y;

COMMIT;

spool off;

Execute:

nohup sqlplus mani/mani@prfengp @ias_rest.sql>ias_rest.out &

Database application interface details collection

col userhost format a10
col TERMINAL format a10
col OBJ$NAME format a20
col userid format a20

select distinct(userhost),userid from SYS.AUD$ where userid not like 'TAG%'  AND userid not like 'TC%' AND userid not in ('LOGLOGIC','DBSNMP','xxDBA');

Oracle gather statistics

#!/bin/ksh
. /db/home/oracle/db.env
MAIL_ADDR=TST@TEST.com
sqlplus / as sysdba <<EOF
spool /db/home/oracle/dbms_stats.log
set time on
set timing on
set feedback on
exec dbms_stats.gather_table_stats('EBDS','ACCOUNT',degree => 16);
exec dbms_stats.gather_table_stats('EBDS','ACCOUNT_PRODUCT',degree => 16);
exec dbms_stats.gather_table_stats('EBDS','DEVICE',degree => 16);
exec dbms_stats.gather_table_stats('EBDS','DEVICE_PRODUCT',degree => 16);
exec dbms_stats.gather_table_stats('EBDS','LOGICAL_ENTITY',degree => 16);
exec dbms_stats.gather_table_stats('EBDS','SERVICE',degree => 16);
exec dbms_stats.gather_table_stats('RECON','ENT_ACCOUNT',degree => 16);
exec dbms_stats.gather_table_stats('RECON','ENT_LOGICAL',degree => 16);

spool off
EOF
mailx -s "Gather stats on DSSRPT" $MAIL_ADDR < /db/home/oracle/dbms_stats.log

ORACLE KILL INACTIVE SESSIONS SCRIPT

MAIL_ADDR=abc@abc.com
OUTPUT=/db/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db1
export ORACLE_HOME
ORACLE_SID=DSSRPT1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool kill.sql
select 'alter system kill session ''' || sid || ',' || serial# || ',' ||'@' || inst_id || ''' immediate;' from gv\$session where username='OSS_READ' and status<>'active';
spool off
spool killed.sql
set echo on
@@kill.sql
exit
EOF
mail -s "OSS_READ DBLINK INACTIVE SESSIONS ON $ORACLE_SID ARE KILLED" $MAIL_ADDR < $OUTPUT


Every day @5pm UTC time
======================================================================================
00 22 * * * /db/home/oracle/dbadmin/inactive_kill.sh



MAIL_ADDR=manikandan_govindaraj@cable.comcast.com
OUTPUT=/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/tds_db
export ORACLE_HOME
ORACLE_SID=PTDSPRB1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool kill.sql
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',' ||'@' || s.inst_id || ''' immediate;'   from gv\$session s, v\$process p where p.addr=s.paddr and s.last_call_et > 10800 and s.status='INACTIVE';
spool off
spool killed.sql
set echo on
@@kill.sql
exit
EOF
mail -s "More than 3Hrs Inactive sessions ON $ORACLE_SID are killed" $MAIL_ADDR < $OUTPUT

====================================================================================================
#!/bin/ksh
MAIL_ADDR=manikandan_govindaraj@abc.com
INPUT=/db/home/oracle/dbadmin/kill.sql
OUTPUT=/db/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db1
export ORACLE_HOME
ORACLE_SID=DSSRPT1
export ORACLE_SID
. /db/home/oracle/db.env
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
set pages 0
spool ${INPUT}
select 'alter system kill session ''' || sid || ',' || serial# || ',' ||'@' || inst_id || ''' immediate;' from gv\$session where username='OSS_READ' and status<>'active';
spool off
spool ${OUTPUT}
set echo on
@@${INPUT}
spool off
exit
EOF
[ -s ${INPUT} ] && /bin/mail -s "OSS_READ DBLINK INACTIVE SESSIONS ON $ORACLE_SID ARE KILLED" $MAIL_ADDR < ${OUTPUT}


====================================================================================================
MAIL_ADDR=manikandan_govindaraj@abc.com
INPUT=/db/home/oracle/dbadmin/kill.sql
OUTPUT=/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/tds_db
export ORACLE_HOME
ORACLE_SID=PTDSPRB1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool ${INPUT}
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',' ||'@' || s.inst_id || ''' immediate;'   from gv\$session s, v\$process p where p.addr=s.paddr and s.last_call_et > 10800 and s.status='INACTIVE' and s.USERNAME='RECONAPP';
spool off
spool ${OUTPUT}
set echo on
@@${INPUT}
select to_char(sysdate,'dd/mm/yyyy hh:mi') from dual;
spool off
exit
EOF
[ -s ${INPUT} ] && /bin/mail -s "RECONN inactive sesson >3Hrs on $ORACLE_SID are killed" $MAIL_ADDR < ${OUTPUT}