How to generate AWR report in RAC

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql awrrpt.sql – > This will generate the one report for the database across all the nodes(i.e for all instances) for a partiular snapshot range. awrrpti.sql – > This will genereate report for a particular instance, i.e for a 2 node RAC […]

Useful ADRCI commands in oracle

ADRCI is the command line interface for diagnostic utility used for viewing diagnostics data like listener log , alert log ,incident and cor dump etc and creating incident packages. Below are the the list of useful commands. 1. Get current  base location:( Also known as ADR_BASE)


3. List current ORACLE_HOME […]

How to drop SQL baselines in oracle

Below is the steps for dropping a sql baseline from the database.   1.Get the sql_handle and sql_baseline name of the sql_id:

2. Drop the baseline:

An sql_handle can have multiple sql baselines tagged, So if you want to drop all the sql baselines of that handle, then drop the sql handle itself. […]

How to generate ADDM report

Automatic Database Diagnostic Monitor(ADDM) can analyze performance issues during a particular period and provide suggestion. An ADDM analysis is performed on a set of awr snapshots. addmrpt.sql script is used to generate addm report. Generate ADDM report: cd $ORACLE_HOME/rdbms/admin SQL> @addmrpt.sql It will ask for begin snap_id and end snap_id

Privileges required for non-sys users […]


The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for  SGA_TARGET. MMON background process gather statistics about sga_target usage and update the V$SGA_TARGET_ADVICE view. Check sga target advisory is enable?


Now if we see the estimated stats for size factor 1.5(i.e with sga_size 153600M), then there […]

Shared Pool Advisory in oracle

Shared pool stores and caches the SQL and PL/SQL queries hitting the database , which avoids hard parsing the repetitive SQLs and It leads to increasing performance and memory usage. shared_pool_size is the parameter which controls the size of shared_pool.But Most of the time DBAs wonder how much value should be the shared_pool_size. In that […]

How to get the execution plan for a SQL between two AWR snapshots

We can get the all the execution plan details of an sql_id for a particular awr snapshot. EXAMPLE: sql_id – > 5vqy6cj4jr89k Run the below script:

We need to pass below inputs while running the script. REPORT_TYPE – NUM_DAYS – BEGIN_SNAP – END_SNAP – SQL_ID –

What is colored sql_id in dba_hist_colored_sql

By default, only top sql_ids are captured in database snapshots. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities  (so that it does not have to be a TOP SQL). Mark an sql_id as colored

colored sql_ids will be populated in dba_hist_colored_sql

NOTE […]

How to modify awr snapshot interval setting

We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function. The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days . DEFAULT SETTING:

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30) The values for both ‘interval’ and ‘retention’ are expressed in […]

How to create awr snapshot manually

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min . But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval. Lets say, we want to generate a report […]

Page 1 of 3123
Skip to toolbar