Manual execution vs v$session discrepancy

We had a SQL that was being run repeatedly over 10K times in our prod system today. Turned out to be something related to fiscal year. It spiked up our CPU big time. I got several value sets for the SQL from the sql bind capture and executed them myself for about a dozen value sets; each and everyone of my manual execution returned in about 2/10ths of a second. However the v$session view would show the sessions run that SQL (sql_id, last_call_et, status) for a few minutes. That was the case for several users the whole day. Per v$sql, the average execution time for that SQL was 7 seconds since the first load this morning. AWR reports showed that the averages are anywhere from 4 seconds and up depending on the hour. I was confused by what the v$session showed me vs my execution times. Am I missing something here? bind capture interval is left at default 900 seconds.

Could it be that the values I checked (about 15) just happened to be 'good values' or the environment could have been different for the executed SQLs? I was hesitant to turn on the trace with the CPU usage through the roof. v10.2

Thanks.

--

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 03 2015 - 04:50:10 CEST