Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL invalidation

A.SivkovMay 29 2013 — edited Jun 14 2013
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2013
Added on May 29 2013
8 comments
1,022 views