Finding the root cause of sudden increase in the archive log generation on the database.
Find tables that have maximum number of changes during the period there was huge archive generation.
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
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('2015_05_23 12','YYYY_MM_DD HH24')
AND to_date('2015_05_25 12','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by SUM(db_block_changes_delta) desc;
SNAP_TIME OBJECT_NAME SUM(DB_BLOCK_CHANGES_DELTA)
2015_05_25 05:00 GL_INTERFACE_HISTORY 17470624
2015_05_25 03:00 GL_INTERFACE_HISTORY 17459680
2015_05_25 05:00 GL_IMPORT_REFERENCES_N1 15055776
2015_05_25 03:00 GL_IMPORT_REFERENCES_N1 15036448
2015_05_25 02:00 XLA_GLT_1721152_N3 14766960
2015_05_25 05:00 XLA_AE_HEADERS 14447360
2015_05_25 04:00 XLA_GLT_1721153_N3 13673392
2015_05_25 01:00 XLA_AE_HEADERS 11555808
2015_05_24 19:00 XLA_AE_LINES_GT 11472352
2015_05_24 16:00 XLA_AE_LINES_GT 9156944
Once we know the tables, get the information related to those tables.
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
SUM(db_block_changes_delta)
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 dhsso.object_name = 'GL_INTERFACE_HISTORY'
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') order by SUM(db_block_changes_delta) desc;
SNAP_TIME SUM(DB_BLOCK_CHANGES_DELTA)
2015_05_25 05:00 17470624
2015_05_25 03:00 17459680
Find more SQL information related to the tables that have changed
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 '%GL_INTERFACE_HISTORY%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id;
TO_CHAR(BEGIN_INTERVAL_TIME,'YYYY_MM_DDHH24:MI') DBMS_LOB.SUBSTR(SQL_TEXT,4000,1) INSTANCE_NUMBER SQL_ID EXECUTIONS_DELTA ROWS_PROCESSED_DELTA
SQL Text: 2015_05_25 03:00 insert into gl_interface_history
(status, ledger_id, user_je_source_name, user_je_category_name,
accounting_date, currency_code, average_journal_flag,………from XLA_GLT_1721152 int where (user_je_source_name, group_id) in (('Cost Management', 1721152)) and request_id+0 = 97068430
INSTANCE_NUMBER: 1
SQL_ID: fqntqgv0jjv1a
EXECUTIONS_DELTA: 1
ROWS_PROCESSED_DELTA: 7227044
SQL Text: 2015_05_25 03:00 2015_05_25 05:00 insert into gl_interface_history
(status, ledger_id, user_je_source_name, user_je_category_name,
accounting_date, currency_code, average_journal_flag, ,……… from XLA_GLT_1721153 int
where (user_je_source_name, group_id) in (('Cost Management', 1721153))
and request_id+0 = 97069264
INSTANCE_NUMBER: 1
SQL_ID: 7qy4ar0fznjpw
EXECUTIONS_DELTA: 1
ROWS_PROCESSED_DELTA: 7227044
From the below query we can find the program name that has done the changes.
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 ('fqntqgv0jjv1a','7qy4ar0fznjpw');
INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME,'YYYY_MM_DDHH24:MI:SS') USER_ID PROGRAM
1 2015_05_25 05:23:18 212 GLLEZL@ebsstgapp02 (TNS V1-V3)
1 2015_05_25 05:23:08 212 GLLEZL@ebsstgapp02 (TNS V1-V3)
1 2015_05_25 05:22:58 212 GLLEZL@ebsstgapp02 (TNS V1-V3)
The above query gives the program short name.
With the help of this we can find the concurrent program details.
System Administrator > Concurrent > Program > Define > F11 > Short Name: GLLEZL Ctrl F11
Program Name: Journal Import
System Administrator > Concurrent > Requests > Name > Journal Import > Find
We can also find this information by querying the request id we got in the 3rd query.
System Administrator > Concurrent > Requests > Request ID > 97068430 > Find
All the details about the concurrent request can be found here.