Find which program/query is generating more archive log in oracle database

on
Please find the steps to seek out out which program/query is generating more archive logs.

Step 1:
First, you must know when there are more number archive logs generated in the databases. To find that you can use the below query. The below SQL query gives how many archive logs are generated for each and every hour.

col day for a12
set lines 1000
set pages 999
col "00" for a3
col "01" for a3
col "02" for a3
col "03" for a3
col "04" for a3
col "05" for a3
col "06" for a3
col "07" for a3
col "08" for a3
col "09" for a3
col "10" for a3
col "11" for a3
col "12" for a3
col "13" for a3
col "14" for a3
col "15" for a3
col "16" for a4
col "17" for a3
col "18" for a4
col "19" for a3
col "20" for a3
col "21" for a3
col "22" for a3
col "23" for a3
SELECT 
to_char(first_time,'DD-MON-YYYY') day, 
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99')  "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23",
count(*) Tot
from
v$log_history
WHERE first_time > sysdate -7  
GROUP by 
to_char(first_time,'DD-MON-YYYY'),trunc(first_time) order by trunc(first_time);

Output:


Step 2:
From the above output, we can see that the more 05:00 to 06:00, now we need to find which objects have undergone more number of DB block changes between 05:00 to 06:00. 

SELECT To_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
       dhsso.object_name,
       SUM(db_block_changes_delta)                        AS maxchanges
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-09-16 05:00:00',
           'YYYY-MM-DD HH24:MI:SS') AND To_date(
               '2015-09-16 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP  BY begin_interval_time,
          dhsso.object_name
ORDER  BY maxchanges ASC;

Output:


Step 3: From the above screenshot, we can see that the maximum DB block changes have been made in XXDOMI_WIP_MAT_REQS% MView and index. We can easily find the SQL statement which caused more archive log generation using the below SQL query. 

col SQL_TEXT for a60
SELECT To_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
       dbms_lob.Substr(sql_text, 5000, 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 '%XXDOMI_WIP_MAT_REQS%'
       AND dhss.snap_id = dhs.snap_id
       AND dhss.instance_number = dhs.instance_number
       AND begin_interval_time BETWEEN
           To_date('2015-10-16 05:00:00',
           'YYYY-MM-DD HH24:MI:SS') AND To_date(
               '2015-10-16 06:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND dhss.sql_id = dhst.sql_id;
Output:



Step 4: With the sql_id we need to find which program is using the SQL. To find please use the below query. 

SELECT To_char(sample_time, 'yyyy_mm_dd hh24:mi:ss'),
       user_id,
       sql_id,
       program,
       MODULE,
       action
FROM   dba_hist_active_sess_history
WHERE  sql_id IN ( 'b8zxruby7m55d', '48bwdpdtk96c9' )
       AND To_char(sample_time, 'YYYY-MM-DD HH24:MI:SS') BETWEEN
           '2015-10-16 05:00:00' AND '2015-10-16 07:00:00'
ORDER  BY 1;

The issue here is MVIEW refresh was happening in complete mode. The complete MVIEW refresh just deletes all the data in the mview table and inserts the new data from the select query in the MVIEW. The solution here is to create a FAST refreshable MVIEW. We will discuss about MIVEW in a separate blog post.

Comments

Post a Comment