Optimizer hint not working properly

[sandeepagarwal]

I've specified optimizer hint in the following query to use the indexes.

SELECT /*+ first_rows ordered use_nl(B C) index(CUSTORDER_IDX03) index(PK_ORDERINFO)*/
UPPER(C.CITY) PUCITY,
SUM(DECODE(B.USERID, NULL, 0, 1)) WEBCOUNT,
COUNT(*) TOTALCOUNT
FROM CUSTORDER B, ORDERINFO C
WHERE B.ROUTERECID = C.ROUTERECID
AND B.CONTROLNUM = C.CONTROLNUM
AND C.PUDELFLAG = 'P'
AND B.ORDERENTDATETIME BETWEEN TO_DATE('05/30/2002 ','MM/DD/YYYY') AND TO_DATE('06/30/2002', 'MM/DD/YYYY')
GROUP BY UPPER(C.CITY)
ORDER BY TOTALCOUNT DESC;

The PK in CUSTORDER is ROUTERECID and CONTROLNUM
This is FK in ORDERINFO referencing CUSTORDER table.
The PK in ORDERINFO is ROUTERECID, CONTROLNUM and PUDELFLAG.

The index (CUSTORDER_IDX03) is on column ORDERENTDATETIME in CUSTORDER. (Excuse me for this bad index name)

I want to use this index which Oracle is not using by itself so I've specified the optimizer hint and it works!!!

The problem is if I give any date range more than 5 days it does not use this index and go for a full CUSTORDER table scan. I've tried many permutation and combination for optimizer hints but the problem area is the date column only.

Can anybody help me where what is going wrong?
I'm working on Oracle Database 8.1.7.

Sandeep.

 

[sandeepagarwal]

Thanks Sem for this link.
Ya I'm updating my knowledge on optimizer hints.

 

[ThomVF]

OCB will not use parallelization unless 2 parallel servers are available....does your production server have PARALLEL_SERVERS_MIN=2 (or more?)

I suggest you compare init parameters to be sure. "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[uparna]

Sandeep ,
If the hint is working perfectly in the test database , adn is not being used in production (num_rows being comparable) , the problem may be of a more basic nature:
1.is the index being referred to here , created in production? Most of the times we create the indexes in test , growthge hints around them , deploy the hints in prod , blissfully forgetting to create the index in prod.
2. Instead of exporting the stats , why not analyze these objects in prod itself?
3.If your prod machine has more than 2 - 3 CPUs , why not use the parallel hints ? they are really useful : try this : PARALLEL(B,3) PARALLEL(C,3) . Also try using hashing funtions : USE_HASH(B C) . If the parallel_min_percent parameter is set to 0 , then my guess is that the performance improvement can be dramatic. Pls try this even with the "ordered" hint removed and observe the changes.
Hope this helps. Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at upparna@yahoo.com .

 

[sandeepagarwal]

Thanks Uparna for your advice. I will look into point#3 in detail. Indexes are already there and objects are also analyzed.

 

[fmorel]

Hi !

Did you try initialization parameters:
optimizer_index_caching = 50
optimizer_index_cost_adj = 10

It forces CBO to use indexes more often.

Hope this can help

 

[ThomVF]

Use the EXPLAIN PLAN table on the query.
Use UTLXPLP.sql to view the plan - it gives the most detail and shows parallel plans, if they are used.
Observe the total cost of the query.

I have a similar query and if I USE_NL on my driver table, it brings the cost down.

In your case CUSTORDER seems to be the driver.
Try this hint and compare the cost to using other suggestions.

/*+ USE_NL(B) PARALLEL(B,4) */

(If you dont have 3 or more CPUs, get rid of the parallel hgint)

Try different options and combinations until you get the cost down as far as possible.

NL will force a Nested Loop and gives better response time, versus better overall throughput.

Using the ORDERED hint helps determine the order of table in the FROM clause. Add it, shuffle the FROM order and see what the cost does.
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com