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!
Information Technology Development Program/Software Engineer
National Security Agency (NSA) 
Fort George G Meade, MD
To advance the development of Information Technology, NSA has created the new Information Technology Development Program ... 'Software development life cycle experience (e.g., software design, quality assurance, testing, planning,...
---------------------------
IT & Engineering Professionals w/ Poly
SRA International 
Herndon, VA
Business focuses on providing high-end information technology solutions and support to clients within the ... Architects ? Configuration Management Specialists ? Software Engineers ? Software Developers ? Geospatial...
---------------------------
IT Application Analyst
Saint Barnabas Health Care System 
Oceanport, NJ
projects. Participate in and provide leadership for development and support of automation initiatives. Research ... skills. Experience in w orking with and developing hospital information systems. Patient Accounting and/or registration...
---------------------------
IT Mgr Software Engineering
Apollo Group 
Phoenix, AZ
supervision and the software development guidelines and technology standards defined by Apollo Corporate IT, this ... Experience supervising and/or leading other professional level IT staff is required...
---------------------------
Senior Software Developer - Info Technology Job
Mercer 
Deerfield, IL
Senior Software Developer - Info Technology-DEE000C5 Description Mercer is the global leader for trusted Human Resources ... information, please visit us at: www.mmc.com/diversity. Job Information Technology/Software Development Primary Location...
---------------------------
Account Manager - IT Staff Augmentation/IT Consulting Services
TNT Staffing 
Emerson, NJ
for highly motivated, dynamic Information Technology (IT) Sales Professionals to join our growing IT Staffing ... and a passion for relationship building and business development. The right candidate will have a successful...
---------------------------
Software Development Manager 2 - Health IT
Northrop Grumman 
Providence, RI
Northrop Grumman is seeking an experienced Software Development Manager to join our team of qualified, diverse ... years of relevant experience - Experience with IT Project Development Lifecycle - Strong Medicaid/Medicare business...
---------------------------
Software Development Analyst 2 - Health IT
Northrop Grumman 
Providence, RI
Northrop Grumman Information Systems Sector is seeking a Software Development Analyst 2 to join our team of qualified, ... development life cycle (SDLC) in support of application software from design, development, testing, documentation...
View all jobs