当前位置:首页 >> IT/计算机 >>

Oracle Database 10g Managing Oracle On Linux for DBA Student Guide


Tuning Oracle on Linux

Copyright 2007, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to do the following: Tune supported file systems Configure initialization parameters Implement asynchronous input/output (I/O) Implement advanced memory management techniques

10 - 2

Copyright 2007, Oracle. All rights reserved.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 2

Basic Oracle Database Optimizations



Use locally managed tablespaces (default). Use a larger database buffer cache (within reason). Use an appropriately sized database block. Use a larger redo log buffer. Use multiple database writer processes on Symmetric Multiprocessing (SMP) machines. Use Automatic Shared Memory Management.

10 - 3

Copyright 2007, Oracle. All rights reserved.

Basic Oracle Database Optimizations Oracle Database has a set of well-known (but often overlooked) features that enhance performance. Locally Managed Tablespaces Locally Managed Tablespaces (LMT) are the default in Oracle Database 10g. Starting with Oracle9i Release 2, even the system tablespace may be locally managed. LMT reduces I/O by managing the extent allocation within a file with a bitmap in that data file instead of updating tables in the data dictionary. This decreases the I/O requirements for the system tablespace. Performance increases of 2% to 15% have been documented using LMT, depending on the application. Larger Database Buffer Cache The database instance caches the most recently used blocks in the database buffer cache. The larger the cache, the more data blocks can be cached. Because a disk access takes much longer than a memory access, each database block that can be cached and reused improves performance. Size the cache according to the db_cache_advice feature in Oracle Enterprise Manager. Do not make the cache so large that paging increases or (in extreme cases) it takes longer to search for a block in memory than it would have to just read the block from disk. Performance gains depend on the amount of reuse in the data blocks.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 3

Sizing Database Blocks

The DB_BLOCK_SIZE parameter should be set according to the type of application: 8 KB block size gives good overall performance. Smaller blocks give better concurrency with OLTP applications. Larger blocks reduce the amount of overhead for data warehouse/decision support system (DW/DSS) applications.

10 - 4

Copyright 2007, Oracle. All rights reserved.

Sizing Database Blocks The size of the database block has a large impact on the performance of the application. Tests have shown that the best block size is usually 8 KB. The database block must always be the same size or a multiple of the memory page size. On Linux, the page size is 4 KB but (as discussed in the lesson titled “Linux Measurement Tools”) can be increased to 2 or 4 MB using hugepages. The database block should also be the same size or a multiple of the file system block size. The block size on the ext3 file system is 4 KB. Other file systems may use other block sizes. OLTP Applications An online transaction processing (OLTP) application is characterized by a large number of concurrent users and relatively small transactions. As the blocks get larger, the probability of two or more users requesting access to the same block simultaneously also increases. This leads to “buffer busy” waits, because only one process can access a block at a time. Smaller blocks reduce the number of rows in the block, which then reduces the likelihood of concurrent access. If your application has these characteristics, then consider setting DB_BLOCK_SIZE to 4 KB.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 4

Sizing Database Blocks (continued) DSS/DW Applications Data warehouse–type applications are characterized by large data sets and frequent full table scans. Because the data sets are so large that the database buffer cache cannot hold the full set, the set must be read from disk. In this case, use the largest block size available (16 KB on 32-bit Linux). This reduces the percentage of space used by block headers, and increases the number of rows per block, thus reducing the number of I/Os by packing more data into the same number of bytes. Effect of Block Size on Indexes In databases with large tables and small database blocks, consider a very large OLTP application such as credit card processing. The indexes may get quite large. Index lookups are always done serially: first the root block is accessed, and then the first-level branch block is accessed. This process continues to the n-1 level branch, finally reaching the leaf block, which is the nth level. This lookup may require n I/Os for an n-level index, if the blocks needed are not already in the cache. Ideally, the index is no more than three levels deep, meaning n should be three or less. In the following example, a 20-level index is chosen to simplify the mathematics. Realistically, an index would seldom get that large. To illustrate the effect of the block size on an index, assume a 2 KB block size and a 20-level index. If this index were in a database with 4 KB block size, then each leaf would hold at least twice as many index row entries, so that half as many leaf blocks would be used. The first-level branch blocks would hold twice as many leaf pointers, but there would be only half as many leaf blocks, so the number of first-level branch blocks would be reduced to 1/4 of the original index. Continuing with this exponential reduction in branches would reduce the number of levels to five. The first index required 20 reads for an index lookup, and the rebuilt index requires five reads. This significantly improves the performance of index lookups. Because of this, you may want to use a larger block size if the application has very large indexes, or consider using partitioned indexes.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 5

