Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com
  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
使用DBMS_MONITOR包单独TRACE SQL
Tracing SQL in Oracle Database 10g
By Kimberly Floss
 
New tools help you better understand the performance of your applications.
 
In a busy production environment with many active users, tracing a SQL session is time-consuming and complicated, because processing SQL statements in any multitier system that uses a connection pool can span multiple processes, or even different instances.
 
With Oracle Database 10g, Oracle rationalizes SQL tracing through a new built-in package, DBMS_MONITOR, which encompasses the functionality of previously undocumented trace tools, such as the DBMS_SUPPORT package. Now you can easily trace any user's session from beginning to end—from client machine to middle tier to back end—and generate trace files based on specific client ID, module, or action.
 
In addition, Oracle Database 10g includes a new utility, trcsess, that lets you selectively extract trace data from numerous trace files and save them into a single file, based on criteria such as session ID or module name. This utility is especially useful in a shared server configuration, since a dispatcher may route each user request to a different shared server process, resulting in multiple trace files for any given session. Rather than digging through numerous trace files, Oracle Database 10g's trcsess lets you obtain consolidated trace information pertaining to a single user session.
 
Getting Started
 
As with prior Oracle database releases, trace files are output to the directory specified by the user_dump_dest parameter of the server's initialization file (or spfile). The default location depends on the operating system; for example, for Microsoft Windows platforms using DBCA, the default is $ORACLE_BASE\instance_name\admin\udump, where instance_name is the name of the Oracle instance. You can dynamically change this parameter by using the alter system command:

alter system set user_dump_dest="c:\kflosstrace";

You can also add your own marker to the trace file names so you can more easily find the generated files. To do so, set the tracefile_identifier initialization parameter before starting a trace:

alter session set
tracefile_identifier ="kfloss_test";

Trace files generated by this command have the string value you set appended to the filenames. Although neither of these alter commands is necessary, both make it easier to find the results of a tracing session.
 
Now that we've set these parameters, let's look at the new tracing package and the Oracle Enterprise Manager interface. Let's set up a trace by module name and client name, using the new DBMS_MONITOR package.
 
Setting Up Tracing with DBMS_MONITOR
 
