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... >>> Jerome.Whittle_at_scott.af.mil 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.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Mark Richard [SMTP:mrichard_at_transurban.com.au]
>

        <<< 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: jhowerton_at_uabmc.edu

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: ListGuru_at_fatcity.com (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).