PL/Sql Performance Tunning - Interview Question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: PL/Sql Performance Tunning - Interview Question

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    72

    PL/Sql Performance Tunning - Interview Question

    Hi,

    Recently I have attend a Interview from MNC..

    they asked that "How you deal with performance issues in Pl/Sql block"...

    I said, Using Explan plan, we can know the full table scan area and we can put index whereever necessary ..like that
    (for individual block of statement we have to check...)

    Is there any systamatic way of approach to find the delay area of our Pl/Sql block of statement ???

    Pl. Help

    --Sathy

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    we use tkprof and profilers

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    With any performance issue, the first step is to look at the code and understand the process. Given table structure/size/indexes, an experienced candidate should be able to suggest whether the process in the code is appropriate, and where any performance bottlenecks might be expected.
    Look at the V$ tables to identify any poor performing SQL (either a single SQL that takes a long time, or one that happens too often).
    If there's no problem in the SQL, then you can identify the location of PL/SQL problems using DBMS_PROFILER.

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Can you explain how to use DBMS_PROFILER Please...

    What is tkprof ?? Whether it is third party tool or oracle inbuild...??

    --Sathy

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    This can all be looked up in the Oracle documentation.
    http://download-west.oracle.com/docs...a96625/toc.htm

    But I think that misses the point. If you go for a job, your resume/CV should indicate your level of experience. If I was interviewing someone with no Oracle experience, I wouldn't be asking them about how to identify performance issues.

    While DBMS_PROFILER doesn't get used as much as it should (for some reason the package is not installed by default until 10G), I would expect anyone who has worked in Oracle for a year or more to be at least AWARE of tkprof.

    If your resume/CV indicates that you have experience that you don't actually have, then you are trying to lie to your prospective employer. If you have straight from college/university/training courses and that is what the employer is expecting and looking for, then being honest about what you have learnt should not be a problem.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Sathy,

    Did you get the job?

    Tamil

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