LOG_BUFFER and Redo Log File

Increasing the size of the redo log components can help the performance of high data manipulation language (DML) applications. Examine performance reports for waits on the redo log components. Tune the redo log file and archive log file transfers first. Increase the size of the LOG_BUFFER parameter.

10 - 6

Copyright 2007, Oracle. All rights reserved.

LOG_BUFFER and Redo Log File Every DML statement produces some amount of redo information. This data must be written to the redo log files on commit. The server process does not report Commit complete to the user until the redo information has been written to the redo log file. When the current log file is full, a log file switch occurs. If the LGWR process cannot write to the log files fast enough to clear space in the log buffer for other processes that are attempting to execute DML statements, then various wait statistics are incremented. For example, if there is no space in the log buffer at the time an attempt is made to write to it, there will be a log buffer space wait event. Increasing the size of the LOG_BUFFER can reduce or eliminate these waits. The minimum LOG_BUFFER size is 64 KB. An optimal setting for LOG_BUFFER varies with application. High-volume DML applications can benefit from a LOG BUFFER set to something higher than the default value.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 6

LOG_BUFFER and Redo Log File (continued) Sizing redo log files is dependent on the speed at which redo is generated. If the switch is delayed for any reason, the processes trying to write into the LOG_BUFFER have to wait, and a wait event log file completion is recorded. You want to eliminate these waits and at the same time, do not want to size the files so large that they become unmanageable. Redo log files in the range of tens to hundreds of megabytes are considered reasonable. A tentative guideline is to size them such that the log file switch occurs every 20 to 30 minutes. For details, refer to the Oracle Database 10g: Performance Tuning course and the Oracle Database 10g Performance Tuning Guide.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 7

Advanced Features of Oracle Server

There are a few advanced initialization parameters that affect performance on Linux: DB_WRITER_PROCESSES DBWR_IO_SLAVES PRE_PAGE_SGA

10 - 8

Copyright 2007, Oracle. All rights reserved.

Advanced Features of Oracle Server The parameters listed here are considered advanced parameters. Very few initialization parameters depend on the OS. The parameters listed in the slide are the ones that are most affected by the OS.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 8

Multiple DBWR Processes

DB_WRITER_PROCESSES: These processes are set to no more than CPU_COUNT, up to 20. Multiple DBWR processes write from LRU to disk. These processes can use asynchronous I/O. These processes are best used in OLTP environments.

LRU lists

DBWR processes
10 - 9 Copyright 2007, Oracle. All rights reserved.

Multiple DBWR Processes In high-volume OLTP databases, the standard configuration of the DBWR process may not be able to write changed data blocks back to the disk fast enough to maintain the pool of free buffers for new blocks coming into the database buffer cache. The usual first step is to increase the DB_CACHE_SIZE, but this may not be enough. The next step is to increase the number of DB_WRITER_PROCESSES. This starts the specified number of DBWR processes. This can be up to 20 as of Oracle9i, Release 2. Each DBWR process handles one or more LRU lists moving dirty buffers to the disk for those lists. If asynchronous I/O is turned on, then all the DBWR processes make use of it. The default value for DB_WRITER_PROCESSES is (CPU_COUNT+7)/8. For example, with between one and eight CPUs, DB_WRITER_PROCESSES is set to one. If your CPU_COUNT = 20, then DB_WRITER_PROCESSES is set to three. The value of DB_WRITER_PROCESSES must be adjusted incrementally. When increasing the number of DB_WRITER_PROCESSES, consider the number of disk controllers that are available and monitor the disk I/O queues. Too many DB_WRITERS_PROCESSES can cause contention for the controller channel.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 9

DB Writer Slaves

DBWR_IO_SLAVES: Used to simulate asynchronous I/O One DBWR, multiple writers to disk

Buffer cache

DBWR

I/O slaves

10 - 10

