Dear colegues,
I'm running Oracle 10.2.0.4 on RHEL5
Application executes always same query using bind variable.
I do several calls in a row of this application and see that number of loads / invalidations of this SQL when querying v$sqlarea view are quite strange (please note that no stats capturing / change of table structure is done between calls, so I do not see the point in invalidation of SQL):
SQL> select sql_id from v$sql where sql_text like 'select pm.ri from gui g ,gui_methods gm%';
SQL_ID
-------------
7hkj2qyu2tuas
Call1 and then query in DB:
SQL> select last_active_time , LAST_LOAD_TIME, FIRST_LOAD_TIME, LOADS, INVALIDATIONS from v$sqlarea
2 where sql_id='7hkj2qyu2tuas';
LAST_ACTIVE_TIME LAST_LOAD_TIME FIRST_LOAD_TIME LOADS INVALIDATIONS
-------------------- -------------------- ------------------- ---------- -------------
29-May-2013 02:40:57 29-May-2013 02:40:57 2013-05-28/04:25:20 39 9
Call2 and then query in DB:
SQL> select last_active_time , LAST_LOAD_TIME, FIRST_LOAD_TIME, LOADS, INVALIDATIONS from v$sqlarea
2 where sql_id='7hkj2qyu2tuas';
LAST_ACTIVE_TIME LAST_LOAD_TIME FIRST_LOAD_TIME LOADS INVALIDATIONS
-------------------- -------------------- ------------------- ---------- -------------
29-May-2013 02:41:07 29-May-2013 02:41:07 2013-05-28/04:25:20 10 0
Call3 and then query in DB:
SQL> select last_active_time , LAST_LOAD_TIME, FIRST_LOAD_TIME, LOADS, INVALIDATIONS from v$sqlarea
2 where sql_id='7hkj2qyu2tuas';
LAST_ACTIVE_TIME LAST_LOAD_TIME FIRST_LOAD_TIME LOADS INVALIDATIONS
-------------------- -------------------- ------------------- ---------- -------------
29-May-2013 02:41:22 29-May-2013 02:41:22 2013-05-28/04:25:20 41 10
Can you please share your ideas why SQL stats is changing this strange way.
Thanks in advance for your time.