Executions in v$sql

I have a third-party application called Clarity running on Solaris 9 (64-bit) using Oracle 9.2.0.8 (64-bit).  This is a development database, not production.  Archived redo log files have been generated every minute since midnight May 31st.  I was concerned at the frequency the archived redo log files were being generated as this is not a typical load for this database  Further investigation was warranted.

I started by checking v$sql looking for SQL with a high number of executions to see if I could find the SQL causing the high DML activity.  The output is below.

SYSTEM@nikudev> select executions, sql_text from v$sql where executions > 500000 order by executions desc;

EXECUTIONS SQL_TEXT
—————————– ————————————————————————————————————————
   1813867 INSERT INTO PRJ_BLB_SLICES_M_ALC (SLICE_
           REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, S
           LICE, CREATED_DATE) VALUES (:REQUEST_ID,
           :OBJECT_ID,:SLICE_DATE,:SLICE,SYSDATE)    

   1420497 DELETE FROM PRJ_BLB_SLICES WHERE SLICE_R
           EQUEST_ID = :1 AND PRJ_OBJECT_ID = :2

    995791 INSERT INTO PRJ_BLB_SLICES_M_HARDALC (SL
           ICE_REQUEST_ID, PRJ_OBJECT_ID, SLICE_DAT
           E, SLICE, CREATED_DATE) VALUES (:REQUEST
           _ID,:OBJECT_ID,:SLICE_DATE,:SLICE,SYSDAT
           E)

    646375 INSERT INTO FCG_CQI_DATA_INTEGRITY (RUN_
           DATE, SEVERITY, OBJECT_TYPE, OBJECT_NAME
           , OBJECT_ID, CATEGORY, DESCRIPTION) SELE
           CT SYSDATE, 2, ‘PRJ’, :B2 , :B1 , ‘Tasks
           ’, ‘Project has an EFFORT task.’ FROM DU
           AL

    543867 BEGIN NBI_EVENTS_INS_SP(:1,:2); END;

I believe this is one way to find the high activity but was wondering if there was some other view I should be querying in addition to this.  I re-ran the above SQL query about every 5 seconds to watch which statement’s execution count was growing at a rapid rate.

It was easy to look at the executions since the database was restarted about 48 hours earlier.  Was just wondering where else I could look to confirm the historical (since instance startup) executions and possibly match with existing redo/undo information (i.e. amount generated).  Unfortunately, I don’t believe I can determine which SQL statements are generating a lot of redo/undo information.

In the end, the Application Administrator was able to recognize what the application was doing based on the SQL statements I provided from my query of v$sql.  We just refreshed data from Production and now the application is going through the data slicing and summarizing it in many ways.  At least we know why.  Not sure it needs to be done for a development environment, but they are allowing to continue to completion.