Top_5_tech_brief.pdf

Five Leading Causes of Oracle Database Performance Problems and How to Solve Them A Buda Consulting Technical Brief

Robert J. Buda President Buda Consulting [email protected]

February 2011

Table of Contents Introduction .................................................................................................................................................................. 1
 Performance Issue #1: Gathering Statistics............................................................................................................... 1
 Methods of Collecting Statistics ................................................................................................................................. 1
 Gathering Statistics Manually.................................................................................................................................... 1
 Copying Statistics ....................................................................................................................................................... 2
 Performance Issue #2: SGA Size................................................................................................................................. 3
 Controlling the Size of the Overall SGA .................................................................................................................... 4
 Controlling Individual Sections of the SGA ............................................................................................................... 4
 Performance Issue #3: Redo Log Size ........................................................................................................................ 4
 Increasing the Size of Log Files ................................................................................................................................. 6
 Performance Issue #4: Index Usage............................................................................................................................ 6
 What Can Prevent the Use of an Index? .................................................................................................................... 8
 Performance Issue #5: Extent Management ............................................................................................................ 10
 Why Reorganize Table Data? .................................................................................................................................. 11
 Manual Reorganization Methods ............................................................................................................................. 12
 Conclusion................................................................................................................................................................... 14
 About Buda Consulting ............................................................................................................................................. 15
 About the Author ...................................................................................................................................................... 15
 Copyright © 2011 by Buda Consulting, Inc. All rights reserved. Printed in USA. Oracle is a registered trademark of Oracle Corporation. All other marks are property of their respective companies. Buda Consulting 957 Route 33 Hamilton Square, NJ 08690 1-888-548-1801

ii

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

Introduction As we evaluate our clients’ Oracle® database systems and help them solve problems relating to performance and stability, we see many causes for these issues. However, there are a small number of basic issues that tend to cause problems on the majority of systems. This paper describes five of the more frequent issues that we see, and discusses how to identify these problems and resolve them.

Performance Issue #1: Gathering Statistics The gathering of statistics can have a significant impact on query performance. The query optimizer’s job is to decide the best way to execute a query. It performs these tasks, among others: •

Choose whether to use an index on a query.

•

Choose which index to use if there is more than one index to choose from.

•

Choose which method to use when executing joins.

The optimizer uses database statistics to make these decisions. To help the optimizer make these decisions, Oracle keeps a number of different types of statistics including: •

The cardinality of the tables (number of rows)

•

Information about the distribution of the data in individual columns

•

Storage characteristics of the data

Frequent and regular statistics gathering will ensure that your optimizer has the best possible data with which to make its decisions. Otherwise performance can be negatively affected. So be sure to keep your statistics up-to-date.

Methods of Collecting Statistics There are two methods of collecting statistics: Automatic and Manual. Oracle will collect statistics automatically if it is configured to do so. Statistics can be gathered automatically by using the job GATHER_STATS_JOB. This job is created automatically when the database is created and is controlled by the scheduler. This job will automatically gather statistics for all objects for which no statistics have been gathered yet, or that have “stale” statistics associated with them; that is, the data has changed by more than 10% since the statistics were gathered. Some database administrators choose to collect statistics manually so that they have more control over when the statistics are collected. If you do not automatically collect statistics, then you must ensure that you collect them whenever the data in the database has changed significantly.

Gathering Statistics Manually The following command is used to gather statistics on the example schema BIG_DATA. The “Gather Auto” option illustrated below is the same option that would be used by the GATHER_STATS job for automatic collection. (There are other options that are beyond the scope of this paper.) Gathering statistics frequently with this method will ensure that your statistics are upto-date. 


Page 1

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

exec dbms_stats.gather_database_stats( options => 'GATHER AUTO', -- include tables with empty or stale stats estimate_percent => dbms_stats.auto_sample_size -- auto determine sample size ) Alternatively, you can gather stats for one particular schema with the following command:

