Abdul Hafeez Kalsekar -- Oracle database tuning case studies

   We came across user requirement where sql profile and sql baseline from 1 sql id needs to  be copied to another sqlid .  SQL_ID1 and SQL_HANDLE1 with a poorly performing Execution Plan with Hash Value PHV1. With a small modification to this query, like adding a CBO Hint or removing one, we obtain query Q2, which performs well, and has SQL_ID2, SQL_HANDLE2 and PHV2. So what we want it to associate PHV2 to SQL_ID This can be done using coe_load_sql_baseline.sql / coe_load_sql_profile.sql   provided under Metalink (Doc ID 1400903.1 )  coe_load_sql_baseline.sql / coe_load_sql_profile.sql are scripts provided with the SQLT tool in the "utl" folder. Have stored  both scripts  below   coe_load_sql_profile.sql : https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/02/coeloadsqlprofilesql-as-per-doc-id.html coe_load_sql_baseline.sql : https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/02/coeloadsqlbaselinesql-as-per-doc-id.html Refe...

 SPO coe_load_sql_baseline.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUM 20 SQLP SQL>; SET SERVEROUT ON SIZE UNL; REM REM $Header: 215187.1 coe_load_sql_baseline.sql 11.4.4.6 2012/06/02 carlos.sierra $ REM REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved. REM REM AUTHOR REM   carlos.sierra@oracle.com REM REM SCRIPT REM   coe_load_sql_baseline.sql REM REM DESCRIPTION REM   This script loads a plan from a modified SQL into the SQL REM   Plan Baseline of the original SQL. REM   If a good performing plan only reproduces with CBO Hints REM   then you can load the plan of the modified version of the REM   SQL into the SQL Plan Baseline of the orignal SQL. REM   In other words, the original SQL can use the plan that was REM   generated out of the SQL with hints. REM RE...

 SPO coe_load_sql_profile.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUM 20 SQLP SQL>; SET SERVEROUT ON SIZE UNL; REM REM $Header: 215187.1 coe_load_sql_profile.sql 11.4.4.4 2012/04/02 carlos.sierra $ REM REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved. REM REM AUTHOR REM   carlos.sierra@oracle.com REM REM SCRIPT REM   coe_load_sql_profile.sql REM REM DESCRIPTION REM   This script loads a plan from a modified SQL into a Custom SQL REM   Profile for the original SQL. REM   If a good performing plan only reproduces with CBO Hints REM   then you can load the plan of the modified version of the REM   SQL into a Custom SQL Profile for the orignal SQL. REM   In other words, the original SQL can use the plan that was REM   generated out of the SQL with hints. REM REM P...

   We all knew to  how to  check sql monitoring  for long running sql .  But there were  situations where sql were not monitored .  To avoid  these issue we  came across below 4 solutions  :    1)  /*+ MONITOR */ hint 2) sql_monitor  event  3) DBMS_SQL_MONITOR.begin_operation 4) “_sqlmon_max_planlines”    parameter   “_sqlmon_max_planlines”  Parameter  There is a hidden parameter “_sqlmon_max_planlines” which states that any SQL with a plan in excess of 300 lines should not be monitored.  The solution is to change either the session or the system to allow monitoring to happen when the plan is over 300 lines. alter system  set "_sqlmon_max_planlines"=500 scope=memory sid='*'; or alter session set "_sqlmon_max_planlines"=500; The negative side effect it that the monitoring will use more resources (primarily memory and CPU), which is why there are default limits on this f...