Oracle11g PL/SQL Compiler Options

PL/SQL has traditionally been run-time interpreted. The compiler makes a p-code and this code is stored in the database along with the PL/SQL source code.



Oracle11g has expanded the internal PL/SQL optimizing compiler introduced in Oracle10g.


Oracle10g implemented an optimizing compiler. This compiler tries to eliminate unaccessed code from being compiled, looks for redundancies, and more.

· Level 0 – no optimization (Oracle9 compatible)
· Level 1 – eliminates unaccessed code from compiler output
· Level 2 – restructures code, aggressive optimization


Oracle11g has enhanced the optimizing compiler, adding a level 3 that streamlines inline code and calls to inline code.




The compiler can be invoked via the commands listed above.

Objects and compiler settings can be viewed using the USER_PLSQL_OBJECTS_SETTINGS (for current objects) and V$PARAMETER (to see the PL/SQL compiler settings at the database level). ALTER SESSION can change any of these settings for the duration of a connected session (ie: the session used to create and/or compile code).



The PL/SQL compiler can give useful warnings. These warnings can provide a variety of information such as performance tips to dead code to other coding issues. Follow the instructions above to implement and use.




Oracle does not allow for functions called from SELECT or DML statements to change the table that is referenced in the SELECT or DML. These functions also cannot issue a commit or an implied commit (such is the kind from a DDL statement).





Oracle can insure specific application behavior at the package level using these settings. These settings allow for specific behavior to be enforced when calling these routines either directly or indirectly from SQL statements.


Summary

Oracle continues to enhance the PL/SQL compiler. It is important to understand what the options are and how they can be used to enhance your applications. This section is an excerpt from my Advanced PL/SQL Tips and Techniques course.

SQL T - Trace File Analysis Tool

SQLT (SQLTXPLAIN) is a tool that inputs one SQL statement and outputs a set of comprehensive diagnostic files for SQL performance analysis and tuning.

***Note*** These notes are from the SQLT user documentation

Comprehensive HTML report that includes an enhanced explain plan, details about the tables accessed by the SQL, their indexes, their columns, CBO statistics including Histograms, partitions and subpartitions if applicable, initialization parameters in effect, stored outlines associated to the SQL, SQL profiles if any, child plans, metrics of SQL execution performance, observations, etc.

SQL T is available via Metalink note 215187.1. Download SQLT.zip and follow the directions to install it.

Since the tool is installed into its own schema and makes use of temporary objects for most of its data structures, it is light and moderate intrusive. The schema owner SQLTXPLAIN only gets granted a small subset of roles and attributes in order to generate SQL analysis results. This SQLTXPLAIN schema owner does not read application data itself.

· This SQLT tool installs into its own schema SQLTXPLAIN
· It does not install any objects into application schemas
· Its footprint is very small
· The RDBMS version can be 9i, 10g or 11g
· The OS can be UNIX, LINUX or WINDOWS
· Source is not wrapped (SQL and PL/SQL)
· It can be installed in RAC systems
· It installs by executing just one script sqcreate.sql

**Note** More information is available by running it as a SYSDBA.

SQLT inputs one SQL DML statement provided as one of these methods:
1. XPLAIN: As a stand-alone SQL in a flat text file.
2. XTRACT: As a memory-resident SQL, identified by its hash_value or sql_id.
3. XECUTE: As a stand-alone script that contains one SQL, together with the declaration and values of its bind variables.
4. XTRXEC: As a combination of XTRACT and XECUTE, this method gets a SQL from memory and executes both XTRACT and XECUTE.

The recommended methods are XECUTE and XTRACT. Try avoiding the XPLAIN method since the Explain Plan generated by it may not be accurate if your SQL contains bind variables.

SQLT runs various traces and collects information from a variety of sources including AWR.

The main.html doc…everything is hyperlinked and all information about this SQL statement is included. This document includes all stats, tablespace info, CBO explain plan info from the 10053 trace, and more!

This tool literally exposes everything you would want to know about a particular SQL statement and how it interacted with the Oracle RDBMS.

Index Monitoring

Oracle9i has introduced an easy method to see of indexes are even being used by your application.

Amazing as it sounds, I am finding an average of 10 indexes per table object, particularly in larger applications. Oracle probably is not using most of these indexes or only uses them occasionally such as at month end/quarter end/year end processing. These unused indexes are maintained and can make batch cycles run twice/three times as long as needed.

My SQL Performance Tuning class (being offered frequently via the Web…or we can schedule this at your company) goes into great detail on how Oracle uses indexes, how the optimizer decides which index to use, as well as this index-monitoring topic…which is an excerpt from my SQL Performance Tuning course guide.

Index Monitoring


Oracle has the ability to tell if an index has ever been used in a SELECT or sub-query. This technology uses the MMON process and has to be tripped on to populate the V$OBJECT_USAGE view.

SQL> desc v$object_usage
Name Null? Type
----------------------------------------- -------- --------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)