Copyright 2007, Oracle. All rights reserved.

DB Writer Slaves On systems without asynchronous I/O capability, the database instance provides a way to simulate asynchronous I/O with the DBWR_IO_SLAVES. Set this parameter to no more than two times the number of disks that the database is spread across. When increasing DBWR_IO_SLAVES, consider the number of disk controllers and monitor the disk I/O queues for contention. Also, when increasing the number of slaves, add only a few at a time, because each of these processes has an overhead cost. If you add too many at once, the overhead of all the processes may outweigh the benefit of the additional processes. Each slave must have a communication area for I/O buffers. This area is taken from the large pool if it is configured, or from the shared pool if the large pool does not exist. Turning on DBWR_IO_SLAVES forces DB_WRITER_PROCESSES to one, and sets the number of slave processes used by the ARCH and LGWR processes to four. Even though I/O slaves are generally used to simulate asynchronous I/O, the slaves use asynchronous I/O if it is turned on. Note: The use of multiple DBWR processes and I/O slaves are mutually exclusive.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 10

Changing SGA Behavior

Normally, unused memory allocated to the System Global Area (SGA) is not mapped to the physical memory until it is used, and infrequently used pages are paged out. To touch all the SGA at startup, use PRE_PAGE_SGA:
– The SGA is mapped to physical memory. – The SGA could slow connection processing.

10 - 11

Copyright 2007, Oracle. All rights reserved.

Changing SGA Behavior The normal behavior is that shared memory up to SGA_MAX_SIZE is allocated and set up in virtual memory at startup. Unused pages in the SGA are not mapped to physical memory until they are used, and pages in the SGA that are not frequently used may be paged out. The PRE_PAGE_SGA initialization parameter causes each server process that starts to touch every page of the SGA. This has the advantage of mapping all the SGA pages to physical memory, so that when they are required later, they will be already mapped. The disadvantage is that with large number of pages, each server process that starts touches all the pages, thus increasing the connection time. If some pages are swapped out, those pages have to be swapped in for the touch. This in turn may force other more active pages to swap, further reducing performance. Set PRE_PAGE_SGA = true only when there is sufficient real memory to hold the entire SGA.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 11

Automatic Shared Memory Management

Automatically adapts to workload changes Maximizes memory utilization Helps eliminate out-of-memory errors
Buffer cache Example: Large pool Shared pool Java pool Streams pool Online users
10 - 12

Buffer cache Large pool Shared pool Java pool Streams pool Batch jobs

Copyright 2007, Oracle. All rights reserved.

Automatic Shared Memory Management Automatic Shared Memory Management (ASMM) is another key self-management enhancement in the Oracle database. This functionality automates the management of the most important shared memory structures used by an Oracle database instance, and relieves you of having to configure these components manually. Besides making more effective use of available memory and thereby reducing the cost incurred for acquiring additional hardware memory resources, the ASMM feature significantly simplifies Oracle database administration by introducing a more dynamic, flexible, and adaptive memory management scheme. For example, in a system that runs large online transactional processing (OLTP) jobs during the day (requiring a large buffer cache) and runs parallel batch jobs at night (requiring a large value for the large pool), you would have to simultaneously configure both the buffer cache and the large pool to accommodate your peak requirements. With ASMM, when the OLTP job runs, the buffer cache grabs most of the memory to allow for good I/O performance. When the data analysis and reporting batch job starts up later, the memory is automatically migrated to the large pool so that it can be used by parallel query operations without producing memory overflow errors.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 12

Basic Linux Optimizations

Basic changes to the Linux default setup can give some performance improvements. Use the latest supported kernel. Turn off last time-read updates for the database files.

10 - 13

Copyright 2007, Oracle. All rights reserved.

Basic Linux Optimizations Apply the latest supported kernel patches through the .rpm files provided by the distribution vendor. This ensures that you are using the most efficient kernel available. By default, the last time-read attribute (atime) is updated every time a file is read. For database files, this is not an important statistic and you can turn it off to reduce the number of I/Os. This parameter can be set for a file with chattr +A <filename> or for a directory with chattr R +A <directory name>. To make this change persistent across reboots, change it for a file system by changing the /etc/fstab file and adding the noatime option to the fourth column. /dev/hdb7 /u3/app/oracle/ ext3 rw,noatime 1 1

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 13

Choosing a Kernel

Choose the appropriate kernel for best performance: If you have more than 4 GB of physical memory, then use the enterprise or hugemem kernel. If you have more than one CPU, then use the smp kernel. Otherwise, use the uniprocessor kernel.

10 - 14

Copyright 2007, Oracle. All rights reserved.

Choosing a Kernel The Linux installers tend to make good choices about which kernel to install, and install the kernel that uses your hardware and gives a good performance. If you have to choose which kernel to use, follow these guidelines. For machines with more than 4 GB of physical memory, an enterprise kernel is required. The enterprise kernel includes the Physical Address Extensions (PAE) that are required to address more than 4 GB of physical memory. This includes a three-level memory page table, so if you have less than 4 GB of physical RAM, then the three-level page table is an extra overhead that is not required. For multiprocessor machines, the installer chooses an smp kernel. The smp kernel has the required extensions to handle scheduling processes on more than one CPU. The uniprocessor kernel handles up to 4 GB of physical memory on a single processor. An example of the uniprocessor name is 2.4.9-e.3. (Note that there is no special designator.) An enterprise or smp kernel may be used on a uniprocessor machine, but has extra overhead associated with the unused features.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 14

Summary

In this lesson, you should have learned how to: Evaluate file systems Tune supported file systems Configure initialization parameters Implement asynchronous input/output (I/O) Implement advanced memory management techniques

10 - 15

Copyright 2007, Oracle. All rights reserved.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 15

Practice 10 Overview: Tuning Performance
This practice covers the following topics: Adjusting the block size of database objects Identifying poorly performing sessions at the operating system level

10 - 16

Copyright 2007, Oracle. All rights reserved.

Oracle Database 10g: Managing Oracle on Linux for DBAs 10 - 16


相关文章:
Linux_系统运维之Oracle_DBA
Linux 系统运维之 Oracle DBA Linux 系统运维之 Oracle DBA UNIXHOT 运维社区 ...Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With...
oracle-DBA日常检查
oracle-DBA日常检查,DBA必备,包括日常巡检、周检内容 Oracle10g 数据库 日常维护...1.3. 检查 Oracle 监听状态 /home/oracle>lsnrctl status LSNRCTL for Linux:...
《Oracle Applications DBA 基础》 1-2 系统概要及安装
3.《R12安装手册 Ver2 for Linux32 生产环境》 ...< 189708.1 > Oracle Reports 6i Setup Guide ...Database logfile - /u01/db/tech_st/10.2.0/...
阿里巴巴Oracle-DBA笔试题及答案
on explain plan set statement_id = &item_id for &sql; select * from table(dbms_xplan.display); http://download-west.oracle.com/docs/cd/B14117_01...
oracleDBA笔试经典试题
oracleDBA笔试经典试题_计算机软件及应用_IT/计算机_专业资料。Oracle数据库管理员...student a where a.rowid < (select max(rowid) from student b where a....
DBA简历模板
(10G) 工作经验:三年 期望薪资: 职位:oracle dbalinux 系统管理员 日常工作描述: 1.负责 Oracle 数据库的日常操作和监控 ,及时发现和处理碎片,所用平台为...
Oracle 10g DBA手册_图文
Oracle 10g DBA手册_计算机软件及应用_IT/计算机_...在 UNIX 和 Linux 安装时,Specify Inventory ...选择 Use Database Control for Database Management...
在linux上安装oracle时先要创建两个组再创建一个用户的...
linux上安装oracle时先要创建两个组再创建一个用户的意义_计算机软件及应用_IT...(2)ASM 数据库管理员组(OSDBA for ASM,一般为 asmdba) ASM 数据库管理员组...
51CTO学院-Oracle DBA工厂(一)-Oracle从入门到精通全套...
linux 基础知识以及 Oraclelinux 下的安装部署 适用人群 Oracle 技术爱好者、Oracle 从业人员和有意从事 Oracle DBA 工作的的人员等 课程简介 <<Oracle DBA ...
oracle DBA常用命令大全
oracle DBA常用命令总结 3页 1下载券 Oracle数据库管理员的常... 5页 1下载...99 在指字表空间里建索引 create index t1_ind on t1(a) tablespace mytbs...
更多相关标签: