The Database Whisperer: When SQL Attacks!

The Database Whisperer: When SQL Attacks! Brad Price Werner Enterprises Session Code: C07 5/4/2011 11:00 AM to 12:00 PM | Platform: ; DB2 for Linux, UNIX, Windows

Agenda Finding and identifying bad SQL Effectively utilizing Visual Explain to understand what is causing the bad SQL behavior Using db2batch, snapshots and Administrative Views to zero in on specific problems in an SQL statement Identifying best corrective action for the SQL - new/modified indexes, rewrite SQL, database configuration changes, etc. Applying benchmarking techniques to quantify improvements

Agenda Finding and identifying bad SQL Effectively utilizing Visual Explain to understand what is causing the bad SQL behavior Using db2batch, snapshots and Administrative Views to zero in on specific problems in an SQL statement Identifying best corrective action for the SQL - new/modified indexes, rewrite SQL, database configuration changes, etc. Applying benchmarking techniques to quantify improvements 3

The SQL attacks you….. Verify that the SQL statement give to you is really causing the problem Snapshot for dynamic sql Event monitor for statements Check overall health of the database List applications to see if other connections are moving Snapshot for DB

When you have to go looking Snapshots Traditional snapshots Table functions snapshots Administrative view snapshots Event monitor

Table Functions and Administrative Views

Snap_Get_Tab Table Function SNAP_GET_TAB table function >>-SNAP_GET_TAB--(--dbname--,--dbpartitionnum--)--------------->< The schema is SYSPROC. The SNAP_GET_TAB table function returns snapshot information from the table logical data group. dbname An input argument of type VARCHAR(255) that specifies a valid database name in the same instance as the currently connected database when calling this function. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify the null value to take the snapshot from the currently connected database. dbpartitionnum An input argument of type INTEGER that specifies a valid partition number. Specify -1 for the current partition, or -2 for all partitions. If the null value is specified, -1 is set implicitly. If both parameters are set to NULL, the snapshot will be taken only if a file has not previously been created by the SNAPSHOT_FILEW stored procedure for the corresponding snapshot API request type. The function returns a table as shown below.

Traditional Get Snapshot [didbap1@aixudb1 /opt/IBM/didbap1]$ db2 get snapshot for tables on dbdba01 Table Snapshot First database connect timestamp = 12/26/2006 09:49:36.638633 Last reset timestamp = Snapshot timestamp = 12/26/2006 12:14:26.100968 Database name = DBDBA01 Database path = /db2udb/didbap1/dbdba01/dbpath/didbap1/NODE0000/SQL00001/ Input database alias = DBDBA01 Number of accessed tables = 23 Table List Table Schema = SYSIBM Table Name = SYSTABLES Table Type = Catalog Data Object Pages = 33 Index Object Pages = 17 LOB Object pages = 1024 Rows Read = 731 Rows Written = 7 Overflows = 0 Page Reorgs = 0

Table Snapshot using Admin view/Table Function

Store snapshot results in a table!! • Create table that looks like the snaptab admin view • create table benchmark.snaptab as (select * from sysibmadm.snaptab) with no data • Add extra columns as needed • alter table benchmark.snaptab add column dbname varchar(20) • alter table benchmark.snaptab add column test_iteration varchar(20)

Inserting data from table function into our new table insert into benchmark_snaptab (select a.*, 'SAMPLE','mytest1' from sysibmadm.snaptab a where tabname in ('EMPLOYEE','DEPARTMENT','CUSTOMER')); [Run your application queries] insert into benchmark_snaptab (select a.*, 'SAMPLE','mytest1' from sysibmadm.snaptab a where tabname in ('EMPLOYEE','DEPARTMENT','CUSTOMER'));

