{"id":433,"date":"2014-08-15T14:53:59","date_gmt":"2014-08-15T06:53:59","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=433"},"modified":"2014-08-15T14:59:28","modified_gmt":"2014-08-15T06:59:28","slug":"%e6%80%a7%e8%83%bd%e8%b0%83%e4%bc%98%e5%b7%a5%e5%85%b705-sql-profiles","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=433","title":{"rendered":"\u6027\u80fd\u8c03\u4f18\u5de5\u517705–SQL Profiles"},"content":{"rendered":"
SQL Profiles<\/p>\n
\n
    \n
  1. SQL Profiles\u4ecb\u7ecd:\n
      \n
    1. SQL Profiles\u662f\u572810g\u4e2d\u88ab\u5f15\u8fdb\u7684\u65b0\u7279\u6027,\u4e3b\u8981\u662f\u901a\u8fc7DBMS_SQLTUNE\u5305\u6216\u8005EM\u6765\u7ba1\u7406,\u5b83\u662fAST(Automatic SQL Tuning)\u8fc7\u7a0b\u7684\u4e00\u90e8\u5206;\u4e5f\u572810g\u7248\u672c\u4e2d\u4f7f\u7528\u8f83\u591a,11g\u4e2d\u66f4\u591a\u4f7f\u7528SPM;<\/li>\n
    2. Automatic SQL Tuning:\n
        \n
      1. \u56e0\u4e3a\u7f3a\u5c11\u5fc5\u8981\u7684\u4fe1\u606f,\u67e5\u8be2\u4f18\u5316\u5668\u5bf9sql\u8bed\u53e5\u53ef\u80fd\u4f1a\u4ea7\u751f\u4e0d\u51c6\u786e\u7684\u8bc4\u4f30(\u6bd4\u5982\u8fd4\u56de\u7684\u8bb0\u5f55\u6570\u7b49),\u4ece\u800c\u5bfc\u81f4\u751f\u6210\u4e00\u4e2a\u8f83\u5dee\u7684\u6267\u884c\u8ba1\u5212;\u4f20\u7edf\u7684\u5904\u7406\u65b9\u6cd5\u662f\u624b\u52a8\u6dfb\u52a0hints\u6765\u6539\u53d8\u6267\u884c\u8ba1\u5212,\u4f46\u662f\u5bf9\u4e8e\u6253\u5305\u7684\u5e94\u7528\u7a0b\u5e8f,\u65e0\u6cd5\u4fee\u6539\u76f8\u5e94\u7684sql\u8bed\u53e5,\u6240\u4ee5\u53ea\u80fd\u7b49\u5e94\u7528\u7a0b\u5e8f\u63d0\u4f9b\u5546\u6765\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898;<\/li>\n
      2. Automatic SQL Tuning\u4f7f\u7528SQL Profile\u7684\u7279\u6027\u89e3\u51b3\u4e86\u8fd9\u4e2a\u95ee\u9898;Automatic Tuning Optimizer\u4f1a\u4e3a\u6bcf\u6761sql\u8bed\u53e5\u521b\u5efa\u4e00\u4e2a\u5305\u542b\u8f85\u52a9\u4fe1\u606f(\u4e3b\u8981\u901a\u8fc7\u52a8\u6001\u91c7\u7528\u548c\u90e8\u5206\u6267\u884c\u6280\u672f\u6536\u96c6\u9644\u52a0\u4fe1\u606f)\u7684SQL Profile;\u67e5\u8be2\u4f18\u5316\u5668\u53ef\u4ee5\u8ba1\u7b97\u51fa\u66f4\u7cbe\u786e\u7684cardinality\/selectivity\/cost\u7684\u8bc4\u4f30,\u4ece\u800c\u751f\u6210\u66f4\u4f18\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n
      3. \u5728\u4ea7\u751fSQL Profile\u671f\u95f4,Automatic Tuning Optimizer\u4e5f\u4f1a\u4e3a\u4e86\u4e0e\u4f18\u5316\u5668\u53c2\u6570\u5339\u914d\u4ece\u800c\u6267\u884cSQL\u8bed\u53e5\u7684\u5386\u53f2\u4fe1\u606f,\u6bd4\u5982\u628aoptimizer_mode\u53c2\u6570\u4eceall_rows\u4fee\u6539\u5230first_rows;<\/li>\n<\/ol>\n<\/li>\n
      4. SQL Profile:\n
          \n
        1. SQL Profile\u662f\u5b58\u50a8\u5728\u6570\u636e\u5b57\u5178\u4e2d\u7684\u4e00\u7cfb\u5217\u7684\u4fe1\u606f(\u4e3b\u8981\u662f\u5305\u62ec\u5728AST\u8fc7\u7a0b\u4e2d\u9488\u5bf9\u8f83\u5dee\u6267\u884c\u8ba1\u5212\u7684\u4e00\u4e9b\u4fee\u6b63\u4fe1\u606f),\u5b83\u53ef\u4ee5\u4f7f\u67e5\u8be2\u4f18\u5316\u5668\u9488\u5bf9\u67d0\u6761sql\u751f\u6210\u66f4\u4f18\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n
        2. SQL Profile\u5e76\u4e0d\u5305\u542b\u72ec\u7acb\u7684\u6267\u884c\u8ba1\u5212,\u53ea\u662f\u4f18\u5316\u5668\u5728\u9009\u62e9\u6267\u884c\u8ba1\u5212\u65f6\u9664\u4e86\u53c2\u8003\u6570\u636e\u5e93\u914d\u7f6e\/\u7ed1\u5b9a\u53d8\u91cf\u503c\/\u4f18\u5316\u5668\u7edf\u8ba1\u4fe1\u606f\/\u6570\u636e\u96c6\u7b49\u4fe1\u606f\u4e4b\u5916\u8fd8\u4f1a\u53c2\u8003SQL Profile\u4e2d\u7684\u9644\u52a0\u4fe1\u606f;<\/li>\n
        3. \u4e00\u6bb5\u65f6\u95f4\u540e,SQL Profile\u7684\u5185\u5bb9\u53ef\u80fd\u4f1a\u8fc7\u671f,\u9700\u8981\u8fd0\u884cAST\u91cd\u65b0\u751f\u6210SQL Profile;<\/li>\n
        4. tips:SQL Profile\u5e76\u4e0d\u4f1a\u50cfStored Outlines\u4e00\u6837\u56fa\u5b9aSQL\u8bed\u53e5\u7684\u6267\u884c\u8ba1\u5212;\u5373\u4fbf\u62e5\u6709\u76f8\u540cSQL Profile,\u7531\u4e8e\u8868\u7684\u589e\u957f\u6216\u8005\u7d22\u5f15\u7684\u521b\u5efa\u5220\u9664,\u6267\u884c\u8ba1\u5212\u4e5f\u4f1a\u6539\u53d8;<\/li>\n<\/ol>\n<\/li>\n
        5. \u5982\u4f55\u63a7\u5236SQL Profile\u7684\u8303\u56f4:\n
            \n
          1. SQL Profile\u7684\u8303\u56f4\u7531category\u5c5e\u6027\u63a7\u5236,\u5b83\u51b3\u5b9a\u4e86\u54ea\u4e9b\u7528\u6237\u4f1a\u8bdd\u53ef\u4ee5\u5e94\u7528\u8fd9\u4e9bprofile;<\/li>\n
          2. \u53ef\u4ee5\u901a\u8fc7dba_sql_profiles\u5b57\u5178\u6765\u67e5\u770b(SELECT NAME, category FROM dba_sql_profiles);<\/li>\n
          3. \u9ed8\u8ba4\u60c5\u51b5\u4e0b,\u6240\u6709\u7684profiles\u90fd\u5c5e\u4e8eDEFAULT\u5206\u7c7b,\u8868\u793a\u6240\u6709\u7684\u521d\u59cb\u5316\u53c2\u6570sqltune_category=DEFAULT(\u8fd9\u4e5f\u662f\u9ed8\u8ba4\u503c)\u7684\u7528\u6237\u4f1a\u8bdd\u90fd\u53ef\u4f7f\u7528\u8fd9\u4e9bprofile;<\/li>\n
          4. \u53ef\u4ee5\u901a\u8fc7\u4fee\u6539sqltune_category\u53c2\u6570\u6765\u63a7\u5236profile\u4f7f\u7528\u7684\u8303\u56f4,\u4ece\u800c\u53ef\u4ee5\u5728\u53d1\u5e03\u4e4b\u524d\u8fdb\u884c\u6d4b\u8bd5;<\/li>\n<\/ol>\n<\/li>\n
          5. \u53ef\u4ee5\u4f7f\u7528SQL Profile\u7684\u8bed\u53e5:\n
              \n
            1. SELECT\u8bed\u53e5;<\/li>\n
            2. UDPATE\u8bed\u53e5;<\/li>\n
            3. INSERT INTO SELECT\u8bed\u53e5;<\/li>\n
            4. DELETE\u8bed\u53e5;<\/li>\n
            5. CTAS\u8bed\u53e5;<\/li>\n
            6. MERGE\u8bed\u53e5(\u5305\u542bUPDATE\u6216\u8005INSERT\u64cd\u4f5c);<\/li>\n<\/ol>\n<\/li>\n
            7. Sql Profiles\u662f\u5bf9OUTLINES\u7684\u589e\u5f3a,\u5e76\u4e14\u5177\u6709\u81ea\u5df1\u7684\u7279\u70b9:\n
                \n
              1. SQL Profiles\u66f4\u5bb9\u6613\u751f\u6210,\u66f4\u6539\u548c\u63a7\u5236;<\/li>\n
              2. SQL Profiles\u5bf9SQL\u8bed\u53e5\u7684\u652f\u6301\u66f4\u5e7f\u6cdb;<\/li>\n<\/ol>\n<\/li>\n
              3. \u4f7f\u7528SQL Profiles\u7684\u76ee\u7684:\n
                  \n
                1. \u7a33\u5b9a\u6216\u8005”\u9501\u5b9a”SQL\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n
                2. \u5728\u4e0d\u80fd\u4fee\u6539\u5e94\u7528\u7a0b\u5e8f\u4e2dSQL\u7684\u60c5\u51b5\u4e0b,\u4f7fSQL\u6309\u7167\u6307\u5b9a\u7684\u6267\u884c\u8ba1\u5212\u8fd0\u884c;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
                3. \u7ba1\u7406SQL Profiles:\n
                    \n
                  1. \u9996\u5148\u7528\u6237\u9700\u8981\u6709CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, ALTER ANY SQL_PROFILE\u6743\u9650;<\/li>\n
                  2. \u4eceSTA\u4f18\u5316\u7ed3\u679c\u4e2d\u63a5\u53d7\u4e00\u4e2aProfile:DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name,object_id,name,description,category,task_owner,replace,force_match,profile_type),\u4f8b\u5b50<<\u5982\u4f55\u4f7f\u7528SQL Tuning Advisor\u6765\u751f\u6210SQL Profile,\u53caSQL Profile\u539f\u7406>>;<\/li>\n
                  3. \u4fee\u6539Profile:DBMS_SQLTUNE.ALTER_SQL_PROFILE (name,attribute_name,value);<\/li>\n
                  4. \u5220\u9664Profile:DBMS_SQLTUNE.DROP_SQL_PROFILE (name,ignore);<\/li>\n
                  5. SQL Profile\u7684\u8fc1\u79fb,\u4f8b\u5b50<<How to Move SQL Profiles from One Database to Another>>;<\/li>\n
                  6. \u4f7f\u7528DBMS_SQLTUNE.IMPORT_SQL_PROFILE\u8fc7\u7a0b\u624b\u5de5\u521b\u5efaSQL Profiles,\u8fd9\u79cd\u65b9\u6cd5\u521b\u5efa\u7684SQL Profiles\u548cOutlines\u4e00\u6837,\u80fd\u591f\u7a33\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212,\u4f8b\u5b50;\n
                      \n
                    1. \u7a33\u5b9a\u73b0\u6709\u7684SQL\u7684\u6267\u884c\u8ba1\u5212;<\/li>\n
                    2. \u5728\u4e0d\u80fd\u4fee\u6539SQL\u7684\u60c5\u51b5\u4e0b\u6539\u53d8\u5e76\u56fa\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212,\u5373\u4f7f\u539f\u6765\u7684SQL\u4f7f\u7528\u4e86hints;<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/div>\n
                      <\/div>\n
                      ———————- \u5982\u4f55\u4f7f\u7528SQL Tuning Advisor\u6765\u751f\u6210SQL Profile,\u53caSQL Profile\u539f\u7406 ———————-<\/div>\n
                      — 1.\u5b9e\u9a8c\u73af\u5883;<\/div>\n
                      SQL>\u00a0SELECT * FROM v$version;<\/p>\n

                      BANNER
                      \n—————————————————————-
                      \nOracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
                      \nPL\/SQL Release 10.2.0.1.0 – Production
                      \nCORE\u00a0\u00a0\u00a0 10.2.0.1.0\u00a0\u00a0\u00a0\u00a0\u00a0 Production
                      \nTNS for Linux: Version 10.2.0.1.0 – Production
                      \nNLSRTL Version 10.2.0.1.0 – Production<\/p><\/div>\n

                      <\/div>\n
                      — 2.\u521b\u5efa\u6d4b\u8bd5\u8868;<\/div>\n
                      conn \/ as sysdba
                      \nCREATE TABLE t1 AS SELECT object_id, object_name FROM dba_objects WHERE rownum <= 50000;
                      \nCREATE TABLE t2 AS SELECT * FROM dba_objects;
                      \nCREATE INDEX idx_t2_oid ON t2(object_id);<\/p>\n

                      BEGIN
                      \ndbms_stats.gather_table_stats(USER, ‘T1’, cascade => TRUE, method_opt => ‘for all columns size 1’);
                      \ndbms_stats.gather_table_stats(USER, ‘T2’, cascade => TRUE, method_opt => ‘for all columns size 1’);
                      \nEND;
                      \n\/<\/p>\n

                      — 3.\u67e5\u770bSQL\u7684\u6267\u884c\u8ba1\u5212\u548c\u7edf\u8ba1\u4fe1\u606f,\u5e76\u9009\u62e9\u4e0d\u540c\u7684\u8bbf\u95ee\u8def\u5f84;<\/p><\/div>\n

                      1.\u67e5\u770bSQL\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      \n
                      \u00a0 \u00a0 \u00a01.\u4ece\u6267\u884c\u7ed3\u679c\u53d1\u73b0:\u8fd9\u6761sql\u5728\u4e24\u4e2a\u8868\u4e0a\u90fd\u662f\u5168\u8868\u626b\u63cf,\u5728T1\u4e0a\u56e0\u4e3a\u6709like\u6761\u4ef6,\u6240\u4ee5\u5f88\u6b63\u5e38;\u5728T2\u8868\u4e0a\u5e94\u8be5\u6709\u5176\u5b83\u7684\u8bbf\u95ee\u8def\u5f84;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u5728\u6267\u884c\u8ba1\u5212ID=2\u90a3\u4e00\u884c,CBO\u8bc4\u4f30\u8868T1\u00a0LIKE ‘%T1%’\u7684\u8fd4\u56de\u7ed3\u679c\u4e3a2500\u884c,\u5373T1\u8868\u603b\u8bb0\u5f55\u76845%(\u7c7b\u4f3c\u4e8eLIKE\u7b49\u8c13\u8bcd\u7684\u9ed8\u8ba4\u9009\u62e9\u7387\u4e3a5%<\/span>);\u5982\u679c\u91c7\u7528\u4e24\u4e2a\u8868\u91c7\u7528INDEX RANGE SCAN + NESTED LOOPS\u8fde\u63a5\u7684\u8bdd,CBO\u4f30\u7b97\u7684\u6210\u672c\u4f1a\u9ad8\u4e8eFULL TABLE SCAN + HASH JOIN;<\/div>\n
                      <\/div>\n

                      SQL> SET AUTOT ON;
                      \nSQL> SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;<\/p><\/div>\n

                      <\/div>\n
                      ……<\/div>\n
                      26 rows selected.<\/p>\n

                      Execution Plan
                      \n———————————————————-
                      \nPlan hash value: 1838229974<\/p>\n

                      —————————————————————————
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |
                      \n—————————————————————————
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2500 |\u00a0\u00a0\u00a0 97K|\u00a0\u00a0 212\u00a0\u00a0 (3)| 00:00:03 |
                      \n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2500 |\u00a0\u00a0\u00a0 97K|\u00a0\u00a0 212\u00a0\u00a0 (3)| 00:00:03 |
                      \n|*\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL| T1\u00a0\u00a0 |\u00a0 2500 | 72500 |\u00a0\u00a0\u00a0 55\u00a0\u00a0 (4)| 00:00:01 |
                      \n|\u00a0\u00a0 3 |\u00a0\u00a0 TABLE ACCESS FULL| T2\u00a0\u00a0 | 50350 |\u00a0\u00a0 540K|\u00a0\u00a0 155\u00a0\u00a0 (2)| 00:00:02 |
                      \n—————————————————————————<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
                      \n2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)<\/p>\n

                      Statistics
                      \n———————————————————-
                      \n304\u00a0 recursive calls
                      \n0\u00a0 db block gets
                      \n974\u00a0 consistent gets
                      \n0\u00a0 physical reads
                      \n0\u00a0 redo size
                      \n1402\u00a0 bytes sent via SQL*Net to client
                      \n396\u00a0 bytes received via SQL*Net from client
                      \n3\u00a0 SQL*Net roundtrips to\/from client
                      \n5\u00a0 sorts (memory)
                      \n0\u00a0 sorts (disk)
                      \n26\u00a0 rows processed<\/p><\/div>\n

                      <\/div>\n
                      2.\u52a0hints\u6539\u53d8sql\u7684\u8bbf\u95ee\u8def\u5f84\u67e5\u770b\u6210\u672c;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u4ece\u6267\u884c\u8ba1\u5212\u53d1\u73b0:CBO\u8bc4\u4f30\u7684\u6210\u672c\u4e3a5061,\u8fdc\u8fdc\u9ad8\u4e8e\u539f\u6765\u7684212;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u4ece\u7edf\u8ba1\u4fe1\u606f\u53d1\u73b0:\u52a0\u4e86hint\u4e4b\u540e\u7684\u903b\u8f91\u8bfb\u53ea\u6709288,\u8fdc\u8fdc\u4f4e\u4e8e\u539f\u6765\u7684974;<\/div>\n
                      \u00a0 \u00a0 \u00a03.\u8bf4\u660e\u7531\u4e8eORACLE\u8fc7\u9ad8\u7684\u4f30\u8ba1\u4e86T1\u8868\u8fd4\u56de\u7684\u7ed3\u679c\u96c6\u884c\u6570,\u8fdb\u800c\u8fc7\u9ad8\u7684\u4f30\u8ba1\u4e86NEST LOOP\u7684\u6210\u672c,\u5bfc\u81f4\u6ca1\u6709\u9009\u62e9\u6700\u4f18\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      \u00a0 \u00a0 \u00a04.tips:\u4e5f\u53ef\u4ee5\u4f7f\u7528EXPLAIN PLAN FOR\u7136\u540eSELECT * FROM TABLE(DBMS_XPLAN.display())\u7684\u65b9\u6cd5\u67e5\u770b\u6267\u884c\u8ba1\u5212;<\/div>\n
                      <\/div>\n
                      SQL>\u00a0SET AUTO ON;<\/div>\n
                      SQL> SELECT \/*+ use_nl(t1 t2) index(t2) *\/ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;<\/p>\n

                      26 rows selected.<\/p>\n

                      Execution Plan
                      \n———————————————————-
                      \nPlan hash value: 1164754026<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      | Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)|
                      \nTime\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      |\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2500 |\u00a0\u00a0\u00a0 97K|\u00a0 5061\u00a0\u00a0 (1)|
                      \n00:01:01 |<\/p>\n

                      |\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 11 |\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      |\u00a0\u00a0 2 |\u00a0\u00a0 NESTED LOOPS\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 2500 |\u00a0\u00a0\u00a0 97K|\u00a0 5061\u00a0\u00a0 (1)|
                      \n00:01:01 |<\/p>\n

                      |*\u00a0 3 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2500 | 72500 |\u00a0\u00a0\u00a0 55\u00a0\u00a0 (4)|
                      \n00:00:01 |<\/p>\n

                      |*\u00a0 4 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | IDX_T2_OID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
                      \n4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)<\/p>\n

                      Statistics
                      \n———————————————————-
                      \n1\u00a0 recursive calls
                      \n0\u00a0 db block gets
                      \n288\u00a0 consistent gets
                      \n0\u00a0 physical reads
                      \n0\u00a0 redo size
                      \n1402\u00a0 bytes sent via SQL*Net to client
                      \n396\u00a0 bytes received via SQL*Net from client
                      \n3\u00a0 SQL*Net roundtrips to\/from client
                      \n0\u00a0 sorts (memory)
                      \n0\u00a0 sorts (disk)
                      \n26\u00a0 rows processed<\/p><\/div>\n

                      <\/div>\n
                      — 4.\u4f7f\u7528STA\u8c03\u4f18\u8fd9\u6761sql;<\/div>\n
                      1.\u67e5\u770b\u8fd9\u6761sql\u7684sql_id\u4e3a’cy6x06rhrt54x’;<\/div>\n
                      \u53ef\u4ee5\u4ecev$sql\u6216\u8005dba_hist_sqltext\u89c6\u56fe\u4e2d\u62ff\u5230sql_id;<\/div>\n
                      2.\u53ef\u4ee5\u4f7f\u7528\u5de5\u5177\u5305sqltrpt.sql\u6765\u8c03\u4f18;<\/div>\n
                      SQL>\u00a0@?\/rdbms\/admin\/sqltrpt.sql<\/a><\/div>\n
                      Tune the sql
                      \n~~~~~~~~~~~~<\/p>\n

                      GENERAL INFORMATION SECTION
                      \n——————————————————————————-
                      \nTuning Task Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : TASK_62
                      \nTuning Task Owner\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : SYS
                      \nScope\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 : COMPREHENSIVE
                      \nTime Limit(seconds)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 1800
                      \nCompletion Status\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : COMPLETED
                      \nStarted at\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 07\/26\/2014 10:45:03
                      \nCompleted at\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 07\/26\/2014 10:45:03
                      \nNumber of SQL Profile Findings\u00a0\u00a0\u00a0 : 1<\/p>\n

                      ——————————————————————————-
                      \nSchema Name: SYS<\/p>\n

                      SQL ID\u00a0\u00a0\u00a0\u00a0 : cy6x06rhrt54x
                      \nSQL Text\u00a0\u00a0 : SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE
                      \n‘%T1%’ AND t1.object_id = t2.object_id<\/p>\n

                      ——————————————————————————-
                      \nFINDINGS SECTION (1 finding)
                      \n——————————————————————————-<\/p>\n

                      1- SQL Profile Finding (see explain plans section below)
                      \n——————————————————–
                      \nA potentially better execution plan was found for this statement.<\/p>\n

                      Recommendation (estimated benefit: 49.32%)<\/p>\n

                      ——————————————
                      \n– Consider accepting the recommended SQL profile.
                      \nexecute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, replace
                      \n=> TRUE);<\/span><\/p>\n

                      ——————————————————————————-
                      \nEXPLAIN PLANS SECTION
                      \n——————————————————————————-<\/p>\n

                      1- Original With Adjusted Cost
                      \n——————————
                      \nPlan hash value: 1838229974<\/p>\n

                      —————————————————————————
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |
                      \n—————————————————————————
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 26 |\u00a0 1040 |\u00a0\u00a0 212\u00a0\u00a0 (3)| 00:00:03 |
                      \n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 26 |\u00a0 1040 |\u00a0\u00a0 212\u00a0\u00a0 (3)| 00:00:03 |
                      \n|*\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL| T1\u00a0\u00a0 |\u00a0\u00a0\u00a0 26 |\u00a0\u00a0 754 |\u00a0\u00a0\u00a0 55\u00a0\u00a0 (4)| 00:00:01 |
                      \n|\u00a0\u00a0 3 |\u00a0\u00a0 TABLE ACCESS FULL| T2\u00a0\u00a0 | 50350 |\u00a0\u00a0 540K|\u00a0\u00a0 155\u00a0\u00a0 (2)| 00:00:02 |
                      \n—————————————————————————<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)<\/p>\n

                      2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)<\/p>\n

                      2- Using SQL Profile
                      \n——————–
                      \nPlan hash value: 1164754026<\/p>\n

                      ——————————————————————————–
                      \n———-
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)|
                      \nTime\u00a0\u00a0\u00a0\u00a0 |
                      \n——————————————————————————–
                      \n———-
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\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 26 |\u00a0 1040 |\u00a0\u00a0 107\u00a0\u00a0 (2)|<\/p>\n

                      00:00:02 |
                      \n|\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 11 |\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0 (0)|
                      \n00:00:01 |
                      \n|\u00a0\u00a0 2 |\u00a0\u00a0 NESTED LOOPS\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 26 |\u00a0 1040 |\u00a0\u00a0 107\u00a0\u00a0 (2)|
                      \n00:00:02 |
                      \n|*\u00a0 3 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a026<\/span>\u00a0|\u00a0\u00a0 754 |\u00a0\u00a0\u00a0 55\u00a0\u00a0 (4)|
                      \n00:00:01 |
                      \n|*\u00a0 4 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | IDX_T2_OID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)|
                      \n00:00:01 |
                      \n——————————————————————————–
                      \n———-<\/p>\n

                      Predicate Information (identified by operation id):<\/p>\n

                      —————————————————<\/p>\n

                      3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
                      \n4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)<\/p>\n

                      3.\u4ece\u7ed3\u679c\u53d1\u73b0STA\u627e\u5230\u4e86\u6700\u4f18\u7684\u6267\u884c\u8ba1\u5212,T1\u8868\u4e0a\u7684\u6761\u4ef6\u8fc7\u6ee4\u540e\u7684\u7ed3\u679c\u96c6\u8bb0\u5f55\u6570\u4e3a26,\u975e\u5e38\u51c6\u786e,\u6240\u4ee5\u53ef\u4ee5\u9009\u62e9\u63a5\u53d7\u8fd9\u4e2aSQL Profile;<\/p><\/div>\n

                      SQL> EXECUTE dbms_sqltune.accept_sql_profile(task_name => ‘TASK_62’, REPLACE=> TRUE);<\/p>\n

                      PL\/SQL procedure successfully completed.<\/p><\/div>\n

                      \n— 5.\u67e5\u770bSQL Profile\u505a\u4e86\u4ec0\u4e48;<\/div>\n
                      1.\u67e5\u770bSQL Profile\u4fe1\u606f;<\/div>\n
                      SQL>\u00a0 SELECT NAME, CATEGORY, signature, sql_text FROM dba_sql_profiles;<\/p>\n

                      NAME\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 CATEGORY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SIGNATURE
                      \n—————————— —————————— ———-
                      \nSQL_TEXT
                      \n——————————————————————————–
                      \nSYS_SQLPROF_0151dc24f6338000\u00a0\u00a0 DEFAULT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8.9755E+18
                      \nSELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
                      \n_id = t2.object_id<\/p><\/div>\n

                      <\/div>\n
                      2.\u5728sys.sqlprof$attr\u6570\u636e\u5b57\u5178(11g\u4e2d\u4e3aSQLOBJ$,SQLOBJ$AUXDATA,SQLOBJ$DATA\u5b57\u5178)\u4e2d,\u6709signature\u548cattr_val\u4e24\u5217\u91cd\u8981\u4fe1\u606f;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u572810g\u4e4b\u540e\u7684v$sql\u89c6\u56fe\u4e2d\u6709\u4e24\u5217exact_matching_signature,force_matching_signature\u4fe1\u606f,\u7528\u6765\u8868\u793asql\u7684\u7279\u5f81,\u53ef\u4ee5\u7528\u4e8e\u67e5\u627e\u6ca1\u6709\u4f7f\u7528\u7ed1\u5b9a\u53d8\u91cf\u7684sql\u8bed\u53e5;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u5bf9\u4e8edbms_sqltune.accept_sql_profile\u8fc7\u7a0b\u4e2d,\u5982\u679cforce_match=>true\u7684\u8bdd\u8868\u793adba_sql_profile\u4e2d\u7684signature\u7531sql\u7684force_matching_signature\u800c\u6765,\u5426\u5219\u5c31\u662fexact_matching_signature;<\/div>\n
                      \u00a0 \u00a0 \u00a03.\u8fd9\u4e5f\u9a8c\u8bc1\u4e86SQL Profile\u6bd4outlines\u597d\u7684\u4e00\u70b9,\u56e0\u4e3a\u5b83\u4e5f\u652f\u6301\u6ca1\u6709\u4f7f\u7528\u7ed1\u5b9a\u53d8\u91cf\u7684SQL;<\/div>\n
                      \u00a0 \u00a0 \u00a04.attr_val\u5b9e\u9645\u4e0a\u5c31\u662f\u4e00\u4e9bhints,\u548coutline\u672c\u8d28\u662f\u4e00\u6837\u7684,\u53ea\u662fSQL Profile\u4e2d\u7684hints\u6ca1\u6709\u6307\u5b9asql\u4f7f\u7528\u7684\u7d22\u5f15\u548c\u8868\u8fde\u63a5\u7684\u65b9\u5f0f\u53ca\u987a\u5e8f;\u8fd9\u91cc\u4ec5\u4ec5\u63d0\u4f9b\u4e86\u4e00\u4e9b\u8f85\u52a9\u4fe1\u606f,\u5373\u8bc4\u4f30\u4e86\u8868\u8fd4\u56de\u8bb0\u5f55\u7684\u884c\u6570,\u4e0e\u539f\u59cb\u7684\u8bc4\u4f30\u8fd4\u56de\u7684\u884c\u6570\u7684\u653e\u5927\/\u7f29\u5c0f\u500d\u6570;<\/div>\n
                      \u00a0 \u00a0 \u00a05.\u8ba1\u7b970.0104*2500=26,\u5c31\u544a\u8bc9CBO,T1\u8868\u7ecf\u8fc7\u8c13\u8bcd\u8fc7\u6ee4\u8fd4\u56de\u884c\u6570\u5e94\u8be5\u4e3a\u8bc4\u4f30\u76840.0104\u500d;<\/div>\n
                      \u00a0 \u00a0 \u00a06.\u8fd9\u91cc\u4e5f\u9a8c\u8bc1\u4e86SQL Profile\u5e76\u4e0d\u4f1a\u9501\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212,\u53ea\u662f\u63d0\u4f9b\u66f4\u591a,\u66f4\u51c6\u786e\u7684\u7edf\u8ba1\u4fe1\u606f\u7ed9CBO\u53c2\u8003;<\/div>\n
                      <\/div>\n
                      SQL> SELECT * FROM sys.sqlprof$attr;<\/p>\n

                      SIGNATURE \u00a0 CATEGORY\u00a0\u00a0\u00a0\u00a0\u00a0 ATTR# \u00a0\u00a0ATTR_VAL
                      \n———–\u00a0——– ———- \u00a0——————————————————————————–<\/p><\/div>\n

                      8.9755E+18\u00a0 DEFAULT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 \u00a0OPT_ESTIMATE(@”SEL$1″, TABLE, “T1″@”SEL$1”, SCALE_ROWS=0.0104)<\/p>\n<\/div>\n
                      3.\u4fee\u6539\u8c13\u8bcd\u6761\u4ef6?\u5982\u679c\u6267\u884csql:SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE\u00a0‘%T2%’<\/span>\u00a0AND t1.object_id = t2.object_id;<\/div>\n
                      1.\u572810.2.0.1\u7248\u672c\u4e2d:\u5219\u8fd8\u662f\u4f1a\u8d70\u6ca1\u6709\u4f7f\u7528SQL Profile\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      2.\u4e5f\u8bb8\u5728\u5176\u5b83\u7684\u7248\u672c\u4e2d\u4f1a\u4f7f\u7528SQL Profile,\u4f46\u662f\u8bc4\u4f30\u7684T1\u8868\u8fd4\u56de\u7684\u8bb0\u5f55\u6570\u5e94\u8be5\u548cLIKE\u00a0‘%T1%’<\/span>\u4e00\u6837,\u4ee5\u4e3a\u8fd9\u4e2aSQL Profile\u662f\u4e3a\u8fd9\u4e2a\u6761\u4ef6\u751f\u6210\u7684;<\/div>\n
                      <\/div>\n
                      4.\u4fee\u6539\u8868\u7684\u7edf\u8ba1\u4fe1\u606f,\u8fdb\u4e00\u6b65\u9a8c\u8bc1;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u5c06T1\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u4fee\u6539\u4e3a500W,\u5219Oracle\u4f1a\u8bc4\u4f30\u8fd4\u56de500W*5%*0.0104=2600\u884c\u8bb0\u5f55;\u6267\u884c\u8ba1\u5212\u53d8\u4e3aFULL TABLE SCAN + HASH JOIN;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u867d\u7136SQL Profile\u8d77\u4e86\u6240\u7528,\u4f46\u662f\u5e76\u6ca1\u6709\u9501\u5b9a\u6267\u884c\u8ba1\u5212;<\/div>\n
                      <\/div>\n
                      SQL> EXEC dbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows=>5000000);<\/div>\n
                      \nSQL> EXPLAIN PLAN FOR SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;<\/p>\n

                      Explained.<\/p>\n

                      SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \nPlan hash value: 1838229974<\/p>\n

                      —————————————————————————
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |
                      \n—————————————————————————
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2600 |\u00a0\u00a0 101K|\u00a0\u00a0 401\u00a0 (49)| 00:00:05 |
                      \n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2600 |\u00a0\u00a0 101K|\u00a0\u00a0 401\u00a0 (49)| 00:00:05 |
                      \n|*\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL| T1\u00a0\u00a0 |\u00a0 2600 | 75400 |\u00a0\u00a0 245\u00a0 (79)| 00:00:03 |
                      \n|\u00a0\u00a0 3 |\u00a0\u00a0 TABLE ACCESS FULL| T2\u00a0\u00a0 | 50350 |\u00a0\u00a0 540K|\u00a0\u00a0 155\u00a0\u00a0 (2)| 00:00:02 |
                      \n—————————————————————————<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \nPredicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
                      \n2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)<\/p>\n

                      Note
                      \n—–
                      \n– SQL profile “SYS_SQLPROF_0151dc24f6338000” used for this statement<\/p><\/div>\n

                      ———————- \u5982\u4f55\u4f7f\u7528SQL Tuning Advisor\u6765\u751f\u6210SQL Profile,\u53caSQL Profile\u539f\u7406\u00a0———————-<\/div>\n
                      <\/div>\n
                      ———————-\u00a0How to Move SQL Profiles from One Database to Another\u00a0———————-<\/div>\n
                      — 1.\u5b9e\u9a8c\u73af\u5883;<\/div>\n
                      1.\u6e90\u7aef\u6570\u636e\u5e93\u4fe1\u606f;<\/div>\n
                      SQL>\u00a0SELECT * FROM v$version;<\/p>\n

                      BANNER
                      \n—————————————————————-
                      \nOracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
                      \nPL\/SQL Release 10.2.0.1.0 – Production
                      \nCORE\u00a0\u00a0\u00a0 10.2.0.1.0\u00a0\u00a0\u00a0\u00a0\u00a0 Production
                      \nTNS for Linux: Version 10.2.0.1.0 – Production
                      \nNLSRTL Version 10.2.0.1.0 – Production<\/p><\/div>\n

                      <\/div>\n
                      2.\u76ee\u6807\u7aef\u6570\u636e\u5e93\u4fe1\u606f;<\/div>\n
                      SQL>\u00a0SELECT * FROM v$version;<\/p>\n

                      BANNER
                      \n——————————————————————————–
                      \nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
                      \nPL\/SQL Release 11.2.0.3.0 – Production
                      \nCORE\u00a0\u00a0\u00a0 11.2.0.3.0\u00a0\u00a0\u00a0\u00a0\u00a0 Production
                      \nTNS for Linux: Version 11.2.0.3.0 – Production
                      \nNLSRTL Version 11.2.0.3.0 – Production<\/p><\/div>\n

                      <\/div>\n
                      — 2.\u51c6\u5907\u6d4b\u8bd5\u6570\u636e,\u5728\u6e90\u7aef\u548c\u76ee\u6807\u7aef\u5206\u522b\u521b\u5efa\u6d4b\u8bd5\u8868,\u7ed3\u679c\u90fd\u662f\u8d70\u5168\u8868\u626b\u63cf;<\/div>\n
                      SQL> conn \/ as sysdba
                      \nSQL> ALTER USER hr IDENTIFIED BY oracle ACCOUNT UNLOCK;
                      \nSQL> conn hr\/oracle
                      \nSQL> CREATE TABLE emp AS SELECT * FROM employees;
                      \nSQL> ALTER TABLE emp ADD CONSTRAINT PK_EMP_ID PRIMARY KEY (EMPLOYEE_ID) USING INDEX;
                      \nSQL> SET AUTOT ON
                      \nSQL> SELECT \/*+ NO_INDEX(e pk_emp_id) *\/ employee_id, first_name FROM emp e WHERE employee_id=198;<\/p>\n

                      EMPLOYEE_ID FIRST_NAME
                      \n———– ——————–
                      \n198 Donald<\/p>\n

                      Execution Plan
                      \n———————————————————-
                      \nPlan hash value: 3956160932<\/p>\n

                      ————————————————————————–
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |
                      \n————————————————————————–
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0 100 |\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0 (0)| 00:00:01 |
                      \n|*\u00a0 1 |\u00a0 TABLE ACCESS FULL| EMP\u00a0 |\u00a0\u00a0\u00a0\u00a0 4 |\u00a0\u00a0 100 |\u00a0\u00a0\u00a0\u00a0 3\u00a0\u00a0 (0)| 00:00:01 |
                      \n————————————————————————–<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      1 – filter(“EMPLOYEE_ID”=198)<\/p>\n

                      Statistics
                      \n———————————————————-
                      \n0\u00a0 recursive calls
                      \n0\u00a0 db block gets
                      \n5\u00a0 consistent gets
                      \n0 \u00a0physical reads
                      \n0\u00a0 redo size
                      \n482\u00a0 bytes sent via SQL*Net to client
                      \n385\u00a0 bytes received via SQL*Net from client
                      \n2\u00a0 SQL*Net roundtrips to\/from client
                      \n0\u00a0 sorts (memory)
                      \n0\u00a0 sorts (disk)
                      \n1\u00a0 rows processed<\/p><\/div>\n

                      <\/div>\n
                      — 3.\u5728\u6e90\u7aef\u4f7f\u7528STA\u8fdb\u884cSQL\u8c03\u4f18,\u7136\u540e\u5e76\u4f7f\u7528\u7ed9\u51fa\u7684\u5efa\u8bae\u751f\u6210\u4e00\u4e2aSQL Profile;<\/div>\n
                      DECLARE
                      \nmy_sta_name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2(30);
                      \nmy_sqltext\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CLOB;
                      \nBEGIN
                      \nmy_sqltext\u00a0 := ‘SELECT \/*+ NO_INDEX(e pk_emp_id) *\/ employee_id, first_name FROM emp e WHERE employee_id=198’;
                      \nmy_sta_name := dbms_sqltune.create_tuning_task(sql_text => my_sqltext,
                      \nuser_name => ‘HR’,
                      \nscope => ‘COMPREHENSIVE’,
                      \ntime_limit => 60,
                      \ntask_name => ‘my_sql_tuning_task’);
                      \ndbms_sqltune.execute_tuning_task(task_name => my_sta_name);
                      \ndbms_sqltune.accept_sql_profile(task_name => my_sta_name, NAME => ‘my_sql_profile’);
                      \nEND;
                      \n\/<\/div>\n
                      <\/div>\n
                      — 4.\u5728\u6e90\u7aef\u67e5\u770b\u76f8\u5e94\u7684\u6539\u53d8;<\/div>\n
                      1.\u67e5\u770b\u751f\u6210\u7684profile\u4fe1\u606f;<\/div>\n
                      SELECT * FROM dba_sql_profiles WHERE name = ‘my_sql_profile’;<\/div>\n
                      2.\u67e5\u770bsql\u7684\u6267\u884c\u8ba1\u5212\u6539\u53d8;<\/div>\n
                      SQL> SELECT \/*+ NO_INDEX(e pk_emp_id) *\/ employee_id, first_name FROM emp e WHERE employee_id=198;<\/p>\n

                      EMPLOYEE_ID FIRST_NAME
                      \n———– ——————–
                      \n198 Donald<\/p>\n

                      Execution Plan
                      \n———————————————————-
                      \nPlan hash value: 2864363232<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      | Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)| T
                      \nime\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      |\u00a0\u00a0 0 | SELECT STATEMENT\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 1 |\u00a0\u00a0\u00a0\u00a0 9 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      |\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| EMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0 9 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      |*\u00a0 2 |\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PK_EMP_ID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      2 – access(“EMPLOYEE_ID”=198)<\/p>\n

                      Note
                      \n—–
                      \n–\u00a0SQL profile “my_sql_profile” used for this statement<\/span><\/p>\n

                      Statistics
                      \n———————————————————-
                      \n0\u00a0 recursive calls
                      \n0\u00a0 db block gets
                      \n2\u00a0 consistent gets
                      \n0\u00a0 physical reads
                      \n0\u00a0 redo size
                      \n482\u00a0 bytes sent via SQL*Net to client
                      \n385\u00a0 bytes received via SQL*Net from client
                      \n2\u00a0 SQL*Net roundtrips to\/from client
                      \n0\u00a0 sorts (memory)
                      \n0\u00a0 sorts (disk)
                      \n1\u00a0 rows processed<\/p><\/div>\n

                      <\/div>\n
                      — 5.\u5728\u6e90\u7aef\u6253\u5305SQL Profiles\u5e76\u5bfc\u51fa;<\/div>\n
                      1.\u6253\u5305SQL Profiles(\u8868\u540d\u548cschema\u540d\u4e25\u683c\u533a\u5206\u5927\u5c0f\u5199);<\/div>\n
                      BEGIN
                      \ndbms_sqltune.create_stgtab_sqlprof(table_name => ‘TB_SQLP_STAGE’, schema_name => ‘HR’);
                      \ndbms_sqltune.pack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
                      \nstaging_table_name => ‘TB_SQLP_STAGE’,
                      \nstaging_schema_owner => ‘HR’);
                      \nEND;
                      \n\/<\/div>\n
                      2.\u5bfc\u51fa\u5bf9\u5e94\u7684stage table;<\/div>\n
                      [oracle@singleton10g ~]$ expdp hr\/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE;<\/p>\n

                      Export: Release 10.2.0.1.0 – Production on Friday, 25 July, 2014 11:49:29<\/p>\n

                      Copyright (c) 2003, 2005, Oracle.\u00a0 All rights reserved.<\/p>\n

                      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
                      \nWith the Partitioning, OLAP and Data Mining options
                      \nStarting “HR”.”SYS_EXPORT_TABLE_01″:\u00a0 hr\/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
                      \nEstimate in progress using BLOCKS method…
                      \nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA
                      \nTotal estimation using BLOCKS method: 320 KB
                      \nProcessing object type TABLE_EXPORT\/TABLE\/TABLE
                      \n. . exported “HR”.”TB_SQLP_STAGE”\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9.257 KB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 rows
                      \nMaster table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded\/unloaded
                      \n******************************************************************************
                      \nDump file set for HR.SYS_EXPORT_TABLE_01 is:
                      \n\/u01\/app\/oracle\/admin\/ORCL\/dpdump\/sqlp.dmp<\/p><\/div>\n

                      Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:49:33<\/div>\n
                      3.\u628adump\u6587\u4ef6\u653e\u5230\u76ee\u6807\u7aef;<\/div>\n
                      <\/div>\n
                      — 6.\u5728\u76ee\u6807\u7aef\u5bfc\u5165\u5e76\u89e3\u5305SQL Profiles;<\/div>\n
                      1.\u5bfc\u5165\u5bf9\u5e94\u7684stage table;<\/div>\n
                      [oracle@singleton11g ~]$ impdp hr\/oracle directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE<\/p>\n

                      Import: Release 11.2.0.3.0 – Production on Fri Jul 25 11:52:37 2014<\/p>\n

                      Copyright (c) 1982, 2011, Oracle and\/or its affiliates.\u00a0 All rights reserved.<\/p>\n

                      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
                      \nWith the Partitioning, OLAP, Data Mining and Real Application Testing options
                      \nMaster table “HR”.”SYS_IMPORT_TABLE_01″ successfully loaded\/unloaded
                      \nStarting “HR”.”SYS_IMPORT_TABLE_01″:\u00a0 hr\/******** directory=data_pump_dir dumpfile=sqlp.dmp tables=TB_SQLP_STAGE
                      \nProcessing object type TABLE_EXPORT\/TABLE\/TABLE
                      \nProcessing object type TABLE_EXPORT\/TABLE\/TABLE_DATA
                      \n. . imported “HR”.”TB_SQLP_STAGE”\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9.257 KB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 rows
                      \nJob “HR”.”SYS_IMPORT_TABLE_01″ successfully completed at 11:53:05<\/p><\/div>\n

                      2.\u89e3\u5305SQL Profiles(\u5bf9\u5e94\u7684SQL Profile\u88ab\u6dfb\u52a0\u5230\u4e86\u6570\u636e\u5b57\u5178\u4e2d,\u53ef\u4ee5\u901a\u8fc7dba_sql_profiles\u67e5\u770b);<\/div>\n
                      BEGIN
                      \ndbms_sqltune.unpack_stgtab_sqlprof(profile_name => ‘my_sql_profile’,
                      \nREPLACE => TRUE,
                      \nstaging_table_name => ‘TB_SQLP_STAGE’,
                      \nstaging_schema_owner => ‘HR’);
                      \nEND;
                      \n\/<\/div>\n
                      <\/div>\n
                      — 7.\u5728\u76ee\u6807\u7aef\u67e5\u770b\u5bf9\u5e94\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      SQL> SELECT \/*+ NO_INDEX(e pk_emp_id) *\/ employee_id, first_name FROM emp e WHERE employee_id=198;<\/p>\n

                      EMPLOYEE_ID FIRST_NAME
                      \n———– ——————–
                      \n198 Donald<\/p>\n

                      Execution Plan
                      \n———————————————————-
                      \nPlan hash value: 2864363232<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      | Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)| T
                      \nime\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      |\u00a0\u00a0 0 | SELECT STATEMENT\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 1 |\u00a0\u00a0\u00a0\u00a0 9 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      |\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| EMP\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0 9 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      |*\u00a0 2 |\u00a0\u00a0 INDEX UNIQUE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PK_EMP_ID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 0\u00a0\u00a0 (0)| 0
                      \n0:00:01 |<\/p>\n

                      ——————————————————————————–
                      \n———<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      2 – access(“EMPLOYEE_ID”=198)<\/p>\n

                      Note
                      \n—–
                      \n–\u00a0SQL profile “my_sql_profile” used for this statement<\/span><\/p>\n

                      Statistics
                      \n———————————————————-
                      \n0\u00a0 recursive calls
                      \n0\u00a0 db block gets
                      \n5\u00a0 consistent gets
                      \n0\u00a0 physical reads
                      \n0\u00a0 redo size
                      \n497\u00a0 bytes sent via SQL*Net to client
                      \n419\u00a0 bytes received via SQL*Net from client
                      \n2\u00a0 SQL*Net roundtrips to\/from client
                      \n0\u00a0 sorts (memory)
                      \n0\u00a0 sorts (disk)
                      \n1\u00a0 rows processed<\/p><\/div>\n

                      ———————-\u00a0How to Move SQL Profiles from One Database to Another\u00a0———————-<\/div>\n
                      <\/div>\n
                      ———————-\u00a0\u4f7f\u7528DBMS_SQLTUNE.IMPORT_SQL_PROFILE\u8fc7\u7a0b\u624b\u5de5\u521b\u5efaSQL Profiles\u00a0———————-<\/div>\n
                      — 1.\u5b9e\u9a8c\u73af\u5883;<\/div>\n
                      SQL>\u00a0SELECT * FROM v$version;<\/p>\n

                      BANNER
                      \n—————————————————————-
                      \nOracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
                      \nPL\/SQL Release 10.2.0.1.0 – Production
                      \nCORE\u00a0\u00a0\u00a0 10.2.0.1.0\u00a0\u00a0\u00a0\u00a0\u00a0 Production
                      \nTNS for Linux: Version 10.2.0.1.0 – Production
                      \nNLSRTL Version 10.2.0.1.0 – Production<\/p><\/div>\n

                      <\/div>\n
                      — 2.\u76f8\u5173\u4ecb\u7ecd;<\/div>\n
                      1.\u5b58\u50a8\u8fc7\u7a0b:\u5728\u5e2e\u52a9\u6587\u6863\u4e2d\u5e76\u6ca1\u6709\u4ecb\u7ecd,\u4f46\u662f\u53ef\u4ee5\u901a\u8fc7\u67e5\u770b\u5305\u4f53\u7684\u4fe1\u606f;<\/div>\n
                      SQL> desc dbms_sqltune<\/div>\n
                      PROCEDURE IMPORT_SQL_PROFILE
                      \nArgument Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 In\/Out Default?
                      \n—————————— ———————– —— ——–
                      \nSQL_TEXT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CLOB\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN
                      \nPROFILE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQLPROF_ATTR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN
                      \nNAME\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 VARCHAR2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT
                      \nDESCRIPTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT
                      \nCATEGORY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT
                      \nVALIDATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BOOLEAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT
                      \nREPLACE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BOOLEAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT
                      \nFORCE_MATCH\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BOOLEAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IN\u00a0\u00a0\u00a0\u00a0 DEFAULT<\/div>\n
                      <\/div>\n
                      2.SYS.SQLPROF_ATTR\u662f\u4e00\u4e2aVARCHAR2\u7684\u96c6\u5408\u7c7b\u578b;<\/div>\n
                      SELECT * FROM dba_source WHERE owner = ‘SYS’ AND NAME = ‘SQLPROF_ATTR’;<\/div>\n
                      TYPE sqlprof_attr AS VARRAY(2000) of VARCHAR2(500)<\/div>\n
                      <\/div>\n
                      — 3.\u8fd8\u539f\u73af\u5883;<\/div>\n
                      BEGIN
                      \ndbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_0151dc24f6338000’);
                      \nEND;
                      \n\/
                      \nBEGIN
                      \ndbms_stats.set_table_stats(‘SYS’, ‘T1’, numrows => 5000);
                      \nEND;
                      \n\/<\/div>\n
                      <\/div>\n
                      — 4.\u7a33\u5b9a\u73b0\u6709\u7684SQL\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      1.\u67e5\u770bsql\u7684\u4fe1\u606f;<\/div>\n
                      SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql;<\/p>\n

                      SQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_HASH_VALUE
                      \n————- —————
                      \nSQL_TEXT
                      \n——————————————————————————–
                      \ncy6x06rhrt54x\u00a0\u00a0\u00a0\u00a0\u00a0 1838229974
                      \nSELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object
                      \n_id = t2.object_id<\/p><\/div>\n

                      <\/div>\n
                      2.10g\u5f00\u59cb,v$sql_plan\u4e2d\u5305\u62ec\u4e86SQL\u8bed\u53e5\u7684outlines\u6570\u636e,\u5373\u7a33\u5b9a\u6267\u884c\u8ba1\u5212\u7684hints;<\/div>\n
                      SQL>\u00a0SELECT * FROM TABLE(dbms_xplan.display_cursor(‘cy6x06rhrt54x’,NULL,’outline’));<\/span><\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \nSQL_ID\u00a0 cy6x06rhrt54x, child number 0
                      \n————————————-
                      \nSELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND
                      \nt1.object_id = t2.object_id<\/p>\n

                      Plan hash value: 1838229974<\/p>\n

                      —————————————————————————
                      \n| Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name | Rows\u00a0 | Bytes | Cost (%CPU)| Time\u00a0\u00a0\u00a0\u00a0 |
                      \n—————————————————————————
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 212 (100)|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \n|*\u00a0 1 |\u00a0 HASH JOIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 2500 |\u00a0\u00a0\u00a0 97K|\u00a0\u00a0 212\u00a0\u00a0 (3)| 00:00:03 |
                      \n|*\u00a0 2 |\u00a0\u00a0 TABLE ACCESS FULL| T1\u00a0\u00a0 |\u00a0 2500 | 72500 |\u00a0\u00a0\u00a0 55\u00a0\u00a0 (4)| 00:00:01 |
                      \n|\u00a0\u00a0 3 |\u00a0\u00a0 TABLE ACCESS FULL| T2\u00a0\u00a0 | 50350 |\u00a0\u00a0 540K|\u00a0\u00a0 155\u00a0\u00a0 (2)| 00:00:02 |
                      \n—————————————————————————<\/p>\n

                      Outline Data
                      \n————-<\/p>\n

                      \/*+
                      \nBEGIN_OUTLINE_DATA
                      \nIGNORE_OPTIM_EMBEDDED_HINTS
                      \nOPTIMIZER_FEATURES_ENABLE(‘10.2.0.1’)
                      \nALL_ROWS
                      \nOUTLINE_LEAF(@”SEL$1″)
                      \nFULL(@”SEL$1″ “T1″@”SEL$1″)
                      \nFULL(@”SEL$1” “T2″@”SEL$1″)
                      \nLEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)
                      \nUSE_HASH(@”SEL$1” “T2″@”SEL$1”)
                      \nEND_OUTLINE_DATA
                      \n*\/<\/span><\/p>\n

                      Predicate Information (identified by operation id):<\/p>\n

                      1 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)
                      \n2 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)<\/p><\/div>\n

                      <\/div>\n
                      3.SQLT\u5de5\u5177\u4e2d\u7684coe_xfr_sql_profile.sql\u811a\u672c:\u8fd9\u4e2a\u811a\u672c\u53ef\u4ee5\u7528\u4e8e\u4eceshared pool,awr\u4e2d\u63d0\u53d6\u6307\u5b9a\u7684SQL ID\u7684Outline Data\u5e76\u521b\u5efaSQL Profile;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u811a\u672c\u9700\u8981\u8f93\u5165sql\u7684sql_id\u548cplan_hash_value,\u7136\u540e\u4f1a\u751f\u6210\u4e00\u4e2acoe_xfr_sql_profile_${sql_id}_${plan_hash_value}.sql\u7684\u811a\u672c;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u811a\u672c\u4e2d\u7684force_match\u53c2\u6570\u9ed8\u8ba4\u662fFALSE,\u53ef\u4ee5\u4fee\u6539\u4e3aTRUE,\u4e5f\u53ef\u4ee5\u4fee\u6539\u811a\u672c\u5176\u5b83\u7684\u5185\u5bb9;<\/div>\n
                      <\/div>\n
                      SQL>@\/home\/oracle\/coe_xfr_sql_profile.sql cy6x06rhrt54x 1838229974<\/span><\/p>\n

                      Parameter 1:
                      \nSQL_ID (required)<\/p>\n

                      PLAN_HASH_VALUE AVG_ET_SECS
                      \n————— ———–
                      \n1838229974\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .087
                      \n1164754026<\/p>\n

                      Parameter 2:
                      \nPLAN_HASH_VALUE (required)<\/p>\n

                      Values passed to coe_xfr_sql_profile:
                      \n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                      \nSQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : “cy6x06rhrt54x”
                      \nPLAN_HASH_VALUE: “1838229974”<\/p>\n

                      SQL>BEGIN
                      \n2\u00a0\u00a0\u00a0 IF :sql_text IS NULL THEN
                      \n3\u00a0\u00a0\u00a0\u00a0\u00a0 RAISE_APPLICATION_ERROR(-20100, ‘SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).’);
                      \n4\u00a0\u00a0\u00a0 END IF;
                      \n5\u00a0 END;
                      \n6\u00a0 \/
                      \nSQL>SET TERM OFF;
                      \nSQL>BEGIN
                      \n2\u00a0\u00a0\u00a0 IF :other_xml IS NULL THEN
                      \n3\u00a0\u00a0\u00a0\u00a0\u00a0 RAISE_APPLICATION_ERROR(-20101, ‘PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).’);
                      \n4\u00a0\u00a0\u00a0 END IF;
                      \n5\u00a0 END;
                      \n6\u00a0 \/
                      \nSQL>SET TERM OFF;<\/p>\n

                      Execute coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql
                      \non TARGET system in order to create a custom SQL Profile
                      \nwith plan 1838229974 linked to adjusted sql_text.<\/p>\n

                      COE_XFR_SQL_PROFILE completed.<\/p><\/div>\n

                      <\/div>\n
                      SQL>@coe_xfr_sql_profile_cy6x06rhrt54x_1838229974.sql<\/span><\/div>\n
                      SQL>DECLARE
                      \n2\u00a0 sql_txt CLOB;
                      \n3\u00a0 h\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SYS.SQLPROF_ATTR;
                      \n4\u00a0 PROCEDURE wa (p_line IN VARCHAR2) IS
                      \n5\u00a0 BEGIN
                      \n6\u00a0 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
                      \n7\u00a0 END wa;
                      \n8\u00a0 BEGIN
                      \n9\u00a0 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
                      \n10\u00a0 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
                      \n11\u00a0 — SQL Text pieces below do not have to be of same length.
                      \n12\u00a0 — So if you edit SQL Text (i.e. removing temporary Hints),
                      \n13\u00a0 — there is no need to edit or re-align unmodified pieces.
                      \n14\u00a0 wa(q'[SELECT t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1]’);
                      \n15\u00a0 wa(q'[%’ AND t1.object_id = t2.object_id
                      \n16\u00a0\u00a0 ]’);
                      \n17\u00a0 DBMS_LOB.CLOSE(sql_txt);
                      \n18\u00a0 h := SYS.SQLPROF_ATTR(
                      \n19\u00a0 q'[BEGIN_OUTLINE_DATA]’,
                      \n20\u00a0 q'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
                      \n21\u00a0 q'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
                      \n22\u00a0 q'[ALL_ROWS]’,
                      \n23\u00a0 q'[OUTLINE_LEAF(@”SEL$1″)]’,
                      \n24\u00a0 q'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
                      \n25\u00a0 q'[FULL(@”SEL$1” “T2″@”SEL$1″)]’,
                      \n26\u00a0 q'[LEADING(@”SEL$1” “T1″@”SEL$1” “T2″@”SEL$1″)]’,
                      \n27\u00a0 q'[USE_HASH(@”SEL$1” “T2″@”SEL$1″)]’,
                      \n28\u00a0 q'[END_OUTLINE_DATA]’);
                      \n29\u00a0 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
                      \n30\u00a0 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
                      \n31\u00a0 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
                      \n32\u00a0 sql_text\u00a0\u00a0\u00a0 => sql_txt,
                      \n33\u00a0 profile\u00a0\u00a0\u00a0\u00a0 => h,
                      \n34\u00a0 name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 => ‘coe_cy6x06rhrt54x_1838229974’,
                      \n35\u00a0 description => ‘coe cy6x06rhrt54x 1838229974 ‘||:signature||’ ‘||:signaturef||”,
                      \n36\u00a0 category\u00a0\u00a0\u00a0 => ‘DEFAULT’,
                      \n37\u00a0 validate\u00a0\u00a0\u00a0 => TRUE,
                      \n38\u00a0 replace\u00a0\u00a0\u00a0\u00a0 => TRUE,
                      \n39\u00a0\u00a0force_match => FALSE<\/span>\u00a0\/* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) *\/ );
                      \n40\u00a0 DBMS_LOB.FREETEMPORARY(sql_txt);
                      \n41\u00a0 END;
                      \n42\u00a0 \/<\/p>\n

                      PL\/SQL procedure successfully completed.<\/p>\n

                      SQL>WHENEVER SQLERROR CONTINUE
                      \nSQL>SET ECHO OFF;<\/p>\n

                      SIGNATURE
                      \n———————
                      \n8975541025552400288<\/p>\n

                      SIGNATUREF
                      \n———————
                      \n3960696072677096522<\/p>\n

                      … manual custom SQL Profile has been created<\/p>\n

                      COE_XFR_SQL_PROFILE_cy6x06rhrt54x_1838229974 completed<\/p><\/div>\n

                      <\/div>\n
                      — 5.\u5728\u4e0d\u80fd\u4fee\u6539SQL\u7684\u60c5\u51b5\u4e0b\u6539\u53d8\u5e76\u56fa\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212,\u5373\u4f7f\u539f\u6765\u7684SQL\u4f7f\u7528\u4e86hints;<\/div>\n
                      1.\u4e00\u822c\u9047\u5230sql\u4e0d\u80fd\u4fee\u6539,\u6216\u8005sql\u4f7f\u7528\u4e86\u9519\u8befhints\u7684\u60c5\u51b5\u4e0b\u6709\u4e09\u79cd\u529e\u6cd5\u4fee\u6539sql\u7684\u6267\u884c\u8ba1\u5212;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u8c03\u6574\u7edf\u8ba1\u4fe1\u606f:\u4e0d\u53ef\u9760,\u4f7f\u7528\u6bd4\u8f83\u590d\u6742,\u800c\u4e14\u7edf\u8ba1\u4fe1\u606f\u4f1a\u81ea\u52a8\u6536\u96c6;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u4f7f\u7528outline:\u6bd4\u8f83\u590d\u6742;<\/div>\n
                      \u00a0 \u00a0 \u00a03.\u4f7f\u7528SQL Profiles:\u672c\u8d28\u548cOUTLINE\u662f\u4e00\u6837\u7684;<\/div>\n
                      2.\u4e00\u822c\u7684\u5de5\u4f5c\u6d41;<\/div>\n
                      \u00a0 \u00a0 \u00a01.\u53d6\u5f97\u539f\u59cb\u7684SQL\u6587\u672c,\u5305\u62ecsql_id;<\/div>\n
                      \u00a0 \u00a0 \u00a02.\u6784\u9020\u4e00\u4e2a\u4e0e\u539f\u59cbSQL\u5728\u903b\u8f91\u4e0a,\u7ed3\u6784\u4e0a\u5b8c\u5168\u76f8\u540c\u7684SQL(\u903b\u8f91\u548c\u7ed3\u6784\u76f8\u540c\u5373\u53ef,\u5373sql\u89e3\u6790\u7684\u7528\u6237,\u5f15\u7528\u5bf9\u8c61\u7684schema,\u751a\u81f3\u662f\u8c13\u8bcd\u6761\u4ef6\u53ef\u4ee5\u4e0d\u540c);<\/div>\n
                      \u00a0 \u00a0 \u00a03.\u6267\u884c\u6211\u4eec\u6784\u9020\u7684SQL,\u751f\u6210Outline Data;<\/div>\n
                      \u00a0 \u00a0 \u00a04.\u4f7f\u7528\u539f\u59cbSQL\u7684\u6587\u672c\u548c\u6784\u9020\u7684SQL\u7684Outline Data\u521b\u5efaSQL Profile;<\/div>\n
                      3.\u6784\u5efaSQL;<\/div>\n
                      \u00a0 \u00a0 \u00a01.FULL TABLE SCAN + HASH JOIN\u7684\u65b9\u5f0f:SELECT \/*+ sql_profile full(t1) full(t2) use_hash(t1 t2) *\/ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;
                      \n2.INDEX RANGE SCAN + NESTED LOOP\u7684\u65b9\u5f0f:SELECT \/*+ sql_profile index(t1) use_nl(t1 t2) *\/\u00a0 t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id;<\/div>\n
                      \u00a0 \u00a0 \u00a03.\u67e5\u770bsql_id\u548cplan_hash_value;<\/div>\n
                      SQL>SELECT sql_id, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE ‘%sql_profile%’ AND sql_id IN (‘8cukn1u7dxssh’,’6csfwp8k7qu49′)
                      \n2\u00a0 ;<\/p>\n

                      SQL_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PLAN_HASH_VALUE
                      \n————- —————
                      \nSQL_TEXT
                      \n——————————————————————————–
                      \n8cukn1u7dxssh\u00a0\u00a0\u00a0\u00a0\u00a0 1838229974
                      \nSELECT \/*+ sql_profile full(t1) full(t2) use_hash(t1 t2) *\/ t1.*, t2.owner FROM
                      \nt1, t2 WHERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id<\/p>\n

                      6csfwp8k7qu49\u00a0\u00a0\u00a0\u00a0\u00a0 1164754026
                      \nSELECT \/*+ sql_profile index(t1) use_nl(t1 t2) *\/\u00a0 t1.*, t2.owner FROM t1, t2 WH
                      \nERE t1.object_name LIKE ‘%T1%’ AND t1.object_id = t2.object_id<\/p><\/div>\n

                      <\/div>\n
                      4.\u5206\u522b\u4f7f\u7528coe_xfr_sql_profile.sql\u811a\u672c\u751f\u4ea7\u4e24\u4e2asql\u7684sql_profile\u521b\u5efa\u811a\u672c;<\/div>\n
                      SQL>@\/home\/oracle\/coe_xfr_sql_profile.sql 8cukn1u7dxssh 1838229974 — coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql
                      \nSQL>@\/home\/oracle\/coe_xfr_sql_profile.sql 6csfwp8k7qu49 1164754026 — coe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql<\/div>\n
                      <\/div>\n
                      5.\u7136\u540e\u628acoe_xfr_sql_profile_6csfwp8k7qu49_1164754026.sql\u4e2d\u6784\u5efaSYS.SQLPROF_ATTR\u7684\u90e8\u5206\u548cforce_match => TRUE\u5730\u65b9\u66ff\u6362\u6389coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql\u811a\u672c\u4e2d\u76f8\u5e94\u5185\u5bb9;<\/div>\n
                      h := SYS.SQLPROF_ATTR(
                      \nq'[BEGIN_OUTLINE_DATA]’,
                      \nq'[IGNORE_OPTIM_EMBEDDED_HINTS]’,
                      \nq'[OPTIMIZER_FEATURES_ENABLE(‘10.2.0.1′)]’,
                      \nq'[ALL_ROWS]’,
                      \nq'[OUTLINE_LEAF(@”SEL$1″)]’,
                      \nq'[FULL(@”SEL$1″ “T1″@”SEL$1″)]’,
                      \nq'[INDEX(@”SEL$1” “T2″@”SEL$1” (“T2″.”OBJECT_ID”))]’,
                      \nq'[LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1″)]’,
                      \nq'[USE_NL(@”SEL$1” “T2″@”SEL$1”)]’,
                      \nq'[END_OUTLINE_DATA]’);<\/div>\n
                      <\/div>\n
                      6.\u7136\u540e\u6267\u884c,\u5e76\u67e5\u770b\u6267\u884c\u8ba1\u5212;<\/div>\n
                      SQL>@\/home\/oracle\/coe_xfr_sql_profile_8cukn1u7dxssh_1838229974.sql<\/a><\/div>\n
                      <\/div>\n
                      SQL>EXPLAIN PLAN FOR SELECT \/*+ sql_profile full(t1) full(t2) use_hash(t1 t2) *\/ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE\u00a0‘%T1%’<\/span>\u00a0AND t1.object_id = t2.object_id;<\/p>\n

                      Explained.<\/p>\n

                      SQL>SELECT * FROM TABLE(dbms_xplan.display());<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \nPlan hash value: 1164754026<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      | Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)|
                      \nTime\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 250 | 10000 |\u00a0\u00a0 554\u00a0\u00a0 (1)|
                      \n00:00:07 |<\/p>\n

                      |\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 11 |\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      |\u00a0\u00a0 2 |\u00a0\u00a0 NESTED LOOPS\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 250 | 10000 |\u00a0\u00a0 554\u00a0\u00a0 (1)|
                      \n00:00:07 |<\/p>\n

                      |*\u00a0 3 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 250 |\u00a0 7250 |\u00a0\u00a0\u00a0 53\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–<\/p>\n

                      |*\u00a0 4 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | IDX_T2_OID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \n3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T1%’)
                      \n4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)<\/p>\n

                      Note
                      \n—–
                      \n– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement<\/p><\/div>\n

                      <\/div>\n
                      — 7.\u6539\u53d8\u6761\u4ef6,\u518d\u6b21\u67e5\u770b\u6267\u884c\u8ba1\u5212,\u53d1\u73b0SQL\u7684\u6267\u884c\u8ba1\u5212\u88ab\u56fa\u5b9a\u4e86;<\/div>\n
                      SQL>EXPLAIN PLAN FOR SELECT \/*+ sql_profile full(t1) full(t2) use_hash(t1 t2) *\/ t1.*, t2.owner FROM t1, t2 WHERE t1.object_name LIKE ‘%T2%<\/span>‘ AND t1.object_id = t2.object_id;<\/p>\n

                      Explained.<\/p>\n

                      SQL>SELECT * FROM TABLE(dbms_xplan.display());<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \nPlan hash value: 1164754026<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      | Id\u00a0 | Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Rows\u00a0 | Bytes | Cost (%CPU)|
                      \nTime\u00a0\u00a0\u00a0\u00a0 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \n|\u00a0\u00a0 0 | SELECT STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 250 | 10000 |\u00a0\u00a0 554\u00a0\u00a0 (1)|
                      \n00:00:07 |<\/p>\n

                      |\u00a0\u00a0 1 |\u00a0 TABLE ACCESS BY INDEX ROWID| T2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0 11 |\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      |\u00a0\u00a0 2 |\u00a0\u00a0 NESTED LOOPS\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 250 | 10000 |\u00a0\u00a0 554\u00a0\u00a0 (1)|
                      \n00:00:07 |<\/p>\n

                      |*\u00a0 3 |\u00a0\u00a0\u00a0 TABLE ACCESS FULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | T1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 250 |\u00a0 7250 |\u00a0\u00a0\u00a0 53\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–<\/p>\n

                      |*\u00a0 4 |\u00a0\u00a0\u00a0 INDEX RANGE SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | IDX_T2_OID |\u00a0\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 (0)|
                      \n00:00:01 |<\/p>\n

                      ——————————————————————————–
                      \n———-<\/p>\n

                      Predicate Information (identified by operation id):
                      \n—————————————————<\/p>\n

                      PLAN_TABLE_OUTPUT
                      \n——————————————————————————–
                      \n3 – filter(“T1″.”OBJECT_NAME” LIKE ‘%T2%’)
                      \n4 – access(“T1″.”OBJECT_ID”=”T2″.”OBJECT_ID”)<\/p>\n

                      Note
                      \n—–
                      \n– SQL profile “coe_8cukn1u7dxssh_1838229974” used for this statement<\/p>\n<\/div>\n

                      ———————-\u00a0\u4f7f\u7528DBMS_SQLTUNE.IMPORT_SQL_PROFILE\u8fc7\u7a0b\u624b\u5de5\u521b\u5efaSQL Profiles\u00a0———————-<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                      SQL Profiles SQL Profiles\u4ecb\u7ecd: SQL Profiles\u662f\u572810g\u4e2d\u88ab\u5f15\u8fdb\u7684\u65b0\u7279\u6027, […]<\/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-433","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\/433","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=433"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/433\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=433"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}