本站内容搜索:
   您的位置:素材中国>>教程 >>数据库 >>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专题  论坛

网站介绍 | 广告业务 | 设计业务 | 免责声明 | 版权声明 | 联系我们|提交错误报告
素材中国版权所有