ITPub博客

首页 > Linux操作系统 > Linux操作系统 > explain plan,autotrace,tkprof,执行计划和静态统计信息的解读(转载)

explain plan,autotrace,tkprof,执行计划和静态统计信息的解读(转载)

原创 Linux操作系统 作者:handsomeSJG 时间:2009-02-19 13:21:40 0 删除 编辑
  1. explain plan,autotrace,tkprof,执行计划和静态统计信息的解读   
  2.   
  3.   
  4.   
  5. 1、  执行计划   
  6.   
  7. 执行计划的设定   
  8. conn sys/pwd@gx as sysdba;   
  9.   
  10. CREATE USER TOOL   
  11.   IDENTIFIED BY tool   
  12.   DEFAULT TABLESPACE EXAMPLE   
  13.   TEMPORARY TABLESPACE TEMP  
  14.   PROFILE DEFAULT  
  15.   ACCOUNT UNLOCK;   
  16.   -- 2 Roles for TOOL    
  17.   GRANT RESOURCE TO TOOL;   
  18.   GRANT CONNECT TO TOOL;   
  19.   ALTER USER TOOL DEFAULT ROLE NONE;   
  20.   -- 3 System Privileges for TOOL    
  21.   GRANT CREATE SESSION TO TOOL;   
  22.   GRANT CREATE TABLE TO TOOL;   
  23.   GRANT UNLIMITED TABLESPACE TO TOOL;   
  24.   
  25. CREATE GLOBAL TEMPORARY TABLE tool.PLAN_TABLE   
  26. (   
  27.   STATEMENT_ID       VARCHAR2(30 BYTE),   
  28.   PLAN_ID            NUMBER,   
  29.   TIMESTAMP          DATE,   
  30.   REMARKS            VARCHAR2(4000 BYTE),   
  31.   OPERATION          VARCHAR2(30 BYTE),   
  32.   OPTIONS            VARCHAR2(255 BYTE),   
  33.   OBJECT_NODE        VARCHAR2(128 BYTE),   
  34.   OBJECT_OWNER       VARCHAR2(30 BYTE),   
  35.   OBJECT_NAME        VARCHAR2(30 BYTE),   
  36.   OBJECT_ALIAS       VARCHAR2(65 BYTE),   
  37.   OBJECT_INSTANCE    INTEGER,   
  38.   OBJECT_TYPE        VARCHAR2(30 BYTE),   
  39.   OPTIMIZER          VARCHAR2(255 BYTE),   
  40.   SEARCH_COLUMNS     NUMBER,   
  41.   ID                 INTEGER,   
  42.   PARENT_ID          INTEGER,   
  43.   DEPTH              INTEGER,   
  44.   POSITION           INTEGER,   
  45.   COST               INTEGER,   
  46.   CARDINALITY        INTEGER,   
  47.   BYTES              INTEGER,   
  48.   OTHER_TAG          VARCHAR2(255 BYTE),   
  49.   PARTITION_START    VARCHAR2(255 BYTE),   
  50.   PARTITION_STOP     VARCHAR2(255 BYTE),   
  51.   PARTITION_ID       INTEGER,   
  52.   OTHER              LONG,   
  53.   OTHER_XML          CLOB,   
  54.   DISTRIBUTION       VARCHAR2(30 BYTE),   
  55.   CPU_COST           INTEGER,   
  56.   IO_COST            INTEGER,   
  57.   TEMP_SPACE         INTEGER,   
  58.   ACCESS_PREDICATES  VARCHAR2(4000 BYTE),   
  59.   FILTER_PREDICATES  VARCHAR2(4000 BYTE),   
  60.   PROJECTION         VARCHAR2(4000 BYTE),   
  61.   TIME               INTEGER,   
  62.   QBLOCK_NAME        VARCHAR2(30 BYTE)   
  63. )   
  64. ON COMMIT PRESERVE ROWS;   
  65.   
  66. grant all on TOOL.PLAN_TABLE to public;   
  67.   
  68. CREATE PUBLIC SYNONYM PLAN_TABLE FOR TOOL.PLAN_TABLE;   
  69.   
  70.   
  71. 使用方法:   
  72. truncate table PLAN_TABL;   
  73. explain plan select * from emp;   
  74. select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));   
  75.   
  76. 演示:   
  77. conn scott/tiger   
  78. SQL> explain plan for select * from dept where deptno=10;   
  79. Explained   
  80. SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));   
  81.   
  82. PLAN_TABLE_OUTPUT   
  83. --------------------------------------------------------------------------------   
  84. Plan hash value: 3383998547   
  85. --------------------------------------------------------------------------   
  86. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  87. --------------------------------------------------------------------------   
  88. |   0 | SELECT STATEMENT  |      |     1 |    16 |     4   (0)| 00:00:01 |   
  89. |*  1 |  TABLE ACCESS FULL| DEPT |     1 |    16 |     4   (0)| 00:00:01 |   
  90. --------------------------------------------------------------------------   
  91. Predicate Information (identified by operation id):   
  92. ---------------------------------------------------   
  93.    1 - filter("DEPTNO"=10)   
  94.   
  95. 13 rows selected   
  96.   
  97. 执行计划解读:--估算表   
  98. 表v$sql_plan   
  99. cost概念   
  100. cardinality   
  101.   
  102.   
  103.   
  104. 查询路径—估算树   
  105. create table e   
  106. as select * from emp   
  107.   
  108. create table d   
  109. as  
  110. select * from dept   
  111.   
  112.   
  113. Explain plan for  
  114. select ename,dname from d,e where e.deptno=d.deptno   
  115.   
  116. select * from table(dbms_xplan.display());   
  117.   
  118.   
  119. Plan hash value: 1127375450   
  120.     
  121. ---------------------------------------------------------------------------   
  122. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  123. ---------------------------------------------------------------------------   
  124. |   0 | SELECT STATEMENT   |      |    15 |   630 |     7  (15)| 00:00:01 |   
  125. |*  1 |  HASH JOIN         |      |    15 |   630 |     7  (15)| 00:00:01 |   
  126. |   2 |   TABLE ACCESS FULL| D    |     4 |    88 |     3   (0)| 00:00:01 |   
  127. |   3 |   TABLE ACCESS FULL| E    |    15 |   300 |     3   (0)| 00:00:01 |   
  128. ---------------------------------------------------------------------------   
  129.     
  130. Predicate Information (identified by operation id):   
  131. ---------------------------------------------------   
  132.     
  133.    1 - access("E"."DEPTNO"="D"."DEPTNO")   
  134.     
  135. Note   
  136. -----   
  137.    - dynamic sampling used for this statement   
  138.   
  139. Explain plan for  
  140. select ename,dname from d, (select ename,deptno from e where rownum<2) e where e.deptno=d.deptno    
  141.   
  142. select * from table(dbms_xplan.display());   
  143.   
  144. Plan hash value: 1791846393   
  145.     
  146. -----------------------------------------------------------------------------   
  147. | Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |   
  148. -----------------------------------------------------------------------------   
  149. |   0 | SELECT STATEMENT     |      |     1 |    42 |     5   (0)| 00:00:01 |   
  150. |   1 |  NESTED LOOPS        |      |     1 |    42 |     5   (0)| 00:00:01 |   
  151. |   2 |   VIEW               |      |     1 |    20 |     2   (0)| 00:00:01 |   
  152. |*  3 |    COUNT STOPKEY     |      |       |       |            |          |   
  153. |   4 |     TABLE ACCESS FULL| E    |    15 |   300 |     2   (0)| 00:00:01 |   
  154. |*  5 |   TABLE ACCESS FULL  | D    |     1 |    22 |     3   (0)| 00:00:01 |   
  155. -----------------------------------------------------------------------------   
  156.     
  157. Predicate Information (identified by operation id):   
  158. ---------------------------------------------------   
  159.     
  160.    3 - filter(ROWNUM<2)   
  161.    5 - filter("E"."DEPTNO"="D"."DEPTNO")   
  162.     
  163. Note   
  164. -----   
  165.    - dynamic sampling used for this statement   
  166.   
  167.   
  168. 驱动表概念   
  169. 估算树   
  170.   
  171.     
  172. 从左到右 从下到上   
  173.   
  174. autotrace    
  175. oracle_home\sqlplus\admin\   
  176.   
  177. conn sys/pwd@gx as sysdba;   
  178. drop role plustrace;   
  179. create role plustrace;   
  180.   
  181. grant select on v_$sesstat to plustrace;   
  182. grant select on v_$statname to plustrace;   
  183. grant select on v_$mystat to plustrace;   
  184. grant plustrace to dba with admin option;   
  185. grant plustrace  to public;   
  186.   
  187. grant select on v_$sesstat to public;   
  188. grant select on v_$statname to public;   
  189. grant select on v_$mystat to public;   
  190. grant plustrace to dba with admin option;   
  191. grant plustrace  to public;   
  192.   
  193.   
  194. grant alter session to public;   
  195.   
  196. 使用命令   
  197. set autotrace on  
  198. set autotrace off  
  199. set autotrace on explain   
  200. set autotrace on statistics  
  201. set autotrace traceonly   
  202.   
  203.   
  204. autotrace输出内容解释   
  205. recursive calls:执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句(如触发器)。   
  206. 测试举例:   
  207. 举例1   
  208. conn scott/tiger@gx   
  209. set autotrace on  
  210. alter system flush shared_pool   
  211. set autotrace on  
  212. select * from emp   
  213. select * from emp   
  214.   
  215.   
  216. 举例2   
  217. create table exchage_table   
  218. (   
  219. bill_code   number(10),   
  220. exchage_rate  number(16,3)   
  221. )   
  222. BILL_CODE   EXCHAGE_RATE   
  223. 100 4.678   
  224. 200 5.235   
  225. 300 5.430   
  226. 400 2.654   
  227.   
  228.   
  229. create or replace function today_exchage(p_code in number) return number is    
  230. v_exange number(16,3);   
  231. begin  
  232. select exchage_rate into v_exange from exchage_table   
  233. where bill_code=p_code;   
  234. return v_exange;   
  235. end;   
  236.   
  237. create table affair   
  238. (   
  239.  trans_id  number(10),   
  240.  bill_code number(10),   
  241.  balance   number(16,2)   
  242. )   
  243.   
  244. TRANS_ID    BILL_CODE   BALANCE   
  245. 1000    100 1234.00   
  246. 2000    200 4324.32   
  247. 3000    300 65464.23   
  248.   
  249. 较好的写法为   
  250. select     
  251. trans_id,    
  252. (select exchage_rate    
  253. from exchage_table    
  254. where bill_code=affair.bill_code )*balance    
  255. from affair    
  256.   
  257. 举例3   
  258. drop table tppp purge      
  259. create table tppp(p integer)   
  260.   
  261. create or replace trigger t_trigger   
  262.   before insert on tppp     
  263.   for each row   
  264. declare  
  265.   -- local variables here   
  266. begin  
  267. if :new.p>5 then  
  268. raise_application_error(-20001,'bbbbbbbb');   
  269. end if;   
  270.      
  271. end t_trigger;   
  272.   
  273. 统计信息   
  274. ----------------------------------------------------------   
  275.          29  recursive calls   
  276.          19  db block gets   
  277.          54  consistent gets   
  278.           0  physical reads   
  279.        1172  redo size  
  280.         676  bytes sent via SQL*Net to client   
  281.         627  bytes received via SQL*Net from client   
  282.           3  SQL*Net roundtrips to/from client   
  283.           1  sorts (memory)   
  284.           0  sorts (disk)   
  285.           9  rows processed   
  286.   
  287. 在一次运行   
  288.   
  289. 统计信息   
  290. ---------------------------------------------------------   
  291.          29  recursive calls   
  292.           0  db block gets   
  293.         117  consistent gets   
  294.           1  physical reads   
  295.           0  redo size  
  296.         483  bytes sent via SQL*Net to client   
  297.         416  bytes received via SQL*Net from client   
  298.           2  SQL*Net roundtrips to/from client   
  299.           0  sorts (memory)   
  300.           0  sorts (disk)   
  301.           9  rows processed   
  302. drop  trigger t_trigger;   
  303.   
  304. 统计信息   
  305. ----------------------------------------------------------   
  306.           0  recursive calls   
  307.           0  db block gets   
  308.         108  consistent gets   
  309.           0  physical reads   
  310.           0  redo size  
  311.         483  bytes sent via SQL*Net to client   
  312.         416  bytes received via SQL*Net from client   
  313.           2  SQL*Net roundtrips to/from client   
  314.           0  sorts (memory)   
  315.           0  sorts (disk)   
  316.           9  rows processed   
  317.   
  318. 解决方法为   
  319. 1、  编写高效的trigger  
  320. 2、  用过程代替trigger  
  321.   
  322. 举例4:   
  323. 自我管理表空间与数据字典表空间   
  324. 本地管理的表空间能够减少递归sql   
  325.   
  326.   
  327. 输出内容: 逻辑I/O  (DB BLOCKS| CONSISTENT GETS)   
  328. 解释   
  329.   
  330.  对于一个SQL 逻辑I/O越小越好,通常通过SQL调整实现的   
  331.   
  332.   
  333.   
  334. TKPROF   
  335.   
  336. 使用 TKPROF 工具简介   
  337. TKPROF 工具简介   
  338. TKPROF 工具的使用步骤   
  339. TKPROF 工具如何分析 trace 文件   
  340. 启用TKPROF   
  341. 如何设置自动跟踪   
  342. 1、设定执行表,autotrace。方法如前所述,这里再重复一边。   
  343. 用system登录   
  344. 执行$ORACLE_HOME/rdbms/admin/utlxplan.sql创建计划表   
  345. 执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色   
  346. 如果想计划表让每个用户都能使用,则   
  347. SQL>create public synonym plan_table for plan_table;   
  348. SQL> grant all on plan_table to public;   
  349.   
  350. 2、设定tkprof   
  351. ALTER SESSION  SET SQL_TRACE = TRUE  
  352. ALTER SESSION  SET TIMED_STATISTICS = TRUE;   
  353. alter session set events ‘10046 trace name context forever,level 12’;   
  354. alter session  set max_dump_file_size=unlimited;   
  355. alter session set events  '10046 trace name context off'  
  356.   
  357.   
  358.   
  359. 获取跟踪文件名称   
  360. 跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,如果是unix需要做一定修改)   
  361. conn system/pwd   
  362.   
  363. SELECT p1.value||'\'||p2.value||'_ora_'||p.spid||'.trc' filename   
  364. FROM  
  365. v$process p,   
  366. v$session s,   
  367. v$parameter p1,   
  368. v$parameter p2   
  369. WHERE p1.name = 'user_dump_dest'  
  370. AND p2.name = 'db_name'  
  371. AND p.addr = s.paddr   
  372. AND s.audsid = USERENV ('SESSIONID')   
  373.   
  374. 在unix的目录下   
  375. http://www.eygle.com/faq/script/gettrcnameunix.sql   
  376.   
  377. 有了正确而详细的诊断数据之后,你需要以摘要的形式对其进行查看,这有助于你以最快的速度做出响应。   
  378. Cmd tkprof path\xxx.prc xxx.txt   
  379.   
  380. 报告解读:   
  381. parse(分析):在共享池中找到该查询(软分析)或者创建该查询的新计划(硬分析)   
  382. execute(执行):执行查询的所有工作   
  383. fetch(提取):显示select的提取工作,对于update,则没有内容   
  384.   
  385. count(计数):执行的次数   
  386. cpu:此阶段cpu的耗时,以毫秒为单位   
  387. elapsed(占用时间):挂钟时间,如果大于cpu时间,则有等待时间   
  388. disk(磁盘):执行物理I/O的次数   
  389. QUERY(查询):检索一致性执行的I/O次数   
  390. CURRENT(当前):到当前多执行的逻辑I/O次数   
  391. ROW:此阶段被处理或者受到影响的行   
  392.   
  393. 如果一个UPDATE语句EXECUTE的QUERY,CURRENTROWS分别为2000 1000 500,表示这个语句访问了2000个块找到需要UPDATE的行记录,在UPDATE的时候只访问了1000个块,一共更新了500行。如果只获取很少的数据,而要访问了大量的块,表明SQL与需要优化了。   
  394.   
  395.   
  396. MISSES 缓存命中率:0 表示已经通过软分析   
  397. OPTIMIZER GOAL(优化程序目标)   
  398.   
  399. 执行计划:与前面的执行计划相比,增加了各个阶段涉及的行数   
  400.   
  401. 关闭   
  402. alter system set events '10046 trace name context off';   
  403.   
  404. 更好的方法是使用DBMS_SUPPORT包来激活扩展SQL跟踪:    
  405. dbms_support.start_trace(waits=>;true, binds=>;true)    
  406. /* code to be traced goes here */    
  407. dbms_support.stop_trace()    
  408.   
  409. 请注意DBMS_SUPPORT 没有文档说明,可能也不是数据库默认安装的一部分。要了解DBMS_SUPPORT的信息,请参考MetaLink ( metalink.oracle.com)。     
  410.   
  411. 跟踪别人的代码。如果你想跟踪没有读/写权限的代码,则激活扩展SQL跟踪就有点麻烦了。但也不会难很多。你首先要获得你想跟踪的会话的V$SESSION.SID和V$SESSION.SERIAL#值。然后使用下面的过程调用,可以设置所选会话的TIMED_STATISTICS和MAX_DUMP_FILE_SIZE参数:     
  412.   
  413.   
  414. dbms_system.set_bool_param_in_session(    
  415.    sid     =>; 42,    
  416.    serial# =>; 1215,    
  417.    parnam  =>; 'timed_statistics',    
  418.    bval    =>; true)    
  419. dbms_system.set_int_param_in_session(    
  420.    sid     =>; 42,    
  421.    serial# =>; 1215,    
  422.    parnam  =>; 'max_dump_file_size',    
  423.    intval  =>; 2147483647)    
  424.   
  425.   
  426. (对于Oracle8 8.1.6以前的版本,你可以用ALTER SYSTEM命令处理这些参数。)     
  427.   
  428. 接下来要激活跟踪。有几种方法可以采用,包括下面两个:    
  429.   
  430. 方法一是使用DBMS_SUPPORT:     
  431.   
  432.   
  433. dbms_support.start_trace_in_session(    
  434.    sid     =>; 42,    
  435.    serial# =>; 1215,    
  436.    waits   =>; true,    
  437.    binds   =>; true)    
  438. /* code to be traced executes during this time window */    
  439. dbms_support.stop_trace_in_session(    
  440.   sid      =>; 42,    
  441.   serial   =>; 1215)    
  442.   
  443.   
  444. 若想激活扩展SQL跟踪,请不要使用名为SET_SQL_TRACE_IN_SESSION的DBMS_SUPPORT过程。该过程不允许在跟踪文件中指定等待和绑定的数据。     
  445.   
  446. 第二种方法更为精致,但在Oracle数据库10g之前的版本中并不支持这种方法。 DBMS_MONITOR包的引入解决了许多复杂诊断数据收集问题,这些问题是由连接共享和多线程操作所引起的。你可以在Oracle数据库10g中指定要跟踪的服务、模块或行动,而不指定要跟踪的Oracle数据库会话:     
  447.   
  448.   
  449. dbms_monitor.serv_mod_act_trace_enable(    
  450.   service_name  =>; 'APPS1',    
  451.   module_name   =>; 'PAYROLL',    
  452.   action_name   =>; 'PYUGEN',    
  453.   waits         =>; true,    
  454.   binds         =>; true,    
  455.   instance_name =>; null)    
  456. /* code to be traced executes during this time window */    
  457. dbms_monitor.serv_mod_act_trace_disable(    
  458.   service_name  =>; 'APPS1',    
  459.   module_name   =>; 'PAYROLL',    
  460.   action_name  =>; 'PYUGEN')    
  461.   
  462.   
  463. 利用DBMS_MONITOR包,Oracle可为要跟踪的特定的业务操作提供完全支持激活或停止诊断数据收集的方法。    
  464.   
  465.   
  466. 在PL/SQL中,由于不能执行alter session,可以使用   
  467.      
  468.   dbms_session.set_sql_trace(TRUE);   
  469.      
  470.   必须安装DBMS_SESSION包,并"直接"赋给用户alter session的权限。   
  471. 当我们使用sql   
  472. For Unix:   
  473.  $ sqlplus "/ as sysdba"  
  474.   
  475. SQL*Plus: Release 9.2.0.4.0 - Production on Fri Oct 8 12:08:09 2004   
  476.   
  477. Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.   
  478.   
  479.   
  480. Connected to:   
  481. Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production   
  482. With the Partitioning, OLAP and Oracle Data Mining options   
  483. JServer Release 9.2.0.4.0 - Production   
  484.   
  485. SQL> set echo on  
  486. SQL> @gettrcnameunix   
  487. SELECT       d.VALUE   
  488.          || '/'  
  489.          || LOWER (RTRIM (i.INSTANCE, CHR (0)))   
  490.          || '_ora_'  
  491.          || p.spid   
  492.          || '.trc' trace_file_name   
  493.     FROM (SELECT p.spid   
  494.             FROM v$mystat m, v$session s, v$process p   
  495.            WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,   
  496.          (SELECT t.INSTANCE   
  497.             FROM v$thread t, v$parameter v   
  498.            WHERE v.NAME = 'thread'  
  499.              AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,   
  500.          (SELECT VALUE   
  501.             FROM v$parameter   
  502.            WHERE NAME = 'user_dump_dest') d   
  503. TRACE_FILE_NAME   
  504. --------------------------------------------------------------------------------   
  505. /opt/oracle/admin/hsbill/udump/hsbill_ora_29630.trc   
  506.   
  507. For Nt:    
  508. SELECT    d.VALUE   
  509.         || '\'  
  510.         || LOWER (RTRIM (i.INSTANCE, CHR (0)))   
  511.         || '_ora_'  
  512.         || p.spid   
  513.         || '.trc' trace_file_name   
  514.    FROM (SELECT p.spid   
  515.            FROM v$mystat m, v$session s, v$process p   
  516.           WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,   
  517.         (SELECT t.INSTANCE   
  518.            FROM v$thread t, v$parameter v   
  519.           WHERE v.NAME = 

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/297293/viewspace-555659/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-19

  • 博文量
    23
  • 访问量
    46638