Database Performance Check Guidance Scheme
Author: kamus
Date: 2004-9
After the system is stable, check the product database every month in accordance with this guidance scheme.
This guidelines apply to Oracle9i databases because some scripts can run in 9i.
The inspection method is to execute a command script in SQLPLUS after logging in to the database as a SYSDBA ("Check Method" section of each section has a detailed command script).
Command to log in to the database:
SQLPlus "Sys / Password As Sysdba"
One. Memory performance assessment
When the memory performance assessment, we use the memory performance index (MPI, Memory Performance Index), which lists the various indices in the MPI, which does not mean all-round assessments for the use and allocation of memory, Only a barometer reflects the use and allocation status of the current system memory.
MPI index
classification
Required level
Highest score
Buffer Cache> 98% 30 Data Data Dictionary (Dictionary Cache)> 98% 30 Bank Slow His Library Cache> Sort in Memory (Sort In Memory> 98% 30 Idle Data buffer ratio 10-25% 30 Use the maximum of the first 10 SQL occupied memory <5% 60 Has the first 25 SQLs that have been adjusted using the most 25 SQL is 30 whether to fix frequently used in the cache is 10 MPI index Total score 250
1. Buffer hit rate
The percentage of non-disk read (buffer hit) is shown in the total reader of data. Of course, very high hit rate does not represent the database performance must be excellent, and it is possible to be a bad SQL to cause a large buffer reading. This hit rate can better reflect the database performance after the first query has been adjusted. .
Inspection Method:
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 "Hit Ratio" from V $ sysstat;
Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 95-98% 20> 98% 30
2. Data word code
The percentage of the internal memory reading of the data dictionary and other objects is shown.
Inspection Method:
Select (1 - (summisses) / sum (gets)) * 100 "Hit Ratio" from v $ rowcache;
Evaluation Guidelines:
grade
fraction
<85% 0 86-92% 10 92-98% 20> 98% 30
3. Subcommitte
The percentage of the memory read operation of SQL and PL / SQL objects is shown. Also note that a high hit rate does not always reflect the database performance.
Inspection Method:
Select SUM (Pins) / (SUM (Pins) SUM (Reloads)) * 100 "Hit Ratio" from V $ librarycache
Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 94-98% 20> 98% 30
4. Sort in memory
Depending on the value of the initialization parameter PGA_AGGREGATE_TARGET or SORT_AREA_SIZE, the user's sorting operation may be executed in memory, or it may be executed in the temporary table space. This check is used to display the percentage of the total sorting in the memory.
Inspection Method:
Select A.Value "Disk Sorts", B.Value "Memory Sorts", ROUND ((100 * B.Value) / Decode (A.Value B.Value), 0, 1, (A.Value B. Value)), 2) "PCT Memory Sorts" from V $ SYSSTAT A, V $ sortstat b where a.name = 'sorts (disk)' and b.name = 'sorts (Memory)';
Evaluation Guidelines:
grade
fraction
<90% 0 90-94% 10 94-98% 20> 98% 30
5. Idle data buffer ratio
The number of idle recording is divided by the total number of records in the X $ BH (i.e., the total number of the assigned data block buffers). Also note that databases with many idle buffers are not necessarily an optimal environment, because it may be that the buffer is set too large, waste memory.
Inspection Method:
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)
Evaluation Guidelines:
grade
fraction
<5% 0 5-19% 30 20-25% 20> 25% 0
6. The top 10 statements of the most wasteful memory account for the proportion of all memory reading
Usually in an optimization system, 10 most commonly used SQL statements will account for more than 50% of the entire system. These SQLs are the most important part of the optimization, and it is also a high priority in optimization.
Inspection Method:
select sum (pct_bufgets) from (select rank () over (order by buffer_gets desc) as rank_bufgets, to_char (100 * ratio_to_report (buffer_gets) over (), '999.99') pct_bufgets from v $ sqlarea) where rank_bufgets <11;
Evaluation Guidelines:
grade
fraction
<5% 60 5-19% 50 20-25% 30> 25% 07. Adjust the prior 25 most wasteful memory
In the case where there is no adjustment, in most systems, the internal memory of the number of statements accounts for 75% of the entire system, and the other statement is adjusted is critical. This part of the script is used to get the number of SQL statements that account for the top 25 bits.
Inspection Method:
set serveroutput on size 1000000declare top25 number; text1 varchar2 (4000); x number; len1 number; cursor c1 is select buffer_gets, substr (sql_text, 1, 4000) from v $ sqlarea order by buffer_gets desc; begin dbms_output.put_line ( 'Gets '||' '||' Text '); DBMS_OUTPUT.PUT_LINE (' ------ '||' '|| "-------------') Open C1; for i in 1 .. 25 Loop Fetch C1 INTO TOP25, TEXT1; DBMS_OUTPUT.PUT_LINE (RPAD (TO_CHAR (TOP25), 9) || '|| Substr (Text1, 1, 66)); LEN1: = Length (Text1); x: = 66; while len1> x - 1 loop dbms_output.put_line ('"| | s); x: = x 66; end loop; End loop; END; /
Evaluation Guidelines:
This section does not evaluate the standard, requiring developers or DBA to confirm that the statement that belongs to the application system in this 25 SQL has been tuned.
8. Fixed cache object
Try the object that is fixed (PIN) in the memory, including tables, stored procedures, and more.
Search for objects that need to be greater than 100K continuous space in the shared pool:
Select * from v $ db_object_cache where share_mem> 100000 and type in ('package', 'package body ",' procedure ',' function '
Investigate the result of returning, confirm whether there is a PIN to the shared pool, return the key in the result, if YES, then indicating that the object is fixed in the shared pool, and NO, indicating that it is not fixed.
If you need to fix it, use the following statement:
Exec dbms_shared_pool.keep ('sys.standard');
The DBMS_Shared_Pool package is not created when the database is default, so you need to create the package first.
CD $ oracle_home / rdbms / admin
SQLPlus "/ as sysdba"
@ dbmspool.sql
If we want to fix the table, you can use the Cache key when you create a table or modify the table properties, place the table to the MRU side of the Buffer Cache's LRU list. Usually we need this operation for smaller but frequently used tables. Alter Table Table_name Cache;
We can also place frequently used tables to another independent buffer cache, such as a Keep pool. This operation allows the data of these tables to not be quickly cleared from the default buffer cache.
ALTER TABLE TABLE_NAME Storage (Buffer pool Keep);
Evaluation Guidelines:
This section does not evaluate the standard, developer or DBA is required to be cautious after system analysis.
two. Storage performance assessment
three. Ten contents that need to be viewed first in the StatsPack report
This article reference:
Oracle9i Performance Tuning Tips & Techniques - Richard J.NIEMIEC
Oracle9i Database Concepts - Tahiti.Oracle.com
Oracle9i Database Reference - Tahiti.Oracle.com
