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.
Mohamed Houri’s Oracle Notes 