What determines whether a hints file needs to be configured for Post. (4330423)

At times you find that the Post is running very slow. Depending on the amount of time taken per DML per table, you may see the following messages in the event log to alert you about the situation:

11/01/07 22:26 Warning: sp_opst (for o.SID1-o.SID2 queue queuename) 15034 - Operation on "<owner.tablename>" is took longer than expected. Make sure proper index is being used.
11/01/07 22:26 Warning: sp_opst (for o.SID1-o.SID2 queue queuename) 15034 - Operation on "<owner.tablename>" is took longer than expected. Make sure proper index is being used.

The index may not be used by Post and it may be doing a full table scan or it is using an index but is still running slow.

If Oracle is not using the index, then the Post can run slow. It depends, among others, on whether and how often the tables are analyzed.  Configuring hints file can sometimes alleviate this depending on whether the hint is a valid one. If it is not valid it is simply ignored.

What determines whether an index or a hint is used by Oracle is a subtle topic. To fully utilize the potential of cost based optimizer, one may have to do the following:

- Analyze all tables regularly.

- Set the required OPTIMIZER_GOAL .

- Use hints to help direct the cost based optimizer where required.

- Use hints in PL/SQL to ensure the expected optimizer is used.

- Be careful with the use of bind variables.

The main contributors to not using an index where it is expected to be used are:

  a) The index is 'not selective'.  As cost based optimizer (CBO) computes a cost for
     access to the expected amount of data it determines that
     the cost of individual index block look-ups will cost more
     than scanning the entire table which it can do with multi-
     block reads.

  b) The 'range of values' and hence the number of rows CBO is
     expecting to return is inaccurate due to either inaccurate statistics OR the use of BIND values limiting a range.

This problem can also arise when the query plan is being generated by the CBO. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster than accessing the table via the index. Fundamental things that can be checked are:

USER_TAB_COLUMNS.NUM_DISTINCT - This column defines the number of distinct values the column holds.

USER_TABLES.NUM_ROWS - If NUM_DISTINCT = NUM_ROWS then using an index would be preferable to doing a FULL TABLE SCAN. As the NUM_DISTINCT decreases, the cost of using an index increase thereby making the index less desirable.

USER_INDEXES.CLUSTERING_FACTOR - This defines how ordered the rows are in the index. If CLUSTERING_FACTOR approaches the number of blocks in the table, the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Decrease the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT - A higher value will make the cost of a FULL TABLE SCAN cheaper.

One must supply the leading column of an index, for the index to be used (unless you use a FAST FULL SCAN or SKIP SCANNING).
There are many other factors that affect the cost, but sometimes the above can help to show why an index is not being used by the CBO. If from checking the above you still feel that the query should be using an index, try specifying an index hint. Obtain an explain plan of the query either using TKPROF with TIMED_STATISTICS, so that one can see the CPU utilization, or with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index. If the optimizer mode is set to RULE, it'll use the RBO even in the presence of statistics.    

For optimizer not using index, one may have to dig deeper to find the underlying cause, whether it is bad or missing stats, optimizer_index_cost_adj set wrong, etc. It is a question of what caused the optimizer which works on mathematical principles to come to the wrong conclusion.

Good hints give the optimizer more information -- not directions on what to do. good hints include:

first rows
all rows
driving site
o cardinality
optimizer dynamic sampling

The creation of index and configuring the hints file usually helps in such situations. For more details on configuring hints file, please see Admin Guide Chapter 5 "Shareplex Optional Setup" section "Configuring the hints file". If the tables are analyzed, tables have indexes and the table definitions have not changed since the last activation, then Shareplex uses index when building a SQL in the following order:

primary key
unique key
non-unique index

or

full table scan