盖国强冯春培叶梁冯大辉编著
CIP Oracle 数据库性能优化 / 盖国强等编著. 北京 : 人民邮电出版社,2005.6 ISBN 7-115-13438-3 Ⅰ.O Ⅱ. 盖 Ⅲ. 关系数据库 数据库管理系统,Oracle Ⅳ.TP311.138 中国版本图书馆 CIP 数据核字 (2005) 第 048495 号 内容提要 本书面向实际应用, 从多个角度出发, 对 Oracle 优化中的很多关键问题进行了深入全面的探讨, 涵盖了 Oracle 优化的各个技术层面, 从内存优化 IO 规划及优化, 到 SQL 优化调整, 以较为完整的体系阐述了 Oracle 的优化技术 本书给出了大量取自实际工作现场的实例 在分析实例的过程中, 兼顾深度与广度, 不仅对实际问题的现象 产生原因和相关的原理进行了深入浅出的讲解, 更主要的是, 结合实际应用环境, 提供了一系列解决问题的思路和方法, 包括详细的操作步骤, 具有很强的实战性和可操作性, 满足面向实际应用的读者需求 Oracle 数据库性能优化 编著盖国强冯春培叶梁冯大辉 责任编辑杜洁 人民邮电出版社出版发行 邮编 100061 电子函件 315@ptpress.com.cn 网址 http://www.ptpress.com.cn 读者热线 010-67132692 北京密云春雷印刷厂印刷 新华书店总店北京发行所经销 北京市崇文区夕照寺街 14 号 开本 :787 1092 1/16 印张 :31.5 字数 :763 千字印数 :1 4 000 册 2005 年 6 月第 1 版 2005 年 6 月北京第 1 次印刷 ISBN 7-115-13438-3/TP 4674 定价 :65.00 元本书如有印装质量问题, 请与本社联系电话 :(010)67129223
2 ~ ~ ~ ~ ~
第 1 章 DBA 优化之路 3 3 4 4 5 6 7 8 8 8 9 10 12 12 13 14 15 17 17 19 19 20
2 20 第 2 章 Statspack 高级调整 23 24 24 26 27 第 3 章 Statspack 使用的几个误区 33 33 35 36 36 37 第 4 章 TKPROF 工具使用简介 39 39 40 41 第 5 章使用 Oracle 的等待事件检测性能瓶颈 45 46 46 48 48 50 50 51 54 59 59 第 6 章使用 SQL_TRACE/10046 事件进行数据库诊断 63 63 63 67 68 68 69 69
3 69 70 71 72 73 73 73 74 74 76 77 78 78 78 80 82 83 85 第 7 章表空间的存储管理与优化技术 89 89 90 90 91 92 92 92 94 94 95 95 96 98 98 98 99 99
4 99 99 第 8 章关于 Oracle 数据库中行迁移 / 行链接的问题 101 101 106 108 第 9 章 HWM 与数据库性能的探讨 121 121 122 128 131 135 135 136 137 137 142 143 148 148 148 152 第 10 章调整 I/O 相关的等待 153 153 154 157 158 163 164 166 169 第 11 章 Oracle 在 Solaris 的 VxFS 上的异步 I/O 问题 171 171 171 172 173
5 174 第 12 章关于 Freelists 和 Freelist Groups 的研究 177 177 178 178 180 182 184 185 186 186 第 13 章自动 PGA 管理 原理及优化 193 193 199 201 第 14 章 32bit Oracle SGA 扩展原理和 SGA 与 PGA 的制约关系 203 203 204 207 第 15 章 KEEP 池和 RECYCLE 池 213 213 214 215 219 221 第 16 章深度分析数据库的热点块问题 223 223 223 224 228 230
6 231 第 17 章 Shared Pool 原理及性能分析 233 233 233 234 235 240 244 244 245 246 247 247 249 249 252 253 258 259 267 269 第 18 章一次性能调整过程总结 273 273 273 273 274 275 275 281 第 19 章电信业 Oracle 优化手记 283 283 285 286 287
7 = = 287 288 289 289 289 290 第 20 章一次诊断和解决 CPU 利用率高的问题分析 291 291 292 296 第 21 章一次异常内存消耗问题的诊断及解决 297 297 297 297 297 299 300 302 303 305 第 22 章如何捕获问题 SQL 解决过度 CPU 消耗问题 307 307 308 309 309 311 313 314 315 317 第 23 章一条 SQL 导致数据库整体性能下降的诊断及解决 319 319 319
8 第 24 章 Library Cache Lock 成因和解决方法的探讨 327 327 327 327 328 328 328 329 329 329 330 330 331 341 348 第 25 章 Oracle 数据库优化之索引 (Index) 简介 351 352 353 360 363 第 26 章 CBO 成本计算初探 367 367 369 371 371 373 374 375 第 27 章 Bitmap 索引 377 377 378 380 380 382
9 382 384 385 390 390 390 391 391 第 28 章翻页 SQL 优化实例 395 395 395 第 29 章使用物化视图进行翻页性能调整 405 405 405 406 407 409 410 412 第 30 章如何给 Large Delete 操作提速近千倍 413 + 413 413 413 414 414 415 415 420 420 422 423 424 426 430 第 31 章 Web 分页与优化技术 431 431
10 431 434 439 445 450 450 452 455 456 第 32 章 Oracle 数据封锁机制研究 457 457 457 458 459 461 461 462 463 463 465 466 468 470 472 474 475 475 476 478 478
第 1 章 DBA 优化之路第 2 章 Statspack 高级调整第 3 章 Statspack 使用的几个误区第 4 章 TKPROF 工具使用简介第 5 章使用 Oracle 的等待事件检测性能瓶颈第 6 章使用 SQL_TRACE/10046 事件进行数据库诊断
1 编者按 : 怎样学习 Oracle, 怎样着手优化数据库, 这一直是一个反复被提及和讨论的问题, 本章并非介绍具体的方法论, 而是一篇过来人的经验之谈, 希望大家能够从本章的建议以及推荐中, 找到一条更为平坦的优化之路 1.1 学习的建议
4 1.2 工具推荐 * * * = * 1.3 关于操作系统方面的建议
5 1.4 关于 Oracle 初始化参数的调整
6 1.5 关于 Statspack 的若干建议
7 = 1.6 关于 logmnr 在调优中的运用 sql>exec dbms_logmnr_d.build('esal.ora','/home/oracle/logmnr'); sql>exec dbms_logmnr.add_logfile('&log_file',dbms_logmnr.new); sql>exec dbms_logmnr.add_logfile('&log_file',dbms_logmnr.addfile); sql>exec dbms_logmnr.start_logmnr('/home/oracle/logmnr/esal.ora'); sql>exec dbms_logmnr.end_logmnr();
8 1.7 关于 materialized view 在调优中的运用 1.8 关于 Stored Outline 在 SQL 优化中的运用 = = = 1.9 用 dbms_profiler 调优存储过程 sql>@?/rdbms/admin/profload
sql>@?/rdbms/admin/proftab 9 sql>@?/plsql/demo/profrep declare v_run number; begin dbms_profiler.start_profiler(run_number=>v_run); &procedure_name;-- 输入你要测试的过程名称 dbms_profiler.stop_profiler; dbms_profiler.rollup_run(v_run); prof_report_utilities.print_run(v_run); end; / 1.10 优化前的准备工作
10 1.11 如何对 SQL 进行调整及优化 set lines 99 col sql_text format a81 col bgets_per format 99999999.9 set long 99999999999 set pagesize 9999 select address,hash_value,disk_reads,elapsed_time/1000000 as
"elapsd_time(s)",cpu_time/1000000 as "cpu_time(s)", buffer_gets/executions bgets_per,first_load_time,sql_text from v$sql where executions > 0 11 and (disk_reads/executions > 500 or buffer_gets/executions > 20000); > > = * * * * * * set autot[race] {off on trace[only]}[exp[lain]] [stat[istics]] explain plan [set statement_id = &item_id] for &sql; select * from table(dbms_xplan.display); = =
12 1.12 表结构优化实例 1.13 如何对 session 进行跟踪 alter session set sql_trace=true/false exec dbms_system.set_sql_trace_in_session(&sid,&serial#,&sql_trace); alter session set events '&event trace name context forever,level &level'; alter session set events '&event trace name context off'; exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,''); oradebug event 10046 trace name context forever,level 12
13 1.14 基于等待事件的性能诊断方法 = = = -- 求等待事件及其对应的 latch col event format a32 col name format a32 select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name from v$session_wait sw,v$latch l where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' and sw.p2 = l.latch#(+); -- 求等待事件及其热点对象 col owner format a18 col segment_name format a32 col segment_type format a32 select owner,segment_name,segment_type from DBA_extents where file_id = &file_id and &block_id between block_id and block_id + &blocks - 1; -- 综合以上两条 SQL, 同时显示 latch 及热点对象 ( 速度较慢 ) select sw.sid,event,l.name,de.segment_name from v$session_wait sw,v$latch l,dba_extents de where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%' and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1; -- 如果是非空闲等待事件, 通过等待会话的 SID 可以求出该会话在执行的 SQL select sql_text from v$sqltext_with_newlines st,v$session se where st.address=se.sql_address and st.hash_value=se.sql_hash_value and se.sid =&wait_sid order by piece;
14 1.15 基于资源限制的性能诊断方法 = alter profile default limit logical_reads_per_call 300000; alter profile default limit logical_reads_per_call unlimited; sql>select * from DBA_profiles; PROFILE RESOURCE_NAME RESOURCE LIMIT ---------------------------------- -------- ---------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL 29999999 DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3
15 1.16 如何减少共享池的碎片
16 SQL>select id,name,age from test4; -- 在 select 列表字段最好以它在表中创建时的顺序出现 SQL>select id,name,age from test4 where id = 9 or name ='00009' or age = 1; -- 在 where 子句中字段最好以它在表中创建时的顺序出现 SQL>select id,age name from test4 where id = 9 and name ='00009' and age = 1; -- 这种情况就会给其他人造成混淆, 结果就会出现不应有的硬解析 SQL>SELECT id,name,age, FROM test4 where id = 9 or name='oooo9' or age=1; -- 这个地方应该注意的是关键字必须小写 SQL> insert into test4(id,name,age) values (:id,:name,:age); SQL> insert into test4(id,name) values(:id,:name); -- 这两个 SQL 中的后者与前者的字段列表不符, 通常的写法如下 : SQL>insert into test4(id,name,age) values (:id,:name,:age) -- 这个地方出现的 :age 就在绑定时传入 null 或其他可以默认的值即可
17 1.17 监控表及索引的意义 alter table &table_name monitoring; alter table &table_name nomonitoring; alter index &index_name monitoring usage; alter index &index_name nomonitoring usage; 1.18 通过优化 SQL 消除 temp 表空间膨胀 select se.username,p.spid,su.blocks*8192/1024/1024 Space,sql_text from v$sort_usage su,v$session se,v$sql s,v$process p where su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr and p.addr = se.paddr order by se.username,se.sid;
18 SQL>alter tablespace temp increase 1; SQL>alter tablespace temp increase 0; SQL>alter tablespace temp coalesce; = #+ # # # SQL>alter session set events 'immediate trace name DROP_SEGMENTS level n' SQL>alter database tempfile '...' drop; SQL>alter tablespace temp add tempfile '...' size...m; Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 99.99 Buffer Hit %: 99.36 In-memory Sort %: 100.00 Library Hit %: 99.87 Soft Parse %: 99.84 Execute to Parse %: 1.17 Latch Hit %: 99.96 Parse CPU to Parse Elapsd %: 92.00 % Non-Parse CPU: 94.59 = = event="10061 trace name context forever, level 10" // 禁止回收排序段 event="10269 trace name context forever, level 10" // 禁止合并碎片
19 1.19 理解 compress 选项在优化上的作用 SQL> create table compress_1 compress as select * from dba_objects; SQL> create table compress_2 as select * from dba_objects; SQL> exec show_space('compress_1','auto'); Total Blocks...64 Total Bytes...524288 Unused Blocks...6 Unused Bytes...49152 Last Used Ext FileId...5 Last Used Ext BlockId...544 Last Used Block...2 SQL> exec show_space('compress_2','auto'); Total Blocks...256 Total Bytes...2097152 Unused Blocks...110 Unused Bytes...901120 Last Used Ext FileId...5 Last Used Ext BlockId...11656 Last Used Block...18 1.20 关于在线重定义 table 的建议
20 1.21 关于分区表在数据库设计时的建议 1.22 关于 DataGuard 在高可用方面的建议
21 作者简介 谢中辉, 曾任 ITPUB MS SQL Server 版版主, 现任 ITPUB Oracle 开发版版主 曾经任职于广州大型港资纺织漂染企业, 开发过基于 Oracle 的 ERP 系统 在数据库建模 备份恢复 系统调优 SQL 优化 数据迁移 PL/SQL 开发等方面有丰富的经验 目前任职于某大型电子商务网站, 负责数据库的故障诊断处理 异构数据迁移 新业务实现规划 系统调优与 SQL 优化等工作
2
24 2.1 Statspack 高级调整译文 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- db file sequential read 18,977,104 22,379,571 82.29 latch free 4,016,773 2,598,496 9.55 log file sync 1,057,224 733,490 2.70 log file parallel write 1,054,006 503,695 1.85 db file parallel write 1,221,755 404,230 1.49
25
26
27 表 2-1 等待问题及可能解决方法等待问题可能的解决方法 Sequential Read 表明有很多索引读 调整代码 ( 特别是表连接部分 ) Scattered Read 表明有很多全表扫描 调整代码 将小表放入内存 Free Buffer 增大 DB_CACHE_SIZE 加速检查点和调整代码 Buffer Busy 段头 增加 freelists 或者 freelist groups Buffer Busy 数据块 分离 热点 数据 采用反向关键字索引 采用小的数据块 Buffer Busy 数据块 增大 initrans 和 maxtrans Buffer Busy undo header 增加回滚段 Buffer Busy undo block 增加提交频度 增大回滚段 Latch Free 研究 Latch 细节 ( 可以参考下文 ) Enqueue - ST 使用本地表空间或者预先分配大扩展 Enqueue - HW 预先分配扩展于高水位线之上 Enqueue - TX4 增大表或索引的 initrans 和 maxtrans Enqueue - TM 为外键建立索引, 查看应用程序中的表锁 Log Buffer Space 增大日志缓冲区, 重做日志放在快速磁盘上 Log File Switch 归档设备太慢或者太满, 增加或者扩大重做日志 Log File Sync 每次提交更多记录 更快的存放重做日志的磁盘 裸设备 Idle Event 忽略 * 2.2 关于 Latch
28 > = Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ KCL freelist latch 4,924 0.0 0 cache buffer handles 968,992 0.0 0.0 0 cache buffers chains 761,708,539 0.0 0.4 21,519,841 0.0 cache buffers lru chain 8,111,269 0.1 0.8 19,834,466 0.1 library cache 67,602,665 2.2 2.0 213,590 0.8 redo allocation 12,446,986 0. 0.0 0 redo copy 320 0.0 10,335,430 0.1 user lock 1,973 0.3 1.2 0
29
30 表 2-2 Latch 问题 Library Cache Shared Pool Redo Allocation Redo Copy Row Cache Objects Cache Buffers Chain Cache Buffers LRU Chain Latch 问题及可能解决问题可能的解决方法使用绑定变量, 调整 SHARED_POOL_SIZE 使用绑定变量, 调整 SHARED_POOL_SIZE 最小化 redo 生成并避免不必要的提交增大 _LOG_SIMULTANEOUS_COPIES 增大共享池 _DB_BLOCK_HASH_BUCKETS 应被增大或变为质数设置 DB_BLOCK_LRU_LATCHES 或者使用多个缓冲区池 参考信息 1.Steve Adams. Oracle 8i Internal Services for Waits, Latches, Locks, and Memory. O'Reilly UK,2003 2.Oracle Doc ID: 61998.1, 39017.1 3.Connie Dialeris, Graham Wood. Performance Tuning with Statspack White Paper, 2000 4.Notes from Richard Powell, Cecilia Gervasio, Russell Green and Patrick Tearle 5.Statspack checklist; Kevin Loney, Randy Swanson, Bob Yingst, 2002 6.Rich Niemiec, IOUG Masters Tuning Class, 2002 7.Richard J.Niemiec. Oracle Performance Tuning Tips and Techniques. McGraw-Hill. 1999 8. Richard J.Niemiec. Oracle 9i Performance Tuning Tips and Techniques. McGraw-Hill. 2003
31 作者简介 Richard J.Niemiec,TUSC(The Ultimate Software Consultants) 公司 CEO, 著有 Oracle Performance Tuning Tips and Techniques 一书 译者简介 吕学勇,20 世纪 80 年代在美国攻读组合算法,80 年代后期起在 AT&T 贝尔实验室 芝加哥期货交易中心等地从事关系型数据库方面的工作,1996 年起专职任 Oracle DBA/ 开发员以及后来的高级顾问 2001 年回国服务, 现任 UT 斯达康公司高级顾问
3 编者按 :Statspack 是数据库优化中经常用到的工具, Oracle 数据库 DBA 专题技术精粹 一书中已包含了 Statspack 使用指南 一文, 大家可以从 ITPUB 上找到相关的电子文档作为参考 本章从一些常见的使用误区入手, 对 Statspack 的使用进行了独到的阐述 3.1 以命中率为主衡量性能问题 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
34 --------------- ------------ -------------- ------ -------- --------- ---------- 15,221,088 4311 35,30.7 85.9 109.86 108.13 3159590556 SELECT COUNT (*) FROM (SELECT f2.y FROM foo1 f1, foo2 f2 WHERE f1.x = f2.x) select count(*) from ( select /*+ NO_MERGE ORDERED FULL(f1) INDEX(f2 ix1) USE_NL(f2) */ f2.y from foo1 f1, foo2 f2 where f1.x = f2.x ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 34.59 33.76 0 440025 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 34.59 33.76 0 440025 0 1 Misses in library cache during parse: 0 Optimizer goal: FIRST_ROWS Parsing user id: STAT Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=440025 r=0 w=0 time=33763363 us) 1209 VIEW (cr=440025 r=0 w=0 time=33762090 us) 1209 NESTED LOOPS (cr=440025 r=0 w=0 time=33759692 us) 10000 TABLE ACCESS FULL FOO1 (cr=24 r=0 w=0 time=19901 us) 1209 TABLE ACCESS FULL FOO2 (cr=440001 r=0 w=0 time=33708307 us) + select count(*) from ( select /*+ FULL(f2) FULL(f1) */ f2.y from foo1 f1, foo2 f2 where f1.x = f2.x ) call count cpu elapsed disk query current rows
35 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.13 0.12 0 67 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.13 0.12 0 67 0 1 Misses in library cache during parse: 0 Optimizer goal: FIRST_ROWS Parsing user id: SYS Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=67 r=0 w=0 time=122848 us) 1209 HASH JOIN (cr=67 r=0 w=0 time=121743 us) 10000 TABLE ACCESS FULL FOO1 (cr=23 r=0 w=0 time=10827 us) 20000 TABLE ACCESS FULL FOO2 (cr=44 r=0 w=0 time=21011 us) + = + = 注意 逻辑 I/O(LIO) 与物理 I/O(PIO) LIO(Logical I/O) 是指数据库核心对 Buffer Cache 中的数据块的读取操作 如果要获取的对象在 Buffer Cache 中不存在, 则 LIO 可能会产生 PIO v$sesstat 视图中的 db block gets 和 consistent gets 给出关于特定会话的 LIO 的汇总信息 consistent gets 代表对特定版本或时间的 block 的访问 select;db block gets 代表对最新的或当前的 block 的访问, 通常用于 insert update 和 delete 操作 PIO(Physical I/O) 不能从 Buffer Cache 中得到的块读取操作, 可能是因为块不存在或者是因为是跳过 Buffer Cache 的直接 I/O 类型的 I/O 操作 可以简单地理解为 PIO 就是磁盘读取 可以从 v$sesstat 中得到名为 Physical Reads 的统计信息 3.2 快照的采样时间间隔问题 ~ ~ ~
36 Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 1006 09-Nov-04 09:00:02 333 1.8 End Snap: 1044 09-Nov-04 18:00:02 338 2.4 Elapsed: 540.00 (mins) ~ 3.3 以偏概全 3.4 关于 TIMED_STATISTICS 参数的设定
37 = 3.5 你成了泄密者 SQL> ALTER USER perfstat ACCOUNT LOCK; 参考信息 1.Advanced Tuning with Statspack http://otn.oracle.com/oramag/oracle/03-jan/o13expert.html 2.Performance Tuning with Statspack PartII http://otn.oracle.com/deploy/performance/pdf/statspack_tuning_otn_new.pdf 3.Performance Tuning with Statspack PartI http://otn.oracle.com/deploy/performance/pdf/statspack.pdf 作者简介 冯大辉, 网名 Fenng 目前关注于如何利用 Oracle 数据库有效地进行企业应用构建 对 Oracle RDBMS Tuning Trouble Shooting 有浓厚的兴趣 个人技术站点 :http://www.dbanotes.net/ 电子邮件 :dbanotes@gmail.com
4 4.1 TKPROF 工具简介 Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use 'schema.tablename' with 'explain=' option. explain=user/password Connect to ORACLE and issue EXPLAIN PLAN. print=integer List only the first 'integer' SQL statements. aggregate=yes no insert=filename List SQL statements and data inside INSERT statements. sys=no TKPROF does not list SQL statements run as user SYS. record=filename Record non-recursive statements found in the trace file. waits=yes no Record summary for any wait events found in the trace file. sort=option Set of zero or more of the following sort options: prscnt number of times parse was called prscpu cpu time parsing prsela elapsed time parsing prsdsk number of disk reads during parse prsqry number of buffers for consistent read during parse prscu number of buffers for current read during parse prsmis number of misses in library cache during parse execnt number of execute was called execpu cpu time spent executing exeela elapsed time executing exedsk number of disk reads during execute exeqry number of buffers for consistent read during execute
40 execu number of buffers for current read during execute exerow number of rows processed during execute exemis number of library cache misses during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchela elapsed time fetching fchdsk number of disk reads during fetch fchqry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchrow number of rows fetched userid userid of user that parsed the cursor = 4.2 TKPROF 工具的使用步骤
41 = SQL> alter system set timed_statistics=false scope=both; 系统已更改 方法一 : = 方法二 : * SQL> alter session set sql_trace=true; 会话已更改 SQL>exec dbms_system.set_sql_trace_in_session(sid, serial#,true); PL/SQL 过程已成功完成 tkprof tracefile outfile [explain=user/password] [options...] 4.3 TKPROF 工具如何分析 trace 文件 load averages: 1.53, 1.37, 1.39 db2 23:11:15 246 processes: 236 sleeping, 1 running, 2 zombie, 4 stopped, 3 on cpu CPU states: 68.0% idle, 17.3% user, 3.0% kernel, 11.6% iowait, 0.0% swap Memory: 16.0G real, 3.4G free, 9.7G swap in use, 11.0G swap free PID USERNAME THR PR NCE SIZE RES STATE TIME FLTS CPU COMMAND 14483 oracle 1 51 0 8.8G 8.8G sleep 77.1H 1 5.69% oracle 28222 oracle 1 52 0 8.8G 8.8G cpu18 93:55 2742 3.32% oracle 3722 oracle 1 59 0 8.8G 8.8G sleep 157:41 0 0.45% oracle 16077 oracle 1 59 0 8.8G 8.8G sleep 17.1H 0 0.34% oracle 12687 oracle 1 59 0 8.8G 8.8G sleep 0:07 0 0.29% oracle 17781 oracle 1 49 0 8.8G 8.8G run 91:11 8 0.24% oracle 2359 oracle 1 59 0 8.8G 8.8G cpu19 524:53 0 0.12% oracle 6559 oracle 1 59 0 8.8G 8.8G sleep 237:41 0 0.10% oracle
42 2242 oracle 1 59 0 8.8G 8.8G sleep 980:56 0 0.09% oracle 2356 oracle 1 59 0 8.8G 8.8G sleep 121:31 0 0.08% oracle 16106 oracle 1 59 0 8.8G 8.8G sleep 168:44 0 0.05% oracle 11432 oracle 1 49 0 2576K 1680K cpu11 0:11 0 0.05% top 2333 oracle 1 59 0 8.9G 8.8G sleep 159:03 0 0.05% oracle 2321 oracle 1 59 0 8.8G 8.8G sleep 78:20 0 0.04% oracle 2282 oracle 1 59 0 8.8G 8.8G sleep 424:57 0 0.03% oracle # SQL> select s.sid,s.serial# from v$session s,v$process p 2 where s.paddr=p.addr and p.spid='14483'; SID SERIAL# ---------- ---------- 101 25695 SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(101,25695,true); PL/SQL procedure successfully completed. $tkprof orcl_ora_14483.trc allan.txt explain=system/manager aggregate=yes sys=no waits=yes sort=fchela TKPROF: Release 9.2.0.4.0 - Production on Sun Dec 5 22:27:28 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- Parse 20 0.01 0.02 0 58 0 0 Execute 13197 0.81 0.90 17 7436 6316 1484 Fetch 12944 22.86 22.10 20 2205941 0 8972 ------- ------ -------- ---------- ---------- ---------- ---------- total 26161 23.68 23.02 37 2213435 6316 10456 Misses in library cache during parse: 14 ************************************************************************* Trace file: orcl_ora_14483.trc Trace file compatibility: 9.00.01 Sort options: fchela 1 session in tracefile. 671 user SQL statements in trace file. 20 internal SQL statements in trace file. 691 SQL statements in trace file. 42 unique SQL statements in trace file. 26 SQL statements EXPLAINed using schema: SYSTEM.prof$plan_table Default table was used. Table was created. Table was dropped. 67054 lines in trace file.
43 Logical Reads =Consistent Gets+DB Block Gets + + + = + = = = = UPDATE test set Amount = Amount - :b2 where TestSequenceID = :b1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 731 22.43 21.52 7 2194 974 731 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- total 731 0.15 0.18 7 2194 974 731 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 43 (TEST) (recursive depth: 1) Rows Execution Plan ------- --------------------------------------------------- 0 UPDATE STATEMENT GOAL: CHOOSE 0 UPDATE OF 'TEST' 0 INDEX (UNIQUE SCAN) OF 'PK_TEST' (UNIQUE) select * from ErrorEvent where rownum<1000 and ERRORCOUNT<100 order by EventID call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- Parse 12 3.02 6.04 0 101 0 0
44 Execute 12 0.00 0.03 0 0 0 0 Fetch 12 6.41 5.47 88 143290 0 10 ------- ------ -------- ---------- ---------- ---------- ---------- total 36 9.43 11.55 88 143391 0 10 Misses in library cache during parse: 11 Optimizer goal: CHOOSE Parsing user id: 43 (TEST) Rows Row Source Operation ------- --------------------------------------------------- 0 SORT ORDER BY (cr=445 r=1 w=0 time=18059 us) 0 COUNT STOPKEY (cr=445 r=1 w=0 time=17987 us) 0 TABLE ACCESS FULL ERROREVENT (cr=445 r=1 w=0 time=17983 us) 0 SELECT STATEMENT GOAL: CHOOSE 0 SORT (ORDER BY) 0 COUNT (STOPKEY) 0 TABLE ACCESS (FULL) OF 'ERROREVENT' 作者简介 叶梁, 网名 coolyl, 现任 ITPUB Oracle 管理版版主 曾任职于国内某大型软件企业做 Oracle 数据库的技术支持, 客户遍及全国各个行业, 尤其是电信 政府 金融行业 现任职于某外资电信企业华北区分公司, 从事 DBA 工作, 负责华北区 40 多个数据库系统的维护, 对大型数据库管理经验丰富 擅长数据库的维护, 对于数据库的安装, 调整, 备份方面有自己独到的经验 同时也给一些国内的大型企业做过 Oracle 的培训, 有一定的培训经验 曾做过很多大型项目的数据库维护和支持工作, 对 Oracle 的维护有丰富的实际经验, 善于现场解决问题 Oracle 数据库 DBA 专题技术精粹 一书的主编及主要作者
5
46 5.1 判断等待事件的相关视图
47 SQL> @syssum.sql Wait Event Time Waited %Time w Waits %Waited ----------------------------------- ----------- ------- --------- ------- db file scattered read 1463 69.61 17528 94.96 buffer busy waits 291 13.84 65 0.35 PL/SQL lock timer 160 7.60 307 1.66 latch free 130 6.18 127 0.69 log file parallel write 37 1.76 269 1.46 db file sequential read 20 0.96 161 0.87 log file switch completion 1 0.02 0 0.00 log file sync 0 0.02 1 0.00 library cache pin 0 0.01 0 0.00 log file sequential read 0 0.01 0 0.00 log file single write 0 0.00 0 0.00 process startup 0 0.00 0 0.00 db file single write 0 0.00 0 0.00 LGWR wait for redo copy 0 0.00 0 0.00 enqueue 0 0.00 0 0.00 library cache load lock 0 0.00 0 0.00 db file parallel write 0 0.00 2 0.01 direct path write 0 0.00 0 0.00 SQL> @cr_base.sql SQL> @in_base.sql 执行用户应用 SQL> @re_base.sql Wait Event Time Waited %Time w Waits %Waited ----------------------------------- ----------- ------- -------- ------- log file parallel write 4.360 73.15 598 72.57 buffer busy waits 0.970 16.28 189 22.94 db file sequential read 0.160 2.68 7 0.85 latch free 0.130 2.18 9 1.09 log file sync 0.060 1.01 1 0.12 enqueue 0.000 0.00 7 0.85 LGWR wait for redo copy 0.000 0.00 1 0.12 direct path write 0.000 0.00 1 0.12 db file scattered read 0.000 0.00 0 0.00 db file single write 0.000 0.00 0 0.00 direct path read 0.000 0.00 0 0.00 library cache load lock 0.000 0.00 0 0.00 library cache pin 0.000 0.00 0 0.00 log file sequential read 0.000 0.00 0 0.00 log file single write 0.000 0.00 0 0.00 log file switch completion 0.000 0.00 0 0.00 process startup 0.000 0.00 0 0.00
48 #
49 SQL> @seswa.sql Num. Sess. Wait Event Waited So Far (sec) Waiting ---------------------------------------- ------------------- ---------- rdbms ipc message 1,012 5 latch free 0 2 smon timer 301 1 buffer busy waits 0 1 db file scattered read 0 165 pmon timer 0 1 6 rows selected.
50 SQL> select sid,event,p1,p2,p3 2 from v$session_wait 3 where event like 'db%file%scat%'; ID Wait Event P1 P2 P3 ---- ----------------------------------- ------------ --------- ----- 8 db file scattered read 8 6572 16 10 db file scattered read 8 6413 16 * * * 5.2 应该怎么考虑进行优化
51 5.3 主要等待事件 1. 空闲等待 * * 2. 非空闲等待事件
52 表 5-1 等待事件 buffer busy waits db file parallel write db file scattered read db file sequential read db file single write direct path read direct path write enqueue free buffer inspected 非空闲等待事件的基本含义等待事件描述表示在等待对数据高速缓存区的访问, 这种等待事件通常出现在会话读取数据到 buffer 中或者修改 buffer 中的数据时, 例如 DBWR 正在写一些数据块到数据文件的同时, 其他进程需要去读取相应的数据块 同时也可能表示着在表上设置的 free lists 太少, 不能支持大量并发的 Insert 操作 在 v$session_wait 视图的 P1 字段值表示相关数据块所在的文件编号,P2 则表示文件上的块编号, 通过这些信息与 dba_data_files 和 dba_extents 的联合查询就可以很快定位到发生竞争的存储对象, 从而进一步确定问题的根源与 DBWR 进程相关的等待, 一般都代表了 I/O 能力出现了问题 通常与配置的多个 DBWR 进程或者 DBWR 的 I/O slaves 个数有关, 当然也可能意味着在设备上存在着 I/O 竞争表示发生了与全表扫描相关的等待 通常意味着全表扫描过多, 或者 I/O 能力不足, 或者 I/O 竞争表示发生了与索引扫描相关的等待 同样意味着 I/O 出现了问题, 通常表示 I/O 竞争或者 I/O 需求太多表示在检查点发生时与文件头写操作相关的等待 通常与检查点同步数据文件头时文件号的紊乱有关表示与直接 I/O 读相关的等待 当直接读数据到 PGA 内存时, direct path read 出现 这种类型的读请求典型地作为 : 排序 IO( 当排序不能在内存中完成的时候 ) 并行 Slave 查询或者预先读请求等 通常这种等待与 I/O 能力或者 I/O 竞争有关同 direct path read, 只是操作为写表示与内部队列机制相关的等待, 例如对保护内部资源或者组件的锁的请求等, 一种并发的保护机制表示在将数据读入数据高速缓存区的时候等待进程找到足够大的内存空间 通常这类等待表示数据高速缓存区偏小
53 续表等待事件等待事件描述 free buffer waits 表示数据高速缓存区缺少内存空间 通常与数据高速缓存区内存太小或者脏数据写出太慢有关 这种情况下, 可以考虑增大数据高速缓存区或者通过设置更多地 DBWR 来增加脏数据的写能力 latch free 表示某个锁存器上发生了竞争 首先应该确保已经提供了足够多的 Latch 数, 如果仍然发生这种等待事件, 那么应该进一步确定是那种锁存器上发生了竞争 ( 在 v$session_wait 上的 P2 字段表示了锁存器的标号 ), 然后再判断是什么引起了这种锁存器竞争 大多数锁存器竞争都不是简单的由锁存器引起的, 而是与锁存器相关的组件引起的, 所以需要找到具体导致竞争的根本 例如, 如果发生了 library cache latch 竞争, 那么通常都表示着库高速缓存的配置不合理, 或者 SQL 语句书写不合理, 带来了大量的硬解析 library cache load lock 表示在将对象装在到库高速缓存时出现了等待 这种事件通常代表着发生了负荷很重的语句重载或者装载, 可能由于 SQL 语句没有共享或者共享池区域偏小造成的 library cache lock 表示与访问库高速缓存的多个并发进程相关的等待 通常表示不合理的共享池大小 library cache pin 这个等待事件也与库高速缓存的并发性有关, 当库高速缓存中的对象被修改或者被检测的时候发生 log buffer space 表示日志缓冲区出现了空间等待事件 这种等待事件意味着写日志缓冲区的时候得不到相应的内存空间, 通常发生在日志缓冲区太小或者 LGWR 进程鞋太慢的时候 ( 可能由其他原因造成 ) log file parallel write 表示等待 LGWR 向操作系统请求 I/O 开始直到完成 IO 在触发 LGWR 写的情况下如 3 秒 1/3 1MB DBWR 写之前可能发生 这种事件发生通常表示日志文件发生了 I/O 竞争或者文件所在的驱动器较慢 log file single write 表示写日志文件头块时出现了等待 一般都是发生在检查点发生时 log file switch(archiving 由于归档过慢造成日志无法切换而发生的等待 这种等待事件的原因可能比较 needed) 多, 最主要的原因就是归档速度赶不上日志切换的速度 可能的原因包括了重做日志文件太小, 重做日志组太少, 归档能力太低, 归档文件发生了 I/O 竞争, 归档进程挂起, 或者归档日志文件放在了慢速磁盘设备上等 log file switch(checkpoint 表示在日志切换时相应文件上的检查点还没有完成 一般意味着日志文件太小造 incomplete) 成日志切换太快或者其他原因 ( 例如 DBWR 没有写完脏数据 ) 造成检查点太慢 log file sync 表示当服务进程发出 commit 或 rollback 命令后, 直到 LGWR 完成相关日志写操作这段时间的等待 如果有多个服务进程同时发出这种命令,LGWR 不能及时完成日志的写操作, 就有可能造成这种等待 transaction 表示发生了一个阻塞回滚操作的等待 undo segment extension 表示在等待回滚段的动态扩展 这表示可能事务量过大, 同时也意味着可能回滚段的初始大小不是最优,MINEXTENTS 设置得偏小 考虑减少事务, 或者使用最小区数更大的回滚段 *
54 * 5.4 案例分析 SQL> @syssum.sql Wait Event Time Waited %Time w Waits %Waited ----------------------------------- ----------- ------- --------- ------- db file scattered read 1501 66.89 17703 92.82 buffer busy waits 309 13.77 78 0.41 PL/SQL lock timer 189 8.40 354 1.85 latch free 138 6.14 134 0.70 log file parallel write 51 2.25 304 1.59 db file sequential read 29 1.31 179 0.94 enqueue 17 0.76 0 0.00 free buffer waits 7 0.30 1 0.00 log file sync 1 0.05 1 0.01 write complete waits 1 0.05 0 0.00 log file switch completion 1 0.05 0 0.00 log buffer space 1 0.03 0 0.00 library cache pin 0 0.01 0 0.00 log file sequential read 0 0.01 0 0.00 log file single write 0 0.00 0 0.00 undo segment extension 0 0.00 314 1.65 LGWR wait for redo copy 0 0.00 0 0.00 process startup 0 0.00 0 0.00 db file single write 0 0.00 0 0.00 library cache load lock 0 0.00 0 0.00 db file parallel write 0 0.00 2 0.01 direct path write 0 0.00 0 0.00 direct path read 0 0.00 0 0.00 buffer deadlock 0 0.00 0 0.00
55 SQL> @cr_base.sql SQL> @in_base.sql 执行用户应用 一段时间之后 SQL> @re_base.sql Wait Event Time Waited %Time w Waits %Waited ----------------------------------- ----------- ------- -------- ------- log file parallel write 23.350 89.70 2164 96.78 free buffer waits 1.020 3.92 1 0.04 db file sequential read 0.390 1.50 14 0.63 latch free 0.080 0.31 5 0.22 log file sync 0.040 0.15 1 0.04 db file parallel write 0.000 0.00 23 1.03 direct path write 0.000 0.00 1 0.04 LGWR wait for redo copy 0.000 0.00 0 0.00 buffer busy waits 0.000 0.00 0 0.00 buffer deadlock 0.000 0.00 0 0.00 db file scattered read 0.000 0.00 0 0.00 db file single write 0.000 0.00 0 0.00 direct path read 0.000 0.00 0 0.00 enqueue 0.000 0.00 0 0.00 library cache load lock 0.000 0.00 0 0.00 library cache pin 0.000 0.00 0 0.00 log buffer space 0.000 0.00 0 0.00 log file sequential read 0.000 0.00 0 0.00 log file single write 0.000 0.00 0 0.00 log file switch completion 0.000 0.00 0 0.00 process startup 0.000 0.00 0 0.00 undo segment extension 0.000 0.00 0 0.00 write complete waits 0.000 0.00 0 0.00 23 rows selected.
56 SQL> @seswt.sql log SID Wait Event Wait Stat W'd So Far (secs) Time W'd (secs) ---- --------------------------- -------- ----------------- --------------- 13 log file parallel write WAITING 0 0 97 log file parallel write WAITING 0 0 176 log file parallel write WAITING 0 0 122 log file parallel write WAITING 0 0 4 rows selected.
57 SQL> @sqls1.sql 1000 1000 Stmt Addr Disk Rds Buff Gets Sorts Runs Body Loads -------------- ------------ ------------ ------- -------- ---------- 2188ED68 276,390,597 278,896,280 1 65,628 1 2187A230 42,435 42,315,278 11,021,345 1 21668D10 189,628 576,659 0 55 1 216DD948 92,147 1,054,969 0 3,267 1 2172F948 3,321 2,424,735 0 377,248 1 219BB408 15,231 70,876 0 48 1 2168C058 4,949 4,373 2 1 1 2168CBB8 4,295 4,387 1 1 1 216978EC 4,245 4,253 1 1 1 2181D5E8 4,179 4,249 1 1 1 2164CBB4 2,862 9,908 1 1 1 2198901C 1,611 91,968 0 48 1 1000 rows selected. SQL> @Sqlst.sql 13 SID User Nam CPU(sec) IO Read(k) IO Write(k) SQL Address ----- -------- -------- ----------- ------------ ----------- 13 WEBBER 0 11 5120 2168CBB8 1 rows selected.
58 SQL> @sqls2.sql 2168CBB8 SQL Statement Text ----------------------------------------------------------------- UPDATE HITS SET STATUS=:b1 WHERE to_char(status) = :b2 1 row selected. $ vmstat 10 10 procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 2 0 0 44736 1480 4300 23972 0 27 124 80 1729 204 17 7 76 0 2 0 44736 1168 4316 24276 0 0 244 35 2330 215 20 11 69 1 2 1 44984 1424 3832 24756 0 25 75 31 946 100 69 25 7 1 4 0 45412 1528 4796 24160 10 47 5 139 1258 280 32 12 57 1 1 0 45408 1080 4812 24524 13 0 12 107 1050 227 54 15 31 1 2 0 45708 1468 5740 23552 1 31 31 95 1110 197 10 4 86 2 5 0 45620 1048 8580 21008 13 0 35 150 1581 320 17 7 77 0 4 0 45540 736 11348 18408 22 0 93 128 1868 285 13 6 81 1 2 0 45540 848 13308 16288 0 0 128 153 2344 328 19 7 74 0 1 0 45528 1400 12412 16624 1 0 114 129 2049 280 19 5 76 0 3 0 45484 1196 12696 16508 5 1 100 129 1922 283 15 5 80 1 3 0 45484 1520 11884 16996 0 0 100 92 1643 215 14 5 81 1 4 0 45484 1496 12392 16512 0 0 45 98 1245 218 9 6 85 0 2 0 45484 732 11748 17924 0 0 67 114 1542 244 12 6 82
59 5.5 小结 5.6 附录 SELECT EVENT "Wait Event", TIME_WAITED "Time Waited", TIME_WAITED / (SELECT SUM(TIME_WAITED) TOTAL_WAITS "Waits", TOTAL_WAITS / (SELECT SUM(TOTAL_WAITS) from v$system_event order by 3 desc ; create table sys_b( EVENT VARCHAR2(64), TIME_WAITED NUMBER, TOTAL_WAITS NUMBER,); FROM v$system_event) "%Time waited", FROM v$system_event) "%Waited" create table sys_e( EVENT VARCHAR2(64), TIME_WAITED NUMBER, TOTAL_WAITS NUMBER,); insert into sys_b select EVENT, TIME_WAITED, TOTAL_WAITS from v$system_event;
60 insert into sys_e select EVENT, TIME_WAITED, TOTAL_WAITS from v$system_event; create table sys_dif as select e. EVENT e.time_waited - b.time_waited TIME_WAITED, e.total_waits - b.total_waits TOTAL_WAITS from sys_b b, sys_e e where b.event=e.event;; SELECT EVENT "Wait Event", TIME_WAITED "Time Waited", TIME_WAITED / (SELECT SUM(TIME_WAITED) FROM sys_dif) "%Time waited", TOTAL_WAITS "Waits", TOTAL_WAITS / (SELECT SUM(TOTAL_WAITS) FROM sys_dif) "%Waited" from sys_dif order by 3 desc ; drop table sys_dif; drop table sys_d; drop table sys_e; select EVENT "Wait Event", count(seconds_in_wait) "Waited So Far (sec)", count(sid) "Num Sess Waiting" from v$session_wait group by EVENT; SELECT SID, EVENT "Wait Event", STATE "Wait Stat", WAIT_TIME "W'd So Far (secs)", SECONDS_IN_WAIT "Time W'd (secs)" FROM v$session_wait WHERE EVENT LIKE '&a&' ORDER BY 5; SELECT * FROM (SELECT ADDRESS "Stmt Addr", DISK_READS "Disk Rds", BUFFER_GETS "Buff Gets", SORTS "Sorts", EXECUTIONS "Runs", LOADS "Body Loads" FROM V$SQLAREA WHERE DISK_READS > &A ORDER BY DISK_READS) WHERE ROWNUM < &B; select cpu.sid "SID", cpu.username "User Name", cpu.value "CPU(sec)", from reads.value "IO Read(k)", writes.value "IO Write(k)"
61 (select a.sid sid, a.username username, b.name, c.value value,a.serial# serial# from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='CPU used by this session' ) cpu, (select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical reads' ) reads, (select a.sid, a.username, b.name, c.value value from v$session a, v$statname b, v$sesstat c where a.sid = c.sid and b.statistic# = c.statistic# and b.name ='physical writes' ) writes where cpu.sid = reads.sid and reads.sid = writes.sid and cpu.username is not null; select SQL_TEXT "SQL Statement Text" from v$sqltext where ADDRESS=&a; 作者简介 Ora-600, 现任 ITPUB OCP 版块版主, 擅长 Oracle DBA 技术 需求分析 系统分析与设计 数据建模 代码开发等,8i/9i OCP 认证获得者 曾参与国土资源厅国土资源土地管理系统 土地整理预算系统 北京公交一卡通项目客服系统和 ED 卡管理系统 武威地区电力局的商务办公自动化系统和电力 MIS 系统 西北电力设计院商务办公自动化系统等项目, 负责 Oracle 数据库后台设计 规划 管理 开发报表系统 培训等各项工作, 曾在北京多家培训中心进行 Oracle OCP 课程讲授, 并为全国多个行业客户提供技术服务和支持
6 6.1 SQL_TRACE 及 10046 事件的基础介绍 表 6-1 参数类型 SQL_TRACE 的说明 布尔型 缺省值 false 参数类别 静态 取值范围 true false 注意 从 Oracle 10g 开始,SQL_TRACE 成为了动态参数
64 警告 设置初始化参数 SQL_TRACE 为 true 会对整个实例产生严重的性能影响, 所以在产品环境 中如非必要, 一定不要设置这个参数 如果只是对特定的会话启用跟踪, 可以使用 alter session 或 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 来设置 如果必须在数据库级启用 SQL_TRACE, 则需要保证以下条件以使对性能的影响最小化 : 1. 至少保证有 25% 的 CPU idle 2. 为 USER_DUMP_DEST 分配足够的空间 3. 条带化磁盘以减轻 IO 负担 注意 如果使用 alter session set sql_trace 来修改 session 级设置, 这个设置并不会在 v$parameter 动 态性能视图中体现出来, 所以, 这个参数仍然被认为是静态参数 SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited; Session altered. 1. 在全局启用 SQL_TRACE sql_trace = true 提示 通过在全局启用 SQL_TRACE, 可以跟踪到所有后台进程的活动, 很多在文档中的抽象说 明, 通过跟踪文件的实时变化, 就可以清晰地看到各个进程之间的紧密协调 $ sqlplus "/ as sysdba"
65 SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 8 23:39:18 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0-64bit Production With the Partitioning, OLAP and Data Mining options SYS AS SYSDBA on 08-MAR-05 >alter system set sql_trace=true; System altered. 2. 在当前 session 级设置 SQL> alter session set sql_trace=true; Session altered. SQL> select count(*) from dba_users; COUNT(*) ---------- 34 SQL> alter session set sql_trace=false; Session altered. 3. 跟踪其他用户进程 SQL> desc dbms_system PROCEDURE SET_SQL_TRACE_IN_SESSION Argument Name Type In/Out Default? ----------------- ----------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN SQL_TRACE BOOLEAN IN # SQL> select sid,serial#,username from v$session 2 where username is not null;
66 SID SERIAL# USERNAME ---------- ---------- ------------------------------ 8 2041 SYS 9 437 EYGLE 设置跟踪 : SQL> exec dbms_system.set_sql_trace_in_session(9,437,true) PL/SQL procedure successfully completed. 可以等候片刻, 跟踪 session 执行任务, 捕获 SQL 操作 如果确定某个功能或模块存在问题, 可以在此期间有意识地调用, 以确保可以捕获问题代码 停止跟踪 : SQL> exec dbms_system.set_sql_trace_in_session(9,437,false) PL/SQL procedure successfully completed. SQL> desc dbms_system... PROCEDURE SET_INT_PARAM_IN_SESSION Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SID NUMBER IN SERIAL# NUMBER IN PARNAM VARCHAR2 IN INTVAL BINARY_INTEGER IN... SQL> select sid,serial#,username from v$session where username is not null; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 18 1605 EYGLE SQL> begin 2 sys.dbms_system.set_bool_param_in_session(18, 1605, 'timed_statistics', true); 3 sys.dbms_system.set_int_param_in_session(18, 1605, 'max_dump_file_size', 2147483647); 4 sys.dbms_system.set_sql_trace_in_session(18, 1605, true); 5 end; 6 / PL/SQL procedure successfully completed.
67 1. 在全局设置 event="10046 trace name context forever,level 12" 2. 对当前 session 设置 SQL> alter session set events '10046 trace name context forever'; Session altered. SQL> alter session set events '10046 trace name context forever, level 8'; Session altered. SQL> alter session set events '10046 trace name context off'; Session altered. 3. 对其他用户 session 设置 SQL> desc dbms_system... PROCEDURE SET_EV Argument Name Type In/Out Default? --------------------- ----------------------- ------ -------- SI BINARY_INTEGER IN SE BINARY_INTEGER IN EV BINARY_INTEGER IN LE BINARY_INTEGER IN NM VARCHAR2 IN... SQL> select sid,serial#,username from v$session where username is not null;
68 SID SERIAL# USERNAME ---------- ---------- ------------------------------ 8 2041 SYS 9 437 EYGLE SQL> exec dbms_system.set_ev(9,437,10046,8,'eygle'); PL/SQL procedure successfully completed. SQL> exec dbms_system.set_ev(9,437,10046,0,'eygle'); PL/SQL procedure successfully completed. SQL> select 2 d.value '/' lower(rtrim(i.instance, chr(0))) '_ora_' p.spid '.trc' trace_file_name 3 from 4 ( select p.spid 5 from sys.v$mystat m,sys.v$session s,sys.v$process p 6 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, 7 ( select t.instance from sys.v$thread t,sys.v$parameter v 8 where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, 9 ( select value from sys.v$parameter where name = 'user_dump_dest') d 10 / TRACE_FILE_NAME -------------------------------------------------------------------------------- /opt/oracle/admin/hsjf/udump/hsjf_ora_1026.trc SQL> set feedback off SQL> set serveroutput on SQL> declare 2 event_level number; 3 begin 4 for event_number in 10000..10999 loop 5 sys.dbms_system.read_ev(event_number, event_level); 6 if (event_level > 0) then 7 sys.dbms_output.put_line(
69 8 'Event ' 9 to_char(event_number) 10 ' is set at level ' 11 to_char(event_level) 12 ); 13 end if; 14 end loop; 15 end; 16 / Event 10046 is set at level 1 6.2 案例分析之一 SQL> select sid,serial#,username from v$session; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 1 1 2 1 3 1 4 1 5 1 6 1 7 284 IFLOW 11 214 IFLOW 12 164 SYS 16 1042 IFLOW 10 rows selected.
70 SQL> exec dbms_system.set_sql_trace_in_session(7,284,true) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(11,214,true) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(16,1042,true) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(7,284,false) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(11,214,false) PL/SQL procedure successfully completed. SQL> exec dbms_system.set_sql_trace_in_session(16,1042,false) PL/SQL procedure successfully completed. **************************************************************************** select auditstatus,categoryid,auditlevel from categoryarticleassign a,category b where b.id=a.categoryid and articleid= 20030700400141 and auditstatus>0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ------ ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.81 0.81 0 3892 0 1 ------- ------ -------- ---------- ------ ---------- ---------- ---------- total 3 0.81 0.81 0 3892 0 1 >
71 **************************************************************************** select auditstatus,categoryid from categoryarticleassign where articleid=20030700400138 and categoryid in ('63', '138','139','140','141','142','143','144','168','213','292','341','346', '347','348','349','350','351','352','353','354','355','356','357','358', '359','360','361','362','363','364','365','366','367','368','369','370', '371','372','383','460','461','462','463','621','622','626','629','631', '634','636','643','802','837','838','849','850','851','852','853','854', '858','859','860','861','862','863','-1') call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 4.91 4.91 0 2835 7 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 4.91 4.91 0 2835 7 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 41 Rows Row Source Operation ------- --------------------------------------------------- 1 TABLE ACCESS FULL CATEGORYARTICLEASSIGN **************************************************************************** SQL> select index_name,table_name,column_name from user_ind_columns 2 where table_name=upper('categoryarticleassign'); INDEX_NAME TABLE_NAME COLUMN_NAME ----------------------- -------------------------- --------------- IDX_ARTICLEID CATEGORYARTICLEASSIGN ARTICLEID IND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN ARTICLEID IND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN CATEGORYID IDX_SORTID CATEGORYARTICLEASSIGN SORTID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ARTICLEID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN CATEGORYID
72 PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ASSIGNTYPE IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN AUDITSTATUS IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ARTICLEID IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN CATEGORYID IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ASSIGNTYPE 11 rows selected. SQL> desc categoryarticleassign Name Null? Type -------------------------------------- -------- ---------------------------- CATEGORYID NOT NULL NUMBER ARTICLEID NOT NULL VARCHAR2(14) ASSIGNTYPE NOT NULL VARCHAR2(1) AUDITSTATUS NOT NULL NUMBER SORTID NOT NULL NUMBER UNPASS VARCHAR2(255) articleid= 20030700400141 SQL> select auditstatus,categoryid 2 from 3 categoryarticleassign where articleid=20030700400132; AUDITSTATUS CATEGORYID ----------- ---------- 9 94 0 383 0 695 Elapsed: 00:00:02.62 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=110 Card=2 Bytes=38) 1 0 TABLE ACCESS (FULL) OF 'CATEGORYARTICLEASSIGN' (Cost=110 Card=2 Bytes=38)
73 ******************************************************************************* select unpass from categoryarticleassign where articleid='20030320000682' and categoryid='113' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 2 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.00 0 2 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 20 Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN 1 INDEX RANGE SCAN (object id 3080) ******************************************************************************** 6.3 案例分析之二 ORA-00604: error occurred at recursive SQL level 1
74 ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist. SQL> alter session set sql_trace=true; Session altered. SQL> drop user wapcomm; ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist. SQL> alter session set sql_trace=false; ******************************************************************************** The following statement encountered a error during parse: DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = 'WAPCOMM' Error encountered: ORA-00942 ******************************************************************************** alter session set sql_trace=true call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1
75 Optimizer goal: CHOOSE Parsing user id: SYS ******************************************************************************** drop user wapcomm call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS ********************************************************************************...( 省略部分递归 SQL) ******************************************************************************** delete from user_history$ where user# = :1 ----- 后台的递归删除操作 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 4 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 4 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 1 DELETE USER_HISTORY$ 1 TABLE ACCESS FULL USER_HISTORY$ ******************************************************************************** ******************************************************************************** declare stmt varchar2(200); BEGIN if dictionary_obj_type = 'USER' THEN stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ' WHERE SDO_OWNER = ''' dictionary_obj_name ''' '; EXECUTE IMMEDIATE stmt;
76 end; end if; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 2 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 2 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 31 (recursive depth: 1) ******************************************************************************** alter session set sql_trace=false call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: SYS Problem Description ------------------- The Oracle Spatial Option has been installed and you are encountering the following errors while trying to drop a user, who has no spatial tables, connected as SYSTEM: ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist ORA-06512: at line 7 A 942 error trace shows the failing SQL statement as:
77 DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = '<user>' Solution Description -------------------- (1) Create a synonym for SDO_GEOM_METADATA_TABLE under SYSTEM which points to MDSYS.SDO_GEOM_METADATA_TABLE. (2) Now the user can be dropped connected as SYSTEM. SQL> connect / as sysdbaconnected. SQL> select * from dba_sdo_geom_metadata order by owner; select * from dba_sdo_geom_metadata order by owner * ERROR at line 1: ORA-00942: table or view does not exist ORA-04063: view "MDSYS.DBA_SDO_GEOM_METADATA" has errors SQL> select object_name from dba_objects where object_name like '%SDO%'; OBJECT_NAME -------------------------------------------------------------------------------- ALL_SDO_GEOM_METADATA ALL_SDO_INDEX_INFO ALL_SDO_INDEX_METADATA DBA_SDO_GEOM_METADATA DBA_SDO_INDEX_INFO DBA_SDO_INDEX_METADATA... DBA_SDO_GEOM_METADATA DBA_SDO_INDEX_INFO... SDO_WITHIN_DISTANCE USER_SDO_GEOM_METADATA USER_SDO_INDEX_INFO USER_SDO_INDEX_METADATA 88 rows selected. SQL> drop user MDSYS cascade; User dropped. SQL> select owner,type_name from dba_types where type_name like 'SDO%'; no rows selected
78 SQL> SQL> alter session set sql_trace=true; Session altered. SQL> drop user wapcomm; User dropped. SQL> alter session set sql_trace=false; Session altered. SQL> exit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0-64bit Production With the Partitioning option JServer Release 8.1.7.4.0-64bit Production 6.4 10046 与等待事件 SQL> create table t as select * from dba_objects; Table created.
79 -- 创建一张测试表 SQL> select file_id,block_id,blocks from dba_extents where segment_name='t'; FILE_ID BLOCK_ID BLOCKS ---------- ---------- ---------- 1 21601 8 1 21609 8 1 21617 8 1 21625 8 1 21633 8 1 23433 8 1 23441 8 1 23449 8 1 23457 8 1 23465 8 10 rows selected. -- 查看其空间使用情况 SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. -- 启用 10046 事件跟踪 SQL> select count(*) from t; COUNT(*) ---------- 6207 -- 由于表上未建立索引, 所以此处应该引发一次全表扫描 SQL> alter session set events '10046 trace name context off'; Session altered. -- 停用跟踪 SQL>! [oracle@eygle udump]$ ls rac1_ora_20695.trc [oracle@eygle udump]$ cat rac1_ora_20695.trc grep scatt WAIT #1: nam='db file scattered read' ela= 11657 p1=1 p2=21602 p3=7 WAIT #1: nam='db file scattered read' ela= 1363 p1=1 p2=21609 p3=8 WAIT #1: nam='db file scattered read' ela= 1297 p1=1 p2=21617 p3=8 WAIT #1: nam='db file scattered read' ela= 1346 p1=1 p2=21625 p3=8 WAIT #1: nam='db file scattered read' ela= 1313 p1=1 p2=21633 p3=8
80 WAIT #1: nam='db file scattered read' ela= 6226 p1=1 p2=23433 p3=8 WAIT #1: nam='db file scattered read' ela= 1316 p1=1 p2=23441 p3=8 WAIT #1: nam='db file scattered read' ela= 1355 p1=1 p2=23449 p3=8 WAIT #1: nam='db file scattered read' ela= 1320 p1=1 p2=23457 p3=8 WAIT #1: nam='db file scattered read' ela= 884 p1=1 p2=23465 p3=5 SQL> col name for a30 SQL> col p1 for a10 SQL> col p2 for a10 SQL> col p3 for a10 SQL> select name,parameter1 p1,parameter2 p2,parameter3 p3 2 from v$event_name where name='db file scattered read'; NAME P1 P2 P3 ------------------------- ---------- ---------- ---------- db file scattered read file# block# blocks SQL> select event,time_waited from v$system_event 2 where event='db file scattered read'; EVENT TIME_WAITED ---------------------------------------------------------------- ----------- db file scattered read 51 SQL> create tablespace eygle 2 datafile '/dev/raw/raw2' size 100M 3 extent management local uniform size 256K; Tablespace created. SQL> alter table t move tablespace eygle; Table altered. SQL> select file_id,block_id,blocks from dba_extents where segment_name='t';