High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability / Edition 1 available in Paperback
- Pub. Date:
"Geoff Ingram has met the challenge of presenting the complex process of managing Oracle performance. This book can support every technical person looking to resolve Oracle8i and Oracle9i performance issues."
-Aki Ratner, President, Precise Software Solutions
Ensuring high-performance and continuous availability of Oracle software is a key focus of database managers. At least a dozen books address the subject of "performance tuning" that is, how to fine-tune the Oracle database for its greatest processing efficiency. Geoff Ingram argues that this approach simply isn't enough. He believes that performance needs to be addressed right from the design stage, and it needs to cover the entire systemnot just the database.
High-Performance Oracle is a hands-on book, loaded with tips and techniques for ensuring that the entire Oracle database system runs efficiently and doesn't break down. Written for Oracle developers and DBAs, and covering both Oracle8i and Oracle9i, the book goes beyond traditional performance-tuning books and covers the key techniques for ensuring 24/7 performance and availability of the complete Oracle system.
The book provides practical solutions for:
• Choosing physical layout for ease of administration and efficient use of space
• Managing indexes, including detecting unused indexes and automating rebuilds
• SQL and system tuning using the powerful new features in Oracle9i Release 2
• Improving SQL performance without modifying code
• Running Oracle Real Application Clusters (RAC) for performance and availability
• Protecting data using Recover Manager (RMAN), and physical and logical standby databases
The companion Web site provides the complete source code for examples in the book, updates on techniques, and additional documentation for optimizing your Oracle system.
|Edition description:||New Edition|
|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 www.dbcool.com.
Read an Excerpt
High-Performance OracleProven Methods for Achieving Optimum Performance and Availability
By Geoff Ingram
John Wiley & SonsISBN: 0-471-22436-7
Chapter OneDesigning 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:
SELECT SUM(PREMIUM_REVAL) FROM TT_FX TT,DT_VALUES DT,SD_LIVE_DEAL_STATES LDS WHERE DT.DEAL_NUM = TT.DEAL_NUM AND TT.TRADING_BOOK = :b2 AND TT.DEAL_STATE = LDS.NAME AND LDS.LIVE = 'Y'
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:
SELECT SUM(DT.PREMIUM_REVAL) FROM TT_FX TT,DT_VALUES DT,SD_LIVE_DEAL_STATES LDS WHERE DT.DEAL_NUM = TT.DEAL_NUM AND TT.TRADING_BOOK = :b2 AND TT.DEAL_STATE = LDS.NAME AND LDS.LIVE = 'Y'
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';
CONSTRAINT_NAME CONSTRAINT_TYPE ----- ----- SYS_C002402 P SYS_C002403 R
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';
CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED ----- ----- ---- PK_EMP P USER NAME SYS_C001898 P GENERATED NAME
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.
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
PART ONE: FUNDAMENTALS OF AN ORACLE CONFIGURATION.
Chapter 1- Installing Oracle.
Chapter 2- Database Creation.
Chapter 3- Configuring Oracle Networking.
Chapter 4- Environment Standards and Tools.
Chapter 5- Securing your Database.
PART TWO: DESIGNING FAST AND SUPPORTABLE APPLICATIONS.
Chapter 6- Designing Supportable Applications.
Chapter 7- Choosing Third Party Software.
PART THREE: PERFORMANCE MANAGEMENT AND TUNING TECHNIQUES.
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.
PART FIVE: HIGH AVAILABILITY SOLUTIONS.
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.