PERFORMANCE TUNING

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 […]

SGA TARGET ADVISORY IN ORACLE

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?

SGA ADVISORY REPORT:

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 […]

WHAT IS SQL PROFILE IN ORACLE

You might have heard of SQL profile and SQL baselines in performance tuning chapters. Lets today go through details about sql profile. What is SQL profile: SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. SQL profile is […]

_use_adaptive_log_file_sync parameter in oracle

The parameter _use_adaptive_log_file_sync was introduced in 11gR2 and controls whether adaptive switching between post/wait and polling is enabled. DEFAULT VALUE of this parameter is TRUE. i.e according to oracle algorithm and internal statistics, the adaptive switching will happen between post/wait and polling method. There are 2 methods by which LGWR and foreground processes can communicate […]

Page 1 of 3123
Skip to toolbar