| RBO access by index, CBO does full scan [message #53264] |
Tue, 10 September 2002 03:01  |
Oskar
Messages: 26
Registered: May 2002
|
Junior Member |
|
|
Hi,
We've been doing lately some tests to switch the optimizer mode to our database (8.1.5) and start working with CBO instead RBO.
Performance decreased a lot and looking at the difference in execution plans I noticed that access to some tables has changed from index to full scan.
I know that it can be better sometimes do a full scan that an access by index. This is NOT the case. Same query lasts ten times more with CBO than with RBO.
Statistics have been calculated using ANALYZE command with clauses "compute statistics FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES" for every table in the schema. No other schema tables are involved in these querys.
Could it be I missed the clause "FOR ALL INDEXED COLUMNS" at the time of calculating statistics? Will this be the reason?
Should I use dbms_stats? Which parameters should I use then?
I hope someone can help me with this.
Thx in advance.
|
|
|
|
| Re: RBO access by index, CBO does full scan [message #53271 is a reply to message #53264] |
Tue, 10 September 2002 09:27  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
I use
analyze table xx compute statistics;
and I did read somewhere that the corresponding index is also analyzed with this command. Works for me.
Another thing, in the init.ora file, how is the optimizer set ?
optimizer_mode = all_rows or first_row ?
How often do you analyze the tables ? You need to analyze following a big change/load of data. Or as I do, every Saturday my script runs to analyze all the tables in the schema.
Good luck.
|
|
|
|
| Re: RBO access by index, CBO does full scan [message #53278 is a reply to message #53264] |
Tue, 10 September 2002 14:56 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
No, I don't think it would help to include "FOR ALL INDEXED COLUMNS" - that does less analyzing - not more.
-- for most comprehensive stats (including histograms on un-indexed columns)
analyze table compute stats;
-- Slightly less comprehensive stats (no histograms on un-indexed columns)
analyze table t compute statistics
for table
for all indexes
for all indexed columns;
If you can re-qrite the query, you could try that or maybe just by changing the indexes on those tables (unless you think they were added using a lot of thought). Hinting the query would also probably fix it (but I guess you don't want to do that). If it's OLTP, see if changing to FIRST_ROWS helps.
You could use dbms_stats to gather the stats too. If you use that, I think you loose either the chained row count or avg row lenght data in user/dba/all_tables.
begin
dbms_stats.GATHER_SCHEMA_STATS ('SCOTT');
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
