Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 28, 2006

New Dictionary Enhancements with 10g – Part I

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 9:39 am

For dictionary terminalogy in Oracle please read this chapter of Oracle® Database Concepts Guide.

In this series I will give some examples on my favorite new dictionary views after 10g, I hope you like them as much as I do :)

1- OS Statistics and Finding System CPU Utilization with 10g

Operating system monitoring tools can be used to determine what processes are running on the system as a whole. If the system is too heavily loaded, check the memory, I/O, and process management areas described later in this section. Tools such as sar -u on many UNIX-based systems allow you to examine the level of CPU utilization on your entire system. CPU utilization in UNIX is described in statistics that show user time, system time, idle time, and time waiting for I/O. A CPU problem exists if idle time and time waiting for I/O are both close to zero (less than 5%) at a normal or low workload. On Windows, use the administrative performance tool to monitor CPU utilization. This utility provides statistics on processor time, user time, privileged time, interrupt time, and DPC time.

In Oracle 10g CPU and memory statistics are gathered by default. This helps Automatic Database Diagnostic Monitor(ADDM) determine how the database activity is related to the bottleneck found. Every process running on your system affects the available CPU resources. Therefore, tuning non-Oracle factors can also improve Oracle performance.
Use the V$OSSTAT or V$SYSMETRIC_HISTORY view to monitor system utilization statistics from the operating system. Useful statistics contained in V$OSSTAT and V$SYSMETRIC_HISTORY include:

– Number of CPUs
– CPU utilization
– Load
– Paging
– Physical memory

Also you may use these views to access the historical information provided by these v$ views: DBA_HIST_SYSMETRIC_HISTORY and DBA_HIST_OSSTAT

2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION

Prior to 10g it was really complicated and costly to identify the sessions who are locking another. I can not think any easier way for this need :)


SELECT blocking_session_status, blocking_session FROM v$session

BLOCKING_SE BLOCKING_SESSION
———– —————-
NOT IN WAIT
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
UNKNOWN
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
NO HOLDER
...

Also you may want to check these views for session history information on 10g: DBA_HIST_ACTIVE_SESS_HISTORY
and V$ACTIVE_SESSION_HISTORY

3- Identify Tracing Enabled Sessions

sql_trace% columns in v$session view now enables us to identify which sessions are producing trace files in the database at that time.


-- first lets check the initial conditions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;

SID    SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
---------- ---------- --------- --------------- ---------------
       184      14563 DISABLED  FALSE           FALSE
      1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

-- then lets enable sql tracing on the first session
BEGIN
dbms_monitor.session_trace_enable(session_id => 184,
serial_num => 14563,
waits      => TRUE,
binds      => FALSE);
END;
/

-- and re-check the status of the sessions
SELECT sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
FROM v$session
WHERE osuser = 'TURKCELL\TCHASYILMAZ' ;

SID    SERIAL# SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
---------- ---------- --------- --------------- ---------------
       184      14563 ENABLED   TRUE            FALSE
1047      21161 DISABLED  FALSE           FALSE
1068      11068 DISABLED  FALSE           FALSE

-- tracing can be stopped by session_trace_disable function
BEGIN
dbms_monitor.session_trace_disable(session_id => 184,
serial_num => 14563) ;
END;
/
commit ;

- These may be used to start tracing at database level
EXECUTE dbms_monitor.database_trace_enable;
EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE);
EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE);

- This may be used to start tracing at instance level
EXECUTE dbms_monitor.database_trace_enable (instance_name=>’RAC1);

All outstanding traces can be displayed in an Oracle Enterprise Manager report or with the DBA_ENABLED_TRACES or v$client_stats views. In the DBA_ENABLED_TRACES view, you can determine detailed information about how a trace was enabled, including the trace type. The trace type specifies whether the trace is enabled for client identifier, session, service, database, or a combination of service, module, and action.

First there’s the statistics (that oracle gathers all the time), which you from 10.x can “scope” in on not only session or system level but also client id, module, action and service:


select sid,username,client_identifier from v$session where username='SYS';

SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
159 SYS

exec dbms_session.set_identifier('some.id');

select sid,username,client_identifier from v$session where username='SYS';

SID USERNAME                       CLIENT_IDENT
---------- ------------------------------ ------------
159 SYS                            some.id

select * from v$client_stats;

no rows selected

exec dbms_monitor.client_id_stat_enable('some.id');

select client_identifier,stat_name,value from v$client_stats;

CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                              2
some.id      DB time                               314
some.id      DB CPU                                314
some.id      parse count (total)                     1
some.id      parse time elapsed                     67
some.id      execute count                           2
some.id      sql execute elapsed time              115
some.id      opened cursors cumulative               1
...
27 rows selected.

select sysdate from dual;

select client_identifier,stat_name,value from v$client_stats;

CLIENT_IDENT STAT_NAME                           VALUE
------------ ------------------------------ ----------
some.id      user calls                             11
some.id      DB time                              1582
some.id      DB CPU                               1582
some.id      parse count (total)                     3
some.id      parse time elapsed                    226
some.id      execute count                           9
some.id      sql execute elapsed time              901
some.id      opened cursors cumulative               3
...
27 rows selected.

exec dbms_monitor.client_id_stat_disable('some.id');

select * from v$client_stats;

no rows selected

Another tool we have that’s now also “scopable” (by client id, etc.) is Sql trace. For example, you can trace across sessions without tracing the entire server. Using the same id as in above example:


select * from dba_enabled_traces;

no rows selected

exec dbms_monitor.client_id_trace_enable(client_id => 'some.id',waits => true);

select * from dba_enabled_traces;

TRACE_TYPE
---------------------
PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1
------------------------------------------------
QUALIFIER_ID2                    WAITS BINDS INSTANCE_NAME
-------------------------------- ----- ----- ----------------
CLIENT_ID
some.id

TRUE  FALSE

exec dbms_monitor.client_id_trace_disable('some.id');

select * from dba_enabled_traces;

no rows selected

Continue reading with Part 2

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

References Used : Oracle® Database Reference 10g Release 2 (10.2)
Oracle® Database New Features Guide 10g Release 2 (10.2)
Oracle ACE Fredrik Adolfsson answer on http://forums.oracle.com

3 Comments »

  1. […] In Part 1 I mentioned three dictionary enhancements; […]

    Pingback by New Dictionary Enhancements with 10g - Part 2 « H.Tonguç YILMAZ Oracle Blog — December 29, 2006 @ 9:48 am | Reply

  2. […] – Oracle New Dictionary Enhancements with 10g Series Like V$OSSTAT, V$SYSMETRIC_HISTORY, V$ACTIVE_SESSION_HISTORY etc. […]

    Pingback by H.Tonguç YILMAZ Blog » Last warning, I moved my blog - Son uyarı, taşındım — December 30, 2006 @ 10:49 am | Reply

  3. […] with 10g – Part 3 Filed under: Oracle 10g New Feature — H.Tonguç Yılmaz @ 2:28 pm In Part 1 I mentioned three dictionary […]

    Pingback by New Dictionary Enhancements with 10g - Part 3 « H.Tonguç YILMAZ Oracle Blog — January 4, 2007 @ 2:35 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: