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
Mark,
What were the elapsed times for each run? You
show the AUTOTRACE, but did you have SET TIMING ON?
Some notes:
The CBO plan isn't so horrendous (although the
elapsed times would be useful to either validate or negate that
statement). The total logical reads were about 66,000
When were statistics last generated?
Any significant DML since then?
Jared
Stahlke, Mark [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/07/2002 02:51 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:
:[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