Guidelines for avoid Bind Variable Peeking behavior
From: Roberto Veiga <roberto_veiga_at_yahoo.com>
Date: Sun, 31 May 2009 07:14:16 -0700 (PDT)
Message-ID: <892309.96169.qm_at_web35603.mail.mud.yahoo.com>
I am facing a lot of problems with bind variables because the access plan is changing and a critical performance problem occurs.(I have already posted a question "Histogram worthwhile" here at Oracle-l). I am suffering the "bind variable peeking" behavior.
My customer is very disapointed about this "feature" of Oracle and he is asking me what to do to avoid this behavior.
We have already tried to remove histograms and the situation become worse.
I have proposed him to focus on the most critical process and put some hints to freeze the plan.
But the processes work with a lot of procedures with a lot of statements. So I need to define a method to focus only in the queries with most change to have this problem.
I can use Stored Outlines (Plan Stability) but I have a *lot of* queries and I need a fast way to configure those queries.
I am thinking use Sql Profiles but I need a fast way to use this with a lot of queries and I really dont know if this approach is going to resolve the changing plan problem.
I am thinking using this points to focus only in a few potential problem queries:
Is this a good approach to identify queries that can have the plan changed?
We are having a great pressure to minimize this situation.
Date: Sun, 31 May 2009 07:14:16 -0700 (PDT)
Message-ID: <892309.96169.qm_at_web35603.mail.mud.yahoo.com>
I am facing a lot of problems with bind variables because the access plan is changing and a critical performance problem occurs.(I have already posted a question "Histogram worthwhile" here at Oracle-l). I am suffering the "bind variable peeking" behavior.
My customer is very disapointed about this "feature" of Oracle and he is asking me what to do to avoid this behavior.
We have already tried to remove histograms and the situation become worse.
I have proposed him to focus on the most critical process and put some hints to freeze the plan.
But the processes work with a lot of procedures with a lot of statements. So I need to define a method to focus only in the queries with most change to have this problem.
I can use Stored Outlines (Plan Stability) but I have a *lot of* queries and I need a fast way to configure those queries.
I am thinking use Sql Profiles but I need a fast way to use this with a lot of queries and I really dont know if this approach is going to resolve the changing plan problem.
I am thinking using this points to focus only in a few potential problem queries:
-Statements with Bind Variables -Columns with Skewed distribution -Statements with more than 5 tables join -Tables with many rows (500k or more) -Access plan using Hash Join
Is this a good approach to identify queries that can have the plan changed?
We are having a great pressure to minimize this situation.
Thanks in advance,
Roberto Veiga
Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 31 2009 - 09:14:16 CDT