Pub. Date:
High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability / Edition 1

High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability / Edition 1

by Geoff Ingram, Ingram


Current price is , Original price is $45.0. You

Temporarily Out of Stock Online

Please check back later for updated availability.

This item is available online through Marketplace sellers.

Product Details

ISBN-13: 9780471224365
Publisher: Wiley
Publication date: 09/03/2002
Edition description: New Edition
Pages: 720
Product dimensions: 9.25(w) x 7.50(h) x 1.47(d)

About the Author

GEOFF INGRAM has spent his entire career in the IT industry, including several years working as a product developer at Oracle Corporation. Since leaving Oracle, he has operated as a freelance consultant for blue chip companies in the banking and telecommunication sectors. He focuses on delivering Oracle systems that meet end-user performance and availability requirements, and has developed a free software application for fine-tuning Oracle applications, available from

Read an Excerpt

High-Performance Oracle

Proven Methods for Achieving Optimum Performance and Availability
By Geoff Ingram

John Wiley & Sons

ISBN: 0-471-22436-7

Chapter One

Designing Supportable Applications

What exactly does making an application supportable mean? It means that when a running application encounters a problem, the exact location in the code can be located immediately, and the root cause identified as quickly as possible. It means that an application reports back on its status in a format that can be easily assimilated by support staff and automated monitoring processes. It means that an application needs to be written in a way that is robust against various types of failures and takes advantage of available features to mitigate the effects of those failures. Many factors influence supportability, and this chapter covers the following topics to address them:

* Tips for supportable SQL

* How to provide tracing facilities

* How to enable error reporting and logging

* Run-time application configuration

* The importance of restartability

* How to use resumable operations in Oracle9i

This chapter is intended for both the database administrator (DBA) and developer. If you're a developer, consider implementing the suggestions to aid supportability. Supportability translates directly to increased availability through reductions in outages and faster problem resolution. If you're a DBA, then you can put forward theinformation in this chapter as a blueprint for the developers in your organization, with a goal of reducing support costs.

Creating Supportable SQL

This section contains four simple tips for SQL layout and naming that are frequently missing from Oracle code yet can provide significant benefits to supportability with minimal effort.

SQL Layout for Readability

Professional DBAs can spend a significant amount of time inspecting resource-intensive SQL statements and investigating ways to improve them in order to improve application response times for end users. It might surprise developers how much this process can be expedited if SQL is written in a way that makes the SELECT list columns, tables in the FROM clause, and WHERE predicates clear in the statement. This is easily seen with an example. Consider this free formatted SQL statement:

SELECT Deal_Type, Deal_Num, Thin_Pack FROM TT_FX_OTC d WHERE (((DEAL_STATE not in ('DLTD', 'MTRD', 'EXCD','ABND') or EOD_REALISED_PREMIUM <> 0.0 or EOD_REALISED_PREMIUM_REVERSED <> 0.0) and ALLOCATION_STATUS<>'ALLOC') or (DEAL_STATE in ('DLTD', 'MTRD','MTDL') and d.DEAL_NUM in (select dt_vals.DEAL_NUM from DT_VALUES dt_vals where dt_vals.DEAL_NUM = d.DEAL_NUM and dt_vals.PL_INC_SUR <> 0.0))) and DEAL_ROLE <> 'BACK'

A DBA attempting to make sense of this SQL has a real challenge on his hands. As a contrast, consider the same SQL formatted for readability:

SELECT Deal_Type, Deal_Num, Thin_Pack FROM TT_FX_OTC d WHERE ( ( (DEAL_STATE not in ('DLTD', 'MTRD', 'EXCD','ABND') or EOD_REALISED_PREMIUM <> 0.0 or EOD_REALISED_PREMIUM_REVERSED <> 0.0 ) and ALLOCATION_STATUS<>'ALLOC' ) or (DEAL_STATE in ('DLTD', 'MTRD','MTDL') and d.DEAL_NUM in (select dt_vals.DEAL_NUM from DT_VALUES dt_vals where dt_vals.DEAL_NUM = d.DEAL_NUM and dt_vals.PL_INC_SUR <> 0.0 ) ) ) and DEAL_ROLE <> 'BACK'

The reformatted version shows the tables involved clearly and more importantly shows that the query result set depends on two OR clauses, where the second has a dependency on another table. The structure of the query often relates directly to the appearance of the query explain plan. The more closely the two match, the easier it is to identify the part of the query on which to concentrate tuning efforts. The query explain plan for the previous query is given in Figure 6.1 and shows that the first part of the query requires a full table scan of the TT_FX_OTC table, identified by the exclamation mark. Tuning efforts could therefore concentrate on that part. Using the unformatted statement, the relationship between the query plan and the SQL is not evident.

