Subscriber Count


Subscribe to Posts by Email


Performance Troubleshooting : DB File Sequential Read When performing Full Table Scan

Hello All,

What wait event you observe when Oracle Performs a full table scan?

DB File Scattered Read Direct Path Reads (from 11g onwards under certain conditions)

What wait event you observe when Oracle Performs a table via index scan?

DB File Sequential Read

While we are working a issue, we apparently found an issue […]

Performance Troubleshooting Series : Identifying Problematic Sessions or Queries (Method 1)

In the Previous Posts , to troubleshoot the problematic queries/long running session issues, we have sorted out two approaches, Here we discuss the Method 1

Method 1. Session Wait Event Approach:- In this approach we will try to identify from the problematic session what its waiting for , why its waiting, what can be […]

Performance Troubleshooting Series : Identifying Problematic Sessions or Queries (Preface)

In this part of the series, we will be looking into , how to identify the Problematic Sessions or Queries.

Before beginning, one should distinguish the question here. You may face questions like, and the answer for both is to identify either loaded sessions for a database.

1. Database is/was slow.

Performance Troubleshooting Series : Troubleshooting Instance Memory Structures (SGA + PGA) Usage

In the previous series we have seen how to troubleshoot IO, CPU and Database TimeSpent/Consumed,

Today’s Post I will be sharing with you the most common issues or to tackle memory related questions.

As we all know Oracle does manage things (blocks/modifications) in memory and track them in memory and sort them in memory and […]

Performance Troubleshooting Series : Understanding where the database time is spending – Time Model Statistics


This is the third post in this series, If you haven’t read the previous articles please go through here,

First Post,

Second Post,

Starting 10g, Apart from Metrics tables, there is another area of troubleshooting can be done by using Time Model series. Its very useful to understand where is the over […]

Performance Troubleshooting Series : Understanding your Oracle Database Load


Very long back, Karlarao has written a wonderful script which delivers a exhaustive report for database performance. The script extracts the data from AWR repository tables.

Where in I have took liberty to tweak the same script for those who does not have AWR licensing and having statspack only.

Here is the script […]

Why is that, I have different execution plans although sql profile has been picked up?

Today I learnt something new, on sql profiles.

Thanks to Kerry Osborne’s blog that let me understand on this stuff

Before going forward have a look at the below plans.

<font size="2">SQL&gt; select * from table(dbms_xplan.display_awr('2pg20pzpc6yb7')); PLAN_TABLE_OUTPUT- —————————————————————————————————– SQL_ID 2pg20pzpc6yb7 ——————– SQL Statement removed intentionally, and it is irrelevant to subject Plan hash value: 526584108 […]

How to find the optimizer settings for a given session or sql_id

During a query’s cost estimation the optimizer picks up many values or parameters to evaluate cost or invoke hints etc to produce an execution plan. v$sess_optimizer_env is a good view to know about any anamolies of a given statement. This can be useful when you comparing UAT and production environment where the query results are […]

What is the minimum execution time that a query/statement for a parallel execution

As per documentation its 30 seconds

The parameter is (from 11gr2) is parallel_min_time_threshold which invokes the automatic degree of parallelism when the query execution goes beyond 30 seconds.

Nevertheless, whether you have object parallelism (table/index degree) set or not, if parallel_degree_policy is set to Limited or Auto, Oracle uses parallelism for any queries depends on […]

Oracle Cursor Sharing Examples

In the previous post we discussed about the Parent & Child Cursors in Oracle and how do they work or shared.

To get into deeper, Oracle controls the sharing of cursors with parameter called cursor_sharing. It has three values


In this post we will create a table which has some records with […]