11.2 Basic Configuration
|
| < Day Day Up > |
|
Let's take a brief look at Oracle networking software configuration files and the Oracle instance configuration parameter file. Any details left out at this stage will be explained later in this book. Oracle instance parameters are covered throughout this book but are described in some detail here.
Let's start with the networking files since they are fairly simple.
11.2.1 Basic Network Files
The listener runs on the database server. It "listens" for requests from network processes, namely Oracle client processes. The database can be connected to a session using a tool such as SQL*Plus through a network protocol such as TCP/IP. A connection can be made on the server itself using something called the bequeath protocol which is a direct connection to the database. The ORACLE_SID variable must be set to use the bequeath protocol. Using the bequeath protocol will probably be a little faster than passing the connection through TNS.
Configuring the Listener
The file below is an example Listener configuration file. This file is installed in the $ORACLE_HOME/network/admin directory. Note that using the IP-Address instead of the hostname for the database server machine may be noticeably faster. However, subsequent changes to IP-Addresses on your network will require changing the Listener configuration file and bouncing the Listener. This is a problem if your database is not allowed any downtime.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>) (PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = <SID>) (ORACLE_HOME = /<path>/oracle/ora92) (SID_NAME = <SID>) ) )
Start the Listener by using the lsnrctl utility. Enter Listener control commands within the utility or as a parameter to the utility. The commands lsnrctl start starts the Listener and lsnrctl stop stops the Listener. The command lsnrctl status will show status of database services served by the Listener. Typing help within the Listener control utility will display a summary of all available Listener control commands.
Configuring the Client
<TNS Name> = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>) (PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = <SID>) ) )
Shown above is a simple example tnsnames.ora file placed on a client machine. To test the connection with the database server execute the command tnsping <TNS Name> from a shell on the client machine.
There is little "tuned" about these two network configuration files here. Much of tuning the performance of Oracle networking software involves tweaking of these two and other configuration files, depending on requirements. Make sure all the parentheses match and are in the correct places otherwise you will not be able to connect to the database. There are other options such as Oracle Names and Connection Manager allowing for scalability but not necessarily better performance.
11.2.2 The Parameter File
This section includes a simple example Oracle instance configuration parameter file with numerous comments. I use a file such as this when creating a database manually. The parameter file is usually placed in the $ORACLE_HOME/database directory or otherwise in the $ORACLE_BASE/admin/<SID>/pfile directory with a pointer file in the $ORACLE_HOME/database directory. This example is a text format version of the parameter file, which in Oracle9i Database and after can also be stored as a binary file called the SPFILE file. The SPFILE binary file was introduced in Oracle9i Database and allows changing of many configuration parameters with the database online.
| Tip | The parameter file contains Oracle Database parameter values overriding default values. The parameter file is parsed and loaded when the Oracle instance is started up. If the parameter file contains errors the database will not start! |
| Note | Oracle Database 10 Grid Configuration parameters have been simplified in Oracle Database 10g by categorizing them. For the simplest configurations only the basic parameters need to be changed. More advanced parameters can be altered for more complex and demanding environments. Many parameters have been greatly simplified, particularly the shared server parameters. |
The following description of the parameter file contains descriptions of different variations for different types of databases plus notes describing various settings. Comment out or delete the notes if you want to use this parameter file. There are many more configuration parameters in Oracle Database. The following query finds all the parameters and their settings.
SELECT name, value FROM v$parameter ORDER BY name;
Let's look at some of the configuration parameters.
Database Identification
db_name = "<SID >" db_domain = "" instance_name = <SID > service_names = <SID >
Control Files
Place multiple control files onto different mount points to ensure that all control files are not lost. This is called multiplexing of control files and has negligible effect on performance.
control_files = ( "/<mp1>/oracle/oradata/<SID>/control01a.ctl" ,"/<mp1>/oracle/oradata/<SID>/control02a.ctl" ,"/<mp2>/oracle/oradata/<SID>/control01b.ctl" ,"/<mp2>/oracle/oradata/<SID>/control02b.ctl" )
Block Size
OLTP databases are typically set to a block size of 8K. Increase the block size to 16K or even 32K for data warehouses and read-only type databases. Block sizes of 2K or 4K are not common even for OLTP databases except in older versions of Oracle Database.
db_block_size = 8192
| Tip | Available block sizes may vary for different operating systems. |
Block checking can cause a performance overhead. It can help prevent data block level corruption. Data corruption is more common when you have not spent enough money on disk storage media. Use of RAID arrays makes use of parameters such as this unnecessary.
db_block_checking = TRUE
Memory Buffers and I/O Behavior
The Database Buffer Cache
Oracle9i Database replaces the DB_BLOCK_BUFFERS parameter with the DB_CACHE_SIZE parameter. The database buffer cache should not be sized to a ridiculously high amount. A very high database buffer cache hit ratio is not an indicator of good performance. Very large database buffer cache sizes can actually hurt performance and sometimes be an indicator of poor performance.
db_cache_size = 80M
Separate database buffer caches must also be set for table-spaces of non-standard database block size. Thus with a block size of 8K, DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE, and DB_32K_CACHE_SIZE parameters with their related tablespaces can be set.
db_2K_cache_size = 2M db_4K_cache_size = 4M db_16K_cache_size = 16M db_32K_cache_size = 32M
| Tip | Available block sizes and thus DB_nK_CACHE_SIZE parameters may vary for different operating systems. A 32K block size is not available for Win2K SP3 running Oracle9i Database Release 2 (9.2). |
Multiple blocks can be read at once. Typically OLTP databases are set to 8K or lower and data warehouses are set to 16K or higher. Setting the DB_FILE_MULTIBLOCK_READ_COUNT parameter to 16 would read 16 blocks at once for every read of database buffer cache or disk. This parameter must remain small for OLTP databases since it can make full table scans more palatable to the Optimizer. Multiple physically contiguous blocks (next to each other) are read at once. Data warehouses can benefit substantially from reading many blocks at once since they are more likely to have data physically sorted depending on how tables are created. Obviously reading large numbers of blocks at once in a highly active OLTP database could have ill effects; it is unlikely even when large chunks of data are required that everything will be physically contiguous.
db_file_multiblock_read_count = 8
The DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE parameters are parameters allowing forcing of MRU (most recently used) data to be retained and LRU (least recently used) data to be discarded with less interference from other data in the main database buffer cache. Depending on the size and function of the database, these parameters could be set at 5% and 25% of the DB_CACHE_SIZE parameter, respectively. These parameters must be a multiple of the standard block size as defined by the DB_BLOCK_SIZE parameter.
db_keep_cache_size = 5M db_recycle_cache_size 20M
The FAST_START_MTTR_TARGET parameter is a crash recovery parameter.
fast_start_mttr_target = 300
The Shared Pool
The shared pool contains the library cache, the dictionary cache, language character set, and shared server queues. Shared server queues can contain some session connection memory. The library cache consists of parsed SQL code, optimization plans, and latches. The dictionary cache contains database metadata. Metadata is the data about the data. Like the database buffer cache the shared pool is often sized much too large, often because of SQL code lacking bind variables. SQL code without use of bind variables forces constant re-parsing, making reuse of SQL code non-existent and increasing use of CPU time substantially.
shared_pool_size = 80M
A small portion of the shared pool can be reserved for large operations creating a contiguous section of the buffer. This helps to avoid defragmentation in the shared pool by large operations, minimizing latch problems and a lot of bouncing all over a large, possibly highly fragmented buffer. Typically the SHARED_POOL_RESERVED_SIZE parameter is set within 5% and 10% of the size of the shared pool, but can be sized larger if circumstances and applications warrant it.
shared_pool_reserved_size = 8M
The processes parameter limits database connections, stored in the shared pool. Oracle8i Database required a minimum of 55 processes with 200 processes required when using Oracle Enterprise Manager software. Oracle9i Database and Oracle Database 10g values should be set higher as concurrent connections grow. The maximum processes for a dedicated server environment recommended by Oracle Corporation was set around 500. On the contrary, dedicated server environments can exceed 1,000 concurrent connections and still function efficiently. To get an idea of process memory usage there is 1 process per connection in a dedicated server environment. For 100 concurrent connections at 300K per connection, 30M of memory would be required. For 500 concurrent connections at 300K per connection 150M of memory would be required. A shared server configuration may decrease the amount of connection memory required because there is sharing of resources with sharing of server processes.
processes = 500
Connection Memory Requirements
For dedicated servers set the WORKAREA_SIZE_POLICY parameter to AUTO and set the PGA_AGGREGATE_TARGET parameter for automated session connection memory requirements. In the case of a shared server the WORKAREA_SIZE_POLICY parameter must be set to MANUAL.
Setting the SORT_AREA_SIZE parameter to 1M with 1,000 connections could claim 1 Gb of memory if every connection was sorting at once. To reiterate, 1 Gb of memory would only be claimed for sorting if every connection was performing an in-memory sort at the same time. This is unlikely but it is possible depending on what applications do. Each user connection is allocated up to a SORT_AREA_SIZE parameter specified chunk of memory whenever a sort is performed. Do not make the SORT_AREA_SIZE parameter too large because it will use a lot of memory if there are many concurrent connections performing sorting. Reporting, lots of joins, bad SQL code, and data warehousing could very well cause lots of sorting.
sort_area_size = 256K
The SORT_AREA_RETAINED_SIZE parameter is recommended to be set to 10% of SORT_AREA_SIZE when most transactions are small and set equal to SORT_AREA_SIZE when many large transactions are the norm. A small portion of memory sort space is retained by the connection for queries containing multiple sorts. This parameter can potentially speed up some queries, probably complex, poorly tuned or very large queries.
sort_area_retained_size = 6K
Other parameters in this group are as follows. These parameters are best not altered without proper planning and thought. Making the HASH_AREA_SIZE parameter larger tends to be useful when trying to persuade the Optimizer to favor hash joins rather than nested loop joins. I do not see the point because I never assume that I am more intelligent than the Optimizer unless statistics are completely useless, especially in Oracle9i Database and beyond.
-
HASH_AREA_SIZE. Used for SQL code Optimizer hash joins on larger row sets.
-
BITMAP_MERGE_AREA_SIZE. Bitmap merges.
-
CREATE_BITMAP_AREA_SIZE. Creating bitmaps.
The WORKAREA_SIZE_POLICY = AUTO and PGA_ AGGREGATE_TARGET parameters are intended as replacements for the previous five parameters controlling session shared pool memory usage. Oracle Corporation recommends use of these parameters in Oracle9i Database to automate PGA memory management.
| Tip | WORKAREA_SIZE_POLICY = AUTO will only function in a dedicated-server not a shared-server environment. |
The OPEN_CURSORS parameter sets a limit on the maximum number of open cursors allowed per session. Setting this parameter is application dependent and being a limitation does not reserve space for each session.
open_cursors = 100
The Large Pool
The large pool is required for use of tools such as RMAN and parallel queries, and could be set to much less than 200M, often much less.
Shared server configuration pushes some connection session memory requirements from the shared pool into the large pool. Increase large pool size to accommodate use of shared servers. The shared pool can be decreased accordingly.
large_pool_size = 200M
The Java Pool
The Java pool is used for the Oracle JVM if you choose to write stored procedures in Java. This parameter must be set if the Oracle JVM is installed into a database. Otherwise this parameter can be set to 0 and is a complete waste of resources unless Java is being used to write stored procedures.
java_pool_size = 0
| Note | Oracle Database 10 Grid JAVA_POOL_SIZE can be altered using the ALTER SYSTEM command and a binary parameter file, SPFILE. |
The Redo Log Buffer Cache
Oracle9i Database defaults the log buffer cache to 512K. This could be too high for low-activity OLTP databases but it is not necessary to change the default. Perhaps set the LOG_BUFFER parameter higher for the biggest, highly concurrent databases or large transactional databases with a lot of throughput and throughput changing rather than just reading data.
log_buffer = 524288
Small increases in the size of the log buffer can occasionally produce big increases in performance. However, I have seen databases with 500 Mb log files and log buffer caches of 100 Mb. Suffice it to say that these databases had serious performance problems, not only with little use of the log buffer cache but a lot of time spent copying redo log files to archive logs. The log buffer cache does not need to be enormous; it is recycled rapidly and written to disk.
Duplexing of redo logs is efficient, especially on servers with more than one CPU, and will generally not affect performance. It is always prudent to create multiple copies of redo logs no matter what your disk storage structure is. Redo logs files cannot be included in backups. Duplexing the redo logs provides for a second copy.
SQL Code Optimization
There are various parameters classifiable as influencing the Optimizer.
-
CURSOR_SHARING. This parameter is a temporary solution helping to alleviate performance problems when not using bind variables in SQL code. The CURSOR_SHARING parameter can be set to SIMILAR or FORCE, partially deteriorating the accuracy of statistics usage. The FORCE option will parse only the first SQL statement submitted. Since all literals are replaced with bind variables, subsequent submissions of similar SQL code may not match the current execution plan very well at all, depending on the structure of data. The SIMILAR option is the same as FORCE except histograms are used to help tune the execution plan better than when using the FORCE option. Use CURSOR_SHARING settings of FORCE or SIMILAR in OLTP systems but not in data warehouses. A data warehouse database will perform better using exact matches on statistics with literal values in SQL code since data volumes are much greater. In a data warehouse environment, perhaps set the CURSOR_SHARING parameter to EXACT.
-
HASH_JOIN_ENABLED. This parameter must be set to TRUE to allow use of hash joins in SQL code join optimization. It is extremely unlikely that any OLTP database would ever need to only use sort merge joins unless your OLTP database has very poorly tuned SQL join code or there is extensive reporting. If this parameter is not set to TRUE inefficient sort merge joins will always be used in favor of hash joins. Sort merge joins are very bad for performance. Hash joins are potentially much faster than sort merge joins.
-
QUERY_REWRITE_ENABLED. Set to TRUE to enable function-based indexing and query rewrites when using materialized views. Materialized views are generally only used in data warehouses.
-
QUERY_REWRITE_INTEGRITY. Set to TRUSTED to enable function-based indexing.
-
STAR_TRANSFORMATION_ENABLED. Set to TRUE in data warehouses to enable star query transformations.
-
PARALLEL_AUTOMATIC_TUNING. Set to TRUE if you have more than one CPU. If there is only one CPU this parameter can still help to automate semi-parallel processed Optimizer tuning. Rely on the Optimizer; setting this parameter to TRUE helps the Optimizer.
Note Oracle Database 10 Grid The PARALLEL_AUTOMATIC_TUNING parameter is deprecated.
-
COMPATIBLE. Set to the version of Oracle Database installed to take advantage of newly introduced optimization features.
compatible = 9.2.0.1.0
Tip Some of the connection memory parameters, named as %_AREA_SIZE could also be included here since they can affect Optimizer behavior.
Auditing and Tracing
Auditing and tracing allows for tracking, auditing, and tuning of database use.
Auditing
Database auditing can be performed at the operating system, database or even DML statement history levels. Switch auditing off if it is no longer required. It is a performance hog!
audit_trail = DB
Tracing
Tracing will affect performance drastically. I like to categorize levels of tracing into six groups:
-
No tracing.
timed_statistics = TRUE timed_os_statistics = 0 sql_trace = FALSE
Tip The overhead for setting the TIMED_STATISTICS = TRUE parameter is negligible.
-
Some tracing.
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = FALSE max_dump_file_size = 1M statistics_level = BASIC or TYPICAL
-
SQL tracing.
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = TRUE max_dump_file_size = 1M statistics_level = TYPICAL
-
Too much tracing.
timed_statistics = TRUE timed_os_statistics = 5 sql_trace = TRUE max_dump_file_size = 1M statistics_level = ALL
-
Oracle support tracing. This level of tracing you should never need. It produces a large amount of superfluous information and is utilized by setting the TRACE_ENABLED parameter to TRUE. This option is intended for the use of Oracle Corporation in analyzing potential bugs in Oracle software.
trace_enabled = FALSE
Note Oracle Database 10 Grid Oracle Trace and all ORACLE_TRACE_% parameters have been deprecated.
-
Session-level tracing. Specific sessions can be traced. Tracing at the session level can help to remove tracing overhead from the database in general and focus on a specific session.
However, the value of session-level tracing is relative to how easy it is to track when poorly performing SQL code occurs. It is true that instance-level tracing is instantiated for the entire database and can hurt performance severely. Session-level tracing is easier to use in large transactional environments as opposed to sharing of sessions between shared servers in an OLTP environment. Using the Oracle Database Wait Event Interface may provide better possibilities for finding problems at this level of detail.
Archiving, Check Points, the Alert Log, and Trace Files
Archive Logs
log_archive_start = true log_archive_dest_1 = 'LOCATION =<mp1>/oracle/oradata/<SID>/ archive' log_archive_format = Arc_%s.arc log_archive_start = false
Check Pointing
A checkpoint forces writing of all dirty buffer cache blocks to data files. Setting the LOG_CHECKPOINT_INTERVAL parameter to 0 will ignore checkpoints altogether.
log_checkpoint_interval = 0
Checkpoints are executed every 5 min with this setting.
log_checkpoint_timeout = 300
The Alert Log and Trace Files
The BACKGROUND_DUMP_DEST parameter determines the location of the alert log file and process trace files. User trace files are placed in the directory specified in the USER_DUMP_DEST parameter.
background_dump_dest = /<mp1>/oracle/admin/<SID>/bdump user_dump_dest = /<mp1>/oracle/admin/<SID>/udump
Rollback and Undo
Oracle9i Database and beyond allows for automated undo in addition to manually configured rollback segments. My test database with my Accounts schema uses both manual and automated undo at different points in time. This book contains tuning information on both since many existing pre-Oracle Database 10g installations use manual rollback. Growth appears to be properly controlled using automated undo segments in highly active concurrent OLTP databases. Automated undo should be used if possible in Oracle9i Database.
| Note | Oracle Database 10 Grid Manual rollback is deprecated. |
Manual Rollback Segments
OLTP databases require many small rollback segments. Many small rollback transactions for an OLTP database will increase database performance drastically for an OLTP database because transactions will spread evenly across all online rollback segments. It may even be intelligent to make many rollback segments as small as 16K initial and next. Set optimal size depending on transaction size.
| Tip | Separate large rollback segments placed offline by default can be placed online and utilized for large transactions, and then placed offline on transaction completion. |
OLTP databases can perform well with a large number of small rollback segments. The ROLLBACK_SEGMENTS parameter forces rollback segments online at database startup and is advisable even for public rollback segments. Otherwise unused rollback segments may never be placed online and used. The number of rollback segments placed online at startup is dependent on potential transactions, not the sizes of rollback segments. When using large numbers of rollback segments make sure that next extent values are very small to avoid rollback segments sharing too many transactions at once. The idea is to separate small transactions into separate rollback segments to reduce contention between different transactions using the same rollback segment.
max_rollback_segments 50 rollback_segments = ( RB00,RB01,RB02,RB03,RB04,RB05,RB06,RB07,RB08,RB09 ,RB10,RB11,RB12,RB13,RB14,RB15,RB16,RB17,RB18,RB19 ,RB20,RB21,RB22,RB23,RB24,RB25,RB26,RB27,RB28,RB29 ,RB30,RB31,RB32,RB33,RB34,RB35,RB36,RB37,RB38,RB39 ,RB40,RB41,RB42,RB43,RB44,RB45,RB46,RB47,RB48,RB49 )
| Note | Oracle Database 10 Grid Manual rollback is deprecated. |
Automated Undo Segments
Databases with small transactions can have lower values for the UNDO_RETENTION parameter.
undo_management = AUTO undo_retention = 900 undo_tablespace = UNDOTBS
| Note | Oracle Database 10 Grid The UNDO_SUPPRESS_ERRORS parameter is deprecated. |
Resource Management
Resource Management allows the distribution of resources such as CPU time between different groups of sessions. The RESOURCE_MANAGER_PLAN parameter sets the default resource management plan or group. Resource plans can be altered depending on processing requirements during the course of a day, allowing for variable performance requirements. For instance, during high activity times an OLTP grouping plan can be used allowing for fast reaction times and during low activity times a BACKUP or batch-type grouping could be used allowing for faster throughput.
resource_manager_plan = 'OLTP'
Job Scheduling
This parameter has an effect on how many jobs can be scheduled and executed at once. I have some highly active code executed on my database using the DBMS_JOBS package to produce a tunable database for writing this book. This code kicks off between 10 and 100 jobs per second in order to produce highly concurrent activity. The intention was to create a tunable database. The default value for the JOB_QUEUE_PROCESSES parameter is 10.
job_queue_processes = 10
Networking
Networking parameters only affect Oracle shared servers (formerly MTS or Multi-Threaded Server). Shared servers allows for connection pooling, load balancing, and sharing of database connections. Shared servers perform the same function that an application server or web server would. A word of advice: If you can afford a connection pooling application server or web server avoid shared servers; they have not necessarily always been problematic but perhaps misused and misunderstood.
| Tip | Shared servers were called MTS or Multi-Threaded Server prior to Oracle9i Database. |
dispatchers = "(PROTOCOL = TCP) (DISPATCHERS = 1) (POOL = ON) (PORT = 1521)" shared_servers = 2 circuits = 10 shared_server_sessions = 10 max_dispatchers = 2 max_shared_servers = 5
| Note | Oracle Database 10 Grid Shared server parameter configuration is simplified. |
A simple explanation of shared servers is as shown in Figure 11.1. In a dedicated server environment, client sessions connect directly to the database via a dedicated server process. In a shared servers configuration, client processes can share connections to shared server processes, having messages passed among themselves and those shared server processes using dispatcher processes. Shared servers can therefore service a lot more connections since idle sessions are not always connected to the database, particularly when a client connection is idle. Idle connections through dedicated server processes waste resources better used otherwise. A shared server is more scalable than a dedicated server environment in terms of the number of concurrent connections to the database.
Figure 11.1: Dedicated and Shared Servers Architecture
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 164