- Interview questions for oracle sql developer how to#
- Interview questions for oracle sql developer code#
Interview questions for oracle sql developer code#
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. Name the two profiler tools and describe what they do.ġ. 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.Ħ. The PL/SQL Optimizer will rearrange code for better performance during the translation of source code to system code this is done by default.
Overloading is probably one of my favorite mechanisms to share and increase usability within code. 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. 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. Besides running an EXAPLAIN PLAN to view the execution path of a SQL statement, what other means might you use to view explain plans?Īfter 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. 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 Ĥ.
Interview questions for oracle sql developer how to#
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. 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. 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. What tools/utilities might you use to help tune your SQL? 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.ģ. 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. 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? 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.Ģ. Many DBAs, who “know” their database is tuned properly, often point fingers at the application. Pointing our fingers at a section of PL/SQL code is always a very easy thing to do. 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?
As always, please take this article as a jumping off place for you to investigate and practice for your next interview.ġ. 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. 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.
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. However, all too often we are under such extreme pressures to write code that we forget about the performance aspects. Next to functionality, everyone wants their applications to perform well. This set of interview questions addresses the concerns that a hiring manager might have concerning the performance of the code you write. All rights reserved.All too often, database developers are under such extreme pressure to write code that they forget about the performance aspects.
0 kommentar(er)
