数据库性能调整专家必读

2009-12-14 18:44| 发布者: admin| 查看: 20| 评论: 0|原作者: 琴姬

○Deploying，Managing，a......

　　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
------------------ ------------------------------------------------------------
-----
　　12987 select order#，columns，types from orders
　　where substr（orderid，1，2）=：1
　　11131 select custid， city from customer
　　where city = ‘CHICAGO’
　　EXAMPLE 6 （FINDING THE LARGEST AMOUNT OF LOGICAL READS BY QUERY）
　　select buffer_gets， sql_text
　　from v$sqlarea
　　where buffer_gets > 200000
　　order by buffer_gets desc；
　　BUFFER_GETS SQL_TEXT
------------------ ------------------------------------------------------------
-----
　　300219 select order#，cust_no， from orders
　　where division = ‘1’
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 7
　　GOAL#4： TUNE THE PROBLEM QUERIES
　　A. WHAT YOU NEED TO KNOW BEFORE YOU TUNE YOUR SYSTEM
　　The first thing you need to know is the data. The volume of data and the distribution of data will affect how you
　　tune individual queries. You also need to have a “shopping cart" full of tuning methods to try. Multiple approaches
　　must be made to cover all types of queries. A single method of tuning or a single tuning product is not enough. You
　　also need to know where the system is slow. Many DBAs and developers spend endless hours finding problem
　　queries instead of asking the users of the system. Users will almost always be happy to volunteer this information.
　　You also need to network with other developers that work on a similar system. Sharing information at user groups is
　　a great way to network.
　　B. USING “KEY” HINTS FOR OPTIMIZATION
　　Eventually， you will find a query that requires specific tuning attention. When the query is found， you must take
　　advantage of the “hints” that Oracle offers for tuning individual queries.
　　FULL - Force a Full Table Scan
　　SELECT /*+ FULL（table_name） */ column1， column2 ……
　　INDEX - Force an Indexed Search
　　SELECT /*+ INDEX（table_name index_name1 index_name2……） */
　　ORDERED - Force the driving table as in FROM clause
　　SELECT /*+ ORDERED */ column1， column2 ……
　　FROM table1， table2
　　ALL_ROWS - Explicitly chooses the cost-based approach with a goal of best throughput.
　　Select /*+ ALL_ROWS */ ……
　　FIRST_ROWS - Explicitly chooses the cost-based approach with a goal of best response time.
　　Select /*+ FIRST_ROWS */ ……
　　？ Note： The optimizer ignores this hint in ‘delete’ and ‘update’ statements， and in select statements that contain any
　　of the following： set operators， group by clause ， for update， group functions and distinct operators.
　　C. THE DRIVING TABLE
　　In v7， the cost-based approach uses various factors in determining which tables should be the driving table （the table
　　that drives the query） in a multi-table join query. The best thing to remember is to realize that you have control over
　　which table will drive the query through the use of the ORDERED hint. No matter what the order is from the
　　optimizer， that order can be overridden by the ORDERED hint. The key is to use the ORDERED hint and vary the
　　order of the tables to get the correct order from a performance standpoint.
　　select tabA.col_1， tabB.col2
　　from tabA， tabB
　　where tabB.col2 = ‘ANL’；
　　select /*+ ORDERED */
　　tabA.col_1， tabB.col2
　　from tabA， tabB
　　where tabB.col2 = ‘ANL’；
　　Sometimes the optimizer “goes to lunch”：
　　Rule based explain plan：
　　186 Lines （4 hours）
　　Use of the “ORDERED” HINT：
　　7 Lines （35 sec.）
　　？ By using the ORDERED hint and varying the order of the tables in the FROM clause of the query， you can
　　effectively find out which driving table is best for your query.
　　D. PARALLEL QUERY
　　Oracle‘s parallel query option has opened up a new avenue for performance enhancements. DBAs can now spread a
　　CPU intensive report across many processors， taking advantage of the full speed of the box. You can also use the
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 8
　　PARALLEL=TRUE with DIRECT=TRUE with SQL*Loader. On the down side， you can also take down a ten
　　processor box with a single query using this. The queries listed below should give you the general syntax and uses for
　　the PARALLEL hint.
　　EXAMPLE7 （USING THE PARALLEL HINT； PARALLELISM DEGREE IS 4）
　　SELECT /*+ FULL（CUST） PARALLEL（CUST， 4） */
　　ENAME， JOB
　　FROM CUST
　　WHERE TABLE_NAME = 'EMP'；
　　FUNCTION-BASED INDEXES （ORACLE8I）
　　One of the largest problems with indexes is that the indexes are often suppressed by developers. Developers using
　　the UPPER function can suppress an index on a column for a given query. In Oracle8i， there is now a way to combat
　　this problem. Function-based indexes allow you to create an index based on a function or expression. The value of
　　the function or expression is specified by the person creating the index and is stored in the index. Function-based
　　indexes can involve multiple columns， arithmetic expressions or may be a PL/SQL function or C callout. The
　　following example shows an example of a function based index.
　　Creating the Function-based Index
　　CREATE INDEX emp_idx ON emp （UPPER（ename））；
　　An index has been created on the ename column when the UPPER function is used on this column.
　　Query the emp table using the Function-based Index：
　　select ename， job， deptno
　　from emp
　　where upper（ename） = ‘ELLISON’；
　　The function-based index （emp_idx） can be used for the query above. For large tables where the condition retrieves a
　　small amount of records， the query yields substantial performance gains over a full table scan.
　　？ 8i Tip： Function-based indexes can lead to dramatic performance gains when used to create indexes on functions
　　often used on selective columns. See Chapter 13 for additional Oracle8i performance enhancements.
　　To comprehend the advantages of function-based indexes consider the following queries.
　　EXAMPLE8 （USING THE FUNCTION-BASED INDEXES）
　　select count（*）
　　from sample
　　where ratio（balance，limit） >.5；
　　Elapse time： 20.1 minutes
　　We create a functional index.
　　create index ration_idx on sample （ ratio（balance， limit））；
　　We re-run the query using the function-based index.
　　select count（*）
　　from sample
　　where ratio（balance，limit） >.5；
　　Elapse time： 7 seconds！！！
　　Note that the function RATIO simply divides argument 1 by argument 2.
　　ORACLE8I： MATERIALIZED VIEWS AND QUERY REWRITE
　　The combination of Materialized Views and Query Rewrite are power tools for the Oracle data warehouse in
　　Oracle8i. Materialized views can be used to create and automatically refresh summary fact tables （the central table in
　　a data warehouse）。 Query Rewrite allows the Oracle optimizer to modify queries against the larger detail tables that
　　can be completely satisfied by a smaller summary table. Oracle uses the summary table instead of going to the larger
　　detail table which can improve performance substantially.
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 9
　　In the example below， the detail table contains a count of households at a zip code and zip+4 level. The materialized
　　view， ZIP， summarizes the household count at a zip code level. As the explain plans show， Oracle will access the ZIP
　　materialized view rather then the ZIP4_COUNT table for the following query：
　　EXAMPLE9 （USING MATERIALIZED VIEWS）
　　Create the larger ZIP4_COUNT table：
　　CREATE TABLE ZIP4_COUNT
　　AS
　　SELECT ZIP， ZIP4， SUM（HH_CNT） HH_CNT
　　FROM TEST2
　　GROUP BY ZIP， ZIP4；
　　Create the smaller ZIP materialized view：
　　CREATE MATERIALIZED VIEW ZIP
　　BUILD IMMEDIATE
　　ENABLE QUERY REWRITE
　　AS
　　SELECT ZIP， SUM（HH_CNT）
　　FROM ZIP4_COUNT
　　GROUP BY ZIP；
　　In the preceding query， we have created a materialized view called zip. This materialized view is a summary of the
　　ZIP4_COUNT table. We have also enabled Oracle to rewrite a query （unless overriden with a NOREWRITE hint）
　　that can take advantage of this view. In the following two queries， we will query the table using the NOREWRITE
　　and REWRITE hints.
　　Query the ZIP4_COUNT table disallowing rewrites of the query：
　　SELECT /*+ NOREWRITE */ ZIP， SUM（HH_CNT）
　　FROM ZIP4_COUNT
　　GROUP BY ZIP；
　　SELECT STATEMENT Optimizer=CHOOSE
　　TABLE ACCESS （FULL） OF 'ZIP4_COUNT'
　　Elapsed Time： 0.28 seconds
　　In the query above， we disallow Oracle's ability to rewrite the query. Hence， the ZIP4_COUNT （the larger nonsummarized）
　　table is accessed.
　　SELECT /*+ REWRITE */ ZIP， SUM（HH_CNT）
　　FROM ZIP4_COUNT
　　GROUP BY ZIP；
　　SELECT STATEMENT Optimizer=CHOOSE
　　TABLE ACCESS （FULL） OF 'ZIP'
　　Elapsed Time： 0.03 seconds
　　In the preceding example， Oracle rewrites the query to go to the smaller ZIP materialized view which improves the
　　performance of query substantially.
　　As the example above shows， Query Rewrite can improve performance by several orders of magnitude. If your
　　database makes use of summary tables， building Materialized Views to take advantage of Oracle's Query Rewrite
　　capability is a feature you will want to investigate when you upgrade to the Oracle8i database engine. Author's note：
　　This section was added to this chapter rather than the Oracle8i chapter on the final day of edits （this is the only
　　chapter they would let me edit - remember Casablanca）。 My apologies for inconveniences in its placement.
　　The following init.ora parameters must be set to use materialized views and function-based indexes.
　　query_rewrite_enable = true
　　query_rewrite_integrity = trusted
　　OTHER TUNING TIPS
　　The FIRST_ROWS hint will generally force the use of an index where it normally would not have been used by the
　　Optimizer （But it definitely depends on the query）。 The ALL_ROWS hint will generally NOT use an index where it
　　normally would have been used by the Optimizer （But it definitely depends on the query）。 Which index the optimizer
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 10
　　uses may depend on which one was created first. Although this seems unbelievable， it has been validated by a
　　multitude of developers and DBAs. Build the most unique index FIRST （future versions will probably correct this）！
　　Moving the .DLLs to the Client Machine will almost always make a client-server application faster， but it also makes
　　the client “fatter.” In a multiple database environment， it is important to use views to access remote tables （keeps
　　Oracle from moving the entire table between databases）。
　　TUNING USING SIMPLE MATHEMATICAL TECHNIQUES
　　This section （which is covered in detail in Chapter 9） discusses some simple but effective mathematical techniques
　　you can use to significantly improve the performance of some Oracle SQL-based systems. These techniques can
　　leverage the effectiveness of Oracle performance diagnostic tools and uncover hidden performance problems that can
　　be overlooked by other methods. It also makes it easier to make performance predictions at higher loads. This
　　section was provided by Joe A. Holmes. I am extremely grateful for his contribution as I believe it ties all of tuning
　　together.
　　The methodology， called Simple Mathematical Techniques， involves isolating and testing the SQL process in question
　　under ideal conditions， graphing the results of rows processed versus time， deriving equations using simple methods
　　（without regression）， predicting performance， and interpreting and applying performance patterns directly to tuning
　　SQL code.
　　SIMPLE QUADRATIC EQUATION DETERMINATION
　　The following is a simple three-point method for determining a quadratic best performance equation：
　　y = a0 + a1x + a2x2
　　This equation can be calculated for any query using the techniques detailed in Chapter 9 of the book so that you can
　　retrieve one of several possible graphs for a given query. Consider some of the graphs in the figure below and
　　problems that are detailed in the table which follows.
　　Pattern in Figure 3 Possible Problem Possible Solution
　　A Missing Index on a query
　　SELECTing values
　　Create an index. Fix a suppressed
　　index
　　A Over-indexed table suffering
　　during an INSERT
　　Delete some of the indexes or
　　index less columns （or smaller
　　columns） for the current indexes.
　　B No Problem. Don‘t touch it！
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 11
　　C Missing Index on a query
　　SELECTing values
　　Create an index. Fix a suppressed
　　index
　　C Over-indexed table suffering
　　during an INSERT
　　Delete some of the indexes or
　　index less columns （or smaller
　　columns） for the current indexes.
　　D Doing a FULL table scan or using
　　the ALL_ROWS hint when you
　　shouldn‘t be.
　　Try to do an indexed search. Try
　　using the FIRST_ROWS hint to
　　force the use of indexes.
　　E The query was fine until some
　　other limitation （such as disk I/O
　　or memory） was encountered.
　　You need to find which ceiling
　　that you hit to cause this problem.
　　Increasing the SGA may solve the
　　problem， but this could be many
　　things.
　　PATTERN INTERPRETATION
　　Graphical performance patterns provide clues to underlying SQL problems and solutions. Our ultimate goal in using
　　these methods is to convert a steep linear or quadratic best performance line to one that is both shallow and linear by
　　optimizing the SQL process. This may involve experiments with indexes， TEMP tables， optimizer HINT commands，
　　or other methods of Oracle SQL performance tuning.
　　With pattern interpretation， it is important to do your own application specific SQL experiments to develop an
　　expertise at using these methods. The following are more specific interpretations based on my personal experience
　　that provide a basic idea of how to apply what is observed directly to tuning SQL code. Provided the scale is correct，
　　pattern interpretation will often provide a more accurate picture of what is actually happening to a process and may
　　support or even contradict what a diagnostic tool may tell you.
　　An upward sloping （concave） quadratic curve almost always indicates a problem with the process because， as more
　　rows are added the time to process each additional row increases. If the sloping is very small， the equation may be
　　more linear. However， a very slight bowing may be an indicator of something more insidious under much higher
　　volumes.
　　In rare cases a quadratic curve might appear downward sloping （convex） indicating a process where as more rows are
　　added the time to process each additional one decreases， i.e. economies of scale. This is desirable and may occur at a
　　threshold where a full table scan is more efficient than using an index.
　　？ Tip： If you want an Oracle symphony as great as Beethoven‘s， you must learn and know how to apply
　　mathematical techniques to your tuning efforts. You don‘t have to learn everything that you learned in college
　　calculus， simply apply the simple equations in this chapter to tie everything in this book together. Thank you Joe
　　Holmes for doing the math for us （detailed with examples in Chapter 9 of the book）！
　　NIEMIEC‘S 7 RULES OF TUNING
　　Rule 1： The level of tuning achieved can be directly attributable to the number of straight hours that you can work
　　and how much junk food is available.
　　Rule 2： The level of tuning achieved is tremendously increased if user input is solicited and those users are NOT of
　　the type that try to be politically correct （i.e. You need users that are not afraid to say that this report runs horribly！）。
　　Rule 3： The level of tuning achieved can be directly attributable to the security access to the system that the tuning
　　professional has.
　　Rule 4： The level of tuning achieved is severely hampered by the level of theoretical knowledge required by the tuning
　　professional.
　　Rule 5： The level of tuning achieved is severely hampered by the amount of time that a manager is present.
　　Rule 6： The level of tuning achieved by the number of keyboards， terminals， monitors and PC‘s that are within the
　　reach of the tuning professional.
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 12
　　Rule 7： The usual attributes of a good tuning professional （outside of actual performance） can usually be spotted by
　　the person who； calculates the shortest line at McDonalds； calculates the most efficient method for getting each task
　　done yet still leaves at 1am； has coupons for every pizza place that stays open 24 hours at their desk； tends to use
　　twice as much coffee grounds when making the coffee or uses caffeine enhanced water when making the coffee； asks
　　if you would like to go to lunch when it is time for dinner； answers email with a single or half sentence （never aparagraph）； has an occasional triple digit weekly hours reported； has no time to be political； and when they have one
　　hour left to go with a problem， you can guarantee that you better multiply by at least four.
　　TUNING SUMMARY
　　Since a single query or a poorly setup INIT.ora can bring system to its knees， the key to tuning often comes down to
　　how effectively you can tune the database memory and also those single problem queries. You must remember to
　　tune both the INIT.ora parameters as well as the actual queries. To tune effectively， you must know your DATA
　　since your system is UNIQUE. You must adjust methods to suit your system. A single index or a single query can
　　bring an entire system to a near standstill. Find those queries with v$sqlarea！
　　Deploying， Managing， and Administering the Oracle Internet Platform
　　Paper #224/ Page 13
　　REFERENCES
　　Performance Tuning Tips and Techniques； Richard J. Niemiec， Oracle Press： ISBN： 0-07-882434-6
　　PL/SQL Tips and Techniques； Joseph C. Trezzo， Oracle Press
　　Oracle Application Server； Bradley D. Brown， Oracle Press
　　Joe Holmes； Oracle Query Tuning using Mathematical Techniques， Select Magazine
　　TUSC Internal Oracle DBA Guide， TUSC 1993，1994，1995
　　Server Manager ； Brad Brown， TUSC
　　Tuning Oracle； Corey， Abbey， Dechichio
　　Performance Tuning； Now YOU are the Expert， Undocumented Index Suppression， Rich Niemiec， TUSC； 1991
　　Get the most for your Money： Utilize the V$ Tables； Joseph C. Trezzo； TUSCTuning an Oracle Database； Sue Jang；
　　Oracle Corporation
　　Version 6 & 7 DBA， Migration and Performance Tuning Guides， Oracle Corporation
　　IOUG Proceedings； Multiple Downsizing and Distributed Database Articles
　　Oracle7 Internals； Oracle Corp.； Craig A. Shallahamer
　　Oracle 7.1 Release Features Parallel Everything； Integrator； Summer 1994
　　Tuning Oracle for Batch and On-Line Processing； Eyal Aronoff； Select Magazine
　　Tuning Oracle in the Land of Expert Systems； Monty Carolan， Richard Niemiec， Dave Kaufman， TUSC
　　SPECIAL THANKS TO
　　Brad Brown， Joe Trezzo， Randy Swanson， Burk Sherva， Jake Van der Vort， Greg Pucka and the TUSC Team who
　　have all made contributions to this document. Dave Kaufman， Sean McGuire and Mike Henderson for help in the
　　INIT.ORA section of this article
　　ABOUT THE AUTHOR：
　　Richard J. Niemiec （niemiecr@tusc.com） is the Executive Vice President of The Ultimate Software Consultants
　　（TUSC）， a Lombard， Illinois based database consulting company. TUSC specializes in the full cycle of database
　　development including Business Modeling， Design， Development， Implementation and Support. Richard has been
　　giving lectures and presentations on Oracle for the past 8 years and is the current President of the Midwest Oracle
　　Users Group （MOUG）。 Rich can be reached at TUSC at （630） 960-2909 （www.tusc.com）。
　　Please report errors in this article to TUSC. Neither TUSC nor the author warrant that this document is error-free.

收藏分享邀请

		自动登录	找回密码
密码			注册

数据库性能调整专家必读

最新评论

相关分类

下级分类