<Insert Picture Here> Oracle 数据库性能调优 金丹 资深技术顾问 2009 Oracle 版权所有第 0 页
最重要的规则 最少的参数, 最少的性能影响 除非你很清楚你在干什么, 否则不要设置 _**** 除非你很清楚你在干什么, 否则不要设置 event 2009 Oracle 版权所有第 1 页
定期监控 DB statspack,awr,addm OS,vmstat,sar. Network Grid Control RUEI 2009 Oracle 版权所有第 2 页
Oracle Tuning Methods Prehistoric (v5) Debug code Dark Ages (v6) Counters/Ratios BSTAT/ESTAT SQL*Trace Renaissance (v7) Introduction of Wait Event instrumentation Move from counters to timers STATSPACK 2009 Oracle 版权所有第 3 页
The Modern Age of Time-based Tuning YAPP (8i) - Instance tuning using instance statistics Non intrusive, always on Broadly scoped Method R (9i) - Session tuning using 10046 SQL traces Tightly scoped Must be highly selective DB Time Tuning (10g) Comprehensive tuning using fundamental notion of time in database Multiple scoping levels Always on, non-intrusive Built into infrastructure: instrumentation, ASH, AWR, ADDM, EM 2009 Oracle 版权所有第 4 页
Oracle 10g/11g 的智能化工具 SQL Tuning Advisor PGA PGA Advisor Memory Buffer Cache Advisor ADDM SQL Access Advisor SGA Library Cache Advisor AWR Space Segment Advisor Undo Advisor 2009 Oracle 版权所有第 5 页
Active Session History (ASH) Samples active sessions every one second into memory (v$active_session_history) Direct access to kernel structures One of ten samples flushed to AWR at every snapshot Data captured includes: SID SQL ID Program, Module, Action Wait event# Object, File, Block actual wait time (if captured while waiting) Enables targeted performance analysis of transient problems 2009 Oracle 版权所有第 6 页
Active Session History (ASH) Query for Khalid Husaini Novels Browse and Read Reviews Add item to cart Checkout using one-click DB Time Time SID Module SQL ID State Event 7:38:26 213 Book by author qa324jffritcf WAITING db file sequential read 7:42:35 213 Get review id aferv5desfzs5 CPU 7:50:59 213 Add to cart hk32pekfcbdfr WAITING buffer busy wait 7:52:33 213 One click abngldf95f4de WAITING log file sync 2009 Oracle 版权所有第 7 页
ASH Report: Main Sections 2009 Oracle 版权所有第 8 页
ASH Report: Top Events 2009 Oracle 版权所有第 9 页
ASH Report: Top SQL 2009 Oracle 版权所有第 10 页
ASH Report: Activity Over Time 2009 Oracle 版权所有第 12 页
Automatic Workload Repository (AWR) Built-in workload and performance statistics repository in the database Automatically Captures Workload Data Every 60 minutes, or manually, saves data for 8 days by default Resides in SYSAUX tablespace Space requirements automatically managed Old data is automatically purged nightly based on retention interval Stores different classes of data: BASE STATISTICS e.g., physical reads SQL STATISTICS e.g., disk reads (per sql stmt) METRICS e.g., physical reads / sec ACTIVE SESSION HISTORY (ASH) 2009 Oracle 版权所有第 13 页
AWR Compare Period Report 2009 Oracle 版权所有第 14 页
AWR Compare Period Report: Load Profile 2009 Oracle 版权所有第 15 页
AWR Compare Period Report: Top Timed Events 2009 Oracle 版权所有第 16 页
数据库性能基础 Schema 设计和 SQL 语句 好的 Schema 和数据模型设计是好的数据库性能的基础. 好的 Schema 和数据模型设计应该使应用操作更加简单并减少错误发生 选择正确的数据类型 ( e.g. Char vs VARCAHR2) 使用不同的索引设计高效率的访问路径 选择正确的存储选项 使用分区 收集并维护准确的统计信息 编写简单高效的 SQL 尽量避免序列化瓶颈 2009 Oracle 版权所有第 17 页
数据库性能基础会话和游标 应用服务器 / 中间件管理数据库会话连接, 保证数据库的性能和稳定 在一台机器上超过 5000 进程会使机器变的不稳定 在实验室很容易模拟大量的数据库连接, 但是在生产系统上要使用则非常困难 logon/logoff 对性能的影响进程的建立和撤销开销非常大 连接和去连接到共享内存的开销也非常大 共享 SQL, 在 OLTP 应该中使用绑定变量 应用应该尽量避免对数据库对象进行 creation drop truncate 等操作 2009 Oracle 版权所有第 18 页
会话管理 不要连接所有浏览器用户 不要连接 执行操作 断开连接 使用连接池 中间层的最大优点 2009 Oracle 版权所有第 19 页
性能与数据安全的一些取舍 影响性能的一些数据安全设置 : 多镜像控制文件 重做日志组中多镜像日志组成员 频繁 Check Point 备份数据文件 归档状态 块校验 并发用户访问和事务 2009 Oracle 版权所有第 20 页
数据库性能基础容量规划 CPU and Memory For OLTP workloads the CPU utilization should not exceed 65-70% Scale Nodes/CPUs/Cores as a power of 2. Start with 4Gig Memory per Core and expand according to workload type I/O Target 5-10 millisec response time for disks performing response time critical I/O. Start by assuming 30 IOPS per disk for OLTP and 20MBytes per Disk in DSS. This is way below theoretical values but allows for media repair etc. You can run HBAs and SAN switches at about 70-90% of theoretical values. Allocate and Administer Disk by bandwidth in addition to storage requirements. 200MB/s per CPU core Interconnect For Data passing intensive DSS Clusters Balance equalize Interconnect bandwidth with I/O bandwidth. Complex queries running across a cluster will perform sorts and joins across the interconnect. In the future we may require higher interconnect bandwidth for DW/BI workloads because the data on disk is compressed Do not confuse bits for Bytes when capacity planning Interconnect or SAN. 2009 Oracle 版权所有第 21 页
Oracle 的性能解决方案 绑定变量 并行查询 索引 物化视图 分区 Real Application Cluster 压缩 结果集缓存 Active Data Guard TimesTen 2009 Oracle 版权所有第 22 页
并行 并行查询 SELECT /*+ PARALLEL(SALES,9)*/ * FROM SALES; SELECT /*+ PARALLEL_INDEX(c,ic,3)*/ * FROM customers c WHERE cust_city = 'MARSEILLE'; 并行 DML ALTER SESSION ENABLE/DISABLE PARALLEL DML MERGE /*+ PARALLEL(c,3) PARALLEL(d,3) */ INTO customers c USING diff_customers d ON (d.cust_id = c.cust_id) WHEN MATCHED THEN UPDATE SET c.cust_last_name = d.cust_last_name, c.cust_city = d.cust_city WHEN NOT MATCHED THEN INSERT (c.cust_id,c.cust_last_name) VALUES (d.cust_id,d.cust_last_name); 并行 DDL CREATE INDEX CREATE TABLE... AS SELECT ALTER INDEX... REBUILD 2009 Oracle 版权所有第 23 页
并行查询性能分析表扫描 vs 索引读取 假设某张表拥有 100,000,000 行记录数, 每行记录 100 Byte 大小, 表扫描 I/O 速率是每秒 1M/10ms, 索引 I/O 是 8k/5ms 并且假设缓存命中率为 80% 200 180 160 140 120 100 80 60 40 20 0 0.001%.01%.1% Index TS Serial TS DOP=2 TS DOP=4 TS DOP=8 TS DOP=16 2009 Oracle 版权所有第 24 页
索引 索引的影响 加快能适当使用索引的 SELECT, UPDATE 减慢 INSERT 速度 需要额外空间 索引使用原则 只有需要的时候才创建相应的索引 为解决某个问题创建一个索引可能会影响其他的 SQL 执行 删除没用的索引 2009 Oracle 版权所有第 25 页
使用索引 : 集中 IO 2009 Oracle 版权所有第 26 页
使用索引 : 分散 IO 2009 Oracle 版权所有第 27 页
索引的类型 唯一及非唯一索引 复合索引 按索引存储方法 B*-tree 普通 反键索引 函数索引 位图索引 索引组织表 2009 Oracle 版权所有第 28 页
物化视图 物化视图 : 是一个预计算的结果集 拥有自己的数据存储 : 拥有自己的索引 适用场合 : 复杂的 join 概要和聚集数据集 2009 Oracle 版权所有第 29 页
分区的作用 分区对性能影响 分区忽略 并行处理 2009 Oracle 版权所有第 30 页
数据分区技术大大提高访问速度 有的放矢 并行处理 select sum(amount) From 销售情况表 Where 日期 between 20-JAN-2004 and 5-FEB-2004 客户计费表 分区可以显著提高访问大表时的性能 分区的存在对应用系统是透明的 分区忽略技术系统核心自动根据分区情况优化数据访问, 忽略无关的数据分区 January 销售情况表 Partition February 销售情况表 Partition March 销售情况表 Partition 2009 Oracle 版权所有第 31 页
强大的并行处理能力 C P U Query--Index--Load Insert--Update--Delete C P U C P U C P U C P U C P U C P U C P U 随着数据库的增长, 必须采用并行处理方式以保证响应时间 在数据库中进行并行处理可以显著提高批量操作的性能 批量更新 批量删除 批量插入 并行执行 SQL 语句 数据动态分片 RAC 的多节点并行更新 2009 Oracle 版权所有第 32 页
EXPLAIN PLAN 命令 : 示例 1 EXPLAIN PLAN SET STATEMENT_ID = 'demo01' FOR SELECT e.last_name, d.department_name FROM hr.employees e, hr.departments d WHERE e.department_id = d.department_id; Explained. EXPLAIN PLAN 命令并不实际执行该 SQL 2009 Oracle 版权所有第 33 页
EXPLAIN PLAN 命令 : 示例 2 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Plan hash value: 2933537672 ------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU -------------------------------------------------------------------------------- 0 SELECT STATEMENT 106 2862 6 (17 1 MERGE JOIN 106 2862 6 (17 2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS 27 432 2 (0 3 INDEX FULL SCAN DEPT_ID_PK 27 1 (0 * 4 SORT JOIN 107 1177 4 (25 5 TABLE ACCESS FULL EMPLOYEES 107 1177 3 (0 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("e"."department_id"="d"."department_id") filter("e"."department_id"="d"."department_id") 18 rows selected. 2009 Oracle 版权所有第 34 页
SQL*Plus 中设置 AUTOTRACE: 示例 1 启动 AUTOTRACE set autotrace on 隐藏输出 set autotrace traceonly 只显示执行计划 AUTOTRACE 需要实际执行 SQL 语句 set autotrace traceonly explain 2009 Oracle 版权所有第 35 页
SQL*Plus 中设置 AUTOTRACE: 示例 2 set autotrace traceonly statistics SELECT * FROM products; Statistics ------------------------------------------------------ 1 recursive calls 0 db block gets 9 consistent gets 3 physical reads 0 redo size 15028 bytes sent via SQL*Net to client 556 bytes received via SQL*Net from client 6 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 72 rows processed 2009 Oracle 版权所有第 36 页
哪些因素影响执行计划 统计信息 对象统计信息 表 列 索引 系统统计信息 I/O 性能 CPU 性能 优化器 Hints 初始化参数 索引 分区 物化视图 并行 2009 Oracle 版权所有第 37 页
统计信息如何收集 自动收集统计信息 GATHER_STATS_JOB 手动收集统计信息 使用 DBMS_STATS 包 动态采样 optimizer_dynamic_sampling 2009 Oracle 版权所有第 38 页
优化器 RBO CBO ALL_ROWS FIRST_ROWS FIRST_ROWS_n 2009 Oracle 版权所有第 39 页
Hints 使用举例 UPDATE /*+ INDEX(p PRODUCTS_PROD_CAT_IX)*/ products p SET p.prod_min_price = (SELECT (pr.prod_list_price*.95) FROM products pr WHERE p.prod_id = pr.prod_id) WHERE p.prod_category = 'Men' AND p.prod_status = 'available, on stock' / 2009 Oracle 版权所有第 40 页
Hints 类型 优化器模式 ALL_ROWS 访问路径 FULL 查询转换 STAR_TRANSFORMATION Join 顺序 ORDERED Join 操作 其他 USE_NL, USE_HASH APPEND 2009 Oracle 版权所有第 41 页
SQL Trace 针对当前会话 : SQL> ALTER SESSION SET sql_trace = true; 针对任意会话 : SQL> EXECUTE dbms_session.set_sql_trace(true); SQL> EXECUTE dbms_system.set_sql_trace_in_session 2 (session_id, serial_id, true); 实例级, 可以直接设置初始化参数 : SQL_TRACE = TRUE 2009 Oracle 版权所有第 42 页
TKPROF 格式化 trace 文件 TKPROF 命令示例 : OS> tkprof tracefile outputfile [options] OS> tkprof OS> tkprof ora_902.trc run1.txt OS> tkprof ora_902.trc run2.txt sys=no sort=execpu print=3 2009 Oracle 版权所有第 43 页
TKPROF 格式化后输出 : 示例... select max(cust_credit_limit) from customers where cust_city ='Paris' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ------- Parse 1 0.02 0.02 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.10 0.09 1408 1459 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ------- total 4 0.12 0.11 1408 1459 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=1459 pr=1408 pw=0 time=93463 us) 77 TABLE ACCESS FULL CUSTOMERS (cr=1459 pr=1408 pw=0 time=31483 us) 2009 Oracle 版权所有第 44 页
10046 事件分析 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. 执行跟踪 : SQL> exec dbms_system.set_ev(5,323,10046,8, scott'); PL/SQL procedure successfully completed. 结束跟踪 : SQL> exec dbms_system.set_ev(5,323,10046,0, scott'); PL/SQL procedure successfully completed. 2009 Oracle 版权所有第 45 页
10053 事件分析 SQL> alter session set events '10053 trace name context forever, level 1'; Session altered. SQL> alter session set events '10053 trace name context off'; Session altered. 执行跟踪 : SQL> exec dbms_system.set_ev(5,323,10053,1, scott'); PL/SQL procedure successfully completed. 结束跟踪 : SQL> exec dbms_system.set_ev(5,323,10053,0, scott'); PL/SQL procedure successfully completed. 2009 Oracle 版权所有第 46 页
Q U E S T I O N S A N S W E R S 2009 Oracle 版权所有第 47 页
2009 Oracle 版权所有第 48 页