Packages: pkg_db_type & pkg_db_const

Like most developers, when coding PL/SQL I find myself requiring the use of the same data types and constants. To prevent excessive repetition of the same declarations and values I have centralised key type definitions and constants into two packages:

pkg_db_type

This package contains type definitions for common database types that I find myself requiring throughout application code. The type definitions are database level types and are not associated with any application. The package is specifically designed so that it has no dependencies on other objects, such as being anchored to table column types.

CREATE OR REPLACE PACKAGE pkg_db_type
AS

--------------------------------------------------------------------------------
--  Package       : pkg_db_type
--  Author        : Mark Hoxey
--  Creation Date : 21/09/2009
--
--  Overview      : Common database types
-----------------------------------------------------------------
-- Source Control: $Id$
-----------------------------------------------------------------
--  Modification History :
--
--  Initials  Date        Description
--  --------  ----------  ---------------------------------------
--  M Hoxey   21/09/2009  Initial version
--  M Hoxey   04/02/2010  Added context specific data types
-----------------------------------------------------------------

-- The size of an Oracle database object name
SUBTYPE t_object_name   IS VARCHAR2(30 BYTE);

-- The maximum size of a VARCHAR2 string
SUBTYPE t_big_string    IS VARCHAR2(32767 BYTE);

-- The maximum size of a VARCHAR2 string for
-- the database engine
SUBTYPE t_max_db_string IS VARCHAR2(4000 BYTE);

-- The data type of SQLERRM
SUBTYPE t_sqlerrm       IS VARCHAR2(512 BYTE);

-- The data type of an error message for RAISE_APPLICATION_ERROR
SUBTYPE t_err_msg       IS VARCHAR2(2000 BYTE);

-- The data types of Oracle module, action and client_info
-- strings
SUBTYPE t_action_name   IS VARCHAR2(32 BYTE);
SUBTYPE t_module_name   IS VARCHAR2(48 BYTE);
SUBTYPE t_client_info   IS VARCHAR2(64 BYTE);

-- Data types for context variables
SUBTYPE t_context_namespace IS VARCHAR2(30 BYTE);
SUBTYPE t_context_attribute IS VARCHAR2(30 BYTE);
SUBTYPE t_context_value     IS VARCHAR2(256 BYTE); 

END pkg_db_type;
/

pkg_db_const

Following on from the previous package, this one contains commonly used, *application agnostic* constants. Again, this package has no dependencies on any other object and has no package body.

CREATE OR REPLACE PACKAGE pkg_db_const
AS

-----------------------------------------------------------------
--  Package       : pkg_db_const
--  Author        : Mark Hoxey
--  Creation Date : 24/09/2009
--
--  Overview      : Common database associated constants
-----------------------------------------------------------------
-- Source Control: $Id$
-----------------------------------------------------------------
--  Modification History :
--
--  Initials  Date        Description
--  --------  ----------  ---------------------------------------
--  M Hoxey   24/09/2009  Initial version
--  M Hoxey   04/02/2010  Added context specific constants
-----------------------------------------------------------------

-- The size of an Oracle database object name
c_object_name_len          CONSTANT NUMBER(2) := 30;

-- The maximum size of a VARCHAR2 string
c_max_plsql_string_len     CONSTANT NUMBER(6) := 32767;

-- The maximum size of a VARCHAR2 string for
-- the database engine
c_max_db_string_len        CONSTANT NUMBER(4) := 4000;

-- The size of SQLERRM
c_max_sqlerrm_len          CONSTANT NUMBER(3) := 512;

-- The size of an error message for RAISE_APPLICATION_ERROR
c_max_err_msg_len          CONSTANT NUMBER(4) := 2000;

-- The size of Oracle module, action and client_info
-- strings
c_action_name_len          CONSTANT NUMBER(2) := 32;
c_module_name_len          CONSTANT NUMBER(2) := 48;
c_client_info_len          CONSTANT NUMBER(2) := 64;

-- The size of a dbms_output line
--   Oracle 9i and earlier is 255
--   Oracle 10g and later is 32767
c_max_dbms_output_line_len CONSTANT NUMBER(5) := 32767;

-- The size of various context details
c_context_namespace_len    CONSTANT NUMBER(2) := 30;
c_context_attribute_len    CONSTANT NUMBER(2) := 30;
c_context_value_len        CONSTANT NUMBER(3) := 256;  

