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

As we all know, after the application system is running for a period of time, users report that the system will run slowly, so that they cannot complete all the work, it takes too long to complete transactions and queries, or the application slows down at certain times of the day. In order to determine the essential cause of the problem, it is necessary to evaluate the actual use of system resources and further analyze the bottleneck of resource use.

Users usually report performance problems:

Response time for transactions or queries is longer than expected

L transaction throughput is not sufficient to complete the necessary workload

L transaction throughput reduction

For DB2 to improve performance, you can simply start from the following four aspects:

SQL

Bufferpool

Lock

SORTHEAP

So how do you get the best-performing SQL? Let's take a look at several related tools provided by DB2:

Dead DB2 Visual Explain

DB2 Visual Explain can get a visual query plan. With the query plan, we can optimize the query specifically. Find out the most expensive scans (table scans, index scans, etc.) and operations (Join,Filter,Fetch, etc.) according to the query plan, and then optimize the query by rewriting the query or creating an index to eliminate costly scans or operations.

Dead db2exfmt

The db2exfmt command can format and output the access plan information stored in the Explain table as text. The db2exfmt command displays the information more intuitively and is more convenient to use.

Dead db2expln

The db2expln command is similar to the Visual Explain function mentioned earlier. You can get a query plan in text form through this command. Db2expln is an interpretation tool on the command line.

Dead db2advis

Db2advis is another very useful command provided by DB2. Through this command DB2 can give suggestions to improve query performance according to the configuration of the optimizer and the performance of the machine.

For now, we use db2advis the most, because the advice given by this tool is more intuitive, focusing on how to create indexes, how much query costs can be reduced by these indexes, which tables or Materialized Query Table (MQT), and so on. So here we mainly analyze how to use db2advis to improve the query performance of SQL statements.

The db2advis command is as follows:

Db2advis-d-a /-I-o Example: db2advis-d test_db-a user/password-I D:\ temp\ sql_2.txt > D:\ temp\ sql_2_result_db2advis.txt

There are usually sql statements with high time consumption and high cost in db2 database. Long time-consuming sql statements take up all kinds of resources, such as CPU, Memory, transaction log, etc., which increases the waiting time of other sql statements, resulting in poor performance of the whole database. Therefore, we will always monitor the sql with poor performance.

The following example is that I came across a poor performance statement in the Nanji warehouse.

We first received a warning on our side:

[BOMC] alarm, level: 2grad IP address: 172.16.5.48, alarm time: 2017-02-08 07:04:42, alarm content: 172.16.5.48 basic SDBs-execute more than 1 hour and occupy a large number of transaction log applications for a long time: process number 1573 execution time 89

When I logged in to check, sql had finished running, so the following sql statement was found by querying the corresponding history history with process number 1573:

Select op_time, channel_city_name, channel_region_name, promo_name, promo_id, cond_name, cond_id, user_id, product_no, valid_date, channel_name, channel_type1, channel_type2, channel_type3, op_id, op_name

From (select * from (select rownumber () over (order by channel_city_id asc) as row_,temp_.*)

From (select * from bass2.stat_act_repeat_order a where 1 and a.op_time='2017-01-17' order by channel_city_id asc) as temp_)

As temp2_ where row_ between 031 and 15) a

Because the statement is long, I encapsulate the statement in test2.sql to perform the optimization. Before optimizing, make sure that there are no breakings between statements, and that there can be no double quotation marks, and replace it with single quotation marks''if any.

The following is a detailed optimization process:

Bash-3.2 $db2advis-d bassdb-I test2.sql-We write the sql statement to be optimized in test.sql2, which is suitable for longer sql.

Using user id as default schema name. Use-n option to specify schema

CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode =-443. Getting database size from the catalog tables.

Execution started at timestamp 2017-02-08-09.52.12.667113

Found [1] SQL statements from the input file

Recommending indexes...

Total disk space needed for initial set [15.060] MB-the total size of indexes to be created

Total disk space constrained to [2227893.025] MB

Trying variations of the solution set.

1 indexes in current solution

[3428.0000] timerons (without recommendations)-the time cost before optimization is 3428

[96.0000] timerons (with current solution)-it is estimated that the time cost after optimization is 96.

[97.20%] improvement-can improve query efficiency by 97.20%, and the effect is obvious.

Db2advis recommends creating an index (how to create the "LIST OF RECOMMENDED INDEXES" shown below), the cost is expected to be reduced from 3428 to 96, and the query efficiency is increased by 97.20%.

--

--

-- LIST OF RECOMMENDED INDEXES

-- = =

-- index [1], 15.060MB-the space occupied by the index to be added is 15.06MB.

CREATE INDEX "DB2INST1". "IDX1702101953330" ON "BASS2". STAT_ACT_REPEAT_ORDER "

("OP_TIME" ASC, "CHANNEL_CITY_ID" ASC, "OP_NAME" ASC

"OP_ID" ASC, "CHANNEL_TYPE3" ASC, "CHANNEL_TYPE2"

ASC, "CHANNEL_TYPE1" ASC, "CHANNEL_NAME" ASC, "VALID_DATE"

ASC, "PRODUCT_NO" ASC, "USER_ID" ASC, "COND_ID" ASC

"COND_NAME" ASC, "PROMO_ID" ASC, "PROMO_NAME" ASC

"CHANNEL_REGION_NAME" ASC, "CHANNEL_CITY_NAME" ASC)

ALLOW REVERSE SCANS COLLECT SAMPLED DETAILED STATISTICS

COMMIT WORK

--

--

-- RECOMMENDED EXISTING INDEXES

-- = =

--

--

-- UNUSED EXISTING INDEXES

-- = =

-- = =

--

14 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.

In order to avoid full table scanning, db2advis recommends adding an index, but we can see that more keywords are added to the index, which will take up unnecessary space, because the general advice given by db2advis can not be fully adopted, so we need DBA to further analyze how to create an index to achieve the highest efficiency with the lowest cost.

Here are the sql statements in test2.sql

Select op_time, channel_city_name, channel_region_name, promo_name, promo_id, cond_name, cond_id, user_id, product_no, valid_date, channel_name, channel_type1, channel_type2, channel_type3, op_id, op_name

From (select * from (select rownumber () over (order by channel_city_id asc) as row_,temp_.*)

From (select * from bass2.stat_act_repeat_order a where 1 and a.op_time='2017-01-17' order by channel_city_id asc) as temp_)

As temp2_ where row_ between 031 and 15) a

From a rough analysis of the above statement, the main search keywords may be concentrated on channel_city_id and op_time, so we build the index to include only these two key fields. After a series of change control processes, we add the index and run db2advis again to see what the result is:

Bash-3.2 $db2advis-d bassdb-I test2.sql

Using user id as default schema name. Use-n option to specify schema

CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode =-443. Getting database size from the catalog tables.

Execution started at timestamp 2017-02-08-14.58.45.473590

Found [1] SQL statements from the input file

Recommending indexes...

Total disk space needed for initial set [0.000] MB

Total disk space constrained to [2232773.544] MB

Trying variations of the solution set.

0 indexes in current solution

[76.0000] timerons (without recommendations)-the current time cost of consumption has been reduced from 3428 to 76.

[76.0000] timerons (with current solution)

[0.005%] improvement-nothing can be promoted

--

--

-- LIST OF RECOMMENDED INDEXES

-- = =

-- no indexes are recommended for this workload.

--

--

-- RECOMMENDED EXISTING INDEXES

-- = =

RUNSTATS ON TABLE "BASS2". "STAT_ACT_REPEAT_ORDER" FOR SAMPLED DETAILED INDEX "DB2INST1". "IDX1702110408560"

-- COMMIT WORK

--

--

-- UNUSED EXISTING INDEXES

-- = =

-- = =

--

3 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.

Wait a minute, are we missing something from the above example? The statistics are not updated after adding the index!

Now run the updated index of runstats statistics, and let's take a look at the current db2advis results.

Bash-3.2 $db2advis-d bassdb-I test2.sql

Using user id as default schema name. Use-n option to specify schema

CALL SYSPROC.GET_DBSIZE_INFO failed, sqlcode =-443. Getting database size from the catalog tables.

Execution started at timestamp 2017-02-08-14.58.45.473590

Found [1] SQL statements from the input file

Recommending indexes...

Total disk space needed for initial set [0.000] MB

Total disk space constrained to [2234220.950] MB

Trying variations of the solution set.

0 indexes in current solution

[31.0000] timerons (without recommendations)-the current time cost of consumption has been reduced from 76 to 31.

[31.0000] timerons (with current solution)

[0.005%] improvement-nothing can be promoted

--

--

-- LIST OF RECOMMENDED INDEXES

-- = =

-- no indexes are recommended for this workload.

--

--

-- RECOMMENDED EXISTING INDEXES

-- = =

-- RUNSTATS ON TABLE "BASS2". "STAT_ACT_REPEAT_ORDER" FOR SAMPLED DETAILED INDEX "DB2INST1". "IDX1702110408560";-here is a hint to update the statistics of the index.

It's already been implemented, so db2advis has some suggestions

Think about it for yourself.

-- COMMIT WORK

--

--

-- UNUSED EXISTING INDEXES

-- = =

-- = =

--

3 solutions were evaluated by the advisor

DB2 Workload Performance Advisor tool is finished.

From the above results, we can see that the index keyword we selected is correct, there is no room for further improvement, and remember to collect statistics again after adding the index in order to get a more accurate evaluation.

Summary:

1. The suggestions provided by db2advis need to be modified according to the actual situation in order to achieve the highest query performance at the lowest cost.

two。 Ensure that all tables involved have collected statistical data before performing db2advis, which can improve the accuracy of the data provided.

3. After adding a new index, the index also needs to collect statistics, which will not affect the actual optimized performance of the database, but will affect the optimized performance evaluation of DBA.

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.