实战:oracle sql profile

最新推荐文章于 2026-03-08 02:26:30 发布
原创最新推荐文章于 2026-03-08 02:26:30 发布 · 6.4k 阅读
1 ·
本内容遵循CC 4.0 BY-SA版权协议
oracle 专栏收录该内容
86 篇文章
订阅专栏
本文详细介绍了Oracle数据库10g中SQL配置文件(SQL Profile)的概念、使用步骤及管理方法，并演示了如何创建、应用和调整SQL Profile以优化SQL语句执行计划。
************************************************************
第一部分：profile概念

************************************************************


Oracle数据库10g使用了一个叫做SQL配置文件的新方法弥补了存储概要的缺点， 
DBA可以使用SQL调整顾问(STA)或SQL访问顾问(SAA)来识别可以得到更好性能的SQL语句， 
这些语句可以保存在SQL调整集、一个AWR快照或保存在当前的库缓存中，一旦识别出调整候
选者， 这些顾问程序就开始分析捕获到的语句以期获得更好的性能，然后生成专用的语句扩展(就叫
做SQL配置文件)并重写SQL语句以在执行期间获取更佳的性能。

与存储概要类似，一个SQL配置文件提供了使用更好的执行计划的能力(如果这个执行计
划是可用的)，SQL配置文件也可以象存储概要一样分阶段执行，或限制到对特定会话才能执
行该SQL配置文件，但是大多数重要的改进超过了存储概要，
至少有两方面：
(a)自我调整的能力保障了SQL配置文件能提供最好的执行计划
(b)检查SQL配置文件不再有效的能力(因此ADDM建议需要生成一个新的SQL配置文件)



SQL Profile对于一下类型语句有效：
     SELECT语句；
     UPDATE语句；
     INSERT语句（仅当使用SELECT子句时有效）；
     DELETE语句；
     CREATE语句（仅当使用SELECT子句时有效）；
     MERGE语句（仅当作UPDATE和INSERT操作时有效）。
另外，使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。



************************************************************
第二部分：profile使用

************************************************************


---1.准备测试环境

create table t1
(
sid number not null ,
sname varchar2(10)
)
tablespace test;







--循环导入数据
declare
        maxrecords constant int:=200000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t1 values(i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据！ ');
    commit;
    end; 
/



create index index_01 on t1(sid);



exec dbms_stats.gather_table_stats('SCOTT','T1',cascade => true);



---2.执行一个查询

var v varchar2(5);  
exec :v :=10;  
set autot trace;
select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v;



执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|   139   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |   126K|   139   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("SID"<=TO_NUMBER(:V))


统计信息
----------------------------------------------------------
          5  recursive calls
          5  db block gets
        513  consistent gets
          0  physical reads
        496  redo size
        723  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed



set autotrace off;


---3.创建优化任务

exec dbms_sqltune.drop_tuning_task('ocpyang_tuning_sql_test');
DECLARE
my_task_name VARCHAR2(30);
my_sqltext   CLOB;
BEGIN
my_sqltext := 'select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,
user_name   => 'SCOTT',   --注意是大小写否则会报错提示用户无效
scope       => 'COMPREHENSIVE',
time_limit  => 60,
task_name   => 'ocpyang_tuning_sql_test',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'ocpyang_tuning_sql_test');
END;
/



/******函数CREATE_TUNING_TASK


在函数CREATE_TUNING_TASK，sql_text是需要优化的语句，
user_name是该语句通过哪个用户执行，scope是优化范围（limited或comprehensive），
time_limit优化过程的时间限制，task_name优化任务名称，description优化任务描述。
可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务


********/




---4.执行优化任务


exec dbms_sqltune.execute_tuning_task('ocpyang_tuning_sql_test');


---5.检查优化任务的状态

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='ocpyang_tuning_sql_test';

TASK_NAME                      STATUS
------------------------------ -----------
ocpyang_tuning_sql_test        COMPLETED



-----6. 查看报表


通过使用dbms_sqltune.report_tning_task函数获到优化任务的结果.

set serveroutput on
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'ocpyang_tuning_sql_test') from DUAL;



报告结果如下：


DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : ocpyang_tuning_sql_test
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_12222
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Started at         : 01/13/2014 17:37:55
Completed at       : 01/13/2014 17:37:55

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 9vaj2kxqhud9p
SQL Text   : select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 92.06%)
  ------------------------------------------
  - 考虑接受推荐的 SQL 概要文件。
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'ocpyang_tuning_sql_test', task_owner => 'SCOTT', replace =>
            TRUE);

DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 此 SQL 语句至少缺少一个重要绑定值。指导分析的准确性可能取决于提供的所有重要绑
定值。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|   139   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |   126K|   139   (3)| 00:00:02 |
--------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SID"<=TO_NUMBER(:V))

2- Using SQL Profile
--------------------
Plan hash value: 1514635137

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          | 10000 |   126K|    11   (0)| 00:00:01
 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       | 10000 |   126K|    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_01 |  1800 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("SID"<=TO_NUMBER(:V))

DBMS_SQLTUNE.REPORT_TUNING_TASK('OCPYANG_TUNING_SQL_TEST')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------




-----7. 接受推荐的SQL Profile

/*******************


函数：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，
那就既可以看优化建议的实际效果又不影响生产环境。

*******************/


declare
my_sqlprofile_name VARCHAR2(200);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
(
task_name => 'ocpyang_tuning_sql_test',
name =>'ocpyang01',
task_owner => 'SCOTT',
replace => TRUE
);
end;
/


----8.查看profile的效果


var v varchar2(5);  
exec :v :=10;  
set autot trace;

select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 1514635137

----------------------------------------------------------------------------------------
| Id  | Operation		    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |	       | 10000 |   126K|    11	 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       | 10000 |   126K|    11	 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | INDEX_01 |  1800 |       |     6	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("SID"<=TO_NUMBER(:V))

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




----9.修改profile

ALTER_SQL_PROFILE的原型是：
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
   name                 IN  VARCHAR2,
   attribute_name       IN  VARCHAR2,
   value                IN  VARCHAR2);

其中，name就是profile的名字；
attribute_name是需要修改的属性的名字；
value是修改后的值


begin
dbms_sqltune.alter_sql_profile(
name            => 'ocpyang01',
attribute_name  => 'status',
value           => 'disabled');
end;
/

---查看禁用后的效果

select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v;

已选择10行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 | 10000 |   126K|   139   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1	 | 10000 |   126K|   139   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("SID"<=TO_NUMBER(:V))


统计信息
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	500  consistent gets
	  0  physical reads
	  0  redo size
	730  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client






---10.删除profile



BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'ocpyang01');
END;
/





---11.确认某条语句是否已经有相应profile


set serveroutput on
declare
v_signature number;
begin
v_signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
                      sql_text    => 'select /*+ no_index(t1 index_01) */  * from t1 where sid<=:v',
                      force_match => FALSE);
if v_signature is null then
DBMS_OUTPUT.put_line('profile没有这个sql语句');
else
DBMS_OUTPUT.put_line('profile存在该语句');
end if;
end;
/


************************************************************
第三部分：profile的转储与移植

************************************************************


第一步：创建存储表


/********

DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
   table_name            IN VARCHAR2,
   schema_name           IN VARCHAR2 := NULL,
   tablespace_name       IN VARCHAR2 := NULL);


Table_name是需要创建的存储表的名字，schema_name是它所属schema的名字，
tablespace_name是所属表空间。
此外，需要注意的是，这个存储过程实际上做的是create table操作，
也就是DDL操作，所以不要在一个事务中调用它。使用这个存储过程需要有CREATE ANY TABLE的权限。


*******/

begin
dbms_sqltune.create_stgtab_sqlprof (
table_name            => 'profile_stgtab',
schema_name           => 'scott',
tablespace_name       => 'test');
end;
/


desc profile_stgtab





第二步：将profile中数据存入存储表中

利用存储过程可以将profile中数据存储刚才建的那张存储表中：



BEGIN
  DBMS_SQLTUNE.pack_stgtab_sqlprof(      
    profile_name         => '%',   
    staging_table_name   => 'PROFILE_STGTAB',
    staging_schema_owner => 'SCOTT' );
END;
/ 



/********参数介绍

DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
   profile_name          IN VARCHAR2 := '%',
   profile_category      IN VARCHAR2 := 'DEFAULT',
   staging_table_name    IN VARCHAR2,
   staging_schema_owner  IN VARCHAR2 := NULL);



profile_name是需要存储的profile的名字（大小写敏感），默认为’%’，即这个CATEGORY下的所有profile；
profile_category即需要存储的profile所在category名字（大小写敏感），默认是DEFAULT；
staging_table_name就是用于存储profile数据的表名（大小写敏感）；
staging_schema_owner是该表所属的schema。调用该函数需要有的CREATE ANY SQL PROFILE系统权限，
并且对存储表要有SELECT权限。
要注意一点：调用了这个存储过程，会执行COMMIT，所以要注意对前面事务的影响。



---常见错误:


第 1 行出现错误:
ORA-38172: 没有符合指定过滤器的 SQL 管理对象 ORA-06512: 在
"SYS.DBMS_SMB", line 663
ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 6325
ORA-06512: 在 line 2

解决办法:

SELECT * FROM dba_sql_profiles;

查看profile是否禁用.





*********/



select  task_id, category, sql_text from PROFILE_STGTAB;



-----第三步：导入profile到其它服务器

begin
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name          => 'new_sql_profile',
replace               => FALSE,
staging_table_name    => 'STGTAB',
staging_schema_owner  => 'SCOTT');
end;
/