Oracle SQL Profile Internals

Black Box

It is very easy to pick a piece of poor performing code either by SQL text or SQL ID and then execute the Oracle SQL tuning advisor against that code. When a SQL profile is recommended though, how many people know what has actually been suggested by the profile internally?

This update covers a little known database view DBMSHSXP_SQL_PROFILE_ATTR which when supplied with the SQL profile name displays the internal hints that the profile is suggesting in XML format. This provides the missing piece to understanding what the SQL profile.

As always, here is an example;

1.  Create SQL tuning task on the statement. This can be done by SQL_ID or by SQL text.

     SQL_ID:

            DECLARE
                  l_sql_tune_task_id varchar2(100);
            BEGIN
                  l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                                  begin_snap=>1234,
                                  end_snap=>1235,
                                  sql_id=> '9yycxkm1kb8jn',
                                  scope=>dbms_sqltune.scope_comprehensive,
                                  time_limit=>600,
                                  task_name=>'SQLID',
                                  description=>'Tuning Example by SQL ID');
            END;
             /

SQL TEXT:

DECLARE
l_sql_tune_task_id      varchar2(100);
my_sqltext              CLOB;
BEGIN
     my_sqltext := ‘select count(*) from scott.emp’;

     l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                          sql_text=> my_sqltext,
                          user_name=>’SCOTT’
                          scope=>dbms_sqltune.scope_comprehensive,
                          time_limit=>600,
                          task_name=>'SQLTEXT',
                          description=>'Tuning Example by SQL ID');
END;
/

2.  Now Execute the SQL tuning task created in Step 1.

SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLID’);

OR

            SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLTEXT’;

3.  Now run the report of the SQL tuning task recommendations. -

SQL>spool results.out

                       set long 999999999
                       set lines 80
                       set pages 0

SQL> select dbms_sqltune.report_tuning_task(‘SQLTEXT’) as recommended from dual;

See below some sample output recommending a SQL profile for the statement.


3- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.05%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT', task_owner => 'SYS', replace => TRUE);

To actually accept the SQL profile

 SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT',task_owner => 'SYS', replace => TRUE);

To
check that profile has been saved in the data dictionary

SQL> SELECT NAME, SQL_TEXT
FROM DBA_SQL_PROFILES
WHERE SQL_TEXT LIKE 'select count(*) from Scott.emp%';

NAME SQL_TEXT
-----------------------------                     ------------------------------------------------            

SYS_SQLPROF_014c9e57e27g0006 select count(*) from scott.emp

To drop the sql tuning session if not applicable, or if the profile is implemented and it is wanted to remove, then  dbms_sqltune.drop_sql_profile can be executed also.

SQL> execute dbms_sqltune.drop_tuning_task(task_name=>’SQLTEXT’);

 4.  Finally to display the hints suggested by the profile for further analysis

 NOTE: Under Oracle 10g SQL profiles were stored in : SQLPROF$ and SQLPROF$ATTR.  However in 11g SQL profiles are stored in SQLOBJ$ and SQLOBJ$DATA and DBMSHSXP_SQL_PROFILE_ATTR. It is also necessary to convert the format as they are stored in XML format.

 SQL> SELECT *
FROM DBMSHSXP_SQL_PROFILE_ATTR

WHERE PROFILE_NAME='
SYS_SQLPROF_014c9e57e27g0006’;

 PROFILE_NAME COMP_DATA
 -----------------------------                       ------------------------------------------------

 SYS_SQLPROF_014c9e57e27g0006 <outline_data><hint><![CDATA[PARALLEL]]></hint></outline_data>

So it can be seen by analysis of the above that the Parallel hint has been suggested by the profile. However if other recommendations were made these in turn can be analyzed for relevance and understanding for further tuning.

  Some examples of other types of hint are:

 OPT_ESTIMATE(@"SEL$1", TABLE, "EMP"@"SEL$1", SCALE_ROWS=0.018)]]>

  The scale_rows figure is multiplied by the number of rows in the table to get an estimate on the number of rows to be retrieved by the query and supply to the optimizer.

 COLUMN_STATS("SCOTT"."EMP", "ID", scale, length=3)]]>

  The above is storing the estimate on the average column length of a field.

 TABLE_STATS("SCOTT"."EMP", scale, blocks=10123 rows=988.542)]]>

  The above is displaying the number of rows and blocks to the optimizer.

 IGNORE_OPTIM_EMBEDDED_HINTS]]>

  The above tells the optimizer to ignore any hints given in the statement itself.

 OPTIMIZER_FEATURES_ENABLE('9.0.0')]]>

The above tells the optimizer to execute the statement with features enabled in version 9.0.0

Comments:

Hi Andy

Thank you for your posting. It is very interesting and helpful as always.

My question when I tested the above, an error
' ORA-13786: missing SQL text of statement object "1" for tuning task "SQLTEXT_6" '
was thrown, when I was trying to apply the recommendation using "execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT_6',task_owner => 'SYS', replace => TRUE);"

In metalink article ID 1067741.1, it said this is a bug and the workaround is to disable the procedure parameter FORCE_MATCH. Then I set it to Force but same error again.

Can you please share any idea about this ?
Thank you very much for your time and your posts again

Posted by guest on May 29, 2013 at 03:01 AM EST #

Hi there, thanks for going through the update. In terms of the error, obviously the first thing is to always look at the error code and meaning.

ORA-13786:missing SQL text of statement object "string" for tuning task "string"
Cause: The user attempted to accept SQL profile for an object that has not a SQL text associated to it.
Action: Check the identifier of the object and retry the operation.

Obviously your post does not have all the entry to see / check the tuning task etc. I have seen 2 issues in the past, the first you found but there is also another mainly experienced from grid control.

You can run the following after submitting the task and getting the error:

DBA_ADVISOR_RATIONALE returns the following message for the object_id:

Note the object_id is given in the error code returned. From your post this looked to be 1.

SQL> select message from dba_advisor_rationale where object_id=1363;

MESSAGE
--------------------------------------------
This attribute enables parallel execution.

If the SQL statement above being tuned generates a parallel plan as above then there is another bug associated with this error: bug 10313110

Cause

Since the SQL profile is for a plan that runs with parallel execution, the profile type needs to be a parallel profile, however the "profile_type => DBMS_SQLTUNE.PX_PROFILE" is missing in the command generated by the SQL Tuning Advisor.

A PX_PROFILE is one that enables parallel execution. For such profiles you must pass a value with the PROFILE_TYPE parameter set to 'PX_PROFILE' for ACCEPT_SQL_PROFILE. Without this the command takes the default value, of REGULAR_PROFILE.

The quick workaround is that you just have to apply it manually with the option DBMS_SQLTUNE.PX_PROFILE and you are set :)

DECLARE
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => 'SQLTEXT_6',
name => 'TEST_PROFILE',
force_match => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/
The other thing is to review the recommendations and see what it is showing, there have been a couple of issues relating to where the optimizer suggest several conflicting solutions and when applying an issue is raised. You can also query out from the base tables mentioned in the note the items suggested in the recommendations and check these manually.

You should also review versions / patches and PSU's to ensure you are not operating too far behind. Obviously not an end user / business critical issue but may give you operational evidence that it is time to bring forward development and test on a newer version. If the profile is able to be generated elsewhere you can also look at moving the profile or converting to a baseline.

Hope that gets you started please let me know how you go.
Regards

Andy Baker.

Posted by Andy Baker on May 29, 2013 at 08:27 AM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today