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

Swingbench Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (10g,11g,12c). SwingBench consists of a load generator, a coordinator and a cluster overview. The software enables a load to be generated and the transactions/response times to be charted. Swingbench can be used to demonstrate and test technologies such as… Continue reading Swingbench