Oracle Session Tracing Part IV

Author   : James F. Koopmann

More at  : www.jameskoopmann.com

Part IV in our series will focus on determining which internal Oracle views hold the information to our enabled statistical gathering.

· In Part I we learned how to set the CLIENT_IDENTIFIER session variable to more accurately pinpoint resource consumption for an individual user or group of users.

· In Part II we learned how to set the ACTION and MODULE name so that we could track where in our code transactions where being executed and track the use of resources by transaction type or section of code.

· In Part III we focused on enabling tracing of statistics through the DBMS_MONITOR package.

· Now in part IV of this series we will take a look at the internal views within Oracle that keep track of the enabled statistics gathering enabled in Part III.

In Part III we left off displaying the enabled traces and statistic gatherings we enabled through the DBMS_MONITOR package. Recall that in Part III we queried the DBA_ENABLED_AGGREGATIONS view and had a listing such as in Listing 1. Table 1 gives a breakdown of the DBA_ENABLED_AGGREGATIONS view as I have renamed some of the columns for Listing 1.

Listing 1

DBA_ENABLED_AGGREGATIONS

SQL> SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

AGGREGATION          SERVICE              MODULE               ACTION

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

CLIENT_ID            webclient

SERVICE_MODULE       ACCT                 PAYROLL

Table 1

DBA_ENABLED_AGGREGATIONS

Column	Description
AGGREGATION_TYPE	This is the type of statistical aggregation being done. This relates to the actual procedure called in the DBMS_MONITOR package. In Listing 1 I we have called the CLIENT_ID_STAT_ENABLE and SERV_MOD_ACT_STAT_ENABLE procedures
PRIMARY_ID	This is the CLIENT_IDENTIFIER or SERVICE_NAME in the call
QUALIFIER_ID1	The module name
QUALIFIER_ID2	The action name

In the last three parts of this series we have also zeroed in on setting different session environment variables. Those variables were CLIENT_IDENTIFIER, ACTION, and MODULE. We have also tracked some of the sessions by SID, SESSION_ID, SERVICE_NAME, and SERIAL#. I thought it might be interesting to take a look at where within Oracle’s internal views these columns might be defined as this could help us in our future needs of investigation. Instead of searching through the endless documentation of Oracle, I instead decided to produce a query (Listing 2) in which I queried the DBA_TAB_COLUMNS view to take a look at what internal Oracle views or objects have these columns in common. I can then from the output take a look at the definitions of those named objects that intrigue me. Listing 2 is only a partial listing and I encourage you to execute the query and see the total output. That way if you are ever interested in searching for a singe column or combination of columns you will know where within the Oracle internal views this information is stored. Do not take this output too lightly. If you remember that we were enabling statistics gathering and tracing in the previous parts to this article for combinations of these columns, you can quickly see where in this listing the information is kept and where aggregates on columns are stored. Be also warned that most of the output you will see are for Oracle’s workload repository, advisories, and snapshots mechanisms and are not of any real use unless you are using those utilities. For us I have only left the objects we will be concerned with for looking at the gathered statistics we have enabled. Table 2 gives a brief explanation of these views, how you might use them, and some things to look out for.

Listing 2

Query to investigate Oracle Views that contain our statistical information

select table_name

      ,sum(decode(column_name,'SID',1,'SESSION_ID',1,0))               SID

      ,sum(decode(column_name,'CLIENT_ID',1,'CLIENT_IDENTIFIER',1,0))  CLIENT_ID

      ,sum(decode(column_name,'SERVICE_NAME',1,0))                     SERVICE_NAME

      ,sum(decode(column_name,'ACTION',1,0))                           ACTION

      ,sum(decode(column_name,'MODULE',1,0))                           MODULE

      ,sum(decode(column_name,'SERIAL#',1,0))                          SERIAL#

from (select owner,table_name,column_name

        from dba_tab_columns

       where owner = 'SYS'

         and column_name in ('SID', 'SESSION_ID',

                             'CLIENT_ID', 'CLIENT_IDENTIFIER',

                             'SERVICE_NAME', 'ACTION', 'MODULE', 'SERIAL#'))

group by table_name

order by table_name

/

                                   CLIENT SERVICE

TABLE_NAME                     SID     ID    NAME ACTION MODULE SERIAL#

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

DBA_HIST_ACTIVE_SESS_HISTORY     1      1       0      1      1       0

V_$ACTIVE_SESSION_HISTORY        1      1       0      1      1       0

V_$CLIENT_STATS                  0      1       0      0      0       0

V_$SERVICE_STATS                 0      0       1      0      0       0

V_$SERV_MOD_ACT_STATS            0      0       1      1      1       0

Table 2

Table	Description
V_$ACTIVE_SESSION_HISTORY	· The V$ACTIVE_SESSION_HISTORY view is a holding area for sampled session data performed on your behalf from the Oracle engine. This data is sampled once per second and is a great resource for determining where the true bottleneck lies in your system. · Use this view in conjunction with setting the CLIENT_IDENTIFIER, ACTION, and MODULE to aggregate (GROUP BY) and find the largest consumers of resources within your system. · Take a look at another article I wrote entitled “True Session Wait Activity in Oracle 10g” for how to use this view. This article is at http://www.databasejournal.com/features/oracle/article.php/3388861
DBA_HIST_ACTIVE_SESS_HISTORY	· This view is just a historical representation of the V$ACTIVE_SESSION_HISTORY view above. While it is good for some historical information, be warned that it does not contain all the collected statistics that were captured in the V$ACTIVE_SESSION_HISTORY view. Please read the above mentioned article to get an understanding how this works.
V_$CLIENT_STATS	· If you have enabled any statistics for a CLIENT_IDENTIFIER you will see the aggregated statistics for that CLIENT_IDENTIFIER in this view that are currently active. · These statistics are only good for current activity and troubleshooting and thus should only be used for getting a glimpse of what clients are consuming the most resources. · Very valuable for a quick current view but if you need to drill to a particular client you will end up going to V$ACTIVE_SESSION_HISTORY.
V_$SERVICE_STATS	· Provides a reduced set of statistics that can be used to determine how well an instance is performing for the requests made upon it. · This is typically the SID_NAME but beware it may be different if you are not connecting through TNS.
V_$SERV_MOD_ACT_STATS	· This view provides an aggregated view for the combination of SERVICE_NAME, MODULE, and ACTION you have defined when you enabled aggregated statistics. Just be careful to name these aggregates appropriately so that you can quickly determine where bottlenecks reside.

The use of these views is quite straight forward. You need only query them for the statistical aggregation you have set up through the DBMS_MONITOR package. The real difficulty lies in setting up those aggregations stepped through in earlier Parts of this series. Next time we will get into the new reporting options available for traces in Oracle 10g.