After a period of operation for a while, the user complained that some operation response speeds were too slow; this did not have similar problems in the previous period, so suspected that the amount of data was too large. However, only more than 30,000 data volume in the relevant business table is not sufficient to constitute a bottleneck that affects the slower response speed. More strangely, the import method is used to load this table data, but did not find the above phenomenon, I can't understand.
A few days later, I accidentally read a magazine, in which a paragraph - "Whenever the SQL statement is sent to the DB2 database manager, the SQL optimizer will read the system catalog to determine the referenced. The characteristics of the column and the index have been defined during the referenced table, and the size of each table referenced by the statement is included. According to these information, the optimizer can estimate each of the SQL statements needed. The cost of the data access path, then recommend the best one. The optimizer is used to make decisions. The database statistics collection data is a key element in the system catalog table. Therefore, the changes in statistics may result in changes in the selection of access paths. If the information is lost or out, the optimizer may be selected will cause the SQL statement to execute the time than normal. For example, a delete operation may leave an empty data page that cannot be used later. For various lengths The field can be updated to cause the new field value to be stored in the same data page. This will cause some rows to be moved to different page and generate internal gaps or unused spaces. Therefore, DB2 has to go Read more physical pages to retrieve the data required by the application. " In conjunction with this problem, the physical table involved in this operation is indeed often increasingly deleted, is it because of this reason? I have learned about the contents of the table reorganization and running statistics, knowing that DB2 has RunStats and REORG tools to complete the operating statistics and reorganization of the table. So I did the following experiments:
- 1 First check if you want to reorganize the data REORGCHK CURRENT Statistics on Table DB2Admin.t_ckd to get the statistics and index statistics of the table shown below:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table statistics:
Table statistics:
F1: 100 * Overflow / Card <5
F2: 100 * Tsize / (FPAGES-1) * (TablePagesize-76)> 70
F3: 100 * NPAGES / FPAGES> 80
CREATOR NAME CARD OV NP FP TSIZE F1 F2 F3 REORG
-------------------------------------------------- ------------------------------
DB2ADMIN T_CKD 1 0 1 12 9 0 0 8 - **
-------------------------------------------------- ------------------------------
Index statistics:
F4: Clusterratio or normal CLUSTERFAACTOR> 80
F5: 100 * (keys * (isize 8) * 4) / (NLEAF * Indexpagesize)> 50f6: (100 100esesize-96) / (isize 12) ** ( NLEVELS-2) * (Indexpagesize-96) / (Keys * (isize 8) * 4) <100
Creat Name Card Leaf Lvls Isize Keys F4 F5 F6 REORG
-------------------------------------------------- ------------------------------
Table: DB2Admin.t_ckd
DB2ADMIN XAK1T_CKD 1 1 2 28 1 100 - ---
DB2ADMIN XIE1T_CKD 1 1 1 10 1 100 - ----
DB2ADMIN XIE2T_CKD 1 1 1 10 1 100 - ----
DB2ADMIN XIE3T_CKD 1 1 1 4 1 100 - ----
DB2ADMIN XIE4T_CKD 1 1 1 18 1 100 - ---
SYSIBM SQL010510174815750 1 1 2 28 1 100 - ---
-------------------------------------------------- ------------------------------
Clusterratio or normal ClusterFactor (f4) will indicate indexes require REORG, which is not in the same sequence in the same sequence. When multiple indexes are defined in the table, one or more indexs may be marked as needed as needed. Specifies the most important index of the REORG order.
You can see that F1 <5, F2> 70, F3> 80 in the table statistics, and F1 = 0, F2 = 0, F3 = 8 cannot meet the requirements, and most of the F4, F5, F6 of the index cannot be Meet the requirements, you must re-statistically
---- 2 Reissue database table index
REORG TABLE DB2ADMIN.T_CKD INDEX DB2ADMIN.XIE3T_CKD
---- 3 Re-statistical index
Runstats on Table DB2Admin.t_ckd and indexes all
---- 4 After re-statistics, you can look at the information of the data table. Reorgchk Current Statistics on Table DB2Admin.t_ckd Gets the statistics and indexes of the table display:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table statistics:
Table statistics:
F1: 100 * Overflow / Card <5
F2: 100 * Tsize / (FPAGES-1) * (TablePagesize-76)> 70
F3: 100 * NPAGES / FPAGES> 80CREATOR NAME CARD OV NP FP TSIZE F1 F2 F3 REORG
-------------------------------------------------- ------------------------------
DB2ADMIN T_CKD 4893 0 401 401 1546188 0 96 100 ---
-------------------------------------------------- ------------------------------
Index statistics:
F4: Clusterratio or normal CLUSTERFAACTOR> 80
F5: 100 * (Keys * (isize 8) * 4) / (NLEAF * Indexpagesize> 50
F6: (100-PCTFREE) * (Indexpagesize-96) / (NLEVELS-2) * (IndexPageSize-96) / (Keys * (isize 8) * 4) <100
Creat Name Card Leaf Lvls Isize Keys F4 F5 F6 REORG
-------------------------------------------------- ------------------------------
Table: DB2Admin.t_ckd
DB2ADMIN XAK1T_CKD 4893 49 2 28 4893 81 87 2 ---
DB2ADMIN XIE1T_CKD 4893 7 2 10 3 99 68 18 ---
DB2ADMIN XIE2T_CKD 4893 7 2 10 2 99 68 18 ---
DB2ADMIN XIE3T_CKD 4893 7 2 4 18 100 68 18 ---
DB2ADMIN XIE4T_CKD 4893 6 2 18 6 90 80 18 ---
SYSIBM SQL010510174815750 4893 49 2 28 4893 81 87 2 ---
-------------------------------------------------- ------------------------------
Clusterratio or normal ClusterFactor (f4) will indicate indexes require REORG, which is not in the same sequence in the same sequence. When multiple indexes are defined in the table, one or more indexs may be marked as needed as needed. Specifies the most important index of the REORG order.
At this point, the test was completed. Next, compare the operating statistics and restructuring before and after operation, as shown below:
Running restructuring statistics
After running restructuring statistics
Comparing the SQL statement cost before and after running statistics, it can be seen that the operation before running into operations 1572, the operating cost is one-third of the original. Then run the program that the response speed is more than the previously improved improvement, and this tricky problem is solved (of course, this is not the rule of the standard, "should be used to optimize its performance from the SQL statement itself). At the same time, I also found the answer to "I use the imported method to load this table data but did not find the above phenomenon." The question is also - in the Import process represents a new table, the import tool will use similar operation statistics. The way the data is evenly filled into the foliage, so the speed will also speed up. This problem explains that regular operational statistics is helpful for database performance improvements for tables that often change in the database.
[Appendix: Some other background knowledge]
Considering factors for metrics used to Reorgchk include: (when viewed when viewing the output of the REORGCHK tool, find these columns used to table, and the F4, F5, F6, F7 and F8 used to index Columns. If you have an asterisk (*) in these columns, the current table and / or index be out of the threshold.) F1: The percentage of the row belonging to the overflow record. When this percentage is greater than 5%, there is an asterisk (*) in the output F1 column. F2: The percentage of space used in the data page. When this percentage is less than 70%, there will be an asterisk (*) on the output F2 column.
F3: The percentage of the page containing data containing certain records. When this percentage is less than 80%, there will be an asterisk (*) on the output F3 column.
F4: The cluster rate, the percentage of rows that have the same order in the table and indexes. When this percentage is less than 80%, then there is an asterisk (*) on the F4 column output.
F5: The percentage of space for the index key on each index page. When this percentage is less than 50%, there will be an asterisk (*) on the output F5 column.
F6: The number of keys that can be stored in each index level can be stored. When this number is less than 100, there will be an asterisk (*) on the F6 column output.
F7: The percentage of record ID (keys) marked as deleded in a page. When this percentage is greater than 20%, there will be an asterisk (*) on the output F7 column.
F8: The percentage of the index hollow leaf page. When this percentage is more than 20%, there will be an asterisk (*) on the output F8 column.
Run the REORGCHK tool for all tables and make sure you are using the current statistics, you can use the command:
REORGCHK UPDATE STATISTICS ON TABLE User
You can use the following statement to check any table with no statistics:
Select TabName from syscat.tables where stats_time is null
You can use the following statement to check any index without statistics:
Select Indname from syscat.indexes where stats_time is null
You can use the following statement to find tables and indexes with statistics for more than 30 days:
Select Tabname from syscat.tables where stats_time Note: When using the RUNSTATS command, you must specify the mode of the table.