END pkg_db_const;
/

The drawback to this centralisation of types and constants is that any code using them will have a dependency on the packages. Within Oracle this has impact. If you’ve ever faced the following error then you will appreciate this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "<>" has been invalidated
ORA-04065: not executed, altered or dropped package "<>"
ORA-06508: PL/SQL: could not find program unit being called: "<>"
ORA-06512: at line 1

Oracle has made improvements in its dependency checking to reduce the invalidation of dependent code in recent versions of the database. It used to be that simply recompiling the referenced package without change would result in a dependent package being invalidated.

Let’s see what changes we’re able to make to our packages that won’t invalidate dependent packages. We start with a simple package that references both pkg_db_types and pkg_db_const packages and then make changes these packages to see when our dependent package is invalidated. Our test package is:

CREATE OR REPLACE PACKAGE pkg_test
AS
   FUNCTION get_value
   RETURN pkg_db_type.t_big_string;
END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   c_const CONSTANT NUMBER(6) := pkg_db_const.c_object_name_len;

   FUNCTION get_value
   RETURN pkg_db_type.t_big_string
   AS
   BEGIN
      RETURN TO_CHAR(c_const);
   END get_value;
END pkg_test;
/

Now we’ll make the following changes to pkg_db_type:

• Compile it without change
• Add a new type
• Remove a non-referenced type
• Alter a non-referenced type
• Alter a referenced type

We can test whether a change has invalidated our dependent package by running:

SELECT object_name
,      object_type
,      status
FROM   user_objects
WHERE  object_name = 'PKG_TEST'
/

Rather than list all the changes made to pkg_db_type, I’ll simply list the outcome of the test. Note, these results were obtained under Oracle Database version 11.2.0.2.0.

Our starting status is:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

After adding a new type to pkg_db_type, we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

Removing a non-referenced type, we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

Altering a non-referenced type also results in:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             VALID
PKG_TEST        PACKAGE BODY        VALID

It’s only when we alter a referenced type do we get:

OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
PKG_TEST        PACKAGE             INVALID
PKG_TEST        PACKAGE BODY        INVALID

This is quite pleasing. We can do many operations to our package and not invalidate our dependent code.

What about the constants though? Let’s perform the same type of test but this time make changes to pkg_db_const:

• Compile it without change
• Add a new constant after our referenced constant
• Add a new constant before our referenced constant
• Remove a non-referenced constant after our referenced constant
• Remove a non-referenced constant before our referenced constant
• Alter a non-referenced constant after our referenced constant
• Alter a non-referenced constant before our referenced constant
• Alter a referenced constant
• Alter the order of constants that includes our referenced one

The scenarios that invalidated our test package were:

• Add a new constant before our referenced constant
• Remove a non-referenced constant before our referenced constant
• Alter a referenced constant
• Alter the order of constants that includes our referenced one

Strangely enough, the position for adding a new constant impacted whether or not the test package was invalidated. Further testing using a local variable within pkg_test, as opposed to a package level constant, showed the same effect.

So, it would seem that we can make pretty much any change to the types package without impacting dependent packages so long as we don’t alter the specific types we’re using. The constants are a bit more complex. While we could say that if we want to add new constants we should be okay if we add them to the end of the package I would probably play safe and steer clear of any changes if there were concerns about impacting dependent code in a production system whilst the system was up.

The centralising of type definitions and constants in packages as shown here is a debatable strategy. On one hand it means we don’t have to repeat the definitions throughout different pieces of code. On the other hand we may be creating a situation where changes to these packages are difficult to make. The balance that I’ve tried to strike with these particular packages is that they are application agnostic and changes to them should be infrequent. Of course, in an environment with many developers this does rely on all members understanding this. If the packages end up being dumping grounds for any odd type or constant then problems will arise.

---

Download scripts shown in this post.

Coding Standards

This post will be quite short and simply describe the conventions that I follow in my code. I fully realise that many different views abound and that coding standards often create intense discussion. The main reason for this post is not to promote my conventions but to simply note them for anyone reading code from this site.

I have yet to find a standard that I consider totally acceptable, which is why you may find me not abiding by my self-imposed standards.

Schema Naming Conventions

