{"id":435,"date":"2014-06-01T01:55:25","date_gmt":"2014-05-31T17:55:25","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=435"},"modified":"2014-08-15T14:58:50","modified_gmt":"2014-08-15T06:58:50","slug":"%e6%80%a7%e8%83%bd%e8%b0%83%e4%bc%98%e5%b7%a5%e5%85%b701-sql-tuning-advisor%e5%b7%a5%e5%85%b7%e7%9a%84%e4%bd%bf%e7%94%a8","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=435","title":{"rendered":"\u6027\u80fd\u8c03\u4f18\u5de5\u517701–Sql Tuning Advisor\u5de5\u5177\u7684\u4f7f\u7528"},"content":{"rendered":"
STA(Sql Tuning Advisor)\u5de5\u5177\u7684\u4f7f\u7528<\/p>\n
\n
    \n
  1. STA\u5de5\u5177\u5176\u5b9e\u5c31\u662fDBMS_SQLTUNE\u5305,\u8981\u4f7f\u7528STA\u7684\u8bdd,\u5fc5\u987b\u662f\u5728CBO\u6a21\u5f0f\u4e0b\u4f7f\u7528;<\/li>\n
  2. \u6267\u884cDBMS_SQLTUNE\u5305,\u9700\u8981\u6709advisor\u89d2\u8272\u6743\u9650;<\/li>\n
  3. DBMS_SQLTUNE\u5305\u4ecb\u7ecd:\n
      \n
    1. DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text, bind_list, user_name, scope, time_limit, task_name, DESCRIPTION) RETURN VARCHAR2;\n
        \n
      1. sql_text:\u9700\u8981\u4f18\u5316\u7684sql\u8bed\u53e5;<\/li>\n
      2. bind_list:\u7ed1\u5b9a\u7684\u53d8\u91cf\u5217\u8868,\u9ed8\u8ba4\u4e3aNULL;<\/li>\n
      3. user_name:\u8981\u4f18\u5316\u7684sql\u8bed\u53e5\u901a\u8fc7\u54ea\u4e2a\u7528\u6237\u6267\u884c,\u9ed8\u8ba4\u4e3aNULL;<\/li>\n
      4. scope:\u4f18\u5316\u8303\u56f4,\u6709\u4e24\u4e2a\u53d6\u503c,limited-\u5206\u6790\u65f6\u4e0d\u4f7f\u7528\u63a8\u8350\u7684SQL Profile,\u5927\u7ea6\u6bcf\u4e2a\u8bed\u53e51s\u65f6\u95f4;comprehensive-\u5206\u6790\u65f6\u4f7f\u7528\u63a8\u8350\u7684SQL Profile,\u53ef\u80fd\u82b1\u8d39\u7684\u65f6\u95f4\u8f83\u957f,\u9ed8\u8ba4\u503c;<\/li>\n
      5. time_limit:\u4f18\u5316\u8fc7\u7a0b\u7684\u65f6\u95f4\u9650\u5236,\u9ed8\u8ba4\u4e3aTIME_LIMIT_DEFAULT;<\/li>\n
      6. task_name:\u4f18\u5316\u4efb\u52a1\u7684\u540d\u79f0,\u9ed8\u8ba4\u4e3aNULL;<\/li>\n
      7. decription:\u63cf\u8ff0\u4fe1\u606f,\u9ed8\u8ba4\u4e3aNULL;<\/li>\n<\/ol>\n<\/li>\n
      8. DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);\n
          \n
        1. task_name:\u8981\u6267\u884c\u7684\u4f18\u5316\u4efb\u52a1\u7684\u540d\u79f0;<\/li>\n<\/ol>\n<\/li>\n
        2. DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name, type, level, section, object_id, result_limit);\n
            \n
          1. task_name:\u8981\u67e5\u770b\u7684\u4f18\u5316\u4efb\u52a1\u7684\u540d\u79f0;<\/li>\n
          2. type:\u4f18\u5316\u62a5\u544a\u7684\u7c7b\u578b,TEXT,HTML\u6216\u8005XML,\u9ed8\u8ba4\u662fTEXT;<\/li>\n
          3. level:\u683c\u5f0f\u5316\u7684\u7ea7\u522b,TYPICAL, BASIC, ALL.\u9ed8\u8ba4\u662fTYPICAL;<\/li>\n
          4. section:\u4f18\u5316\u62a5\u544a\u7684\u90e8\u5206,FINDING,PLAN,INFORMATION,ERROR\u6216\u8005ALL,\u9ed8\u8ba4\u662fALL;<\/li>\n
          5. \u8c03\u7528\u65b9\u6cd5:SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) from dual;<\/li>\n<\/ol>\n<\/li>\n
          6. DBMS_SQLTUNE.DROP_TUNING_TASK(task_name);\n
              \n
            1. task_name:\u8981\u5220\u9664\u7684\u4f18\u5316\u4efb\u52a1\u7684\u540d\u79f0;<\/li>\n<\/ol>\n<\/li>\n
            2. DBMS_SQLTUNE.RESET_TUNING_TASK(task_name):\u91cd\u7f6e\u4f18\u5316\u4efb\u52a1\u7684\u7ed3\u679c;\n
                \n
              1. task_name:\u4f18\u5316\u4efb\u52a1\u7684\u540d\u79f0;<\/li>\n
              2. \u6267\u884c\u8fc7\u4e4b\u540e\u5c31\u53ef\u4ee5\u91cd\u65b0\u6267\u884cEXECUTE_TUNING_TASK\u8fc7\u7a0b\u4e86;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
              3. \u4e00\u822c\u7684\u6b65\u9aa4:\n
                  \n
                1. \u521b\u5efa\u4f18\u5316\u4efb\u52a1;<\/li>\n
                2. \u6267\u884c\u4f18\u5316\u4efb\u52a1;<\/li>\n
                3. \u663e\u793a\u4f18\u5316\u7ed3\u679c;<\/li>\n
                4. \u6839\u636e\u5efa\u8bae\u6765\u505a\u76f8\u5e94\u7684\u4f18\u5316;<\/li>\n
                5. \u5220\u9664\u4f18\u5316\u4efb\u52a1;<\/li>\n<\/ol>\n<\/li>\n
                6. \u521b\u5efa\u6d4b\u8bd5\u7528\u6237:SQLTUNE; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                7. \u521b\u5efa\u6d4b\u8bd5\u8868; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                8. \u6267\u884c\u67e5\u8be2\u8bed\u53e5:SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name;\n
                    \n
                  1. \u9700\u8981\u7684\u65f6\u95f4; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                  2. \u6267\u884c\u8ba1\u5212; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                  3. \u7edf\u8ba1\u4fe1\u606f; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n<\/ol>\n<\/li>\n
                  4. \u521b\u5efa\u4f18\u5316\u4efb\u52a1; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                  5. \u6267\u884c\u4f18\u5316\u4efb\u52a1,\u5e76\u67e5\u770b\u4f18\u5316\u4efb\u52a1\u7684\u6267\u884c\u8fdb\u5ea6; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                  6. \u67e5\u8be2\u4f18\u5316\u7ed3\u679c;\n
                      \n
                    1. \u5173\u4e8e\u8fd9\u6b21\u4f18\u5316\u4efb\u52a1\u7684\u57fa\u672c\u4fe1\u606f:\u5982\u4efb\u52a1\u540d\u79f0,\u6267\u884c\u65f6\u95f4,\u8303\u56f4,\u6d89\u53ca\u5230\u7684\u8bed\u53e5,\u53d1\u73b0\u95ee\u9898\u7684\u7c7b\u578b\u53ca\u4e2a\u6570\u7684\u4fe1\u606f\u7b49\u7b49; \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                    2. \u53d1\u73b0\u7684\u95ee\u9898:\u4e24\u4e2a\u8868\u6ca1\u6709\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                    3. \u53d1\u73b0\u7684\u95ee\u9898:\u4e24\u4e2a\u8868\u6ca1\u6709\u7d22\u5f15; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                    4. \u6309\u7167\u4f18\u5316\u5efa\u8bae\u4fee\u6539\u524d\u540e\u7684\u6267\u884c\u8ba1\u5212\u7684\u5bf9\u6bd4; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"\"\"<\/li>\n<\/ol>\n<\/li>\n
                    5. \u6309\u7167\u5efa\u8bae\u505a\u76f8\u5e94\u7684\u4fee\u6539,\u7136\u540e\u6267\u884c\u8bed\u53e5\u9a8c\u8bc1\u4f18\u5316\u7684\u7ed3\u679c; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                    6. \u5220\u9664\u4f18\u5316\u4efb\u52a1: \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0\"\"<\/li>\n
                    7. \u5982\u679c\u9700\u8981\u5bf9\u591a\u6761\u8bed\u53e5\u8fdb\u884c\u4f18\u5316\u65f6,\u5e94\u8be5\u4f7f\u7528STS(Sql Tuning Set);\n
                        \n
                      1. STS\u53ef\u4ee5\u4ece\u591a\u79cd\u6570\u636e\u6e90(Cursor Cache, AWR, STS)\u83b7\u53d6SQL;<\/li>\n
                      2. \u521b\u5efa\u4e00\u4e2aSTS:DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => ‘sts_test’);<\/li>\n
                      3. \u4f7f\u7528Cursor Cache\u52a0\u8f7dSTS:DBMS_SQLTUNE.LOAD_SQLSET();<\/li>\n
                      4. \u67e5\u770bSTS\u7684\u5185\u5bb9:SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(‘sts_test’));<\/li>\n
                      5. \u4f7f\u7528STS\u521b\u5efa\u4e00\u4e2a\u4f18\u5316\u4efb\u52a1:DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name=>’sts_test’, task_name=>’sta_test’);<\/li>\n<\/ol>\n<\/li>\n
                      6. \u4f7f\u7528EM,\u5728EM->Performance->Advisor Central->SQL Advisors->SQL Tuning Advisor; \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\"\"<\/li>\n
                      7. \u5355\u6761sql\u7684\u8c03\u4f18\u53ef\u4ee5\u4f7f\u7528sqltrpt.sql(\u6267\u884cSQL>@?\/rdbms\/admin\/sqltrpt.sql sqlid\u5373\u53ef)\u811a\u672c\u4f20\u5165sqlid\u5373\u53ef<\/span>;\u5bf9\u4e8e\u591a\u6761sql\u8bed\u53e5\u53ef\u4ee5\u4f7f\u7528dbmssqlt.sql\u811a\u672c,\u8fd9\u4e2a\u811a\u672c\u7ed9\u51fa\u4e86dbms_sqltune\u5305\u7684\u5b9a\u4e49\u548c\u76f8\u5173\u7684\u4f8b\u5b50;<\/li>\n<\/ol>\n
                        <\/div>\n<\/div>\n
                        <\/div>\n
                        — \u521b\u5efa\u6d4b\u8bd5\u7528\u6237SQLTUNE;<\/div>\n
                        CREATE USER SQLTUNE IDENTIFIED BY “oracle”;
                        \nGRANT CONNECT, RESOURCE, ADVISOR TO SQLTUNE;
                        \nCONN SQLTUNE\/ORACLE;<\/div>\n
                        <\/div>\n
                        — \u521b\u5efa\u6d4b\u8bd5\u8868;<\/div>\n
                        CREATE TABLE bigtable AS SELECT rownum id, t.* FROM sys.all_objects t;
                        \nCREATE TABLE smalltable AS SELECT rownum id, t.* FROM sys.all_objects t;
                        \nINSERT INTO bigtable SELECT * FROM bigtable;
                        \nINSERT INTO bigtable SELECT * FROM bigtable;
                        \nINSERT INTO bigtable SELECT * FROM bigtable;
                        \nCOMMIT;<\/div>\n
                        <\/div>\n
                        — \u521b\u5efa\u4f18\u5316\u4efb\u52a1;<\/div>\n
                        DECLARE
                        \nmy_task_name VARCHAR2(30);
                        \nmy_sqltext\u00a0\u00a0 CLOB;
                        \nBEGIN
                        \nmy_sqltext := ‘SELECT COUNT(*) FROM bigtable a, smalltable b WHERE a.object_name = b.object_name’;
                        \nmy_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext, user_name => ‘SQLTUNE’, task_name => ‘sta_test’);
                        \nEND;<\/div>\n
                        <\/div>\n
                        — \u00a0\u6267\u884c\u4f18\u5316\u4efb\u52a1;<\/div>\n
                        EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘sta_test’);<\/div>\n
                        SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =’sta_test’;<\/div>\n
                        <\/div>\n
                        — \u67e5\u770b\u4f18\u5316\u7ed3\u679c;<\/div>\n
                        SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘sta_test’) from dual;<\/div>\n
                        <\/div>\n
                        — \u6267\u884c\u4f18\u5316\u5efa\u8bae;<\/div>\n
                        CREATE INDEX IDX_BIGTABLE_OBJECTNAME ON BIGTABLE(‘OBJECT_NAME’);
                        \nCREATE INDEX IDX_SMALLTABLE_OBJECTNAME ON SMALLTABLE(‘OBJECT_NAME’);
                        \nEXEC dbms_stats.gather_table_stats(USER, ‘BIGTABLE’, CASCADE=>TRUE);
                        \nEXEC dbms_stats.gather_table_stats(USER, ‘SMALLTABLE’, CASCADE=>TRUE);<\/div>\n
                        <\/div>\n
                        — \u5220\u9664\u4f18\u5316\u4efb\u52a1;<\/div>\n
                        EXEC\u00a0DBMS_SQLTUNE.DROP_TUNING_TASK(‘sta_test’);<\/div>\n
                        <\/div>\n
                        <\/div>\n
                        <\/div>\n
                        — \u521b\u5efaSQL Tuning Set\u521b\u5efa\u4f18\u5316\u4efb\u52a1;<\/span><\/div>\n
                        — create a STS
                        \nBEGIN
                        \ndbms_sqltune.create_sqlset(sqlset_name => ‘my_sts’);
                        \nEND;
                        \n\/<\/p>\n

                        — load STS using cursor cache
                        \nDECLARE
                        \nl_cur dbms_sqltune.sqlset_cursor;
                        \nBEGIN
                        \nOPEN l_cur FOR
                        \nSELECT VALUE(t)
                        \nFROM TABLE(dbms_sqltune.select_cursor_cache(‘sql_id in (”588rxmp05xt7g”,”7rucbfq8vcr7d”)’)) t;<\/p>\n

                        dbms_sqltune.load_sqlset(sqlset_name => ‘sts_test’, populate_cursor => l_cur);
                        \nEND;
                        \n\/<\/p>\n

                        — display contents of STS
                        \nSELECT * FROM TABLE(dbms_sqltune.select_sqlset(‘sts_test’));<\/p>\n

                        — drop a sql tuning task
                        \nBEGIN
                        \ndbms_sqltune.drop_tuning_task(task_name => ‘my_sql_tuning_task’);
                        \nEND;
                        \n\/<\/p>\n

                        — create a sql tuning task by using STS
                        \nDECLARE
                        \nl_task_name VARCHAR2(30);
                        \nl_sqltext CLOB;
                        \nBEGIN
                        \nl_task_name := dbms_sqltune.create_tuning_task(sqlset_name => ‘sts_test’, task_name => ‘my_sql_tuning_task’);<\/p><\/div>\n

                        END;
                        \n\/<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                        STA(Sql Tuning Advisor)\u5de5\u5177\u7684\u4f7f\u7528 STA\u5de5\u5177\u5176\u5b9e\u5c31\u662fDBMS_SQLTUNE\u5305,\u8981\u4f7f\u7528 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[34,8],"class_list":["post-435","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-performance"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/435","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=435"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/435\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=435"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=435"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=435"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}