第二部分:profile的控制
对profile的控制比较简单:修改和删除。包DBMS_SQLTUNE提供了两个存储过程来完成这两个操作:ALTER_SQL_PROFILE和DROP_SQL_PROFILE。
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/
1.Profile的修改
ALTER_SQL_PROFILE的原型是:
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name IN VARCHAR2,
attribute_name IN VARCHAR2,
value IN VARCHAR2);
其中,name就是profile的名字;attribute_name是需要修改的属性的名字;value是修改后的值。例如,需要使’my_sql_profile’失效,可以修改STATUS属性为DISABLED:
SQL> BEGIN
2 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
3 name => 'my_sql_profile',
4 attribute_name => 'STATUS',
5 value => 'DISABLED');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autot on exp
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2298554444
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| SMALLTAB | 1 | 18 | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TABLE_NAME"='TAB$')
2.删除profile
DROP_SQL_PROFILE的原型是:
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
其中,name是profile的名字,ignore为TRUE时,当指定的profile不存在时不报错。
3.确认某条语句是否已经有相应profile
当然我们也希望能确认某条语句是否已经形成了profile,看是否有必要在对它进行tuning。这时就可以利用SQLTEXT_TO_SIGNATURE函数:
SQL> set serveroutput on
SQL> declare
2 v_signature number;
3 begin
4 v_signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
5 sql_text => 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = ''TAB$''',
6 force_match => FALSE);
7 if v_signature is null then
8 DBMS_OUTPUT.put_line('no such sql text in profile');
9 else
10 DBMS_OUTPUT.put_line('The sql text is in profile');
11 end if;
12 end;
13 /
The sql text is in profile
PL/SQL procedure successfully completed.
其中,sql_text就是需要检测的内容;force_match的解释与ACCEPT_SQL_PROFILE中相应参数是一样的。
第三部分:profile的转储与移植
在某些环境下,比如生产环境的安全要求非常高,无法直接对生产环境进行优化,只能在一个与生产环境保持一致的镜像环境中作优化,那么,希望将优化结果实施到生产环境中去,该怎么办呢?DBMS_SQLTUNE包提供了另外几个函数用于将profile的数据导出到表中,然后可以再将表中的数据移植到其它环境中,下面介绍一下如何使用它们。
第一步:创建存储表
先利用存储过程创建一张存储profile的表:
SQL> begin
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
3 table_name => 'PROFILE_STGTAB',
4 schema_name => 'DEMO',
5 tablespace_name => 'EDGARDEMO');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> desc PROFILE_STGTAB
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
PROFILE_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
SIGNATURE NUMBER
SQL_TEXT CLOB
DESCRIPTION VARCHAR2(500)
TYPE VARCHAR2(9)
STATUS VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES SQLPROF_ATTR
VERSION NUMBER
SPARE1 CLOB
SPARE2 BLOB
Table_name是需要创建的存储表的名字,schema_name是它所属schema的名字,tablespace_name是所属表空间。
此外,需要注意的是,这个存储过程实际上做的是create table操作,也就是DDL操作,所以不要在一个事务中调用它。使用这个存储过程需要有CREATE ANY TABLE的权限。
第二步:将profile中数据存入存储表中
利用存储过程可以将profile中数据存储刚才建的那张存储表中:
SQL> BEGIN
2 DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
3 profile_name => '%',
4 profile_category => 'DEFAULT',
5 staging_table_name => 'PROFILE_STGTAB',
6 staging_schema_owner => 'DEMO');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> select profile_name, category, sql_text from PROFILE_STGTAB;
PROFILE_NAME CATEGORY SQL_TEXT
------------------------------ ---------------------------------------------------------------------------------------
my_sql_profile DEFAULT select /*+no_index(smalltab smalltab_idx1)*/count(*)
frommalltab where table_name = 'TAB$'
profile_name是需要存储的profile的名字(大小写敏感),默认为’%’,即这个CATEGORY下的所有profile;profile_category即需要存储的profile所在category名字(大小写敏感),默认是DEFAULT;staging_table_name就是用于存储profile数据的表名(大小写敏感);staging_schema_owner是该表所属的schema。调用该函数需要有的CREATE ANY SQL PROFILE系统权限,并且对存储表要有SELECT权限。
要注意一点:调用了这个存储过程,会执行COMMIT,所以要注意对前面事务的影响。
另外,可以通过存储过程来修改存储表中的信息:
SQL> begin
2 DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF (
3 old_profile_name => 'my_sql_profile',
4 new_profile_name => 'new_sql_profile',
5 new_profile_category => 'DEV',
6 staging_table_name => 'PROFILE_STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /
PL/SQL procedure successfully completed.
Old_profile_name是需要修改的存储在存储表中的profile的名字(大小写敏感);new_profile_name是需要修改为的新名字,默认是NULL,既不修改名字;new_profile_category是需要修改为的新目录名字,默认是NULL,既不修改;staging_table_name是需要修改的存储表的名字(大小写敏感);staging_schema_owner是存储表所属的schema。
当然,调用这个函数需要有对存储表的UPDATE权限。
第三步:从存储表中导入profile数据
将profile中的数据导入到存储表中后,我们就可以将存储表中的数据导到其他数据库中去了:
SQL> create table STGTAB as select * from PROFILE_STGTAB@EDGAR;
Table created.
然后将数据导出为profile:
SQL> begin
2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
3 profile_name => 'new_sql_profile',
4 profile_category => 'DEV',
5 replace => FALSE,
6 staging_table_name => 'STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select name, category, sql_text from DBA_SQL_PROFILES
2 ;
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ ----------------------------------------------------
new_sql_profile DEV select /*+no_index(smalltab smalltab_idx1)*/count(*)
from smalltab where table_name = 'TAB$'
可以看到,profile已经被成功导入。调用该存储过程需要有的CREATE ANY SQL PROFILE系统权限。参数的含义与前面的函数是一样的。
补充:存储表中的数据的删除和存储表的删除。
实际上,存储表数据的删除和存储表的删除和普通表的操作是一样的,使用DELETE和DROP就可以了:
SQL> delete from STGTAB;
1 row deleted.
SQL> drop table STGTAB;
Table dropped.
三、总结
正如文章开始提到的,这个工具让语句调优工作变得非常简单,DBA可以用最短的时间、最好的方式给出优化建议,并有最安全的方式来调试优化结果。此外,STA还有一套对于数据仓库环境下调忧十分有用的工具:SQL Tuning Set。我们将来单独用一篇文章介绍它。
转载自:
http://www.hellodba.com/reader.php?ID=163&lang=CN
http://www.hellodba.com/reader.php?ID=164&lang=CN
http://www.hellodba.com/reader.php?ID=165&lang=CN