Oracle的sql语句自动调优
发布时间：2020-12-12 16:02:11 所属栏目：百科 来源：网络整理
导读：1、创建测试表 SQL create table t1 ( 2 n number); 2、生成测试数据 SQL declare 2 begin 3 for i in 1 .. 10000 4 loop 5 insert into t1 values(i); 6 commit; 7 end loop; 8 end; 9 / PL/SQL 过程已成功完成。 SQL select count(*) from t1; COUNT(*) --
|
1、创建测试表
SQL> create table t1 ( 2 n number); 2、生成测试数据 SQL> declare 2 begin 3 for i in 1 .. 10000 4 loop 5 insert into t1 values(i); 6 commit; 7 end loop; 8 end; 9 / PL/SQL 过程已成功完成。 SQL> select count(*) from t1; COUNT(*) ---------- 10000 3、收集统计信息 SQL>exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1',method_opt=>'for all columns size 1',CASCADE=>true); 4、创建索引 SQL> create index idx_t1 on t1(n); 索引已创建。 5 、查询语句，使用no_index 使语句不走索引 SQL> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1; N ---------- 1 6、查看执行计划 SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID au4x8czp461nu,child number 0 ------------------------------------- select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------- 1 - filter("N"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] 已选择42行。 6、创建自动调整任务 SQL>declare my_task_name varchar2(30); my_sqltext clob; begin my_sqltext:=' select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1'; my_task_name:=dbms_sqltune.create_tuning_task( sql_text => my_sqltext, user_name => 'TEST', scope =>'COMPREHENSIVE', time_limit=>60, task_name =>'my_sql_tuning_task_2', description=>'Task to tune a query on table t1'); end; / 7、执行自动调整任务 SQL>BEGIN dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2'); end; / 8、查看自动调整任务的结果 SQL>SET LONG 9000 SQL>SET LONGCHUNKSIZE 1000 SQL>SET LINESIZE 200 SQL> select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : my_sql_tuning_task_2 Tuning Task Owner : TEST Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 09/03/2016 01:24:36 Completed at : 09/03/2016 01:24:38 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: TEST SQL ID : 21qpu3xh964v1 SQL Text : select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1 ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) -------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 为此语句找到了性能更好的执行计划。 Recommendation (estimated benefit: 90.9%) ----------------------------------------- - 考虑接受推荐的 SQL 概要文件。 execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_2',task_owner => 'TEST',replace => TRUE); Validation results ------------------ 已对 SQL profile 进行测试,方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时 间内就完成, DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 则另一计划可能只执行了一部分。 Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000138 .000016 88.4 % CPU Time (s): 0 0 User I/O Time (s): 0 0 Buffer Gets: 22 2 90.9 % Physical Read Requests: 0 0 Physical Write Requests: 0 0 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. the original plan 的统计信息是 10 执行的平均值。 2. the SQL profile plan 的统计信息是 10 执行的平均值。 DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION ------------------------------------------------------------------------------- 1- Original With Adjusted Cost ------------------------------ Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=1) 2- Using SQL Profile -------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 - access("N"=1) ------------------------------------------------------------------------------- 9、接受更好的执行计划建议 SQL> execute dbms_sqltune.accept_sql_profile(task_name=>'my_sql_tuning_task_2',task_owner=>' TEST',replace=>TRUE); PL/SQL 过程已成功完成。 10、再次执行sql语句，验证结果 SQL> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1; N ---------- 1 11、查看执行计划 SQL> select * from table(dbms_xplan.display_cursor(null,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID au4x8czp461nu,child number 0 ------------------------------------- select /*+ no_index(t1 idx_t1)*/ * from t1 where n=1 Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 1 - access("N"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] Note ----- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - SQL profile SYS_SQLPROF_0156ebfbb3b00000 used for this statement 已选择46行。 12、稳定的执行计划是不能有变动的，如果变动将不再生效，如当条件中n=2时，还是继续走全表扫描。 SQL> select /*+ no_index(t1 idx_t1)*/ * from t1 where n=2; N ---------- 2 SQL> select * from table(dbms_xplan.display_cursor(null,'advanced')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 1hnvc732gf7qu,child number 0 ------------------------------------- select /*+ no_index(t1 idx_t1)*/ * from t1 where n=2 Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- 1 - filter("N"=2) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "N"[NUMBER,22] 已选择42行。
13、通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务 SQL>execdbms_sqltune.drop_tuning_task('my_sql_tuning_task_2'); （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |