SQL中DBMS

54次阅读
没有评论

共计 9330 个字符,预计需要花费 24 分钟才能阅读完成。

这篇文章主要介绍了 SQL 中 DBMS_SQLTUNE 怎么用,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。

SQL 调优工具包 DBMS_SQLTUNE 的使用方法

oracle 提供了优化建议功能包 DBMS_SQLTUNE,该包可以帮助我们分析 SQL,并提供优化建议。

原有执行计划
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename= SCOTT and DEPTNO=20;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL, runstats_last

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID  8k1gbrapm7zpd, child number 0
————————————-
select * from test.emp where ename= SCOTT and DEPTNO=20

Plan hash value: 3956160932

————————————————————————————
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
————————————————————————————
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1 |00:00:00.01 |       4 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
   1 – filter((ENAME = SCOTT AND DEPTNO =20))

下面就用 DBMS_SQLTUNE 优化该 SQL
–1. 赋予用户 ADVISOR 权限
grant ADVISOR  to test;

–2. 创建 sql tuning 任务
conn test/test

DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := select * from emp where ename= :name and DEPTNO= :deptno
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    = my_sqltext,
bind_list   = sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)), 
         user_name   = TEST ,
         scope       = COMPREHENSIVE ,
         time_limit  = 60,
         task_name   = test_sql_tuning , 
         description = Task to tune a query on emp
END;
/

参数说明:
bind_list:多个绑定变量以 , 逗号分隔。参数值一定要根据绑定变量对应的列的类型书写.
如:emp.ename 类型是 VARCHAR2(10),那么就要写成
 bind_list    = sql_binds(anydata.convertvarchar2(10)),

time_limit:执行的最长时间,默认是 60。

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

** 也可以用 sql_id 创建 sql tunning 任务,比用 sql_text 方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
 SCOPE                          VARCHAR2                IN     DEFAULT
 TIME_LIMIT                     NUMBER                  IN     DEFAULT
 TASK_NAME                      VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT

DECLARE
 my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         SQL_ID      = ddw7j6yfnw0vz ,
         scope       = COMPREHENSIVE ,
         time_limit  = 60,
         task_name   = tunning_task_ddw7j6yfnw0vz , 
         description = Task to tune a query on  ddw7j6yfnw0vz
END;

/*2014-4- 8 日增加 end*/

–3. 查看任务名  SELECT TASK_NAME 
FROM   DBA_ADVISOR_LOG 
WHERE  OWNER = TEST
TASK_NAME
——————————
test_sql_tuning

–4. 执行 sql tuning 任务
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name = test_sql_tuning
END;
/

–5. 查看 sql tunning 任务状态
SELECT status 
FROM   USER_ADVISOR_TASKS 
WHERE  task_name = test_sql_tuning
STATUS
———–
COMPLETED

–6. 展示 sql tunning 结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
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  : TEST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 04/01/2014 16:45:16
Completed at       : 04/01/2014 16:45:17

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
——————————————————————————-
Schema Name: TEST
SQL ID     : 95fv6dbj64d0f
SQL Text   : select * from emp where ename= :name and DEPTNO= :deptno

——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-
1- Statistics Finding
———————

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
  Table TEST . EMP was not analyzed.

  Recommendation
  ————–
  – Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname = TEST , tabname =
            EMP , estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt = FOR ALL COLUMNS SIZE AUTO

  Rationale
  ———

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
    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: 66.67%)
  ——————————————
  – Consider running the Access Advisor to improve the physical schema design

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
    or creating the recommended index.
    create index TEST.IDX$$_00D80001 on TEST.EMP(ENAME , DEPTNO

  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.

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————
   1 – filter(ENAME =:NAME AND DEPTNO =:DEPTNO)

2- Using New Indices
——————–

DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
Plan hash value: 2106247215
———————————————————————————————-
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————-
|   0 | SELECT STATEMENT            |                |     1 |    87 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    87 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |     1 |       |     1   (0)| 00:00:01 |
———————————————————————————————-

Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK(TEST_SQL_TUNING)
—————————————————————————————————-
—————————————————
   2 – access(ENAME =:NAME AND DEPTNO =:DEPTNO)

——————————————————————————-

建议报告总结:
1 收集 EMP 表的统计信息
execute dbms_stats.gather_table_stats(ownname = TEST , tabname = EMP , estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt = FOR ALL COLUMNS SIZE AUTO

2 创建索引
create index TEST.IDX$$_00D80001 on TEST.EMP(ENAME , DEPTNO  

优化后执行计划
——————————————————————————————————–
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
——————————————————————————————————–
|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |      1 |      1 |      1 |00:00:00.01 |       2 |
——————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
   2 – access(ENAME = SCOTT AND DEPTNO =20)

–7. 完成后删除 sql tunning 任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(test_sql_tuning

–8. 其他
–sql tunning 任务创建后,也可以修改参数
BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name = test_sql_tuning ,
    parameter = TIME_LIMIT , value = 300);
END;
/

– 查看 SQL Tuning Advisor 的进展(task 执行很久)
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK 
FROM   V$ADVISOR_PROGRESS 
WHERE  USERNAME = TEST

感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“SQL 中 DBMS_SQLTUNE 怎么用”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计9330字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)