玉面飞龙的BLOG

V$SQL in Oracle10g

May 25th, 2007 | Categories: Boring | Tags:

文档上提到，从10gR2开始，Oracle将每5秒左右对运行时间比较长的SQL，更新这些SQL的统计信息，如buffer gets,disk reads等，便于监控SQL的运行状态。这样每次的statspack就更够比较精确的统计出高成本的SQL。

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

如下在statistics_level=typeical下测试，

Session 执行一个迪卡尔乘积，需要很多时间完成的SQL.

SQL> select count(*) from big,big;

观察这个SQL的统计信息是否及时更新，

SQL> select sql_text,CPU_TIME,BUFFER_GETS,DISK_READS,sysdate from v$sql
where sql_text='select count(*) from big,big';
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
----------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 138886474 2589064 2585070 2007-05-25 10:02:57
SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
----------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 150663672 2798411 2794091 2007-05-25 10:03:09
SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
----------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 521347231 9531699 9516942 2007-05-25 10:09:26
SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
----------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 527235870 9641775 9626846 2007-05-25 10:09:35

可以看到对于运行长时间的SQL能够及时更新统计信息。

然后在statistics_level=basic下测试，

SQL> alter system set statistics_level=basic;
System altered.

同样执行迪卡尔乘积,观察v$sql统计信息

SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
---------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 6969 17 0 2007-05-25 10:14:14
SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
---------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 6969 17 0 2007-05-25 10:15:00
SQL> /
SQL_TEXT CPU_TIME BUFFER_GETS DISK_READS SYSDATE
---------------------------------------- ----------- ---------- -------------------
select count(*) from big,big 6969 17 0 2007-05-25 10:15:45