HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

利用Oracle 10g SQL优化器(STA)优化语句(3)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2006-02-07 14:50:11

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

       

第二部分:profile的控制

 

profile的控制比较简单:修改和删除。包DBMS_SQLTUNE提供了两个存储过程来完成这两个操作:ALTER_SQL_PROFILEDROP_SQL_PROFILE

 

BEGIN

  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');

END;

/

1Profile的修改

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);

 

其中,nameprofile的名字,ignoreTRUE时,当指定的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下的所有profileprofile_category即需要存储的profile所在category名字(大小写敏感),默认是DEFAULTstaging_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系统权限。参数的含义与前面的函数是一样的。

补充:存储表中的数据的删除和存储表的删除。

实际上,存储表数据的删除和存储表的删除和普通表的操作是一样的,使用DELETEDROP就可以了:

SQL> delete from STGTAB;
 
1 row deleted.
 
SQL> drop table STGTAB;
 
Table dropped.

三、总结

正如文章开始提到的,这个工具让语句调优工作变得非常简单,DBA可以用最短的时间、最好的方式给出优化建议,并有最安全的方式来调试优化结果。

此外,STA还有一套对于数据仓库环境下调忧十分有用的工具:SQL Tuning Set。我们将来单独用一篇文章介绍它。

  

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat