ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle sqlprofile 偷梁换柱

Oracle sqlprofile 偷梁换柱

原创 Linux操作系统 作者:parameters 时间:2012-05-29 13:32:18 0 删除 编辑

注:测试环境 win7+oracle10.2.0.4

基础知识:

STA—sql tuning advisor;

使用sqlprofile 时需要创建 sql tuning task,也就是说要先搞清楚 sta 的使用情况,下面来看看sta 的使用

Sta 的使用可以通过em完成,也可以通过oracle db中的 dbms_sqltue完成;我比较习惯后者;dbms_sqltune的使用主要分为两步:

—创建sql tuning task

—执行 sql tuning task

来看一个简单的实例:

SQL> desc emp 名称                                                  是否为空? 类型 —————————————————– ——– EMPNO                                                 NOT NULL NUMBER(4) ENAME                                                          VARCHAR2(10) JOB                                                            VARCHAR2(9) MGR                                                            NUMBER(4) HIREDATE                                                       DATE SAL                                                            NUMBER(7,2) COMM                                                           NUMBER(7,2) DEPTNO                                                         NUMBER(2)SQL> show userUSER 为 “SCOTT”SQL> DECLARE  2    huosi_task1 VARCHAR2(30);  3    huosi_txt1 CLOB;  4  BEGIN  5    huosi_txt1 := ‘SELECT * ‘   ||  6                  ‘FROM EMP ‘ || ‘WHERE EMPNO=7369′ ;  7    huosi_task1 := DBMS_SQLTUNE.CREATE_TUNING_TASK(  8                             sql_text => huosi_txt1,  9                             user_name => ‘SCOTT’, 10                             scope => ‘COMPREHENSIVE’, 11                             time_limit => 60, 12                             task_name => ‘test_task1′, 13                             description => ‘first test task’); 14  END; 15  /PL/SQL 过程已成功完成。SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => ‘test_task1′);PL/SQL 过程已成功完成。SQL> select status from dba_advisor_log where task_name=’test_task1′;STATUS———————-COMPLETEDSQL> set long 65536SQL> set longchunksize 65536SQL> set linesize 100SQL> select dbms_sqltune.report_tuning_task(‘test_task1′) from dual  2  ;SQL>

注意此时没有返回数据的原因是,sqlplus 不显示lob数据,可以plsql查看,如图:

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name                  : test_task1Tuning Task Owner                 : SCOTTScope                             : COMPREHENSIVETime Limit(seconds)               : 60Completion Status                 : COMPLETEDStarted at                        : 05/29/2012 10:35:39Completed at                      : 05/29/2012 10:35:40Number of Statistic Findings      : 1——————————————————————————-Schema Name: SCOTTSQL ID     : 31qp81kj64a38SQL Text   : SELECT * FROM EMP WHERE EMPNO=7369——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- Statistics Finding———————  表 “SCOTT”.”EMP” 及其索引的优化程序统计信息已失效。  Recommendation  ————–  – 考虑收集此表的优化程序统计信息。    execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname =>            ‘EMP’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,            method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);  Rationale  ———    为了选择好的执行计划, 优化程序需要此表的最新统计信息。——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original———–Plan hash value: 2949544139————————————————————————————–| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |————————————————————————————–|   0 | SELECT STATEMENT            |        |     1 |    32 |     1   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    32 |     1   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |————————————————————————————–Predicate Information (identified by operation id):—————————————————   2 – access(“EMPNO”=7369)——————————————————————————-

到这里 STA 的基本使用就完成了;

Sql profile :

Sql profile 是10g 的一个新特性,可以通过em 也可以通过dbms_sqltune进行管理;

Sql profile 本身是存储在数据字典中的信息的一个集合,这些信息可以被cob使用,以使优化器能够生成一个更优化的执行计划;profile中的信息能够改善优化器中的cardinality和selectivity,从而使优化器能够选择更好的执行计划;

当时sql profile 并不包括单个的执行计划的信息,当优化器选择执行计划时它本身会包含以下的信息:

  •   The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
  • The supplemental statistics in the SQL profile

Sql profile 本身并不会像 outline一样固定一个sql 的执行计划,比如当数据量发生变化时执行计划可能就会变化;能够应用sql profile 的语句包括:

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

在10g当中,oracle CBO的功能进一步加强,也就是说oracle 的优化器会根据系统的统计信息、sqlprofile等来自动选择执行计划,比如在某些情况下优化器会跳过hint;

下面来看看 sqlprofile是如何偷梁换柱,避开hint 的;

SQL> show userUSER 为 “SCOTT”SQL> create table test_sp(n number);表已创建。SQL> declare  2            begin  3             for i in 1 .. 10000 loop  4                 insert into test_sp values(i);  5                 commit;  6             end loop;  7            end;  8  /PL/SQL 过程已成功完成。

 