Select from our snapshot table WITH start_stats AS (SELECT Substr(tabname, 1, 15) tabname, rows_read, test_iteration FROM benchmark_snaptab a WHERE a.snapshot_timestamp = (SELECT MIN(snapshot_timestamp) FROM benchmark_snaptab b WHERE a.test_iteration = b.test_iteration)), end_stats AS (SELECT Substr(tabname, 1, 15) tabname, rows_read, test_iteration FROM benchmark_snaptab a WHERE a.snapshot_timestamp = (SELECT MAX(snapshot_timestamp) FROM benchmark_snaptab b WHERE a.test_iteration = b.test_iteration)) SELECT end.test_iteration, end.tabname, end.rows_read - start.rows_read rows_read FROM start_stats start, end_stats end WHERE start.tabname = end.tabname AND start.test_iteration = end.test_iteration ORDER BY test_iteration; TEST_ITERATION TABNAME ROWS_READ -------------------- --------------- -------------------- mytest1 CUSTOMER 12 mytest1 DEPARTMENT 168 mytest1 EMPLOYEE 252 mytest2 CUSTOMER 6 mytest2 DEPARTMENT 14 mytest2 EMPLOYEE 151788 “Reset Monitor All” does not work for table functions or admin views, so need to capture beginning/ending stats and then find delta

Snapshot Monitoring – db2_get_snapshots.sh TEST_NAME=$1 TEST_ITERATION=$2 TEST_DATABASE=$3 db2 connect to dbiaa01 db2 +p -t << EOF insert into benchmk.snapshot_bp (select snapshot_bp.*, '$TEST_NAME', '$TEST_ITERATION' from table(snapshot_bp('$TEST_DATABASE',-2)) as snapshot_bp) ; EOF db2 +p -t << EOF insert into benchmk.snapshot_tbscfg (select snapshot_tbs_cfg.*, '$TEST_NAME', '$TEST_ITERATION' from table(snapshot_tbs_cfg('$TEST_DATABASE',-2)) as snapshot_tbs_cfg) ; EOF db2 +p -t << EOF insert into benchmk.snapshot_table (select snapshot_table.*, '$TEST_NAME', '$TEST_ITERATION' from table(snapshot_table('$TEST_DATABASE',-2)) as snapshot_bp) ; EOF

Admin View results vs Table Function Using Admin View Easy to read!! select * from sysibmadm.snapappl_info SNAPSHOT_TIMESTAMP AGENT_ID APPL_STATUS CODEPAGE_ID -------------------------- -------------------- ---------------------- -------------------- 2007-02-15-00.41.53.547803 19 UOWWAIT 1252 2007-02-15-00.41.53.547803 17 UOWWAIT 1208 2007-02-15-00.41.53.547803 16 UOWEXEC 1252 select * from table(snapshot_appl_info('SAMPLE',-2)) a SNAPSHOT_TIMESTAMP AGENT_ID APPL_STATUS CODEPAGE_ID -------------------------- -------------------- -------------------- -------------------- 2007-02-15-00.41.53.610311 19 4 1252 2007-02-15-00.41.53.610311 17 4 1208 2007-02-15-00.41.53.610311 16 3 1252 Using Table Function Not so easy to read!!

Table Function “Decoder Ring” /opt/IBM/db2/V9.7/include/sqlmon.h /******************************************************************************/ /* application status (appl_status) */ /******************************************************************************/ #define SQLM_CONNECTPEND 1 /* connect pending */ #define SQLM_CONNECTED 2 /* connect completed */ #define SQLM_UOWEXEC 3 /* UOW executing */ #define SQLM_UOWWAIT 4 /* UOW waiting */ #define SQLM_LOCKWAIT 5 /* lock wait */ . . . . . . . . . #define SQLM_TEND 15 /* Transaction ended */ #define SQLM_CREATE_DB 16 /* Creating Database */ #define SQLM_RESTART 17 /* Restarting a Database */ #define SQLM_RESTORE 18 /* Restoring a Database */ #define SQLM_BACKUP 19 /* Performing a Backup */

Add case statements to decode select agent_id, case when appl_status = 4 then 'UOW Wait ' when appl_status = 3 then 'Executing ' when appl_status = 5 then 'Lock Wait ' else 'Other ' end as status , substr(appl_name,1,20) appl_name from table(snapshot_appl_info('DBIAA99',-2)) a AGENT_ID STATUS APPL_NAME -------------------- -------------- -------------------- 175 UOW Wait javaw.exe 905 UOW Wait javaw.exe 758 UOW Wait tscom3.out 1392 UOW Wait javaw.exe 1061 Executing db2bp.exe

