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

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

[English]

作者: fuyuncat

来源: www.HelloDBA.com

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

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

       

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

首先要说明一点的是,最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句。二是可以利用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即我们创建的优化建议任务的名称,nameprofile的名字,可以是任意合法名称。此外这个函数还有其他一些函数,下面是这个函数的原型:

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

  

Top

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

申明
by fuyuncat