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 ???
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.
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.
Reply With Quote
Bookmarks