All time favorite snapshot SQL!!! select rows_read, num_executions, decimal(round(decimal(rows_read,20,2)/decimal(num_executions,20,2),0),20,2) rows_per_exec, total_exec_time, decimal(decimal(total_exec_time,10,2)/decimal(num_executions,10,2),10,2) "TIME_PER_EXEC(sec)", total_usr_cpu_time, total_sys_cpu_time, substr(stmt_text,1,1000) from sysibmadm.snapdyn_sql a where num_executions <> 0 order by rows_read desc [ or by num_executions desc] [ or by total_exec_time desc] fetch first 20 rows only;

All time favorite snapshot SQL NUM_ TOTAL ROWS_READ EXECUTIONS ROWS_PER_EXEC EXEC_TIME TIME_PER_EXEC STMT_TEXT --------- ---------------- ------------- --------- --------------- ------------------ 2567888 566 4537.00 9 0.01 SELECT TRACE_TIME FR 2542273 566 4492.00 8 0.01 SELECT TRACE_TIME FR 2537845 566 4484.00 9 0.01 SELECT TRACE_TIME FR 1965040 2030 968.00 4 0.00 select categorise0_. 1816070 169154 11.00 775 0.01 select rolesincon0_.

Summary: Table Functions and Administrative Views Both are easy to use, great ways to retrieve virtually any type of information you would like on a database or instance Data returned in table format, so very easy to manipulate with SQL or store in tables for historical/trend analysis Administrative views are easier to use and results are much easier to read, but are limited to currently connected database and partition Table functions are a little more difficult to use, but allow flexibility to specify database, partition or in some cases more specific information (table) Table functions excel at things like looking for locks across all partitions or seeing aggregate sql execution counts select * from table(snapshot_lock('DBEDW01',-2)) a select * from table(snapshot_dyn_sql(‘DBEDW01’,-2)) a Unfortunately, “reset monitor” does not work with Table Functions or Administrative Views 

Event Monitor • Easy to create via Control Center • Didn’t see in Data Studio…. • Collect statements • Collects each execution of every SQL statement • By authid, applid or applname • If possible, monitor while only running suspected problem • Resource intensive!!! • Run for short periods only • Can be helpful for proving how much time is spent in application

