Shulou(Shulou.com)06/01 Report--

There is a batch program running for more than 24 hours still can not be completed, collected during the program running AWR report is as follows.

As can be seen above, the system is a single instance database of AIX, with a sampling time of 1319.96 minutes and a DB time of 1532.15 minutes.

Look at the TOP wait event:

You can see that there is a very high DB file scattered read wait event, which indicates that a large number of data blocks are read into discontiguous areas of memory, often indicating a large full table scan.

During the running of the program, looking at the ASH dynamic view v$active_session_history, we can also find a large number of DB file scattered read waiting events. From the execution plan column of this view, we can see that Table Full Scan is happening.

Then let's look at the TOP SQL section:

We see that SQL ID consumes a lot of resources for the SQL statement of 2yhcj6jcbtvac, which is as follows:

SELECT MATCH_CLIENT_ID, MATCH_CLIENT_ROLE, DECODE (MATCH_SYS_CODE, 'HKP', 1,' UVP', 2, 'CAS', 3,' NB', 4, 'GP', 5,' GLH', 6, 'GI', 7,' MFD', 8, 'CRC', 9, 10) AS MATCH_POL_ORDER, MATCH_SYS_CODE, MATCH_LOB, MATCH_CONTRACT_NO MATCH_CERTIFICATE_NO FROM POSSIBLE_CUST_REPORT_SCB WHERE BATCH_DATE =: B3 AND CLIENT_ID =: B2 AND MATCH_CLIENT_ID =: B1

You can see that the statement is a single table query, and you can also see from v$active_session_history that a large number of DB file scattered read occurs in this statement. Looking at the definition of the table, there is no index, so it can be determined for the first time that the above SQL statement has been executed many times, and a full table scan is being generated because there is no index.

Check out the segment section of AWR to confirm this:

This table segment produces maximum logical reads, physical reads, and non-optimized reads.

Therefore, it is now fully certain that a large number of full table scans on the table is the most important performance problem, so you need to add indexes to the table.

Create an index on the table:

CREATE INDEX idx1_POSSIBLE_CUST_REPORT_SCB ON POSSIBLE_CUST_REPORT_SCB (BATCH_DATE,CLIENT_ID,MATCH_CLIENT_ID)

So immediately after the creation is completed, check the v$active_session_history again and you can see that the index is changed by executing the road strength.

The final result confirms that the performance of the batch program is improved by more than 3 times.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.