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 */
f_name,l_name,address
from emp
where emp_no=1;
1.I would have checked,first thing, communication problems on the host or checking for heavy cpu process on the host.
A comms problem would affect all uer/queries i would think. I guess they were looking for the execution plan answer.
Another answer might be that the query itself had been changed de to an upgrade/problem fix, and not regression tested.
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 */
f_name,l_name,address
from emp
where emp_no=1;
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
Originally posted by nir_s 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 */
f_name,l_name,address
from emp
where emp_no=1;
Regards,
Nir
No, he has specifically mentioned WHERE clause. Pando explained it.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
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.
"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
You should have replied that whatever skills you lack in performance tuning are more than made up for by your strength in implementing export/import as a bulletproof backup/recovery strategy.
Reply With Quote
Bookmarks