Home > Oracle, Performance > Database workload analysis using REDO

Database workload analysis using REDO


Often DBAs get request from the application managers to ‘size’ the transaction or workload.  That can be for migration, server sizing, planning for new hardware or even to manage a performance issue.  Below is one of the methods to identify the transaction ‘volume’ happening to a database.

A transaction is bunch of SQLs to complete a related job – either succeeds or rollback as a single unit.  Some used tell, there is N number of transactions per hour, but what is the size.  It can be a OLTP type, like updating account balance or a batch processes to manage day end data sales data.  As the size of the transactions varies, counting transaction won’t make any sense in the true world.

In an archive log database almost every transaction makes REDO and UNDO.   DBAs usually force database create REDO where ever oracle standby database exists.  Moreover, everyone knows, without redo, the database recovery will be in jeopardy.  So, REDO could be an ideal sizing factor.

Oracle captures REDO history in the AWR tables and can access it thorough view DBA_HIST_SYSSTAT.  Following SQL will help you to identify how many days of AWR data available for you.

SQL> select extract( day from retention) from dba_hist_wr_control;
EXTRACT(DAYFROMRETENTION)
-------------------------
                        8

So, I must have 8 days of AWR information available to analysis REDO information.  DBA_HIST_SYSSAT holds the snapshot of cumulative system wide since the system was started, just like V$SYSSTAT.  So, I will create view as follows,

SQL> create or replace  view vw_sysredo_snap as select n.SNAP_ID,
to_char(BEGIN_INTERVAL_TIME,'MON-DD:HH24:MI') START_TIME,
VALUE/1024/1024 RedoSize_MB 
from dba_hist_sysstat st,dba_hist_snapshot n  
where STAT_NAME = 'redo size' and n.INSTANCE_NUMBER=st.INSTANCE_NUMBER 
and st.SNAP_ID=n.SNAP_ID 
order by n.SNAP_ID;
View created.

Use the below SQL to capture REDO generated an hour interval.

SQL> select a.SNAP_ID,a.START_TIME ,a.REDOSIZE_MB - b.REDOSIZE_MB  
from vw_sysredo_snap a,vw_sysredo_snap b where a.SNAP_ID =b. SNAP_ID+1 ;
   SNAP_ID START_TIME   A.REDOSIZE_MB-B.REDOSIZE_MB
---------- ------------ ---------------------------
      8173 MAR-07:00:30                  2.91418076
      8174 MAR-07:01:30                  2.96246338
      8175 MAR-07:02:31                  2.95665741
      8176 MAR-07:03:30                  2.94236755
      8177 MAR-07:04:30                  2.89385986
      8178 MAR-07:05:30                  2.89277267
      8179 MAR-07:06:30                  2.94590378
      8180 MAR-07:07:30                  2.78068161
      8181 MAR-07:08:30                  2.95744324
      8182 MAR-07:09:30                  2.85288239
      8183 MAR-07:10:30                   3.0640831

My relatively idle database generated almost 3MB REDO in every one hour.  So, will get an idea what is happening in an every hour.  You can use the below SQL to capture the daily REDO pattern.

SQL> select substr(START_TIME,1,6),sum(redosize )
  2  from (select a.SNAP_ID,a.START_TIME ,a.REDOSIZE_MB,
 (a.REDOSIZE_MB - b.REDOSIZE_MB) redosize
  3  from vw_sysredo_snap a,vw_sysredo_snap b where a.SNAP_ID =b. SNAP_ID+1)
  4  group by substr(START_TIME,1,6);
SUBSTR SUM(REDOSIZE)
------ -------------
MAR-10    -9646.6137
MAR-08    70.3697433
MAR-07    70.3389549
MAR-15    46.9579964
MAR-12    135.984203
MAR-13    143.886005
MAR-14    -1363.5863
MAR-11    201.071934
MAR-09    69.2040558
9 rows selected.

Note: The negative number indicates that you have re-started the database instance so that the V$SYSSTAT value is reset.

This is in the database level.   Sometimes, you may need to capture the workload statistics in a table level – how many SELECT, INSERT, DELETE, UPDATES etc.  DBA_TAB_MODIFICATIONS holds DML statistics happening to a table.

So, for testing, I have created a new table as

SQL>  create table test as select * from dba_objects;
Table created.
SQL>  update  test set owner='THOMAS';
17456 rows updated.
SQL> commit ;
Commit complete.

Queried the DBA_TAB_MODIFICATIONS to get the statistics for the table TEST, but I was disappointed, as my query returns zero records.

SQL> select TRUNCATED,INSERTS,UPDATES,DELETES,DROP_SEGMENTS
  2  from user_tab_modifications
  3  where Table_name='TEST';
no rows selected

As per the documentation, you need to analyze the table to capture the statistics, so I am.

SQL> exec dbms_stats. gather_table_stats('thomas','test');
PL/SQL procedure successfully completed.
SQL> update  test set owner='THOMAS';
17456 rows updated.
SQL> commit ;
Commit complete.
SQL> select TRUNCATED,INSERTS,UPDATES,DELETES,DROP_SEGMENTS
  2   from user_tab_modifications
  3  where Table_name='TEST';
no rows selected

Still, I did not get a record as it is writing this on regular intervals.  But, dbms_stats.flush_database_monitoring_info can be used to flush the usage statistics to the usage table as below.

SQL> execute dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> select TRUNCATED,INSERTS,UPDATES,DELETES,DROP_SEGMENTS
  2  from user_tab_modifications
  3  where Table_name='TEST';
TRU    INSERTS    UPDATES    DELETES DROP_SEGMENTS
--- ---------- ---------- ---------- -------------
NO           0      17456          0             0
 

But, I was not able to identify a AWR table to analyze the workload information in any interval, rather we get the cumulative data since the instance startup.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: