点击(此处)折叠或打开
- --以下是sql tuning advisor调优sql常用脚本,通过coe_xfr_sql_profile调优参见:http://blog.itpub.net/28539951/viewspace-1603192/
-
- --os:centos 6.6
- --db:11.2.0.4
-
- --建测试表
- create table scott.t_test01 as select * from dba_objects;
- --插入数据
- insert into scott.t_test01 select * from scott.t_test01;
- commit;
- insert into scott.t_test01 select * from scott.t_test01;
- commit;
- insert into scott.t_test01 select * from scott.t_test01;
- commit;
-
- --建立生成sql tuning advisor
- DECLARE
- ret_val VARCHAR2(4000);
- sqltext CLOB;
- BEGIN
- sqltext := 'select * from scott.t_test01 where owner=''SCOTT''';
- --sql标识可以用sql_text也可以用sql_id
- ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(
- sql_text => sqltext,
- bind_list => NULL,
- user_name => 'SYS',
- scope => 'COMPREHENSIVE',
- time_limit => 1800,
- task_name => 'sql_tune_002',
- description => 'SQL Tuning Advisor Task');
- Dbms_Sqltune.EXECUTE_TUNING_TASK(ret_val);
- End;
-
- --查看sql tuning advisor report
- select Dbms_Sqltune.REPORT_TUNING_TASK('sql_tune_002', 'TEXT', 'ALL') report from dual;
- /*
- GENERAL INFORMATION SECTION
- -------------------------------------------------------------------------------
- Tuning Task Name : sql_tune_002
- Tuning Task Owner : SYSTEM
- Tuning Task ID : 62
- Workload Type : Single SQL Statement
- Execution Count : 1
- Current Execution : EXEC_52
- Execution Type : TUNE SQL
- Scope : COMPREHENSIVE
- Time Limit(seconds): 1800
- Completion Status : COMPLETED
- Started at : 04/26/2016 19:53:42
- Completed at : 04/26/2016 19:53:43
-
- -------------------------------------------------------------------------------
- Schema Name: SYS
- SQL ID : 7z30ga5js6pvn
- SQL Text : select * from scott.t_test01 where owner='SCOTT'
-
- -------------------------------------------------------------------------------
- FINDINGS SECTION (2 findings)
- -------------------------------------------------------------------------------
-
- 1- Statistics Finding
- ---------------------
- Table "SCOTT"."T_TEST01" was not analyzed.
-
- Recommendation
- --------------
- - Consider collecting optimizer statistics for this table.
- execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
- 'T_TEST01', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
- method_opt => 'FOR ALL COLUMNS SIZE AUTO');
-
- 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.81%)
- ------------------------------------------
- - Consider running the Access Advisor to improve the physical schema design
- or creating the recommended index.
- create index SCOTT.IDX$$_003E0001 on SCOTT.T_TEST01("OWNER");
-
- 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: 3092827266
-
-
- ------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 111 | 22977 | 2749 (1)| 00:00:33 |
- |* 1 | TABLE ACCESS FULL| T_TEST01 | 111 | 22977 | 2749 (1)| 00:00:33 |
- ------------------------------------------------------------------------------
-
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
-
- 1 - SEL$1 / T_TEST01@SEL$1
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 1 - filter("OWNER"='SCOTT')
-
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
-
- 1 - "OWNER"[VARCHAR2,30], "T_TEST01"."OBJECT_NAME"[VARCHAR2,128],
- "T_TEST01"."SUBOBJECT_NAME"[VARCHAR2,30],
- "T_TEST01"."OBJECT_ID"[NUMBER,22], "T_TEST01"."DATA_OBJECT_ID"[NUMBER,22
- ], "T_TEST01"."OBJECT_TYPE"[VARCHAR2,19], "T_TEST01"."CREATED"[DATE,7],
- "T_TEST01"."LAST_DDL_TIME"[DATE,7],
- "T_TEST01"."TIMESTAMP"[VARCHAR2,19], "T_TEST01"."STATUS"[VARCHAR2,7],
- "T_TEST01"."TEMPORARY"[VARCHAR2,1], "T_TEST01"."GENERATED"[VARCHAR2,1],
- "T_TEST01"."SECONDARY"[VARCHAR2,1], "T_TEST01"."NAMESPACE"[NUMBER,22],
- "T_TEST01"."EDITION_NAME"[VARCHAR2,30]
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
- 2- Using New Indices
- --------------------
- Plan hash value: 3193164626
-
-
- ----------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 56 | 11592 | 5 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| T_TEST01 | 56 | 11592 | 5 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | IDX$$_003E0001 | 56 | | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------------
-
- Query Block Name / Object Alias (identified by operation id):
- -------------------------------------------------------------
-
- 1 - SEL$1 / T_TEST01@SEL$1
- 2 - SEL$1 / T_TEST01@SEL$1
-
- Predicate Information (identified by operation id):
- ---------------------------------------------------
-
- 2 - access("OWNER"='SCOTT')
-
- Column Projection Information (identified by operation id):
- -----------------------------------------------------------
-
- 1 - "OWNER"[VARCHAR2,30], "T_TEST01"."OBJECT_NAME"[VARCHAR2,128],
- "T_TEST01"."SUBOBJECT_NAME"[VARCHAR2,30], "T_TEST01"."OBJECT_ID"[NUMBER,22],
- "T_TEST01"."DATA_OBJECT_ID"[NUMBER,22], "T_TEST01"."OBJECT_TYPE"[VARCHAR2,19],
- "T_TEST01"."CREATED"[DATE,7], "T_TEST01"."LAST_DDL_TIME"[DATE,7],
- "T_TEST01"."TIMESTAMP"[VARCHAR2,19], "T_TEST01"."STATUS"[VARCHAR2,7],
- "T_TEST01"."TEMPORARY"[VARCHAR2,1], "T_TEST01"."GENERATED"[VARCHAR2,1],
- "T_TEST01"."SECONDARY"[VARCHAR2,1], "T_TEST01"."NAMESPACE"[NUMBER,22],
- "T_TEST01"."EDITION_NAME"[VARCHAR2,30]
- 2 - "T_TEST01".ROWID[ROWID,10], "OWNER"[VARCHAR2,30]
-
- Note
- -----
- - dynamic sampling used for this statement (level=2)
-
- -------------------------------------------------------------------------------
- */
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2113752/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2113752/