Re: wrong data in v$sqlstats after cursor invalidation
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-lReceived on Tue Sep 13 2011 - 10:09:32 CDT