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
Tales From A Lazy Fat DBA 