|
Category :
Performance Tuning
DB Version
:
Oracle 10G
OS Details :
Sun Solaris 10
In versions prior to
10G, executions stats at a session level can be obtained by querying
v$mystat but it is a challenge when we have to obtain execution stats
for individual process / statement executions within a session. To
obtain process / statement level execution stats, we'll have to create
break points and during those break points, populate a temporary table
or a utlfile file to get the statistics. 10G makes it a whole lot
easier with the introduction of the dbms_monitor package. For this to
work, statistics_level parameter needs to be set at TYPICAL or
ALL at the instance level.
Using this method in
10G, the following run time statistics can be obtained:
user calls
DB time
DB CPU
parse count (total)
parse time elapsed
execute count
sql execute elapsed time
opened cursors cumulative
session logical reads
physical reads
physical writes
redo size
user commits
workarea executions - optimal
workarea executions - onepass
workarea executions - multipass
session cursor cache hits
user rollbacks
db block changes
gc cr blocks received
gc cr block receive time
gc current blocks received
gc current block receive
time
cluster wait time
concurrency wait time
application wait time
user I/O wait time
To get the run time
execution statistics, v$client_stats view will have to be queried.
An Example of setting
up and getting the statistics:
-- From Session1
SQL> exec
dbms_session.set_identifier('TEST_STATS')
-- From Session1 or
Session2 or any session
SQL> exec
dbms_monitor.CLIENT_ID_STAT_ENABLE('TEST_STATS')
-- From any session
SQL> select
stat_name,value from v$client_stats;
-- After the stats have
been collected for the execution
SQL> exec
dbms_monitor.CLIENT_ID_STAT_DISABLE('TEST_STATS')
SQL> exec
dbms_session.clear_identifier
-- To find out sessions
for which stats collection have been enabled:
select sid,serial#,v$session.client_identifier
from v$session left outer join (select distinct client_identifier from
v$client_stats) clnt on
(v$session.client_identifier = clnt.client_identifier)
where v$session.client_identifier is not null
/ |
