How to find fragmentation in oracle table

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

Monitor Index usage to find unused index

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

log file switch (checkpoint incomplete) Waits event

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

IN-MEMORY in oracle 12c

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

How to flush a sql statement from shared pool

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

Migrating sql baselines from one database to another

                     Follow below steps for migrating sql plan baselines from one database to another. 1. Get the sql_plan and sql_handle[SOURCE]

2. Create a staging table[SOURCE]

3. Pack the sql_plan to the staging table[SOURCE]

4. Take export of staging table[SOURCE]

5. Copy the […]

Tracing sessions in oracle

           There are multiple methods for enabling tracing for sessions in oracle. 1. Enabling tracing for all session of a user.              For this we need to create a trigger.

2. Enabling trace for a single session(using dbms_system)

3.  Enabling trace using oradebug.


Migrate sql profiles from one database to another

 Follow below steps for migrating sql profiles from one database to another. 1. Create a staging table

1. Pack the profiles to the staging table 

3. Export the staging table:

4. copy the dump to target db and import the table.

5. Unpack the sql profiles

How to run SQL tuning advisor for a sql_id

When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be done that query to improve performance. It might give suggestion to create few indexes or accepting a SQL profile. Diagnostic and Tuning license is required to use this feature In this below tutorial we will explain how to run […]

How to export and import statistics in oracle

If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful. Here i will take export of statistics of a table RAJ.TEST from PROD and import into […]

Page 2 of 3123
Skip to toolbar