当前位置:首页 >> 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下oracle10g 静默安装完整版.doc
On51ZzEeXif2RJMQz3CWSxZK_hGQ43a04sFL613 10201_database_linux32.zip、 ...dba useradd -g oinstall -G dba oracle passwd oracle 1.5 创建目录 mkdir ...
Linux5.5下安装Oracle10G.doc
Oracle 10gLinux 下的安装 1. 创建 Oracle 用户 groupadd -g 701 oinstall {数据库所在的用户组} groupadd -g 702 dba {数据库用户的权限} useradd -...
linux5+oracle10g实战精辟文档.doc
linux5+oracle10g实战精辟文档_计算机软件及应用_IT/...dba #useradd -m -g oinstall -G dba oracle #...Oracle Database 10g is not certified on the curren...
csg1.ppt
Oracle Database 10 Managing 10g: Oracle on Linux for System Administrators Student Guide Volume I D46592GC11 Edition 1.1 October 2007 D53201 ? ...
Oracle10g RAC安装与管理文档(for linux)_图文.doc
Database Installation Guide 10g Release 2 (10.2) for Linux x86-64 Part ...dba #useradd -g oinstall -G dba oracle #passwd oracle 3.4.2 Oracle ...
Oracle 12c Database Installation on Oracle Linux 6 ....doc
Oracle 12c Database Installation on Oracle Linux ...Oracle 12c Database Installation Guide Oracle has ...Y Read more on Managing Oracle 12c CDB’s and...
Linux下oracle10g 静默安装完整版.doc
---oracle10g 命令行安装全过程---1.下载并解压文件 在Oralce官网上下载个Oralce10g for Linux,分i386和x86_64两种。 10201_database_linux32.zip、10201_data...
OracleEBS11i-DBA维护笔记.pdf
10g Preparation Download Database10g Install the Release 10.2 Oracle Software...Linux Error: 28: No space left on device ORA-01075: you are currently ...
Oracle Database 10g数据库安装及配置.doc
Oracle Database 10g数据库安装及配置_计算机软件及应用...“P/L SQL Developer”工具,以 DBA(用户名:System...for Linux x86-64 http://download.oracle.com/...
LINUX下服务器安装oracle10g数据库教程_图文.doc
LINUX下服务器安装oracle10g数据库教程_计算机硬件及网络_IT/计算机_专业资料。1...说明: # useradd -g oinstall -G dba -d /home/oracle oracle (新增用户 ...
ORACLE10g-OCP官方教材II(第三版).pdf
Preface - 3 Related Publications Oracle Publications Title Oracle Database 2 Day DBA 10g Release 2 (10.2) Oracle Database Administrator's Guide 10g Relea...
OCM大师实验班之调优实训中文教材目录.doc
10g Linux for DBA 中文教材 10g 性能调优中文教材 10g SQL 调优中文教材 Oracle Database 10g: Managing Oracle on Linux for Database Administrators 课程 教学...
Oralce_DBA_教学大纲.doc
Oracle9i Database Administration Fundamentals I Oracle9i Database Administration Fundamentals II 10g Managing Oracle on Linux for DBA Expert Oracle Database ...
Oracle 10g EM安装方法_图文.pdf
(dba),503(oper) [oracle@hubery ~]$ lsnrctl status LSNRCTL for Linux: ...Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 ...
test4pass oracle test 1Z0-046 exam dumps questions ....pdf
Test4pass Expert In IT Certification Exams Exam Title : : Oracle 1Z0-046 Exam Oracle Database 10g: Managing Oracle on Linux for DBAs Version : Demo ...
Oracle database 11g Administration workshop-II-2.pdf
Student Guide e gm Stud @ ?sh e this b tcr...Oracle Database 11g: “g” Stands for Grid I-...DBA Tools 1-25 Facilitating Database Management ...
Oracle10g Install_and_patch_for_linux_图文.doc
Oracle10.2.0.1 Install for Linux Installation Guide Contenta Contents ...Oracle Database 10g release 2 (10.2) on RedHat enterpraise as 4 update...
Oracle Database 11g Administration Workshop II sg1.pdf
Student Guide D50079GC20 Edition 2.0 September ...that are supported by Oracle Database 10g. ?...Managing Oracle Restart components with the srvctl ...
Oracle官方文档阅读方法.doc
Oracle 数据库管理。作为 DBA,《Oracle Database ...(OU 称之为 Student Guide) , 并且有课程代号和...for Linux and UNIX-Based Operating Systems 这个...
Install_Oracle_Database_11g_on_Linux.doc
Install_Oracle_Database_11g_on_Linux_英语学习_外语学习_教育专区。oracle11g安装 linux环境下得 Database Installation Guide 11g Release 2 (11.2) for Linux...
更多相关标签: