SQL TUNING(sqltext文本方式)

1.给权限 
grant ADVISOR  to system;
(system应该有)




2.查看原来SQL的执行计划


alter session set statistics_level=all;
set serveroutput off
set linesize 222 
set pagesize 1000
set long 99999
select * from system.wh where object_id = 2;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));


SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  famdvdxmp0gnf, child number 0
-------------------------------------
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last')
)


Plan hash value: 3713220770


-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |      1 |        |     22 |00:00:00.10 |    3108 |     97 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |      1 |   8168 |     22 |00:00:00.10 |    3108 |     97 |
-----------------------------------------------------------------------------------------------------------------------




14 rows selected.








3.创建sql tuning任务


DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := 'select * from system.wh where object_id = :object_id ';
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.convertnumber(10)), 
         user_name   => 'SYSTEM',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'test_sql_tuning', 
         description => 'Task to tune a query on wh');
END;
/


参数说明：
bind_list：多个绑定变量以','逗号分隔。参数值一定要根据绑定变量对应的列的类型书写.
如：
  wh.OBJECT_ID  类型是 NUMBER
  那么就要写成
   bind_list    =>sql_binds(anydata.convertnumber(10)),
 wh.OBJECT_NAME  类型是 VARCHAR2(30)
  那么就要写成
    bind_list    =>sql_binds(anydata.convertvarchar(30)),


time_limit：执行的最长时间，默认是60。


scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。




4.查看创建的信息


SQL> SELECT TASK_NAME FROM   DBA_ADVISOR_LOG WHERE  OWNER = 'SYSTEM';


TASK_NAME
------------------------------
test_sql_tuning




5.执行sql tuning任务


BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_tuning' );
END;
/


6.查看sql tunning任务状态


SQL> SELECT status FROM   USER_ADVISOR_TASKS WHERE  task_name = 'test_sql_tuning';


STATUS
-----------
COMPLETED


7.展示sql tunning结果


SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning') FROM   DUAL;




SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning') FROM   DUAL;


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_sql_tuning
Tuning Task Owner  : SYSTEM
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 02/15/2017 19:30:49
Completed at       : 02/15/2017 19:30:50




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : f35tfnjj88suz
SQL Text   : select * from system.wh where object_id = :object_id
Bind Variables :
 1 -  (NUMBER):10


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  Table "SYSTEM"."WH" was not analyzed.


  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>
            'WH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
            => 'FOR ALL COLUMNS SIZE AUTO');




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.


2- 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: 99.31%)


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SYSTEM.IDX$$_000C0001 on SYSTEM.WH("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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
    account index maintenance overhead and additional space consumption.


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


1- Original
-----------
Plan hash value: 41526393


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


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   290   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| WH   |    12 |  2484 |   290   (1)| 00:00:04 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=:OBJECT_ID)




DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------
2- Using New Indices
--------------------
Plan hash value: 2798116298


----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WH             |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_000C0001 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING')
----------------------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"=:OBJECT_ID)


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








看到有2个建议
1.收集一下表信息
execute dbms_stats.gather_table_stats(ownname => 'SYSTEM', tabname =>'WH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO');


2.创建一个索引


create index SYSTEM.IDX$$_000C0001 on SYSTEM.WH("OBJECT_ID");

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

转载于:http://blog.itpub.net/7569309/viewspace-2133641/