n a previous 10-Minute Solution, "New PL/SQL Features in Oracle 8i: Part I
," I write that Oracle PL/SQL programmers develop complex code nowadays, utilizing custom and Oracle-supplied packages. It therefore becomes critical to identify and solve performance problems. Oracle 8i's Profiler does just that: it analyzes PL/SQL code and locates bottlenecks.
As you probably know, the first working version of your code is usually inefficient and slow. There is a way to measure its execution time on a line-by-line basis without using a profiler, but it's tedious and slow. It involves calling the GET_TIME function (from the Oracle-supplied DBMS_UTILITY package) before and after selected lines of code. That's primitive and inefficient because it requires adding multiple lines of temporary code and recompiling it. Plus you still don't know how many times each line gets executed without adding even more code.
In this 10-Minute Solution I share my experience in profiling PL/SQL code using one of the GUI tools with profiling capabilities (Quest Software's TOAD, for "Tool for Oracle Application Developers") as well as by using the Oracle standard application development tool called SQL*Plus.
Because PL/SQL programming involves writing and using custom and Oracle-supplied packages, as well as standalone procedures and functions, performance tuning is an important task for Oracle developers. Checking code line by line is slow and inefficient because it requires adding multiple lines of temporary code and recompiling it.
Use the Oracle 8i Profiler to analyze PL/SQL code and locate bottlenecks. Improving PL/SQL code performance is an iterative process:
- Run the application code with profiler data collection enabled.
- Analyze the profiler data and identify performance problems.
- Fix the problems.