Oracle Database Blog: Experiments & Learnings

May 4, 2011

Yet another case of Oracle Database performance

Filed under: Oracle RDBMS — Tags: , , , — Saurabh Manroy @ 10:23 am

For best results, an application working in tandem with Oracle Database should use bind variables in its queries. Query using bind variables can share execution plan with all the subsequent runs of the same query, saving lots of CPU time.

If a query is using literals, every time the same query is run: oracle would check syntax, semantics, create parse tree and eventually create an execution plan for every run. This is also known as hard parse. This is a CPU intensive operation.

Having said that, using bind variables may not always work in favor. Infamous problem of bind peeking is known to produce suboptimal execution plans.

Problem

Environment Details:

2 node RAC

RDBMS Version : 10.2.0.4

Clusterware: 11.1.0.7

Listener: 11.1.0.7

ASM : 11.1.0.7

cursor_sharing=exact

Queries were using bind variables.

There was a process that was running 24*7 in the database. This daemon used to select from one table and update another table based on values of previous select statement. During weekdays, the process would randomly start running slowly (select and update query) and the backlogs at the application end used to pile up. This was severely impacting the performance of whole system.

The problematic process mentioned above always ran on Node 1 (preferred instance).

The cause of cursor invalidation that led to change in execution plan during the day was unknown.

Workaround

Shared pool flush used to resolve the problem by changing the execution plan.

Analysis

a.) Problem was with the Select and Update statement in the module. The select statement used to select few records from a table depending upon the condition specified in the Where clause. Then application would pickup those records one at a time and update another table.

b.) Statastics on the tables involved were gathered every night. In the stats gather procedure, CURSOR_NOINVALIDATE was set to auto. In other words, it was left to Oracle to decide whether to invalidate dependent cursors or not. So, after stats gather plan may or maynot change.

c.) Histograms were present on columns which had skewed data distribution.

d.) When the execution plan was bad:

Select Statement would start using Full table scans instead of Index scans.

Update statement would start using a composite index rather than a primary key index.It was going for index range scan and was resulting in abnormally high buffer gets and disk reads (for updating just one row).

e.) ‘dbms_xplan.display_awr’ was used to capture the plans for the sql_id involved. Output reported multiple plans of same query.

f.) @?/rdbms/admin/awrsqrpt.sql was run to check for plans and statistics when performance was bad. We saw lots of buffer gets and physical disk reads.

Solution
As expected, on an unfortunate day, the workaround of shared pool flush didn’t work. In other words, the plan remained the same (bad) even after shared pool flush. Database restart was not an option because it was a production database.

With these details in place, we thought of following solutions:

a.) Hint the queries to use primary key index which would result in a better plan. This would involve change to application which was not acceptable to application team.

b.) Use stored outlines to fix the plan.

c.) Identify what was leading to plan change during the day time (mentioned in problem description).

While an Oracle SR was in place, we couldn’t get any reply. We planned to load test the QA environment and run the daemon. Fortunately, on QA environment, the query picked up better plan. Stored outline was created immediately and an export was taken, followed by import on production database. Backlogs got cleared in a span of just 5 minutes. Environment is stable now for two months.

Advertisements

1 Comment »

  1. […] Uncategorized ← Yet another case of Oracle Database performance […]

    Pingback by Stored Outlines Weird Behaviour « Oracle Database Blog: Experiments & Learnings — July 22, 2011 @ 7:42 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: