Help - Search - Members - Calendar
Full Version: index optimization and cost
Oracle DBA Forums > Oracle > Oracle Forum
TJG
We have a query in our product that runs very slowly at a customer site. The query is over XML data (using contains/HAS_PATH) and relies on a CTXSYS.CONTEXT index. In comparing the plan for the query locally with one at the customer site over a similar number of records on 11g the cost difference seems to come down to the use of the CONTEXT index:

From Local Plan:
TABLE ACCESS (BY INDEX ROWID) OF 'CAN_XMLDATA' (TABLE) (Cost=613 Card=1 Bytes=126)
DOMAIN INDEX OF 'XMLIND2CAN_XMLDATA' (INDEX (DOMAIN)) (Cost=613)

From the customer plan:
TABLE ACCESS (BY INDEX ROWID) OF 'CAN_XMLDATA' (TABLE) (Cost=8595 Card=1 Bytes=128)
DOMAIN INDEX OF 'XMLIND2CAN_XMLDATA' (INDEX (DOMAIN)) (Cost=8595)

I don't believe differences in the amount of data can justify the difference in cost, so my question is: could an index in need to optimization affect the cost this way?
Thanks.
burleson
Hi,

The plans look the same but one table has lots more rows!

I would start by running the query with autotrace to see the execution details.

http://www.dba-oracle.com/t_OracleAutotrace.htm

Next, rebuild the context indexes during scheduled dowwntime.

Finally, post the autotrace results!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.