Storage performance assessment
When the storage performance assessment, we use the disk performance index (DPI, DISK Performance Index). The following table lists the various indices in the DPI. This score system does not mean the full-scale assessment of the use and allocation of the disk. However, representing a barometer, reflecting whether there is a place where the current disk is used and whether there is a need to improve or pay attention.
MPI index
classification
Required level
Highest score
Adjustment table and index
Yes
30
Table line connection problem
no
30
Separate key Oracle file
Yes
30
Rollback segment balance
30
Temporary section balance
30
Use the top 10 SQL disk usage
<5%
60
Whether to adjust the top 25 SQL using the most disk
Yes
40
MPI index
Total
250
1. Adjustment table and index
Since the data blocks of the tables and indexes are typically read simultaneously, they should be placed on different disks as much as possible to reduce the I / O conflict of the file.
Inspection Method:
Select I.index_name, t.table_name, t.tablespace_name from user_tables t, user_indexes I where t.table_name = i.table_name and t.tablesPACE_NAME = i.tablespace_name;
The return result is the table and associated indexes created in the same tablespace. It is recommended to create new tablespaces to specifically store index and put the current index Rebuild to the newly created table space.
Alter Index IDX_Name Rebuild TableSpace TS_NAME;
Evaluation Guidelines:
grade
fraction
Table and index are placed on the same disk
0
Store the disk array, no further adjustment
20
Store the disk array, has been adjusted for the RAID type
30
Tables and indexs have been planned on different disks
30
2. Table link problem
When updating a table, there is no sufficient remaining space in the data block to accommodate the modification, the "row link" phenomenon occurs, which is linked to another data block with sufficient space, that is, one The record spans multiple data blocks so that more I / O will consume more I / O when reading the record. When there is a large number of "row links" in the database, the overall performance of the database will decline.
Inspection Method:
SQLPlus / NOLOG
Connect App_User / Password
SQL> @ $ oracle_home / rdbms / admin / utslchain.sql
SQL> Analyze Table
SQL> SELECT Count (*) chained_rows, table_name from chained_rows group by table_name
If no row is returned, it means that there is no "row link" phenomenon. Otherwise, the "row link" is displayed in each table in accordance with the analyzed table.
The "Row Link" phenomenon generates an improper relationship with the PCTFree parameters. The PCTFREE value defaults to 10%. If there is a large number of row links in the system, the block reserved space specified by this parameter is too small, not enough to accommodate all records in the block. The PCTFREE value of the corresponding table should be increased.
Evaluation Guidelines:
grade
fraction
Row link phenomenon
0
Do not exist line link phenomenon
30
3. Separate key Oracle file
Whether it is for security or performance, it is recommended to distribute key Oracle files on the available separate disks.
First after the error occurs, the data file used to be recovered and the control file used to recover, the archive log file, the archive log file should be separated. If possible, distribute the following key files on different disks. System Table Space (SYSTEM), Temporal Temperature (TEMP), Rolling Table Space (Undo), Online Refine Log File (Redo), and Archive Log File (ARCH), often accessible user tablespace, frequently accessible user index Table space, operating system disk, key Oracle software file in ORACL_EBASE.
At least online Relive log file (REDO) and archive log files (ARCH) should be stored on different disks with other files, and because most of the time of the log file is only write attributes, it is necessary to consider the weakness of RAID5 in writing. Try not to store the log file on the array group of RAID5.
Inspection Method:
select file_name, tablespace_name, bytes from dba_data_filesunion allselect file_name, tablespace_name, bytes from dba_temp_filesunion allselect name file_name, NULL, NULL from v $ controlfileunion allselect member file_name, to_char (a.group #) tablespace_name, b.bytes bytes from v $ logfile a, v $ log b where a.group # = b.group # union all (select value file_name, nULL, nULL from v $ parameter where name like 'log_archive_dest_%' and value is not null minus select value file_name, nULL, nULL from v $ parameter where name like 'Log_archive_Dest_State%');
Returns the location of all critical files in the database, and the result returned by DBA and SA, confirming that the storage location of the key file has been adjusted in accordance with the actual situation.
Evaluation Guidelines:
grade
fraction
No adjustment, all on a single disk
0
No adjustments, all on RAID
20
Have been adjusted
30
4. Rollback segment balance
Before Oracle 9i and Oracle9i, if there is no automatic management of the rollover segment, the performance of the rollback segment is still required to monitor and adjust.
Check if you use a returned segment automatic management:
Select Name, Value from V $ Parameter Where Name Like '% UNDO_%'
If the value of the undo_management is auto in the return result, it means that the rollback segment is automatically managed, and the undo_tablespace value shows the backup scroll space that automatically manages, the undo_retrion value shows the time limit for retaining the rollback data in the rolling table space In seconds.
Note: If the value of undo_management is auto but undo_tablespace does not set the corresponding value, then use the System table space in the System table space, this is absolutely avoided. If no return segment is automatically managed, you need to monitor the frequency of users who use the rollback segment, in principle, it is considered that more than 1 user should not use 1 return segment.
Inspection Method:
Select A.Name, B.Extents, B.rsize, B.xActs, B. Waits, B.Gets, Optsize, Status from V $ ROLLNAME A, V $ ROLLSTAT B Where A.usn = B.USN;
Check the output result, for all returns, if the XACTS (Active Transaction) and Waits are often exceeded by 1, then it means that the number of returning segments is needed to avoid possible contention.
Method for increasing the rollback segment:
Create Rollback Segment RS_Name TableSpace Rbs Storage (Initial
1
M
NEXT
2
M
); alter rollback segment rs_name online;
If you use a rollback segment, you can query the usage and allocation of the current rollback segment from the views of V $ undostat, V $ ROLLSTAT, DBA_UNDO_EXTENTS.
Evaluation Guidelines:
grade
fraction
Have a returning segment waiting phenomenon
0
No return segment waiting phenomenon
30
Automatic management of returns
30
5. Temporary section balance
When the Sort_Area_SIZE size defined in the initialization parameter cannot meet the space of the sort required, the temporary segment in the temporary table space will be used, and the disk sort is slower than 100,000 times, so the disk sort is the performance adjustment work. An important part.
It may cause sorted operations with Create Index, Distinct, Order By, Group By, etc.
Inspection Method:
SELECT NAME, VALUE from V $ sysstat where name like '% sorts%';
Returning Sorts (Memory) indicates memory sorting, and sorts (disk) represents disk sorting, if there is a large number of disk sort, it indicates that we need to increase the size of the sort zone such as sort_area_size, or you need to check the current system Whether the SQL of a large number of disks has been adjusted (check the SQL of the first 25-bit disk) will be mentioned later).
Check the session information that uses disk sort, you can locate a session of a large disk sort.
Inspection Method:
Select B.Name, A.SID, A. Value from V $ SESSSTAT A, V $ statname b Where a.statistic # = b.statistic # and b.Name = 'sorts (disk)' and A.Value> 0 Order By A.Value DESC;
If possible, we should distribute multiple temporary data files in the temporary table space on different disks to reduce the disk conflicts that may be generated when sorting.
In Oracle9i, we can set PGA_AGGREGATE_SIZE initialization parameters to specify that all sessions will use the PGA size, but also set the Workarea_Size_Policy parameter to Auto. Other details see the "4. Sort" section in the memory performance assessment.
Evaluation Guidelines:
grade
fraction
There is no assessment for the existing disk sort
0
Adjustment has been adjusted on the existing disk
30
6. The top 10 statements of the most waste disk read operation accounts for the proportion of all statements
Usually in an optimized system, 10 most commonly used SQL statements account for more than 50% of the disk read operations across the system. These SQLs are the most important part of the optimization, and it is also a high priority in optimization. Usually our optimized goal is to reduce the percentage of these SQL disk read operations to 5-19%.
Inspection Method:
select sum (pct_bufgets) from (select rank () over (order by disk_reads desc) as rank_bufgets, to_char (100 * ratio_to_report (disk_reads) 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%
0
7. Adjust the first 25 most waste disk read operations
In the case where there is no adjustment, in most systems, the disk read operation of the number of statements accounting for the first 25-bit sentences will occupy 75% of the entire system, which is critical to adjust this part of the statement. This part of the script is used to get the number of SQL statements that account for the top 25 bits. EXEC in the output result indicates the number of times the SQL is executed.
Inspection Method:
set serveroutput on size 1000000declare execution number; top25 number; text1 varchar2 (4000); x number; len1 number; cursor c1 is select executions, disk_reads, substr (sql_text, 1, 4000) from v $ sqlarea order by disk_reads desc; begin dbms_output .put_line ('exec' || '' || 'TEXT'); DBMS_OUTPUT.PUT_LINE ('-----' || '' || "-------- - '||' '||' ------------- '; Open C1; for i in 1 .. 25 loop fetch c1 int1; dbms_output.put_line RPAD (TO_CHAR (Execution), 5) || '|| RPAD (TO_CHAR (TOP25), 8) ||' '|| 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;
Evaluation Guidelines:
There is no specific assessment criterion, which requires developers or DBA to confirm that the statement that belongs to the application system in these 25 SQL has been tuned. 8. Other storage related adjustments
1) The total number of extents in the dictionary management table space is not more than 4096
Inspection Method:
select a.tablespace_name, sum (a.extents) from dba_segments a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.extent_management = 'DICTIONARY' group by a.tablespace_name order by sum (a.extents);
Check the output result, if the total number of EXTENTs in a table space exceeds 4096, then it is necessary to expand the extent size of this table space, excessive Extent has a negative impact on the spatial management of DMT.
2) The number of EXTENTs in the local management table space does not exceed 1024
Inspection Method:
select segment_name, segment_type, extents, bytes, b.tablespace_name from dba_segments a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.extent_management = 'LOCAL' and a.extents> 1024;
Check the output result, the returned record is an object of a single segment greater than 1024. For these objects, a separate table space with larger Extent size should be created, and then these objects move to the new table space.
3) Check if the Pctincrease value of the dictionary management table space is 0
For all EXTENTs in the table space, it is recommended that all segments in the tablespace do not set a stand-alone Storage parameter. For the PctinCrease parameters of the table space, it is recommended to set to 0, and the MineXtents parameter should be set to ensure that the initial allocation of sufficient space gives the newly created object.
For the LMT table space, the PctincRease and next parameters in the Storage parameter are invalid, and it is recommended to set the appropriate UNIFORM parameter management table space Extent assignment.
4) Consider using partitions to avoid disks
The partition table has strong practicability in the improvement of the convenience and performance of management, and can even think that the partition is the best way to improve the performance related to large tables. By accessing a small segment of a table or an index, the partition can improve efficiency well without accessing the entire table or index. If a table or index partition is on a different disk, it can greatly increase data throughput and get good database performance.
For partitioning, please refer to other partition documents for the purpose of use.
5) Whether the non-partition key index of the partition table is a global partition index
Since the data volume of the partition table is usually huge, if the index is created on the non-partition key of the partition table, it is recommended to create a global partition index, which can better improve performance. Note: If a partition is truncated or deleted a partition, you must Rebuild's all global indexes in this partition table, otherwise these global indexes will be in the Invalid status, resulting in the use of these indexed SQL statements fail.
