SQL QUERY TO MONITOR REDO GENERATION OF DML QUERIES

If we want to find out the DML query which generate redo’s, we can use the block_changes column of v$sess_io to find the number of redolog generated after executing an SQL statement. This information is stored in memory.

--REDO GENERATION FROM SQL
col SID format a20
col BLOCK_CHANGES format 999999999
col SID format 999999
col SQL_TEXT format a20
select se.sid,si.block_changes,se.sql_id,sq.sql_text
from v$session se
inner join v$sess_io si on se.sid=si.sid
inner join v$sql sq on se.sql_id=sq.sql_id;

Output:

kish@exdbx<>col SID format a20
kish@exdbx<>col BLOCK_CHANGES format 999999999
kish@exdbx<>col SID format 999999
kish@exdbx<>col SQL_TEXT format a20
kish@exdbx<>select se.sid,si.block_changes,se.sql_id,sq.sql_text
  2  from v$session se
  3  inner join v$sess_io si on se.sid=si.sid
  4  inner join v$sql sq on se.sql_id=sq.sql_id;

    SID BLOCK_CHANGES SQL_ID        SQL_TEXT
------- ------------- ------------- --------------------
     83             0 fpdg6gpww2rz1 select se.sid,si.blo
                                    ck_changes,se.sql_id
                                    ,sq.sql_text from v$
                                    session se inner joi
                                    n v$sess_io si on se
                                    .sid=si.sid inner jo
                                    in v$sql sq on se.sq
                                    l_id=sq.sql_id

     86       1728654 cvx718m1y52mm update SALES_TAB_COP
                                    Y nologging set NULL
                                    _COLUMN = 'ABC' wher
                                    e mod(ORDER_ID,3)=1

     94             6 cvx718m1y52mm update SALES_TAB_COP
                                    Y nologging set NULL
                                    _COLUMN = 'ABC' wher
                                    e mod(ORDER_ID,3)=1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s