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

[English]

作者： fuyuncat

来源： www.HelloDBA.com

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

分享到 

第二部分：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。我们将来单独用一篇文章介绍它。