Re: Oracle position on hints 9.2.0.X
Something l would try in that position is create stored outline on ... your select statement in 8.1.6 Then select from user_outline_hints to find what hints Oracle had generated in the fast plan. Then write the same set of hints into the SQL in 9.2 (or even export/import the hints) - sometimes it helps, sometimes it just satisfies the curiosity. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th UK___April 8th UK___April 22nd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 11 March 2003 14:08 After spending 2 ½ days trying hints, init parameters, re-writing the query, a completely useless TAR, etc. to get a query that runs in 1 second on 8.1.6.X to go faster than 1 minute on 9.2.0.2 I found a new to 9.2.0.X dynamic init parameter optimizer_dynamic_sampling, if I understand it correctly this parameter forces the optimizer to try harder to get an efficient execution plan. Check the FM there are some interesting things that each level causes the optimizer to do. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle position on hints 9.2.0.X
After spending 2 ½ days trying hints, init parameters, re-writing the query, a completely useless TAR, etc. to get a query that runs in 1 second on 8.1.6.X to go faster than 1 minute on 9.2.0.2 I found a new to 9.2.0.X dynamic init parameter optimizer_dynamic_sampling, if I understand it correctly this parameter forces the optimizer to try harder to get an efficient execution plan. Check the FM there are some interesting things that each level causes the optimizer to do. The default is optimizer_dynamic_sampling=1 I've tried optimizer_dynamic_sampling = 5 7. The query in question has several joins across database links. In 8.1.6 the 10046 trace shows 68 I/O's to the remote database in 9.2.0.2 with optimizer_dynamic_sampling =1 10046 shows 1.4 million I/O's to the remote database. With optimizer_dynamic_sampling = 5 the I/O's are back to 68. Check this parameter it saved us from re-writing a bunch of sql... ...JIM... [EMAIL PROTECTED] 3/11/03 7:33:56 AM Hi, We recently upgraded from 7.3.4 to 8.7 (management plans on getting to that Y2K problem shortly ;-) We had an SQL statement that really needed a hint in 7.3.4. After upgrading to 8.7, I removed the hint and it runs much faster without it. I spend some of my spare time testing SQL with hints removed now. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Mark Richard [SMTP:[EMAIL PROTECTED] snip 1) You are limiting functionality when the database is upgraded - I have seen several examples where Oracle went from 7 to 8 and noone looks at every SQL statement to reevaluate the validity of every hint. snip -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: James Howerton INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
