| Re: COST -> RULE based optimizer [message #369145] |
Fri, 24 March 2000 14:52 |
Stephan Merkli
Messages: 1
Registered: March 2000
|
Junior Member |
|
|
Hi
I'm probably to late with my contribution, but I had a similar problem recently and I want to share the knowledge I've gained from the solution - it might be of value to others.
The problem I was having showed up in a way that executing the same statements in two different databases with similar amounts of data in them and exactly the same configuration (init.ora: optimizer mode CHOOSE, but no STATISTICS arround) had differences in execution time of factor >> 10 and up. Both databases were running Oracle Workgroup Server Version 8.0.4.2. The slow database had huge temporary files, compared to the fast one.
When looking at the execution plan of the statements in the faster DB (lets call it DB1), everything looked perfect - just index scans and ROWID accesses of tables. When looking at the execuction plans of the same statements in the slow DB (lets name it DB2), there were plenty of FULL table accesses, but only one index scan. In contrary to the first execution plan, the second one showed informations about COST, CARDinality and BYTES affected by each optimizer step - but, as I mentioned, no optimizer stat's were arround (optimizer mode had been set to CHOOSE).
I tried a real lot of things to speed up DB2: setting optimizer mode to RULE in both session and init.ora, adding hints, deleting possibly hidden (???) STATISTICS information, dropping and recreating all indizes, exporting the DB with EXP STATISTICS=NONE and reimporting it with IMP ANALYZE=NO, importing the DB2 into DB1 (so DB1 got slow ...), etc. - but found no clue where the problem was.
I finally found a hint to the problem when importing the slow DB into our Oracle 8.0.5 EE development DB: when analyzing execution plans of the statements once more, the plan_table showed information about PARALLEL optimization, although that option was not installed in that DB (btw, its not possible for the workgroup version, so not installed on the other DB's as well). I then looked into the DMP-File of EXP and compared the CREATE TABLE statements of the two dumps - and found that the tables in the slow DB were created with the option "PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT)" - although I'm sure that I had not defined it like that (the tables in both databases were created with the same script).
So basically, I don't know where the problem was coming from - but after modifing all of the tables in DB2 (alter table <WHATEVER> noparallel;), all statements worked as fast as in DB1 again.
Hope this might be a help for someone!
Regards,
Stephan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
