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

SQL> spool a2.txt

$ 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

對比可以看出:

修改參數:statistics_level = all後:

_rowsource_execution_statistics=true

timed_os_statistics=true