Data | database | performance
Storage performance Assessment
When storing performance evaluations, we use the Disk Performance index (DPI, disk Performance Index), and the following table lists the indices in the DPI, which do not imply a full assessment of the use and allocation of the disk, but represent a barometer, Reflects whether there is a need for improvement or attention in the use and allocation of the current disk.
MPI Index
Classification
Required level
Highest score
Adjusting tables and indexes
Is
30
Row join problem for table
No
30
Separating critical Oracle Files
Is
30
Balance of the rollback segment
30
Balance of the temporary paragraph
30
Disk usage with the top 10 SQL
<5%
60
Have you adjusted the top 25 SQL with the most disks?
Is
40
MPI Index
Score
250
1. Adjusting tables and indexes
Because the data blocks for tables and indexes are usually read at the same time, you should try to place the table and its associated indexes on separate disks to reduce the file's I/O conflict.
Check 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 result is a table created in the same table space and an associated index. It is recommended that you create a new tablespace to store the index specifically and rebuild the current index into the newly created tablespace.
Alter index IDX_NAME rebuild tablespace ts_name;
Evaluation criteria:
Grade
Scores
Tables and indexes on the same disk
0
Storage uses disk array, no further adjustments
20
Storage uses a disk array and has been adjusted for RAID types
30
Tables and indexes are already planned on different disks
30
2. Row link problem for table
When a table is updated and there is not enough space in the block to accommodate the changes made, a "row link" phenomenon occurs that is linked to another block of data that has enough space, that is, a record spans multiple blocks of data, which consumes more I/O when reading the record. When there is a large number of "row link" phenomena in the database, the overall performance of the database will decrease.
Check method:
Sqlplus/nolog
Connect App_user/password
sql> @ $ORACLE _home/rdbms/admin/utlchain.sql
sql> Analyze table <table_name> list chained rows;
Sql> Select COUNT (*) Chained_rows, table_name
From Chained_rows
GROUP BY TABLE_NAME;
If no rows are returned, there is no "row link" behavior. Otherwise, you will follow the tables that have been analyzed to show how many records in each table appear with the row link phenomenon.
The occurrence of the "row link" phenomenon is related to improper setting of pctfree parameters. The Pctfree value defaults to 10%, and if there is a large number of row links in the system, the block reserved for this parameter is too small to hold the update operation for all records in the block. You should increase the Pctfree value of the corresponding table at this point.
Evaluation criteria:
Grade
Scores
There is a row link phenomenon
0
No row link phenomenon exists
30
3. Separating critical Oracle Files
Regardless of security considerations or performance considerations, it is recommended that you distribute critical Oracle files on available stand-alone disks.
First, after the error occurs, the data file to be recovered and the control file used for recovery are redo log files, and the archive log files should be separated and stored. If possible, distribute the following key files on different disks.
System tablespaces (systems), temporary tablespace (temp), rollback tablespace (UNDO), online redo log files (REDO) and archive log files (ARCH), frequently accessed user tablespace, frequently accessed user Index table space, OS disk, $ORACL _ Key Oracle software files in the ebase.
At least online redo log files (REDO) and archive log files (ARCH) should be stored on separate disks from other files. And because most of the log files are write-only, you need to consider RAID5 's weakness in writing, and try not to store the log files on the RAID5 array group.
Check method:
Select file_name, Tablespace_name, bytes
From Dba_data_files
UNION ALL
Select file_name, Tablespace_name, bytes
From Dba_temp_files
UNION ALL
Select name file_name, NULL, NULL
From V$controlfile
UNION ALL
Select 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 the key file stores in the database, the results of the DBA and SA Review, confirming that a realistic adjustment has been made to the location where the key files are stored.
Evaluation criteria:
Grade
Scores
No adjustments, all on a single disk
0
No adjustments, all on raid
20
has been adjusted
30
4. Balance of the rollback segment
If the rollback segment is not used for automatic management prior to Oracle 9i and Oracle9i, the performance of the rollback segment still needs to be monitored and adjusted.
Check that the rollback segment is used for automatic management:
Select name, value from V$parameter where name like '%undo_% ';
If the value of undo_management in the returned result is auto, the rollback segment is used for automatic management, and the Undo_tablespace value shows the rollback table space used by the auto management, Undo_ The retention value shows the time, in seconds, to retain the rollback data in the rollback table space.
Note: If the value of Undo_management is auto but Undo_tablespace does not set the corresponding value, then the system rollback segment in the system table space is used, which is an absolutely unavoidable phenomenon.
If you do not use the rollback segment for automatic management, you need to monitor how often users use the rollback segment, and in principle think that no more than 1 users should use 1 rollback segments at the same time.
Check method:
Select A.name,
B.extents,
B.rssize,
B.xacts,
B.waits,
B.gets,
Optsize,
Status
From V$rollname A, V$rollstat b
where A.usn = B.usn;
Check the output, and for all rollback segments, if Xacts (active transaction) and waits (segment header wait) often exceed 1, it indicates the need to increase the number of rollback segments to avoid possible contention.
Ways to increase the rollback segment:
Create rollback segment Rs_name tablespace RBS storage (initial 1M next 2M);
Alter rollback segment Rs_name online;
If you are using rollback segment automation, you can query the usage and allocation of the current rollback segment from views such as V$undostat, V$rollstat, dba_undo_extents, and so on.
Evaluation criteria:
Grade
Scores
There is a rollback segment waiting phenomenon
0
No rollback segment Wait phenomenon
30
Use rollback segment Automatic management
30
5. Balance of the temporary paragraph
When the sort_area_size size defined in the initialization parameter does not meet the sorting requirements, the temporary segments in the temporary table space are sorted and the disk sort is 100-10000 times slower than the memory sort, so minimizing disk sorting is an important part of the performance tuning effort.
The operations that may cause sorting are create INDEX, distinct, order by, group by, and so on.
Check method:
Select name, value from V$sysstat where name like '%sorts% ';
The sorts (memory) in the return result represents the sort of memory, while sorts (disk) indicates the sort of disks, and if there is a large number of disk sorts, we need to increase the size of the sort areas such as sort_area_size or hash_area_size. Or you need to check that the SQL that is consuming a large amount of disk in your system is already tuned (check the first 25-bit SQL for the disk in the later section).
Check session information that uses disk sorting to locate sessions that perform a large number of disk sorting.
Check method:
Select B.name, A.sid, A.value
From V$sesstat 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 from a temporary tablespace to different disks to reduce the number of disk conflicts that might occur when sorting.
In Oracle9i, we can set the Pga_aggregate_size initialization parameter to specify the PGA size that all sessions will use, and must also set the Workarea_size_policy parameter to auto. Additional details are shown in the memory performance assessment "4. In-Memory sorting "section.
Evaluation criteria:
Grade
Scores
Not evaluated for existing disk sort
0
The existing disk sort has been adjusted
30
6. The first 10 statements that are the most wasteful of disk reads account for the proportions of all statements
Typically, in a system without optimization, the 10 most commonly used SQL statements have access to more than 50% of the disk read operations throughout the system. These are the parts that need to be optimized most, and the high priority part of the optimization effort. Usually our optimization goal is to reduce the percentage of disk read operations for these SQL to 5-19%.
Check method:
Select SUM (pct_bufgets)
From (select rank () disk_reads desc) as Rank_bufgets,
To_char (Ratio_to_report (disk_reads) 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 first 25 statements that are the most wasteful of disk read operations
Without adjustment, in most systems, the disk read operation of the first 25 bits of the statement will occupy 75% of the entire system's disk read operations, and 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. The exec in the output result indicates the number of times the SQL was executed.
Check method:
Set serveroutput on size 1000000
Declare
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 ' | | ' ' || ' Reads ' | | ' ' || ' Text ');
Dbms_output.put_line ('-----' | | ' ' || '--------' || ' ' ||
'-------------');
Open C1;
For I in 1. Loop
Fetch C1
into execution, TOP25, Text1;
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;
End
/
Evaluation criteria:
There is no specific evaluation guideline for this section, and a developer or DBA is required to confirm whether the statements that belong to the application system in these 25 SQL have been tuned.
8. Other storage-related adjustments
1 The total number of extent in the Dictionary management table space is not more than 4096
Check 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);
Checking output, if the total number of extents in a table space is more than 4096, the extent size of the tablespace needs to be enlarged, and too much extent has a negative impact on the space management of DMT.
2 The extent number of individual segement in the local management table space does not exceed 1024
Check 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 and return records that are objects with an interval greater than 1024 for a single segment, and for those objects, create a separate tablespace with a larger extent size, and move those objects to the new tablespace.
3) Check the Pctincrease value of the Dictionary management table space is 0
For all extent sizes in the table space, it is recommended that you do not set separate storage parameters for all segments in the tablespace. For table space Pctincrease parameters, the recommendation is set to 0, and the minextents parameter should be set to ensure that enough space is initially allocated to the newly created object.
For LMT Tablespace, the pctincrease and next parameters in the storage parameter are invalid, and it is recommended that you set the appropriate uniform parameter to manage the extent allocation of the tablespace.
4 Consider using partitions to avoid disk contention
Partitioned tables are more useful in managing convenience and performance, and can even be considered partitioning is the best way to improve performance related to large tables. Partitioning can be a good way to improve efficiency by accessing smaller fragments of a table or index, rather than accessing the entire table or index. If a table or index partition is on a different disk, it can greatly increase data throughput and achieve good database performance.
For the use of partitions, temporarily out of the scope of this article, see the other partition documentation.
5 is the global Partitioning index for the non-partitioning key index of the partitioned table
Because the data volume of partitioned tables is usually large, it is recommended that you create a global partitioning index if you create an index on a non-partitioning key on a partitioned table, which can improve performance better. NOTE: If you truncate a partition's data or delete a partition, you must rebuild all the global indexes in the partitioned table, otherwise the global indexes will be in invalid state, causing the SQL statements that use those indexes to fail.
