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 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

Fixed Views definition and difference

There are several fixed views to retrieve SQL-related information from different perspectives. As always, multiple tools brings benefits as well as confusions. Here is an excerpt from Oracle doc regarding general explanation of them. V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the… Continue reading Fixed Views definition and difference

Optimize SQL with subquery factoring

Recently, one of our clients reported one particular statement, which runs faster on one node (within 10 secs) while the response time is longer on the other node (about 1 mins or even longer). Our Server Enviroment: OS : AIX 7.1 using PowerVM DB : Oracle RAC The original SQL statement: SELECT distinct t1.EIR_ENTY_SRNO,… Continue reading Optimize SQL with subquery factoring

Useful documents regarding shared pool/library cache

Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1) Troubleshooting Library Cache: Lock, Pin and Load Lock (Doc ID 444560.1) What is “Library cache lock” ? What is “Library cache pin”? Troubleshooting: High Version Count Issues (Doc ID 296377.1) What do the reasons given in v$sql_shared_cursor mean? How to Find which Session is Holding… Continue reading Useful documents regarding shared pool/library cache