SQL Performance (MOSC)

MOSC Banner

Need help to create outline using first_rows(100) hint without changing the plan

edited Oct 16, 2016 5:00AM in SQL Performance (MOSC) 1 commentAnswered

oracle db version: 10.2.0.3.0

optimizer mode: ALL_ROWS

Some observations on my query select a1.col1, a1.col2... from a1,...a39 where <40 conditions> which is expected to run within few seconds (10-15 seconds at most):

1. without any hint costs 1,330,000+ and doesn't fetch anything within 60 seconds.

2. with /*+FIRST_ROWS(100)*/ hint costs only 5,600+ and fetch result within 15 seconds.

I tried to create outline on the query no. 2 (hinted query) and have the following observations:

1. before creating outline, query costs 5,600+ and fetch results within 15 seconds.

2. after creating outline, cost of the same query increased to 1,350,000+ and again fetched nothing

3. then dropped the outline and found the query cost reduced to 5,600+ and fetching result

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center