SQL PROFILE DEMYSTIFIED : PART – II

In my earlier post SQL PROFILE DEMYSTIFIED : PART – I, I had explained the purpose and working of the sql profile. In this post, I will demonstrate the creation and use of SQL profile for a poorly tuned SQL statement and also explain the hints the that are stored as part of the profile.
Let’s start …
- SETUP
-- create a table
SQL>conn hr/hr


    drop table t purge;
    create table t (id constraint id_pk primary key, pad) as
    select rownum , lpad (‘*’, 4000, ‘*’)
    from all_objects
    where rownum <= 10000;
-- check that  there is 1 row with id = 84 (126 – 42 )
SQL>select count(*) from hr.t where id+42=126;


COUNT(*)
———-
         1
ISSUE FOLLOWING STATEMENT AND SEE ITS EXECUTION PLAN –
– NOTE THAT ESTIMATED NO. OF ROWS = 108 WHICH NEED 1404 BYTES TO BE READ
SQL>CONN / AS SYSDBA


         SET AUTOTRACE TRACEONLY
         select count(*) from hr.t where id+42=126;
         SET AUTOTRACE OFF
Execution Plan
——————————————————————————-
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT      |       |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| ID_PK |   108 |  1404 |     9   (0)| 00:00:01 |
——————————————————————————-
– Create SQL tuning task on above stmt
SQL>variable sqltext varchar2(4000);


BEGIN
 :sqltext := q’#select count(*) from hr.t where id+42=126#';
END;
/


variable spmtune   varchar2(30);


exec :spmtune := dbms_sqltune.create_tuning_task(sql_text => :sqltext);


PRINT SPMTUNE
SPMTUNE
——————————–
TASK_412
– Execute the tuning task
SQL>exec dbms_sqltune.execute_tuning_task(:spmtune);
- RUN REPORT OF SQL TUNING TASK TO VIEW RECOMMENDATIONS –
SQL>set long 10000
         set line 10000


         select dbms_sqltune.report_tuning_task(:spmtune, ‘TEXT’)
          from dual;
– I have trimmed off the output and am displaying only the recommendation
   of SQL profile
2- SQL Profile Finding (see explain plans section below)
——————————————————–
  A potentially better execution plan was found for this statement.
  Recommendation (estimated benefit: 18.05%)
  ——————————————
  – Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_412′,-
            task_owner => ‘SYS’, replace => TRUE);
  Validation results
  ——————
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.
                           Original Plan  With SQL Profile  % Improved
                           ————-  —————-  ———-
  Completion Status:            COMPLETE          COMPLETE
DBMS_SQLTUNE.REPORT_TUNING_TASK(:SPMTUNE,’TEXT’)
——————————————————————————–
  Elapsed Time(us):                1122              1270     -13.19 %
  CPU Time(us):                    1562              1562          0 %
  User I/O Time(us):                  0                 0
  Buffer Gets:                       22                18      18.18 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1
– Accept the profile
SQL>execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_412′,-
            task_owner => ‘SYS’, replace => TRUE);
-- check that profile has been saved in data dictionary
SQL> SELECT NAME, SQL_TEXT  
           FROM DBA_SQL_PROFILES
           WHERE SQL_TEXT LIKE ‘select count(*) from hr.t where id+42=126%';
NAME                           SQL_TEXT
—————————— ————————————————
SYS_SQLPROF_013b9d57d27f0001   select count(*) from hr.t where id+42=126
--Let’s execute the statement again and check the execution plan now
set autotrace traceonly
select count(*) from hr.t where id+42=126;
set autotrace off
———————————————————-
| Id  | Operation        | Name  | Rows  | Bytes | Cost  |
———————————————————-
|   0 | SELECT STATEMENT |       |     1 |    13 |    21 |
|   1 |  SORT AGGREGATE  |       |     1 |    13 |       |
|*  2 |   INDEX FULL SCAN| ID_PK |     2 |    26 |    21 |
———————————————————-
Predicate Information (identified by operation id):
—————————————————
   2 – filter(“ID”+42=126)
Note
—–
   – cpu costing is off (consider enabling it)
   – automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL profile “SYS_SQLPROF_013b9d57d27f0001″ used for this statement
– Earlier plan was :
——————————————————————————-
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT      |       |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| ID_PK |   108 |  1404 |     9   (0)| 00:00:01 |
——————————————————————————-
Note that 
   - estimated no. of rows now = 2 which is much closer to the actual value of 1
   – No. of bytes read have dropped by 1404 – 26 =   1378
