1. statistics not up to date, lost an index, parameter changes, table growed which is related to statistics
2. from memory of course, you get those statistics from shared pool
3. add a function to your predicate or add a number
1.I would have checked,first thing, communication problems on the host or checking for heavy cpu process on the host.
2.Both v$librarycache and v$rowcache collects statistics regarding the usage of the shared pool.
The shared pool holds both the data dictionary cache and the fully parsed or compiled representations of PL/SQL blocks and SQL statements. PL/SQL blocks include procedures, functions, packages, triggers, and any anonymous PL/SQL blocks submitted by client programs.
Therfore,the collected statistics is on the memory usage and not on the data usage.
3.To enforce the optimizer choosing FTS instead of index access you should insert the the "FULL" hint in the "select" statement,like in the following example:
select /*+ FULL */
Originally posted by slimdave about /*+ FULL */ Not really an enforcement, more a suggestion. The hint just lowers the relative cost of full and index scans -- doesn't guarantee a FTS i believe
My reading of the doc suggests it IS enforced (8i Tuning, chapter 7):
"The FULL hint explicitly chooses a full table scan for the specified table."
"For example, Oracle performs a full table scan on the ACCOUNTS table to execute this statement, even if there is an index on the ACCNO column that is made available by the condition in the WHERE clause . . . "
However it seems that you do have to specify the table alias (or name if no alias given): /*+ FULL(abc) */ failure to do so might have it still using an index?
Strange question - the problem is usually how to avoid FTS ! ! !
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Weeeeellllllll, it says that it does, but from what i've read elsewhere (eg Tom Kyte), it doesn't necessarily force it. It artificially reduces the relative cost of a FTS compared with other methods, and this is usually enough to "tip the balance" in favour of FTS. But I don't think it's an absolute guarantee.