Oracle SQL Tuning Advisor 测试
生活随笔
收集整理的這篇文章主要介紹了
Oracle SQL Tuning Advisor 测试
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
如果面對一個需要優化的SQL語句，沒有很好的想法，可以先試試Oracle的SQL Tuning Advisor。
SQL> select * from v$version;BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production --創建測試表 SQL> create table test_sql_tuning(id_ number);Table created.SQL> declare2 begin3 for i in 1..100000 loop4 insert into test_sql_tuning values(i)5 end loop;6 commit;7 end;8 /PL/SQL procedure successfully completed.SQL> set autotrace on exp SQL> select * from test_sql_tuning t where t.id_=333;ID_ ----------333Execution Plan ---------------------------------------------------------- Plan hash value: 3855182387------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 39 | 69 (2)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TEST_SQL_TUNING | 3 | 39 | 69 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("T"."ID_"=333)Note ------ dynamic sampling used for this statement (level=2)SQL> declare2 my_task_name varchar2(50);3 begin4 my_task_name := dbms_sqltune.create_tuning_task(5 SQL_ID => 'g72kdvcacxvtf',6 scope => 'COMPREHENSIVE',7 task_name => 'ZEN_TEST_SQLTUNE');8 DBMS_SQLTUNE.execute_tuning_task(my_task_name);9 end;10 /PL/SQL procedure successfully completed.SQL> select dbms_sqltune.report_tuning_task('ZEN_TEST_SQLTUNE') from dual;DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name : ZEN_TEST_SQLTUNE Tuning Task Owner : HR Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 1800 Completion Status : COMPLETED Started at : 07/02/2017 16:31:20 Completed at : 07/02/2017 16:31:20DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Schema Name: HR SQL ID : cnvs1fb15pqb4 SQL Text : select * from test_sql_tuning t where t.id_=333------------------------------------------------------------------------------- FINDINGS SECTION (2 findings) -------------------------------------------------------------------------------1- Statistics Finding --------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') --------------------------------------------------------------------------------Table "HR"."TEST_SQL_TUNING" was not analyzed.Recommendation--------------- Consider collecting optimizer statistics for this table.execute dbms_stats.gather_table_stats(ownname => 'HR', tabname =>'TEST_SQL_TUNING', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZEAUTO');RationaleDBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -----------------------------------------------------------------------------------------The optimizer requires up-to-date statistics for the table in order toselect 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 moreindices.Recommendation (estimated benefit: 98.55%)------------------------------------------ DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') --------------------------------------------------------------------------------- Consider running the Access Advisor to improve the physical schema designor creating the recommended index.create index HR.IDX$$_06650001 on HR.TEST_SQL_TUNING("ID_");Rationale---------Creating the recommended indices significantly improves the execution planof this statement. However, it might be preferable to run "Access Advisor"using a representative SQL workload as opposed to a single statement. Thiswill allow to get comprehensive index recommendations which takes intoaccount index maintenance overhead and additional space consumption.DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') --------------------------------------------------------------------------------------------------------------------------------------------------------------- EXPLAIN PLANS SECTION -------------------------------------------------------------------------------1- Original ----------- Plan hash value: 3855182387------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 39 | 69 (2)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SQL_TUNING | 3 | 39 | 69 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------1 - filter("T"."ID_"=333)DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- 2- Using New Indices -------------------- Plan hash value: 1603088386----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX$$_06650001 | 1 | 13 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------- DBMS_SQLTUNE.REPORT_TUNING_TASK('ZEN_TEST_SQLTUNE') -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------1 - access("T"."ID_"=333)-------------------------------------------------------------------------------1 row selected.Execution Plan ---------------------------------------------------------- Plan hash value: 1388734953----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | ----------------------------------------------------------------- SQL>
上面的report總共分為3個部分，
分別是SQL調優的基本信息、SQL調優的建議findings、以及SQL對應的執行計劃部分
在基本信息部分包含了SQL調優的任務名稱，狀態，執行，完成時間，對應的SQL完整語句等
在finding部分則給出本次調優所得到的成果，如本次是提示缺少統計信息，可以及創建索引。
在執行計劃部分則給出了當前SQL語句的執行計劃以及謂詞信息
轉載于:https://www.cnblogs.com/Alex-Zeng/p/7106491.html
總結
以上是生活随笔為你收集整理的Oracle SQL Tuning Advisor 测试的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 我要买猪 现在一只小猪崽大概多少钱 崽猪
- 下一篇： 2020款五菱宏光S后挡玻璃多少钱？