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.
==================================================================
session 1
SQL>alter system set statistics_level=basic;
SQL>select count(*) from dba_objects,dba_objects;
session 2
SQL>select cpu_time,elapsed_time,disk_reads from v$sql
where sql_text='select count(*) from dba_objects,dba_objects ';
cpu_time and elapsed_time are updated every 5 seconds
==================================================================
session 1
SQL>alter system set statistics_level=TYPICAL;
SQL>select count(*) from dba_objects,dba_objects;
session 2
SQL>select cpu_time,elapsed_time,disk_reads from v$sql
where sql_text='select count(*) from dba_objects,dba_objects ';
cpu_time and elapsed_time are updated immediately.
reference:
Oracle 10g 版本最后不再支持（而不是反对）基于规则的优化器 (RBO)。因为 CBO 依靠准确的（或者说
比较准确的）统计信息来产生优化的执行路径，所以 DBA 需要确保定期收集统计信息，创建另一个执行核
对清单。在 10g 之前，这一过程可能由于多种原因而徒劳无功。在 10g 中已经消除了很多这类的顾虑，
它能够自动收集统计信息。在 Oracle9i 中，您可以通过打开表监视选项 (ALTER TABLE ...MONITORING)
然后检查这些表的视图 DBA_TAB_MODIFICATIONS，检查表中的数据是否已明显更改过。
在 10g 中，已不再使用 MONITORING 语句了。而通过设置初始化参数 STATISTIC_LEVEL 为 TYPICAL 或
ALL，就可以自动收集统计信息。（默认值为 TYPICAL，因此可以随即启用自动收集统计信息的功
能。）Oracle 数据库 10g 具有一个预定义的调度程序作业，名称为 GATHER_STATS_JOB，它由
STATISTIC_LEVEL 参数的适当数值所激活。
SQL> show parameter statistics_
NAME TYPE VALUE
------------------------------------ ----------- -----------------
statistics_level string TYPICAL
statistics_level 默认是typical，在10g中表监控是激活的，强烈建议在10g中此参数的值是typical。如
果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)
统计信息的收集是资源相当密集的工作，因此您可能希望确保它不影响数据库的正常操作。在 10g 中，您
可以使这一工作自动完成：可对一个名为 AUTO_TASK_CONSUMER_GROUP 的特定资源用户组进行预定义，用
于自动执行一些任务，比如收集统计信息。该用户组确保这些统计信息收集作业的优先权低于默认用户组
，因此减少或消除了自动化任务占用整个机器的风险。
这个自动任务默认情况下在工作日晚上10:00 - 6:00 和周末全天开启。调用
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC 收集统计信息。该过程首先检测统计信息缺失和陈旧的对
象。然后确定优先级，再开始进行统计信息。
可以通过以下查询这个JOB的运行情况：
SQL> set lines 256
SQL> col last_start_date format a40
SQL> col last_run_duration format a30
SQL> SELECT owner, enabled, auto_drop, restartable, state, run_count, failure_count,
last_start_date, last_run_duration FROM dba_scheduler_jobs where job_name =
'GATHER_STATS_JOB';
OWNER ENABL AUTO_ RESTA STATE RUN_COUNT FAILURE_COUNT
LAST_START_DATE LAST_RUN_DURATION
------------------------------ ----- ----- ----- --------------- ---------- ------------- --
-------------------------------------- ------------------------------
SYS TRUE FALSE TRUE SCHEDULED 57 0
27-NOV-08 10.00.02.228110 PM +08:00 +000000000 00:00:25.870394
如果希望将参数 STATISTIC_LEVEL 设为 TYPICAL 却不希望自动收集统计信息时该怎么办？很简单。只需
使用以下语句来禁用调度程序作业即可：
SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
那么您为什么要这样做呢？有很多原因 — 其中一个原因是，虽然表中大部分行发生了变化，但分布情况可
能没有改变，这在数据仓库中很常见。在这种情况下，您不希望再次收集统计信息，而只是希望重用原来
的统计信息。另一个原因可能是您正在使用分区交换功能来刷新物化视图 (MV)，并且不希望收集关于物化
视图的统计信息，因为关于被交换表的统计信息也会被导入。但是，您也可以将特定的表排除在自动统
信息收集作业之外，而不需要禁止整个作业。
在优化器收集统计信息时可能出现的复杂情况之一是执行计划的改变 — 也就是说，原来的优化方法在收集
统计信息之前一直工作良好，但是在此之后，由于新收集的统计信息产生了不良计划，导致查询突然出错
。这种问题并不少见。
为避免这种灾难，统计信息的收集作业在收集新信息之前保存当前的统计信息。如果出现问题，您总可以
返回到原有的统计信息，或者至少可以检查二者之间的不同之处，以便于解决问题。
例如，假设在 5 月 31 日晚上 10:00 开始运行表 REVENUE 上的统计信息收集作业，而随后查询的性能变
差。Oracle 保存了原有的统计信息，您可以通过执行以下命令重新获取这些信息：
SQL> exec dbms_stats.restore_table_stats ( 'ARUP', 'REVENUE', '31-MAY-04 10.00.00.000000000
PM -04:00');
此命令恢复到 5 月 31 日晚上 10:00 为止的统计信息,时间信息是以 TIMESTAMP 数据类型提供。这样您
就立即还原了由新的统计信息收集程序所作的更改。
您能够恢复的时间长度是由保留参数所决定的。要查看当前的保留参数，可使用以下查询：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
在本示例中表示可以保存相当于 31 天的统计信息，但并不能予以保证。要了解统计信息所覆盖到的确切
时间和日期，只需使用以下查询：
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
----------------------------------------------------------------
27-10月-08 11.14.20.357622000 下午 +08:00
该查询表明可用的最陈旧统计信息日期为 10 月 27 日下午 11:14。
您可以通过执行内建的函数将保留时间设为不同的值。例如，要将其设为 45 天，可使用：
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45);
--End--
