Re: wrong data in v$sqlstats after cursor invalidation

From: LS Cheng <exriscer_at_gmail.com>
Date: Tue, 13 Sep 2011 17:09:32 +0200
Message-ID: <CAJ2-Qb9hGYoLJ2riHrS0jkHXkoVM7h+QLYLWKAziWKRXNMaV0g_at_mail.gmail.com>



there was a typo, should look like

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 7474 0 2106

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

On Tue, Sep 13, 2011 at 5:08 PM, LS Cheng <exriscer_at_gmail.com> wrote:

> 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
>
>

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

Original text of this message