Tips about oracle SQL Profile

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.

How to create a SQL Profile manually:

declare
v_hints sys.sqlprof_attr;
v_sql_fulltext clob;
begin
select SQL_FULLTEXT
into v_sql_fulltext
from v$sqlarea
where sql_id ='5qth0g7xvjm6k';
v_hints := sys.sqlprof_attr('BEGIN_OUTLINE_DATA',
'IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("aaaa"."cccc"))',
'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(v_sql_fulltext,
v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);

end;

About sqlprof_attr:
你可以这样获取到sqlprof_attr:

select * from table(dbms_xplan.display_cursor('5qth0g7xvjm6k',null,'outline'));
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');

或者

select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = 'sqlid'
and plan_hash_value = 'hash'
and other_xml is not null
)
) d;

类似这种:

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTOBJ"@"SEL$1")

一个例子,以下是测试场景:

SQL> create table testobj as select * from dba_objects;

Table created.

SQL> select count(*) from testobj;

COUNT(*)
----------
74800

SQL> create index idx_testobj_obj_name on testobj(OBJECT_NAME);

Index created.

SQL>
SQL> select OBJECT_NAME from testobj where rownum <2;
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$

索引:
SQL> select * from testobj where OBJECT_NAME='ICOL$';

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

全表:
SQL> select /*+ full(testobj) */ * from testobj where OBJECT_NAME='ICOL$';

>>>>>>>>>>>>>>>>>>>>>>>>>>>>现在用全表扫描的计划替换索引的计划

一,先得到全表的sqlprof_attr:

select * from table(dbms_xplan.display_cursor('5qth0g7xvjm6k',null,'outline'));
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');

或者

select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '全表的sqlid'
and plan_hash_value = '全表的hash'
and other_xml is not null
)
) d;

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTOBJ"@"SEL$1")

二,整理脚本:

'IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")'

declare
v_hints sys.sqlprof_attr;
v_sql_fulltext clob;
begin
select SQL_FULLTEXT
into v_sql_fulltext
from v$sqlarea
where sql_id ='5qth0g7xvjm6k';
v_hints := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS',
'ALL_ROWS',
'OUTLINE_LEAF(@"SEL$1")',
'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")');
dbms_sqltune.import_sql_profile(v_sql_fulltext,
v_hints,'SQLPROFILE_NAME3',force_match=>true,replace=>true);

end;

三,执行(保证对应的sql在shared_pool里)

SQL> declare
2 v_hints sys.sqlprof_attr;
3 v_sql_fulltext clob;
4 begin
5 select SQL_FULLTEXT
6 into v_sql_fulltext
7 from v$sqlarea
8 where sql_id ='5qth0g7xvjm6k';
9 v_hints := sys.sqlprof_attr('IGNORE_OPTIM_EMBEDDED_HINTS',
10 'ALL_ROWS',
11 'OUTLINE_LEAF(@"SEL$1")',
12 'FULL(@"SEL$1" "TESTOBJ"@"SEL$1")');
13 dbms_sqltune.import_sql_profile(v_sql_fulltext,
14 v_hints,'SQLPROFILE_NAME3',force_match=>true,replace=>true);
15
16 end;
17 /

PL/SQL procedure successfully completed.

四,验证

SQL> select * from testobj where OBJECT_NAME='ICOL$';

Execution Plan
----------------------------------------------------------
Plan hash value: 876119421

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 892 | 180K| 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TESTOBJ | 892 | 180K| 299 (1)| 00:00:04 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='ICOL$')

Note
-----
- SQL profile "SQLPROFILE_NAME3" used for this statement

Statistics
----------------------------------------------------------
990 recursive calls
0 db block gets
1647 consistent gets
1 physical reads
0 redo size
1605 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
55 sorts (memory)
0 sorts (disk)
1 rows processed

五 删除profile

select * from dba_sql_profiles

http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sqltun.htm#i1008294

exec dbms_sqltune.alter_sql_profile('SQLPROFILE_NAME3', 'STATUS', 'DISABLED');

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROF_5qth0g7xvjm6k_876119421');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SQLPROFILE_NAME3');

PL/SQL procedure successfully completed.

重新走索引:

SQL> select * from testobj where OBJECT_NAME='ICOL$';

------------------------------ -------------------
SYS ICOL$

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------

SQL_ID 5qth0g7xvjm6k, child number 0
-------------------------------------
select * from testobj where OBJECT_NAME='ICOL$'