SQL> create index test_idx on test_sp(n);索引已创建。SQL> exec dbms_stats.gather_table_stats(”,’TEST_SP’);PL/SQL 过程已成功完成。SQL> set autotrace onSQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1  2  ;         N———-         1执行计划———————————————————-Plan hash value: 3988747878—————————————————————————–| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT  |         |     1 |     3 |     7   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TEST_SP |     1 |     3 |     7   (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):—————————————————   1 – filter(“N”=1)统计信息———————————————————-          1  recursive calls          0  db block gets         24  consistent gets          0  physical reads          0  redo size        412  bytes sent via SQL*Net to client        400  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

此时可以看出 sql是进行的全表扫描;

重新开启一个会话:

SQL> declare  2    my_task_name VARCHAR2(30);  3    my_sqltext CLOB;  4    begin  5       my_sqltext := ‘select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1′;  6       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(  7       sql_text => my_sqltext,  8       user_name => ‘SCOTT’,  9       scope => ‘COMPREHENSIVE’, 10       time_limit => 60, 11       task_name => ‘my_sql_tuning_task_3′, 12       description => ‘Task to tune a query on a specified table’); 13  end; 14  /PL/SQL 过程已成功完成。SQL> begin  2  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_3′);  3  end;  4  /PL/SQL 过程已成功完成。SQL>

看看 sqlprofile的信息;

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name                  : my_sql_tuning_task_3Tuning Task Owner                 : SYSScope                             : COMPREHENSIVETime Limit(seconds)               : 60Completion Status                 : COMPLETEDStarted at                        : 05/29/2012 11:32:13Completed at                      : 05/29/2012 11:32:13Number of SQL Profile Findings    : 1——————————————————————————-Schema Name: SCOTTSQL ID     : 1ks8q8x9ttbbySQL Text   : select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1

 

——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- SQL Profile Finding (see explain plans section below)——————————————————–  为此语句找到了性能更好的执行计划。  Recommendation (estimated benefit: 86.4%)  —————————————–  – 考虑接受推荐的 SQL 概要文件。    execute dbms_sqltune.accept_sql_profile(task_name =>            ‘my_sql_tuning_task_3′, replace => TRUE);——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original With Adjusted Cost——————————Plan hash value: 3988747878—————————————————————————–| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT  |         |     1 |     3 |     7   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TEST_SP |     1 |     3 |     7   (0)| 00:00:01 |—————————————————————————–

 

Predicate Information (identified by operation id):—————————————————   1 – filter(“N”=1)2- Using SQL Profile——————–Plan hash value: 2882402178—————————————————————————–| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     1   (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):—————————————————   1 – access(“N”=1)——————————————————————————-
SQL> DECLARE2  my_sqlprofile_name VARCHAR2(30);

3  begin

4  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

5  task_name => ‘my_sql_tuning_task_3′,

6  name => ‘my_sql_profile’);

7  end;

8  /

PL/SQL 过程已成功完成。

SQL>

此时再次执行查询语句:

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1;

 

         N———-         1执行计划———————————————————-Plan hash value: 2882402178—————————————————————————–| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     1   (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):—————————————————   1 – access(“N”=1)Note—–   – SQL profile “my_sql_profile” used for this statement统计信息———————————————————-          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        412  bytes sent via SQL*Net to client        400  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

此时sql的执行已经跳开了hint,开始使用索引,也就是说现在优化器选择一个比hint更优的执行计划

此时再次修改下 sql:

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2;

 

         N———-         2执行计划———————————————————-Plan hash value: 3988747878—————————————————————————–| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT  |         |     1 |     3 |     7   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TEST_SP |     1 |     3 |     7   (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):—————————————————   1 – filter(“N”=2)统计信息———————————————————-          1  recursive calls          0  db block gets         24  consistent gets          0  physical reads          0  redo size        412  bytes sent via SQL*Net to client        400  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

可以发现,此时sql执行的仍旧为全表扫描,仅仅是将1变成了2,sqlprofile 就失去作用;

 

那么对于此种literal sql 是否可以使用相同的sql profile呢,答案是肯定的,下面演示下:

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task_3′, replace => TRUE, force_match=>true);PL/SQL 过程已成功完成。

此时sql profile将启用force match,其作用类型crsor_sharing 的force设置;

此时再次执行sql(scott):

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2;         N———-         2执行计划———————————————————-Plan hash value: 2882402178—————————————————————————–| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |—————————————————————————–|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     3 |     1   (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):—————————————————   1 – access(“N”=2)Note—–   – SQL profile “SYS_SQLPROF_014de5abcc24c000″ used for this statement统计信息———————————————————-          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        412  bytes sent via SQL*Net to client        400  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedSQL>

此时可以看出sql 已经开始使用索引;

通过以上的简单例子可以发现对sql profile可以帮助用来更好的优化sql,所以掌握sql profile 是非常有必要的;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25656398/viewspace-731367/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-04-07

  • 博文量
    45
  • 访问量
    46985