[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
跟蹤看看 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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-714377/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- alter session setSession
- ALTER SESSION SET EVENTSSession
- alter session|system set eventsSession
- alter session set events /Oracle跟蹤SessionOracle
- alter session set events 'immediate trace name controlf level 8'Session
- alter session set events語句dump 控制檔案頭資訊示例Session
- alter session set events語句dump REDO日誌檔案頭資訊示例Session
- AUTHID DEFINER\authid current_user與alter session set current_schemaSession
- 記一次ALTER SESSION SET hash_join_enabled specifies an obsolete parameterSession
- alter system kill sessionSession
- alter database和alter system和alter session的區別DatabaseSession
- 轉儲控制檔案時遇到的知識點:所有alter session set eventSession
- alter system set event和set events的區別
- oracle10g_alter session set events 'immediate trace name controlf level 8'dumpOracleSession
- ALTER SESSION ADVISE ClauseSession
- ALTER SYSTEM DISCONNECT SESSION ClauseSession
- alter system archive log current / all / switch logfileHive
- 使用alter session set current_schema=scott用explain plan for檢視scott使用者的sqlSessionAISQL
- alter system set events 相關知識
- alter system set events 知識 [final]
- alter system set events相關知識:
- alter system set events相關知識
- alter system kill session 不成功Session
- Session store not set on request.Session
- Laravel - Session store not set on requestLaravelSession
- Alter system kill session and ORA-00031: session marked for killSession
- 【Oracle】alter system set events 相關知識Oracle
- ALTER SYSTEM KILL SESSION這麼沒力?(轉)Session
- v$lock之alter table drop column與alter table set unused column區別系列五
- alter system switch log file 與 archive log current/all 區別Hive
- How To List All The Named Events Set For A Database [ID 436036.1]Database
- alter system set events相關知識(轉帖2)
- alter system set events相關知識(轉帖1)
- alter system disconnect/kill session 'sid,serial#';Session
- ora-00054 , alter system kill session 'id,serial#'Session
- 【Django】關聯查詢set.all() 方法的使用Django
- How To List All The Named Events Set For A Database (Doc ID 436036.1)Database
- "alter system kill session" can crash the instance if IO slaves usedSession