The purpose of this chapter is to address performance issues related to Personal Oracle and, more importantly, to an upsized implementation of a Personal Oracle database prototype. The material in this chapter is certainly not exhaustive. My intent is to provide some guidelines on performance improvement. Here are some general suggestions:
Keep these guidelines in mind as you read about the performance characteristics that occur in an Oracle client/server environment.
Users are the best judges of the performance of an information system. Users measure performance in terms of response time, report turnaround time, and data transfer speed. A very inefficient system may be perceived by users as providing excellent turnaround time. Conversely, a group of users might consider the performance of a very efficient information system to be inadequate. Ignore the perception of the users at your peril.
Suppose you have to analyze a system that is the object of complaints about lackluster performance. You will need to examine four elements of a client/server architecture:
Your goal in performance tuning should be to make the biggest immediate improvement with the least amount of disruption to existing software, hardware, and procedures. The following sections look at each element in some detail.
The network used in a client/server architecture may be a LAN, WAN, or a combination of both. Seek the help of a networking expert to determine the saturation level of the network. Characterize the network demands of a single client. If the network appears to be the performance bottleneck, investigate the client application software to discover whether you can reduce the number of SQL requests to the server. Changing the client application software has a huge impact; this strategy should be your last resort!
If you're just beginning to design an application, you can reduce the network traffic by identifying functionality that can be implemented with stored procedures and functions in the database. (Refer to Chapter 19, "Creating and Using Stored Procedures, Functions, and Packages.")
Oracle performance-tuning efforts can be classified in three ways:
Your best bet is to focus on tuning efforts that have a minimal effect on the existing application.
Among its other uses, the SGA functions as Oracle's cache. If you increase the number of data block buffers in the SGA, you increase the probability that a SQL statement will find the block that it needs in memory--;thereby reducing the number of disk reads it needs to locate a block. However, be sure that the SGA isn't so large that it's swapped out of memory by the operating system.
Personal Oracle7 stores its initialization parameters in C:\Orawin95\Database\Initorcl.ora. One of these parameters specifies the number of database buffers. To increase the number of database buffers, simply edit the appropriate parameter--;in this case, db_block_buffers. For example, if you want to increase the number of database buffers to 400, change
db_block_buffers = 200
to
db_block_buffers = 400
The new value for an initialization parameter will not take effect until the database is started again.
To increase the number of database buffers for Personal Oracle7 for Windows 3.11, invoke Database Manager by double-clicking its icon. Click the Configure button. On the Configure Initialization Parameters screen, the Block Buffers parameter is set to 400. (See Figure 32.1.)
Change the value of Block Buffers from 400 to 450. (See Figure 32.2.) Click the OK button.
Database Manager informs you that the change will not take effect until the next time the database is started. (See Figure 32.3.)
If you wish, you can then use Database Manager to shut down and then start up the Personal Oracle database. You can leave the value of Block Buffers at 450 or set it to its original value of 400.
If your Oracle server is supporting many users and the server process count is very high, consider switching from SQL*Net v1 to SQL*Net v2. By switching to SQL*Net v2, you'll be able to use Oracle's MultiThreaded Server, which enables several remote users to share the same server process. The net result is a reduction in the number of server processes and a reduction in server CPU utilization. Also, network usage is slightly more efficient in SQL*Net v2 than it is in SQL*Net v1.
If you're already using SQL*Net v2 and the MultiThreaded Server, experiment with the number of dispatchers and the number of remote connections that can share a single server process.
In Chapter 26, "Managing Space," I explained how Oracle manages the storage of tables and indexes. As you recall, when a table is created, Oracle allocates an initial extent for the table. As more rows are inserted into the table and the free space in the initial extent is exhausted, Oracle allocates another extent for the table. Index storage works in the same way. The extents may be dispersed across a datafile--;or even across datafiles. As the number of extents increases, the following problems can occur:
To determine the level of fragmentation of a table, query the USER_EXTENTS data dictionary view. For instance, if you wanted to see the number of extents allocated to My_Table, you would issue the following query:
SQL> select bytes
2 from user_extents
3 where
4 segment_name = 'MY_TABLE';
BYTES
---------
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
10240
16 rows selected.
As you can see, the value of PCTINCREASE for My_Table is 0 because the size of each extent is the same. To store all of My_Table's rows in its initial extent, you have the following choices:
Ideally, the contents of every table and index should be contained within its initial extent. This arrangement minimizes the number of disk reads needed to retrieve the rows to satisfy a query.
To achieve acceptable performance on the client machine, the design of the client application should invoke Oracle stored procedures and functions where appropriate. Very few performance enhancements can be applied to a poorly designed application; performance must be designed into the application. Even with a good design, the client platform must have adequate RAM and disk space to support the application.
The performance of the server and the Oracle database are tightly coupled. For instance, if the Oracle database processes many queries with each query returning a large number of rows, the server will be handling many disk I/O requests. To improve server performance, focus on tuning disk I/O and CPU utilization.
To tune disk I/O, you must first determine if disk I/O requests are balanced across all the server's disk drives. Use operating system commands and utilities to identify the average number of I/O requests serviced by each disk drive. Your objectives should include the following:
The server's CPU utilization depends a lot on the use of the database that the server is supporting. If the database is processing many small transactions, CPU utilization might be very high. If this situation is true, here are options to ponder:
A significant portion of database activity consists of SELECT statements. Accordingly, improving query performance results in better overall application performance. A query generally is processed more quickly if it uses an index to access the qualified rows. A full table scan is a query in which all of a table's rows are read to find the qualified rows. To determine whether a query performs a full table scan, you must obtain the query's execution plan. When an execution plan indicates that a full table scan is being performed, consider creating an index that the query can use.
However, be alert to the possibility of overindexing a table. Remember that Oracle automatically maintains a table's indexes whenever the contents of the table change because of an INSERT, UPDATE, or DELETE statement. Your goal should be to optimize the most frequent queries without forcing Oracle to maintain an inordinate number of indexes.
Oracle furnishes two optimizers: the rule-based optimizer and the cost-based optimizer. Both optimizers are concerned with maximizing the performance of queries. The rule-based optimizer looks at the structure of the SQL statement to be optimized, determines what indexes exist for the table, and constructs an execution plan; it doesn't use any information about the contents of the table to be queried or its index values. The cost-based optimizer looks at statistics regarding the table, its columns, and its indexes and then calculates an execution plan based on the lowest cost path--;the number of database blocks that must be read to retrieve the query results. Oracle stores these statistics in the data dictionary tables whenever an analysis of the tables and indexes is performed--;which I explain at the end of the chapter.
An initialization parameter named OPTIMIZER_MODE controls the choice between the rule-based and cost-based optimizer; this parameter has three possible values:
NoteMeasuring the performance of a query is not as simple as it sounds. If you perform the same query twice against a large table, the second query will probably retrieve the results more quickly because the SGA already contains the database blocks that the query needs to read. Fewer disk reads are needed so the second query takes less time to complete. You can accomplish this by using the following SQL statement:
SQL> alter system flush shared_pool;
If you don't analyze tables and indexes, Oracle will use the rule-based optimizer to determine the best execution plan for each query. You can employ the EXPLAIN PLAN statement to obtain the execution plan for a query. The syntax for EXPLAIN PLAN is
EXPLAIN PLAN FOR sql-statement
where sql-statement is the SQL statement for which an execution plan is to be generated.
Before you use the EXPLAIN PLAN statement, you'll need to run a script from SQL*Plus that creates the PLAN_TABLE in your Oracle account.
SQL> @c:\orawin\rdbms71\admin\utlxplan.sql Table created. SQL> desc plan_table Name Null? Type ------------------------------- -------- ---- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER(38) ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) OTHER LONG
After the PLAN_TABLE has been created, you can begin using the EXPLAIN PLAN statement. Whenever the EXPLAIN PLAN statement is executed, Oracle inserts rows into the PLAN_TABLE; as a result, you need to delete the contents of PLAN_TABLE before each use of EXPLAIN PLAN. For example, suppose you create a table that records the day of the year and the maximum temperature in degrees Fahrenheit for each day. As a developer, you want to determine the efficiency of a query's execution plan.
SQL> delete from plan_table; 0 rows deleted. SQL> explain plan for 2 select day_number, temp_deg_f 3 from day_temp 4 where day_number = 100; Explained. SQL> select operation, options, object_name, id, parent_id, position 2 from plan_table 3 order by id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION ------------------- --------------- ------------ --------- --------- --------- SELECT STATEMENT 0 TABLE ACCESS FULL DAY_TEMP 1 0 1
EXPLAIN_PLAN is looking for the word FULL that appears in the OPTIONS column in conjunction with the TABLE ACCESS operation. FULL signifies that the query performs a full table scan to retrieve the data. If a query involving Day_Number is a fairly common operation, you should consider adding an index on the Day_Number column. Here's how to create the index and rerun EXPLAIN_PLAN.
SQL> create index day_temp_day_number_ck 2 on day_temp (day_number); Index created. SQL> delete from plan_table; 2 rows deleted. SQL> explain plan for 2 select day_number, temp_deg_f 3 from day_temp 4 where day_number = 100; Explained. SQL> select operation, options, object_name, id, parent_id, position 2 from plan_table 3 order by id; OPERATION OPTIONS OBJECT_NAME ID PARENT_ID POSITION ------------------ ----------- ------------------ ----- --------- --------- SELECT STATEMENT 0 TABLE ACCESS BY ROWID DAY_TEMP 1 0 1 INDEX RANGE SCAN DAY_TEMP_DAY_NUMBER_CK 2 1 1
As you can see, by creating the index, you've changed the optimizer's execution plan for the query. Instead of performing a full table scan, the optimizer will perform an index range scan, which is almost always a more efficient operation (although not for a table with a small number of rows). Even though the EXPLAIN PLAN statement supplies useful information about the methods used by the optimizer, it doesn't provide any hard performance numbers. To retrieve performance data, you'll want to use the TKPROF utility.
In the directory c:\orawin95\bin, you'll find an executable file named tkprof72.exe. This program must be executed from an MS-DOS window. TKPROF processes Oracle trace files to produce a text file that describes the SQL activity that occurred during a particular Oracle session. A trace file is extremely useful for performance analysis and tuning because
By default, Personal Oracle7 won't produce trace files. To produce a trace file with performance data, you'll need to set the TIMED_STATISTICS initialization parameter to TRUE and restart the Oracle instance. Include the following line in C:\ORAWIN95\DATABASE\INITORCL.ORA:
timed_statistics=TRUE
You must stop the database and restart it for this parameter to take effect.
If you wanted to analyze the performance of a group of SQL statements processed by SQL*Plus, you can enable a trace file for the Oracle session in the following way:
SQL> alter session set sql_trace true; Session altered.
You may then process any SQL statements that you were interested in analyzing. Oracle stores trace files in c:\orawin95\rdbms72\trace. You should end the Oracle session before you try to analyze the trace file.
Next, open an MS-DOS window. If you type tkprof72 at the prompt, the program will display a list of its command-line arguments. (See Figure 32.4.)
At a minimum, you should specify the name of the trace file, the name of the output file, and the Oracle user and password whose PLAN_TABLE will be used to determine the statement execution plan. (See Figure 32.5.)
By default, the output file will have an extension of .prf. You may then use an editor to display and print the contents of the output file. The summary portion of a sample TKPROF output file follows.
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 1.59 2 0 4 0
Execute 8 0.00 0.49 2 13 38 10
Fetch 5 0.00 0.04 2 3 9 35
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 2.12 6 16 51 45
Misses in library cache during parse: 4
Misses in library cache during execute: 1
8 user SQL statements in session.
26 internal SQL statements in session.
34 SQL statements in session.
4 statements EXPLAINed in this session.
********************************************************************************
Trace file: c:\orawin95\rdbms72\trace\ora63171.trc
Trace file compatibilty: 7.02.01
Sort options: execpu
1 session in tracefile.
8 user SQL statements in trace file.
26 internal SQL statements in trace file.
34 SQL statements in trace file.
18 unique SQL statements in trace file.
4 SQL statements EXPLAINed using schema:
FRAYED_WIRES.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
382 lines in trace file.
As you can see, the trace file provides a wealth of statistical information about the SQL statements that were processed during the Oracle session. This tool is particularly powerful in helping you solve the so-called 80-20 problem: identifying and improving the 20 percent of the SQL statements that represent 80 percent of the work.
Oracle collects table, column, and index statistics by executing the ANALYZE TABLE and ANALYZE INDEX statements. The syntax for the ANALYZE_TABLE statement is
ANALYZE TABLE table-name COMPUTE STATISTICS
where table-name is the name of the table to be analyzed.
Here's an example of the results produced by the ANALYZE TABLE statement. It creates a TEST_TABLE table and puts 1,000 rows into it.
SQL> create table Test_Table ( 2 Record_Number integer, 3 Current_Date date); Table created. SQL> SQL> declare 2 2 max_records constant int := 1000; 3 i int := 1; 4 4 begin 5 5 for i in 1..max_records loop 6 6 insert into Test_Table 7 (record_number, current_date) 8 values 9 (i, SYSDATE); 10 10 end loop; 11 11 end; 12 / PL/SQL procedure successfully completed.
If you inspect the row in the data dictionary view USER_TABLES, which contains information about TEST_TABLE, you'll see that the relevant columns are either null or zero.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 from user_tables
3 where
4 table_name = 'TEST_TABLE';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
--------- --------- ------------ --------- --------- -----------
0 0 0 0
However, once you analyze the table, the statistics related to TEST_TABLE are populated.
SQL> analyze table test_table compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 from user_tables
3 where
4 table_name = 'TEST_TABLE';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
--------- --------- ------------ --------- --------- -----------
1000 14 5 196 0 15
Besides filling in values in USER_TABLES, the ANALYZE TABLE statement also updates related information in USER_TAB_COLUMNS.
SQL> select column_name, num_distinct, low_value, high_value, density 2 from user_tab_columns 3 where 4 table_name = 'TEST_TABLE' 5 order by column_name; COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ---------------- ------------ ------------------ ------------------- --------- CURRENT_DATE 5 77C3060C101D32 77C3060C101D36 .02631579 RECORD_NUMBER 1000 C102 C20B .001
If a table contains many rows, you may want to consider an alternative form of the statement, which will sample a percentage of the rows:
ANALYZE TABLE table-name ESTIMATE STATISTICS SAMPLE percentage PERCENT
where table-name is the table to be analyzed and percentage is the percentage of the table's rows to be analyzed.
You can also remove the statistics for the pertinent table with the following statement:
ANALYZE TABLE table-name DELETE STATISTICS
where table-name is the table whose statistics are to be eliminated.
Using the previous table as an example, you can analyze TEST_TABLE by sampling a portion of the rows.
SQL> analyze table Test_Table delete statistics;
Table analyzed.
SQL> analyze table Test_Table estimate statistics sample 10 percent;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 from user_tables
3 where
4 table_name = 'TEST_TABLE';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
--------- --------- ------------ --------- --------- -----------
1000 14 5 196 0 15
For the cost-based optimizer to work well, the indexes that belong to the table being queried must also be analyzed. The syntax for the ANALYZE INDEX statement is almost identical to that of the ANALYZE TABLE statement.
ANALYZE INDEX index-name COMPUTE STATISTICS
index-name is the name of an index on the queried table.
To illustrate how the ANALYZE INDEX statement updates index statistics in the data dictionary, here is an example that uses the unique index PK_MANUFACTURER, which enforces the primary key for the Manufacturer table. To obtain information about table indexes, you can query the data dictionary view USER_INDEXES.
SQL> select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,
2 avg_data_blocks_per_key
3 from user_indexes
4 where
5 index_name = 'PK_MANUFACTURER';
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
-------------------- --------- ----------- -------------
----------------------- -----------------------
PK_MANUFACTURER
Now you can analyze the index and then query the data dictionary view USER_INDEXES again.
SQL> analyze index pk_manufacturer compute statistics;
Index analyzed.
SQL> select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key,
2 avg_data_blocks_per_key
3 from user_indexes
4 where
5 index_name = 'PK_MANUFACTURER';
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
-------------------- --------- ----------- -------------
----------------------- -----------------------
PK_MANUFACTURER 0 1 10
1 1
As you can see, executing the ANALYZE INDEX statement updates the index statistics, such as LEAF_BLOCKS and DISTINCT_KEYS.
This chapter discusses the following aspects of Oracle client/server performance tuning: