[Oracle] How to use DBMS

Oracle

Finishing: Fenng
Date: 24-oct-2004
Source: Http://www.dbanotes.net
Version: 0.9

Dbms_support is a software package provided by Oracle. Used by internal support personnel for more efficient tracking of SQL. There is no formal documentation for this package, and the system does not install this package by default. If you need to use, you need to set up separately. There should be dbmssupp.sql,prvtsupp.plb two files in your $oracle_home/rdbms/admin/directory.
sql> connect/as sysdba connected.sql> @ $ORACLE _home/rdbms/admin/dbmssupp.sqlpackage created. Package body created. Sql>
If you want other users to be able to use this package, consider submitting the following authorization action (public can be replaced with a specific user) and creating a synonym:
Sql> GRANT EXECUTE on dbms_support to public; Grant succeeded. sql> CREATE public synonym dbms_support for dbms_support;
The structure of the dbms_support is not complex:
sql> desc dbms_supportfunction mysid RETURNS numberfunction package_version RETURNS varchar2procedure START_TRACE Argument Name Type in/out Default? -------------------------------------------------------------------WAITS Boolean in DEFAULT binds Boolean in DEFAULTP Rocedure start_trace_in_session Argument Name Type in/out Default? -------------------------------------------------------------------SID number in serial number in WAITS BOOLEAN in DEF Ault binds BOOLEAN in defaultprocedure stop_traceprocedure stop_trace_in_session Argument Name Type in/out Default? -------------------------------------------------------------------SID number in serial number insql>
The SID (as well as serial#) for the current session is available through Mysid:
Sql> SELECT SYS.dbms_support.mysid from DUAL; Mysid----------25sql> sql>select sid, serial# from v$session WHERE SID = dbms_support.mysid; SID serial#--------------------4328sql>
The lowest pl/sql version that the package can support can be obtained 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 do I activate SQL tracing? Start_trace has two parameters: Waits and binds. Where waits defaults to true,binds defaults to false. If a simple submission:
Sql>execute Dbms_support.start_trace;
The 10046 event is activated by default, Level 1. It is equivalent to:
Sql>alter session SET EVENTS ' 10046 Trace name Context forever, Level 1 ';
If you submit:
Sql>execute Dbms_support.start_trace (binds=>true);
Activates the 10046 event, Level 4. Equivalent to:
Sql>alter session SET EVENTS ' 10046 Trace name Context forever, Level 4 ';
If you submit:
Sql>execute Dbms_support.start_trace (waits=>true);
Activates the 10046 event, Level 8. Equivalent to:
Sql>alter session SET EVENTS ' 10046 Trace name Context forever, Level 8 ';
To stop tracking submissions:
Sql>execute Dbms_support.stop_trace;
The start_trace_in_session has four parameters and can refer to the output of the describe above. If you want to track another user's session, you'll need to use it. Examples of the above output (SID and serial# are required):
Sql>execute dbms_support.start_trace_in_session (4328, waits->true,binds=>true,);
The above command has the same function as the following statement:
Sql>execute Dbms_system.set_ev (25, 4328, 10046, 4, 8);
To stop tracking submissions:
Sql>execute Dbms_support.stop_trace_in_session (25, 4328);
Other issues
This package is available from Oracle RDBMS 7.2. However, it may not exist on some versions/platforms.

Note: The basic explanation of each level:
Level 0 = No statistics generated Level 1 = Standard trace output including parsing, executes and fetches plus. Level 2 = Same AS Level 1. Level 4 = Same AS Level 1 but includes bind information level 8 = Same AS Level 1 but includes wait ' s information level 12 = Same AS Level 1 but includes binds and waits

Reference information
Metalink-http://metalink.oracle.com (note 62294.1)
The writer Fenng, a US-funded corporate DBA, who spends his spare time in various database-related technical forums and enjoys it. The current focus is on how to effectively build enterprise applications using Oracle databases. There is a little research on Oracle tuning and troubleshooting.
Personal technology site: http://www.dbanotes.net/. You can contact him via email dbanotes@gmail.com. Original source http://www.dbanotes.net/Oracle/Oracle_DBMS_SUPPORT.htm

Back Page
All articles (by fenng) are licensed under a Creative Commons.
I would welcome any feedback. Please send questions, comments or corrections to dbanotes@gmail.com