{"id":1750,"date":"2024-05-09T15:51:29","date_gmt":"2024-05-09T07:51:29","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=1750"},"modified":"2024-05-09T15:51:29","modified_gmt":"2024-05-09T07:51:29","slug":"%e8%bd%ac%e7%94%9f%e6%88%90%e3%80%81%e6%9f%a5%e7%9c%8b%e3%80%81%e7%90%86%e8%a7%a3oracle%e7%9a%84%e6%89%a7%e8%a1%8c%e8%ae%a1%e5%88%92","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=1750","title":{"rendered":"\u8f6c:\u751f\u6210\u3001\u67e5\u770b\u3001\u7406\u89e3Oracle\u7684\u6267\u884c\u8ba1\u5212"},"content":{"rendered":"<div class=\"RichText ztext Post-RichText css-jflero\" options=\"[object Object]\">\n<h2 data-first-child=\"\">SQL\u662f\u5982\u4f55\u88ab\u6267\u884c\u7684<\/h2>\n<p data-pid=\"T8KsJHnc\">SQL\u8bed\u53e5\u53ea\u662f\u5b9a\u4e49\u4e86\u8981\u67e5\u8be2\u4ec0\u4e48\u6837\u7684\u6570\u636e\uff0c\u4f46\u662f\u6ca1\u6709\u5b9a\u4e49\u6570\u636e\u662f\u600e\u6837\u88ab\u67e5\u8be2\u51fa\u6765\u3002<\/p>\n<ol>\n<li data-pid=\"u88N-HTN\">\u5f53DB\u63a5\u6536\u5230\u4e00\u6761SQL\u67e5\u8be2\u8bed\u53e5(INSERT, UPDATE, SELECT, DELETE)\u65f6\uff0cDB\u8bed\u6cd5\u89e3\u6790\u5668(Parser)\u4f1a\u5148\u5c06\u5176\u89e3\u6790\u6210\u4e00\u68f5\u62bd\u8c61\u8bed\u6cd5\u6811(Abstract Syntax Tree, AST)\uff0c\u89e3\u6790\u5668\u5728\u4e0d\u5f71\u54cd\u6267\u884c\u7ed3\u679c\u7684\u524d\u63d0\u4e0b\u53ef\u80fd\u4f1a\u91cd\u5199\u8fd9\u4e2aSQL\u6765\u4f18\u5316\u6267\u884c\u3002<\/li>\n<li data-pid=\"FM2_7YGm\">\u63a5\u4e0b\u6765\u8fd9\u68f5\u751f\u6210\u7684\u8bed\u6cd5\u6811\u4f1a\u88ab\u9001\u5230DB\u7684\u57fa\u4e8e\u5f00\u9500\u7684\u4f18\u5316\u5668(Cost-Based Optimizer, CBO)\uff0c\u4f18\u5316\u5668\u4f1a\u521b\u5efa\u6267\u884c\u8ba1\u5212(Execution Plan)\uff0c\u5e76\u901a\u8fc7\u9009\u62e9join\u7c7b\u578b(Nested Loops, Merge Join, Hash Join\u7b49)\u3001join\u987a\u5e8f\u3001\u662f\u5426\u4f7f\u7528\u7d22\u5f15\u3001\u4f7f\u7528\u4ec0\u4e48\u7d22\u5f15\u7b49\u65b9\u5f0f\u627e\u5230\u4f7f\u7528\u5f00\u9500(CPU, IO, \u5185\u5b58)\u6700\u5c0f\u7684\u8ba1\u5212\u4f5c\u4e3a\u6700\u4f73\u7684\u6267\u884c\u8ba1\u5212\u3002<\/li>\n<li data-pid=\"LmD07Vro\">\u7136\u540e\u8fd9\u4e2a\u6700\u4f73\u7684\u6267\u884c\u8ba1\u5212\u88ab\u9001\u5230DB\u7684\u6267\u884c\u5668(Executor)\uff0c\u6267\u884c\u5668\u6267\u884c\u8fd9\u4e2a\u8ba1\u5212\uff0c\u5e76\u5c06\u53d6\u5230\u7684\u6570\u636e\u8fd4\u56de\u7ed9DB\u8c03\u7528\u65b9\u3002<\/li>\n<\/ol>\n<p class=\"ztext-empty-paragraph\"><\/p>\n<figure data-size=\"normal\"><noscript><img decoding=\"async\" src=\"https:\/\/pic4.zhimg.com\/v2-dac698edca8c802a8a503af01aad6143_b.jpg\" data-caption=\"\" data-size=\"normal\" data-rawwidth=\"2181\" data-rawheight=\"1633\" class=\"origin_image zh-lightbox-thumb\" width=\"2181\" data-original=\"https:\/\/pic4.zhimg.com\/v2-dac698edca8c802a8a503af01aad6143_r.jpg\"\/><\/noscript><\/p>\n<div><img fetchpriority=\"high\" decoding=\"async\" src=\"https:\/\/pic4.zhimg.com\/80\/v2-dac698edca8c802a8a503af01aad6143_720w.webp\" data-caption=\"\" data-size=\"normal\" data-rawwidth=\"2181\" data-rawheight=\"1633\" class=\"origin_image zh-lightbox-thumb lazy\" width=\"2181\" data-original=\"https:\/\/pic4.zhimg.com\/v2-dac698edca8c802a8a503af01aad6143_r.jpg\" data-actualsrc=\"https:\/\/pic4.zhimg.com\/v2-dac698edca8c802a8a503af01aad6143_b.jpg\" data-original-token=\"v2-dd27f63d9b48f270100e754deec4c156\" height=\"1633\" data-lazy-status=\"ok\"><\/div>\n<\/figure>\n<p class=\"ztext-empty-paragraph\"><\/p>\n<h2>\u4ec0\u4e48\u662f\u6267\u884c\u8ba1\u5212<\/h2>\n<blockquote data-pid=\"m_mNYOyR\"><p>A statement's execution plan is the sequence of operations Oracle performs to run the statement. The <code>EXPLAIN<\/code> <code>PLAN<\/code> statement displays execution plans chosen by the Oracle optimizer for <code>SELECT<\/code>, <code>UPDATE<\/code>, <code>INSERT<\/code>, and <code>DELETE<\/code> statements.<\/p><\/blockquote>\n<p data-pid=\"iMPK2f4t\">\u6267\u884c\u8ba1\u5212\u662fDB\u4f18\u5316\u5668\u751f\u6210\u7684\uff0c\u4e3a\u4e86\u5b8c\u6210\u4e00\u4e2aSQL\u67e5\u8be2\u7684\u4e00\u7cfb\u5217\u5fc5\u8981\u4e14\u6709\u5e8f\u7684\u6570\u636e\u5e93\u64cd\u4f5c(Operation)\u7684\u96c6\u5408\u3002<\/p>\n<h2>\u751f\u6210\u6267\u884c\u8ba1\u5212<\/h2>\n<p data-pid=\"ye6RFzKF\"><b>\u9884\u4f30\u6267\u884c\u8ba1\u5212<\/b>\uff1a\u6bcf\u6267\u884c\u4e00\u6b21EXPLAIN PLAN\uff0cDB\u4f18\u5316\u5668(Optimizer)\u5c31\u4f1a\u751f\u6210\u6267\u884c\u8ba1\u5212<b><u><i>\u800c\u4e0d\u6267\u884cSQL<\/i><\/u><\/b>(\u56e0\u6b64\u53eb\u201c\u9884\u4f30\u201d)\uff0c\u5e76\u5199\u5165PLAN_TABLE\u8868\u4e2d(Oracle\u9ed8\u8ba4\u4f7f\u7528PLAN_TABLE\u4e34\u65f6\u8868\u6765\u4fdd\u5b58EXPLAIN PLAN\u7684\u7ed3\u679c):<\/p>\n<blockquote data-pid=\"o6nxXVzQ\"><p>The <code>PLAN_TABLE<\/code> is automatically created as a global temporary table to hold the output of an <code>EXPLAIN<\/code> <code>PLAN<\/code> statement for all users. <code>PLAN_TABLE<\/code> is the default sample output table into which the <code>EXPLAIN<\/code> <code>PLAN<\/code> statement inserts rows describing execution plans<\/p><\/blockquote>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">EXPLAIN PLAN\n   SET STATEMENT_ID = 'statement1'  --\u6307\u5b9a\u8be5\u6761\u8bed\u53e5\u7684statement_id\uff0c\u540e\u7eed\u53ef\u7528\u4e8e\u67e5\u8be2PLAN_TABLE\u8868\u4e2d\u7684\u6267\u884c\u8ba1\u5212\n   INTO my_plan_table               --\u53ef\u4ee5\u4e0d\u4f7f\u7528\u9ed8\u8ba4\u7684PLAN_TABLE\u800c\u4f7f\u7528\u81ea\u5b9a\u4e49\u8868\nFOR\nSELECT last_name FROM employees;    --\u8981\u5206\u6790\u7684SQL\u8bed\u53e5\u3002INSERT, UPDATE, SELECT\u548cDELETE\u8bed\u53e5\u3002<\/code><\/pre>\n<\/div>\n<p data-pid=\"sRSnNXUm\"><b>\u5b9e\u9645\u6267\u884c\u8ba1\u5212<\/b>\uff1a\u5728<b><u><i>SQL\u6267\u884c\u7684\u65f6\u5019<\/i><\/u><\/b>\u7531DB\u4f18\u5316\u5668(Optimizer)\u521b\u5efa\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>\n<p data-pid=\"RFyYPDQL\">EXPLAIN PLAN\u7684\u6267\u884c\u7ed3\u679c\u4ee3\u8868\u5728EXPLAIN\u8fd9\u6761SQL\u7684\u65f6\u5019\uff0cDB\u5e94\u8be5\u5982\u4f55\u53bb\u6267\u884c\u8fd9\u6761SQL\u3002\u800c\u5b9e\u9645\u5728\u6267\u884c\u7684\u65f6\u5019\u53ef\u80fd\u7531\u4e8e\u6570\u636e\u6570\u91cf\uff0c\u7ed3\u6784(\u4f8b\u5982\u589e\u52a0\u4e86\u7d22\u5f15\u7b49)\u7b49\u7684\u53d8\u5316\uff0c\u4f7f\u5f97\u5b9e\u9645\u7684\u6267\u884c\u8ba1\u5212\u4e0e\u9884\u4f30\u6267\u884c\u8ba1\u5212\u7565\u6709\u4e0d\u540c\u3002<\/p>\n<h2>\u67e5\u8be2\u6267\u884c\u8ba1\u5212<\/h2>\n<h3>\u9884\u4f30\u6267\u884c\u8ba1\u5212<\/h3>\n<p data-pid=\"6gG9J2wu\"><a href=\"https:\/\/link.zhihu.com\/?target=https%3A\/\/www.oschina.net\/action\/GoToLink%3Furl%3Dhttps%253A%252F%252Fdocs.oracle.com%252Fdatabase%252F121%252FARPLS%252Fd_xplan.htm%2523ARPLS70132\" class=\" wrap external\" target=\"_blank\" rel=\"nofollow noreferrer noopener\" data-za-detail-view-id=\"1043\">DBMS_XPLAN.DISPLAY<\/a>\u662f\u4e00\u4e2aOracle\u81ea\u5e26\u7684\u51fd\u6570(\u70b9\u51fb\u94fe\u63a5\u67e5\u770b\u8be6\u60c5)\uff0c\u5b83\u53ef\u4ee5\u63a5\u53d7\u4e00\u4e9b\u9009\u9879\u7528\u4e8e\u5c55\u793a\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">--\u4e0d\u5e26\u9009\u9879\nSELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());\n\n--\u8fd9\u91cc\u4f7f\u7528\u4e86\u81ea\u5b9a\u4e49\u7684\u8868my_plan_table\u548c\u81ea\u5b9a\u4e49\u7684SQL ID statement1\nSELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('my_plan_table','statement1','TYPICAL'));<\/code><\/pre>\n<\/div>\n<p data-pid=\"DVYGmYmU\">\u6267\u884c\u7ed3\u679c\u5927\u6982\u957f\u8fd9\u6837\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">Plan hash value: 1906736282\n\n---------------------------------------------------------------------------------------------\n| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT      |                     |     1 |    40 |     6   (0)| 00:00:01 |\n|   1 |  NESTED LOOPS         |                     |     1 |    40 |     6   (0)| 00:00:01 |\n|   2 |   MERGE JOIN CARTESIAN|                     |     4 |   128 |     6   (0)| 00:00:01 |\n|*  3 |    TABLE ACCESS FULL  | PRODUCT_INFORMATION |     1 |    28 |     5   (0)| 00:00:01 |\n|   4 |    BUFFER SORT        |                     |   105 |   420 |     1   (0)| 00:00:01 |\n|   5 |     INDEX FULL SCAN   | ORDER_PK            |   105 |   420 |     1   (0)| 00:00:01 |\n|*  6 |   INDEX UNIQUE SCAN   | ORDER_ITEMS_UK      |     1 |     8 |     0   (0)| 00:00:01 |\n---------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   3 - filter(\"MIN_PRICE\"&lt;40 AND \"LIST_PRICE\"&lt;50)\n   6 - access(\"O\".\"ORDER_ID\"=\"ORDER_ID\" AND \"P\".\"PRODUCT_ID\"=\"O\".\"PRODUCT_ID\")\n\n19 rows selected.<\/code><\/pre>\n<\/div>\n<h3>\u5b9e\u9645\u6267\u884c\u8ba1\u5212<\/h3>\n<p data-pid=\"HrHyz_UW\">\u7531\u4e8e\u5b9e\u9645\u6267\u884c\u8ba1\u5212\u8981\u5728\u6267\u884cSQL\u65f6\u624d\u4f1a\u751f\u6210\uff0c\u67e5\u770b\u6267\u884c\u8ba1\u5212\u524d\uff0c\u8981\u5148\u6267\u884cSQL\u3002<a href=\"https:\/\/link.zhihu.com\/?target=https%3A\/\/www.oschina.net\/action\/GoToLink%3Furl%3Dhttps%253A%252F%252Fdocs.oracle.com%252Fdatabase%252F121%252FARPLS%252Fd_xplan.htm%2523ARPLS70136\" class=\" wrap external\" target=\"_blank\" rel=\"nofollow noreferrer noopener\" data-za-detail-view-id=\"1043\">DBMS_XPLAN.DISPLAY_CURSOR<\/a>\u4e5f\u662f\u4e00\u4e2aOracle\u81ea\u5e26\u7684\u51fd\u6570(\u70b9\u51fb\u94fe\u63a5\u67e5\u770b\u8be6\u60c5)\uff0c\u5b83\u53ef\u4ee5\u5c55\u793a\u5df2\u7ecf\u52a0\u8f7d\u5230cache\u4e2d\u7684\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">--\u4e0d\u5e26\u9009\u9879\uff0c\u76f4\u63a5\u67e5\u8be2\u6700\u540e\u4e00\u6761SQL\u7684\u6267\u884c\u8ba1\u5212\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());\n\n--\u6307\u5b9a\u4e86sql_id\u548cchild_number\uff0c\u67e5\u8be2\u6307\u5b9aSQL\u7684\u6267\u884c\u8ba1\u5212\u3002\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));\n\n-----------------------------------------------------------\n\n--1.\u4e3a\u4e86\u533a\u522b\u5176\u4ed6\u7684SQL\uff0c\u9700\u8981\u5728\u6267\u884cSQL\u7684\u65f6\u5019\u52a0\u5165\u7279\u6b8a\u6807\u8bb0\u7684\u6ce8\u91ca\uff0c\u6bd4\u5982\uff1a\nSELECT \/* TOTO *\/ ename, dname FROM dept d join emp e USING (deptno);\n\n--2.\u901a\u8fc7SQL\u6807\u8bb0\uff0c\u67e5\u5230sql_id\u548cchild_number\u3002v$sql\u662f\u4e00\u4e2a\u7cfb\u7edf\u89c6\u56fe\uff0c\u4fdd\u5b58\u6267\u884c\u8fc7\u7684sql\u8bed\u53e5\nSELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%TOTO%';\n\nSQL_ID         CHILD_NUMBER\n----------     -----------------------------\ngwp663cqh5qbf   0\n\n--3.\u518d\u67e5\u8be2\u8be5SQL\u7684\u6267\u884c\u8ba1\u5212\nSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));<\/code><\/pre>\n<\/div>\n<p data-pid=\"zQtMs68_\">\u6267\u884c\u7ed3\u679c\u5927\u6982\u957f\u8fd9\u6837\uff1a <\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0\n--------------------------------------------------------\nSELECT \/* TOTO *\/ ename, dname \nFROM dept d JOIN emp e USING (deptno);\n\n----------------------------------------------------------------------------\n| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------\n|   0 | SELECT STATEMENT    |      |       |       |     7 (100)|          |\n|   1 |  SORT GROUP BY      |      |     4 |    64 |     7  (43)| 00:00:01 |\n|*  2 |   HASH JOIN         |      |    14 |   224 |     6  (34)| 00:00:01 |\n|   3 |    TABLE ACCESS FULL| DEPT |     4 |    44 |     3  (34)| 00:00:01 |\n|   4 |    TABLE ACCESS FULL| EMP  |    14 |    70 |     3  (34)| 00:00:01 |\n----------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n   2 - access(\"E\".\"DEPTNO\"=\"D\".\"DEPTNO\")<\/code><\/pre>\n<\/div>\n<h2>\u7406\u89e3\u6267\u884c\u8ba1\u5212<\/h2>\n<p data-pid=\"SdjAR-6X\">Oracle\u7684\u6267\u884c\u8ba1\u5212\u770b\u8d77\u6765\u662f\u4ee5tab\u7b26\u548c\u884c\u7684\u65b9\u5f0f\u5c55\u793a\u7684\uff0c\u5b9e\u9645\u4e0a\u5b83\u662f\u4e00\u68f5\u6267\u884c\u6811\uff0c\u56e0\u6b64\u5728\u9605\u8bfb\u6267\u884c\u8ba1\u5212\u7684\u65f6\u5019\uff0c\u8981\u4ece\u6811\u7684\u53f6\u5b50\u8282\u70b9\u770b\u8d77\uff0c\u5373<u><i><b>\u4ece\u6267\u884c\u8ba1\u5212\u8868\u91ccOperation\u5217\u7f29\u8fdb\u6700\u591a\u7684\u4e00\u884c\u770b\u8d77<\/b><\/i><\/u>\u3002<\/p>\n<p data-pid=\"Qt08Gteu\">\u5047\u5982\u6709\u4ee5\u4e0bSQL\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">SELECT ename FROM emp e, dept d \nWHERE  e.deptno = d.deptno  \nAND    e.empno=7369;<\/code><\/pre>\n<\/div>\n<p data-pid=\"dNC5dmIg\">\u6267\u884c\u4e4b\u540e\u67e5\u8be2\u5b9e\u9645\u6267\u884c\u8ba1\u5212\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR());<\/code><\/pre>\n<\/div>\n<p data-pid=\"vZB-Uqy5\">\u7ed3\u679c\uff1a<\/p>\n<div class=\"highlight\">\n<pre><code class=\"language-text\">Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0\n------------------------------------------------------------------\nSELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno\nAND e.empno=7369\n\n---------------------------------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      |       |       |            |          |\n|*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |\n|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |\n|   3 |   TABLE ACCESS FULL| DEPT |     4 |    12 |     3  (34)| 00:00:01 |\n---------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n   1 - access(\"E\".\"DEPTNO\"=\"D\".\"DEPTNO\")\n   2 - filter(\"E\".\"EMPNO\"=7369)\n\n21 rows selected.<\/code><\/pre>\n<\/div>\n<p data-pid=\"4G5Cghh1\">\u53ef\u4ee5\u770b\u5230\uff0c\u6267\u884c\u8ba1\u5212\u5305\u542b\u4e09\u90e8\u5206\uff1a\u6267\u884c\u8ba1\u5212\u7684hash\u503c\uff0c\u4ee5\u8868\u683c\u5c55\u793a\u7684\u6267\u884c\u8ba1\u5212\u6811\uff0c\u4ee5\u53caPredicate\u4fe1\u606f\u3002<\/p>\n<p data-pid=\"Omp6lDtm\">\u5bf9\u4e8e\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\uff0cID=2\u548cID=3\u8fd9\u4e24\u884c\u7684\u7f29\u8fdb\u6700\u591a\uff0c\u5e94\u8be5\u4ece\u8fd9\u4e24\u884c\u5f00\u59cb\u770b(\u6ce8\u610f\uff1a\u8868\u683c\u7684ID\u5217\u4e2d\u6709*\u7684\u884c\uff0c\u4ee3\u8868\u8be5\u884c\u5728Predicate Information\u90e8\u5206\u6709\u66f4\u8be6\u7ec6\u4fe1\u606f)\uff1a<\/p>\n<ul>\n<li data-pid=\"vv9KfJ3O\">ID=3\uff0c\u8fd9\u662f\u4e00\u4e2a\u5168\u8868\u626b\u63cfDEPT\u7684\u64cd\u4f5c\uff0c\u67094\u884c\u8bb0\u5f55\u517112\u5b57\u8282\u3002<\/li>\n<li data-pid=\"41PFGzqe\">ID=2\uff0c\u5168\u8868\u626b\u63cfEMP\u8868\uff0c\u67091\u884c\u8bb0\u5f55\u517113\u5b57\u8282\u3002\u5728Predicate Information\u4e2d\u770b\u5230\uff0c\u8fd9\u4e2a\u6b65\u9aa4\u8fd8\u8981\u8fc7\u6ee4\u6389empno=7369\u7684\u4fe1\u606f\uff0c\u800c\u8fd9\u6b63\u662fsql\u4e2d\u7684where\u6761\u4ef6\u3002<\/li>\n<li data-pid=\"VwKtdo1k\">ID=1\uff0c\u4e24\u4e2a\u8868\u8fdb\u884chash join\uff0c\u5f97\u52301\u884c\u8bb0\u5f55\u517116\u5b57\u8282\u3002\u5728Predicate Information\u4e2d\u770b\u5230\uff0c\u8fd9\u4e2a\u6b65\u9aa4join\u7684\u65b9\u5f0f\u662fe.deptno=d.deptno\uff0c\u8fd9\u4e5f\u662fsql\u79cd\u7684where\u6761\u4ef6\u3002<\/li>\n<li data-pid=\"KETIQs89\">ID=0\uff0c\u6267\u884cSELECT\u64cd\u4f5c\uff0c\u83b7\u53d6\u7ed3\u679c\u3002<\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>SQL\u662f\u5982\u4f55\u88ab\u6267\u884c\u7684 SQL\u8bed\u53e5\u53ea\u662f\u5b9a\u4e49\u4e86\u8981\u67e5\u8be2\u4ec0\u4e48\u6837\u7684\u6570\u636e\uff0c\u4f46\u662f\u6ca1\u6709\u5b9a\u4e49\u6570\u636e\u662f\u600e\u6837\u88ab\u67e5\u8be2\u51fa\u6765\u3002 \u5f53DB\u63a5\u6536\u5230\u4e00\u6761SQL\u67e5\u8be2\u8bed\u53e5(INSERT, UPDATE, SELECT, DELETE)\u65f6\uff0cDB\u8bed\u6cd5\u89e3\u6790\u5668(Parser)\u4f1a\u5148\u5c06\u5176\u89e3\u6790\u6210\u4e00\u68f5\u62bd\u8c61\u8bed\u6cd5\u6811(Abstract Syntax Tree, AST)\uff0c\u89e3\u6790\u5668\u5728\u4e0d\u5f71\u54cd\u6267\u884c\u7ed3\u679c\u7684\u524d\u63d0\u4e0b\u53ef\u80fd\u4f1a\u91cd\u5199\u8fd9\u4e2aSQL\u6765\u4f18\u5316\u6267\u884c\u3002 \u63a5\u4e0b\u6765\u8fd9\u68f5\u751f\u6210\u7684\u8bed\u6cd5\u6811\u4f1a\u88ab\u9001\u5230DB\u7684\u57fa\u4e8e\u5f00\u9500\u7684\u4f18\u5316\u5668(Cost-Based Optimizer, CBO)\uff0c\u4f18\u5316\u5668\u4f1a\u521b\u5efa\u6267\u884c\u8ba1\u5212(Execution Plan)\uff0c\u5e76\u901a\u8fc7\u9009\u62e9join\u7c7b\u578b(Nested Loops, Merge Join, Hash Join\u7b49)\u3001join\u987a\u5e8f\u3001\u662f\u5426\u4f7f\u7528\u7d22\u5f15\u3001\u4f7f\u7528\u4ec0\u4e48\u7d22\u5f15\u7b49\u65b9\u5f0f\u627e\u5230\u4f7f\u7528\u5f00\u9500(CPU, IO, \u5185\u5b58)\u6700\u5c0f\u7684\u8ba1\u5212\u4f5c\u4e3a\u6700\u4f73\u7684\u6267\u884c\u8ba1\u5212\u3002 \u7136\u540e\u8fd9\u4e2a\u6700\u4f73\u7684\u6267\u884c\u8ba1\u5212\u88ab\u9001\u5230DB\u7684\u6267\u884c\u5668(Executor)\uff0c\u6267\u884c\u5668\u6267\u884c\u8fd9\u4e2a\u8ba1\u5212\uff0c\u5e76\u5c06\u53d6\u5230\u7684\u6570\u636e\u8fd4\u56de\u7ed9DB\u8c03\u7528\u65b9\u3002 \u4ec0\u4e48\u662f\u6267\u884c\u8ba1\u5212 A statement&#8217;s execution plan is the sequence of operations Oracle performs to run the statement. The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. \u6267\u884c\u8ba1\u5212\u662fDB\u4f18\u5316\u5668\u751f\u6210\u7684\uff0c\u4e3a\u4e86\u5b8c\u6210\u4e00\u4e2aSQL\u67e5\u8be2\u7684\u4e00\u7cfb\u5217\u5fc5\u8981\u4e14\u6709\u5e8f\u7684\u6570\u636e\u5e93\u64cd\u4f5c(Operation)\u7684\u96c6\u5408\u3002 \u751f\u6210\u6267\u884c\u8ba1\u5212 \u9884\u4f30\u6267\u884c\u8ba1\u5212\uff1a\u6bcf\u6267\u884c\u4e00\u6b21EXPLAIN PLAN\uff0cDB\u4f18\u5316\u5668(Optimizer)\u5c31\u4f1a\u751f\u6210\u6267\u884c\u8ba1\u5212\u800c\u4e0d\u6267\u884cSQL(\u56e0\u6b64\u53eb\u201c\u9884\u4f30\u201d)\uff0c\u5e76\u5199\u5165PLAN_TABLE\u8868\u4e2d(Oracle\u9ed8\u8ba4\u4f7f\u7528PLAN_TABLE\u4e34\u65f6\u8868\u6765\u4fdd\u5b58EXPLAIN PLAN\u7684\u7ed3\u679c): The PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN PLAN statement for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans EXPLAIN PLAN SET STATEMENT_ID = &#8216;statement1&#8217; &#8211;\u6307\u5b9a\u8be5\u6761\u8bed\u53e5\u7684statement_id\uff0c\u540e\u7eed\u53ef\u7528\u4e8e\u67e5\u8be2PLAN_TABLE\u8868\u4e2d\u7684\u6267\u884c\u8ba1\u5212 INTO my_plan_table &#8211;\u53ef\u4ee5\u4e0d\u4f7f\u7528\u9ed8\u8ba4\u7684PLAN_TABLE\u800c\u4f7f\u7528\u81ea\u5b9a\u4e49\u8868 FOR SELECT last_name FROM employees; &#8211;\u8981\u5206\u6790\u7684SQL\u8bed\u53e5\u3002INSERT, UPDATE, SELECT\u548cDELETE\u8bed\u53e5\u3002 \u5b9e\u9645\u6267\u884c\u8ba1\u5212\uff1a\u5728SQL\u6267\u884c\u7684\u65f6\u5019\u7531DB\u4f18\u5316\u5668(Optimizer)\u521b\u5efa\u7684\u6267\u884c\u8ba1\u5212\u3002 EXPLAIN PLAN\u7684\u6267\u884c\u7ed3\u679c\u4ee3\u8868\u5728EXPLAIN\u8fd9\u6761SQL\u7684\u65f6\u5019\uff0cDB\u5e94\u8be5\u5982\u4f55\u53bb\u6267\u884c\u8fd9\u6761SQL\u3002\u800c\u5b9e\u9645\u5728\u6267\u884c\u7684\u65f6\u5019\u53ef\u80fd\u7531\u4e8e\u6570\u636e\u6570\u91cf\uff0c\u7ed3\u6784(\u4f8b\u5982\u589e\u52a0\u4e86\u7d22\u5f15\u7b49)\u7b49\u7684\u53d8\u5316\uff0c\u4f7f\u5f97\u5b9e\u9645\u7684\u6267\u884c\u8ba1\u5212\u4e0e\u9884\u4f30\u6267\u884c\u8ba1\u5212\u7565\u6709\u4e0d\u540c\u3002 \u67e5\u8be2\u6267\u884c\u8ba1\u5212 \u9884\u4f30\u6267\u884c\u8ba1\u5212 DBMS_XPLAN.DISPLAY\u662f\u4e00\u4e2aOracle\u81ea\u5e26\u7684\u51fd\u6570(\u70b9\u51fb\u94fe\u63a5\u67e5\u770b\u8be6\u60c5)\uff0c\u5b83\u53ef\u4ee5\u63a5\u53d7\u4e00\u4e9b\u9009\u9879\u7528\u4e8e\u5c55\u793a\u6267\u884c\u8ba1\u5212\uff1a &#8211;\u4e0d\u5e26\u9009\u9879 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); &#8211;\u8fd9\u91cc\u4f7f\u7528\u4e86\u81ea\u5b9a\u4e49\u7684\u8868my_plan_table\u548c\u81ea\u5b9a\u4e49\u7684SQL ID statement1 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(&#8216;my_plan_table&#8217;,&#8217;statement1&#8242;,&#8217;TYPICAL&#8217;)); \u6267\u884c\u7ed3\u679c\u5927\u6982\u957f\u8fd9\u6837\uff1a Plan hash value: 1906736282 &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212; | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[88,101,80],"tags":[],"class_list":["post-1750","post","type-post","status-publish","format-standard","hentry","category-oracle","category-101","category-80"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1750","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1750"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1750\/revisions"}],"predecessor-version":[{"id":1751,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1750\/revisions\/1751"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1750"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1750"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1750"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}