DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: interview question

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Posts
    32

    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)?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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'

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  10. #10
    Join Date
    May 2002
    Posts
    2,645
    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
  •  


Click Here to Expand Forum to Full Width