sql tune

来源：互联网发布：js urldecode 编辑：程序博客网时间：2024/05/21 18:39

具体包的使用可参照Oracle® Database PLSQL Packages and Types Reference 12c Release 1(12.1)

准备数据：

CREATE USER OPT IDENTIFIED BY 1;--新建个用户
GRANT DBA TO OPT; --权限无所谓

DROP TABLE OPT.EMPLOYEES;

--这样建表后，这个表没有任何索引，主键
CREATE TABLE OPT.EMPLOYEES
AS
SELECT * FROM HR.EMPLOYEES;

执行

--创建tuning任务

DECLARE
tune_task VARCHAR2(30);
tune_sql CLOB;
BEGIN
tune_sql := 'select employee_id from opt.employees where employee_id=198';
tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => tune_sql,
                                               user_name   => 'OPT',
                                               scope       => 'COMPREHENSIVE',
                                               time_limit => 60,
                                               task_name   => 'tune_test',
                                               description => 'Provide SQL text');
END;
/

--执行
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test');
set long 10000 longchunksize 10000 linesize 132 pagesize 200

--显示结果
select dbms_sqltune.report_tuning_task('tune_test') from dual;

SQL> DECLARE
2    tune_task VARCHAR2(30);
3    tune_sql CLOB;
4 BEGIN
5    tune_sql := 'select employee_id from opt.employees where employee_id=198';
6    tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => tune_sql,
7                                                 user_name   => 'OPT',
8                                                 scope       => 'COMPREHENSIVE',
9                                                 time_limit => 60,
10                                                 task_name   => 'tune_test',
11                                                 description => 'Provide SQL text');
12 END;
13 /

PL/SQL 过程已成功完成。

SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test');

PL/SQL 过程已成功完成。

SQL> set long 10000 longchunksize 10000 linesize 132 pagesize 200
SQL> select dbms_sqltune.report_tuning_task('tune_test') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TEST')
-------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tune_test
Tuning Task Owner : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at         : 04/07/2017 13:27:26
Completed at       : 04/07/2017 13:27:26

-------------------------------------------------------------------------------
Schema Name: OPT
SQL ID : 682hwfsrfdu6c
SQL Text : select employee_id from opt.employees where employee_id=198

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

1- Statistics Finding
---------------------
尚未分析表 "OPT"."EMPLOYEES"。

Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'OPT', tabname =>
            'EMPLOYEES', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。

2- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。

Recommendation (estimated benefit: 66.69%)
------------------------------------------
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index OPT.IDX$$_01110001 on OPT.EMPLOYEES("EMPLOYEE_ID");

Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

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

1- Original
-----------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |    13 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

1 - filter("EMPLOYEE_ID"=198)

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

-----------------------------------------------------------------------------------
| Id | Operation        | Name           | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |    13 |     1   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX$$_01110001 |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

1 - access("EMPLOYEE_ID"=198)

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

SQL> exec dbms_sqltune.drop_tuning_task(task_name => 'tune_test');

PL/SQL 过程已成功完成。

SQL>

--删除任务
exec dbms_sqltune.drop_tuning_task(task_name => 'tune_test');

0 0