|
SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具，它的特点是简单、智能，DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。 使用STA一定要保证优化器是CBO模式下。 一、利用STA优化语句 对于问题语句的收集，可以通过Oracle10g的另一个自动化工具：数据库自动诊断监视工具（ADDM）。它的使用可以参照另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。 我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的，STA是用起来很简单（只要你会调用存储过程，都能使用这个工具），三个步骤就可以完成一次语句调优。 测试环境创建： SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a; Table created. SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a; Table created. SQL> ALTER TABLE bigtab MODIFY (empno NUMBER); Table altered. SQL> DECLARE n NUMBER; BEGIN FOR n IN 1..100 LOOP INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a; COMMIT; END LOOP; END; / PL/SQL procedure successfully completed. 这里创建一张大表和一张小表，并且都没有索引，下面执行一个查询： SQL> set timing on SQL> set autot on SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name; COUNT(*) ---------- 135000 Elapsed: 00:00:05.59 Execution Plan ---------------------------------------------------------- Plan hash value: 3089226980 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 | | 1 | SORT AGGREGATE | | 1 | 36 | | | |* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 | | 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 16013 consistent gets 14491 physical reads 0 redo size 412 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 可以看出这个语句执行性能很差：16013 consistent gets。 第一步：创建优化任务并执行 通过调用函数CREATE_TUNING_TASK来创建优化任务，调用存储过程EXECUTE_TUNING_TASK执行该任务： SQL> set autot off SQL> set timing off SQL> DECLARE 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 BEGIN 5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name'; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => my_sqltext, 8 user_name => 'DEMO', 9 scope => 'COMPREHENSIVE', 10 time_limit => 60, 11 task_name => 'tuning_sql_test', 12 description => 'Task to tune a query on a specified table'); 13 14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test'); 15 END; 16 / PL/SQL procedure successfully completed. 在函数CREATE_TUNING_TASK，sql_text是需要优化的语句，user_name是该语句通过哪个用户执行，scope是优化范围 （limited或comprehensive），time_limit优化过程的时间限制，task_name优化任务名称，description优 化任务描述。 可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。 SQL> select task_name, status from USER_ADVISOR_LOG where task_name='tuning_sql_ test'; TASK_NAME STATUS ------------------------------ ----------- tuning_sql_test COMPLETED 第二步：查看优化结果 通过函数可以查看优化结果。 SQL> set long 10000 SQL> set longchunksize 1000 SQL> set linesize 100 SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST') ---------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : tuning_sql_test Tuning Task Owner : DEMO Scope : COMPREHENSIVE Time Limit(seconds) : 60 Completion Status : COMPLETED Started at : 11/30/2005 13:16:43 Completed at : 11/30/2005 13:16:44 Number of Index Findings : 1 Schema Name: DEMO SQL ID : 6p64dnnsqf9pm SQL Text : select count(*) from bigtab a, smalltab b where a.object_name=b.table_name ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- Index Finding (see explain plans section below) The execution plan of this statement can be improved by creating one or more indices. Recommendation (estimated benefit: 100%) ---------------------------------------- - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DEMO.IDX$$_06C50001 on DEMO.SMALLTAB('TABLE_NAME'); - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index DEMO.IDX$$_06C50002 on DEMO.BIGTAB('OBJECT_NAME'); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. However, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. This will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original ----------- Plan hash value: 3089226980 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 | | 1 | SORT AGGREGATE | | 1 | 36 | | | |* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 | | 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME") 2- Using New Indices -------------------- Plan hash value: 494801882 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3)| 00:00:14 | | 1 | SORT AGGREGATE | | 1 | 36 | | | |* 2 | HASH JOIN | | 155K| 5462K| 1108 (3)| 00:00:14 | | 3 | INDEX FAST FULL SCAN| IDX$$_06C50001 | 1223 | 22014 | 3 (0)| 00:00:01 | | 4 | INDEX FAST FULL SCAN| IDX$$_06C50002 | 1205K| 20M| 1093 (2)| 00:00:14 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME") ------------------------------------------------------------------------------- 看一下这个优化建议报告： 第一部分是关于这次优化任务的基本信息：如任务名称、执行时间、范围、涉及到的语句等等。 第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述：可以通过建立更多的所引来提高性能；然后是建议的具体内容：在表 smalltab的字段table_name上创建索引，在表bigtab的字段object_name上创建索引；最后是相关注意事项：此次优化虽然给 出了创建索引的建议，但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析，那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。 最后，报告还给出了原有的查询计划，以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。 第三步：按照优化建议进行优化 首先要说明一点的是，最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句。二是可以利用sql profile这对某条语句优化或者针对某些会话进行优化（下一章会给出sql profile如何使用）。我们这里只是验证一下优化建议的效果。 按照建议，创建两个索引： SQL> create index smalltab_idx1 on smalltab(table_name); Index created. SQL> create index bigtab_idx1 on bigtab(object_name); Index created. SQL> analyze table smalltab compute statistics; Table analyzed. SQL> analyze table bigtab compute statistics; Table analyzed. SQL> set timing on SQL> set autot on SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name; COUNT(*) ---------- 135000 Elapsed: 00:00:01.09 Execution Plan ---------------------------------------------------------- Plan hash value: 2594317117 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 36 | 1119 (3)| 00:00:14 | | 1 | SORT AGGREGATE | | 1 | 36 | | | |* 2 | HASH JOIN | | 155K| 5463K| 1119 (3)| 00:00:14 | | 3 | INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 1223 | 22014 | 3 (0)| 00:00:01 | | 4 | INDEX FAST FULL SCAN| BIGTAB_IDX1 | 1205K| 20M| 1104 (2)| 00:00:14 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME") Statistics ---------------------------------------------------------- 332 recursive calls 0 db block gets 4999 consistent gets 1 physical reads 0 redo size 412 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 12 sorts (memory) 0 sorts (disk) 1 rows processed 可以看出，consistent gets比优化前大大下降了，优化建议确实提高了性能。 Oracle10g让优化变得如此简单。 二、利用sql profile存储优化策略 利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。你可以按照STA给出的建议重写语句。但是，有些情 况下，你可能无法重写语句（比如在生产环境中，你的语句又在一个包中）。这个时候就可以利用sql profile，将优化策略存储在profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用profile的策 略，生成新的查询计划。 第一部分：profile的使用 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等系统权限。 还是举例说明吧： 第一步：给用户赋权限 SQL> conn sys/sys as sysdba Connected. SQL> GRANT CREATE ANY SQL PROFILE TO DEMO; Grant succeeded. SQL> GRANT DROP ANY SQL PROFILE TO DEMO; Grant succeeded. SQL> GRANT ALTER ANY SQL PROFILE TO DEMO; Grant succeeded. SQL> conn demo/demo Connected. SQL> create index smalltab_idx1 on smalltab(table_name); Index created. SQL> analyze table smalltab compute statistics; Table analyzed. SQL> set autot on 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 | ------------------------------------------------------------------------------- 第二步，创建、执行优化任务 SQL> DECLARE 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 BEGIN 5 my_sqltext := 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = ''TAB$'''; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => my_sqltext, 8 user_name => 'DEMO', 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 14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test'); 15 END; 16 / PL/SQL procedure successfully completed. 第三步：查看优化建议 SQL> set autot off SQL> set long 10000 SQL> set longchunksize 1000 SQL> set linesize 100 SQL> 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 : DEMO Scope : COMPREHENSIVE Time Limit(seconds) : 60 Completion Status : COMPLETED Started at : 11/29/2005 14:52:09 Completed at : 11/29/2005 14:52:09 Number of SQL Profile Findings : 1 DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- Schema Name: DEMO SQL ID : 3kta54ycuqccb SQL Text : select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = 'TAB$' ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') -------------------------------------------------------------------------------- --------------------------------------------------------------------------- A potentially better execution plan was found for this statement. Recommendation (estimated benefit: 90.94%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test', replace => TRUE); ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') -------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ 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 | | | DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- |* 2 | TABLE ACCESS FULL| SMALLTAB | 1 | 18 | 11 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TABLE_NAME"='TAB$') 2- Using SQL Profile -------------------- Plan hash value: 2664476518 DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | INDEX RANGE SCAN| SMALLTAB_IDX1 | 1 | 18 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') ---------------------------------------------------------------------------------------------------- 2 - access("TABLE_NAME"='TAB$') ------------------------------------------------------------------------------- 这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。 第三步：接受profile SQL> DECLARE 2 my_sqlprofile_name VARCHAR2(30); 3 BEGIN 4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 5 task_name => 'sql_profile_test', 6 name => 'my_sql_profile'); 7 END; 8 / PL/SQL procedure successfully completed. 在这里用了包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的效果 SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta ble_name = 'TAB$'; COUNT(*) ---------- 1 Execution Plan ---------------------------------------------------------- Plan hash value: 2664476518 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | INDEX RANGE SCAN| SMALLTAB_IDX1 | 1 | 18 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TABLE_NAME"='TAB$') Note ----- - SQL profile "my_sql_profile" used for this statement 可以看到，语句采用了profile中的数据，创建了新的查询计划。并且在查询计划中还有一些附加信息，表明这个语句是采用了’my_sql_profile’这个profile，而不是根据对象上面的统计数据来生成的查询计划。 第二部分：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 [url=mailto  ROFILE_STGTAB@EDGAR]PROFILE_STGTAB@EDGAR[/url];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。我们将来单独用一篇文章介绍它。 |
|
|