<table_name>_pk denotes both the primary key index and primary key constraint on table <table_name>
<table_name>_i<n> denotes an index on <table_name>, where <n> is a simple integer
<table_name>_uk<n> denotes a unique index (other than the primary key) as well as unique constraint on <table_name>, where <n> is a simple integer
<table_name>_fk<n> denotes a check constraint on <table_name>, where <n> is a simple integer
<table_name>_ch<n> denotes a check constraint on <table_name>, where <n> is a simple integer

I don’t have any specific naming convention for database tables, views and materialised views other than to use a name that reflects the data content.

PL/SQL

Naming Conventions

I tend to keep things fairly simple here:

t_ prefix denotes a type definition and I don’t differentiate between PL/SQL types and schema types
c_ prefix denotes a constant, whether locally declared or package globally
g_ prefix denotes a package level, global variable
l_ prefix denotes a local routine variable
p_ prefix denotes a routine parameter and I don’t differentiate between IN, OUT and IN OUT parameters
_cur suffix denotes a cursor, used for both type declarations and variables
_rec suffix denotes a record, used for both type declarations and variables
_tab suffix denotes a collection, used for both type declarations and variables (associative arrays, nested tables & varrays)

Upper & Lower Case

I use upper case for reserved words, built-in function names and other Oracle defined identifiers. Lower case for everything else, including Oracle’s own package names.

Indentation

I use 3 spaces for indentation. Tab characters are avoided due to the formatting problems with different editors.

Other

For lists that comma separated, such as the columns in a CREATE TABLE statement, the SELECT list of a query and the parameters of a routine, I place the comma at the start of the line rather than at the end, only one entry per line.

Using autotrace – part 2

In the previous part of this post we briefly looked at autotrace, what it is, how to access it and what it can tell us. In this part we’ll use autotrace to make some observations about simple queries to illustrate it’s usefulness.

The examples that follow were performed on an Oracle 11.2.0.2 64-bit database running on Windows 7 64-bit.

We’ll start off by creating a test table with 3 columns, a primary key on one of the columns and populate it with 1 million rows. Like any good Oracle developer we gather statistics on the table once we’ve populated it.

SET ECHO ON
SET DEFINE OFF

CREATE TABLE test_tab
  (id      NUMBER(10) NOT NULL
  ,val     NUMBER(10) NOT NULL
  ,padding CHAR(50)   NOT NULL
  )
/

INSERT INTO test_tab
SELECT rownum
,      MOD(rownum,1000)
,      'X'
FROM   dual
CONNECT BY rownum <= 1000000
/

COMMIT
/

CREATE UNIQUE INDEX test_tab_pk
ON test_tab (id)
/

ALTER TABLE test_tab
ADD CONSTRAINT test_tab_pk
PRIMARY KEY (id)
/

EXEC dbms_stats.gather_table_stats('','TEST_TAB')

First up, let’s run a simple query to look up a single row from the table with autotrace enabled in its default state. A word of warning though, the script clears the buffer and library caches first so please don’t try this on your production system! The script we’ll execute is:

SET ECHO ON

ALTER SYSTEM FLUSH BUFFER_CACHE
/

ALTER SYSTEM FLUSH SHARED_POOL
/

SET AUTOTRACE ON

SELECT *
FROM   test_tab
WHERE  id = 123456
/

SET AUTOTRACE OFF

Which gives us the following output:

SQL> SET ECHO ON
SQL>
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE
  2  /

System altered.

SQL>
SQL> ALTER SYSTEM FLUSH SHARED_POOL
  2  /

System altered.

SQL>
SQL> SET AUTOTRACE ON
SQL>
SQL> SELECT *
  2  FROM   test_tab
  3  WHERE  id = 123456
  4  /

        ID        VAL PADDING
---------- ---------- --------------------------------------------------
    123456        456 X

Execution Plan
----------------------------------------------------------
Plan hash value: 752309145

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    60 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB    |     1 |    60 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TEST_TAB_PK |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=123456)

Statistics
----------------------------------------------------------
         73  recursive calls
          0  db block gets
         77  consistent gets
         45  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> SET AUTOTRACE OFF

As can be expected, the execution plan section of the output shows that Oracle obtained the relevant row from the table via the primary key index. The execution plan shown is the same as would be obtained from using EXPLAIN PLAN FOR … followed by DBMS_XPLAN.DISPLAY. The Statistics section is where autotrace shows its “value add” beyond the execution plan.

