Mohamed Houri’s Oracle Notes

October 1, 2016

SQL Profile: or when the hint exceeds 500 bytes

Filed under: Oracle — hourim @ 5:13 pm

A couple of days ago I have been in a weird situation following a 12c upgrade:

  • a critical query (46h7mfaac03yv) started to perform very poorly impacting the overall response time of a critical report
  • there were no ”good” execution plans in the historical execution of this query to use for fixing a SQL Profile.
  • The current execution plan has more than a hundred of operations making the issue very hard to solve very quickly
  • the real time sql monitoring report shows several parts of the plan contributing to the alteration of the response time.

One thing I have the good habit to do in many situations like the one exposed here is to check the historical execution plans of the same query in TEST environment. I also sometimes backup outlines of critical queries into a dedicated windows directory to use them if the need arises. Hopefully one of the TEST environment contains an execution plans with very good average elapsed time. Having this plan at my disposal, I used the following strategy to fix the good plan for the bad performing query:

  • I took the outline of the TEST execution plan put it into the sql_text of the PRODUCTION sql_id
  • I used the real time sql monitoring report to fill up the corresponding bind variables values
  • I opened a SQLPlus session in PRODUCTION and executed the new hinted query

And as expected the hinted query identified by the tandem(sql_id: 3ts967mzugyxw, child number:0) completes in few seconds. All what remains to do before announcing the good news for the client was to use a custom sql script with which I will transfer the execution plan of the hinted query to the production non hinted one. Something resembling to this:

create table t1 as select rownum n1 from dual connect by level <=1e2;
select count(1) from t1 where n1 <= 5;

---------------------------------------
SQL_ID  addzft9frsckw, child number 0
-------------------------------------
select count(1) from t1 where n1 <= 5

Plan hash value: 3724264953
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| T1   |     5 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 – filter("N1"<=5)


SELECT count(1) from t1 where n1 <= 5;

----------------------------------------
SQL_ID  2w9a295mxcjgx, child number 0
-------------------------------------
SELECT count(1) from t1 where n1 <= 5

Plan hash value: 3724264953
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| T1   |     5 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"<=5)   

Creating a SQL profile for the first sql_id using the metadata of the second one is accomplished by means of the following call:

SQL> @fixProfilefromCache
Enter value for sql_id_from: 2w9a295mxcjgx
Enter value for child_no_from: 0
Enter value for sql_id_to: addzft9frsckw
Enter value for sql_id_to: addzft9frsckw
Enter value for sql_id_to: addzft9frsckw

PL/SQL procedure successfully completed.
select count(1) from t1 where n1 <= 5;

----------------------------------------
SQL_ID  addzft9frsckw, child number 0
-------------------------------------
select count(1) from t1 where n1 <= 5

Plan hash value: 3724264953
-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |       |
|   1 |  SORT AGGREGATE    |      |     1 |
|*  2 |   TABLE ACCESS FULL| T1   |     5 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"<=5)

Note
-----
   - SQL profile profile_addzft9frsckw_attach used for this statement
 

Unfortunately there are situations where you will stop to be lucky. Look to that weird situation I have been faced to when I applied the same script for the real life query:

SQL> @fixProfilefromCache
Enter value for sql_id_from: 3ts967mzugyxw
Enter value for child_no_from: 0
Enter value for sql_id_to: 46h7mfaac03yv
Enter value for sql_id_to: 46h7mfaac03yv
Enter value for sql_id_to: 46h7mfaac03yv
declare
*
ERROR at line 1:
ORA-05602: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 11

Few minutes of PL/SQL investigations reveal that the above error occurs around the following piece of code in the FixProfilefromCache.sql script

declare
   ar_profile_hints sys.sqlprof_attr;
   cl_sql_text clob;
begin
   select
      extractvalue(value(d), '/hint') as outline_hints
         bulk collect into ar_profile_hints
   from
      xmltable('/*/outline_data/hint'
         passing (
            select
               xmltype(other_xml) as xmlval
            from
               gv$sql_plan
            where
               sql_id = '&sql_id_from'
               and child_number = &child_no_from
               and other_xml is not null
			   and rownum =1
         )
      ) d;

