Squiring: FENNG Date: 24-Oct-2004 Source: http://www.dbanotes.net version: 0.9
DBMS_SUPPORT is a package provided by Oracle. Internal support staff is used to track SQL more effectively. This package does not have formal explanation files, by default, the system does not install this package. If you need to use it, you need to make separate settings. There should be dbmssupp.sql, prvTSupp.plb. Two files should be present in your $ Oracle_Home / Rdbms / Admin / Directory.
SQL> Connect / as sysdba
Connected.
SQL> @ $ oracle_home / rdbms / admin / dbmssupp.sql
Package created.
Package body created.
SQL>
If you want other users, you can use this package, you can consider submitting the following authorization (PUBLIC can replace the specific user) and create synonyms:
SQL> Grant Execute On DBMS_SUPPORT TO PUBLIC;
Grant succeeded.
SQL> CREATE PUBLIC SYNONYM DBMS_SUPPORT for DBMS_SUPPORT;
The structure of dbms_support is not complicated:
SQL> DESC DBMS_SUPPORT
Function Mysid Returns Number
Function package_version returns varchar2
Procedure Start_Trace
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Waits Boolean in Default
Binds Boolean in Default
Procedure start_trace_in_session
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Sid Number in
Serial Number in
Waits Boolean in Default
Binds Boolean in Default
Procedure stop_trace
Procedure stop_trace_in_session
Argument Name Type In / Out Default?
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------- --------
Sid Number in
Serial Number INSQL>
Among them, the current session (and Serial #) of the current session (and serial) can be obtained by MySID:
SQL> SELECT SYS.DBMS_SUPPORT.MYSID from DUAL
Mysid
------------
25
SQL>
SQL> SELECT SID, Serial # from v $ session where sid = dbms_support.mysid;
Sid serial #
---------- ----------
25 4328
SQL>
You can get the lowest PL / SQL version of the package that can be supported by package_version:
SQL> SELECT DBMS_SUPPORT.PACKAGE_VERSION
2 from dual;
Package_version
-------------------------------------------------- ------------------
DBMS_SUPPORT VERSION 1.0 (17-AUG-1998) - Requires Oracle 7.2 - 8.0.5
How to activate SQL Tracking? Start_Trace has two parameters: Waits and Binds. Where waits defaults to true, Binds defaults to false. If you are simple to submit:
SQL> EXECUTE DBMS_SUPPORT.START_TRACE;
The 10046 event is activated by default, Level 1. Also equivalent:
SQL> ALTER Session Set Events '10046 TRACE NAME CONTEXT ForeVER, Level 1';
If submitted:
SQL> EXECUTE DBMS_SUPPORT.Start_Trace (BINDS => true);
Activate 10046 events, Level 4. Equivalent to:
SQL> ALTER session set events '10046 Trace Name Context Forever, Level 4';
If submitted:
SQL> EXECUTE DBMS_SUPPORT.START_TRACE (waits => true);
Activate 10046 events, Level 8. Equivalent to:
SQL> ALTER Session Set Events '10046 Trace Name Context Forever, Level 8';
Stop tracking submission:
SQL> EXECUTE DBMS_SUPPORT.STOP_TRACE;
START_TRACE_IN_SESSION has four parameters that can refer to the output of DESCRIBE. If you want to track the session of other users, you should use it. Examples of the output above (requires SID and Serial #):
SQL> EXECUTE DBMS_SUPPORT.START_TRACE_IN_SESSION (25, 4328, Waits-> True, Binds => true,);
Above this command and the following statements are the same function:
SQL> EXECUTE DBMS_SYSTEM.SET_EV (25, 4328, 10046, 4, 8);
Stop tracking submission:
SQL> EXECUTE DBMS_SUPPORT.STOP_TRACE_IN_SESSION (25, 4328);
other problems
This package is available from Oracle RDBMS 7.2. But there may be no existence on some versions / platforms. Note: Basic explanations of each level:
Level 0 = no statistics generated, executes and fetches plus more.
Level 2 = Same as level 1.
Level 4 = Same As Level 1 But includees bind information
Level 8 = Same As Level 1 But includees Wait's Information
Level 12 = Same As Level 1 But includees binds and waits
Reference Information
Metalink - http://metalink.racle.com (Note 62294.1)
Letter author
Fenng, a US-raising company DBA, spare time, mixed with each database, is not tired. Currently payment of how to use the Oracle database effectively build enterprise applications. A little study on Oracle Tuning, Troubleshooting. Personal technology site:
http://www.dbanotes.net/
. You can contact him by email dbanotes@gmail.com.
Origin
http://www.dbanotes.net/oracle/oracle_dbms_support.htm
Back home
All articles (by fenng) Are Licensed Under A
Creative Commons license
.. W ,,,,,,,,,
