Fandom

Scratchpad

Oracle Monitoring

215,800pages on
this wiki
Add New Page
Discuss this page0 Share

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.

Monitor Current Process

select s.username, s.status, a.first_load_time, executions, elapsed_time, a.sql_text stmt, hash_value, address
from v$session s, v$sqlarea a
where s.username is not null
           -- Ignore the Oracle
           -- background processes
 and s.audsid <> userenv('SESSIONID')
           -- Ignore the current
           -- session
 and s.sql_address = a.address
 and s.sql_hash_value = a.hash_value
order by s.status

or

select a.first_load_time, executions, elapsed_time, a.sql_text stmt, hash_value, address
from v$sqlarea a
where users_executing > 0

elapsed_time is the accumulated microseconds elapsed time used by the SQL

The two views should be granted for select (using sys as sysdba) for the account for running this checking query

grant SELECT on V_$SQLAREA to bdsuser
with grant option;
grant SELECT on V_$Session to bdsuser
with grant option;

Check Long Running Process

The following SQL order the processing or processed SQLs by running time

SELECT sql_text, first_load_time, elapsed_time, executions, hash_value, address
FROM v$sqlarea
ORDER BY elapsed_time / Decode(executions, 0, 1, executions) desc;

Check current progress of running statement

 select sid,   message
 from   v$session_longops
 order by start_time;

Monitor Execution Plan

Firstly, find out the hash_value and address from v$sqlarea of the sql

and then execute the following:

 select * from v$sql_plan where address = ? and hash_value = ?

Monitor SQL Statistics

Check Statistics Options

SELECT system_status, activation_level
FROM V$STATISTICS_LEVEL
WHERE statistics_view_name = 'V$SQL_PLAN_STATISTICS';

if system_status = 'ENABLED' and activation_level = 'ALL', then it's okay, if not, enable the statistics by (use sys account for instance-wide set):

ALTER SYSTEM SET statistics_level=ALL;

Check Execution Plan Statistics

SELECT * FROM V$SQL_PLAN_STATISTICS where address = ? and hash_value = ? 

address and hash_value are found by v$sqlarea

Also on Fandom

Random wikia