While performing SQL Tuning typical questions arises in mind of developers or DBA:

  • What is the best SQL language construct for the job?
  • Which indexes are used and which not?
  • How can I generate alternate SQL access path?
  • How to put application logic into SQL statements?
  • Where to start SQL access path tuning?
  • What are my most important SQL statements?
  • Goal:

  • Provide means to collect and analyze the SQL Workload of your database.
  • Tune the workload with the most efficient tuning object “INDEX” apply new SQL constructs to simplify and speedup SQL statements

What are my most important SQL statements?

Those which hurt…
How to find out?

  • phone calls from frustrated users
  • bad performance metrics
  • reports sorted by elapsed time or number of executions

Collect your SQL Workload

Create a table to store SQL statements and performance counters
CREATE TABLE ANUPAM.DYNSQL_COLL
(
NUM_EXECUTIONS BIGINT,
TOTAL_EXEC_TIME BIGINT,
LAST_NUM_EXECUTIONS BIGINT,
LAST_TOTAL_EXEC_TIME BIGINT,
STMT_TEXT VARCHAR(32000)
)
IN NTS_DBA;

Combine MERGE and Snapshot function / view

MERGE INTO ANUPAM.DYNSQL_COLL mdyn USING
(SELECT NUM_EXECUTIONS,CAST(STMT_TEXT AS VARCHAR(32000)) AS
STMT_TEXT,total_exec_time,
FROM TABLE(sysproc.snapshot_dyn_sql('SAMPLE',-1)) AS temp
WHERE STMT_TEXT LIKE '%select%' OR STMT_TEXT LIKE '%SELECT%'
) sdyn ON (mdyn.stmt_text = sdyn.stmt_text)

WHEN MATCHED AND tdyn.last_num_executions < sdyn.num_executions THEN UPDATE SET num_executions = num_executions + sdyn.num_executions - tdyn.last_num_executions, total_exec_time = total_exec_time + sdyn.total_exec_time - tdyn.last_total_exec_time, l ast_num_executions = sdyn.num_executions, l ast_total_exec_time = sdyn.total_exec_time WHEN MATCHED AND tdyn.last_num_executions > sdyn.num_executions THEN
UPDATE SET
num_executions = num_executions + sdyn.num_executions,
total_exec_time = total_exec_time + sdyn.total_exec_time,
last_num_executions = sdyn.num_executions,
last_total_exec_time = sdyn.total_exec_time
WHEN NOT MATCHED THEN
INSERT(stmt_text,num_executions,last_num_executions,total_exec_time,
last_total_exec_time)
VALUES(sdyn.stmt_text,sdyn.num_executions,sdyn.num_executions,
sdyn.total_exec_time,sdyn.total_exec_time)

Collect your SQL Workload using MERGE + SNAPSHOT
Run MERGE statement in a loop (every 1 hour, or more often)

Remarks on this approach:

– much better than a single DYNSQL snapshot
– you may miss some statements but it is still showing the trend
– statement text is limited to 32 k (now 2 MB possible, Clob might help, but harder to handle)
– time consuming to compare statement text, one could add a hashing algorithm for sql stmt text to speedup search performance for given sql
->capture workloads for a given time (nightly batch, etl, user queries) and delete the sql table more frequently

– same statements with different literals are considered different
SELECT * FROM test WHERE tno = A1000′
SELECT * FROM test WHERE tno =’B2000′
– there are tools which deal with those problems! (that was the shameless marketing moment)

Use your workload and find appropriate indexes (and/or MQT/MDC)

ANUPAM.DYN_SQL

Stmt_Text Num_Execs
Select*fro… 10500
Select a,b… 8235/td>
Select c fro… 4856
…..


db2 -x SELECT '--#SET FREQUENCY ' || char(num_executions) ||chr(13) || chr(10) || ltrim(rtrim(stmt_text )) || ';' FROM "ANUPAM"."MYDYNSQL" num_executions desc fetch first 20 rows only > input.sql;

Transfer content of above query in file.
cat input.sql
--#SET FREQUENCY 10500
Select * from EMPLOYEE WHERE.....
--#SET FREQUENCY 8235
select a,b ...
--#SET FREQUENCY 4856
select c fr.....

Use input.sql file as input to DB2 ADVISOR and receive index recommendation.

Approach:

DB2 Design Advisor(DB2ADVIS):
db2advis – db test -stmt “select e.name, d.dname from staff e, dstaff d where e.deptnum=d.deptno”

-- LIST OF RECOMMENDED INDEXES
CREATE INDEX "DB2ADMIN"."IDX807172138370000" ON "DB2ADMIN"."staff" ("DEPTNUM" ASC, "DEPTNAME" ASC) ALLOW REVERSE SCANS;
CREATE INDEX "DB2ADMIN"."IDX807172138430000" ON "DB2ADMIN"."dstaff" ("DEPTNO" ASC, "LASTNAME" ASC) ALLOW REVERSE SCANS;

Optimization finished.
2 indexes in current solution
1127,0000] timerons (without recommendations)
[960,0000] timerons (with current solution)
[14.82%] improvement with 1 call and no thiking.

alternative Solution to DB2ADVIS

set register from command line:
SET CURRENT EXPLAIN MODE RECOMMEND INDEX

Run your SQL Statement :
select e.name, d.dname from staff e, dstaff d where e.deptnum=d.deptno

Visualize Access Path with DB2EXFMT
db2exfmt -1 -d test -g TIC -o output.sql -w -1

Ok, i got those index recommendations, but are they good?”
-> virtual indexes can show the new access path without physical index creation

SOLUTION:
SET CURRENT EXPLAIN MODE EVALUATE INDEX
Visualize Access Path with DB2EXFMT
db2exfmt -1 -d sample -g TIC -o output.txt -w -1

SAMPLE SCRIPT for index recommendations and virtual indexes

Title : find and emulate indexes
.$HOME/sqllib/db2profile
db2 connect to %1 user %2 using %3 > NULL
echo create "old" access path
db2 set current explain mode EXPLAIN > NULL
db2 -tsf %4 > NULL
db2exfmt -1 -d %1 -e %2 -g TIC -o before.txt -u %2 %3 -w -1
echo create new access path with virtual index
db2 set current explain mode recommend indexes
db2 -tsf %4 > NULL
db2exfmt -1 -d %1 -e %2 -g TIC -o after.txt -u %2 %3 -w -1
db2 set current explain mode no > NULL
db2 connect reset > NULL
echo writing virtual Indexes to file virind_out.txt
db2 connect to %1 user %2 using %3 > NULL
db2 select name, creator, tbname, colnames from %2.ADVISE_INDEX where EXPLAIN_TIME=(SELECT MAX(EXPLAIN_TIME) from %2.ADVISE_INDEX) > virind_out.txt echo finished

see files before.txt and after.txt

Index analysis: DB2PD (which indexes are used, which not)

usage of DB2PD
– Serverside command line tool
– no API
“unformatted” output

call it from command line:
db2pd -db sample -tcbstats index (same thing for unused tables)

Look for section TCB Index Stats:

Table Name IID Scans
SYSTABLES 1 8712
TEST 2 25
…. …. ….



IID refers to index id and scans refers to number of index scans till last database activation.

DB2PD
– unfortunately no SQL table function or stored procedure for DB2PD
– you need to grep the output on the db2 server

Join The Discussion

Your email address will not be published. Required fields are marked *