The Statistics from the initial query tells us (keep in mind that the library and buffer caches had been cleared):

• Oracle needed to do 73 internal query operations to run the query. These internal queries are lookups into the data dictionary to ensure the table exists, what columns the table has, whether the user has access to the table, etc.
• The 0 db block gets tells us that Oracle did not access any data in “current” mode, which is what would be required if Oracle were to update the data.
• 77 consistent gets gives us the number of logical I/O operations Oracle made into it’s block cache. Many of these would have resulted from the recursive queries Oracle ran.
• Oracle was required to make 45 physical reads against the disk in order to get the data needed for the query. This too will include data needed for the recursive calls made and not just the TEST_TAB table that the query ran against.
• Since the query did not modify the data the redo size is 0, which would be expected.
• The next two entries tells us how much data was transferred from the database to the SQL Plus client. The roundtrips entry tells us that all the data was done via 1 roundtrip and that Oracle didn’t need to stream data to SQL Plus across multiple trips to retrieve all the data.
• 6 sorts of data were required to complete the query. Given that the execution plan didn’t contain an operation requiring a sort we can assume that the sorts were done by the recursive calls.
• Finally the query resulted in 1 row being returned back to the client.

If we were to execute the exact same query again without flushing the library and buffer caches, the autotrace statistics would change:

SQL> SET ECHO ON
SQL> SET AUTOTRACE ON
SQL>
SQL> SELECT *
  2  FROM   developer.test_tab
  3  WHERE  id = 123456
  4  /

        ID        VAL PADDING
---------- ---------- --------------------------------------------------
    123456        456 X

Execution Plan
----------------------------------------------------------
Plan hash value: 752309145

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    60 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TAB    |     1 |    60 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TEST_TAB_PK |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=123456)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> SET AUTOTRACE OFF
SQL>
SQL> SELECT blevel
  2  FROM   user_indexes
  3  WHERE  index_name = 'TEST_TAB_PK'
  4  /

    BLEVEL
----------
         2

The execution plan section shows that we executed a query that was identical to the previous one. However, since Oracle now has the query cached in its library and the table data cached in the buffer cache we see the elimination of recursive calls and sorts in the statistics section. More importantly, logical I/O (consistent gets) has dropped dramatically and is now just 4. We can calculate this value by looking at the execution plan and the size of the index. According to Oracle it “walked” the index to find where the relevant row was in the table and then jumped straight to the row. Querying the data dictionary we see that the index contains 2 branch levels (BLEVEL from the USER_INDEXES view) so that accounts for 2 consistent gets, accessing the index leaf block gives us 1 more consistent get and reading the row in the table gives us another 1, which totals the 4 that autotrace reports.

As might be anticipated, the number of bytes sent to and from the SQL Plus client remains the same, as does the number of rows processed.

The last autotrace example illustrates the impact that the number of rows that the client fetches at a time has on the processing of a query. In this example, we retrieve 1,000 rows from our table. In the first query we retrieve 10 rows at a time and in the second query we take 500 rows at a time.

Our script is:

SET ECHO ON

VARIABLE l_start NUMBER
VARIABLE l_end   NUMBER

EXEC :l_start := 123000
EXEC :l_end   := 123999

SET ARRAYSIZE 10

SET AUTOTRACE TRACEONLY

SELECT *
FROM   test_tab
WHERE  id BETWEEN :l_start AND :l_end
/

SET ARRAYSIZE 500

SELECT *
FROM   test_tab
WHERE  id BETWEEN :l_start AND :l_end
/

SET AUTOTRACE OFF

and the output is:

SQL> SET ECHO ON
SQL>
SQL> VARIABLE l_start NUMBER
SQL> VARIABLE l_end   NUMBER
SQL>
SQL> EXEC :l_start := 123000

PL/SQL procedure successfully completed.

SQL> EXEC :l_end   := 123999

PL/SQL procedure successfully completed.

SQL>
SQL> SET ARRAYSIZE 10
SQL>
SQL> SET AUTOTRACE TRACEONLY
SQL>
SQL> SELECT *
  2  FROM   test_tab
  3  WHERE  id BETWEEN :l_start AND :l_end
  4  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1954378606

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  2500 |   146K|    53   (0)| 00:00:01 |
|*  1 |  FILTER                      |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  2500 |   146K|    53   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_TAB_PK |  4500 |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(:L_START)<=TO_NUMBER(:L_END))
   3 - access("ID">=TO_NUMBER(:L_START) AND "ID"<=TO_NUMBER(:L_END))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        214  consistent gets
          0  physical reads
          0  redo size
      32791  bytes sent via SQL*Net to client
       1609  bytes received via SQL*Net from client
        101  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> SET ARRAYSIZE 500
