`
ufopw
  • 浏览: 163326 次
  • 性别: Icon_minigender_1
  • 来自: 合肥
社区版块
存档分类
最新评论

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

阅读更多
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 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。我们将来单独用一篇文章介绍它。

作者: fuyuncat
来源: www.HelloDBA.com
分享到:
评论

相关推荐

    oracle_11g_性能优化_调优

    SQL Tuning Advisor是Oracle 11g中的一个关键组件,它能够自动分析SQL语句的执行计划,并提供优化建议。STA的工作原理主要是通过模拟不同的执行计划来评估其对性能的影响,从而找出最佳方案。 ##### 2. SQL Access ...

    oracle10G性能调整与优化

    ### Oracle 10G 性能调整与优化 #### 一、引言 在数据库管理领域,Oracle 10G 是一个重要的版本,它不仅带来了众多功能上的改进,还为性能优化提供了更多可能性。《Oracle 10G性能调整与优化》这本书由Oracle...

    Oracle Database 10g SQL Tuning Workshop

    该课程旨在帮助用户理解并掌握如何有效地优化 SQL 语句在 Oracle 10g 数据库中的性能。通过本课程的学习,参与者将能够识别、分析和解决 SQL 性能问题,从而提高数据库的整体响应速度和吞吐量。 #### 课程目标 - **...

    oracle分析优化工具 sqlhc

    Oracle SQLHC,全称为SQL Health Check,是一款针对Oracle数据库的SQL优化分析工具。它通过深入分析SQL查询的执行计划和性能指标,帮助DBA(数据库管理员)找出性能瓶颈,优化数据库查询,提升系统整体性能。 SQL...

    Db10g SQL Tuning Vol-I

    STA 是 Db10g 中的一个自动化工具,它可以自动识别并优化执行计划不佳的 SQL 语句。STA 会分析 SQL 语句的执行情况,并提供改进建议。 ### 四、高级 SQL Tuning 技巧 #### 1. 使用 Bind Peak Bind Peak 是一种...

    ORACLE10gSqlTunning.pdf

    通过对上述内容的学习,我们了解到在ORACLE 10G环境下进行SQL调优的重要性,以及如何通过各种技术和工具来优化SQL性能。这些知识点不仅有助于提升个人的技术能力,也能帮助企业提高数据库的整体性能和稳定性。 总之...

    oracle调优STA无脑命令

    文档为oracle调优的STA无脑命令,直接安装命令贴到sqlplus里面就好,sql_id号从awr报告里面获得,想调优的语句找到sql_id,替换到文档里面的sql_id位置,最后无脑贴进去就可以使用STA调优助手了。

    Oracle Database 10g:New Features for Administrators

    6. **SQL优化顾问(SQL Tuning Advisor, STA)**:这个特性提供了自动化SQL调优,分析查询性能并提供改进建议,包括重写SQL语句、创建索引等。 7. **安全管理增强**:10g引入了统一认证、访问控制和审计功能,加强...

    Oracle Database 11g R2性能调整与优化

    其次,SQL Tuning Advisor (STA) 是用于优化SQL查询的工具。它能够分析慢速执行的SQL语句,提供改进建议,如创建索引、修改查询语句或者调整执行计划。通过应用这些建议,可以显著提高查询速度。 再者,Database ...

    SQL优化与调整

    - **SQL Tuning Advisor (STA)**:STA能够分析SQL语句并提出改进建议,包括索引调整、查询重写等。 - **SQL Access Advisor**:该工具可以根据数据库的工作负载情况,提供关于创建新索引、合并现有索引等方面的建议...

    ORACLE SQL 优化

    - **SQL Tuning Advisor (STA)**:自动分析SQL语句并提出优化建议。 - **SQL Access Advisor (SAA)**:提供关于索引、分区等结构调整的建议。 - **SQL Performance Analyzer (SPA)**:评估SQL语句性能的变化情况。 ...

    oracle高性能优化

    3. **SQL Tuning Advisor (STA)**:利用Oracle的SQL Tuning Advisor工具对性能较差的SQL进行自动优化建议。 #### 六、应用层面优化 1. **批处理**:尽可能将多个操作合并成一个批处理操作,减少网络传输次数。 2. ...

    使用SQL tuning advisor(STA)自动优化SQL

    Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短...

    SQL Tuning Advisor使用总结

    SQL Tuning Advisor(STA)是Oracle数据库中的一个强大工具,用于优化SQL语句的性能。通过自动化的诊断过程,STA能够帮助数据库管理员(DBA)识别出低效的SQL语句,并提供改进建议。本文将详细介绍如何利用SQL ...

    Oracle10g数据库自动诊断监视工具

    Oracle10g数据库自动诊断监视工具(ADDM)是Oracle数据库10g版本引入的一个重要特性,用于自动化数据库性能问题的诊断和优化建议。ADDM与SQL优化建议工具(SQL Tuning Advisor, STA)共同工作,极大地减轻了DBA的...

    Oracle Database 10g完全参考手册英文版

    9. **Performance Enhancements**:Oracle 10g在查询优化器、并行执行、内存管理等方面做了很多改进,提高了数据库的整体性能。 10. **Management Pack**:提供了一系列的管理工具和打包解决方案,如Database ...

    sql turning advise的使用

    ### SQL Turning Advise (STA) 的使用 #### 一、简介 SQL Turning Advise (STA) 是Oracle数据库中的...通过合理利用STA提供的建议,不仅可以减少手动优化SQL语句的时间消耗,还能显著提高系统的整体响应速度和稳定性。

    Oracle10g DBA 两日速成自学材料9

    - **SQL Tuning Advisor**和**SQL Access Advisor**:这两个工具都是Oracle企业管理器的一部分,主要用于优化SQL语句和数据访问策略。 - **Memory Advisor**:同样作为企业管理器的功能之一,用于监控和优化数据库...

    Oracle数据库常见故障处理和调优

    2. **SQL Tuning Advisor (STA)**:这是一个自动化的工具,用于诊断和优化SQL语句的性能问题。 3. **SQL Access Advisor (SAA)**:此工具提供了一套完整的解决方案,用于识别和优化数据库访问模式。 4. **Explain ...

Global site tag (gtag.js) - Google Analytics