-- CHECK THE HINTS SAVED AS PART OF THE PROFILE WHICH WILL BE USED FOR FURTHER EXECUTIONS OF THE STATEMENT
– Pls note that name of the table containing hints in Oracle 10g is SQLPROF$ATTR
SQL> SELECT * 
           FROM DBMSHSXP_SQL_PROFILE_ATTR
           WHERE PROFILE_NAME=’SYS_SQLPROF_013b9d57d27f0001′;
PROFILE_NAME                   COMP_DATA
—————————— ——————————————————————————–
SYS_SQLPROF_013b9d57d27f0001   <outline_data><hint><![CDATA[OPT_ESTIMATE(@”SEL$1″, TABLE, “T”@”SEL$1″, SCALE_ROWS=0.0160247093)]]></hint><hint><![CDATA[COLUMN_STATS(“HR”.”T”, “ID”, scale, length=3)]]></hint><hint><![CDATA[TABLE_STATS(“HR”.”T”, scale, blocks=10143 rows=9889.425)]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(‘8.0.0′)]]></hint></outline_data>
- LET’S ANALYZE THE HINTS
HINT – 1 
OPT_ESTIMATE(@”SEL$1″, TABLE, “T”@”SEL$1″, SCALE_ROWS=0.0160247093
This hint tells the optimizer that no. of estimated rows need to be multiplied by 0.0160247093 to get
the actual no. of rows returned by the query.
Let’s check
– Earlier plan was :
——————————————————————————-
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————-
|   0 | SELECT STATEMENT      |       |     1 |    13 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| ID_PK |   108 |  1404 |     9   (0)| 00:00:01 |
——————————————————————————-
As per earlier execution plan estimated no. of rows = 108
Let’s multiply it by the scaling factor of 0.0160247093 .
This gives us 108 * 0.0160247093 = 1.73 (= 2 approx as seen in the execution plan with SQL PROFILE)
which much closer to the 1 (actual no. of rows returned)
HINT – 2
COLUMN_STATS(“HR”.”T”, “ID”, scale, length=3)
This hint tells the oprimizer that length of id column is 3.
Let’s check the average length of column id :
SQL> select sum(length(id))/count(*) from hr.t;
SUM(LENGTH(ID))/COUNT(*)
————————
                  3.8894
We see that average length = 3.9 which is quite close the value stored as hint
HINT – 3
TABLE_STATS(“HR”.”T”, scale, blocks=10143 rows=9889.425
This hiint tells the optimizer that no. of rows in the table = 9889.425 and blocks = 10143
Let’s compare with actual values:
SQL> select count(*) from hr.t;
  COUNT(*)
———-
     10000
SQL> select owner, table_name, blocks from dba_tables where table_name=’T';
OWNER                          TABLE_NAME                         BLOCKS
—————————— —————————— ———-
HR                             T                                   10143
Note that
  – Actual no. of rows (10000) is quite close the value stored in hint (9889.425)
  – Actual No. of blocks  = Stored value 10143
HINT – 4
IGNORE_OPTIM_EMBEDDED_HINTS
This hint tells the optimizer to ignore any hints provided in the statement.
HINT – 5
OPTIMIZER_FEATURES_ENABLE(‘8.0.0′)
This hint tells the optimizer to execute the statement with features of optimizer version 8.0.0
I hope this post was useful.
Your comments and suggestions are always welcome.
References:

http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take-2/
Oracle’s OPT_ESTIMATE hint: Usage Guide

——————————————————————————————————-

 

7 thoughts on “SQL PROFILE DEMYSTIFIED : PART – II

    1. Hi Harry,
      Thanx for ur time to read my blog.

      When SQL profiles become stale, the SQL statement starts performing badly. To update SQL Profile, you need to run STA in comprehensive mode on the statement in question and accept SQL profile.

      Hope it helps you.
      Regards
      Anju

  1. With SQL PROFILE CPU cost has increased from 9 to 21 not decreased as you stated “CPU cost has dropped from 21 to 9″ when compared with original plan.

    Post is very helpful.
    Thank you.

    Guy

  2. FYI – Looks like this demo no longer works with 12.2 Statistics, feedback kicks in and the estimated rows are not off. Running the tuning task only recommends creating an index or re-writing the predicate. It no longer comes up with a profile. I’ve tested it on Linux and Windows same results.

Your comments and suggestions are welcome!