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

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

分享到

第三步：按照优化建议进行优化

首先要说明一点的是，最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句。二是可以利用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(*)

----------

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(*)

----------

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

 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(*)

----------

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，而不是根据对象上面的统计数据来生成的查询计划。

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

下一篇：利用Oracle 10g SQL优化器（STA）优化语句(1)

本类中的所有文章