[20120103] alter session set statistics_level = all;

[20120103] alter session set statistics_level = all;

跟踪看看 alter session set statistics_level = all,修改了哪些参数：

SQL> alter session set events '10046 trace name context forever,level 12';
SQL> alter session set statistics_level = all;
SQL> alter session set events '10046 trace name context off';

SQL> exec dbms_monitor.session_trace_enable();
SQL> alter session set statistics_level = all;
SQL> exec dbms_monitor.session_trace_disable();
--这个跟踪不到！

$cat hide.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22

select
   a.ksppinm  name,
   a.ksppdesc DESCRIPTION,
   b.ksppstdf DEFAULT_VALUE,
   b.ksppstvl SESSION_VALUE,
   c.ksppstvl SYSTEM_VALUE
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx
 and a.indx = c.indx
 and a.ksppinm like '%&1%'
order by 1;


sqlplus sys as sysdba
SQL> spool a1.txt
SQL> @hide %

sqlplus sys as sysdba
SQL> spool a2.txt
SQL> alter session set statistics_level = all;
SQL> @hide %

$ diff -Nur a1.txt  a2.txt
--- a1.txt      2012-01-03 16:19:32.000000000 +0800
+++ a2.txt      2012-01-03 16:20:05.000000000 +0800
@@ -1,4 +1,4 @@
-16:19:26 SQL> @hide
+16:19:57 SQL> @hide
 Enter value for 1: %
 old  10:  and a.ksppinm like '%&1%'
 new  10:  and a.ksppinm like '%%%'
@@ -1808,7 +1808,7 @@
 _row_locking                             row-locking                                                        TRUE                   always                 always
 _row_shipping_explain                    enable row shipping explain plan support                           TRUE                   FALSE                  FALSE
 _row_shipping_threshold                  row shipping column selection threshold                            TRUE                   80                     80
-_rowsource_execution_statistics          if TRUE, Oracle will collect rowsource statistics                  TRUE                   FALSE                  FALSE
+_rowsource_execution_statistics          if TRUE, Oracle will collect rowsource statistics                  TRUE                   TRUE                   FALSE
 _rowsource_profiling_statistics          if TRUE, Oracle will capture active row sources in v$active_sessio TRUE                   TRUE                   TRUE
                                          n_history

@@ -2530,11 +2530,11 @@
 standby_archive_dest                     standby database archivelog destination text string                TRUE                   ?/dbs/arch             ?/dbs/arch
 standby_file_management                  if auto then files are created/dropped automatically on standby    TRUE                   MANUAL                 MANUAL
 star_transformation_enabled              enable the use of star transformation                              TRUE                   FALSE                  FALSE
-statistics_level                         statistics level                                                   TRUE                   TYPICAL                TYPICAL
+statistics_level                         statistics level                                                   TRUE                   ALL                    TYPICAL
 streams_pool_size                        size in bytes of the streams pool                                  TRUE                   0                      0
 tape_asynch_io                           Use asynch I/O requests for tape devices                           TRUE                   TRUE                   TRUE
 thread                                   Redo thread to mount                                               TRUE                   0                      0
-timed_os_statistics                      internal os statistic gathering interval in seconds                TRUE                   0                      0
+timed_os_statistics                      internal os statistic gathering interval in seconds                TRUE                   60                     0
 timed_statistics                         maintain internal timing statistics                                TRUE                   TRUE                   TRUE
 trace_enabled                            enable in memory tracing                                           TRUE                   TRUE                   TRUE
 tracefile_identifier                     trace file custom identifier                                       TRUE
@@ -2553,4 +2553,4 @@

 2399 rows selected.

-16:19:31 SQL> quit
+16:20:03 SQL> quit


对比可以看出:
-_rowsource_execution_statistics          if TRUE, Oracle will collect rowsource statistics                  TRUE                   FALSE                  FALSE
+_rowsource_execution_statistics          if TRUE, Oracle will collect rowsource statistics                  TRUE                   TRUE                   FALSE

-statistics_level                         statistics level                                                   TRUE                   TYPICAL                TYPICAL
+statistics_level                         statistics level                                                   TRUE                   ALL                    TYPICAL

-timed_os_statistics                      internal os statistic gathering interval in seconds                TRUE                   0                      0
+timed_os_statistics                      internal os statistic gathering interval in seconds                TRUE                   60                     0

修改参数:statistics_level = all后:

_rowsource_execution_statistics=true
timed_os_statistics=true