Archive for the ‘DBA Scripts’ 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 – Find View Source Code

August 21, 2011 1 comment

If you wish to find the source code for a view, you can look in the user_views table:

SQL> SELECT view_name, text FROM user_views;

You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).

SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;

Further Reading

Oracle – Retrieve instance name

August 11, 2011 7 comments

To retrieve the instance name of the database instance you are currently connected to, you can perform a number of queries:

Non-public single-node query

SQL> SELECT name FROM v$instance;

Non-public RAC node query

SQL> SELECT instance_name FROM gv$instance;

Public query

SQL> SELECT sys_context('USERENV','DB_NAME') AS instance_name FROM dual;

Oracle – MMON

MMON (Memory Monitor) is an Oracle background process that gathers memory statistics (snapshots) and stores this information in the AWR.

MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

MMON is available from Oracle 10g.

The default for recording an AWR snapshot is hourly.

Oracle – SMON

SMON (system monitor) is the Oracle background process which performs instance recovery, cleans up after unclean shutdowns and coalesces adjacent free extents into larger free extents.

SMON wakes up every 5 minutes to perform housekeeping duties, and the database instance will terminate if SMON is not running.

You can check if SMON is running from the command line in Linux/Unix:

$ ps -ef | grep pmon

Oracle – PMON

PMON is an Oracle background process created when you start the database instance. It is responsible for freeing up resources if a user process fails.

PMON normally wakes up every 3 seconds to perform housekeeping.

If PMON is not running then the database instance will terminate.

To speed up house-keeping you can wake-up PMON manually:

SQL> oradebug setmypid
SQL> oradebug wakeup 2