SQL>
SQL> SELECT *
  2  FROM   test_tab
  3  WHERE  id BETWEEN :l_start AND :l_end
  4  /

1000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1954378606

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  2500 |   146K|    53   (0)| 00:00:01 |
|*  1 |  FILTER                      |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST_TAB    |  2500 |   146K|    53   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TEST_TAB_PK |  4500 |       |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(:L_START)<=TO_NUMBER(:L_END))
   3 - access("ID">=TO_NUMBER(:L_START) AND "ID"<=TO_NUMBER(:L_END))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
      14857  bytes sent via SQL*Net to client
        531  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL>
SQL> SET AUTOTRACE OFF

From the autotrace output we can see that the execution plan is identical for both queries, which is not unexpected as the query is the same. It may also be fairly obvious that the number of network round trips and the amount of data sent to the SQL Plus client is greater with the smaller batch size. We did 101 round trips, totaling 32,791 bytes with a 10 row fetch size as opposed to 3 round trips and 14,857 bytes. The larger number of round trips will be especially noticeable in situations where network latency is a concern.

As a side note; why were there 101 round trips to fetch 1,000 rows when the fetch size was 10 (1000 / 10 = 100, not 101) and 3 round trips with the 500 fetch size? The answer is that after being provided with all the rows the client makes one more request and is told “that’s it”, which gives us the extra 1 round trip.

Autotrace also alerts us to one other aspect that may not be so obvious; the number of consistent gets is greater when the fetch size is 10 than when it is 500; 214 as compared to 19, a difference of 195. The reason for this is that after every batch is fetched the database is required to go back into the data where it left off. These extra visits result in more logical I/O operations since it will visit the same index and table blocks multiple times; nearly 100 extra index and table visits, which gives us the 195 extra consistent gets.

From this last example autotrace has clearly shown the extra overhead involved in having a small fetch size. Since the fetch size is controlled by the client it is worth reviewing with the client application developers that they have sized this appropriately. Of course, an assumption in this example is that the 1,000 rows were actually required. The most efficient strategy is to filter the data inside the database and only retrieve the rows that are actually required by the application.

This concludes our initial look at autotrace. It’s a quick and simple tool for obtaining some highly useful information about the performance of SQL statements. The combination of execution plan, simple statistics and easy accessibility makes it particular attractive for quick investigations when things go wrong or for evaluating different strategies when composing SQL.

Using autotrace – part 1

Database performance is always a hot issue for any application. It seems to me that whenever a user complains about performance, the database is the first place that people point their finger at. In many ways though this attitude is deserved… the performance of the database is often not considered early enough in system design and the impact of an inefficient query can be devastating. The focus of this post is on one of the simplest performance tools Oracle provides; autotrace.

Autotrace provides the database developer with a number of key pieces of information necessary to assess the performance characteristics of a query:

• The query result-set
• The proposed query execution plan
• Execution statistics, including I/O, network details and sorts

It’s important to understand that autotrace can only used for a single SQL statement only, i.e. SELECT, INSERT, UPDATE, DELETE or MERGE. It cannot be used for PL/SQL blocks. For more advanced statistics that can be used with PL/SQL then you may like to look at Tom Kyte’s runstats utility.

Accessing & Enabling Autotrace

A key advantage of autotrace is that it is available to every developer as part of the database and is accessible from within tools that are commonly used, such as Oracle SQL Developer and Quest Toad.

If autotrace hasn’t been configured on your database then you will find the installation script at:

$ORACLE_HOME/sqlplus/admin/plustrce.sql

Oracle SQL Developer

SQL Developer makes autorace easy; simply enter your SQL in the editor and press F6. Alternately, the following button on the toolbar will also work:

Interestingly, SQL Developer displays a lot more statistics than the SQL Plus facility does leading me to conclude it’s simply mimicking the true autotrace facility. Regardless, the key autotrace statistics are present.

Quest TOAD

