数据库性能调整专家必读

• 　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 1

  　　DBA PERFORMANCE TUNING FOR THE EXPERT ONLY：

  　　BEGINNERS WILL BE SMOKED！

  　　Richard J. Niemiec， TUSC

  　　ABSTRACT

  　　Version8 of the Oracle database has brought on a whole new level of issues for the DBA. While the queries for

  　　tuning the database and individual queries has not changed much， the data retrieved by these queries has changed and

  　　must be analyzed for partitioned tables and other cost-based optimizer functions. This paper will serve to give you

  　　the individual queries to be successful.

  　　WHAT WILL BE COVERED （GOALS FOR TUNING）

  　　Goal#1： Have enough memory allocated to Oracle - The first goal should be to get enough memory （from your

  　　physical hardware） allocated to “key” Oracle parameters. We will look at how to see what the current settings of a

  　　given system are set to and also look at the “key” parameters： DB_BLOCK_BUFFERS， SHARED_POOL_SIZE，

  　　and SORT_AREA_SIZE.

  　　Goal#2： Get the data loaded into memory - Once you have enough memory allocated to Oracle， the focus must shift

  　　to ensuring that the most important information is getting into memory and staying there. We will look at using x$bh

  　　and using the ‘cache’ parameter of ‘alter table……’ to investigate this area.

  　　Goal#3： Find queries that are clogging memory and causing I/O - Finding problem areas is， at times， the most

  　　difficult problem. We will investigate a method for easily identifying the bottlenecks by using v$sqlarea.

  　　Goal#4： Tune the Problem Queries - Tuning the problem queries could easily encompass an entire training course. I

  　　will focus on a couple of key areas： What you need to know before you tune my system， using the Parallel Query

  　　Option and general tuning tips.

  　　Function Based Indexes - This new feature in Oracle8.1 can be a big help.

  　　Materialized Views - This feature in Oracle8.1 can help with large tables.

  　　GOAL#1： HAVE ENOUGH MEMORY ALLOCATED TO ORACLE

  　　Even if the system that you are working on has 10 Gig of memory available， this doesn‘t help much if only a small

  　　portion of it is allocated to Oracle. We allocate memory to Oracle through the INITsid.ORA file. Some of the key

  　　parameters are listed below. We will cover each of these parameters in the following sections. By going to

  　　“v$parameter” or by using the either Server Manager or Oracle Enterprise Manager， we can find the parameters that

  　　affect Oracle‘s performance.

  　　A. FINDING THE VALUES OF ‘KEY’ INIT.ORA PARAMETERS

  　　select name， value

  　　from v$parameter

  　　where name in （‘db_block_buffers’，……etc）；

  　　NAME VALUE

  -------------------------------------------------- ----------------

  　　db_block_buffers 4000

  　　db_block_size 4096

  　　shared_pool_size 7000000

  　　sort_area_size 262144 .

  　　You can also view the init.ora parameters in Oracle‘s Enterprise Manager as shown below：

  　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 2

  　　B. LOOK AT DB_BLOCK_BUFFERS

  　　The first parameter to look at is the INITsid.ORA parameter： DB_BLOCK_BUFFERS. This is the area of the SGA

  　　that is used for the storage and processing of data in memory. As users request information， the information is put

  　　into memory. If the DB_BLOCK_BUFFERS parameter is set too low， then the least recently used data will be

  　　flushed from memory. If the data flushed is recalled with a query， it must be re-read from disk （causing I/O and

  　　CPU resources to be used）。 If DB_BLOCK_BUFFERS is too low， users will not have enough memory to operate

  　　efficiently. If DB_BLOCK_BUFFERS is too high， your system may begin to swap and may come to a halt.

  　　DETERMINE IF THE DATA BLOCK BUFFERS IS SET HIGH ENOUGH

  　　select 1-（sum（decode（name， 'physical reads'， value，0））/

  　　（sum（decode（name， 'db block gets'， value，0）） +

  　　（sum（decode（name， 'consistent gets'， value，0））））） * 100

  　　"Read Hit Ratio"

  　　from v$sysstat；

  　　Read Hit Ratio

  　　98.415926

  　　Although hit ratios below 90-95% are usually a sign of poor indexing； Distortion of the hit ration numbers is possible.

  　　See the next section for more information.

  　　Response Time in Minutes

  　　Buffers at

  　　200% of

  　　Optimum

  　　Buffers at

  　　Optimum

  　　Buffers at

  　　50% of

  　　Optimum

  　　Buffers at

  　　20% of

  　　Optimum

  　　Buffers at

  　　5% of

  　　Optimum

  　　0

  　　100

  　　200

  　　300

  　　400

  　　Figure 1： Response Time for a Memory Intensive Report with given SGA （Buffer） settings

  　　HIT RATIO DISTORTION

  　　Even though the equations for finding a problems seems easy， sometimes the results are not accurate. Many third

  　　party products also receive this misinformation， yet some go to other areas to get the correct information. Below， I

  　　show one such case where misinformation is returned.

  　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 3

  　　There are also false hit ratio distortions. SQL*Forms can cause a false high hit ratio， rollback segments can cause a

  　　false high hit ratio impact and indexes can have hit ratios as high as 86% when none of the blocks were cached prior

  　　to the query executing.

  　　C. IT IS IMPORTANT TO LOOK AT THE SHARED_POOL_SIZE FOR PROPER SIZING

  　　With a greater amount of procedures， packages and triggers being utilized with Oracle， the SHARED_POOL_SIZE

  　　makes up a much greater portion of the Oracle SGA. This is the memory allocated for the library and data dictionary

  　　cache. If the SHARED_POOL_SIZE is set too low then you will not get the full advantage of your

  　　DB_BLOCK_BUFFERS.

  　　DETERMINE DICTIONARY CACHE MISS RATIO

  　　select sum（gets） “Gets”， sum（getmisses） “Misses”，

  　　（1 - （sum（getmisses） / （sum（gets） +

  　　sum（getmisses））））*100 “HitRate”

  　　from v$rowcache；

  　　Gets Misses HitRate

  　　10233 508 95.270459

  　　This would be a good Ratio and would probably not require action in this area.

  　　DETERMINE LIBRARY CACHE HIT RATIO

  　　select sum（pins） Executions， sum（pinhits） “Execution Hits”，

  　　（（sum（pinhits） / sum（pins）） * 100） phitrat，

  　　sum（reloads） Misses，

  　　（（sum（pins） / （sum（pins） + sum（reloads））） * 100） hitrat

  　　from v$librarycache；

  　　Executions Execution Hits PHITRAT Misses HITRAT

  　　3，582 3，454 96.43 6 99.83

  　　If the hit ratio or reloads is high， increase the shared_pool_size INIT.ora parameter.

  　　HOW MUCH MEMORY IS LEFT FOR SHARED_POOL_SIZE

  　　col value for 999，999，999，999 heading “Shared Pool Size”

  　　col bytes for 999，999，999，999 heading “Free Bytes”

  　　select to_number（v$parameter.value） value， v$sgastat.bytes，

  　　（v$sgastat.bytes/v$parameter.value）*100 “Percent Free”

  　　from v$sgastat， v$parameter

  　　where v$sgastat.name = 'free memory'

  　　and v$ parameter .name = ‘shared_pool_size；

  　　Shared Pool Size Free Bytes Percent Free

  　　100，000，000 82，278，960 82.27896

  　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 4

  　　A BETTER QUERY

  　　select sum（ksmchsiz） Bytes， ksmchcls Status

  　　from x$ksmsp

  　　group by ksmchcls；

  　　BYTES STATUS

  　　350，000 R-free

  　　40 R-freea

  　　25，056 free

  　　2，571，948 freeabl

  　　4，113，872 perm

  　　1，165，504 recr

  　　If there is free memory then there is no need to increase this parameter.

  　　You can also view the init.ora parameters in Oracle‘s Enterprise Manager as shown below. The add/modify chart

  　　and the result of this query are shown in the two displays below.

  　　D. TRY TO SORT IN MEMORY INSTEAD OF IN TEMPORARY

  　　The INIT.ora parameter SORT_AREA_SIZE will allocate memory for sorting （per user / as needed）。 This is the

  　　area that is the space allocated in main memory for each process to perform sorts. If the sort cannot be performed in

  　　memory， temporary segments are allocated on disk to hold intermediate runs. Increasing the value of sort_area_size

  　　will reduce the total number of disk sorts， thus reducing disk I/O. This can cause swapping， if to little memory is left

  　　over for other processes. Statements that will generate Temporary Segments include： Create Index， Select …… Order

  　　By， Distinct， Group By， Union， Unindexed Joins， Some Correlated Subqueries. Since temporary segments are created to

  　　handle sorts that cannot be handled in memory， the initial extent default for temporary segments should be at least as large as the value of

  　　sort_area_size. This will minimize extension of the segment.

  　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 5

  　　GOAL#2： GET DATA “CACHED” INTO MEMORY

  　　Once you have enough memory allocated to Oracle， the focus must shift to ensuring that the most important

  　　information is getting into memory and staying there. We will look at using x$bh and using the ‘cache’ parameter of

  　　‘alter table……’ to investigate this area below：

  　　A. TO SEE HOW FAST THE SGA GETS USING X$BH

  　　select state， count（*）

  　　from x$bh

  　　group by state；

  　　STATE COUNT（*）

  --------- -----------------

  　　0 371

  　　1 429

  　　In the above result：

  　　Total DB_BLOCK_BUFFERS = 800

  　　Total that have been used = 429

  　　Total that have NOT been used = 371

  　　A BETTER QUERY：

  　　select decode（state，0， 'FREE'， 1， decode（lrba_seq，0，'AVAILABLE'，'BEING USED'），

  　　3， 'BEING USED'， state） "BLOCK STATUS"， count（*）

  　　from x$bh

  　　group by decode（state，0，'FREE'，1，decode（lrba_seq，0，

  　　'AVAILABLE'，'BEING USED'），3， 'BEING USED'， state）；

  　　BLOCK STATUS COUNT（*）

  　　AVAILABLE 779

  　　BEING USED 154

  　　FREE 167

  　　You can also view the init.ora parameters in the Performance Manager inside Oracle‘s Enterprise Manager as shown

  　　below：

  　　B. USING THE ‘CACHE’ PARAMETER OF ‘ALTER TABLE……’）

  　　If you find that “key” tables are being pushed out of memory， you may need to “pin” them into memory using the

  　　CACHE parameter. When you use this parameter， full table scans result in being placed on the “Most recently used”

  　　list instead of the “Least recently used” list. This keeps them in memory for future use. The following examples

  　　investigate the syntax and uses of this command：

  　　EXAMPLE 1 （CREATE A TABLE WITH CACHE）

  　　CREATE TABLE TEST_TAB （COL1 NUMBER）

  　　TABLESPACE USERS

  　　CACHE；

  　　Deploying， Managing， and Administering the Oracle Internet Platform

  　　Paper #224/ Page 6

  　　NOCACHE is the Default！

  　　EXAMPLE 2 （ALTER A TABLE TO CACHE）

  　　ALTER TABLE TEST_TAB

  　　CACHE；

  　　EXAMPLE 3 （THE CACHE HINT）

  　　SELECT /*+ CACHE（CUST） */ ENAME， JOB

  　　FROM CUST

  　　WHERE TABLE_NAME = 'EMP'；

  　　EXAMPLE 4 （THE NOCACHE HINT）

  　　SELECT /*+ FULL（CUST） NOCACHE（CUST） */ ENAME， JOB

  　　FROM CUST

  　　WHERE TABLE_NAME = 'EMP'；

  　　GOAL#3： FIND PROBLEM QUERIES “HURTING” MEMORY

  　　A single index or a single query can bring an entire system to a near standstill. By using v$sqlarea， you can find the

  　　problem queries on your system. Below， the example shows how to find the problem queries. I am searching for

  　　queries where the disk reads are greater than 10，000. If your system is much larger， you may need to set this to a

  　　higher number.

  　　EXAMPLE 5 （FINDING THE LARGEST AMOUNT OF PHYSICAL READS BY QUERY）

  　　select disk_reads， sql_text

  　　from v$sqlarea

  　　where disk_reads > 10000

  　　order by disk_reads desc；

  　　DISK_READS SQL_TEXT

  ------------------ ------------------------------------------------------------
  

• [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] 下一页 
• 复制本页网址和标题，发送给你QQ/Msn的好友一起分享
  
  
• 数据库性能调整专家必读 相关文章：
• ·数据库性能调整专家必读
• 数据库性能调整专家必读 相关软件

上一篇:MySQL中修改密码及访问限制设置

下一篇:SQL注入天书之ASP注入漏洞全接触

• 特别声明：本站除部分特别声明禁止转载的专稿外的其他文章可以自由转载，但请务必注明出处和原始作
• 者.文章版权归文章原始作者所有.对于被本站转载文章的个人和网站，我们表示深深的谢意。如果本站转
• 载的文章有版权问题请联系编辑人员,我们尽快予以更正. 转载请注明来源：http://www.hackhome.com

精品推荐

热点TOP10

特别推荐