Diagnosing DB2 hot page issues

Abstract ________________________________________ Certain types of queries perform worse under increasing user load, due to hash bucket group latch contention caused by hot pages. Symptom ________________________________________ db2pd -latches shows excessive waiters on SQLB_HASH_BUCKET_GROUP_HEADER_groupLatch (“grouplatch”). db2pd -stacks shows excessive EDU waits in latch conflict routines (getConflictComplex) arising from bufferpool routines (sqlbfix, sqlbufix). Cause ________________________________________ There are numerous… Continue reading Diagnosing DB2 hot page issues

ORA-01555 encountered with huge elapsed time discrepancies in alert and awr

We encountered the well-known ORA-1555 error recently : Fri Jun 23 21:07:02 2017 ORA-01555 caused by SQL statement below (SQL ID: a520vskru5tmy, Query Duration=3304 sec, SCN: 0x010a.b517cc1a): SELECT BOND_REPO_DEAL_F.BOND_REPO_DEAL_ID … Query Duration is 3304 seconds, wow! But when I checked the AWR report, it states the Elapsed time for the SQL is only 19.29 seconds.… Continue reading ORA-01555 encountered with huge elapsed time discrepancies in alert and awr

Slow query in DB2 due to Cartesian

Recently one developer came to me complaining poor sql performance and here is how I investigated the issue. Our environment: AIX 7.1 DB2 9.7.0.7 db2top -d dbname ConnTime..: 15:10:01.316 UOW Start.: 15:10:01.322 Appl name.: db2bp DB2 user..: CAIJI OS user…: caiji Agent id..: 62181 Coord DBP.: 0 Coord id..: 46105 Client pid: 26149284 Hash joins:… Continue reading Slow query in DB2 due to Cartesian

SCN generation in RAC

MAX_COMMIT_PROPAGATION_DELAY In A Real Application Clusters Environment (Doc ID 259454.1) Best Practice: Leave Max_commit_propagation_delay=700 (default value ) The Lamport (default) propagation scheme is very efficient, and sufficient for the vast majority of applications. Only a small percentage of RAC production customers have had to change the default value of this parameter. If one can ensure… Continue reading SCN generation in RAC

Fix performance regression with SQL plan baseline

Recently one of our clients complained about some slow queries and here is what I found out. I first used ashtop and snapper to spot the sql_id of the slow sql. Thanks, Tanel! And thanks to Maris Elsins for the excellent work of awr trending. @awr_sqlid_perf_trend_by_plan.sql f1nbddjxk6d4v 8 1 INST TIME PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME_S CPU_TIME_S… Continue reading Fix performance regression with SQL plan baseline