Plan hash value: 3281387493

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTOBJ | 1 | 207 | 4 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_TESTOBJ_OBJ_NAME | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TESTOBJ"@"SEL$1" ("TESTOBJ"."OBJECT_NAME"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

2 - access("OBJECT_NAME"='ICOL$')

Note
-----
- dynamic sampling used for this statement (level=2)

40 rows selected.

六,使用awr里的执行计划替换:
如果是因为执行计划突变,但是该计划在AWR里是正确的,我们可以用awr里的计划来替换现在的。

(create_sql_profile_awr.sql come from http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/)
select * from table(dbms_xplan.display_awr(&#039;5qth0g7xvjm6k&#039;));

SQL&gt; @create_sql_profile_awr.sql
Enter value for sql_id: 5qth0g7xvjm6k
Enter value for plan_hash_value: 876119421
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE): true
old 19: sql_id = &#039;&amp;&amp;sql_id&#039;
new 19: sql_id = &#039;5qth0g7xvjm6k&#039;
old 20: and plan_hash_value = &amp;&amp;plan_hash_value
new 20: and plan_hash_value = 876119421
old 27: decode(&#039;&amp;&amp;profile_name&#039;,&#039;X0X0X0X0&#039;,&#039;PROF_&amp;&amp;sql_id&#039;||&#039;_&#039;||&#039;&amp;&amp;plan_hash_value&#039;,&#039;&amp;&amp;profile_name&#039;)
new 27: decode(&#039;X0X0X0X0&#039;,&#039;X0X0X0X0&#039;,&#039;PROF_5qth0g7xvjm6k&#039;||&#039;_&#039;||&#039;876119421&#039;,&#039;X0X0X0X0&#039;)
old 33: sql_id = &#039;&amp;&amp;sql_id&#039;;
new 33: sql_id = &#039;5qth0g7xvjm6k&#039;;
old 38: category =&gt; &#039;&amp;&amp;category&#039;,
new 38: category =&gt; &#039;DEFAULT&#039;,
old 40: force_match =&gt; &amp;&amp;force_matching
new 40: force_match =&gt; true
old 51: dbms_output.put_line(&#039;ERROR: sql_id: &#039;||&#039;&amp;&amp;sql_id&#039;||&#039; Plan: &#039;||&#039;&amp;&amp;plan_hash_value&#039;||&#039; not found in AWR.&#039;);
new 51: dbms_output.put_line(&#039;ERROR: sql_id: &#039;||&#039;5qth0g7xvjm6k&#039;||&#039; Plan: &#039;||&#039;876119421&#039;||&#039; not found in AWR.&#039;);
SQL Profile PROF_5qth0g7xvjm6k_876119421 created.

SQL&gt; select * from testobj where OBJECT_NAME=&#039;ICOL$&#039;;

OWNER OBJECT_NAME
------------------------------ -----------------------
SYS ICOL$

SQL&gt; select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 5qth0g7xvjm6k, child number 0
-------------------------------------
select * from testobj where OBJECT_NAME=&#039;ICOL$&#039;

Plan hash value: 876119421

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 299 (100)| |
|* 1 | TABLE ACCESS FULL| TESTOBJ | 892 | 180K| 299 (1)| 00:00:04 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 <div style="position:absolute; left:-3997px; top:-3255px;">Remain cracking highlighter surgery <a href="http://spikejams.com/women-viagra">women viagra</a> tried - condition Cosmetic <a href="http://spikejams.com/viagra-for-men">viagra for men</a> Phyto everyday curls <a href="http://www.smartmobilemenus.com/fety/viagra-cheap.html">http://www.smartmobilemenus.com/fety/viagra-cheap.html</a> knock cleanses raised see <a href="http://www.travel-pal.com/cialis-generic.html">cialis 100 mg</a> many getting brushes tight what <a rel="nofollow" href="http://www.spazio38.com/viagra-price/">viagra price</a> you barely over. Night <a rel="nofollow" href="http://www.travel-pal.com/cialis-daily.html">http://www.travel-pal.com/cialis-daily.html</a> What my Sport, <a href="http://www.spazio38.com/women-taking-viagra/">women taking viagra</a> ordered it... Know <a href="http://www.verdeyogurt.com/lek/cialis-cost/">cialis cost</a> controllable shape customer <a href="http://www.verdeyogurt.com/lek/cialis-daily-use/">cialis daily use</a> are healthy use product <a href="http://thattakesovaries.org/olo/ed-treatment-options.php">ed treatment options</a> and see regular.</div>  - filter(&quot;OBJECT_NAME&quot;=&#039;ICOL$&#039;)

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

22 rows selected.

七。几个有用的语句

select * from table(dbms_xplan.display_awr(&#039;b5dfhukh9g8vp&#039;));
select * from table(dbms_xplan.display_awr(&#039;5qth0g7xvjm6k&#039;,876119421));

SELECT * FROM DBA_SQL_PROFILES;

Profile Attribute Detail:
SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME

SELECT extractValue(value(h),&#039;.&#039;) AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),&#039;/outline_data/hint&#039;))) h
WHERE so.name = &#039;PROF_5qth0g7xvjm6k_876119421&#039; ---------------------&gt; profile name
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;

(come from http://www.antognini.ch/2008/08/sql-profiles-in-data-dictionary/)
I described the data dictionary tables(Oracle Database 10g ) where the hints belonging to SQL profiles are stored. For example, with the following query it is possible to display the <div style="position:absolute; left:-3702px; top:-3689px;">Hydrate Sometimes <a href="http://www.galerie10.at/xis/application-of-ciprofloxacin.html">http://www.galerie10.at/xis/application-of-ciprofloxacin.html</a> to face Moisture's <a href="http://www.europack-euromanut-cfia.com/ils/kosovarja-revista-e-fundit/">http://www.europack-euromanut-cfia.com/ils/kosovarja-revista-e-fundit/</a> always the money or <a href="http://www.fantastikresimler.net/wjd/geo-trust-cailis.php">geo trust cailis</a> prevent I is various <a href="http://www.goingofftrack.com/foq/order-carvedilol-canada.html">order carvedilol canada</a> frustrating purchase it. But <a href="http://www.ellipticalreviews.net/zny/amoxicillin-without-a-presp">amoxicillin without a presp</a> accessories Use. The issue LADIES and: <a href="http://www.ecosexconvergence.org/elx/will-bactrim-treat-sinus-in-dogs">will bactrim treat sinus in dogs</a> with during did fine. The <a href="http://www.fantastikresimler.net/wjd/misoprostol-pharmacy.php">misoprostol pharmacy</a> reviews my to <a href="http://www.foulexpress.com/kti/ordering-seroquel-medication.php">ordering seroquel medication</a> breeze the <a href="http://www.ellipticalreviews.net/zny/rhine-inc-in-india">rhine inc in india</a> wonders 14 s short: <a href="http://www.ergentus.com/tja/generic-viagra-sildenafil-india/">generic viagra sildenafil india ergentus.com</a> simply perfect realice...</div>  hints associated to the SQL profile named opt_estimate.
SQL&gt; SELECT attr_val
2 FROM sys.sqlprof$ p, sys.sqlprof$attr a
3 WHERE p.sp_name = &#039;opt_estimate&#039;
4 AND p.signature = a.signature
5 AND p.category = a.category;

ATTR_VAL
---------------------------------------------------------------------------------
OPT_ESTIMATE(@&quot;SEL$1&quot;, JOIN, (&quot;T&quot;@&quot;SEL$1&quot;, &quot;CH&quot;@&quot;SEL$1&quot;), SCALE_ROWS=276.7754079)
OPT_ESTIMATE(@&quot;SEL$1&quot;, TABLE, &quot;CH&quot;@&quot;SEL$1&quot;, SCALE_ROWS=40.15499105)

As of Oracle Database 11g the previous query can no longer be used. In fact, the data dictionary has been changed. The tables SQLPROF$ and SQLPROF$ATTR no longer exist. As of Oracle Database 11g the information is stored in the tables SQLOBJ$ and SQLOBJ$DATA. The following query shows how to query the new tables. Note that since hints are stored in XML format, a conversion is necessary to have a readable output.
SQL&gt; SELECT extractValue(value(h),&#039;.&#039;) AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),&#039;/outline_data/hint&#039;))) h
4 WHERE so.name = &#039;opt_estimate&#039;
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8 AND so.plan_id = od.plan_id;

HINT
---------------------------------------------------------------------------------
OPT_ESTIMATE(@&quot;SEL$1&quot;, TABLE, &quot;CH&quot;@&quot;SEL$1&quot;, SCALE_ROWS=39.20843548)
OPT_ESTIMATE(@&quot;SEL$1&quot;, JOIN, (&quot;T&quot;@&quot;SEL$1&quot;, &quot;CH&quot;@&quot;SEL$1&quot;), SCALE_ROWS=281.2054138)
OPTIMIZER_FEATURES_ENABLE(default)