{"id":840,"date":"2012-10-13T11:40:56","date_gmt":"2012-10-13T11:40:56","guid":{"rendered":"http:\/\/blog.yavor.info\/?p=840"},"modified":"2012-10-12T13:23:39","modified_gmt":"2012-10-12T13:23:39","slug":"manual-sql-tunning-task","status":"publish","type":"post","link":"http:\/\/blog.yavor.info\/?p=840","title":{"rendered":"Manual SQL tunning task"},"content":{"rendered":"<p>\u0422\u0435\u0441\u0442\u0432\u0430\u043d\u043e \u043d\u0430 11.2.0.2<\/p>\n<p>SQL tunning advisor \u0435 \u0441\u0442\u0440\u0430\u0448\u043d\u0430 \u0434\u0430\u043b\u0430\u0432\u0435\u0440\u0430. \u0415, \u0438\u0437\u0438\u0441\u043a\u0432\u0430 \u0434\u043e\u043f\u044a\u043b\u043d\u0438\u0442\u0435\u043b\u0435\u043d \u043b\u0438\u0446\u0435\u043d\u0437 \u0437\u0430 tunning pack, \u043d\u043e \u0432\u044a\u0440\u0448\u0438 \u0440\u0430\u0431\u043e\u0442\u0430, \u043a\u043e\u044f\u0442\u043e \u0437\u0430 \u043e\u0431\u0438\u043a\u043d\u043e\u0432\u0435\u043d \u0447\u043e\u0432\u0435\u043a \u0435 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435\u0432\u044a\u0437\u043c\u043e\u0436\u043d\u0430. <\/p>\n<p>\u041f\u043e-\u0447\u0435\u0441\u0442\u0438\u044f\u0442 \u0432\u0430\u0440\u0438\u0430\u043d\u0442 \u0435 \u043a\u043e\u0433\u0430\u0442\u043e \u043d\u0435\u0449\u043e \u0441\u0435 \u0441\u043a\u0430\u043f\u0435 \u0432 production \u0438 \u0442\u0440\u044f\u0431\u0432\u0430 \u0441\u043f\u0435\u0448\u043d\u043e \u0434\u0430 \u0441\u0435 \u043e\u043f\u0440\u0430\u0432\u0438. \u0415\u0445, \u043a\u043e\u043b\u043a\u043e \u043f\u044a\u0442\u0438 \u043d\u0438 \u0435 \u0441\u043f\u0430\u0441\u044f\u0432\u0430\u043b SQL tunning advisor \u0432 \u0442\u0430\u043a\u0438\u0432\u0430 \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u0438&#8230; \u0425\u0435\u043c \u043d\u0430\u043c\u0438\u0440\u0430 \u0440\u0435\u0448\u0435\u043d\u0438\u0435 (\u0431\u0435\u0437 \u0434\u0430 \u0442\u0438 \u0441\u0435 \u043d\u0430\u043b\u0430\u0433\u0430 \u0434\u0430 \u0440\u0430\u0437\u0431\u0438\u0440\u0430\u0448 \u043a\u0430\u043a\u0432\u043e, \u0430\u0434\u0436\u0435\u0431\u0430, \u043f\u0440\u0430\u0432\u0438 \u0442\u0430\u0437\u0438 \u0437\u0430\u044f\u0432\u043a\u0430); \u0445\u0435\u043c \u043f\u0440\u0430\u0432\u0438 \u043f\u0440\u043e\u0444\u0438\u043b &#8211; \u0442\u0430\u043a\u0430 \u043d\u0435 \u0441\u0435 \u043d\u0430\u043b\u0430\u0433\u0430 \u043f\u0440\u043e\u043c\u044f\u043d\u0430 \u0432 \u043a\u043e\u0434\u0430 \u043d\u0430 \u0441\u0430\u043c\u043e\u0442\u043e \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0435. \u0410\u0437 \u043b\u0438\u0447\u043d\u043e \u043d\u0435 \u0441\u044a\u043c \u0441\u0435 \u0438 \u043e\u043f\u0438\u0442\u0432\u0430\u043b \u0440\u044a\u0447\u043d\u043e \u0434\u0430 \u043f\u0440\u0430\u0432\u044f \u043f\u0440\u043e\u0444\u0438\u043b, \u0441\u0443\u043f\u0435\u0440 \u043f\u0438\u043f\u043a\u0430\u0432\u0430 \u0440\u0430\u0431\u043e\u0442\u0430 \u043c\u0438 \u0438\u0437\u0433\u043b\u0435\u0436\u0434\u0430. \u041f\u0440\u0430\u0432\u0438\u043b \u0441\u044a\u043c \u0440\u044a\u0447\u043d\u043e outline \u0438 \u0442\u043e\u0432\u0430 \u0431\u0435\u0448\u0435 \u043f\u0438\u043f\u043a\u0430\u0432\u043e.<\/p>\n<p>\u0418\u043c\u0430, \u043e\u0431\u0430\u0447\u0435, \u0438 \u0435\u0434\u0438\u043d \u0434\u0440\u0443\u0433 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0439. \u041f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u0435\u0442\u0435 \u0441\u0438 \u0435\u0434\u0438\u043d \u0433\u043e\u0440\u0434 developer, \u043a\u043e\u0439\u0442\u043e \u0435 \u0441\u044a\u0442\u0432\u043e\u0440\u0438\u043b \u0437\u0430\u044f\u0432\u043a\u0430 \u0441 97 \u0440\u0435\u0434\u0430 execution plan. \u0418 \u043c\u043e\u043b\u0438 \u0437\u0430 \u0441\u044a\u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0435 \u043f\u0440\u0438 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f\u0442\u0430. \u0422\u043e\u0432\u0430 \u043d\u0435\u0449\u043e \u0435 \u0438\u0437\u0432\u044a\u043d \u0432\u044a\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438\u0442\u0435 \u0437\u0430 \u0432\u044a\u0437\u043f\u0440\u0438\u0435\u043c\u0430\u043d\u0435. \u0410 SQL tunning advisor \u043c\u0438\u043d\u0430\u0432\u0430 \u0437\u0430 \u043c\u0438\u043d\u0443\u0442\u0438 \u0438 \u0432 \u043f\u043e\u0432\u0435\u0447\u0435\u0442\u043e \u0441\u043b\u0443\u0447\u0430\u0438 \u043d\u0430\u0438\u0441\u0442\u0438\u043d\u0430 \u0434\u0430\u0432\u0430 \u0434\u043e\u0431\u0440\u0438 \u0438\u0434\u0435\u0438. \u041a\u0430\u043a \u0434\u0430 \u0433\u043e \u043f\u0443\u0441\u043d\u0435\u0448, \u043e\u0431\u0430\u0447\u0435?<\/p>\n<p>\u041f\u0440\u0435\u0437 Grid\/Cloud control \u0435 \u043b\u0435\u0441\u043d\u043e &#8211; \u0446\u044a\u043a\u0430\u0448, \u0446\u044a\u043a\u0430\u0448 \u0438 \u0441\u0442\u0430\u0432\u0430. \u041d\u043e \u043a\u0430\u043a\u0432\u043e \u043f\u0430\u0440\u0438\u043c, \u0430\u043a\u043e \u0433\u043e \u043d\u044f\u043c\u0430 Could control \u0437\u0430\u043a\u0430\u0447\u0435\u043d \u043a\u044a\u043c \u0431\u0430\u0437\u0430\u0442\u0430? \u0418\u043b\u0438 \u0430\u043a\u043e \u043f\u0440\u043e\u0441\u0442\u043e \u0438\u0441\u043a\u0430\u043c\u0435 \u0434\u0430 \u0432\u0438\u0434\u0438\u043c \u043d\u044f\u043a\u043e\u0439 \u0441\u044a\u0432\u0435\u0442 \u0437\u0430 \u0435\u0434\u043d\u043e query, \u043a\u043e\u0435\u0442\u043e \u043d\u0438 \u043c\u044a\u0447\u0438? <\/p>\n<p>\u0412\u0441\u044a\u0449\u043d\u043e\u0441\u0442 \u0441\u0442\u0430\u0432\u0430 \u043c\u043d\u043e\u0433\u043e \u043b\u0435\u0441\u043d\u043e:<\/p>\n<p>1. \u0421\u044a\u0437\u0434\u0430\u0432\u0430\u043d\u0435 \u043d\u0430 \u0442\u0430\u0441\u043a:<\/p>\n<pre class=\"brush:sql\">\r\nDECLARE\r\n  v_task VARCHAR2(30);\r\n  v_sql  CLOB;\r\nBEGIN\r\n  v_sql     := 'SELECT ... FROM ... WHERE ...';\r\n  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => v_sql, \r\n                                               user_name => 'HR', \r\n                                               scope => 'COMPREHENSIVE',\r\n                                               time_limit => 3600, -- seconds\r\n                                               task_name => 'tune_test2',\r\n                                               description => 'Tune statement used for the new XYZ functionality.');\r\nEND;\r\n\/<\/pre>\n<p>2. \u0418\u0437\u043f\u044a\u043b\u043d\u0435\u043d\u0438\u0435. \u0422\u043e\u0432\u0430 \u043c\u043e\u0436\u0435 \u0434\u0430 \u043e\u0442\u043d\u0435\u043c\u0435 \u0434\u043e\u0441\u0442\u0430 \u0432\u0440\u0435\u043c\u0435, \u0432 \u0437\u0430\u0432\u0438\u0441\u0438\u043c\u043e\u0441\u0442 \u043e\u0442 \u0441\u043b\u043e\u0436\u043d\u043e\u0441\u0442\u0442\u0430 \u043d\u0430 \u0437\u0430\u044f\u0432\u043a\u0430\u0442\u0430 \u0438 \u0437\u0430\u0434\u0430\u0434\u0435\u043d\u0438\u044f \u043b\u0438\u043c\u0438\u0442:<\/p>\n<pre class=\"brush:sql\">\r\nexec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');\r\n<\/pre>\n<p>3. \u0414\u0430 \u0432\u0438\u0434\u0438\u043c \u0440\u0435\u0437\u0443\u043b\u0442\u0430\u0442\u0430. \u0422\u043e\u0432\u0430 \u0435 \u0446\u0435\u043b\u0442\u0430, \u0432\u0441\u044a\u0449\u043d\u043e\u0441\u0442<\/p>\n<pre class=\"brush:sql\">\r\nset long 90000 longchunksize 90000\r\nset linesize 232 pagesize 9999\r\nselect dbms_sqltune.report_tuning_task('tune_test2') from dual;\r\n\r\nDBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TEST2')\r\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nGENERAL INFORMATION SECTION\r\n-------------------------------------------------------------------------------\r\nTuning Task Name   : tune_test2\r\nTuning Task Owner  : HR\r\nWorkload Type      : Single SQL Statement\r\nScope              : COMPREHENSIVE\r\nTime Limit(seconds): 3600\r\nCompletion Status  : COMPLETED\r\nStarted at         : 10\/05\/2012 12:07:10\r\nCompleted at       : 10\/05\/2012 12:12:13\r\n\r\n-------------------------------------------------------------------------------\r\nSchema Name: HR\r\nSQL ID     : ba6g55fakh01v\r\nSQL Text   : SELECT ...\r\n\r\n-------------------------------------------------------------------------------\r\nFINDINGS SECTION (1 finding)\r\n-------------------------------------------------------------------------------\r\n\r\n1- SQL Profile Finding (see explain plans section below)\r\n--------------------------------------------------------\r\n  A potentially better execution plan was found for this statement.\r\n\r\n  Recommendation (estimated benefit: 76.48%)\r\n  ------------------------------------------\r\n  - Consider accepting the recommended SQL profile.\r\n    execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',\r\n            task_owner => 'HR', replace => TRUE);\r\n\r\n-------------------------------------------------------------------------------\r\nEXPLAIN PLANS SECTION\r\n-------------------------------------------------------------------------------\r\n\r\n1- Original With Adjusted Cost\r\n------------------------------\r\nPlan hash value: 2801951464\r\n\r\n------------------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                         |                             |    21 |  1302 | 30711   (4)| 00:06:09 |\r\n...\r\n|  97 |    TABLE ACCESS FULL                     | MY_TAB                      |     2 |    10 |     2   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n...\r\n  95 - access(\"E\".\"TEST_ID\"=\"T\".\"TEST_ID\")\r\n\r\n2- Original With Adjusted Cost\r\n------------------------------\r\n\r\n-------------------------------------------------------------------------------\r\nError: cannot fetch explain plan for object: 1\r\n-------------------------------------------------------------------------------\r\n\r\n3- Using SQL Profile\r\n--------------------\r\nPlan hash value: 3439552619\r\n\r\n------------------------------------------------------------------------------------------------------------------------\r\n| Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |\r\n------------------------------------------------------------------------------------------------------------------------\r\n|   0 | SELECT STATEMENT                         |                             |    21 |  1302 |  7223   (2)| 00:01:27 |\r\n....\r\n|  97 |    TABLE ACCESS FULL                     | MY_TAB                      |     2 |    10 |     2   (0)| 00:00:01 |\r\n------------------------------------------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n....\r\n  95 - access(\"E\".\"TEST_ID\"=\"T\".\"TEST_ID\")\r\n<\/pre>\n<p><em>(\u0414\u0430, \u043d\u0430\u0438\u0441\u0442\u0438\u043d\u0430 \u0433\u043e \u043f\u0443\u0441\u043a\u0430\u0445 \u0442\u043e\u0432\u0430 \u0437\u0430 \u0437\u0430\u044f\u0432\u043a\u0430 \u0441 97 \u0441\u0442\u044a\u043f\u043a\u0438 \u0432 \u043f\u043b\u0430\u043d\u0430 \u0438 95 \u0444\u0438\u043b\u0442\u044a\u0440\u0430. \u0415\u0441\u0442\u0435\u0441\u0442\u0432\u0435\u043d\u043e \u0442\u0443\u043a \u0441\u044a\u043c \u043e\u0442\u0440\u044f\u0437\u0430\u043b \u043a\u0430\u043a\u0442\u043e \u0437\u0430\u044f\u0432\u043a\u0430\u0442\u0430, \u0442\u0430\u043a\u0430 \u0438 \u043f\u043b\u0430\u043d\u043e\u0432\u0435\u0442\u0435 \u0438 \u043f\u0440\u0435\u0434\u0438\u043a\u0430\u0442\u0438\u0442\u0435 )<\/em><\/p>\n<p>4. \u041c\u043e\u0436\u0435 \u0438 \u0434\u0430 \u043f\u0440\u0438\u043b\u043e\u0436\u0438\u043c \u043f\u0440\u0435\u0434\u043b\u043e\u0436\u0435\u043d\u0438\u044f \u043f\u043b\u0430\u043d. \u0410\u0437 \u0432 \u0441\u043b\u0443\u0447\u0430\u044f \u0438\u0441\u043a\u0430\u0445 \u0441\u0430\u043c\u043e \u0434\u0430 \u0432\u0438\u0434\u044f \u0441\u044a\u0432\u0435\u0442\u0430, \u043d\u043e \u043f\u044a\u043a \u0437\u0430 \u0434\u0430 \u0441\u0435 \u0438\u0437\u043c\u0435\u0440\u0438 \u0440\u0435\u0430\u043b\u043d\u0430\u0442\u0430 \u043f\u0440\u043e\u043c\u044f\u043d\u0430, \u0442\u0440\u044f\u0431\u0432\u0430 \u0434\u0430 \u0441\u0435 \u0442\u0435\u0441\u0442\u0432\u0430 \u043d\u0430\u0438\u0441\u0442\u0438\u043d\u0430 \u0441 \u043f\u0443\u0441\u043a\u0430\u043d\u0435 \u043d\u0430 \u0437\u0430\u044f\u0432\u043a\u0430\u0442\u0430<\/p>\n<pre class=\"brush:sql\">\r\nexecute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',  task_owner => 'HR', replace => TRUE);\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u0422\u0435\u0441\u0442\u0432\u0430\u043d\u043e \u043d\u0430 11.2.0.2 SQL tunning advisor \u0435 \u0441\u0442\u0440\u0430\u0448\u043d\u0430 \u0434\u0430\u043b\u0430\u0432\u0435\u0440\u0430. \u0415, \u0438\u0437\u0438\u0441\u043a\u0432\u0430 \u0434\u043e\u043f\u044a\u043b\u043d\u0438\u0442\u0435\u043b\u0435\u043d \u043b\u0438\u0446\u0435\u043d\u0437 \u0437\u0430 tunning pack, \u043d\u043e \u0432\u044a\u0440\u0448\u0438 \u0440\u0430\u0431\u043e\u0442\u0430, \u043a\u043e\u044f\u0442\u043e \u0437\u0430 \u043e\u0431\u0438\u043a\u043d\u043e\u0432\u0435\u043d \u0447\u043e\u0432\u0435\u043a \u0435 \u043f\u0440\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0438 \u043d\u0435\u0432\u044a\u0437\u043c\u043e\u0436\u043d\u0430. \u041f\u043e-\u0447\u0435\u0441\u0442\u0438\u044f\u0442 \u0432\u0430\u0440\u0438\u0430\u043d\u0442 \u0435 \u043a\u043e\u0433\u0430\u0442\u043e \u043d\u0435\u0449\u043e \u0441\u0435 \u0441\u043a\u0430\u043f\u0435 \u0432 production \u0438 \u0442\u0440\u044f\u0431\u0432\u0430 \u0441\u043f\u0435\u0448\u043d\u043e \u0434\u0430 \u0441\u0435 \u043e\u043f\u0440\u0430\u0432\u0438. \u0415\u0445, \u043a\u043e\u043b\u043a\u043e \u043f\u044a\u0442\u0438 \u043d\u0438 \u0435 \u0441\u043f\u0430\u0441\u044f\u0432\u0430\u043b SQL tunning advisor \u0432 \u0442\u0430\u043a\u0438\u0432\u0430 \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u0438&#8230; \u0425\u0435\u043c \u043d\u0430\u043c\u0438\u0440\u0430 <a href='http:\/\/blog.yavor.info\/?p=840' class='excerpt-more'>[&#8230;]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/840"}],"collection":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=840"}],"version-history":[{"count":6,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/840\/revisions"}],"predecessor-version":[{"id":846,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=\/wp\/v2\/posts\/840\/revisions\/846"}],"wp:attachment":[{"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=840"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.yavor.info\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}