Oracle 11g Performance Tuning is a must-have reference guide for all Oracle professionals. It provides much-needed information on best practices, tips, and techniques in debugging and tuning of the Oracle 11g database. It will bring you the insider information that you can’t get anywhere else. It is a book for people who administer the operation of an Oracle Database system. Referred to as database administrators (DBAs), they are responsible for creating Oracle Database, ensuring its smooth operation, and monitoring its use.
Additional Info
  • Publisher: Laxmi Publications
  • Language: English
  • ISBN : 978-93-83828-31-9
  • Chapter 1

    Getting Started With Oracle Architecture Price 2.99  |  2.99 Rewards Points

    A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. The Oracle Relational Database Management System (RDBMS) reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. This is accomplished while delivering high performance. At the same time, it prevents unauthorized access and provides efficient solutions for failure recovery.
  • Chapter 2

    Designing and Developing for Performance Price 2.99  |  2.99 Rewards Points

    System performance has become increasingly important as computer systems get larger and more complex as the Internet plays a bigger role in business applications. To accommodate this, Oracle has produced a performance methodology based on years of designing and performance experience. This methodology explains clear and simple activities that can dramatically improve system performance.
  • Chapter 3

    Performance Improvement Methods Price 2.99  |  2.99 Rewards Points

    Oracle performance methodology helps you to identify performance problems in an Oracle database. This involves identifying bottlenecks and fixing them. It is recommended that changes be made to a system only after you have confirmed that there is a bottleneck. Performance improvement, by its nature, is iterative. For this reason, removing the first bottleneck might not lead to performance improvement immediately, because another bottleneck might be revealed. Also, in some cases, if serialization points move to a more inefficient sharing mechanism, then performance could degrade. With experience, and by following a rigorous method of bottleneck elimination, applications can be debugged and made scalable.
  • Chapter 4

    Configuring a Database for Performance Price 2.99  |  2.99 Rewards Points

    This section discusses some initial database instance configuration options that have important performance impacts. If you use the Database Configuration Assistant (DBCA) to create a database, then the supplied seed database includes the necessary basic initialization parameters and meets the performance recommendations that are discussed in this chapter.

  • Chapter 5

    Automatic Performance Statistics Price 2.99  |  2.99 Rewards Points

    To effectively diagnose performance problems, statistics must be available. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements. Oracle Database also tracks cumulative statistics on segments and services. When analyzing a performance problem in any of these scopes, you typically look at the change in statistics (delta value) over the period you are interested in. Specifically, you look at the difference between the cumulative value of a statistic at the start of the period and the cumulative value at the end.
  • Chapter 6

    Automatic Performance Diagnostics Price 2.99  |  2.99 Rewards Points

    When problems occur with a system, it is important to perform accurate and timely diagnosis of the problem before making any changes to a system. Oftentimes, a Database Administrator (DBA) simply looks at the symptoms and immediately starts changing the system to fix those symptoms. However, an accurate diagnosis of the actual problem in the initial stage significantly increases the probability of success in resolving the problem. With Oracle Database, the statistical data needed for accurate diagnosis of a problem is stored in the Automatic Workload Repository (AWR).
  • Chapter 7

    Configuring and Using Memory Price 2.99  |  2.99 Rewards Points

    Oracle Database stores information in memory caches and on disk. Memory access is much faster than disk access. Disk access (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more efficient for data requests of frequently accessed objects to be perform by memory, rather than also requiring disk access.
  • Chapter 8

    IO Configuration and Design Price 2.99  |  2.99 Rewards Points

    Every Oracle Database reads or write data on disk, the database generates disk I/O. The performance of many software applications is inherently limited by disk I/O. Applications that spend the majority of CPU time waiting for I/O activity to complete are said to be I/O-bound. Oracle Database is designed so that if an application is well written, its performance should not be limited by I/O. Tuning I/O can enhance the performance of the application if the I/O system is operating at or near capacity and is not able to service the I/O requests within an acceptable time. However, tuning I/O cannot help performance if the application is not I/Obound (for example, when CPU is the limiting factor).
  • Chapter 9

    Managing Operating System Resources Price 2.99  |  2.99 Rewards Points

    Operating system performance issues commonly involve process management, memory management, and scheduling. If you have tuned the Oracle database instance and still need to improve performance, verify your work or try to reduce system time. Ensure that there is enough I/O bandwidth, CPU power, and swap space. Do not expect, however, that further tuning of the operating system will have a significant effect on application performance. Changes in the Oracle Database configuration or in the application are likely to result in a more significant difference in operating system efficiency than simply tuning the operating system.
  • Chapter 10

    Instance Tuning Using Performance Use Price 2.99  |  2.99 Rewards Points

    After the initial configuration of a database, monitoring and tuning an instance regularly is important to eliminate any potential performance bottlenecks. This chapter discusses the tuning process using Oracle V$ performance views. This chapter contains the following sections: • Instance Tuning Steps • Interpreting Oracle Database Statistics • Wait Events Statistics • Real-Time SQL Monitoring • Tuning Instance Recovery Performance: Fast-Start Fault Recovery
  • Chapter 11

    The Query Optimizer Price 2.99  |  2.99 Rewards Points

    The optimizer is built-in software that determines the most efficient way to execute a SQL statement. This section contains the following topics: • Optimizer Operations • Components of the Query Optimizer • Bind Variable Peeking.
  • Chapter 12

    Using Explain Plan Price 2.99  |  2.99 Rewards Points

    The EXPLAIN PLAN statement displays execution plans chosen by the optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement execution plan is the sequence of operations that the database performs to run the statement.
  • Chapter 13

    Managing Optimizer Statistics Price 2.99  |  2.99 Rewards Points

    Optimizer statistics describe details about the database and the objects in the database. The query optimizer uses these statistics to choose the best execution plan for each SQL statement. Optimizer statistics include the following: • Table statistics. o Number of rows o Number of blocks o Average row length. • Column statistics. o Number of distinct values (NDV) in column o Number of nulls in column o Data distribution (histogram) o Extended statistics.
  • Chapter 14

    Using Indexes and Clusters Price 2.99  |  2.99 Rewards Points

    Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
  • Chapter 15

    Using SQL Plan Management Price 2.99  |  2.99 Rewards Points

    SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build a SQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.
  • Chapter 16

    SQL Tuning Overview Price 2.99  |  2.99 Rewards Points

    SQL tuning involves the following basic steps: • Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system. • Verifying that the execution plans produced by the query optimizer for these statements perform reasonably. • Implementing corrective actions to generate better execution plans for poorly performing SQL statements. The previous steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.
  • Chapter 17

    Automatic SQL Tuning Price 2.99  |  2.99 Rewards Points

    Oracle Database uses the optimizer to generate the execution plans for submitted SQL statements. The optimizer operates in the following modes: • Normal mode. The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second. • Tuning mode. The optimizer performs additional analysis to check whether it can further improve the plan produced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.
  • Chapter 18

    SQL Access Advisor Price 2.99  |  2.99 Rewards Points

    Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries. SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. In particular, partitioning of an unpartitioned base table is a complex operation that must be planned carefully.
  • Chapter 19

    Using Optimizer Hints Price 2.99  |  2.99 Rewards Points

    A hint is an instruction to the optimizer. When writing SQL code, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost. In a test or development environments, hints are useful for testing the performance of a specific access path. For example, you may know that a certain index is more selective for certain queries. In this case, you may use hints to instruct the optimizer to use a better execution plan.
  • Chapter 20

    Using Plan Stability Price 2.99  |  2.99 Rewards Points

    Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. Plan stability is most useful when you cannot risk any performance changes in an application.
  • Chapter 21

    Using Application Tracing Tools Price 2.99  |  2.99 Rewards Points

    End to End Application Tracing simplifies the process of diagnosing performance problems in multitier environments. In these environments, a request from an end client is routed to different database sessions by the middle tier, making it difficult to track a client across database sessions. End to End Application Tracing uses a client ID to uniquely trace a specific end-client through all tiers to the database.

About the Author