Performance Problem resolution case 01--sybase database memory issues

The recent Hunan site feedback problem, all electronic signature pages do not open the Instrument (PDF format), the background log did not report any errors.

1, the first thought is the signing of the OCX control problem, check the OCX control installation, found that other computers also can not open the instrument, test page directly open the PDF document, to exclude the problem of the control.

2, the suspicion is the document download problem, check the document download function, we download the PDF document to the local "My Documents" directory, and then use the OCX control to open the instrument, check that some documents can be downloaded to the local, some simply do not download, can be downloaded to the local instrument can open normally. Check the FTP found the document is not a problem, the location of the problem is downloaded from the FTP block.

3, FTP download has 2 steps, first go to the database query to CCBH, and then according to CCBH from FTP download, check found download no problem, query CCBH very slow, the fastest also want to 50s to query out, slow to 4, 5 minutes, patience, wait a few minutes, the instrument is can show, has been positioned for the cause of the problem, SQL execution of query CCBH is very slow.

5, analysis of SQL, simplified after SQL is:

SELECT *  from T_ZXLD_SYYH_FJ WHERE C_BH_SQ='76a1737d773be344b7154aa479634fed' and C_FJLX=  'syyhcx.zz.cxs.gy'

C_BH_SQ is indexed in the query condition and should not be so slow in theory, see Execution Plan discovery:

The index is not used, the execution time is 274s. There is no index but useless, and c_bh_sq this field is the primary table primary key, the selected probability should be very high, suspect that the statistical information problems, using

UPDATE  All STATISTICS T_zxld_syyh_fj

Updates the statistics, executes the original SQL after the update, uses 0.12s, and the execution plan also shows that the index is used

The problem seems to be solved, but occasionally there will be open very slow (fast about 10s, slow for a few minutes), what is going on? Inspection process found not only this page occasionally slow, a lot of pages are occasionally very slow, the first time to open slowly, follow up quickly, wait a while again open and slow.

View P6spy Log Discovery (the Dark Color column is the SQL execution time, only recorded the execution of more than 2s of sql), the execution time of the SQL out of a separate execution, many of the first execution of a few 10 seconds, the second execution of less than 1s, can determine the cause of the problem, the system memory is small.

Sybase data can use the following command to view the IO condition:SET STATISTICS io on| OFF

First execution Result:

Second execution Result:

Can see the first time from the disk and memory read the content, the second physical reads is 0, completely from memory read of course query fast, memory is small, other query data read into the memory, the old data from memory, and then query the old data also need to read from disk to memory, So often appear the first slower, subsequent faster situation. The best way to do this is to increase the memory and read the usual data into the memory cache.

Workaround It is recommended to upgrade Sybase to 15.7 on-site because sybase12.5.3 does not have a 64-bit version and 32 bits uses up to 2G of memory.