The DBMS_MONITOR package has routines for enabling and disabling statistics aggregation as well as for tracing by session ID, or tracing based upon a combination of service name, module name, and action name. (These three are associated hierarchically: you can't specify an action without specifying the module and the service name, but you can specify only the service name, or only the service name and module name.) The module and action names, if available, come from within the application code. For example, Oracle E-Business Suite applications provide module and action names in the code, so you can identify these by name in any of the Oracle Enterprise Manager pages. (PL/SQL developers can embed calls into their applications by using the DBMS_APPLICATION_INFO package to set module and action names.)
 
Note that setting the module, action, and other paramters such as client_id no longer causes a round-trip to the database—these routines now piggyback on all calls from the application.
 
The service name is determined by the connect string used to connect to a service. User sessions not associated with a specific service are handled by sys$users (sys$background is the default service for the background processes). Since we have a service and a module name, we can turn on tracing for this module as follows:

SQL> exec dbms_monitor.serv_mod_act_trace_enable
(service_name=>'testenv', module_name=>'product_update');
 
PL/SQL procedure successfully completed.

We can turn on tracing for the client:

SQL> exec dbms_monitor.client_id_trace_enable
(client_id=>'kimberly');
 
PL/SQL procedure successfully completed.

Note that all of these settings are persistent—all sessions associated with the service and module will be traced, not just the current sessions.
 
To trace the SQL based on the session ID, look at the Oracle Enter-prise Manager Top Sessions page, or query the V$SESSION view as you likely currently do.

SQL> select sid, serial#, username
from v$session;
       SID     SERIAL# USERNAME
     ------    ------- ------------
       133       4152  SYS
       137       2418  SYSMAN
       139         53  KIMBERLY
       140        561  DBSNMP
       141          4  DBSNMP
. . .
       168          1
       169          1
       170          1
28 rows selected.

With the session ID (SID) and serial number, you can use DBMS_MONITOR to enable tracing for just this session:

SQL> exec dbms_monitor.session_trace_enable(139);
 
PL/SQL procedure successfully completed.

The serial number defaults to the current serial number for the SID (unless otherwise specified), so if that's the session and serial number you want to trace, you need not look any further. Also, by default, WAITS are set to true and BINDS to false, so the syntax above is effectively the same as the following:
 

SQL> exec dbms_monitor.session_trace_enable
(session_id=>139, serial_num=>53, waits=>true, binds=>false);
 

Note that WAITS and BINDS are the same parameters that you might have set in the past using DBMS_SUPPORT and the 10046 event.
 
If you're working in a production environment, at this point you'd rerun the errant SQL or application, and the trace files would be created accordingly.
 
Setting Up Tracing with Enterprise Manager
 
Setting up tracing through Oracle Enterprise Manager starts on the Top Consumers page (available from the Performance page in the Additional Monitoring Links section, as shown in Figure 1). This page shows the system's current resource usage by service, module, client, and action.
 
dbms_monitor
Figure 1: Oracle Enterprise Manager Top Consumers page 
 

You can click on the Top Services, Top Modules, Top Actions, Top Clients, or Top Sessions tabs to see the detail for each of these categories of top consumers, and then you can easily enable (or disable) SQL tracing from each of these pages. Simply select the item from the list on the page and then click on Enable SQL Trace to begin the trace (and click on Disable when you're finished).
 
You can enable (or disable) statistics aggregation for any items listed on these pages as well. (DBMS_MONITOR also provides routines for enabling and disabling aggregation.)
 
Analyzing Trace Results
 
Whether you use DBMS_MONITOR or Oracle Enterprise Manager to set up tracing, you'll use the trcsess command line tool to consolidate trace files. Click on the View SQL Trace button in Oracle Enterprise Manager to display a page that shows the syntax you'll use to consolidate all trace files.
 
Be sure to double-quote the strings, and add a ".trc" extension to the filename; otherwise, TKPROF won't accept it as a filename. Before executing the command, navigate to the directory specified in the user_dump_dest (or \udump, if you didn't change this parameter name).
 

C:\...\udump> trcsess output="kfloss.trc" service="testenv"
module="product update"
action="batch insert"
 

You can then run TKPROF against the consolidated trace file to generate a report.
 

C:\...\udump> tkprof kfloss.trc
output=kfloss_trace_report SORT=(EXEELA, PRSELA, FCHELA)
 

If you don't disable tracing, every session that runs that service and module will be traced. Thus, when you're finished, be sure to disable tracing by using either Oracle Enterprise Manager or the DBMS_MONITOR package.
 

--------------------------------------------------------------------------------
Kimberly Floss ( kimberly_floss@ioug.org ) is president of the International Oracle Users Group ( www.ioug.org ). She specializes in Oracle performance tuning and SQL tuning techniques and is also the author of Oracle SQL Tuning and CBO Internals (Rampant TechPress, 2004).
 
 
 
 
 
 
 
 
附DBMS_MONITOR包的定义说明
-------------------------------------

create or replace package sys.dbms_monitor is

  ------------

  --  OVERVIEW

  --

  --  This package provides database monitoring functionality, initially

  --  in the area of statistics aggregation and SQL tracing

 

  --  SECURITY

  --

  --  runs with SYS privileges.

 

  --  CONSTANTS to be used as OPTIONS for various procedures

  --  refer comments with procedure(s) for more detail

 

  all_modules                    CONSTANT VARCHAR2 ( 14 ) := '###ALL_MODULES' ;

  all_actions                    CONSTANT VARCHAR2 ( 14 ) := '###ALL_ACTIONS' ;

 

  -- Indicates that tracing/aggregation for a given module should be enabled

  -- for all actions

 

  ----------------------------

 

  ----------------------------

  --  PROCEDURES AND FUNCTIONS

  --

  PROCEDURE client_id_stat_enable(

    client_id IN VARCHAR2 );

 

  --  Enables statistics aggregation for the given Client ID

  --  Input arguments:

  --   client_id           - Client Identifier for which the statistics

  --                         colection is enabled

 

  PROCEDURE client_id_stat_disable(

    client_id IN VARCHAR2 );

 

  --  Disables statistics aggregation for the given Client ID

  --  Input arguments:

  --   client_id           - Client Identifier for which the statistics

  --                         colection is disabled

 

  PROCEDURE serv_mod_act_stat_enable(

    service_name IN VARCHAR2 ,

    module_name IN VARCHAR2 ,

    action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

 

  --  Enables statistics aggregation for the given service/module/action

  --  Input arguments:

  --   service_name        - Service Name for which the statistics

  --                         colection is enabled

  --   module_name         - Module Name for which the statistics

  --                         colection is enabled

  --   action_name         - Action Name for which the statistics

  --                         colection is enabled. The name is optional.

  --                         if omitted, statistic aggregation is enabled

  --                         for all actions in a given module

 

  PROCEDURE serv_mod_act_stat_disable(

    service_name IN VARCHAR2 ,

    module_name IN VARCHAR2 ,

    action_name IN VARCHAR2 DEFAULT ALL_ACTIONS);

 

  --  Disables statistics aggregation for the given service/module/action

  --  Input arguments:

  --   service_name        - Service Name for which the statistics

  --                         colection is disabled

  --   module_name         - Module Name for which the statistics

  --                         colection is disabled

  --   action_name         - Action Name for which the statistics

  --                         colection is disabled. The name is optional.

  --                         if omitted, statistic aggregation is disabled

  --                         for all actions in a given module

 

  PROCEDURE client_id_trace_enable(

    client_id IN VARCHAR2 ,

    waits IN BOOLEAN DEFAULT TRUE ,

    binds IN BOOLEAN DEFAULT FALSE );

 

  --  Enables SQL for the given Client ID

  --  Input arguments:

  --   client_id           - Client Identifier for which SQL trace

  --                         is enabled

  --   waits               - If TRUE, wait information will be present in the

  --                         the trace

  --   binds               - If TRUE, bind information will be present in the

  --                         the trace

 

  PROCEDURE client_id_trace_disable(

    client_id IN VARCHAR2 );

 

  --  Disables SQL trace for the given Client ID

  --  Input arguments:

  --   client_id           - Client Identifier for which SQL trace

  --                         is disabled

 

  PROCEDURE serv_mod_act_trace_enable(

    service_name IN VARCHAR2 ,

    module_name IN VARCHAR2 DEFAULT ALL_MODULES,

    action_name IN VARCHAR2 DEFAULT ALL_ACTIONS,

    waits IN BOOLEAN DEFAULT TRUE ,

    binds IN BOOLEAN DEFAULT FALSE ,

    instance_name IN VARCHAR2 DEFAULT NULL );

 

  --  Enables SQL trace for the given service/module/action

  --  Input arguments:

  --   service_name        - Service Name for which SQL trace

  --                         is enabled

  --   module_name         - Module Name for which SQL trace

  --                         is enabled. The name is optional.

  --                         if omitted, SQL trace is enabled

  --                         for all modules and actions actions in a given

  --                         service

  --   action_name         - Action Name for which SQL trace

  --                         is enabled. The name is optional.

  --                         if omitted, SQL trace is enabled

  --                         for all actions in a given module

  --   waits               - If TRUE, wait information will be present in the

  --                         the trace

  --   binds               - If TRUE, bind information will be present in the

  --                         the trace

  --   instance_name       - if set, restricts tracing to the named instance

 

  PROCEDURE serv_mod_act_trace_disable(

    service_name IN VARCHAR2 ,

    module_name IN VARCHAR2 DEFAULT ALL_MODULES,

    action_name IN VARCHAR2 DEFAULT ALL_ACTIONS,

    instance_name IN VARCHAR2 DEFAULT NULL );

 

  --  Disables SQL trace for the given service/module/action

  --  Input arguments:

  --   service_name        - Service Name for which SQL trace

  --                         is disabled

  --   module_name         - Module Name for which SQL trace

  --                         is disabled. The name is optional.

  --                         if omitted, SQL trace is disabled

  --                         for all modules and actions actions in a given

  --   action_name         - Action Name for which SQL trace

  --                         is disabled. The name is optional.

  --                         if omitted, SQL trace is disabled

  --                         for all actions in a given module

  --                         the trace

  --   instance_name       - if set, restricts disabling to the named instance

 

  PROCEDURE 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 );

 

  --  Enables SQL trace for the given Session ID

  --  Input arguments:

  --   session_id          - Session Identifier for which SQL trace

  --                         is enabled. If omitted (or NULL), the

  --                         user's own session is assumed

  --   serial_num          - Session serial number for which SQL trace

  --                         is enabled. If omitted (or NULL), only

  --                         the session ID is used to determine a session

  --   waits               - If TRUE, wait information will be present in the

  --                         the trace

  --   binds               - If TRUE, bind information will be present in the

  --                         the trace

 

  PROCEDURE session_trace_disable(

    session_id IN BINARY_INTEGER DEFAULT NULL ,

    serial_num IN BINARY_INTEGER DEFAULT NULL );

 

  --  Disables SQL trace for the given Session ID

  --  Input arguments:

  --   session_id          - Session Identifier for which SQL trace

  --                         is disabled

  --   serial_num          - Session serial number for which SQL trace

  --                         is disabled

 

  PROCEDURE database_trace_enable(

    waits IN BOOLEAN DEFAULT TRUE ,

    binds IN BOOLEAN DEFAULT FALSE ,

    instance_name IN VARCHAR2 DEFAULT NULL );

 

  --  Enables SQL trace for the whole database or given instance

  --  Input arguments:

  --   waits               - If TRUE, wait information will be present in the

  --                         the trace

  --   binds               - If TRUE, bind information will be present in the

  --                         the trace

  --   instance_name       - if set, restricts tracing to the named instance

 

  PROCEDURE database_trace_disable(

    instance_name IN VARCHAR2 DEFAULT NULL );

 

  --  Disables SQL trace for the whole database or given instance

  --  Input arguments:

  --   instance_name       - if set, restricts disabling to the named instance

end ;

posted on 2009-04-13 22:26 decode360 阅读(314) 评论(0)  编辑  收藏 所属分类: 06.PLSQL

只有注册用户登录后才能发表评论。
网站导航: