Data | database | performance
Database Performance Check Guidance program
Author:kamus
Date:2004-9
After the system is stabilized, the product database should be inspected once a month in accordance with this guidance plan.
This guidance applies to oracle9i databases, because some scripts can be run in 9i.
The check mode is to execute the command script in Sqlplus after logging into the database as SYSDBA (the "Check method" section of each section has a detailed command script).
command to log in to the database:
Sqlplus "Sys/password as SYSDBA"
A Memory Performance Assessment
In memory performance evaluation, we use the Memory Performance index (MPI, Memory Performance Index), the following table lists the various indices in the MPI, this scoring system does not imply a full evaluation of the use and allocation of memory, but merely represents a barometer, Reflects the current system memory usage and allocation status.
MPI Index
Classification
Required level
Highest score
Buffer hit ratio (buffers cache)
>98%
30
Data dictionary hit Ratio (Dictionary Cache)
>98%
30
Gallery Cache Hit ratio (library cached)
>98%
30
In-memory sorting (sort in Memory)
>98%
30
Percentage of free data buffers
10-25%
30
Memory consumed by the top 10 SQL
<5%
60
Have you adjusted the top 25 SQL with the most
Is
30
Whether to try objects that are used frequently in a fixed cache
Is
10
MPI Index
Score
250
1. Buffer hit Ratio
Shows the percentage of non-disk reads (buffer hits) for the total amount of data read. Of course, very high hit rates do not represent a good database performance, but also may be bad SQL caused a large number of buffer read operations, only after the first query has been adjusted, this hit rate can better reflect the database performance.
Shows the percentage of memory read operations on the data dictionary and other objects.
Check method:
Select (1-(SUM (getmisses)/sum (gets)) * Hit Ratio
From V$rowcache;
Evaluation criteria:
Grade
Scores
<85%
0
86-92%
10
92-98%
20
>98%
30
3. Library Cache Hit Ratio
Shows the percentage of memory read operations on SQL and Pl/sql objects. Also note that a high hit rate does not always reflect good database performance.
Check method:
Select SUM (Pins)/(sum (Pins) + sum (reloads)) * "Hit Ratio"
From V$librarycache;
Evaluation criteria:
Grade
Scores
<90%
0
90-94%
10
94-98%
20
>98%
30
4. In-memory sorting
Depending on the value of the initialization parameter pga_aggregate_target or sort_area_size, the user's sort operation may be performed in memory or in a temporary tablespace. This check shows the percentage of the total sorted in memory sorted.
Check method:
Select A.value "Disk sorts",
B.value "Memory sorts",
Round ((* b.value)/
Decode ((A.value + B.value), 0, 1, (A.value + b.value)),
2) "Pct Memory sorts"
From V$sysstat A, V$sysstat b
where a.name = ' sorts (disk) '
and b.name = ' sorts (memory) ';
Evaluation criteria:
Grade
Scores
<90%
0
90-94%
10
94-98%
20
>98%
30
5. Percentage of free data buffers
The number of idle records divided by the total number of records in the X$BH table (that is, the total number of allocated block buffers) is the percentage of the free buffer. Also note that a database with a large number of free buffers is not necessarily the best environment, because the buffer may be set too large to waste memory.
Check 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 criteria:
Grade
Scores
<5%
0
5-19%
30
20-25%
20
>25%
0
6. The ratio of the top 10 statements that are most wasteful of memory to the total amount of memory read
Typically, in a system without optimization, the 10 most commonly used SQL statements have access to more than 50% of the memory read operations throughout the system. These are the parts that need to be optimized most, and the high priority part of the optimization effort.
Check method:
Select SUM (pct_bufgets)
From (select rank () buffer_gets desc) as Rank_bufgets,
To_char (Ratio_to_report (buffer_gets) over (), ' 999.99 ') pct_bufgets
From V$sqlarea)
where Rank_bufgets < 11;
Evaluation criteria:
Grade
Scores
<5%
60
5-19%
50
20-25%
30
>25%
0
7. Adjust the top 25 most wasted memory statements
In the absence of adjustment, in most systems, the memory read operation of the first 25-bit statement will occupy 75% of the whole system's memory read operation, it is important to adjust this part of the statement. This section of the script is used to obtain the first 25 bits of the SQL statement.
Check method:
Set serveroutput on size 1000000
Declare
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. 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 (' "' | | SUBSTR (Text1, X, 66));
X: = x + 66;
End Loop;
End Loop;
End
/
Evaluation criteria:
This section does not evaluate the guidelines and requires a developer or DBA to confirm whether the statements that belong to the application system in these 25 SQL have been tuned.
8. Fixed Cache object
An attempt to fix (PIN) frequently used objects in memory, including tables, stored procedures, and so on.
Retrieves objects that require more than 100K contiguous space in the shared pool:
SELECT *
From V$db_object_cache
where Sharable_mem > 100000
and type in (' PACKAGE ', ' PACKAGE body ', ' PROCEDURE ', ' FUNCTION ');
Review the results returned, confirm whether you need a pin to the shared pool, return the kept field in the result if yes, it means that the object is already pinned to the shared pool, and no 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 was not created when the database was installed by 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, we can use the Cache keyword when creating the table or modifying the table properties to place the table in the MRU end of the LRU list on the buffer cache. Typically, we need to do this for smaller, but frequently used, tables.
ALTER TABLE TABLE_NAME cache;
We can also place frequently used tables in another separate buffer cache, such as the Keep pool. This allows the data in these tables to not be purged quickly from the default Buffer Cache.
ALTER TABLE TABLE_NAME storage (buffer pool keep);
Evaluation criteria:
This section does not evaluate the guidelines and requires a developer or DBA to implement them carefully after system analysis.
Two Storage performance Assessment
Three 10 items that need to be viewed first in the Statspack report
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
