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(*)

———-

– 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

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

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

| 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

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

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

| 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 :

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

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

| 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 :

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

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

| 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://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

http://kerryosborne.oracle-guy.com/2009/07/how-to-attach-a-sql-profile-to-a-different-statement/

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

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

Related links :

Home

Database Index

Tuning Index

Buffer Cache Wait Events

Clustering Factor Demystified
Find Values Of Another Session’s Parameters

SQL Profile Demystified : Part – I

7 thoughts on “SQL PROFILE DEMYSTIFIED : PART – II”

Hari Krishna says:

February 7, 2013 at 4:17 am

Hi Anju,

Excellent post on SQL_Profiles. One question -

I’ve heard that SQL_profiles becomes stale. How to findout stale sql_profiles and how to update them?

Thanks,
Harry

1. Anju Garg says:
  
  February 7, 2013 at 5:32 am
  
  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
  
pratik says:

October 23, 2013 at 6:13 pm

It was a Very useful

Guy says:

May 11, 2017 at 8:05 am

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

1. Anju Garg says:
  
  May 12, 2017 at 3:33 am
  
  Thanks Guy for pointing out. Updated
  
  Your comments and suggestions are always welcome!
  
  Regards
  Anju
  
Ric says:

August 6, 2017 at 7:20 am

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.

1. Ric says:
  
  August 6, 2017 at 7:37 am
  
  OK got it to work, just have to flush the shared pool first.