All plan generator plans in Oracle
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
291k46444538
asked Nov 10 at 14:24
Oiale
25329
add a comment |
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
291k46444538
asked Nov 10 at 14:24
Oiale
25329
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 |
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
291k46444538
asked Nov 10 at 14:24
Oiale
25329
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
291k46444538
asked Nov 10 at 14:24
Oiale
25329
edited Nov 10 at 18:07
a_horse_with_no_name
291k46444538
asked Nov 10 at 14:24
Oiale
25329
edited Nov 10 at 18:07
a_horse_with_no_name
291k46444538
edited Nov 10 at 18:07
a_horse_with_no_name
291k46444538
edited Nov 10 at 18:07
a_horse_with_no_name
291k46444538
291k46444538
asked Nov 10 at 14:24
Oiale
25329
asked Nov 10 at 14:24
Oiale
25329
asked Nov 10 at 14:24
Oiale
25329
25329
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
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
7,1553831
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
7,1553831
add a comment |
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
7,1553831
add a comment |
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
7,1553831
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
7,1553831
answered Nov 13 at 21:24
Marmite Bomber
7,1553831
answered Nov 13 at 21:24
Marmite Bomber
7,1553831
answered Nov 13 at 21:24
Marmite Bomber
7,1553831
7,1553831
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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