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: select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION ————————————————————————— ————————————————————————— +00000 01:00:00.0 +00008 00:00:00.0 Modify the snapshot setting:( snap_interval 30 min and retention […]

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

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

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

When a lot of DML operations happens on a table, the table will become fragmented because DML does not release free space from the table below the HWM. So despite having less number of rows, due to fragmentation, it consumes more space. So it is best practice to re-org the oracle table regularly. In this […]

WHY IS INDEX MONITORING REQUIRED? Keeping a lot of indexes on a table, can affect the performance of the transaction like insert, update, delete on the table. i.e if we are inserting a new ROW, a new entry will be added to the indexes on that table. So the best way is to remove the […]

PROBLEM: Database response was very slow (almost frozen) with database spending most of the time in wait event “log file switch (checkpoint incomplete)’ Waits event” ANALYSIS: As per Oracle docs: This event indicates that Oracle needs to reuse a redo log file, but the current checkpoint position is still in that log. In this case, […]

Introduction: Oracle in-memory concept has been introduced in oracle 12c. This feature enables tables, partitions, materialized views be stored in memory using column format, which delivers fast SQL processing for the Analytical purpose. To understand Database In-Memory feature and its benefits we first need to understand the unique “dual format” architecture that enables Oracle Database […]

                                  If you flush the shared pool, all the statements in cursor will be flushed. So if you want a sql query to do hard parsing, then you can flush the particular sql statement from shared pool. STEPS: 1. […]

                     Follow below steps for migrating sql plan baselines from one database to another. 1. Get the sql_plan and sql_handle[SOURCE] SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed from dba_sql_plan_baselines; SQL_HANDLE PLAN_NAME ENA ACC FIX ———————————- ————————————– — — — SQL_63267a8ff7127534 SQL_PLAN_669mujzvj4x9n0d025331 YES YES NO 2. Create a staging table[SOURCE] BEGIN […]