Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Part 2 : Creating custom SQL Profile for a slow SQL in a Data Guard physical standby database

Posted by FatDBA on January 19, 2022

Someone asked me lately if its possible to fix a plan for a bad/slow SELECT SQL in a Data Guard environment ? The guy was asking as he was using ‘coe_xfr_sql_profile.sql‘, the script that you can use to create custom SQL Profiles to correct optimizer cost estimates to encourage good execution plans for a SQL.

Well, the post is not about using it to fix SQL plans, but is to understand how to fix a plan for a SQL that is slow on standby environment. Because if you go and try to execute the script that SQLT generates you will get ‘ORA-00600: internal error’ as dataguard is always opened in the RO mode and you cannot write anything on the database, so the question is – How to fix the plan then for a standby database ?

Here in the example, I’ve got a ADG setup with name ‘TESTSTANDBY’ and one of the SELECT statement with SQL ID 0y521mggg73pk that is slow and it has 4 different plan hash values (PHV) and there is plan flip that happens and due to that sometimes it picks the wrong plan and that’s when it goes slow. Here 1469174702 is the PHV that is WORST and both 3847062775 and 2797333706 PHVs are BEST and we can any of the two.

SQL> @1

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
TESTSTANDBY                    READ ONLY WITH APPLY PHYSICAL STANDBY



SQL_ID        SUM(EXECS)   MIN_ETIME   MAX_ETIME   NORM_STDDEV
------------- ---------- ----------- ----------- -------------
0y521mggg73pk         32         .03        9.34      143.6554


SQL> @2
Enter value for sql_id: 0y521mggg73pk

SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME AVG_CPU_TIME        AVG_LIO      AVG_PIO
------------- --------------- ------------ ------------ ------------ -------------- ------------
0y521mggg73pk      3847062775           10         .032         .021        1,448.0           .0
0y521mggg73pk      2797333706           10         .032         .021        1,448.0           .0
0y521mggg73pk      1687052623           10         .034         .025        1,448.0           .0
0y521mggg73pk      1469174702            2        9.340        1.021      541,054.0      4,155.5



-- This is what you will receive if try to run it on STANDBY database. 
SQL> @coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql

DECLARE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2652], [108], [108], [0], [0], [3891200], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 18876
ORA-06512: at "SYS.DBMS_SQLTUNE", line 9368
ORA-06512: at "SYS.DBMS_SQLTUNE", line 9328
ORA-06512: at line 37

So, what you have to do here, you need to move the file (do scp, ftp, cp whatever) ‘coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql‘ that is produced by the COE file, to the PRIMARY database and run that file from there and not on the standby.

-- Run the script on PRIMARY database
SQL>
SQL> @coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql
SQL> REM
SQL> REM $Header: 215187.1 coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql 11.4.4.4 2022/01/13 carlos.sierra $
SQL> REM
SQL> REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL> REM
SQL> REM AUTHOR
SQL> REM   carlos.sierra@oracle.com
SQL> REM
SQL> REM SCRIPT
SQL> REM   coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql
SQL> REM
SQL> REM DESCRIPTION
SQL> REM   This script is generated by coe_xfr_sql_profile.sql
SQL> REM   It contains the SQL*Plus commands to create a custom
SQL> REM   SQL Profile for SQL_ID 0y521mggg73pk based on plan hash
SQL> REM   value 3847062775.
SQL> REM   The custom SQL Profile to be created by this script
SQL> REM   will affect plans for SQL commands with signature
SQL> REM   matching the one for SQL Text below.
SQL> REM   Review SQL Text and adjust accordingly.
SQL> REM
SQL> REM PARAMETERS
SQL> REM   None.
SQL> REM
SQL> REM EXAMPLE
SQL> REM   SQL> START coe_xfr_sql_profile_0y521mggg73pk_3847062775.sql;
SQL> REM
SQL> REM NOTES
SQL> REM   1. Should be run as SYSTEM or SYSDBA.
SQL> REM   2. User must have CREATE ANY SQL PROFILE privilege.
SQL> REM   3. SOURCE and TARGET systems can be the same or similar.
SQL> REM   4. To drop this custom SQL Profile after it has been created:
SQL> REM         EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_0y521mggg73pk_3847062775');
SQL> REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL> REM         for the Oracle Tuning Pack.
SQL> REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL> REM         Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL> REM         By doing so you can create a custom SQL Profile for the original
SQL> REM         SQL but with the Plan captured from the modified SQL (with Hints).
SQL> REM
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM
SQL> VAR signature NUMBER;
SQL> VAR signaturef NUMBER;
SQL> REM
SQL> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[/* SQL Analyze(63,1) */ DELETE FROM PRTINSTANCEITER PII WHERE EX]');
 15  wa(q'[ISTS ( SELECT 1 FROM PRTPROCESS PP WHERE PP.INSTANCEID = PII.INS]');
 16  wa(q'[TANCEID||':'||PII.LASTITERATION AND CURRENTTIME < :B1 AND (STATU]');
 17  wa(q'[S = 4 OR STATUS = 5)) AND ROWNUM <=10000]');
 18  DBMS_LOB.CLOSE(sql_txt);
 19  h := SYS.SQLPROF_ATTR(
 20  q'[BEGIN_OUTLINE_DATA]',
 21  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 22  q'[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]',
 23  q'[DB_VERSION('12.2.0.1')]',
 24  q'[ALL_ROWS]',
 25  q'[OUTLINE_LEAF(@"SEL$3BA1AD7C")]',
 26  q'[UNNEST(@"SEL$1")]',
 27  q'[OUTLINE(@"DEL$1")]',
 28  q'[OUTLINE(@"SEL$1")]',
 29  q'[INDEX_RS_ASC(@"SEL$3BA1AD7C" "PP"@"SEL$1" ("PRTPROCESS"."CURRENTTIME" "PRTPROCESS"."STATUS"))]',
 30  q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3BA1AD7C" "PP"@"SEL$1")]',
 31  q'[FULL(@"SEL$3BA1AD7C" "PII"@"DEL$1")]',
 32  q'[LEADING(@"SEL$3BA1AD7C" "PP"@"SEL$1" "PII"@"DEL$1")]',
 33  q'[USE_HASH(@"SEL$3BA1AD7C" "PII"@"DEL$1")]',
 34  q'[END_OUTLINE_DATA]');
 35  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 36  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 37  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 38  sql_text    => sql_txt,
 39  profile     => h,
 40  name        => 'coe_0y521mggg73pk_3847062775',
 41  description => 'coe 0y521mggg73pk 3847062775 '||:signature||' '||:signaturef||'',
 42  category    => 'DEFAULT',
 43  validate    => TRUE,
 44  replace     => TRUE,
 45  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 46  DBMS_LOB.FREETEMPORARY(sql_txt);
 47  END;
 48  /

PL/SQL procedure successfully completed.

SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;

            SIGNATURE
---------------------
  3905242471056150906


           SIGNATUREF
---------------------
  3905242471056150906


... manual custom SQL Profile has been created

not spooling currently

COE_XFR_SQL_PROFILE_0y521mggg73pk_3847062775 completed
SQL>
SQL>

Perfect, it ran successfully on the PRIMARY database, and the same will be transferred on to the STANDBY database by its own. Let’s check if its created or moved to the STANDBY database or not. Let’s query dba_sql_profiles to see if its there …

-- Lets check it on Standby
SQL> select NAME, SQL_TEXT, CREATED, STATUS from dba_sql_profiles order by created;

NAME                           SQL_TEXT                                                                         CREATED                        STATUS
------------------------------ -------------------------------------------------------------------------------- ------------------------------ --------
coe_0y521mggg73pk_3847062775   DELETE FROM BIGTABDIXITTAB PII WHERE EXISTS ( SELECT 1                          13-JAN-22 06.28.03.000000 AM   ENABLED

So, the issue is fixed on the standby and the SQL immediately picked the right plan that we asked it to follow.

Hope It Helped!
Prashant Dixit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: