I have a third-party application called Clarity running on Solaris 9 (64-bit) using Oracle 220.127.116.11 (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;
1813867 INSERT INTO PRJ_BLB_SLICES_M_ALC (SLICE_
REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, S
LICE, CREATED_DATE) VALUES (:REQUEST_ID,
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
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
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.