exec dbms_stats.gather_schema_stats( ownname => 'BIG_DATA’, -- collect stats on the BIG_DATA Schema options => 'GATHER AUTO', -- include tables with empty or stale stats estimate_percent => dbms_stats.auto_sample_size -- auto determine sample size ) Other options enable you to collect stats on a specific table or index.

Copying Statistics After statistics have been gathered, they can be copied from one database to another. This is useful if, for example, you want the optimizer to choose execution paths similar to your production paths on databases that may have a smaller data set, such as development or QA databases. This can be done using the following commands:

-- Create a table to hold the statistics exec dbms_stats.create_stat_table (ownname=>’BOB’, stattab=>'PRODUCTION_STATISTICS’); -- Export statistics to the newly created table exec dbms_stats.export_schema_stats (ownname=>’BOB’, stattab=> 'PRODUCTION_STATISTICS'); -- Export the stats table !exp bob/tiger tables='PRODUCTION_STATISTICS' -- Drop the table drop table PRODUCTION_STATISTICS; -- Connect to the target database connect bob/tiger -- Import the stats table !imp bob/tiger fromuser=bob touser=bob -- Import the statistics exec dbms_stats.import_schema_stats (ownname=>’BOB’, stattab=>'PRODUCTION_STATISTICS'); --Drop the stats table drop table 'PRODUCTION_STATISTICS';

Page 2

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

Performance Issue #2: SGA Size Oracle uses two main memory areas to do its work, the system global area (SGA) and the program global area (PGA). This section discusses controlling the size of the SGA. A description of the PGA is also listed for completeness. If the SGA is not already configured to take advantage of all available memory on the server, then increasing the size can significantly improve performance. Carefully evaluate the available memory, however. Consider total memory on the system and the memory requirements of other applications on the system. As a general rule, I configure Oracle to use about 80% of the remaining memory on Windows after the needs of other applications. You can see the current size of the SGA by issuing the “show sga” command at the SQL command line or by selecting from the v$sga system view. This gives a high-level summary of the SGA size. The SGA is a set of buffers that Oracle uses to cache data. It is comprised of the following areas:

Area

Initialization Parameter

Purpose

Overall SGA

sga_target

Overall SGA

sga_max_size

Setting this to a nonzero value turns on the automatic shared memory management feature and sets the target value. Limits the max size of the SGA when you set the individual components manually.

Default Buffer Cache

db_cache_size

Recycle_Buffer_Cache

db_recycle_cache_size

Keep_Buffer_Cache

db_keep_cache_size

Block Size Caches

db_8k_cache_size db_16k_cache_size

Shared Pool

Shared_Pool_Size

Large Pool Java Pool Redo_Log_Buffer

Large_Pool_Size

Streams Pool

Streams_Pool_Size

Java_Pool_Size Log_Buffer_Size

Table 1: SGA Buffers

Page 3

All cached data that is not directed to one of the other buffer caches is stored here. Optional area used for large tables that should not be kept in memory because queries are unlikely to need the same data within a short period of time. Optional area used for data that will be frequently used and that you don’t want to be moved out of the cache to make room for other data. Up to five optional caches for specific block sizes for tablespaces with blocksizes different from the default. Contains optimized query plans, parsed SQL statements, packages, and other items that can be shared across sessions. Optional area used by RMAN. Contains all session-specific Java Code. Circular buffer that holds information about changes made to the database. Streams memory area.

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

The PGA is a set of buffers that is allocated for operations performed by user processes. This area is comprised of:

Area

Initialization Parameter

Purpose

Sort Area Hash Area

Sort_Area_Size

Used for sorting. Used for merging and other operations.

Hash_Area_Size

Table 2: PGA Buffers

Controlling the Size of the Overall SGA In Oracle 10g and 11i, the SGA size can either be controlled automatically, using automatic segment space management (ASSM), as described below, or manually by setting the size of the individual components. Two parameters control the overall size of the SGA. •

SGA_TARGET Setting this to a nonzero value turns on the automatic shared memory management feature and sets the target value for the entire SGA. If this is set, you can still set sizes of individual components and the system will set the remaining parameters automatically up to the SGA_MAX_SIZE.

•

SGA_MAX_SIZE Limits the max size of the SGA when you set the individual components manually.

Controlling Individual Sections of the SGA In addition to these two parameters, which control the size of the overall SGA, the initialization parameters listed in Table 1 above can be used to control the size of the respective SGA area. Tuning the SGA can be a complicated task, and may require looking at all of the SGA areas to determine if they are large enough. But as an initial step, if you have large amounts of unused memory on your server, consider one of the two options: Increasing the size of either the Default Buffer Cache or the individual Block Size Caches, if you use them. This will enable Oracle to keep more data in memory and is likely to reduce the number of physical reads it needs to perform. Turning on ASSM by Setting the SGA_TARGET to the amount of memory that you would like Oracle to use. Note: If you choose to use ASSM, I suggest setting the individual SGA component areas to a reasonable number. This becomes a minimum setting for these areas. Some customers using ASSM have reported that when the individual components are not set, performance suffers significantly because the automatic resizing can result at times in a very small db_cache_size or other too-small minimum settings.

Performance Issue #3: Redo Log Size In systems with very high insertion or update rates, online redo log activity is a critical factor to examine. If the logs are too small, frequent log switches can cause significant delays in processing inserts or updates. Redo log files are typically configured in groups of three files. Each time one of the files fills up, Oracle will do a log switch and then begin using the next one in sequence. If archive logging is

Page 4

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

turned on, the prior redo log is then archived to another location. This process continues until the third log is filled, and then the first log is reused, as illustrated in Figure 1.

Figure 1: How Oracle’s log switching works Source: Oracle Database Administrator's Guide, 10g Release 2 (10.2), Part Number B14231-02

When logs are undersized for the insertion or update rates, logs switch frequently. This results in numerous problems. Here are three of the most typical: 1. Each of the log switches takes system resources, and so constitutes a general drag on the system. Log switches can be seen by viewing the v$loghist system view.

Page 5

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

2. Very frequent switches can cause a very high number of log sync waits. These waits delay the insertion of data. Log sync waits can be seen in an automatic workload repository (AWR) report.

3. If an online log file cannot be archived by the time the system wants to use that file again, no inserts or updates will take place until that file is successfully archived. By increasing the size of the redo log, you can decrease the time between log switches. As a general rule, you should try to log switch no more frequently than every 20 minutes.

Increasing the Size of Log Files You cannot change the size of existing log files. In order to use larger log files, you must add a new set of larger files and then drop the older files. This can be done as described below. You can view information about your logfiles by using the following views: V$log

Displays redo log information.

V$logfile

Displays information about groups, members, and the corresponding physical files.

V$loghist

Displays information about prior use of the logfiles, including log switches.

Add new logfiles, and drop old ones, using the following commands: Alter database add logfile

To add the new, larger logfiles

Alter system switch logfile

To ensure that the old logs are no longer used

Alter database drop logfile

To drop the older logfiles

After increasing the size of your logs, check v$loghist after a few hours of production usage to see if the time between log switches has been increased to more than twenty minutes. If it has not, repeat the above process until you have at least twenty minutes between switches.

Performance Issue #4: Index Usage A very common cause of performance problems is lack of proper indexes or the use of queries that are not using existing indexes. If a query is performing a full table scan on a large table, it is very likely that an index on one or more fields in the table will improve query performance, sometimes significantly.

Page 6

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

To determine whether a query is using an index, you can view the execution plan of the query. To do this, use the “explain plan” command as demonstrated in the following example. For this example, we will use the warehouse_inventory table: -- create the table that we will use for our example Create table warehouse_inventory ( warehouse_id number, warehouse_name varchar2(30), product_id number , qty number );
 -- we will use the following query for our example: select * from warehouse_inventory where warehouse_name= 'AAA'; -- Issue the explain plan command at the sqlplus prompt: explain plan for select * from warehouse_inventory where warehouse_name= 'AAA';

The above command stores the resulting execution plan in the plan table. Note that this form of the “explain plan” command does not give a name to the stored execution plan. Therefore you must delete all rows from the plan table before issuing another “explain plan” command. To view the formatted results of the plan, use the following query: substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; -- Results look like this: SELECT STATEMENT () TABLE ACCESS (FULL)

WAREHOUSE_INVENTORY

Note the line that says TABLE ACCESS (FULL), followed by a table name. This indicates that this query is not using any indexes for the referenced table. After creating an index on the warehouse_name field, the results of the execution plan will reflect the use of the index. -- create an index on the warehouse_name field create index warehouse_name1_ind on warehouse_inventory(warehouse_name); -- clear the plan table delete plan_table; -- execute the explain plan again explain plan for select * from warehouse_inventory where warehouse_name='AAA’ -- Query the plan table select

Page 7

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id;

Here are the new results: Operation -----------------------------SELECT STATEMENT () TABLE ACCESS (BY INDEX ROWID) INDEX (RANGE SCAN)

Object -----------------------------WAREHOUSE_INVENTORY WAREHOUSE_NAME1_IND

Note how the newly created index is now used in the execution plan.

What Can Prevent the Use of an Index? There are various causes that could prevent a query from using an index that is available for use. •

Invalid/stale statistics. As mentioned above, the optimizer uses statistics to determine when an index on a given column or group of columns should be used. If statistics are very stale, the optimizer may choose to perform a full table scan even though there is an index available. This can happen, for example, if statistics were gathered when the table had very few rows and many rows have been added since the statistics were gathered.

•

Use of a function on an indexed column. If a function is applied to a column in the WHERE clause of a query, no index applied directly to that column can be used. This is because the original value is indexed, not the result of the function call. For example, recreating the table that we used earlier: -- Drop and recreate the table Drop table warehouse_inventory; Create table warehouse_inventory ( warehouse_id number, warehouse_name varchar2(30), product_id number , qty number ); -- Create an index on the name field Create index wh2_ind on warehouse_inventory(warehouse_name); -- we wish to issue the following query Select product_id,sum(qty) from warehouse_inventory where upper(warehouse_name) = 'NORTH_WAREHOUSE' group by product_id -- issue the explain plan explain plan for Select product_id,sum(qty) from warehouse_inventory where upper(warehouse_name) = 'NORTH_WAREHOUSE' group by product_id

Page 8

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief -- Query the plan table select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; -- the results of the explain plan SELECT STATEMENT () HASH (GROUP BY) TABLE ACCESS (FULL) WAREHOUSE_INVENTORY

Even though the warehouse_name column has an index, this index cannot be used because the indexed value may not be the same as the result of the upper function. However, if you must use a function in the query as above, you can create a function–based index on the function that you are using. For example, to create an index on the function in the above example: -- create the function based index CREATE INDEX Upper_Warehouse_Name_ind ON warehouse_inventory (Upper(warehouse_name)); --clear the plan_table Delete plan_table; -- issue the explain plan explain plan for Select product_id,sum(qty) from warehouse_inventory where upper(warehouse_name) = 'NORTH_WAREHOUSE' group by product_id -- Query the plan table select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation", object_name "Object" from plan_table start with id = 0 connect by prior id=parent_id; -- the results of the explain plan SELECT STATEMENT () HASH (GROUP BY) TABLE ACCESS (BY INDEX ROWID WAREHOUSE_INVENTORY INDEX (RANGE SCAN) UPPER_WAREHOUSE_NAME_IND

The function-based index is now used in the query plan.

Page 9

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

•

Use of a non-leading column in an index. Another reason that an index may be ignored is when the leading column of a multi-column index is not used in the query. Revising the indexes on our warehouse_inventory table gives us: Create table warehouse_inventory ( warehouse_id number, warehouse_name varchar2(30), product_id number , qty number ); Create index wh1_ind on warehouse_inventory(warehouse_id,warehouse_name); Select product_id,sum(qty) from warehouse_inventory where upper(warehouse_name) = 'NORTH_WAREHOUSE' group by product_id;

Even though warehouse_name is in the wh1_ind index, it will not be used here because we are not also using the warehouse_id, which is the leading column in the index, in the WHERE clause.

Performance Issue #5: Extent Management To understand this topic, some background on storage structure is helpful. Table and index data is stored in segments. Segments are composed of extents, which are in turn composed of database blocks. Optimally, each row of a table is stored completely in one database block (see Figure 2). Each table and each index has one segment, unless it is partitioned, in which case it has one segment per partition.

Page 10

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

Figure 2: Storage structure Source: Oracle9i Database Concepts, Release 2 (i9.2)d, Part Number A96524-01

When a table or index is first created, the extents within the segment are on contiguous areas on the disk. This means there is no empty space between them.

Why Reorganize Table Data? As table data gets updated and deleted, the storage of table data becomes less efficient. The following conditions reduce the efficiency of the table storage: •

When rows are deleted, empty space remains in the blocks where the rows once were. This space is not available for data unrelated to this table until the table is reorganized. Also, the space freed within a block due to deletes may not be contiguous.

•

Similar to the deletion of a row, when a column value is updated with a smaller value, empty space is left in the block.

•

If a row is updated and there is not enough free space to fit the entire record in its original data block, then Oracle performs row chaining, which can place part of the row in a separate block.

•

If a row is updated and there is not enough room to fit the entire record in the block, but there is room to fit the record in another block, then Oracle performs row migration. When performing row migration, Oracle places a pointer to the row in the original block and relocates the entire row to another block.

Page 11

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

In addition to the impact that these conditions have on query performance, they waste disk space. For these reasons, you need to periodically reorganize database tables in order to ensure that the storage is efficient. Reorganizing will eliminate the empty space, thus freeing up the space for use by other tables. It will also eliminate chaining and migration of rows. Oracle also sometimes coalesces space automatically. As mentioned above, there can be multiple non-contiguous free areas inside a database block. When this occurs, there are times that Oracle will automatically coalesce those areas into one large area. According to the Oracle documentation, this will occur only when the following occurs: •

An INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece,

•

The free space is fragmented so the row piece cannot be inserted in a contiguous section of the block.

Manual Reorganization Methods There are multiple methods available to manually reorganize a table. These include: •

Online Segment Shrink

•

Online Table Redefinition

•

CTAS (create table as select * from…)

•

Export/Import

•

Flat File/Sql loader

Online segment shrink is the best choice when it is available. This option is only available when locally managed tablespaces are used with ASSM. You can use the Oracle Segment Advisor to identify objects that can benefit from reorganization. This method does not require extra disk space and the object can remain online for queries, inserts, updates, and deletes during most of the operation. Using the Segment Advisor is non-trivial. There are multiple ways to do this but unless you have Oracle Enterprise Manager available, the quickest and easiest is to run Segment Advisor manually and view the results manually. Enterprise Manager is a very useful tool, but many smaller organizations do not have this installed because it can be difficult to install and configure. In a future Tech Brief, I will discuss the configuration of Enterprise Manager. To run the Segment Advisor manually, you must perform the following steps using the dbms_advisor package: 1. Create an advisor task. 2. Create an advisor object (which you will be analyzing). 3. Set task parameters. 4. Execute the task. Detailed instructions for doing this can be found in the section “Running the Segment Advisor Manually with PL/SQL” in the Oracle documentation.

Page 12

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

After the Segment Advisor is executed, there are numerous ways to view the results. The simplest way to do this is to use the DBA_ADVISOR views. The following examples from the Oracle documentation demonstrate each step of this process. First, create, configure, and execute the task. In this example, we are examining the Employees table in the HR schema.

After running this procedure, view the status of the task:

When the task is completed, view the results:

Page 13

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

After reviewing the results and determining that reorganization is necessary, you can issue the online “shrink” command. The “cascade” option indicates that index segments associated with the table should be shrunk as well.

Online table redefinition can be used if you need to keep the table online but are not using ASSM. This method requires extra disk space but, like online segment shrink, the table remains available for use during the operation. The online table redefinition process is complex and is beyond the scope of this paper. It is only a good option for this purpose if the table must remain online. If the table can be taken offline during the operation, then the following simpler options are available. 1. Using “Create table as select * from ...”, truncating the table, and then re-inserting from the copy. 2. Using export/import to dump the table out to an external file and then bring it back in. 3. Unload the data to a flat file and use the SQL Loader utility to bring it back in. The best method to choose among these options depends on the size of the table involved and other factors. We will explore this further in a future Tech Brief.

Conclusion The five topics discussed in this paper are frequently the cause of performance problems in an Oracle database. Resolving these issues before they cause problems can help keep your applications running smoothly and trouble-free.

Page 14

Five Leading Causes of Oracle Performance Problems and How to Solve Them: A Buda Consulting Technical Brief

About Buda Consulting For over twenty years, Buda Consulting has provided database consulting services to businesses that need to harness the potential of their information assets. Specializing in relational database design, application development, database administration and infrastructure support, Buda Consulting solves its clients’ business problems quickly through proven expertise in the latest database management systems, including Oracle, SQL Server and MySQL. Based in Central New Jersey, Buda Consulting serves companies of all sizes throughout the United States. Our clients include Johnson & Johnson, Wachovia, Johns Hopkins University, City University of New York, Aventis Pharmaceutical and Verizon Wireless, as well as city governments, medical offices and other small to midsized businesses.

About the Author Robert J. Buda, founder and President of Buda Consulting, has over twenty years experience in the information technology field. He is an expert in the design and development of database systems and is certified in Oracle Database Administration.

Page 15