Autotrace can be activated in TOAD by right-clicking in an editor window and selecting ‘autotrace’ from the context menu. The menu is on the rather long and you’ll find the autotrace option somewhere near the middle. Selecting the option will put a tick next to it and TOAD will then populate the autotrace output window when a query is run. You may need to enable the autotrace output window by right-clicking in the output panel and selecting the autotrace option.

A word of caution about autotrace in TOAD; it seems to only track a single query execution across all editor tabs for a session. If you have multiple editor tabs open and run different queries in them simultaneously then double check the autotrace output matches the query. Perhaps this has been fixed in the later versions of TOAD though…

SQL Plus

Simply issuing the command:

SET AUTOTRACE ON

enables autotrace within SQL Plus. Other variants of the command can be used to restrict the details that are displayed. For example,

SET AUTOTRACE TRACEONLY

suppresses the display of the query result set so that only the execution plan and run time statistics are shown. This can be further limited with:

SET AUTOTRACE TRACEONLY STATISTICS

which only displays the run time statistics only. I find these last options particularly useful for comparing similar statements since it removes the “clutter” of the query output allowing me to focus on the analysis details.

Information Displayed

As previously noted, the key pieces of information displayed by autotrace are:

• The query result-set
• The proposed query execution plan
• Execution statistics, including I/O, network details and sorts

The details that follow are written from the view of autotrace in SQL Plus.

Query Result-set

There’s not much to say about this section; it’s the results of the query. One point to note is that even if the query output is suppressed by using one of the options noted previously the data is still fetched from the database and transferred to the client. So, a query that returns millions of rows is still going to transfer all that data to your client; it just won’t get displayed to the screen.

In a similar vein, all of the data needs to be transferred before autotrace can report on the statistics so it will fetch the entire result-set. You may find that some queries return fast when autotrace is disabled in a GUI tool and take ages with autotrace enabled. This is probably due to the GUI client fetching only the first few rows of the result-set and displaying those immediately rather than waiting until all rows have been fetched before displaying anything.

Proposed Execution Plan

The query execution plan displayed by autotrace is the same that you would get by running:

EXPLAIN PLAN
FOR
<<insert query>>
/

SELECT *
FROM   TABLE(dbms_xplan.display())
/

I don’t intend to discuss execution plans in this post as it’s a rather large subject. The main point to note is that the execution plan is simply the proposed plan and in certain situations the database may choose to execute the query differently. For example, the execution plan may be altered as a result of bind variable peeking.

Execution Statistics

This is the section that I find most valuable. The execution statistics displayed are:

recursive calls	The number of queries the database runs in addition to the query specified. This includes any data dictionary queries when hard parsing the query, e.g. looking up table and column details used in the original query. Queries executed by functions used within the query will also show up as recursive calls. CURSOR expressions will also increase the number of recursive calls.
db block gets	the amount of logical I/O (logical I/O is I/O performed against the buffer cache) that is performed in “current mode”, i.e. the latest version of the block as opposed to “read consistent mode”
consistent gets	the amount of logical I/O that is performed in “read consistent mode”, i.e. as the data appeared at the start of the query execution.
physical reads	The amount of data that was read from storage. This may include data that was read for operations such as sorts that were too large to be performed in memory.
redo size	The amount of redo, in bytes, generated by the query. Generally this will be 0 for normal SELECT queries and have some value for INSERTs, UPDATEs, MERGEs and DELETEs.
bytes sent via SQL*Net to client	The amount of data sent to the client application by the database
bytes received via SQL*Net from client	The amount of data received by the database from the client application
SQL*Net roundtrips to/from client	The number of network communications from client to server and back again.
sorts (memory)	The number of data sort operations that were able to be performed in memory.
sorts (disk)	The number of data sort operations that were sufficiently large enough to require spilling to disk. These will perform more slowly than memory sorts.
rows processed	The number of rows returned by a SELECT query or the number of rows modified by an INSERT, UPDATE, MERGE or DELETE statement.

You’ll note that there is no statistic regarding physical writes. This is because the DB Writer process is responsible for writing modified data blocks to disk, not the session that modified the data. A session may perform physical writes for operations such as disk sorts but this would be only part of the overall I/O picture.

In the next part on this post we explore a couple of queries and look at what autotrace tells us about them…

---

Other autotrace references:
autotrace in the Oracle Database documentation
Tom Kyte has a good write-up of autotrace in his book Effective Oracle by Design