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
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 