Hi,

My understanding on bind variable peeking is that oracle optimizer peeks on bind variables and creates an execution plan based on the value provided first and keeps on executing the same execution plan irrespective of the data distribution until the execution plan is in the shared pool.

I have a production db in which i am facing fluctuating performance issue.

My perception is, this causes unstable performance.

How do i keep the performance stable?

Does anyone face similar situation and what mitigating plan did you follow to resolve it?

Thanks
Murali

>> How do i keep the performance stable?

influence optimizer, have updated statistics, and potentially tune the query itself query.

can you give realtime and specific issue you are facing?

Hi Murali,

First off, have you confirmed that the values of host variables in your SQL should cause differences in execution plans?

Are you using cursor_sharing=silimar? On what release?

>> oracle optimizer peeks on bind variables and creates an execution plan based on the value provided first

Yes, that's my understanding too.

Using cursor_sharing=similar has always been dicey, and I don't use it. However, they say that it's much better in 11g with the new "adaptive cursor sharing":

http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm

Sure, This is what i am doing.

The statistics for the table is collected automatically by oracle when more than 5% of the data is changed
on the table.

Production application users are running in this db and they are having unstable performance issues.

The underlying db has bind variable peeking enabled and also have histograms on some tables.

We also pin objects and cursors which are repeatedly executed in the shared pool and periodically flush the shared pool as a scheduler job.

Whenever the sql performance goes south due to a bad plan, we manually unkeep that sql and flush the shared pool.

Is there a permanent solution to this problem?

Thanks
Murali





Hi Don Burleston,

My answers are given below.

First off, have you confirmed that the values of host variables in your SQL should cause differences in execution plans?

YES. When performance goes south, all we do is unpin the
execution plan and let oracle optimizer come with a better plan
which in my opinion is like playing a dice. This varying
sql performance affects our batch processing speed

Are you using cursor_sharing=silimar? On what release?

I am using cursor_sharing=exact.
I am on Oracle 10gR2 (10.2.0.4).

Hi,

>> I am using cursor_sharing=exact.

Do you have any problems with non-reentrant SQL (lots of SQL with executions=1)?

http://www.dba-oracle.com/t_cursor_sharing.htm

**********************************************************************
>> When performance goes south, all we do is unpin the
execution plan and let oracle optimizer come with a better plan
which in my opinion is like playing a dice.

I agree!

- How many different execution plans are there?

- One for the majority and one for the outliers?

Usually we have two, one with an FTS and another using an index . . . .

I've seen people deliberately create "plan stability" for the outlier SQL, thereby getting the best of both worlds:

http://www.remote-dba.net/pl_sql/t_optimiz...n_stability.htm

You may also be able to do this in 10g with SQL profiles:

http://www.remote-dba.net/oracle_10g_new_f...ing_advisor.htm

If we examine the evolution of Oracle SQL execution plan (explain plan) management, see see these tools:

Optimizer plan stability (a.k.a. stored outlines) - Stored outlines were cumbersome to manage, and it was very difficult to "swap" execution plans with plan stability.

10g SQL Profiles - Starting in Oracle 10g, we see the SQL Profile approach, whereby a SQL tuning Set (STS) could be tested as a workload, and Oracle would allow the DBA to implement changes to execution plans.

11g SQL Plan management - Starting in 11g we finally see an easy-to-use approach to locking-down SQL execution plans.

***************************************************************************

In sum, I would create the permutations manually and freeze the execution plans. For all of the details, there is an eBook on this subject:

http://www.rampant-books.com/ebook_vendor_tune.htm

Thank you Don Burleston in taking your time to respond to my query.

I really appreciate this.

We do have stored outlines and sql profiles in our production environments and we still have performance instability issues.

My question for this forum is
1. How many companies use data guard (physical standby), streams
replication and rac in their production environments together in
the same production database?
2. Do you guys have the same problem i am facing?

The reason for this question is, i have been in this environment for the past 1.5 years now and have been an oracle dba for the last 14 years and have worked in 5 other companies and have not seen this kind of environment / the number of problems wherever i worked.

My basic DBA approach was always keep it simple instead of being complex. Yes, Oracle has tons of features but the question is, does your organization need to have all of them? My humble approach is to always have technology to be built around the business and not the other way around.

The reason for the above statements is, with the complex setup (may be i am thinking that this is complex, may be it's normal, i don't know!!), I am noticing lot of ORA-600, ORA-7445 and repeated occurence of ORA-4031 due to child cursors (P and C) causing memory leaks makes me think that the problem is not oracle, the problem is us who have created our own destiny.

Please comment based on your experiences, i appreciate.