Stahlke, Mark wrote:
Greetings,
One of our developers came to me with a fairly simple query that runs much
faster when she uses the RBO. I looked at the execution plans generated by
both the RBO and CBO and the CBO's plan is horrible. I was able to get a
reasonable plan from the CBO
tats.
Thanks!
-Tim
- Original Message -
From: "Stahlke, Mark" [EMAIL PROTECTED]
To: "Multiple recipients of list ORACLE-L"
[EMAIL PROTECTED]
Sent: Friday, June 07, 2002 3:51 PM
Subject: Horrendous Execution Plan from
CBO
Greetings, One of our developers came to me
Greetings,
One of our developers came to me with a fairly simple query that runs much
faster when she uses the RBO. I looked at the execution plans generated by
both the RBO and CBO and the CBO's plan is horrible. I was able to get a
reasonable plan from the CBO using a USE_NL hint.
Do any of
:Horrendous Execution Plan from CBO
Greetings,
One of our developers came to me with a fairly simple query that runs much
faster when she uses the RBO. I looked at the execution plans generated by
both the RBO and CBO and the CBO's plan is horrible. I was able to get a
reasonable plan from
:[EMAIL PROTECTED]]
Sent: Friday, June 07, 2002 3:29 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject:Re: Horrendous Execution Plan from CBO
When were statistics last generated?
Any significant DML since then?
Jared
Mark,
Do any of you SQL tuning gurus have any suggestions? I've
listed all the
gory details below.
I am not a SQL tuning guru, but it looks like this is an ideal example where
Hash joins would be of immense help. You could set a largish value for
HASH_AREA_SIZE (defaults to twice
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Horrendous Execution Plan from CBO
Thanks for the quick responses.
I analyzed both tables immediately before I started testing.
The USE_MERGE hint gives me
ORACLE-L
Subject:RE: Horrendous Execution Plan from CBO
Mark,
Do any of you SQL tuning gurus have any suggestions? I've
listed all the
gory details below.
I am not a SQL tuning guru, but it looks like this is an ideal
example where
7.3.4? You're brave...
Try to analyze with the following syntax:
ANALYZE TABLE tname COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
There was a funny with CBO that it ignored index stats completely
even in some very simple joins. With 7.3.4. Usually this fixed
the problem. Can't remember which
