Next to functionality, everyone wants their applications to perform well. However, all too often we are under such extreme pressures to write code that we forget about the performance aspects. Let's face it we are judged on producing applications, not tuning, at least until there are plenty of customers or clients banging on our doors complaining about not being able to get their jobs done or being able to order product. For this reason, we need to take a different look at the way we generate code and help ensure that what we write is going to perform well under certain conditions. This set of interview questions will begin to address the concerns that a hiring manager might have as to your proficiency in thinking proactively about performance and the code you write. As always, please take this article as a jumping off place for you to investigate and practice for your next interview.

1. PL/SQL code can be an issue with performance (loops, conditional statements, etc.) but if a section of PL/SQL code has been targeted as being a performance hog where might you first look?

Pointing our fingers at a section of PL/SQL code is always a very easy thing to do. Many DBAs, who "know" their database is tuned properly, often point fingers at the application. The VERY first thing we should do is remove the barriers between application groups and administrative groups and then start to understand the true problem.

2. After getting the DBA group involved with a performance what might the DBA group do for you to help determine if the application is potentially at fault?

Very simply the DBA group should be able to zero in on the application code that is executing, specifically the SQL being performed, trace and report to you if database issues are really causing the problem. As an example, a DBA should be able to tell you what wait events, if any, are accumulating and causing your application to perform poorly or if there is contention for internal resources.

3. What tools/utilities might you use to help tune your SQL?

It amazes me that I've in the past picked up books on PL/SQL code and they never even have a small section on tuning SQL. While tuning SQL might not be on the top of a PL/SQL developer's list of things to do, properly tuned SQL is at the core of producing well executing PL/SQL code and applications. Being able to run an EXPLAIN PLAN, and understanding its output, is critical and I'd never hire a developer that didn't understand how to produce efficient SQL. The following EXPLAIN COMMAND utility could be used to produce an explain plan for the given SELECT statement:

SQL> EXPLAIN PLAN FOR select * from mytable;

4. Besides running an EXAPLAIN PLAN to view the execution path of a SQL statement, what other means might you use to view explain plans?

After a SQL statement has executed you can view the EXPLAIN PLAN (if it's still in the shared SQL area) through the V$SQL_PLAN view. It is good to note that, while obtaining an EXPAIN PLAN through the use of the EXPLAIN PLAN command utility, as in question #3, viewing the EXPLAIN PLAN through the V$SQL_PLAN view gives the real access path taken during execution. Overloading modules is nothing more than a mechanism that allows the coder to reuse the same name for different programs that are within the same scope. Overloading is probably one of my favorite mechanisms to share and increase usability within code.

5. What does the PL/SQL Optimizer do?

The PL/SQL Optimizer will rearrange code for better performance during the translation of source code to system code; this is done by default. Valid ranges for the parameter PLSQL_OPTIMIZE_LEVEL are from 0 to 3 where the higher the value the more the compiler will try and optimize.

6. Name the two profiler tools and describe what they do.

1. The Profiler API, DBMS_PROFILER package, will compute the time a PL/SQL program spends at each line of code and within each subprogram; very handy if you're trying to just figure out where time is spent. This package will save the statistics it generates into database tables so you can query them.

2. PL/SQL hierarchical profiler, DBMS_HPROF package, will report on the dynamic execution profile of the PL/SQL code; generating a report with an option to also store into database tables for reporting.

7. Name a tracing utility that helps isolate PL/SQL problems and describe what it does.

The Trace API, DBMS_TRACE package, enables you to trace the orders in which subprograms run; allowing you to also specify which subprograms to run and placement of statistics gathered in database tables for custom reporting.

8. How might you determine to use PL/SQL Native Compilation to speed your code?

While you can natively compile any PL/SQL code, it is not always the best thing to do. Native compilation is suited better for those computational intensive procedures, not code that just runs SQL statements.

9. Have you ever used bulk processing? Why?

You've got to say yes here, right? Bulk processing enables you, through FORALL (for selecting data) and BULK COLLECT (for inserts, updates, deletes), to tune the communication layer (context switching) between the PL/SQL engine and SQL engine to improve performance. These are two very important PL/SQL performance enhancers that you cannot do without, both in your code and for an interview.

Well, there are the top questions that come to mind when I think about performance for PL/SQL code. Some of them are clearly PL/SQL code related but some are, (I hope you noticed), from a DBA perspective. I've said this before but many companies are expecting developers to have some DBA performance / tuning knowledge. Not having some experience with things like tracing, running an explain plan, or checking to see if an index is being used within your application are detrimental to you getting that next job. Do remember that these questions are not hard-n-fast questions. If I were the interviewer I'd most definitely jump off into additional real-world scenarios and see if the interviewee actually coded something, actually tuned something, and had some form of methodology in place that allowed them to circumvent performance issues. Giving your interviewer clear samples on how you have pieced together code as well as how you tested its performance can be priceless.

Related Articles

Top 10 PL/SQL Developer Job Interview Questions to Demonstrate Your Coding Skill
Top 5 SQL Questions for an Oracle Database PL/SQL Job Interview
Top 9 Database Object Tips You Need to Know for Your Next PLSQL Job Interview
Top 5 Basic Concept Job Interview Questions for Oracle Database PL/SQL Developers

Back to DBAsupport.com