oracle for trueunix 性能调优

andJan Mark HolzerSenior Member of Technical StaffTru64 UNIX Engineering Groupjmh@Friday, March 29, 2013<Revised March 5, 2002> Configuring Tru64 V5.1 for Optimal PerformancewithORACLE 8.1.7.x/9iOverviewThis is a short writeup on recommended settings to /etc/sysconfigtab in a Tru64 5.1 environment running ORACLE 8.1.7.x and/or 9iIn the past there have been several resources/documents providing recommendations on how to set certain tunables in /etc/sysconfigtab but there has always been a fair amount of confusion which guidelines to follow.This document attempts to give some generic guidelines on how to set some key tunables applicable to an ORACLE environment. The assumption is that the ORACLE version used is8.1.7 or higher. This is important as starting 8.1.7 ORACLE is using AdvFS‟s directIO capabilities to bypass the buffer cache (UBC) part of the filesystem layers.Recommended Patches and Operating System VersionsAs of February 2002 the recommended versions to use for 8.1.7/9i installations are ∙Tru64 and TruCluster V5.1 with patch kit PK4/BL18∙Only Tru64 V5.1/PK4 will require the installation of a separate patch for AdvFS, this patch prevents a problem where an application using AdvFS directIO (such as ORACLE) orD_SYNC may re-read stale data on a CFS cliento The patch identifier is : T64V5118-C0091200-12603-Manual-20011220.tar ∙Tru64 and TruCluster V5.1A with PK1/IPKo The aboved mentioned patch is NOT required for systems running 5.1A/IPK Key TunablesVM SubsystemThe following tunables should be modified in the …vm‟ subsystem section of /etc/sysconfigtabnew_wire_methodThis tunable should be set to …0‟ if SSM memory is used (ie. s sm_threshold is at this default value of 8MB). In Tru64 V5.1 there‟s an interaction between the AIO (Asynchronous I/O) and page wiring mechanism which may result in high system time.This issue can be resolved by setting new_wire_method to …0‟.There‟re n o negative performance impacts by disabling this tunable.To avoid the interaction between new_wire_method and ssm_threshold would be to use granularity hints memory (gh_regions or rad_gh_regions) to pre-wire memory for the database to use.See the section on gh_chunks and rad_gh_regions for more information on GH memory. Recommended value for new_wire_method is ‘0’rad_gh_regions / gh_chunksTru64 supports two options on how to allocate shared memory for ORACLE. The “traditional” option is to use SSM memory which is controlled via the tunable ssm_threshold in the …ipc‟ subsystem. The default on Tru64 V5.1 is to use SSM (Segmented Shared Memory) memory. The other option available is to use granularity hints or also known as large pages.This memory cannot be used for any other purpose, nor can it be returned to the system or reclaimed when not being used it will solely be used by the database for its SGA (System Global Area) or any other application using shmget()/nshmget() to allocate shared memory..On NUMA-aware systems (GS80, GS160, and GS320), the gh_chunks attribute affects only the first Resource Affinity Domain (RAD).Using large pages is the preferred method on GS80/160/320 systems running ORACLE 8.1.7. Large pages can also be configured on non-GS series systems such as the ES, DS and GS140 platforms, on these systems the tunable gh_chunks is used instead of rad_gh_regions..Tru64 V5.1 supports using large 4MB pages "granularity hints" by setting the rad_gh_regions either using contiguous or stripe algorithms, on non-GS series platforms the gh_chunks parameter is used.The amount of GH memory configured on a system can be viewed using the command “vmstat –P”.The syntax to enable large pages support depends on the platform it is implemented on:GS80/GS160/GS320 platformsThe amount of memory per RAD/QBB in MB is specified via the correspondingrad_gh_regions tunable.The naming convention for this tunable is to specify rad_gh_regions[RAD#] , for example to allocate 2GB in QBB0 one would specify 2048 for rad_gh_regions[0], ie. rad_gh_regions[0] = 2048 .You should take your planned/projected Oracle SGA size and divided by the number ofQBBs/RADs your GS system has configured. For each rad_gh_regions[x] (where …x‟ represents the QBB identifier (from 0-7)) you‟d specify the required value in Megabytes (MB).The sum of rad_gh_regions[*] should be set to the size of the Oracle SGA, however one might consider allocating a larger value for rad_gh_regions in order to be able to resize the Oracle SGA without having to reboot the system (changes to rad_gh_regions require a system reboot).Setting rad_gh_regions to a larger value than initially required/calculated can provide the benefit of being able to allocate a larger SGA without having to reboot the system (rad_gh_regions is not a dynamic system tunable).Shared memory should generally be allocated in striped mode in order to distribute memory across all available RADs, changing to a sequential allocation policy may adversely affect performance as it may cause hot-spots in individual RADs.Non-GS series platforms (ES, DS and GS140 systems)On these systems the gh_chunks tunable is used to configure granularity hints memory. As the overall performance gain using gh_chunks is usually only in the range of 7% it might be not an interesting option for most systems due to its complexity in implementation.However some systems might still benefit from using gh_chunks, especially ORACLE environments which are having a very large number of clients connecting and disconnecting from the database. In these type of environment using gh_chunks can actually give a significant performance increase. gh_chunks is calculated by dividing ORACLE‟s SGA size by 4MB and then specifiying the value for gh_chunks (ie. setting to gh_chunks = 1000 would allocate 4GB of memory for GH memory).Recommended setting of rad_gh_regions is the size in MB of the ORACLE SGA / # of QBBs in your system. For example if you’d have a GS320 which has 64GB of main memory in 8 QBBs an d your ORACLE SGA is sized at 16GB you’d specify a value of (at least) 2048 for each rad_gh_regions tunable (from rad_gh_regions[0] to rad_gh_regions[7]).On non-NUMA/GS platforms gh_chunks should be set to at least the SGA size divided by 4MB. If rad_gh_regions or gh_chunks are being used ssm_threshold must be disable (in the ‘ipc’ subsystem) by setting ssm_treshold=0ubc_maxpercentAs ORACLE 8.1.7 is using AdvFS directIO there‟s no need to artificially restrict the UBC, in the past it has been recommended to cut down to a small(er) value to prevent (double) caching in the filesystem. Setting this tunable to a very low value may cause contention in the kernel and therefore impact performance negatively.Recommended value for ubc_maxpercent would be at least 70% and should not be set to a value smaller than 35%.ubc_borrowpercentThe value for this tunable is a percentage of memory above which the UBC is only borrowing memory from the virtual memory subsystem.Paging does not occur until the UBC has returned all its borrowed pages. Usually the default value of 20 is a good fit for most systems. If the system is a database server without any interactive users active one might consider lowering this value to improve backup performance. Recommended value for ubc_borrowpercent is 20% (the default). For database server only systems might consider setting it to 10%.vm_ubcseqpercentThis parameter controls the maximum percentage of UBC memory that can be used to cache a single file. vm_ubcseqstartpercent is used to control when the UBC checks this limit.The default value of 10% is usually a good fit for most systems, however if the system is a database server only environment and to improve performance during backups one might consider reducing this value to 5%.vm_ubcseqstartpercentThis value is a threshold (a percentage of the UBC in terms of its current size) that determines when the UBC starts to check the percentage of UBC pages cached for each file object.If the cached page percentage for any file exceeds the value of vm_ubcseqpercent, the UBC returns that file's UBC LRU pages to virtual memory.Prior to Version 5.0, vm_ubcseqstartpercent was defined to be a percentage of available memory. Starting with Version 5.0, this attribute is defined to be a percentage of ubc_maxpercent, which is itself a percentage of available memory. The definition change has no effect if the value ofubc_maxpercent is at its default value (100 percent).However, the definition change has implications if the value for ubc_maxpercent has been lowered.For example, if on a V4 system, the customer lowered ubc_maxpercent to 80 and setvm_ubcseqstartpercent to 20, the value for vm_ubcseqstartpercent should be set to 25 on the V5 system (assuming the value for ubc_maxpercent is still set to 80) to specify the same thresholdas used on the V4 system.Recommended value is to keep the default of 50%vm_ubcdirtypercentThe percentage of pages that must be dirty (modified) before the UBC starts writing them to disk. Usually the default works pretty well, however on systems with lots of filesystem/UBC activity increasing the value for this tunable may increase the ability to re-read from the filesystem cache. Recommended value is the default value of 10% but on systems which could benefit from keeping filesystem pages in the UBC it could be increased to 90%.vm_swap_eagerThis tunable controls on how the system will use the available swap space. For a database server environment the swap allocation mode could be chang ed from eager (the default) to …lazy‟ Setting “vm_swap_eager = 1”(default) indicates that system is in eager swap allocation mode.If you are running in eager swap allocation mode, sum the anonymous virtual memory for all processes and add at least 10% to size your swap space.Use Eager Swap Allocation mode for highly reliable systems that over commit memorySetting “vm_swap_eager = 0” indicates that the system is in deferred swap allocation modeIf you are running in lazy swap allocation mode, sum the anonymous virtual memory for all processes and subtract half of the physical memoryUse lazy swap allocation mode for any system that does not over commit memory. Ifvm_swap_eager has been set to …0‟ and the system would run out of available swap space apro cess which would attempt to allocate swap space will get killed. There‟s no mechanism to protect processes being deleted in this situation.Recommended setting in an ORACLE database server environment which has enough memory available is to set vm_swap_eager to ‘1’, however if the workload is well understood and the system has been configured with enough memory to prevent it from swapping vm_swap_eager can be set to ‘0’AdvFS SubsystemThe following tunables should be modified in the AdFS subsystemAdvfsSyncMmapPagesThis parameter controls on how AdvFS flushes dirty mmap()‟d pages using the sync() system call. Setting this parameter to …0‟ will prevent AdvFS from trying to flush pages of files which have been mmap()‟d. The default value of …1‟ wil l cause mmaped pager to be written asynchronously to disk during a sync() system call.As most applications which are using mmap() to map pages/files into memory are using their own synchronization via the fsync() call there‟s no need for AdvFS to perform the same operation again. It will also avoid AdvFS trying to flush pages which should actually stay in memory. Recommended value is ‘0’AdvfsCacheMaxPercentAdvfsCacheHashSizeAdvfsCleanupPercentAdvfsMaxFreeAccessPercentAdvfsMinFreeAccessThese tunables are defunct as of Tru64 V5.1 and are no longer used to tune/manage the AdvFS cache. Instead the UBC parameters ubc_maxpercent and ubc_minpercent should be used to tune the filesystem cacheVFS SubsystemThe following tunables should be modified in the VFS subsystemfifo_do_adaptivefifo_do_adaptive is one of the "tunables" where the default value may not been chosen perfectly if the system is running as a database server.The default setting of the fifo_do_adaptive parameter enables an alternate algorithms in the fifo routines. The intent has been to come up with an optimal "working set" size, and then attempt to perform fewer data transfer operations, but of a larger size.This works reasonably well for applications which perform data transfers of a uniform or near-uniform size. This does not work so well for *some* applications which perform data transfers of a random size (i.e.; specifically those which started out performing transfers such that the fifo code determined an optimal transfer size).This works poorly for some applications in which the peer processes operate in sync (meaning procA transfers to procB, then waits for procB's response).By disabling the fifo_do_adaptive parameter, performance for some applications will degrade,and for other applications will improve. The performance change depends on how the pipes get used. For ORACLE environments it has been proven to be beneficial to set this parameter to a value of …0‟Recommended value is to set fifo_do_adaptive to ‘0’IPC SubsystemThe following tunables should be modified in the IPC subsystemssm_thresholdssm_thresold controls which type of shared memory implementation is used the default in Tru64 V5.1 is to SSM (Segmented Shared Memory) memory. You only need to set a different value (…0‟) if large pages are being used. In this case ssm_threshold should be set to …0‟.Recommended value is to leave ssm_threshold at its default value on non GS series systems and if rad_gh_regions or gh_chunks are being used to s et ssm_threshold to ‘0’shm_maxThis tunable controls the maximum size for a single shared memory region. ORACLE will concatenate multiple shared memory regions if the SGA is larger then the value configured for shm_max. As of Tru65 V5.1 the size for a single shared memory segment could be larger than2GB. However other applications using shared memory on the same system may have problems with shared memory segments larger than 2GB, in order to avoid compatibility issues it is recommend to set the maximum size for an individual shared memory segment to 2GB Recommended value is 2GB – 8MB , ie. 2139095040 if only ORACLE is used on the system the size of shm_max can be increased to 4GB – 16MB ie. 4278190080.shm_mniThis tunable specifies the maximum number of shared memory regions that can be used on the system at one time.Recommended value for shm_mni is 256shm_segThis tunable specifies the maximum number of System V shared memory regions that can be attached to a single process at one time.Recommended value for shm_seg is 128INET SubsystemThe following tunables should be set in the inet subsystem. In addition the guidelines for tuning Internet server should be followed a document on how to tune high-performance Internet servers can be found at/docs/best_practices/BP_INTUNING/TITLE.HTM In addition recommendation specific to GigaBit Ethernet performance can be found at /docs/best_practices/BP_GIGABIT/TITLE.HTMudp_sendspaceDefault send buffer size for UDP requests. In environments using Gigabit Ethernet or very heavy network activity you may want to set the value for this tunable to an even higher default value than the one suggested.Recommended value for udp_sendspace is 65536udp_recvspaceDefault receive buffer size for UDP requests. In environments using Gigabit Ethernet or very heavy network activity you may want to set the value for this tunable to an even higher default value than the one suggested.Recommended value for udp_recvspace is 65536PROC SubsystemThe following tunables in the proc subsystem should be changed/adjustedper_proc_stack_sizePer process stack size in bytes. Usually the default value of 8MB for this tunable should be large enough for most ORACLE environments, however in very large installations and dataware house type environments the value should be increased.Recommended value for per_proc_stack_size is 33554432 (32MB)max_per_proc_stack_sizeMaximum per process stack size in bytes. Usually the default value of 32MB for this tunable should be large enough for most ORACLE environments, however in very large installations and dataware house type environments the value should be increased.Recommended value for max_per_proc_stack_size is 536870912 (512MB), depending on the ORACLE environment the maximum stack size can be increased to a maximum value of 3221225472 (3GB)per_proc_data_sizePer process data size in bytes. The value below will set this tunable to the amount of physical memory installed on the system. One could raise the value to a larger value than actual memory available, however this would allow a single process to outgrow a systems main memory and cause extensive swapping and paging. It is highly recommended to stay within the bounds of the available memory for this tunable. One should never raise the tunable to a value larger than the physical memory available plus the configured swap space.Recommended value for per_proc_data_size is 0x40000000000max_per_proc_data_sizeMaximum per process data size in bytes. The value below will set this tunable to the amount of physical memory installed on the system. One could raise the value to a larger value than actual memory available, however this would allow a single process to outgrow a systems main memory and cause extensive swapping and paging. It is highly recommended to stay within the bounds of the available memory for this tunable. One should never raise the tunable to a value larger than the physical memory available plus the configured swap space.Recommended value for max_per_proc_data_size is 0x40000000000per_proc_address_spacePer process address size in bytes. The value below will set this tunable to the amount of physical memory installed on the system. One could raise the value to a larger value than actual memory available, however this would allow a single process to outgrow a systems main memory andcause extensive swapping and paging. It is highly recommended to stay within the bounds of the available memory for this tunable. One should never raise the tunable to a value larger than the physical memory available plus the configured swap space.Recommended value for max_per_proc_data_size is 0x40000000000max_per_proc_address_spaceMaximum per process address size in bytes. The value below will set this tunable to the amount of physical memory installed on the system. One could raise the value to a larger value than actual memory available, however this would allow a single process to outgrow a systems main memory and cause extensive swapping and paging. It is highly recommended to stay within the bounds of the available memory for this tunable. One should never raise the tunable to a value larger than the physical memory available plus the configured swap space.Recommended value for max_per_proc_data_size is 0x40000000000max_proc_per_userMaximum number of processes/tasks a user can create (non-root users). To disable the limits for this tunable one can set it to …0‟.Recommended value for max_proc_per_user is 1024. If the application/environment requires more than 1024 tasks per user the value can be increased accordingly.max_threads_per_userMaximum number of threads a user can create (non root users). ). To disable the limits for this tunable one can set it to …0‟.Recommended value for max_threads_per_user is 4096. If the application/environment requires more than 1024 tasks per user the value can be increased accordingly. maxusersGeneric base tunable used to calculate various other data structures and tunables Recommended value for ES40 class systems and higher is 8192, the value can be increased to the maximum of 16.384.RT Subsystemaio_task_max_numMaximum number of concurrent active AIO tasks.Recommended value should be greater than the maximum of either the DBWR I/O operations or the value of the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, whichever is higher.The maximum number of DBWR I/O operations defaults to 8192 unless the_DB_WRITER_MAX_WRITES initialization parameter is specified.A simple formula for this tunable is:(DB_WRITER_MAX_WRITES (default 8192) * DB_WRITER_PROCESSES)+ (PARALLEL_MAX_SERVERS * DB_FILE_MULTIBLOCK_READ_COUNT)+ 10RDG Subsystemmax_objsAt least 5 times the number of Oracle processes per node and up to the larger of 10240 or the number of Oracle processes multiplied by 70.msg_sizeEqual to or greater than the maximum value of the DB_BLOCK_SIZE parameter for the database. Oracle Corporation recommends a value of 32768 because Oracle9i supports different block sizes for each tablespace.max_async_reqAt least 100.Note: A value of 256 might provide better performance. As of PK4/BL18 for TruCluster V5.1 the default value for this parameter has been raised to 1000.max_sessionsAt least the number of Oracle processes plus 2rdg_max_auto_msg_wiresMust be set to 0.RM subsystemrm_check_for_iplDue to an oversight the 9i/RAC installation guide states the value for the parameter must be set to ‘0’ . This is no longer true and the parameter should be left at its default value.Improving the Performance of gettimeofday() in ORACLEThe Oracle Server times many functions as it executes. This is especially true if the INIT.ORA parameter timed_statistics is set to TRUE.These timing functions result in system calls into the OS kernel which can degrade ORACLE performance because the calling process relinquishes the CPU.There is a feature in Tru64 UNIX that gives a process direct access to the operating systems' real time clock.Using this feature will improve performance on a heavily used system. It will also improve performance on a lightly loaded system but it may not be as noticeable.To enable this feature, login as root and issue the following command sequence: mknod /dev/timedev c 15 0chmod 644 /dev/timedevThe above commands only have to be performed for each system or node in a cluster.The /dev/timedev special file will be persistent across system rebootsIn order to use this feature with ORACLE the instance has to be restarted. The existence of/dev/timedev is checked only on instance startup. It is recommended that all instances in a cluster (and hence all nodes) have this feature enabled.This feature is supported on ORACLE versions V7.3 and beyond.Choosing and Enabling IPC Communication Protocols for 8i and 9i ORACLE can use either UDP or RDG (Reliable Datagram) for DLM/IPQ inter-instance communication. Starting with ORACLE 9i the recommendation is to use RDG as the protocol for IPC instead of using UDP.Although ORACLE 8.1.7 does support the use of RDG for communication it is not recommended to enable it but continue to use UDP.In some cases it might be necessary to use UDP as the communication protocol in a 9i environment.The commands below will show on how to enable/disable the different protocols for IPC.To disable NUMA support in ORACLE 8i or 9i use the following commands: cd $ORACLE_HOME/rdbms/libmake -f ins_rdbms.mk numa_offmake -f ins_rdbms.mk ioracleEnabling NUMA support in ORACLE is currently not supported for 8.1.7/OPS and 9.0.1/RAC installations. If you are planning to use RAC or OPS you must have NUMA disabled.To enable Oracle 9i RAC or 8.1.7 Oracle Parallel Servercd $ORACLE_HOME/rdbms/libFor ORACLE 8imake -f ins_rdbms.mk ops_onFor ORACLE 9imake -f ins_rdbms.mk rac_onmake -f ins_rdbms.mk ioracleTo make IPC use the UDP Protocol (default for ORACLE 8i OPS)make -f ins_rdbms.mk ipc_udpmake -f ins_rdbms.mk ioracleTo use Reliable DataGram (RDG) for IPC (default for 9i)make -f ins_rdbms.mk rac_onmake -f ins_rdbms.mk ioracleResources and ReferencesCompaq Websites and Resources∙Tru64 UNIX NUMA Technical Overviewo /faqs/publications/base_doc/DOCUMENTATION/V5 1_HTML/NUMA/TITLE.HTM∙Tru64 UNIX V5.1 Base Operating System Documentationo /faqs/publications/pub_page/V51_DOCS/V51_DOCL IST.HTM∙TruCluster V5.1 Documentationo /faqs/publications/cluster_doc/cluster_51/TCR51_DOC.HTM∙TruCluster ORACLE Best Practice Documentationso Configuring a single instance ORACLE 8i installation on TruCluster V5.1▪/faqs/publications/best_practices/BP_TCR_ORA_SS/TITLE.HTMo Configuring an ORACLE/OPS 8i installation on TruCluster V5.1▪/faqs/publications/best_practices/BP_TCR_ORA_OPS817/TITLE.HTM∙CFS DirectIO Technical Descriptiono /faqs/publications/cluster_doc/cluster_51/TCR51_DOC.HTMORACLE Websites and Resources∙ORACLE 8i Websiteo /ip/deploy/database/8i/index.html?ee.html∙ORACLE Technet 8i Websiteo /products/oracle8i/o /faqs/publications/cluster_doc/cluster_51/TCR51_DOC.HTM∙Products & Serviceso /o /∙What's In Shippingo /prod_avail/as_qry.htm@search.as_init∙Eventso /corporate/seminars_and_events/index.html?content.html ∙Technical Documents (certify, whitepapers,fact sheets, benchmarks)o /o /o /o /appsnet/index.html∙Marketing, Press Releases, Success Stories,Collateralo /corporate/press/index.html?content.htmlo /succst-wwwprd-dcd/plsql/query_success_frame.show_frame_all。