And, to be more precise, exactly at this line

   select
      extractvalue(value(d), '/hint') as outline_hints
        bulk collect into ar_profile_hints

What is the definition of the sys.sqlprof_attr object type I am using very often without being preoccupied by its data type so far?

   desc SQLPROF_ATTR
        SQLPROF_ATTR VARRAY(2000) OF VARCHAR2(500)

It’s a list of strings not allowed to exceed 500 bytes each. Is this meaning that one of my real life query outline hints exceeded 500 bytes? Let’s check:

  select
    substr(outline_hints,1,45) outline_hints
   ,outline_hints_length
from
 (
   select
      extractvalue(value(d), '/hint') as outline_hints  
     ,length(extractvalue(value(d), '/hint')) as outline_hints_length
   from
      xmltable('/*/outline_data/hint'
         passing (
            select
               xmltype(other_xml) as xmlval
            from
               gv$sql_plan
            where
               sql_id = '3ts967mzugyxw'
               and child_number = 0
               and other_xml is not null
			   and rownum =1
         )
      ) d
  order by outline_hints_length desc
  )
where rownum <= 1;

OUTLINE_HINTS                                 OUTLINE_HINTS_LENGTH
--------------------------------------------- --------------------
USE_CONCAT(@"SEL$C59E9DD6" 8 OR_PREDICATES(3)                  508

That’s it. One of the outline hints exceeds the 500 bytes upper limit imposed by the sys.sqlprof_attr type. Don’t try to create your proper sys.sqlprof_attr type allowing to store more than 500 bytes. The import_sql_profile procedure of the dbms_sqltune package doesn’t allow a parameter with a different data type:

 PROCEDURE import_sql_profile(
                   sql_text      IN CLOB,
                   profile       IN sqlprof_attr,
                   name          IN VARCHAR2 := NULL,
                   description   IN VARCHAR2 := NULL,
                   category      IN VARCHAR2 := NULL,
                   validate      IN BOOLEAN  := TRUE,
                   replace       IN BOOLEAN  := FALSE,
                   force_match   IN BOOLEAN  := FALSE);

Hopefully it seems that Oracle has already foreseen this kind of situation and has overloaded the above procedure in order to accept the hint as a CLOB data type via the proxfile_xml parameter:

PROCEDURE import_sql_profile(
                   sql_text      IN CLOB,
                   profile_xml   IN CLOB,
                   name          IN VARCHAR2 := NULL,
                   description   IN VARCHAR2 := NULL,
                   category      IN VARCHAR2 := NULL,
                   validate      IN BOOLEAN  := TRUE,
                   replace       IN BOOLEAN  := FALSE,
                   force_match   IN BOOLEAN  := FALSE);

Finally I have created a new script which I have named fixprofilexmlfromcache and which I have successfully used to transfer the good plan to the sql_id of the real life query as shown below:

SQL> @FixProfileFromXmlFromCache
Enter value for sql_id_from: 3ts967mzugyxw
Enter value for child_no_from: 0
Enter value for sql_id_to: 46h7mfaac03yv
Enter value for sql_id_to: 46h7mfaac03yv
Enter value for sql_id_to: 46h7mfaac03yv

PL/SQL procedure successfully completed.

SUMMARY

While it is extremely rare to use a SQL Profile with hints exceeding 500 bytes it is however good to know that there is an alternative to overcome this limit by using the second overloaded import_sql_profile procedure of the dbms_sqltune package which accepts the outline hints as a CLOB instead of a varray of 500 bytes.

4 Comments »

  1. Nice to have the warning.

    The same problem (though with no solution) appears with the SQL_PATCH mechanism – it’s indirectly limited to a single varchar2(500): http://jonathanlewis.wordpress.com/2014/07/06/sql-plan-baselines-2/#comment-69029

    Comment by Jonathan Lewis — October 1, 2016 @ 6:03 pm | Reply

  2. Thank you Mohamed.
    I’m adding this script right now in my toolbox

    Comment by ahmed aangour — October 2, 2016 @ 6:40 am | Reply

  3. Very intersting !. Thank’s.

    Comment by Noureddine — October 5, 2016 @ 9:00 am | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: