-
interview question
During interview last week, I couldn't explain properly the following 3 questions:
1)Users complaining that a particular query was ok till last week...now it is very slow..he has not changed anything. As a
DBA how u go about it?
Ans: I told execution plan might have changed.....but not able to
explain the steps to rectify.....
Can anyone give detailed steps to take?
2)Regarding gets and misses of V$librarycache and v$rowcache...
He asked these views uses only memory or data to get 'gets &misses' information.....
Frankly I don't understand the question itself...
3)How to make a query (with where clause) to use Full table scan(means not to use index)?
-
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
where upper(X) = 'X'
where to_char(hiredate, 'YYYYMMDD') = '20030504'
where salary + 0 = '1000000'
-
Hi,
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;
Regards,
Nir
-
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"
-
Again if the index is Fn based index...u need to add Fn to Fn..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
-
I bow to the wisdom of the professor of the SOHK.
"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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|