Event Monitor output SELECT agent_id, rows_read, stmt_operation, start_time, stop_time, substr(stmt_text,1,100) FROM EDW.STMT_MON1_STATEMENTS fetch first 10 rows only; AGENT_ID ROWS_READ STMT_OPERATION START_TIME STOP_TIME STMT_TEXT ----------- ----------- -------------- ------------------- ------------------- ------------------------------ 839 0 19 2007-12-07-15.30.54 2007-12-07-15.30.54 839 7 1 2007-12-07-15.30.54 2007-12-07-15.30.54 with temp as ( Select distinct 839 0 4 2007-12-07-15.30.55 2007-12-07-15.30.55 with temp as ( Select distinct 839 101647 6 2007-12-07-15.30.55 2007-12-07-15.30.56 with temp as ( Select distinct 839 0 8 2007-12-07-15.30.56 2007-12-07-15.30.56 839 0 1 2007-12-07-15.30.56 2007-12-07-15.30.56 with temp as ( Select distinct 839 0 4 2007-12-07-15.30.56 2007-12-07-15.30.56 with temp as ( Select distinct 839 101647 6 2007-12-07-15.30.56 2007-12-07-15.30.56 with temp as ( Select distinct 839 0 8 2007-12-07-15.30.56 2007-12-07-15.30.56 839 0 1 2007-12-07-15.30.56 2007-12-07-15.30.56 with temp as ( Select distinct 10 record(s) selected.

Stmt_operation decodes (from sqlmon.h) /******************************************************************************/ /* Statement Operation Types (stmt_operation) */ /******************************************************************************/ /* SQL operations */ #define SQLM_PREPARE 1 /* SQL Prepare */ #define SQLM_EXECUTE 2 /* SQL Execute */ #define SQLM_EXECUTE_IMMEDIATE 3 /* SQL Execute Immediate */ #define SQLM_OPEN 4 /* SQL Open */ #define SQLM_FETCH 5 /* SQL Fetch */ #define SQLM_CLOSE 6 /* SQL Close */ #define SQLM_DESCRIBE 7 /* SQL Describe */ #define SQLM_STATIC_COMMIT 8 /* SQL Static Commit */ #define SQLM_STATIC_ROLLBACK 9 /* SQL Static Rollback */ #define SQLM_FREE_LOCATOR 10 /* SQL Free Locator */ #define SQLM_PREP_COMMIT 11 /* Prepare to commit (2-phase commit)*/ #define SQLM_CALL 12 /* Call a stored procedure */ #define SQLM_SELECT 15 /* SELECT statement */ #define SQLM_PREP_OPEN 16 /* Prep. and open (DB2 Connect only) */ #define SQLM_PREP_EXEC 17 /* Prep. and execute (DB2 Connect) */ #define SQLM_COMPILE 18 /* Compile (DB2 Connect only) */ #define SQLM_SET 19 /* SET statement */

Event Monitor Statements Table(abbreviated) dw010@/opt/IBM/db2>db2 describe table edw.stmt_mon1_statements Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ------ AGENT_ID SYSIBM BIGINT 8 0 No AGENTS_TOP SYSIBM BIGINT 8 0 No APPL_ID SYSIBM CHARACTER 32 0 No FETCH_COUNT SYSIBM BIGINT 8 0 No POOL_DATA_L_READS SYSIBM BIGINT 8 0 No POOL_DATA_P_READS SYSIBM BIGINT 8 0 No POOL_INDEX_L_READS SYSIBM BIGINT 8 0 No POOL_INDEX_P_READS SYSIBM BIGINT 8 0 No POOL_TEMP_DATA_L_READS SYSIBM BIGINT 8 0 No POOL_TEMP_DATA_P_READS SYSIBM BIGINT 8 0 No POOL_TEMP_INDEX_L_READS SYSIBM BIGINT 8 0 No POOL_TEMP_INDEX_P_READS SYSIBM BIGINT 8 0 No ROWS_READ SYSIBM BIGINT 8 0 No ROWS_WRITTEN SYSIBM BIGINT 8 0 No START_TIME SYSIBM TIMESTAMP 10 0 No STMT_OPERATION SYSIBM BIGINT 8 0 No STMT_TYPE SYSIBM BIGINT 8 0 No STOP_TIME SYSIBM TIMESTAMP 10 0 No SYSTEM_CPU_TIME SYSIBM BIGINT 8 0 No TOTAL_SORT_TIME SYSIBM BIGINT 8 0 No TOTAL_SORTS SYSIBM BIGINT 8 0 No USER_CPU_TIME SYSIBM BIGINT 8 0 No STMT_TEXT SYSIBM CLOB 2097152 0 Yes

Intermission Break (with prizes!!) 4 • All of these puzzles were made up by me • Each answer has something to do with DB2 Puzzle Time Answer 1 2 3 4 5 0 Foreign Key + 0 2 4 3 5 1 eDatabase This person believed the glass was “half full”. Yet, he was very stingy and hoarded money. He was known as the: 0 6 7 8 9 10 2 3 5 1 4 “The Optimizer”

Explain • Visual Explain • Dynexpln and others • Look at optimized SQL • ALWAYS!! - check runstats time on tables and indexes • Stats_time on syscat.tables and syscat.indexes

Explains • Visual Explain is favored by most people • Consider changing settings to operator cardinality • Statement-view-settings-operator • Sometimes can be quite helpful to look at optimized SQL • statement-optimized sql • Plug optimized into a sql formatter tool like http://www.dpriver.com/pp/sqlformat.htm (free)

Visual Explain change Operator

Visual Explain Optimized SQL

Optimized SQL SELECT Q8.SHIPPER_NM AS "SHIPPER_NM", Q7.ORDER_ID AS "ORDER_ID", Q10.TOUR_ID AS "TOUR_ID", ((Q10.TOTAL_REVENUE * Q7.WEIGHT_QY) / Q6.$C1) AS "LANECOST", Q10.TOTAL_REVENUE AS "TOTCOST" FROM (SELECT Q5.$C0, SUM(Q5.$C1) FROM (SELECT Q4.TOUR_ID, Q2.WEIGHT_QY FROM VASDW.ORDER AS Q1, VASDW.ORDER_DETAIL AS Q2, VASDW.TOUR_ORDER AS Q3, FACT.TOUR_FACT AS Q4 WHERE (Q1.ORDER_ID = Q2.ORDER_ID) AND (Q3.ORDER_ID = Q2.ORDER_ID) AND (Q4.TOUR_ID = Q3.TOUR_ID) AND (Q4.PROJECT_ID = 17) AND (DATE(Q4.ACTUAL_DEPARTURE_TS) <= '2007-12-31') AND ('2007-12-01' <= DATE(Q4.ACTUAL_DEPARTURE_TS)) AND NOT(Q4.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543))) AS Q5 GROUP BY Q5.$C0) AS Q6, VASDW.ORDER_DETAIL AS Q7, VASDW.ORDER AS Q8, VASDW.TOUR_ORDER AS Q9, FACT.TOUR_FACT AS Q10, DIMENSION.SHIPPER AS Q11, DIMENSION.CONSIGNEE AS Q12 WHERE (Q8.ORDER_ID = Q7.ORDER_ID) AND (Q9.ORDER_ID = Q7.ORDER_ID) AND (Q10.TOUR_ID = Q9.TOUR_ID) AND (Q10.PROJECT_ID = 17) AND (Q11.SHIPPER_ID = Q8.SHIPPER_ROLE_ID) AND (Q12.CONSIGNEE_ID = Q8.CONSIGNEE_ROLE_ID) AND (Q6.$C0 = Q9.TOUR_ID) AND NOT(Q10.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543)) AND ('2007-12-01' <= DATE(Q10.ACTUAL_DEPARTURE_TS)) AND (DATE(Q10.ACTUAL_DEPARTURE_TS) <= '2007-12-31')

Reformatted SQL SELECT Q8.SHIPPER_NM AS "SHIPPER_NM", Q7.ORDER_ID AS "ORDER_ID" , Q10.TOUR_ID AS "TOUR_ID" , ((Q10.TOTAL_REVENUE * Q7.WEIGHT_QY) / Q6.$C1) AS "LANECOST" , Q10.TOTAL_REVENUE AS "TOTCOST" FROM (SELECT Q5.$C0, SUM(Q5.$C1) FROM (SELECT Q4.TOUR_ID, Q2.WEIGHT_QY FROM VASDW.ORDER AS Q1, VASDW.ORDER_DETAIL AS Q2, VASDW.TOUR_ORDER AS Q3, FACT.TOUR_FACT AS Q4 WHERE (Q1.ORDER_ID = Q2.ORDER_ID) AND (Q3.ORDER_ID = Q2.ORDER_ID) AND (Q4.TOUR_ID = Q3.TOUR_ID) AND (Q4.PROJECT_ID = 17) AND (DATE(Q4.ACTUAL_DEPARTURE_TS) <= '2007-12-31') AND ('2007-12-01' <= DATE(Q4.ACTUAL_DEPARTURE_TS)) AND NOT(Q4.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543)) ) AS Q5 GROUP BY Q5.$C0 ) AS Q6 , VASDW.ORDER_DETAIL AS Q7 , VASDW.ORDER AS Q8 , VASDW.TOUR_ORDER AS Q9 , FACT.TOUR_FACT AS Q10, DIMENSION.SHIPPER AS Q11, DIMENSION.CONSIGNEE AS Q12 WHERE (Q8.ORDER_ID = Q7.ORDER_ID) AND (Q9.ORDER_ID = Q7.ORDER_ID) AND (Q10.TOUR_ID = Q9.TOUR_ID) AND (Q10.PROJECT_ID = 17) AND (Q11.SHIPPER_ID = Q8.SHIPPER_ROLE_ID) AND (Q12.CONSIGNEE_ID = Q8.CONSIGNEE_ROLE_ID) AND (Q6.$C0 = Q9.TOUR_ID) AND NOT(Q10.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543)) AND ('2007-12-01' <= DATE(Q10.ACTUAL_DEPARTURE_TS)) AND (DATE(Q10.ACTUAL_DEPARTURE_TS) <= '2007-12-31')

Run your query “Inside Out” SELECT Q8.SHIPPER_NM AS "SHIPPER_NM", Q7.ORDER_ID AS "ORDER_ID" , Q10.TOUR_ID AS "TOUR_ID" , ((Q10.TOTAL_REVENUE * Q7.WEIGHT_QY) / Q6.$C1) AS "LANECOST" , Q10.TOTAL_REVENUE AS "TOTCOST" FROM (SELECT Q5.$C0, SUM(Q5.$C1) FROM (SELECT Q4.TOUR_ID, Q2.WEIGHT_QY FROM VASDW.ORDER AS Q1, VASDW.ORDER_DETAIL AS Q2, VASDW.TOUR_ORDER AS Q3, FACT.TOUR_FACT AS Q4 WHERE (Q1.ORDER_ID = Q2.ORDER_ID) AND (Q3.ORDER_ID = Q2.ORDER_ID) AND (Q4.TOUR_ID = Q3.TOUR_ID) AND (Q4.PROJECT_ID = 17) AND (DATE(Q4.ACTUAL_DEPARTURE_TS) <= '2007-12-31') AND ('2007-12-01' <= DATE(Q4.ACTUAL_DEPARTURE_TS)) AND NOT(Q4.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543)) ) AS Q5 GROUP BY Q5.$C0 ) AS Q6 , VASDW.ORDER_DETAIL AS Q7 , VASDW.ORDER AS Q8 , VASDW.TOUR_ORDER AS Q9 , FACT.TOUR_FACT AS Q10, DIMENSION.SHIPPER AS Q11, DIMENSION.CONSIGNEE AS Q12 WHERE (Q8.ORDER_ID = Q7.ORDER_ID) AND (Q9.ORDER_ID = Q7.ORDER_ID) AND (Q10.TOUR_ID = Q9.TOUR_ID) AND (Q10.PROJECT_ID = 17) AND (Q11.SHIPPER_ID = Q8.SHIPPER_ROLE_ID) AND (Q12.CONSIGNEE_ID = Q8.CONSIGNEE_ROLE_ID) AND (Q6.$C0 = Q9.TOUR_ID) AND NOT(Q10.TOUR_STATUS_ID IN (0, 1230, 1001, 1330, 2742, 1543)) AND ('2007-12-01' <= DATE(Q10.ACTUAL_DEPARTURE_TS)) AND (DATE(Q10.ACTUAL_DEPARTURE_TS) <= '2007-12-31')

Matching up Explain to Optimized SQL

Runstats tips • Know your runstats schedule. If stats_time doesn’t match what you expect, rerun runstats and see if access plan changes • Should you use “with distribution”?? • If SQL uses parameter markers, then no (OLTP)* • If SQL uses literals, then yes (DW) • To save lots of time/resources on runstats, use sampling • Tablesample system(10) • Sampled detailed indexes all

Use db2batch with snapshot • db2batch can take an “application snapshot” at the completion of the query. • Allows you to determine if query is heavy on data reads, index reads, sorts, etc… • -o p 3 turns on snapshot • -o r 10 says to return only 10 rows of result set. • Fetches will still occur • This allows you to not have to deal with lots of data rows in the db2batch output • Ex: db2batch –d mydb –f mysql –o r 0 –o p 3

db2batch output w/Snapshot Number of rows retrieved is: 206 Number of rows sent to output is: 10 Elapsed Time is: 172.124 seconds Locks held currently = 29 Lock escalations = 1 Total sorts = 6201 Total sort time (ms) = 4382 Sort overflows = 6 Buffer pool data logical reads = 2043472 Buffer pool data physical reads = 1083307 Buffer pool data writes = 57430 Buffer pool index logical reads = 7063581 Buffer pool index physical reads = 88149 Buffer pool index writes = 0 (and more ....) Summary of Results ================== Elapsed Agent CPU Rows Rows Statement # Time (s) Time (s) Fetched Printed 1 172.124 133.330 206 10

Passing parms into db2batch • If a query uses parameter markers, it’s important to benchmark it the same way • -m option in db2batch allows an input file containing parm values to be passed in to db2batch

Identify “hot” tables in query • Refer back to earlier part of presentation where we discussed capturing before/after snapshots from snaptab • In a hurry? • Issue a “db2 reset monitor all” from the clp and then issue a series of “db2 get snapshot for tables” commands and observe the “rows_read” metric • db2 get snapshot for tables on mydb|grep –e ‘Table Name’ –e ‘Rows Read’

Example using clp to see “hot” tables edw01:/home/ibm/inedwp/brad/sqltuning/gis/benchmark# db2 reset monitor all DB20000I The RESET MONITOR command completed successfully. edw01:/home/ibm/inedwp/brad/sqltuning/gis/benchmark# db2 get snapshot for tables on dbedwp|grep -e 'Table Name' -e 'Rows Read’ Table Name = SYSPLANAUTH Rows Read = 4 Table Name = LONG_SQL Rows Read = 289 Table Name = SYSUSERAUTH Rows Read = 17 Table Name = AVAILABLE_TRACTOR_TRIP Rows Read = 46530 Table Name = TRIP Rows Read = 8989 Table Name = TRACTOR_SEGMENTS Rows Read = 105982

How to identify “hot” indexes?? • Good question!!! • So far, I’ve not found a snapshot or any other command that directly reports read activity by index • If index reads are heavy, go back to explain and try to figure out if any indexes are undergoing major scans • Sometimes need to just start benchmarking pieces of the query to see where index reads jump up • db2pd –tcbstats index will show you total scans for each index. Good for the long view, but not for analyzing a single query • Hello IBM?? This would be a nice feature to add!! • Mon_get_index (v10) will do this

Corrective action – Indexes • New Indexes • Use Design Advisor as a starting point only!! • Existing Indexes • Review explain to ensure indexes are being used appropriately • (example, next slide)

select * from edw.freight_bill where billing_invoice_nb = '1234567'

select * from edw.freight_bill where billing_invoice_nb = '1234567' select substr(indname,1,30) indname, substr(colnames,1,70) colnames from syscat.indexes where tabname = 'FREIGHT_BILL' INDNAME COLNAMES ------------------------------ ---------------------------------------------------------------------- SQL041218090456180 +PK_FREIGHT_BILL_NB+PK_TRIP_ID FREIGHT_BILL_IX1 -DSTAGE_IDENTITY_NB FREIGHT_BILL_IX03 +RECORD_UPDATE_TS FREIGHT_BILL_IX04 +BILLING_DT+BILL_TYPE_CD+PK_FREIGHT_BILL_NB+RECORD_DELETE_FG+BILL_TO_C FREIGHT_BILL_IX5 -TRIP_LOAD_DT FREIGHT_BILL_IX06 +BILL_TO_CD+RECORD_DELETE_FG+PK_FREIGHT_BILL_NB+CONSIGNEE_CD+SHIPPER_C FRIEGHT_BILL_IX08 +PK_TRIP_ID+RECORD_DELETE_FG+AMOUNT_PAID_ON_BILL_AM+TOTAL_CHARGE_AM+PK FREIGHT_BILL_IX09 +RECORD_DELETE_FG-BILLING_INVOICE_NB

SQL Rewrite • Most difficult, but often has the biggest payback in terms of resources and performance • There are two ways of constructing a software design: One way is to make is so simple that there are obviously no deficiencies, and the other way is to make it so complicated that there are no obvious deficiencies. - C. A. R. Hoare • Everything should be made as simple as possible, but not simpler. - Albert Einstein, 1879 - 1955

One simple rewrite example SELECT DISTINCT to.tour_id FROM vasdw.tour_order to LEFT OUTER JOIN vasdw.order o ON to.order_id = o.order_id AND shipper_letter_instruction_nb IS NOT NULL WHERE o.record_update_ts > '2011-02-13 22:06:12.174554' OR to.record_update_ts > '2011-02-13 22:06:12.174554'; Both tables have indexes on record_update_ts, why aren’t they being used????

One simple rewrite example SELECT to.tour_id FROM vasdw.tour_order to LEFT OUTER JOIN vasdw.order o ON to.order_id = o.order_id AND shipper_letter_instruction_nb IS NOT NULL WHERE o.record_update_ts > '2011-02-13 22:06:12.174554' union SELECT to.tour_id FROM vasdw.tour_order to LEFT OUTER JOIN vasdw.order o ON to.order_id = o.order_id AND shipper_letter_instruction_nb IS NOT NULL WHERE to.record_update_ts > '2011-02-13 22:06:12.174554';

Corrective Action – DB CFG • Sortheap can have a huge impact on query plan • If sortheap too small, nested loop joins favored over mergescan and hash joins • Watch out if you have STMM enabled. If database is not continuously active, you may find sortheap being drained down.

The Database Whisperer: When SQL Attacks!