Archive for the ‘Performance’ Category

Oracle – Find consuming sql from process id

September 5, 2011 5 comments

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.


Oracle – Disable dbms gather_stats_job or optimizer stats

To disable gather_stats_job in 10g:


To disable gather_stats_job (also known as optimizer stats) in 11g:

client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);

Oracle – RMAN 11g List Failure

The 11g version of RMAN has a useful feature for diagnosing and repairing any failures.

Find any database failures:

RMAN> list failure;

Determine manual or automatic repair:

RMAN> advise failure;

From the output of the advise, try to manually fix the failure. If this fails to work, then try the repair command:

RMAN> repair failure;

Further Reading

Oracle┬« Database Backup and Recovery User’s Guide

Oracle – Check Auditing Policy

To check what auditing has been set on your Oracle database you can query the following:

FROM dba_stmt_audit_opts;

Further Reading

Auditing in Oracle 10g Release 2

Oracle – Blocking Session

Show blocking sessions

Blocking sessions occur when an insert, update, delete is being issued and a commit has not been performed, this locks the row and prevents other users from making any changes to it.

SELECT blocking_session
, username
, sid
, serial#
, wait_class
, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

From here we can see which user(s) are blocking the sessions and go and find out why.

Oracle – Check Users Connected to Database

July 12, 2011 1 comment

To find how many users are on the database issue the following:

SQL> SELECT username
FROM v$session;

Show what users are running

, a.serial#
, a.username
, b.sql_text
FROM v$session a
, v$sqlarea b
WHERE a.sql_address=b.address;

You can add the following to the script if you have a lot of users and want to find one specific users code, but you will need to remove a.username from the above script:

AND a.username = '<username>';