Friday, June 1, 2012

Secrets of Database Code Optimization

The pressures on database developers have never been greater, and modern requirements for database efficiency are becoming more intense. SQL is a complex programming language, and the variability in the language can make for inefficient execution.
If SQL is not developed with efficiency in mind, then further down the line, performance can be drastically affected in other areas. If improper code goes unchecked into production, then external problems can arise, with delayed response times, and significant downtime, which can be costly for a company. Worse still is the fact that locating and solving problematic or inefficient code is a far costlier process once the PI/SQL has been implemented within the system. Locating the problem can take a long time, which requires specific skills that come at a price, with results that cannot give a long term guarantee. This, alongside the dynamics of database applications, with constant updates and fine-tuning to ensure efficiency, makes the process fraught with danger.
If the problems can be routed in the development process, this in most cases eliminates these issues for companies and organisations alike. Indeed, it is well known that 80% of problems in the Oracle environment can be prevented by the optimal coding of SQL and other related source code. This is where data optimization for Oracle comes in. SQL optimization is designed to make sure that SQL performance is at its highest, in the production environment, before it's taken for application. The optimization process is built to intelligently scan through PL/SQL and other source code, in a way similar to a human optimization expert, by actively screening for inefficient code, replacing the code, and testing the new code for performance.
SQL optimization specifically scans through multiple SQL statements, and checks for warnings of poor performance. It changes the code reversibly, while displaying the new code. It also gives a query of every other possible coding plan, from most optimal, to least optimal. It also allows the user to see if other alternatives may be more suitable for their specific environment, and also to test within a database environment to check efficiency. It can also export newly written SQL for usage. This optimization regime allows for high quality and reliable performance testing, before database application.
Optimization is ideal for DBA's, who need to meet certain criteria, through constantly upgrading and configuration changes. Poor SQL is a nightmare for administrators, and SQL optimization allows them to easily target issues in performance. Optimization is also flexible, and can be integrated into other database types, for multiple implementations and testing.
SQL optimization is designed as a tuning tool, for future proof, and to insure against costly downtime in your runtime environment. The usage of SQL optimization allows developers to concentrate on other important aspects, such as application functionality, while not having to waste time on optimization. User experience suggests that the benefits of optimization are vast, with no small effect on system efficiency and application performance, and reliability.

Analyzing Performance of PL/SQL Code

Tuningplsql - Before you can tune your application, you need to figure out what is running slowly and where you should focus your efforts. Oracle and third-party vendors offer a variety of products to help you do this; generally they focus on analyzing the SQL statements in your code, offering alternative implementations, and so on. These tools are very powerful, yet they can also be very frustrating to PL/SQL developers. They tend to offer an overwhelming amount of performance data without telling you what you really want to know: how fast did a particular program run and how much did the performance improve after making this change?
To answer these questions, Oracle offers a number of built-in utilities. Here are the most useful:
DBMS_PROFILER
This built-in package allows you to turn on execution profiling in a session. Then, when you run your code, Oracle uses tables to keep track of detailed information about how long each line in your code took to execute. You can then run queries on these tables or—much preferred—use screens in products like Toad or SQL Navigator to present the data in a clear, graphical fashion.
DBMS_UTILITY.GET_TIME
Use this built-in function to calculate the elapsed time of your code down to the hundredth of a second. The scripts tmr.ot and plvtmr.pkg (available on the book’s web site) offer an interface to this function that allows you to use “timers” (based on DBMS_UTILITY.GET_TIME) in your code. These make it possible to time exactly how long a certain operation took to run and even to compare various implementations of the same requirement.
In Oracle Database 10g, you can also call DBMS_UTILITY.GET_CPU_TIME to calculate elapsed CPU time.
In case you do not have access to a tool that offers an interface to DBMS_PROFILER, here are some instructions and examples.
First of all, Oracle does not install DBMS_PROFILER for you automatically. To see if DBMS_PROFILER is installed and available, connect to your schema in SQL*Plus and issue this command:
  SQL> DESC DBMS_PROFILER
If you then see the message:
  ERROR:
  ORA-04043: object dbms_profiler does not exist
you will have to install the program.
For early Oracle versions, such as Oracle7 and Oracle8 Database, you need to ask your DBA to run the following scripts under a SYSDBA account (the first creates the package specification, the second the package body):
$ORACLE_HOME/rdbms/admin/dbmspbp.sql
$ORACLE_HOME/rdbms/admin/prvtpbp.plb
For later versions, you need to run the $ORACLE_HOME/rdbms/admin/profload.sql file instead, also under a SYSDBA account.
You then need to run the $ORACLE_HOME/rdbms/admin/proftab.sql file in your own schema to create three tables populated by DBMS_PROFILER:
PLSQL_PROFILER_RUNS
  
Parent table of runs
PLSQL_PROFILER_UNITS
  
Program units executed in run
PLSQL_PROFILER_DATA
  
Profiling data for each line in a program unit
Finally, you will probably find it helpful to take advantage of some sample queries and reporting packages offered by Oracle in the following files:
$ORACLE_HOME/plsql/demo/profrep.sql
$ORACLE_HOME/plsql/demo/profsum.sql
Once all these objects are defined, you gather profiling information for your application by writing code like this:
  BEGIN
     DBMS_OUTPUT.PUT_LINE (
       DBMS_PROFILER.START_PROFILER (
          'showemps ' ||
          TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS')
          )
       );
    showemps;
    DBMS_OUTPUT.PUT_LINE (
      DBMS_PROFILER.STOP_PROFILER);
  END;
Once you have finished running your application code, you can run queries against the data in the PLSQL_PROFILER_ tables. Here is an example of such a query that displays those lines of code that consumed at least 1% of the total time of the run:
  /* File on web: slowest.sql */
  SELECT      TO_CHAR (
                 p1.total_time / 10000000,
                 '99999999')
           || '-'
           || TO_CHAR (p1.total_occur) AS time_count,
              p2.unit_owner || '.' || p2.unit_name unit,
              TO_CHAR (p1.line#)
           || '-'
           || p3.text text
      FROM plsql_profiler_data p1,
           plsql_profiler_units p2,
           all_source p3,
           plsql_profiler_grand_total p4 
     WHERE p2.unit_owner NOT IN ('SYS', 'SYSTEM')
       AND p1.runid = &&firstparm
       AND (p1.total_time >= p4.grand_total / 100)
       AND p1.runid = p2.runid
       AND p2.unit_number = p1.unit_number
       AND p3.TYPE = 'PACKAGE BODY'
       AND p3.owner = p2.unit_owner
       AND p3.line = p1.line#
       AND p3.NAME = p2.unit_name
  ORDER BY p1.total_time DESC;
As you can see, these queries are fairly complex (I modified one of the canned queries from Oracle to produce the above four-way join). That’s why it is far better to rely on a graphical interface in a PL/SQL development tool.
After you’ve analyzed your code and identified bottlenecks, the following sections can help you determine what kinds of changes to make to improve code performance.