±¾Õ¾ÄÚÈÝËÑË÷£º
   ÄúµÄλÖãºËزÄÖйú>>½Ì³Ì >>Êý¾Ý¿â >>MS SQL >>Êý¾Ý¿âÐÔÄܵ÷Õûר¼Ò±Ø¶Á Ìá½»´íÎ󱨸æ
Êý¾Ý¿âÐÔÄܵ÷Õûר¼Ò±Ø¶Á
[ À´Ô´£ºËزÄÖйú | ×÷Õߣº| ʱ¼ä£º2006-01-12 14:15:21 | ä¯ÀÀ£ºÈË´Î ]

 
 

¡¡¡¡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.

 
 
       
   ÄúµÄλÖãºËزÄÖйú>>½Ì³Ì >>Êý¾Ý¿â >>MS SQL >>Êý¾Ý¿âÐÔÄܵ÷Õûר¼Ò±Ø¶Á
 µã´ËÔÚ°Ù¶ÈËÑË÷¹Ø¼ü×Ö"Êý¾Ý¿âÐÔÄܵ÷Õûר¼Ò±Ø¶Á"  µã´ËÔÚGOOGLEËÑË÷¹Ø¼ü×Ö"Êý¾Ý¿âÐÔÄܵ÷Õûר¼Ò±Ø¶Á"
ÈÈÃÅÎÄÕ£º
  ¡¤¶þ½øÖÆ×ªÊ®½øÖƵÄSQLº¯Êý   ¡¤SQL2005 SSIS
  ¡¤¶ÔÊý¾Ý¿â×Ö¶ÎʹÓÃĬÈÏÖµ   ¡¤exp/impµ¼³öµ¼È빤¾ßµÄʹÓÃ
  ¡¤¾­³£Óõ½µÄ½»²æ±íÎÊÌâ,Ò»°ãÓö¯Ì¬SQLÄÜÉú³É¶¯Ì¬ÁÐ   ¡¤¾«»ªÈ«Ãæ½Ó´¥SQLÓï·¨
  ¡¤ÔÚ´æ´¢¹ý³ÌÖÐÁ¬½ÓÔ¶³ÌÊý¾Ý¿â²¢½øÐвÙ×÷   ¡¤SqlServerµÄ¸üÐÂËø(UPDLOCK)
  ¡¤¼¸ÖÖ·ÖÒ³Ëã·¨¡£·­Ò³±Ø±¸   ¡¤¹ØÓÚSQL Server SQLÓï¾ä²éѯ·ÖÒ³Êý¾ÝµÄ½â¾ö·½°¸

  Ê×Ò³  ËزÄͼƬ  ¸ß¾«Í¼¿â  Ê¸Á¿Í¼¿â  ÍøÒ³ËزĠ ÍøÒ³Ä£°å  ±ÚÖ½  Ã÷ÐÇ  ÏÂÔØ  ½Ì³Ì  ×ÖÌå  Ïã³µÃÀÅ®  QQרÌâ  ÂÛ̳

ÍøÕ¾½éÉÜ | ¹ã¸æÒµÎñ | Éè¼ÆÒµÎñ | ÃâÔðÉùÃ÷ | °æÈ¨ÉùÃ÷ | ÁªÏµÎÒÃÇ|Ìá½»´íÎ󱨸æ
ËØ²ÄÖйú°æÈ¨ËùÓÐ