All plan generator plans in Oracle
up vote
3
down vote
favorite
While reading Oracle SQL tuning guide I came across such a quote (you can find it in this chapter):
The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.
Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.
Figure 4-2 Optimizer Components
Is it possible somehow to get all these plans that were generated by plan generator?
Maybe there are standard PL/SQL packages for this or system views?
sql oracle optimization
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
asked Nov 10 at 14:24
Oiale
22617
add a comment |
up vote
3
down vote
favorite
While reading Oracle SQL tuning guide I came across such a quote (you can find it in this chapter):
The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.
Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.
Figure 4-2 Optimizer Components
Is it possible somehow to get all these plans that were generated by plan generator?
Maybe there are standard PL/SQL packages for this or system views?
sql oracle optimization
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
asked Nov 10 at 14:24
Oiale
22617
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
– XING
Nov 10 at 14:53
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
1
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
While reading Oracle SQL tuning guide I came across such a quote (you can find it in this chapter):
The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.
Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.
Figure 4-2 Optimizer Components
Is it possible somehow to get all these plans that were generated by plan generator?
Maybe there are standard PL/SQL packages for this or system views?
sql oracle optimization
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
asked Nov 10 at 14:24
Oiale
22617
While reading Oracle SQL tuning guide I came across such a quote (you can find it in this chapter):
The plan generator explores various plans for a query block by trying out different access paths, join methods, and join orders.
Many plans are possible because of the various combinations that the database can use to produce the same result. The optimizer picks the plan with the lowest cost.
Figure 4-2 Optimizer Components
Is it possible somehow to get all these plans that were generated by plan generator?
Maybe there are standard PL/SQL packages for this or system views?
sql oracle optimization
sql oracle optimization
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
asked Nov 10 at 14:24
Oiale
22617
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
asked Nov 10 at 14:24
Oiale
22617
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
edited Nov 10 at 18:07
a_horse_with_no_name
285k45428526
285k45428526
asked Nov 10 at 14:24
Oiale
22617
asked Nov 10 at 14:24
Oiale
22617
asked Nov 10 at 14:24
Oiale
22617
22617
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
– XING
Nov 10 at 14:53
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
1
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27
add a comment |
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
– XING
Nov 10 at 14:53
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
1
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
Nov 10 at 14:53
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
Nov 10 at 14:53
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
1
1
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.
To answer this the ideal entry is the 10053 trace produced by Oracle.
There is a classical paper on this thema from Wolfgang Breitling which can be found here.
Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.
Google will for sure provide other references.
A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.
To answer this the ideal entry is the 10053 trace produced by Oracle.
There is a classical paper on this thema from Wolfgang Breitling which can be found here.
Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.
Google will for sure provide other references.
A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
add a comment |
up vote
1
down vote
accepted
Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.
To answer this the ideal entry is the 10053 trace produced by Oracle.
There is a classical paper on this thema from Wolfgang Breitling which can be found here.
Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.
Google will for sure provide other references.
A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.
To answer this the ideal entry is the 10053 trace produced by Oracle.
There is a classical paper on this thema from Wolfgang Breitling which can be found here.
Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.
Google will for sure provide other references.
A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
Typically you do not need to see all possible plans, but in some cases there is an interesting question, why does Oracle discard some plan and use an alternative one instead.
To answer this the ideal entry is the 10053 trace produced by Oracle.
There is a classical paper on this thema from Wolfgang Breitling which can be found here.
Also Jonathan Lewis in his Cost-Base Oracle Fundamentals dedicated a chapter 14 The 10053 Trace to this thema.
Google will for sure provide other references.
A word of warning. The 10053 is not the simplest stuff in Oracle context. Also in each release you may expect changes in the output. A good starting point to learn is to investigate a query that produces a different execution plan in two different environments. Simple take the 10053 trace in both databases and make a file diff and observe the difference. You will see, if there is a difference in the optimizer parameters, object or system statistics and how those changes have influence on the selection of the final execution plan.
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
answered Nov 13 at 21:24
Marmite Bomber
6,7273828
6,7273828
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53239892%2fall-plan-generator-plans-in-oracle%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
No its not possible to get the intermediate plans generated whiling execution of a SQL in Oracle.
– XING
Nov 10 at 14:53
@XING Ok, thx. Maybe post it as an answer? I'll accept your answer then.
– Oiale
Nov 10 at 14:57
1
check the 10053 trace if you want to lear internals about query optimization. You can see there some of the alternatives that were thrown away
– Marmite Bomber
Nov 10 at 15:59
@MarmiteBomber Thx for the link!
– Oiale
Nov 11 at 13:27