A query transaction causes a high CPU usage on the database

This is a while doing a query for trading stress tests, using the Aix+informix database. Applications and databases are deployed on two machines, respectively. Use LoadRunner for concurrent testing. The historical data for the related table is 20W level. Test with 20 concurrency.

During the test, the application server load is normal, the database server disk, network utilization is normal, but the CPU utilization is about 98%. It's strange, because this machine is the best performance in a test environment, and should not behave like this. So first guess will be informix parameter configuration is wrong, so search some Informix parameters affect CPU utilization parameters, but the phenomenon is still. The SQL statements for the three main tables inside the query are viewed using the execution plan, and the query is no problem, and all falls on the index. Until the first two days to see an article, which mentions that if you are not sure which piece of code caused by the performance problem, you can use the method of segmented comment code to locate the problem of which all the code, so also want to be able to query all the tables in the transaction query the SQL statements to find out which table is the cause of the operation. Finally, when querying another table with a larger amount of data, the index is deleted in the script that leads the data, resulting in another inefficient index. Here are some of the query plans:

QUERY: (Optimization timestamp:02-12-2015 14:33:42)
------
SELECT Cust_num,bank_card from Yw_zhzj where fund_card= ' fund_card0002456 ' and zjzt= ' 0 '

Estimated cost:12456
Estimated # of Rows Returned:2

1) Ywk.yw_zhzj:INDEX PATH

Filters:ywk.yw_zhzj.zjzt = ' 0 '

(1) Index name:ywk. 413_1682
Index keys:cust_num bank_card fund_card dxjgdm CPDM (Key-first) (Serial, Fragments:all)
Index Key Filters: (ywk.yw_zhzj.fund_card = ' fund_card0002456 ')

Query Statistics:
-----------------

Table Map:
----------------------------
Internal name Table Name
----------------------------
T1 YW_ZHZJ

Type table Rows_prod est_rows rows_scan time Est_cost
-------------------------------------------------------------------
Scan T1 1 2 200000 00:00.58 12456

You can see that although the "INDEX PATH" is used, but the following rows_scan is 200000, the data volume of this table is 200000, that is, the full table scan. Later, with the index on the missing Fund_card field added to the YW_ZHZJ table, the database CPU usage dropped to below 5% immediately.

Suddenly think of the words I saw before: the database CPU usage is unusually high, it is likely that a large table is a full table scan. Unfortunately, the experience was shallow and still stuck to Informix's parameter adjustment. Now look back and summarize two points:

1,CPU usage is high, but other resource usage is normal, that is to consider the table with a large amount of data for full table scan first.

2, direction is important.

3, annotation method is the killer of positioning problem

However, for the execution of the plan, still a bit confused, like the above example, even if the query on this table falls on another index, then why did the full table scan? Because the index keys include cust_num bank_card fund_card DXJGDM CPDM, there are fund_card, and not a full-table scan. Learning is not fine, follow up to learn more!

This article from "Love Growth, Love life" blog, declined reproduced!

A query transaction causes a high CPU usage on the database