wrong data in v$sqlstats after cursor invalidation
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Sep 2011 17:08:25 +0200
Hi all
Running 11.2.0.1 in Linux x64.
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
