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.

Advertisements

2 Responses to Executions in v$sql

  1. Gary says:

    Well for a start, I’d be looking at ROWS_PROCESSED not executions as it is possible that you have a single update/insert/update/merge statement execution covering a lot of rows (and generating lots of redo). You could also ignore SELECT statements.
    Also, if you can catch it while it is happening, you can use session statistics to identify the database session generating the most redo
    SELECT sid,name,value
    FROM V$SESSTAT s, v$statname n
    WHERE n.statistic# = s.statistic#
    AND value > 1000
    AND name like ‘%redo size%’
    ORDER BY sid,LOWER(name);

  2. Ewan says:

    The best way to get historical data in Oracle 9i is using statspack, which can be run on a scheduled basis then you build reports on the snaps later.

    You can look at existing snaps by running the following command in sqlplus, it should list every existing statspack snap and let you choose to build a report between 2 times.

    @?/rdbms/admin/spreport.sql

    Otherwise, you can run this command in sqlplus to run a snap on demand, so you can run it, leave it 10 minutes, then run the same command again.

    exec statspack.snap();

    Afterwards, you can run the spreport.sql and it will output a spool file full of details like memory usage, disk i/o usage, and includes the top SQL by executions, buffer get, and disk i/o between the times of the 2 snaps requested.

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: