Performance Tuning

Articles related to performance tuning

“library cache lock” waits from many concurrent DBMS_JOB.submit() operations

I have faced performance issue on one of production database in banking environment. From AWR report I found that “library cache lock” as the top wait event. Database has spent 68.95% of database time waiting on this wait event. By

Oracle Query Tuning – Example 3

In this query tuning example we will see how to index table columns null value to improve the performance of query. Many developers writes queries which has filter condition as NULL i.e. they are quering the tables with something IS

APPROX_COUNT_DISTINCT Function – Oracle 12c

The need to count distinct values of column is common operation in data analysis. It helps to decide whether an index will be useful or not. Prior to 12c database, we need to execute a query on the table to

Oracle Query Tuning – Example 2

Day by day technology is upgrading and every organization tries to make easy way of communication for their customers. In our day-today life when we make any payment, Recharge Mobiles etc, we get notification on mobile phone via SMS. These

OPT_PARAM Hint in oracle

The OPT_PARAM hint in oracle lets you set an initialization parameter for the duration of the current query only. As per oracle documentation following parameters supports with OPT_PARAM hint. OPTIMIZER_DYNAMIC_SAMPLING OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_SECURE_VIEW_MERGING STAR_TRANSFORMATION_ENABLED

Virtual Index in Oracle Database

Virtual index is definition of index which is physically not created that is segment is not created for such index. The main purpose of virtual index is to determine whether the created index would be useful or not for the

Calculating Execution Time of Query in Oracle

In this post I am going to show how to calculate execution time of Query in Oracle Database. When user submits the query oracle parses the query and compute Hash Value for the statement. In V$SQL view we are having

How to identify Parallel DML is enabled for DML statement

In this post, I am going to show you how to identify Parallel DML is enabled or disabled for DML statement. For this I am going to compare the executions plans with parallel DML enabled and parallel DML Disabled. We will

Page 1 of 3123