Oracle Session Tracing Part IV

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 were 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 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, 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 look at where within Oracle’s internal views these columns might
be defined since this could help us in future needs of our 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 look at what internal Oracle views or objects have
these columns in common. From the output, I can then 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 single 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. Also, be 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.

DBA_HIST_ACTIVE_SESS_HISTORY

  • This view is
    just an 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 of the collected statistics that were captured in the
    V$ACTIVE_SESSION_HISTORY view. Please read the above-mentioned article to get
    an understanding of 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 that are currently active in
    this view.

  • 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 be aware 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 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 straightforward. 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.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles