wrong data in v$sqlstats after cursor invalidation

I noticed that after a cursor invalidation (run DDL against table, dbms_shared_pool or alter system flush shared_pool) the statistics in v$sql, v$sqlarea differs to v$sqlstats, the first two contains new data while v$sqlstats retains old invalidated cursor data.

For example:

select sql_id, buffer_gets, disk_reads, executions from v$sql where sql_id = 'd5dbt7af1yjjm'
SQL_ID BUFFER_GETS DISK_READS EXECUTIONS
------------- ----------- ---------- ----------
d5dbt7af1yjjm 4184 0 1234

select sql_id, buffer_gets, disk_reads, executions from v$sqlstats where sql_id = 'd5dbt7af1yjjm'
SQL_ID BUFFER_GETS DISK_READS EXECUTIONS
------------- ----------- ---------- ----------
d5dbt7af1yjjm 98428270 6269 20845

*after a while:*

select sql_id, buffer_gets, disk_reads, executions from v$sql where sql_id = 'd5dbt7af1yjjm'
SQL_ID BUFFER_GETS DISK_READS EXECUTIONS
------------- ----------- ---------- ----------
d5dbt7af1yjjm 98431560 6269 21717

select sql_id, buffer_gets, disk_reads, executions from v$sqlstats where sql_id = 'd5dbt7af1yjjm'
SQL_ID BUFFER_GETS DISK_READS EXECUTIONS
------------- ----------- ---------- ----------
d5dbt7af1yjjm 7474 0 2106

both v$sql and v$sqlstats shows 3290 buffer gets increase and 872 executions increase.

This leads to a problem in 11gR2 Statspack, all top sql data are wrong when this situation happens.

Anyone has observed this behaviour?

Thanks

--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 13 2011 - 10:08:25 CDT