【DB笔试面试608】在Oracle中，如何使用STA来生成SQL Profile？
【DB笔试面试608】在Oracle中，如何使用STA来生成SQL Profile？
小麦苗DBA宝典
发布于 2019-09-29 16:06:02
发布于 2019-09-29 16:06:02
♣
题目部分
在Oracle中，如何使用STA来生成SQL Profile？
♣
答案部分
利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，SQL语句又在一个包中）。这个时候就可以利用Sql Profile，将优化策略存储在Profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用Profile的策略，生成新的查询计划。
第一步：给用户赋权限
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@dlhr>
SYS@dlhr>
SYS@dlhr>
SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;
Grant succeeded.
SYS@dlhr> conn lhr/lhr
Connected.
LHR@dlhr>
LHR@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects;
Table created.
LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);
PL/SQL procedure successfully completed.
LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@dlhr> set autot off
LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ;
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
7jt1btjkcczb8
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100
7suktf0w95cry
EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L
HR_20160525_01 where object_id = 100
第二步：创建、执行优化任务
LHR@dlhr> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'LHR',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'sql_profile_test',
12 description => 'Task to tune a query on a specified table');
13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
14 END;
15 /
PL/SQL procedure successfully completed.
或者也可以使用sqlid来生成优化任务，如下：
LHR@dlhr> DECLARE
2 a_tuning_task VARCHAR2(30);
3 BEGIN
4 a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '7jt1btjkcczb8',
5 task_name => 'sql_profile_test_SQLID');
6 dbms_sqltune.execute_tuning_task(a_tuning_task);
7 END;
8 /
PL/SQL procedure successfully completed.
第三步：查看优化建议
LHR@dlhr> set autot off
LHR@dlhr> set long 10000
LHR@dlhr> set longchunksize 1000
LHR@dlhr> set linesize 100
LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_profile_test
Tuning Task Owner : LHR
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/25/2016 16:58:31
Completed at : 05/25/2016 16:58:32
-------------------------------------------------------------------------------
Schema Name: LHR
SQL ID : 9kzm8scz6t92z
SQL Text : select /*+no_index(TB_LHR_20160525_01
TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.83%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',
task_owner => 'LHR', 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
Elapsed Time (s): .006278 .00004 99.36 %
CPU Time (s): .003397 .000021 99.38 %
User I/O Time (s): 0 0
Buffer Gets: 1249 2 99.83 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
2- Using SQL Profile
--------------------
Plan hash value: 661515879
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
2 - access("OBJECT_ID"=100)
-------------------------------------------------------------------------------
这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。
第四步：接受profile
LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3612989399
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 351 (2)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| TB_LHR_20160525_01 | 1 | 5 | 351 (2)| 00:00:05 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1249 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);
PL/SQL procedure successfully completed.
LHR@dlhr> set autot off
LHR@dlhr> SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints
2 FROM dba_sql_profiles d,
3 dba_advisor_tasks e,
4 SYS.SQLOBJ$DATA A,
5 SYS.SQLOBJ$ B,
6 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
7 '/outline_data/hint'))) h
8 where a.signature = b.signature
9 and a.category = b.category
10 and a.obj_type = b.obj_type
11 and a.plan_id = b.plan_id
12 and a.signature = d.signature
13 and d.task_id=e.task_id
14 and d.name = 'SYS_SQLPROF_0154e728ad3f0000'
15 ;
TASK_NAME NAME
------------------------------ ------------------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
HINTS
----------------------------------------------------------------------------------------------------
sql_profile_test SYS_SQLPROF_0154e728ad3f0000
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
OPTIMIZER_FEATURES_ENABLE(default)
sql_profile_test SYS_SQLPROF_0154e728ad3f0000
select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
where object_id = 100
IGNORE_OPTIM_EMBEDDED_HINTS
在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL;
task_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE)
RETURN VARCHAR2;
Description是profile的描述信息；task_owner是优化建议任务的所有者；replace为TRUE时，如果这个profile已经存在，就代替它；force_match为TURE时，表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似，为FALSE时，与cursor_sharing设置为EXACT时类似，即完全匹配。
这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE_CATEGORY，他的默认值是DEFAULT。而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而如果我们给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。
此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。
第五步：查看profile的效果
LHR@dlhr> set autot on
LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 661515879
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| TB_LHR_20160525_01_IDX | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=100)
Note
-----
- SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从NOTE部分可以看到，语句采用了profile中的数据，创建了新的查询计划。并且在查询计划中还有一些附加信息，表明这个语句是采用了“SYS_SQLPROF_0154e728ad3f0000”这个Profile，而不是根据对象上面的统计数据来生成的查询计划。
但上述方法主要是依赖sql tuning advisor,如果它无法生成你想要的执行计划.你还可以通过手动的方式,通过sql profile把hint加进去.复杂的SQL的hint可以采用脚本coe_xfr_sql_profile.sql来产生原语句的outline data和加hint语句的outline data，然后替换对应的SYS.SQLPROF_ATTR，最后执行生成的sql就可以了。
使用PLSQL DEVELOPER 11查看执行计划，如下图，新版本的好处：
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-08-03，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读