Home » SQL & PL/SQL » SQL & PL/SQL » How many execution plans Oracle generates for a query?
How many execution plans Oracle generates for a query? [message #248155] Thu, 28 June 2007 03:23 Go to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Oracle generates an optimized execution plan for a query based on the following:
1. Statistics (table, index, cluster, histogram) gathered
2. Optimizer mode, hints used

Are there any other factors like above-mentioned that affect execution plan? How many execution plans Oracle generates for a query before choosing the most optimized plan?
Re: How many execution plans Oracle generates for a query? [message #248158 is a reply to message #248155] Thu, 28 June 2007 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

  • nls parameters
  • variables type and length
  • current workload (memory)
  • many optimizer parameters
  • ...

Regards
Michel

Re: How many execution plans Oracle generates for a query? [message #248161 is a reply to message #248158] Thu, 28 June 2007 03:36 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
Yah... these r the other factors used to generate execution plan.
But how many execution plans Oracle generates for a query? Somewhere I saw in Oracle book that Oracle(8i) generates 20 plan b4 choosing optimized plan. Is it correct?
Re: How many execution plans Oracle generates for a query? [message #248162 is a reply to message #248161] Thu, 28 June 2007 03:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
fastfreeeasy wrote on Thu, 28 June 2007 10:36
generates 20 plan b4
Question I stopped reading there. It must be some kind of secret code...

MHE
Re: How many execution plans Oracle generates for a query? [message #248164 is a reply to message #248162] Thu, 28 June 2007 03:45 Go to previous messageGo to next message
fastfreeeasy
Messages: 25
Registered: June 2007
Junior Member
b4 imply before
Re: How many execution plans Oracle generates for a query? [message #248174 is a reply to message #248161] Thu, 28 June 2007 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many plans necessary to find the most optimized one for the current environment limited by the current parameters.

Or if you prefer: 42.

Regards
Michel

[Updated on: Thu, 28 June 2007 04:03]

Report message to a moderator

Re: How many execution plans Oracle generates for a query? [message #248175 is a reply to message #248164] Thu, 28 June 2007 04:03 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
DON'T IMPLY. Use proper English NOT IM speak.
1. Not everyone understands it.
2. It's annoying

[Updated on: Thu, 28 June 2007 04:03]

Report message to a moderator

Re: How many execution plans Oracle generates for a query? [message #249902 is a reply to message #248174] Fri, 06 July 2007 08:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I believe that the maximum number of different plans that the optimizer will consider is determinted by the init.ora parameter OPTIMIZER_MAX_PERMUTATIONS.
Re: How many execution plans Oracle generates for a query? [message #250100 is a reply to message #249902] Sat, 07 July 2007 20:13 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OPTIMIZER_MAX_PERMUTATIONS is obsolete in 10g.

Ross Leishman
Previous Topic: How to transfer 500,000 records from 1 db to another
Next Topic: Data between two dates in a 12-month period
Goto Forum:
  


Current Time: Sun Dec 04 20:27:52 CST 2016

Total time taken to generate the page: 0.07341 seconds