OCP-043 SESSION_TRACE_DISABLE
来源:互联网 发布:皮肤差怎么调理知乎 编辑:程序博客网 时间:2024/05/23 18:38
134. In your test database, you find that a user's session is executing a lot of SQL statements, resulting in
the generation of a large number of trace files. While investigating the reason, you find that SQL trace has
been enabled at the instance level.
You want to disable SQL trace, remotely, only for that user session to reduce the volume of trace data
being generated.
How do you achieve this objective?
A.by setting the SQL_TRACE parameter to FALSE in the parameter file
B.by using DBMS_MONITOR.SESSION_TRACE_DISABLE to disable the tracing for the user session
C.by setting the SQL_TRACE parameter to FALSE by using the ALTER SYSTEM command in the user
session
D.by setting the SQL_TRACE parameter to FALSE by using the ALTER SESSION command in the user
session
Answer: B
Tracing for Session
The SESSION_TRACE_ENABLE
procedure enables the trace for a given database session identifier (SID), on the local instance.
To enable tracing for a specific session ID and serial number, determine the values for the session that you want to trace:
SELECT SID, SERIAL#, USERNAME FROM V$SESSION; SID SERIAL# USERNAME---------- ---------- ------------------------------ 27 60 OE...
Use the appropriate values to enable tracing for a specific session:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60, waits => TRUE, binds => FALSE);
The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE
procedure disables the trace for a given database session identifier (SID) and serial number. For example:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);
While the DBMS_MONITOR
package can only be invoked by a user with the DBA role, any user can also enable SQL tracing for their own session by using theDBMS_SESSION
package. The SESSION_TRACE_ENABLE
procedure can be invoked by any user to enable session-level SQL trace for their own session. For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE, binds => FALSE);
The TRUE
argument specifies that wait information will be present in the trace. The FALSE
argument specifies that bind information will not be present in the trace.
The SESSION_TRACE_DISABLE
procedure disables the trace for the invoking session. For example:
EXECUTE DBMS_SESSION.SESSION_TRACE_DISABLE();
Tracing for Entire Instance or Database
The DATABASE_TRACE_ENABLE
procedure enables SQL tracing for a given instance or an entire database. For example:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, instance_name => 'inst1');
In this example, the inst1
instance is specified to enable tracing for that instance. The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace. This example will result in SQL tracing of every SQL in the inst1
instance.
The DATABASE_TRACE_ENABLE
procedure will override all other session-level traces, but will be complementary to the client identifier, service, module, and action traces. All new sessions will inherit the wait and bind information specified by this procedure until the DATABASE_TRACE_DISABLE
procedure is called. When this procedure is invoked with the instance_name
parameter specified, it will reset the session-level SQL trace for the named instance. If this procedure is invoked without the instance_name
parameter specified, it will reset the session-level SQL trace for the entire database.
The DATABASE_TRACE_DISABLE
procedure disables the tracing for an entire instance or database. For example:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name => 'inst1');
In this example, all session-level SQL tracing will be disabled for the inst1
instance. To disable the session-level SQL tracing for an entire database, invoke theDATABASE_TRACE_DISABLE
procedure without specifying the instance_name
parameter:
EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE();
Enabling the SQL Trace Facility
Enable the SQL Trace facility for the session by using one of the following:
DBMS_SESSION.SET_SQL_TRACE
procedureALTER SESSION SET SQL_TRACE = TRUE;
Caution:
Because running the SQL Trace facility increases system overhead, enable it only when tuning SQL statements, and disable it when you are finished. It is recommended that you use the DBMS_SESSION or DBMS_MONITOR packages to enable SQL tracing for a session or an instance instead. For more information on using these packages, see "Enabling and Disabling for End to End Tracing".You might need to modify an application to contain the
ALTER
SESSION
statement. For example, to issue theALTER
SESSION
statement in Oracle Forms, invoke Oracle Forms using the-s
option, or invoke Oracle Forms (Design) using thestatistics
option. For more information on Oracle Forms, see theOracle Forms Reference.
To disable the SQL Trace facility for the session, enter:
ALTER SESSION SET SQL_TRACE = FALSE;
The SQL Trace facility is automatically disabled for the session when the application disconnects from Oracle.
You can enable the SQL Trace facility for an instance by setting the value of the SQL_TRACE
initialization parameter to TRUE
in the initialization file.
SQL_TRACE = TRUE
After the instance has been restarted with the updated initialization parameter file, SQL Trace is enabled for the instance and statistics are collected for all sessions. If the SQL Trace facility has been enabled for the instance, you can disable it for the instance by setting the value of the SQL_TRACE
parameter toFALSE
.
Note:
SettingSQL_TRACE
to TRUE can have a severe performance impact.
DBMS_MONITOR
The DBMS_MONITOR package let you use PL/SQL for controlling additional tracing and statistics gathering.
The chapter contains the following topics:
Summary of DBMS_MONITOR Subprograms
Summary of DBMS_MONITOR Subprograms
Table 60-1 DBMS_MONITOR Package Subprograms
CLIENT_ID_STAT_DISABLE Procedure
Disables statistic gathering previously enabled for a given Client Identifier
CLIENT_ID_STAT_ENABLE Procedure
Enables statistic gathering for a given Client Identifier
CLIENT_ID_TRACE_DISABLE Procedure
Disables the trace previously enabled for a given Client Identifier globally for the database
CLIENT_ID_TRACE_ENABLE Procedure
Enables the trace for a given Client Identifier globally for the database
DATABASE_TRACE_DISABLE Procedure
Disables SQL trace for the whole database or a specific instance
DATABASE_TRACE_ENABLE Procedure
Enables SQL trace for the whole database or a specific instance
SERV_MOD_ACT_STAT_DISABLE Procedure
Disables statistic gathering enabled for a given combination of Service Name, MODULE
and ACTION
SERV_MOD_ACT_STAT_ENABLE Procedure
Enables statistic gathering for a given combination of Service Name, MODULE
and ACTION
SERV_MOD_ACT_TRACE_DISABLE Procedure
Disables the trace for ALL enabled instances for a or a given combination of Service Name, MODULE
and ACTION
name globally
SERV_MOD_ACT_TRACE_ENABLE Procedure
Enables SQL tracing for a given combination of Service Name, MODULE
and ACTION
globally unless an instance_name
is specified
SESSION_TRACE_DISABLE Procedure
Disables the previously enabled trace for a given database session identifier (SID) on the local instance
SESSION_TRACE_ENABLE Procedure
Enables the trace for a given database session identifier (SID) on the local instance
SESSION_TRACE_DISABLE Procedure
This procedure will disable the trace for a given database session at the local instance.
Syntax
DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 60-12 SESSION_TRACE_DISABLE Procedure Parameters
session_id
Name of the service for which SQL trace is disabled.
serial_num
Serial number for this session.
Usage Notes
If serial_num
is NULL
but session_id
is specified, a session with a given session_id is no longer traced irrespective of its serial number. If both session_id
andserial_num
are NULL
, the current user session is no longer traced. It is illegal to specify NULL
session_id
and non-NULL
serial_num
. In addition, the NULL
values are default and can be omitted.
Examples
To enable tracing for a client with a given client session ID:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);;
SESSION_TRACE_ENABLE Procedure
This procedure enables a SQL trace for the given Session ID on the local instance
Syntax
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL, waits IN BOOLEAN DEFAULT TRUE, binds IN BOOLEAN DEFAULT FALSE)
Parameters
Table 60-13 SESSION_TRACE_ENABLE Procedure Parameters
session_id
Database Session Identifier for which SQL tracing is enabled. Specifying NULL
means that my current session should be traced.
serial_num
Serial number for this session. Specifying NULL
means that any session which matches session_id
(irrespective of serial number) should be traced.
waits
If TRUE
, wait information is present in the trace.
binds
If TRUE
, bind information is present in the trace.
Usage Notes
The procedure enables a trace for a given database session, and is still useful for client/server applications. The trace is enabled only on the instance to which the caller is connected, since database sessions do not span instances. This tracing is strictly local to an instance.
If serial_num
is NULL
but session_id
is specified, a session with a given session_id is traced irrespective of its serial number. If both session_id
and serial_num
are NULL
, the current user session is traced. It is illegal to specify NULL
session_id
and non-NULL
serial_num
. In addition, the NULL
values are default and can be omitted.
Examples
To enable tracing for a client with a given client session ID:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(7,4634, TRUE, FALSE);
To disable tracing specified in the previous step:
EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(7,4634);
Either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5);
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(5, NULL);
traces the session with session ID of 5, while either
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE();
or
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL);
traces the current user session. Also,
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL, TRUE, TRUE);
traces the current user session including waits and binds. The same can be also expressed using keyword syntax:
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>TRUE);
- OCP-043 SESSION_TRACE_DISABLE
- ocp-043
- ocp 认证 043
- OCP-043 RMAN BLOCKRECOVER
- OCP-043 SQL Tuning
- OCP-043 ASM_DISKSTRING
- OCP-043 drop diskgroup
- OCP-043 DB_nK_CACHE_SIZE
- OCP-043 large pool
- OCP-043 automatic PGA
- OCP-043session_longops
- OCP-043 Pending Area
- OCP-043 DBMS_SCHEDULER.LOGGING_RUNS
- ocp-043 DB_CREATE_FILE_DEST
- ocp 043 1-50
- ocp 043 51-90
- ocp-v13-043
- ocp
- hdu 3718
- WDK7.6配置使用STL
- Java 参数列表
- 548 - Tree
- 内网远程隧道 – 内网对内网远程桌面控制
- OCP-043 SESSION_TRACE_DISABLE
- i2c驱动之难点释疑
- 各种排序算法Java实现
- 测试之旅——【测试用例设计】——关于测试类型与归纳用例用例管理
- 程序操作EXCEL报错解决方法大汇总
- javascript DOM与javascript交互详解
- Extjs4---grid+servlet分页查询
- cocos2d-x游戏开发学习笔记
- 雨中飘荡的回忆