ITPub博客

首页 > 数据库 > Oracle > sql tuning task和sql profile

sql tuning task和sql profile

原创 Oracle 作者:liiinuuux 时间:2014-01-28 16:59:51 0 删除 编辑
sql tuning task和sql profile的关系
如果希望oracle对指定sql提出优化建议,需要如下步骤:
1 找出sql的sql_id
2 利用sql_id创建sql tuning task
3 执行sql tuning task
4 查看sql tuning task报告

接下来会有两种情况:
1 如果是缺少统计信息,或者oracle的建议是创建索引只来的,则需要手工按照建议操作。
2 如果oracle发现sql语句本身写法有问题导致执行计划不合理,则会创建一个“sql profile”,建议用户接受该profile。并且给出使用profile前后执行计划的对比。

如果用户接受了sql profile,则不需要修改sql语句,oracle会在适当的时候选择优化后的执行计划。

SQL TUNING TASK

创建测试数据
SQL> select count(*) from big;

  COUNT(*)
----------
   4238336

SQL> select count(*) from small;

  COUNT(*)
----------
      4140



执行sql查询
select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'

寻找该sql的sql_id
SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from big b, small s where s.object_id = b.object_id and b.status = ''INVALID''';

SQL_ID           SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
gb35pax1n9t9w select * from big b, small s where s.object_id = b.object_id and b.status = 'INVALID'

利用sql_id查看该sql执行时的执行计划
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'gb35pax1n9t9w', null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID     gb35pax1n9t9w, child number 0
-------------------------------------
select * from big b, small s where s.object_id = b.object_id and
b.status = 'INVALID'

Plan hash value: 3626037456

----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |        |        | 14296 (100)|        |
|*  1 |  HASH JOIN        |        |   577 |   144K| 14296   (1)| 00:02:52 |
|*  2 |   TABLE ACCESS FULL| BIG   |   577 | 73856 | 14280   (1)| 00:02:52 |
|   3 |   TABLE ACCESS FULL| SMALL |  4140 |   517K|     16   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."STATUS"='INVALID')

Note
-----
   - dynamic sampling used for this statement


26 rows selected.


利用sql_id创建sql tuning task(记住它生成的task名,或者在创建task的时候执行task_name)
set serverout on
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'gb35pax1n9t9w');
dbms_output.put_line(tuning_task);
end;
  7  /

TASK_12

PL/SQL procedure successfully completed.


执行sql tuning task
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_12');

PL/SQL procedure successfully completed.


查看sql tuning结果
SQL> set long 99999
SQL>
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_12') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name            : TASK_13
Tuning Task Owner            : SYS
Scope                      : COMPREHENSIVE
Time Limit(seconds)            : 1800
Completion Status            : COMPLETED
Started at                 : 11/28/2013 14:55:49
Completed at                 : 11/28/2013 14:55:53
Number of Index Findings       : 1

-------------------------------------------------------------------------------
Schema Name: LS
SQL ID        : gb35pax1n9t9w
SQL Text   : select * from big b, small s where s.object_id = b.object_id and
          b.status = 'INVALID'

-------------------------------------------------------------------------------
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 LS.IDX$$_000D0001 on LS.BIG('STATUS');

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index LS.IDX$$_000D0002 on LS.SMALL('OBJECT_ID');

  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: 3626037456

----------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |   166 | 14291   (1)| 00:02:52 |
|*  1 |  HASH JOIN        |        |     1 |   166 | 14291   (1)| 00:02:52 |
|*  2 |   TABLE ACCESS FULL| BIG   |     1 |     83 | 14275   (1)| 00:02:52 |
|   3 |   TABLE ACCESS FULL| SMALL |  4140 |   335K|     16   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("B"."STATUS"='INVALID')

2- Using New Indices
--------------------
Plan hash value: 1262730234

------------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |   166 |     6     (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | SMALL            |     1 |    83 |     2     (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |             |     1 |   166 |     6     (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG            |     1 |    83 |     4     (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | IDX$$_000D0001 |     1 |       |     3     (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX$$_000D0002 |     1 |       |     1     (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."STATUS"='INVALID')
   5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")

-------------------------------------------------------------------------------

报告显示,建议增加两个索引。这样一来,执行计划就会由原来的“1- Original”变成“2- Using New Indices”。
这里创建三个索引测试用
create index idx1 on big(status);
create index idx2 on big(status, object_id);
create index idx3 on big(object_id);


SQL PROFILE

将上面的SQL改写,不让它走索引
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';

打印执行计划
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR( 'bhw1175zgq048', null, null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID     bhw1175zgq048, child number 0
-------------------------------------
select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where
s.object_id = b.object_id and b.status = 'INVALID'

Plan hash value: 566152358

-------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |         |         | 14278 (100)|         |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| SMALL |       1 |     83 |       2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |         |       1 |     166 | 14278   (1)| 00:02:52 |
|*  3 |    TABLE ACCESS FULL         | BIG   |       1 |     83 | 14276   (1)| 00:02:52 |
|*  4 |    INDEX RANGE SCAN         | IDX3  |       1 |         |       1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("B"."STATUS"='INVALID')
   4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


23 rows selected.


创建sql tuning task报告
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_task') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name            : my_task
Tuning Task Owner            : SYS
Scope                      : COMPREHENSIVE
Time Limit(seconds)            : 1800
Completion Status            : COMPLETED
Started at                 : 11/28/2013 16:16:37
Completed at                 : 11/28/2013 16:16:38
Number of SQL Profile Findings       : 1


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID        : bhw1175zgq048
SQL Text   : select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s
          where s.object_id = b.object_id and b.status = 'INVALID'

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.85%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace
         => TRUE);


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 566152358

-------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     99 | 16434 | 14342   (1)| 00:02:53 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SMALL |       1 |     83 |       2   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
|   2 |   NESTED LOOPS              |         |     99 | 16434 | 14342   (1)| 00:02:53 |
|*  3 |    TABLE ACCESS FULL         | BIG   |     33 |  2739 | 14276   (1)| 00:02:52 |
|*  4 |    INDEX RANGE SCAN         | IDX3  |       1 |         |       1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("B"."STATUS"='INVALID')
   4 - access("S"."OBJECT_ID"="B"."OBJECT_ID")


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 2031531658

--------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       99 | 16434 |       21   (5)| 00:00:01 |
|*  1 |  HASH JOIN               |          |       99 | 16434 |       21   (5)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| BIG   |       33 |     2739 |        4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX1  |        1 |          |        3   (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TASK')
------------------------------------------------------------------------------------------------------------------------
|   4 |   TABLE ACCESS FULL          | SMALL |     4140 |     335K|       16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."OBJECT_ID"="B"."OBJECT_ID")
   3 - access("B"."STATUS"='INVALID')

-------------------------------------------------------------------------------

报告中也发现执行计划变成全表扫描了,oracle生成了一个sql profile,建议我们执行下面语句来接受sql profile,这样oracle就可以在适当的时候使用优化后的“2- Using SQL Profile”执行计划:
execute dbms_sqltune.accept_sql_profile(task_name => 'my_task', replace => TRUE);


接受profile后,不修改sql,执行计划却发生了变化,说明sql profile起作用了
SQL> set lines 120
SQL> set autot  traceonly
SQL> select /*+ no_index(b idx1) */ * from ls.big b, ls.small s where s.object_id = b.object_id and b.status = 'INVALID';

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4150764910

---------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |   166 |     6     (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | SMALL |     1 |    83 |     2     (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |       |     1 |   166 |     6     (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG   |     1 |    83 |     4     (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | IDX2  |     1 |       |     3     (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX3  |     1 |       |     1     (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."STATUS"='INVALID')
   5 - access("S"."OBJECT_ID"="B"."OBJECT_ID")


Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
     142  consistent gets
       0  physical reads
       0  redo size
       8251  bytes sent via SQL*Net to client
     450  bytes received via SQL*Net from client
       8  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
     99  rows processed


查看tuning任务和执行状态
     select * from dba_advisor_log


查看SQL PROFILE
SQL> SELECT NAME, CATEGORY, SQL_TEXT FROM DBA_SQL_PROFILES;

NAME                      CATEGORY                 SQL_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS_SQLPROF_0150a772d1540000   DEFAULT                     select /*+ no_index(b IDX1,IDX2) */ * from ls.big b, ls.small s where s.object_i

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

请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    312060