Execution plan in SQL

Question posted in Computer Software on 08 2010
Rate question difficulty level 0 Votes
How does the excution plan works ?
Please specify it's life cycle and expain it's plus and minus points .
 
 
3 Answers
 
Every SQL statement is examined by the Cost Based Optimizer when it submitted to run. Part of this "parsing" of a new statement involves the optimizer deciding on the best way to retrieve the desired data. All subsequent executions of that exact statement will use the execution plan derived at the first parse of the statement, until something causes the statement to be reparsed.

These include gathering fresh statistics; adding or dropping an index; the statement being aged out of the SQL area; and the instance restarting.

John Goodhue

08/05/2010
 
 
As John says, wehen the SQL parsed for the first time, the execution is dervied based on statistics information for the objects, plus bind values as part of the SQL. It will choose the the optimal method based on cost for those bind values, if the SQL gets executed with different set of values, then it may not the optimal way for those bind values, this is what is called as "bind peaking", in such case you need to manually remove the SQL from the cache either by using a DBMS package or by altering th table.

08/06/2010
 
 
Execution plan of a repeat SQL is also affected by Cursor_Sharing parameter setting (Force|Exact|Similar)

08/08/2010
 
 
Add an answer*
 
Your name
Email
 
Company: Binat
Location: Israel
DB data base sql

add a question

arrow_blue


Now hiring!
IT Sourcing Procurement Analyst Software Service Infrastructure
CVS Caremark 
Woonsocket, RI
Strategy, including: Procurement Execution ?Maintain IT procurement process in collaboration with IT stakeholders ... legal risk mitigation strategy are addressed ?Support the development of executive briefings summarizing contract...
---------------------------
Software Development Analyst/ .NET Developer- Health IT
Northrop Grumman 
Atlanta, GA
Sharp programming skills, eagerness for challenging development opportunities, and the ability to work with a ... of information security concepts and principles, as it pertains to application/service development and...
---------------------------
(IT Applications Analyst Developer Lead
Halliburton 
Houston, TX
Houston, TX DUTIES: Responsible for the development, acquisition, maintenance and support of applications in assigned ... plus 7 years of progressive experience in Systems Development and Application Support, including analysis,...
---------------------------
Mgr Software Development 2-Health IT
Northrop Grumman 
Little Rock, AR
and software developers. Works with members of the development team to ensure that all legacy data is ... years of relevant experience - Experience with IT Project Development Lifecycle - Experience converting data for...
---------------------------
IT Specialist (Applications Development)
Parsons 
Houston, TX
in software complexity. Makes a recommendation to senior IT management and the end user. Uses charts, diagrams and ... guidance to lower-level IT staff assigned to an application development project. * Develops functional specifications to...
---------------------------
Software Development Engineer (IT Security)
Amazon.com 
Seattle, WA
Role Description Amazon.com is looking for expert Software Development Engineers to develop tools supporting the IT ... for you. You will participate in every level of software development from requirements gathering to operational...
---------------------------
Software Development Engineer - Kindle with Special Offers, AmazonLocal
Amazon.com 
Seattle, WA
Software Developement Engineer - Kindle Application Development, AmazonLocal AmazonLocal is looking for exceptional, ... or related field * 2+ years professional experience in software development * Fundamentals in object-oriented...
---------------------------
Manager Software Development 2-Health IT
Northrop Grumman 
Little Rock, AR
and software developers. Works with members of the development team to ensure that all legacy data is ... years of relevant experience - Experience with IT Project Development Lifecycle - Experience converting data for...
View all jobs