SQL>

If the index then appears in a query explain plan, the USED column will be set to ‘YES’, otherwise it is ‘NO’. Make sure to connect as the object owner. Only objects owned by the connected user will be visible in this view.


This script can be used to turn on index monitoring for all indexes for a particular schema owner: Start_Index_Monitoring.sql …is a SQL creating SQL script that turns index monitoring on for each index for the schema owner
Index_Monitoring.sql produces a nice report (see below) of the counts.
1. Indexes that were used
2. Indexes that were not used
3. Number of indexes being monitored
4. Number of tables that have the indexes

Ask me for these scripts...

spool Index_Monitor_setup.sql
select 'Alter Index ' index_name ' monitoring usage;' from user_indexes;

spool off
start Index_Monitor_setup.sql

Portion of Index_Monitor_Setup.sql script.


select to_char(sysdate,'mm/dd/yy hh:mi:ss') Date_Time from dual;
select 'Table Count = ' count(*) from user_tables;
select 'Index Count = ' count(*) from v$object_usage;
Select 'Indexes Used = ' count(*) from v$object_usage where USED = 'YES';
select 'Indexes NOT Used = ' count(*) from v$object_usage where USED = 'NO';

Portion of Index_Monitor.sql Script


Index Monitoring Report Date: 02/14/10 10:33:37

Table Count = 27

Index Count = 23

Indexes Used = 3

Indexes NOT Used = 20

Index Monitor Report



Ask me for these scripts (Dhotka@Earthlink.net).

Eliminating unused indexes is batch cycle tuning. Indexes and table structures have 5 Oracle operations applied to each DML:
Lock acquired
Before image journalled in rollback segment
Actual change applied
After image journalled to archive log
Lock released

…for each object associated with the DML. If the index is not being used, then it should not be there as it is being maintained by Oracle.

*** True Story *** A shop in Denver had 98 tables and over 1100 indexes! I tripped on index monitoring and when I left 3 days later, Oracle had not used 100 indexes yet.

*** Tip *** The programmer should use this technique when testing an application and build scripts that create/drop indexes before month end/quarter end/year end processing.

Summary:
Index monitoring is a useful tool to eliminate unused indexes that will decrease batch cycle times and decrease insert/update times.
My SQL Performance Tuning class has a very interesting half-day lecture on indexes, how they are created, how Oracle uses them, when they are not used and why, and how 10g+ tablespace options has an adverse affect on index utilization.

Using the PL/SQL Profiler

Oracle has provided the ability to see how much time each step of a PL/SQL routine takes since Oracle8i. The environment is easy to setup and the information is easy to retrieve.

Introduction

Why Profile? When tuning SQL, it is easy because there is just the single SQL statement. With PL/SQL, there are SQL statements, SQL imbedded in implicit and explicit cursors, called routines (functions/procedures), and the PL/SQL code itself. When a PL/SQL routine is taking 5 minutes to run, exactly what code is taking how much time. This is the information that the PL/SQL profiler provides. Without this information, the person trying to tune the PL/SQL is only shooting in the dark, perhaps pulling out and tuning the SQL within the code, but otherwise has no idea what a PL/SQL routine is doing when it comes to time spent on each line of code.

The profiler is easy to setup and easy to use. Tools like Quest Software’s TOAD provides a nice GUI interface to this profiler.

Installation

The profiler has two scripts that setup the environment. Both are found in the /rdbms/admin folder.

The PROFLOAD.sql script needs to be run as SYS (connect AS SYSDBA). This script will create the DBMS_PROFILER package and create synonyms and permissions for usage.

The PROFTAB.sql script is recommended to be executed for each user desiring to run the DBMS_PROFILER package. This script sets up the three main tables: PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, and the PLSQL_PROFILER_DATA. This script can be setup so that all the users share these tables but this topic is beyond the scope of this paper.

Understanding the Profiler Process

Profiling is initiated with the START_PROFILER program. Start this process then execute the PL/SQL routine to be profiled. When execution is done, run the STOP_PROFILER program. This will stop the profiling process and write the collected information to the profiler tables.

There are two more routines that control the collection of profiler data: PAUSE_PROFILER and RESUME_PROFILER. These routines might be useful if only certain statistics are of interest from a rather large PL/SQL program, or perhaps called subroutines are not desired to be profiled. These routines are typically imbedded in the PL/SQL code.

The FLUSH_DATA routine can also be called to periodically write the collected information to the profiler tables. This might be useful if the STOP_PROFILER routine is taking an excessive amount of time when profiling larger PL/SQL routines. This routine is typically embedded in the PL/SQL code.

When the START_PROFILER routine is started, the Oracle RDBMS collects a variety of information about the PL/SQL routine while it is being executed. The STOP_PROFILER then stops this collection process and writes the collected information to the three profiler tables.

These tables are then examined using SQL to view the results of the profiler collection.


Using the PL/SQL Profiler

