\u5bf9\u4e8eoracle dba\u6765\u8bf4\uff0c\u5176\u4e2d\u6700\u591a\u7684\u65f6\u95f4\u662f\u653e\u5728\u6570\u636e\u5e93\u4f18\u5316\u4e0a\u7684\uff0c\u6570\u636e\u5e93\u4f18\u5316\u4e5f\u662f\u8861\u91cf\u4e00\u4e2adba\u4ef7\u503c\u7684\u91cd\u8981\u65b9\u9762\uff0c\u8fd9\u4e2a\u4e5f\u5728\u5f88\u591a\u521a\u5165\u95e8\u7684dba\u53bb\u9762\u8bd5dba\u804c\u4f4d\u65f6\u88ab\u95ee\u53ca\u7684\u6700\u591a\u5e76\u4e14\u4e5f\u662f\u5fc5\u4e0d\u53ef\u5c11\u7684\u4e00\u4e2a\u95ee\u9898\u3002\u800c\u5bf9\u4e8e\u6570\u636e\u5e93\u4f18\u5316\uff0c\u57fa\u672c\u662f\u5bf9sql\u7684\u4f18\u5316\uff0c\u800c\u8981\u4f18\u5316\u4e00\u6761sql\uff0c\u5219\u83b7\u53d6sql\u7684\u6267\u884c\u8ba1\u5212\u4fbf\u662f\u7b2c\u4e00\u6b65\u3002<\/p>\n
\u4e0b\u8fb9\u6574\u7406\u4e867\u79cdoracle\u5e38\u7528\u7684\u83b7\u53d6sql\u6267\u884c\u8ba1\u5212\u7684\u9014\u5f84\uff0c\u5305\u62ec\u83b7\u53d6\u5f53\u524dsql\u7684\u6267\u884c\u8ba1\u5212\uff0c\u83b7\u53d6sql\u7684\u5386\u53f2\u6267\u884c\u8ba1\u5212\u7b49\u3002\u5e0c\u671b\u80fd\u5e2e\u52a9\u5230\u5927\u5bb6\u3002<\/p>\n
\uff08\u4e00\uff09\u4f7f\u7528explain for \u89e3\u91casql\u7684\u6267\u884c\u8ba1\u5212\uff0c\u901a\u8fc7dbms_xplan()\u51fd\u6570\u67e5\u770b\u89e3\u91ca\u540e\u7684\u6267\u884c\u8ba1\u5212<\/strong> \u793a\u4f8b\u5982\u4e0b\uff1a<\/p>\n \uff08\u4e8c\uff09\u4f7f\u7528dbms_xplan.display_cursor\u67e5\u770b\u771f\u5b9e\u6267\u884c\u8ba1\u5212\uff0810G\u4ee5\u4e0a\uff09<\/strong><\/p>\n \u4f7f\u7528\u8fd9\u4e2a\u51fd\u6570\u53ef\u4ee5\u663e\u793a\u5185\u5b58\u4e2d\u4e00\u4e2a\u6216\u8005\u591a\u4e2a\u6e38\u6807\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>\n \u8bed\u6cd5\uff1a \u6ce8\u610f\uff1a\u5982\u679c\u7b2c\u4e00\u4e2a\u548c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u672a\u8bbe\u7f6e\uff0c\u5219\u4f1a\u8fd4\u56de\u5185\u5b58\u4e2d\u6700\u8fd1\u7684\u4e00\u4e2asql\u7684\u6267\u884c\u8ba1\u5212\uff0c\u7b2c\u4e09\u4e2a\u53c2\u6570\u7528\u6765\u8bbe\u7f6e\u683c\u5f0f\u8f93\u51fa \u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e\u7b2c\u4e00\u4e2a\u548c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u663e\u793a\u6307\u5b9a\u7684sql\u7684\u771f\u5b9e\u6267\u884c\u8ba1\u5212 \u6ce8\u610f\uff1a\u6b64\u5904\u4eba\u4e3a\u6267\u884c\u4e86\u4e00\u4e2asql\uff08\u8be5sql\u52a0\u4e86\u6ce8\u91ca\uff0c\u4fbf\u4e8e\u540e\u8fb9\u8bc6\u522b\uff09\uff0c\u7136\u540e\u4ece\u627e\u5230\u8be5sql\u7684hash_value\u548c\u5b50\u6e38\u6807\u53f7\uff083485646091 0\uff09\uff0c\u6b64\u5904\u4e5f\u53ef\u4ee5\u770b\u5230\u8be5\u6e38\u6807\u662f\u7236\u6e38\u6807\uff0c\u5982\u679c\u8be5sql\u5728\u4e0d\u540c\u7684\u73af\u5883\u4e0b\u6267\u884c\uff0c\u5c06\u4f1a\u4ea7\u751f\u4e0d\u540c\u7684version count\uff0c\u8fd9\u6837\u7684\u8bdd\u5b50\u6e38\u6807\u5728\u8be5\u5904\u4f1a\u6709\u591a\u4e2a\u3002<\/p>\n \u7136\u540e\u8fdb\u884c\u8bbe\u7f6e\u51fd\u6570dbms_xplan.display_cursor()\u7684\u7b2c\u4e00\u4e2a\u548c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u67e5\u770b\u6307\u5b9a\u7684sql\u7684\u6267\u884c\u8ba1\u5212\u3002<\/p>\n \u4f7f\u7528\u683c\u5f0f\u5316\u53c2\u6570BASIC LAST ALLSTATS<\/p>\n \u4f7f\u7528\u683c\u5f0f\u5316\u53c2\u6570advanced<\/p>\n \u4f7f\u7528hint gather_plan_statistics\u7ed3\u5408\u51fd\u6570dbms_xplan.display_cursor()\u8f93\u51fa\u542b\u6709\u7edf\u8ba1\u4fe1\u606f\u7684\u8be6\u7ec6\u7684sql\u7684\u6267\u884c\u8ba1\u5212<\/p>\n \uff08\u4e09\uff09\u4f7f\u7528dbms_xplan.display_awr()\u51fd\u6570\u67e5\u770b\u5b58\u50a8\u5728awr\u4e2d\u7684\u5386\u53f2\u6267\u884c\u8ba1\u5212<\/strong><\/p>\n \u9996\u5148\u627e\u4e2a\u5386\u53f2\u7684sql_id<\/p>\n \u7136\u540e\u4f7f\u7528dbms_xplan.display_awr()\u51fd\u6570\u67e5\u770b\u5386\u53f2\u6267\u884c\u8ba1\u5212<\/p>\n \uff08\u56db\uff09\u4f7f\u7528$ORACLE_HOME\/rdbms\/admin\/awrsqrpt.sql \u751f\u6210\u6307\u5b9asql_id\u7684\u5386\u53f2\u6267\u884c\u8ba1\u5212<\/strong><\/p>\n \u6ce8\u610f\uff1a\u8be5\u5904\u9700\u8981\u6307\u5b9a\u95ee\u9898sql\u7684sql_id\uff0c\u800csql_id\u53ef\u4ee5\u901a\u8fc7awr\u62a5\u544a\u6216\u8005addm\u7b49\u4e8b\u5148\u83b7\u53d6\u3002<\/p>\n \uff08\u4e94\uff09\u901a\u8fc7\u5ba2\u6237\u7aefsql*plus\u81ea\u5e26\u7684autotrace\u5de5\u5177\u81ea\u52a8\u751f\u6210sql\u6267\u884c\u8ba1\u5212<\/strong><\/p>\n \u9996\u5148\uff0c\u9700\u8981\u521b\u5efaplustrace\u89d2\u8272\u5e76\u7ed9\u7528\u6237\u8d4b\u4e88\u76f8\u5173\u7684plustrace\u89d2\u8272<\/p>\n \u6253\u5f00\u7528\u6237\u7684sql_trace\u5e76\u6536\u96c6sql\u6267\u884c\u7684\u4fe1\u606f\u540e\u5173\u95edsql_trace<\/p>\n \u67e5\u627e\u7528\u6237trace\u6587\u4ef6<\/p>\n \u67e5\u770b\u7528\u6237trace\u6587\u4ef6\u4e2d\u7684\u6267\u884c\u8ba1\u5212<\/p>\n \uff08\u4e03\uff09\u4f7f\u7528_optimizer_trace\u6216\u800510053\u8ddf\u8e2a\u4e8b\u4ef6\u751f\u6210sql\u7684\u6267\u884c\u8ba1\u5212<\/p>\n \u67e5\u770b\u5f53\u524d\u4f1a\u8bdd\u7684trace\u6587\u4ef6\u4f4d\u7f6e<\/p>\n \u67e5\u770b\u8be5trace\u6587\u4ef6\u4e2d\u7684\u90e8\u5206<\/p>\n \u4ee5\u4e0a\u4fbf\u662f\u5e38\u7528\u7684\u67e5\u770b\u4e00\u4e2asql\u7684\u6267\u884c\u8ba1\u5212\u7684\u65b9\u6cd5\uff0c\u4e14\u5747\u5728oracle10g\u7248\u672c\u5b9e\u9a8c\u901a\u8fc7\u3002<\/p>\n","protected":false},"excerpt":{"rendered":" \u5bf9\u4e8eoracle dba\u6765\u8bf4\uff0c\u5176\u4e2d\u6700\u591a\u7684\u65f6\u95f4\u662f\u653e\u5728\u6570\u636e\u5e93\u4f18\u5316\u4e0a\u7684\uff0c\u6570\u636e\u5e93\u4f18\u5316\u4e5f\u662f\u8861\u91cf\u4e00\u4e2adba\u4ef7\u503c\u7684\u91cd\u8981\u65b9\u9762\uff0c […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":[],"categories":[32,37],"tags":[132,129,131,130,128,136,135,134,133,125,127],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/726"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=726"}],"version-history":[{"count":9,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/726\/revisions"}],"predecessor-version":[{"id":765,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/726\/revisions\/765"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=726"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\u6ce8\u610f\uff1a\u8be5\u65b9\u6cd5\u5e76\u6ca1\u6709\u771f\u6b63\u7684\u6267\u884c\u6307\u5b9a\u7684sql\uff0c\u800c\u662f\u5c06\u6307\u5b9a\u7684sql\u8fdb\u884c\u4e86\u89e3\u91ca\u800c\u5df2\uff0c\u5e76\u4e14\u5728library cache\u4e2d\u5b58\u5728\u7684\u8be5sql\u5c06\u662fexplain for select\u2026
\n\u800c\u4e0d\u662f\u6307\u5b9a\u7684sql\u3002<\/p>\n\r\n\r\nSQL> explain plan for \r\n 2 select * from scott.emp e where e.deptno in (select deptno from scott.dept); \r\n\r\nExplained.\r\n\r\nSQL> select * from table(dbms_xplan.display());\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 3074306753\r\n\r\n------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 14 | 560 | 3 (0)| 00:00:01 |\r\n| 1 | NESTED LOOPS | | 14 | 560 | 3 (0)| 00:00:01 |\r\n| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |\r\n|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |\r\n------------------------------------------------------------------------------\r\n\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 3 - access("E"."DEPTNO"="DEPTNO")\r\n\r\n15 rows selected.\r\n<\/pre>\n
\nSelect * from table(dbms_xplan.display_cursor('hash_value','child_number','advanced'));<\/p>\n
\n\u5982\u4e0b\uff1a<\/p>\n\r\nSQL> select \/*+gather_plan_statistics*\/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;\r\n\r\n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\r\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\r\n\r\nSQL> select * from table(dbms_xplan.display_cursor);\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nSQL_ID ah916jr1wmqbg, child number 0\r\n-------------------------------------\r\nselect \/*+gather_plan_statistics*\/ * from scott.emp e where e.deptno in\r\n(select deptno from scott.dept) and rownum<3\r\n\r\nPlan hash value: 653357053\r\n\r\n-------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | 2 (100)| |\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n|* 1 | COUNT STOPKEY | | | | | |\r\n| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |\r\n|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| |\r\n-------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter(ROWNUM<3)\r\n 4 - access("E"."DEPTNO"="DEPTNO")\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n\r\n\r\n23 rows selected.\r\n<\/pre>\n
\n\u793a\u4f8b\uff1a<\/p>\n\r\nSQL> set line 200\r\nSQL> select \/*yallonking*\/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;\r\n\r\n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\r\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\r\n\r\nSQL> col SQL_TEXT for a70\r\nSQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%yallonking%';\r\n\r\nSQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT\r\n------------- ---------- ------------ ----------------------------------------------------------------------\r\nabcarhr7w5g8b 3485646091 0 select \/*yallonking*\/ * from scott.emp e where e.deptno in (select dep\r\n tno from scott.dept) and rownum<3\r\n\r\nd625bbv7utkq6 3484207814 0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_te\r\n xt like '%yallonking%'\r\n\r\ndymhjyqqc7x7z 2898523391 0 select hash_value,child_number,sql_text from v$sql where sql_text like\r\n '%yallonking%'\r\n<\/pre>\n
\r\nSQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'BASIC LAST ALLSTATS')); \r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nEXPLAINED SQL STATEMENT:\r\n------------------------\r\nselect \/*yallonking*\/ * from scott.emp e where e.deptno in (select\r\ndeptno from scott.dept) and rownum<3\r\n\r\nPlan hash value: 653357053\r\n\r\n------------------------------------------------\r\n| Id | Operation | Name | E-Rows |\r\n------------------------------------------------\r\n|* 1 | COUNT STOPKEY | | |\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n| 2 | NESTED LOOPS | | 2 |\r\n| 3 | TABLE ACCESS FULL| EMP | 2 |\r\n|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 |\r\n------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter(ROWNUM<3)\r\n 4 - access("E"."DEPTNO"="DEPTNO")\r\n\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nNote\r\n-----\r\n - Warning: basic plan statistics not available. These are only collected when:\r\n * hint 'gather_plan_statistics' is used for the statement or\r\n * parameter 'statistics_level' is set to 'ALL', at session or system level\r\n\r\n\r\n28 rows selected.\r\n<\/pre>\n\r\nSQL> select * from table(dbms_xplan.display_cursor(3485646091,0,'advanced'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nHASH_VALUE 3485646091, child number 0\r\n--------------------------------------\r\nselect \/*yallonking*\/ * from scott.emp e where e.deptno in (select\r\ndeptno from scott.dept) and rownum<3\r\n\r\nPlan hash value: 653357053\r\n\r\n-------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | 2 (100)| |\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n|* 1 | COUNT STOPKEY | | | | | |\r\n| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |\r\n|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| |\r\n-------------------------------------------------------------------------------\r\n\r\nQuery Block Name \/ Object Alias (identified by operation id):\r\n-------------------------------------------------------------\r\n\r\n 1 - SEL$5DA710D3\r\n 3 - SEL$5DA710D3 \/ E@SEL$1\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n 4 - SEL$5DA710D3 \/ DEPT@SEL$2\r\n\r\nOutline Data\r\n-------------\r\n\r\n \/*+\r\n BEGIN_OUTLINE_DATA\r\n IGNORE_OPTIM_EMBEDDED_HINTS\r\n OPTIMIZER_FEATURES_ENABLE('10.2.0.1')\r\n ALL_ROWS\r\n OUTLINE_LEAF(@"SEL$5DA710D3")\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n UNNEST(@"SEL$2")\r\n OUTLINE(@"SEL$1")\r\n OUTLINE(@"SEL$2")\r\n FULL(@"SEL$5DA710D3" "E"@"SEL$1")\r\n INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))\r\n LEADING(@"SEL$5DA710D3" "E"@"SEL$1" "DEPT"@"SEL$2")\r\n USE_NL(@"SEL$5DA710D3" "DEPT"@"SEL$2")\r\n END_OUTLINE_DATA\r\n *\/\r\n\r\nPredicate Information (identified by operation id):\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n---------------------------------------------------\r\n\r\n 1 - filter(ROWNUM<3)\r\n 4 - access("E"."DEPTNO"="DEPTNO")\r\n\r\nColumn Projection Information (identified by operation id):\r\n-----------------------------------------------------------\r\n\r\n 1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],\r\n "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],\r\n "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n 2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],\r\n "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],\r\n "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]\r\n 3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],\r\n "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],\r\n "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]\r\n\r\n\r\n62 rows selected.\r\n<\/pre>\n\r\nSQL> alter system flush shared_pool;\r\n\r\nSystem altered.\r\n\r\nSQL> select \/*+gather_plan_statistics*\/ * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;\r\n\r\n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\r\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\r\n\r\nSQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%scott%';\r\n\r\nSQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT\r\n------------- ---------- ------------ --------------------------------------------------------------------------------\r\ng071m9pbmk8a0 1463361856 0 select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%\r\n scott%'\r\n\r\nah916jr1wmqbg 3284785519 0 select \/*+gather_plan_statistics*\/ * from scott.emp e where e.deptno in (select\r\n deptno from scott.dept) and rownum<3\r\n\r\n\r\nSQL> select * from table(dbms_xplan.display_cursor(3284785519,0,'BASIC LAST ALLSTATS'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nEXPLAINED SQL STATEMENT:\r\n------------------------\r\nselect \/*+gather_plan_statistics*\/ * from scott.emp e where e.deptno in (select\r\ndeptno from scott.dept) and rownum<3\r\n\r\nPlan hash value: 653357053\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\r\n-----------------------------------------------------------------------------------------\r\n|* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:00.01 | 10 |\r\n\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n| 2 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 10 |\r\n| 3 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 8 |\r\n|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 2 | 1 | 2 |00:00:00.01 | 2 |\r\n-----------------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter(ROWNUM<3)\r\n 4 - access("E"."DEPTNO"="DEPTNO")\r\n\r\n\r\n22 rows selected.\r\n<\/pre>\n\r\nSQL> set line 300\r\nSQL> select * from dba_hist_sqltext where rownum<3 and COMMAND_TYPE=3;\r\n\r\n DBID SQL_ID SQL_TEXT COMMAND_TYPE\r\n---------- ------------- -------------------------------------------------------------------------------- ------------\r\n4093928674 byaarkxm6b0r1 SELECT \/*+ use_nl(u,o,t) *\/ NVL(T.DEGREE,1) FROM USER$ U,OBJ$ O,TAB$ T WHERE U.N 3\r\n4093928674 4qfx5ur8a90ym select u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and t.obj#=o.ob 3\r\n\r\n<\/pre>\n
\r\nSQL> select * from table(dbms_xplan.display_awr('4qfx5ur8a90ym'));\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nSQL_ID 4qfx5ur8a90ym\r\n--------------------\r\nselect u.name,o.name from tab$ t,user$ u,obj$ o where t.bobj#=:1 and\r\nt.obj#=o.obj# and o.owner#=u.user#\r\n\r\nPlan hash value: 1863015984\r\n\r\n-----------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | 6 (100)| |\r\n\r\nPLAN_TABLE_OUTPUT\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n| 1 | NESTED LOOPS | | 2 | 96 | 6 (0)| 00:00:01 |\r\n| 2 | NESTED LOOPS | | 2 | 62 | 4 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS BY INDEX ROWID| TAB$ | 2 | 12 | 2 (0)| 00:00:01 |\r\n| 4 | INDEX RANGE SCAN | I_TAB1 | 1 | | 1 (0)| 00:00:01 |\r\n| 5 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 1 | 25 | 1 (0)| 00:00:01 |\r\n| 6 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | | 0 (0)| |\r\n| 7 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |\r\n| 8 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| |\r\n-----------------------------------------------------------------------------------------\r\n\r\n\r\n21 rows selected.\r\n<\/pre>\n\r\nSQL> @?\/rdbms\/admin\/awrsqrpt.sql\r\n\r\nCurrent Instance\r\n~~~~~~~~~~~~~~~~\r\n\r\n DB Id DB Name Inst Num Instance\r\n----------- ------------ -------- ------------\r\n 4093928674 ORA10GR2 1 ora10gr2\r\n\r\n\r\nSpecify the Report Type\r\n~~~~~~~~~~~~~~~~~~~~~~~\r\nWould you like an HTML report, or a plain text report?\r\nEnter 'html' for an HTML report, or 'text' for plain text\r\nDefaults to 'html'\r\nEnter value for report_type: \r\n\r\nType Specified: html\r\n\r\n\r\nInstances in this Workload Repository schema\r\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\n\r\n DB Id Inst Num DB Name Instance Host\r\n------------ -------- ------------ ------------ ------------\r\n* 4093928674 1 ORA10GR2 ora10gr2 rhl5.5\r\n* 4093928674 1 ORA10GR2 ora10gr2 ora10gr2\r\n* 4093928674 1 ORA10GR2 ora10gr2 ora11gr2\r\n\r\nUsing 4093928674 for database Id\r\nUsing 1 for instance number\r\n\r\n\r\nSpecify the number of days of snapshots to choose from\r\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\nEntering the number of days (n) will result in the most recent\r\n(n) days of snapshots being listed. Pressing <return> without\r\nspecifying a number lists all completed snapshots.\r\n\r\n\r\nEnter value for num_days: \r\n\r\nListing all Completed Snapshots\r\n\r\n Snap\r\nInstance DB Name Snap Id Snap Started Level\r\n------------ ------------ --------- ------------------ -----\r\nora10gr2 ORA10GR2 18 21 Feb 2013 19:42 1\r\n 19 21 Feb 2013 21:00 1\r\n 20 21 Feb 2013 22:00 1\r\n\r\n\r\n\r\nSpecify the Begin and End Snapshot Ids\r\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\nEnter value for begin_snap: 18\r\nBegin Snapshot Id specified: 18\r\n\r\nEnter value for end_snap: 20\r\nEnd Snapshot Id specified: 20\r\n\r\n\r\n\r\n\r\nSpecify the SQL Id\r\n~~~~~~~~~~~~~~~~~~\r\nEnter value for sql_id: 393c9swv9f3gt\r\n\u2026 \u2026\r\n\u4ee5\u4e0b\u7701\u7565\r\n<\/pre>\n
\r\nSQL> grant connect,resource,select any table to test;\r\n\r\nGrant succeeded.\r\n\r\nSQL> conn test\/test\r\nConnected.\r\nSQL> set autotrace traceonly\r\nSP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled\r\nSP2-0611: Error enabling STATISTICS report\r\nSQL> show user\r\nUSER is "SYS"\r\nSQL> @?\/sqlplus\/admin\/plustrce.sql\r\nSQL> \r\nSQL> drop role plustrace;\r\ndrop role plustrace\r\n *\r\nERROR at line 1:\r\nORA-01919: role 'PLUSTRACE' does not exist\r\n\r\n\r\nSQL> create role plustrace;\r\n\r\nRole created.\r\n\r\nSQL> \r\nSQL> grant select on v_$sesstat to plustrace;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant select on v_$statname to plustrace;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant select on v_$mystat to plustrace;\r\n\r\nGrant succeeded.\r\n\r\nSQL> grant plustrace to dba with admin option;\r\n\r\nGrant succeeded.\r\n\r\nSQL> \r\nSQL> set echo off\r\nSQL> grant plustrace to test;\r\n\r\nGrant succeeded.\r\n\r\nSQL> conn test\/test\r\nConnected.\r\nSQL> set autotrace traceonly\r\nSQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;\r\n\r\n\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 653357053\r\n\r\n-------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 2 | 80 | 2 (0)| 00:00:01 |\r\n|* 1 | COUNT STOPKEY | | | | | |\r\n| 2 | NESTED LOOPS | | 2 | 80 | 2 (0)| 00:00:01 |\r\n| 3 | TABLE ACCESS FULL| EMP | 2 | 74 | 2 (0)| 00:00:01 |\r\n|* 4 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |\r\n-------------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 1 - filter(ROWNUM<3)\r\n 4 - access("E"."DEPTNO"="DEPTNO")\r\n\r\n\r\nStatistics\r\n----------------------------------------------------------\r\n 0 recursive calls\r\n 0 db block gets\r\n 10 consistent gets\r\n 0 physical reads\r\n 0 redo size\r\n 898 bytes sent via SQL*Net to client\r\n 385 bytes received via SQL*Net from client\r\n 2 SQL*Net roundtrips to\/from client\r\n 0 sorts (memory)\r\n 0 sorts (disk)\r\n 2 rows processed\r\n\r\n<\/pre>\n
\n\uff08\u516d\uff09\u901a\u8fc7\u7528\u6237\u7684sql_trace\u7684dump\u6587\u4ef6\u67e5\u770b\u7528\u6237sql\u7684\u6267\u884c\u8ba1\u5212<\/strong>
\n\u9996\u5148\uff0c\u4fbf\u4e8e\u67e5\u627e\u5f53\u524d\u7528\u6237\u7684trace\u6587\u4ef6\uff0c\u5148\u6807\u793a\u8be5\u7528\u6237\u7684trace\u6587\u4ef6\u3002<\/p>\n\r\nSQL> alter session set tracefile_identifier='yallonking';\r\n\r\nSession altered.\r\n\r\nSQL> set line 200 \r\n<\/pre>\n
\r\nSQL> alter session set sql_trace=true;\r\n\r\nSession altered.\r\n\r\nSQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;\r\n\r\n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\r\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\r\n\r\nSQL> alter session set sql_trace=false;\r\n\r\nSession altered.\r\n<\/pre>\n
\r\nSQL> !ls -ltr \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/*yallonking*\r\n-rw-r----- 1 oracle oinstall 70 Feb 21 23:15 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9540_yallonking.trc\r\n-rw-r----- 1 oracle oinstall 2003 Feb 21 23:16 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9647_yallonking.trc\r\n-rw-r----- 1 oracle oinstall 2146 Feb 21 23:17 \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9655_yallonking.trc\r\n<\/pre>\n
\r\nSQL> !vi \/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9655_yallonking.trc\r\n\r\n\/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9655_yallonking.trc\r\nOracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production\r\nWith the Partitioning, OLAP and Data Mining options\r\nORACLE_HOME = \/u01\/app\/oracle\/product\/10.2.0\/db_1\r\nSystem name: Linux\r\nNode name: ora10gr2\r\nRelease: 2.6.18-194.el5\r\nVersion: #1 SMP Tue Mar 16 21:52:43 EDT 2010\r\nMachine: i686\r\nInstance name: ora10gr2\r\nRedo thread mounted by this instance: 1\r\nOracle process number: 15\r\nUnix process pid: 9655, image: oracle@ora10gr2 (TNS V1-V3)\r\n\r\n*** 2013-02-21 23:17:11.037\r\n*** SERVICE NAME:(SYS$USERS) 2013-02-21 23:17:11.037\r\n*** SESSION ID:(159.38) 2013-02-21 23:17:11.037\r\n=====================\r\nPARSING IN CURSOR #1 len=32 dep=0 uid=0 oct=42 lid=0 tim=1329606866247139 hv=1569151342 ad='3e3f7fbc'\r\nalter session set sql_trace=true\r\nEND OF STMT\r\nEXEC #1:c=1000,e=674,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606866247129\r\n=====================\r\nPARSING IN CURSOR #1 len=88 dep=0 uid=0 oct=3 lid=0 tim=1329606871299685 hv=747907697 ad='43f3aef4'\r\nselect * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3\r\nEND OF STMT\r\nPARSE #1:c=1000,e=1402,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871299673\r\nEXEC #1:c=0,e=268,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606871300323\r\nFETCH #1:c=1999,e=1761,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871302309\r\nFETCH #1:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=1329606871304230\r\nSTAT #1 id=1 cnt=2 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=10 pr=0 pw=0 time=1756 us)'\r\nSTAT #1 id=2 cnt=2 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=10 pr=0 pw=0 time=1711 us)'\r\nSTAT #1 id=3 cnt=2 pid=2 pos=1 obj=51148 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=1091 us)'\r\nSTAT #1 id=4 cnt=2 pid=2 pos=2 obj=51147 op='INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=637 us)'\r\n*** 2013-02-21 23:17:28.830\r\n=====================\r\nPARSING IN CURSOR #1 len=33 dep=0 uid=0 oct=42 lid=0 tim=1329606883623491 hv=525901419 ad='43c4d108'\r\nalter session set sql_trace=false\r\nEND OF STMT\r\nPARSE #1:c=10999,e=10341,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1329606883623481\r\nEXEC #1:c=999,e=588,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1329606883624361\r\n\r\n<\/pre>\n
\r\nSQL> set line 200 \r\nSQL> alter session set "_optimizer_trace"=all;\r\n\r\nSession altered.\r\n\r\nSQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4;\r\n\r\n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\r\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\r\n 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30\r\n\r\nSQL> alter session set "_optimizer_trace"=none;\r\n\r\nSession altered.\r\n<\/pre>\n
\r\nSQL> SELECT a.VALUE\r\n 2 || b.symbol\r\n 3 || c.instance_name\r\n 4 || '_ora_'\r\n 5 || d.spid\r\n 6 || '.trc' trace_file\r\n 7 FROM (SELECT VALUE\r\n 8 FROM v$parameter\r\n 9 WHERE NAME = 'user_dump_dest') a,\r\n 10 (SELECT SUBSTR (VALUE, -6, 1) symbol\r\n 11 FROM v$parameter\r\n 12 WHERE NAME = 'user_dump_dest') b,\r\n 13 (SELECT instance_name\r\n 14 FROM v$instance) c,\r\n 15 (SELECT spid\r\n 16 FROM v$session s, v$process p, v$mystat m\r\n 17 WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d\r\n 18 \/\r\n\r\nTRACE_FILE\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n\/u01\/app\/oracle\/admin\/ora10gr2\/udump\/ora10gr2_ora_9759.trc\r\n\r\n<\/pre>\n
\r\n\u2026 \u2026\r\nsql_id=16v398fw5h696.\r\nCurrent SQL statement for this session:\r\nselect * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4\r\n\r\n============\r\nPlan Table\r\n============\r\n---------------------------------------+-----------------------------------+\r\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\r\n---------------------------------------+-----------------------------------+\r\n| 0 | SELECT STATEMENT | | | | 2 | |\r\n| 1 | COUNT STOPKEY | | | | | |\r\n| 2 | NESTED LOOPS | | 3 | 120 | 2 | 00:00:01 |\r\n| 3 | TABLE ACCESS FULL | EMP | 3 | 111 | 2 | 00:00:01 |\r\n| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 | |\r\n---------------------------------------+-----------------------------------+\r\nPredicate Information:\r\n----------------------\r\n1 - filter(ROWNUM<4)\r\n4 - access("E"."DEPTNO"="DEPTNO")\r\n\u2026 \u2026\r\n<\/pre>\n