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.

 

[carp]

It sounds like the CBO is looking at the date range and deciding it is more efficient to use a full-table scan.

 

[sandeepagarwal]

But both the tables have records in millions and a date range of 10 days only brings up 20 or 30 hundred records. I think CBO go for full table scan if it find records more than 5%. Correct??

Sandeep.

 

[sem]

Try to use RULE hint.

 

[sandeepagarwal]

I tried that too but it didn't work....

 

[ThomVF]

CBO in 817 goes to the full table scan in my case as well.

I have a Product/Account table with 52Million rows and have a cursor that looks for the Accounts for a specific product. There might be 10,000 accounts, there might be 1,000,000 so ORACLE uses the Full Table Scan.

I was surprised and wanted to see how RULE (indexes) performed - it was slower than the Full Table Scan.

If you are seeing poor performance, consider the following when using CBO:

1. Range-Partition your table, perhaps on Year or Quarter (ORDERENTDATETIME)

2. Analyze the table, updating statistics

3. Parallelize the query with a hint, or set the table parallel value - remember that PQO works on Full Table Scans or Partition Index Range Scans

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sandeepagarwal]

Thanks...
For some reasons I can't make partitions now.
I've tried with analyzing tables and updating statistics but that didn't work. Any other idea??

 

[rastorin]

HI !!!

Try this:

CREATE BITMAP INDEX index-name ON table (....) COMPUTE STATISTICS PARALLEL;

Renato,

 

[ThomVF]

Interesting information about Indexes and Full TableScans
from Gaja Krishna Vaidyanatha, Quest Software Inc.

http://www.quest-pipelines.com/newsletter-v3/0302_F.htm

The Index Scan Myth

Index scans are always the preferred method of executing a SQL statement, especially when retrieving less than 15% of the total number of rows in the table, as it will perform much less I/O than a full table scan, and thus execute better.

Fact

Time and time again, real-life applications have proven that a SQL statement loses the efficiency of using an index when the number of blocks that are visited by the SQL statement equals or exceeds the number of blocks to perform a full table scan [Vaidyanatha, 9]. A couple of exceptions to the above rule are columns in tables that contain redundant and low-cardinality data and are supported by bitmapped indexes or fast-full index scans.

The point I am trying to make here is that the overhead associated with reading the root, intermediate, and leaf blocks of an index, plus the data block reads for each row returned by the query, should not outweigh the cost of a full table scan. If an index scan performs more I/O than a full table scan, it will actually hurt performance, rather than help. Ironically, if the rows that are being retrieved are splattered across numerous blocks of the table and are not clustered or located next to each other, the use of an index in the query's execution plan will result in the query not performing at optimal levels (even if the number of rows processed or retrieved is less than the mythical level of 15 percent). This excess I/O generation will strain the I/O sub-system. In this case, a full table scan is a much more viable option and should be preferred. If the table in question is of significant size (definition of significant is left to you), you should even consider a parallel full scan on the table.

"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sandeepagarwal]

Ok.... just now tried this... sorry its not working... its worse than what it was earlier. Now the optimizer is using index for date range of only 2 days and goes for full table scan otherwise. I need some solution as I've to write this query for MIS reports and nobody is going to sit and wait for the report if it takes half an hour.

So anybody got any other idea??

 

[ThomVF]

There are a ton of other issues to consider besides the execution plan when tuning a query.

How many DB_BLOCK_BUFFERS do you have and what is the cache hit ratio ?
What is the DB_FILE_MULTIBLOCK_READ_COUNT set to?
What is the DB_BLOCK_SIZE?
How many CPUS are there ? What speed?
Are you contending for I/O, CPU, etc, etc.
Is your system paging excessively or swapping?

There is no simple answer.

My best advice: If you are using CBO, make sure you have statistics analyzed and try paralellizing the query, if you have more than 1 CPU.


"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sandeepagarwal]

Thanks for your advice. I'll look into it and see what best can be done.

Thanks everybody for your valuable inputs.

Sandeep.

 

[vgg]

Hi sandeep,
Just try using the INDEX_FFS optimizer hint.

regards,
vgg

 

[sandeepagarwal]

Tried... no improvement still.....

 

[sem]

Try to leave only INDEX hints and add aliases to them:
/*+ ordered index(B CUSTORDER_IDX03) index(c PK_ORDERINFO)*/

To force index using on appropriate table you should specify a table alias and (optionally) index name. Without index name optimizer will choose from all indexes of that table.

You specified only index name so your hint was ignored as incorrect.

 

[carp]

Oracle may be trying to send YOU a hint! If you aren't using a bitmap index and you are dealing with millions of rows, the index stands a good chance of hurting your performance. If you really think you must use an index, you may want to build histograms on the column. This may influence Oracle to see things your way.

As previously noted several times, the overhead of using the index may actually slow you down when compared to a full table scan. Couple this with the overhead of storing and maintaining the index, and you may begin to think about dropping the index!

 

[sandeepagarwal]

Ok... I'll try that too.
But I've observed one thing. My query is running fine in the test database which have almost same number of records in these tables and exact replica of the production (created using the cold backup of production)
That means Oracle is accepting those hints in test database but not in production. This is bit amazing!!!

I'm using Explain Plan utility for all my observations regarding execution plan.

Sandeep.

 

[sem]

Did you try to add aliases to hints?

It seems to me that your hints are incorrect and are not used at all.
Your test server has probably no statistics gathered or uses RBO.

 

[sandeepagarwal]

Thanks Sem for your inputs. It seems using alias names in indexes indeed forcing the optimizer to use indexes.
But this requires testing on daily basis as I'm not sure about the execution path taken by optimizer at different points of time.

As the table size is quite big, the response time is still slow but comparatively better than when it goes for full table scan.

And yes Sem, the stats are there for test database and it is using CBO. In fact today we have exported the stats from production and imported in test database to see if the execution plans differs due to that.

Thanks everybody for your time.
Sandeep.

 

[sem]

Don't worry about the future, just read about writing hints more carefully

The table name (alias) is mandatory for INDEX hint:

http://otn.oracle.com/docs/products...oc/server.901/a90125/sql_elements7a.htm#51066