There will be additional profiler information collected if the object being profiled has been compiled using the debug option.

This example will use this simple LOOPING_EXAMPLE code:

CREATE OR REPLACE PROCEDURE looping_example
IS
loop_counter NUMBER := 0;
BEGIN
FOR rec IN (SELECT *
FROM emp)
LOOP
loop_counter := loop_counter + 1;
DBMS_OUTPUT.put_line (
'Record ' loop_counter ' is Employee ' rec.ename
);
END LOOP;

DBMS_OUTPUT.put_line ('Procedure Looping Example is done');
END;

To capture PL/SQL profile information, execute the following statements. The comment submitted with the START command will be populated into the RUN_COMMENT in the PLSQL_PROFILER_RUNS table, see below.

SQL> execute DBMS_PROFILER.START_PROFILER(‘User0 Looping_Example’);
SQL>
SQL> execute LOOPING_EXAMPLE:
SQL>
SQL> execute DBMS_PROFILER.STOP_PROFILER;


This example code and the PROFILER_RPT.sql SQL*Plus script (runs all 3 SQL statements in an interactive script) are available from www.DanHotka.com .

The profiler populates three tables with related information. PLSQL_PROFILER_RUNS has information about each time the profiler is started, including the comment entered when the profiler session was initiated. The PLSQL_PROFILE_UNITS contains information about the PL/SQL code executed during the run. Each procedure, function, and package will have its own line in this table. The PLSQL_PROFILE_DATA contains the executed lines of code, code execution time, and more. The following SQL is useful in extracting the profiler information.

First, find the profiler run of interest. The RUN_COMMENT column has the

select runid, run_owner, run_date, run_comment
from plsql_profiler_runs;

RUNID RUN_OWNER RUN_DATE RUN_COMMENT ---------- --------------- --------- -------------------------
10 USER0 01-FEB-10 LOOPING_EXAMPLE_2/1/2010

In this SQL, enter the RUNID from the prior SQL statement. Oracle will place several lines of ‘’ in the UNIT_OWNER column. This information is the overhead that Oracle incurred executing the code, not the code itself. Since I am not interested in this clutter, I coded the SQL to just show me the profiler information of interest to me.

select runid, unit_number, unit_type, unit_owner, unit_name, unit_timestamp
from plsql_profiler_units
where unit_owner <> ''
and runid = &rpt_runid;

RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER
---------- ----------- --------------- --------------- --------------- --------- 12 4 PROCEDURE USER0 LOOPING_EXAMPLE 01-FEB-10

select pu.unit_name, pd.line#, pd.total_occur passes, round(pd.total_time / 1000000000,5) total_time, us.text text
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &rpt_runid
and pd.unit_number = &rpt_unitid
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION');

UNIT_NAME LINE# PASSES TOTAL_TIME TEXT --------------- ---------- ---------- ---------- --------------------------------------------- LOOPING_EXAMPLE 1 1 .00032 PROCEDURE looping_example LOOPING_EXAMPLE 3 1 .00002 cursor emp_cur is LOOPING_EXAMPLE 4 1 1.25721 select ename LOOPING_EXAMPLE 5 0 .00011 from emp; LOOPING_EXAMPLE 6 1 .00005 loop_counter NUMBER := 0; LOOPING_EXAMPLE 9 1 0 BEGIN LOOPING_EXAMPLE 10 1 .00002 open emp_cur; LOOPING_EXAMPLE 12 1 .00436 fetch emp_cur into ename; LOOPING_EXAMPLE 14 15 .00031 while emp_cur%FOUND LOOPING_EXAMPLE 16 14 .00029 loop_counter := loop_counter + 1; LOOPING_EXAMPLE 17 14 .00398 DBMS_OUTPUT.put_line LOOPING_EXAMPLE 19 14 .01895 fetch emp_cur into ename; LOOPING_EXAMPLE 20 1 0 END LOOP; LOOPING_EXAMPLE 22 1 .00004 DBMS_OUTPUT.put_line LOOPING_EXAMPLE 24 1 .00107 close emp_cur;


This code cleans up the profiler tables.

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;



Summary

The PL/SQL Profiler is an essential tool when tuning PL/SQL and the SQL coded into these same routines. Without something like this profiler process, it is impossible to tell where the time is spend when tuning PL/SQL code.

New TOAD Book

Hi!

Check out the new updated book: TOAD HANDBOOK

Isbn: 978-0321649102 (available on Amazon.com)

Bert and I did a nice job updating this book to the current release of TOAD.

This book contains chapters on:
Setup and Configuration
Schema Browser
SQL and PL/SQL Editors
Database Reporting
Tuning Tools in TOAD
Including the new AWR and Trace File Browsers!
Database Management
Includes many common DBA tasks that TOAD automates
Other useful tools
TOAD Apps Designer

This is a good book for anyone working with TOAD.

Bert: Thank you for working with me again!

Dan