Use V$SQLAREA to know SQL Processing Time

Oracle Database allocates memory from shared pool (Library Cache) when a new SQL statement is parsed, to store in the shared sql area. The size of memory depends on the complexity of the SQL. If the entire Library Cache (LC) is already occupied, Oracle has deallocated items from LC using least recently used (LRU) algorithm. So if your SQL has not been edged out yet, you can find it in v$sqlarea or gv$sqlarea (which is important for RAC environment).

v$sqlarea has very precious information which are important for sql tuning. Among these which I find very much useful are

• SQL_ID (sql id for your sql. This value should be compared to identify soft parse or hard parse).
  
  
• PLAN_HASH_VALUE (ID of Query Execution plan).
  
  
• FIRST_LOAD_TIME (Date & time when this SQL first loaded in LC).
  
  
• LAST_LOAD_TIME (Last Date & time when this SQL is reused from LC i.e. soft parsed).
  
  
• LAST_ACTIVE_TIME (Actually these SQL are the implicit cursors that are sent for Private SQL AREA. This time indicates the last time this cursor is fetched or other way it can be said time at which the query plan was last active).
  
  
• OPTIMIZER_COST (Cost given Oracle Query optimizer).

So, (Last_Active_Time - Last_Load_Time)*24*60 will give you the value of time elapsed to process the sql (including fetch, parse & execute) approximately.

If you want to know this for an sql like - insert into tbl_my_sample ..., you may write the following sql -

select sql_id,plan_hash_value, last_load_time, last_active_time,(last_active_time-last_load_time)*24*60 interval_in_min, OPTIMIZER_COST from gv$sqlarea where sql_text like 'insert into tbl_my_sample%' order by last_active_time desc

Description of V$SQLAREA can be found in http://download.oracle.com/docs/