Most developers would never consider laying out code-be it Java, C, or PL/SQL-in an unformatted way. The same rule should apply to SQL statements.

Use Table Aliases

Another simple SQL fix that can make tuning efforts easier is to always use table aliases in SQL statements, in order to make explicit the table from which a SELECT list column originates. Identification of the underlying table for each SELECT list column is required during SQL tuning in order to check whether appropriate indexes on the table are being used. The process can be appreciably slower when a query contains one or more joins, and columns in the SELECT list don't identify the table in the join. The following SQL contains a SELECT list column that could originate from any of three underlying tables:


In this case, a simple change to the SELECT list to include the table alias, DT, means the DBA no longer needs to query the Oracle dictionary to identify the underlying table, as shown:


When used together with the previous tip on layout, the speed with which DBAs can analyze queries can be increased significantly, even for the simple examples shown. The gains are much higher for longer and more complicated SQL.

Use Explicit Constraint Names

Explicit names should be used for Oracle constraints in DDL statements, rather than allowing Oracle to generate them. Oracle-generated names always begin with the prefix SYS_C. Constraint names are used in error messages generated by Oracle when constraints are violated. The more meaningful the name, the quicker the DBA can identify the cause of the underlying problem. The following example shows the Oracle-generated constraint names for a primary key and foreign key on the EMP table:

create table emp (empno number(4) primary key, ename varchar2(10), deptno number(2) references dept);

select constraint_name, constraint_type from user_constraints where table_name='EMP';


The existence of system-generated constraint names can be avoided by explicit naming of the constraints. The previous example can be rewritten using the following SQL:

create table emp (empno number(4) constraint pk_emp primary key, ename varchar2(10), job varchar2(9) deptno number(2) constraint fk_deptno references dept);

Using explicit names has an extra benefit when the DBA needs to compare schema objects during schema upgrade procedures, such as using Oracle Change Manager. If you allow Oracle to choose the names, the chances are that a constraint with the same purpose will have different names in different databases. Choosing explicit names avoids that possibility and makes change management less complicated. Reduction in complexity for any process generally leads to higher availability.

In Oracle9i, the data dictionary views that display constraint information include an extra column named GENERATED to make it easy to identify constraints that use system-generated (as opposed to user-generated) names, as shown in the following example:

select constraint_name, constraint_type, generated from all_constraints where table_name like 'EMP%' and constraint_type='P';


Use Meaningful Object Names

A consistent naming scheme for objects helps the DBA to identify the types of objects used in SQL statements more quickly by enabling the types to be identified from the name. For example, many development teams use a V_ prefix or _V suffix to identify views, an SP_ prefix to identify stored procedures, and _SEQ to identify sequences. The use of IX prefixes or suffixes for indexes also helps to make sense of explain plans.

The ability to directly identify the underlying objects in SQL speeds up the tuning process. Proponents of naming standards fall into two camps, those who use prefixes and those who prefer suffixes. Prefixes are easier to identify in SQL because they appear on the front of names, whereas suffixes make for easier identification of groups of related objects by enabling the use of a wildcard on the end of the base object name during queries of the Oracle data dictionary tables. The exact details of the standard are not as important as having one and adhering to it at a company level.

Trace Facilities

All applications-whether interactive graphical user interface (GUI) or batch-should provide built-in features for enabling and disabling Oracle SQL trace, including standard SQL tracing, tracing with bind variables, and tracing with event waits.

NOTE SQL tracing is used for the performance profiling of SQL statements submitted to the database server and is covered in more detail in Chapters 9 and 28.

The options can be set using the SET_EV procedure, as shown in the following examples for a session identified by SID=8 and SERIAL=149:

REM identical to ALTER SESSION SET SQL_TRACE TRUE, level 1 begin SYS.DBMS_SYSTEM.SET_EV(SI=>8,SE=>149,EV=>10046,LE=>1,NM=>");end;

REM trace SQL with bind variables, level 5 begin SYS.DBMS_SYSTEM.SET_EV(SI=>8,SE=>149,EV=>10046,LE=>5,NM=>");end; REM trace SQL with event waits, level 9 begin SYS.DBMS_SYSTEM.SET_EV(SI=>8,SE=>149,EV=>10046,LE=>9,NM=>");end;

REM trace SQL with bind variables, event waits, level 13 begin SYS.DBMS_SYSTEM.SET_EV(SI=>8,SE=>149,EV=>10046,LE=>13,NM=>");end;

REM trace off for one session, level 0 begin SYS.DBMS_SYSTEM.SET_EV(SI=>8,SE=>149,EV=>10046,LE=>0,NM=>");end;

Although the DBA can set SQL trace for any session, it's better for developers to provide facilities to set the trace within applications themselves, as this provides finer granularity over the traced sections of code. For example, it's possible to create a mapping table of procedure names and trace levels in a table, and have the procedure read and set the trace settings at the top of the procedure, and unset them at the end. That enables tracing to be turned on and off for individual procedures. In general, it's better to concentrate tracing efforts on the smallest code section possible because tracing can generate massive amounts of trace information in a short time. In the case of batch applications, tracing may need to be turned on at the start of processing, in which case the DBA will not be able to allow tracing early enough during execution by calling SET_EV from a separate session. Command-line utilities should enable tracing to be set through command-line arguments.

The ability to trace the values of bind variables and values is especially important when diagnosing the causes of obscure Oracle error messages in PL/SQL code, especially triggers. It's surprising how often code fails with incorrect values that, according to the developer, couldn't possibly be passed into subroutines. By building extensive tracing facilities into an application, the causes of such problems can be definitively identified more quickly. The inclusion of tracing facilities in code adds an overhead to the software development process. It usually pays off quickly. Chapter 28 shows more examples.

It's necessary for the application code to have access to the System ID (SID) and SERIAL# values that identify the current session in order to pass the values to the SET_EV procedure parameters SI and SE. One way to facilitate that is for the DBA to provide a wrapper around the SET_EV procedure that has the relevant privileges required to access the session settings. Chapter 25 on auditing shows three different ways for identifying the SID and SERIAL# for the current session.

Error Reporting and Logging

All application error messages should provide sufficient information to identify unambiguously the exact location at which an error occurred in code and the cause. Too often, applications use a single error number as a cover-all for several possible causes, and this makes root cause diagnosis more difficult than it needs to be for support staff. Oracle itself has been guilty of this. If you've ever reported an error message to Oracle worldwide support (WWS) and it has taken a long time to identify the root cause, that's probably because the developer of the underlying code could have provided a more specific cause for the error but chose not to in order to get the code completed quicker. Error-handling code is tedious for the developer to implement, but that's a poor excuse for not implementing in a way that can minimize support requirements. If error handling is not complete, then the onus is on the customer and WWS to try and work out which of the range of possible causes is the real one. In such cases, most of the effort to resolve the problem needs to be made by the customer.

For the developer, incomplete error handling makes application delivery slightly quicker, but it's a completely false economy from a business point of view. For example, an extra couple of minutes spent by a developer adding code to identify the location of an error and to specify the exact cause can translate into savings in terms of hours when an error manifests itself in the code at run time. It's not necessary to report locations in a way that is meaningful to users but to report information in a way that is meaningful to support. The following is a PL/SQL code fragment showing the use of a simple numeric variable whereami and string, the_location, that can be used to identify the precise code location of errors in error messages:

... the_location:='update_procedure'; whereami := 6; cursor_name := dbms_sql.open_cursor; whereami := 7; dbms_sql.parse(cursor_name,update_sql,dbms_sql.v7); whereami := 8; ret := dbms_sql.execute(cursor_name); whereami := 9; dbms_sql.close_cursor(cursor_name); whereami := 10; cursor_name := dbms_sql.open_cursor; whereami := 11; dbms_sql.parse(cursor_name,update_last_check_sql,dbms_sql.v7); whereami := 12; ret := dbms_sql.execute(cursor_name); whereami := 13; dbms_sql.c


Excerpted from High-Performance Oracle by Geoff Ingram Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.

Table of Contents





Chapter 1- Installing Oracle.

Chapter 2- Database Creation.

Chapter 3- Configuring Oracle Networking.

Chapter 4- Environment Standards and Tools.

Chapter 5- Securing your Database.


Chapter 6- Designing Supportable Applications.

Chapter 7- Choosing Third Party Software.


Chapter 8- End-to-End Performance Management.

Chapter 9- Fundamentals of SQL Tuning.

Chapter 10- Collecting and Using Optimizer Statistics.

Chapter 11- Partitioning.

Chapter 12- Managing Indexes.

Chapter 13- Managing Space Growth.

Chapter 14- Stress Testing and Benchmarks.

Chapter 15- Server Consolidation and Resource Management.

Chapter 16- Selecting and Using Performance Management Tools.

PART FOUR: Backup, Restore, and Recovery.

Chapter 17- Fundamentals of Oracle Recovery.

Chapter 18- Backup and Recovery Using RMAN.

Chapter 19- Backup and Restore Using Export and Import.


Chapter 20- VERITAS High Availability for Oracle.

Chapter 21- Oracle Replication.

Chapter 22- Oracle Real Application Clusters.

Chapter 23- Protecting Data Using Standby Databases.

PART SIX: Maintaining the Oracle System.

Chapter 24- Guidelines for Healthchecks and Monitoring.

Chapter 25- Auditing Techniques.

Chapter 26- Migration and Upgrade.

Chapter 27- Working Effectively with Oracle Support.

Chapter 28- Troubleshooting Oracle DBMS Problems.


Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews