Home » RDBMS Server » Performance Tuning » Making Oracle generate and use different execution plans for any sql (11.2.0.4)
Making Oracle generate and use different execution plans for any sql [message #645195] Sat, 28 November 2015 09:11 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,

I am trying to make Oracle come up with more than one execution plan and then see that it uses one plan some times and uses another plan some other times. Now for more than 2 days I spent hours using different simple sql queries similar to the one used in my application , but whenever I ran the queries from sqlplus , it just kept using only one plan! Whereas in the application (using jdbc) I do see Oracle coming up with multiple execution plans for the same sql id. So I am trying to define a simple sql query that Oracle will use two possible different plans - in other words to see it make a mistake! and I can't seem to be able to do it. Can someone suggest an example where this happens -same sql but different plans?

(Btw, I did succeed, just once when I saw for a brief time it used a different plan - an INDEX FAST FULL SCAN instead of an INDEX RANGE SCAN for a simple count sql that I tried,but again , next time that did not reproduce).

Thanks,
OrauserN

Re: Making Oracle generate and use different execution plans for any sql [message #645198 is a reply to message #645195] Sun, 29 November 2015 01:41 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
I think you are trying to demonstrate ACS (Adaptive Cursor Sharing). Just create a table with skewed data, and run a query using a bind variable as the predicate. For example,
create table people(c1 varchar2(10));
insert into people (select 'american' from dual connect by level < 1000000);
insert into people values('russian');
create index pi on people(c1);
exec dbms_Stats.gather_table_stats(user,'people',estimate_percent=>100,method_opt=>'for all indexed columns size skewonly')
var v1 varchar2(10)
exec :v1:='russian'
select count(*) from people where c1=:v1;
exec :v1:='american'
select count(*) from people where c1=:v1;

run the queries a few times and you'll see the different plans.
Re: Making Oracle generate and use different execution plans for any sql [message #645199 is a reply to message #645198] Sun, 29 November 2015 05:56 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks a lot John!! You are right!!! I tried with another dataset that had this kind of skew and yes the different plans came up.
Previous Topic: Execution plan cost
Next Topic: Testing SPM
Goto Forum:
  


Current Time: Tue Mar 19 05:22:50 CDT 2024