PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

恒芮
7 years ago
Views:

4 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 页

5 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 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 页

7 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 页

8 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: Book by author qa324jffritcf WAITING db file sequential read 7:42: Get review id aferv5desfzs5 CPU 7:50: Add to cart hk32pekfcbdfr WAITING buffer busy wait 7:52: One click abngldf95f4de WAITING log file sync 2009 Oracle 版权所有第 7 页

13 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 页

17 数据库性能基础 Schema 设计和 SQL 语句好的 Schema 和数据模型设计是好的数据库性能的基础. 好的 Schema 和数据模型设计应该使应用操作更加简单并减少错误发生选择正确的数据类型 ( e.g. Char vs VARCAHR2) 使用不同的索引设计高效率的访问路径选择正确的存储选项使用分区收集并维护准确的统计信息编写简单高效的 SQL 尽量避免序列化瓶颈 2009 Oracle 版权所有第 17 页

18 数据库性能基础会话和游标应用服务器 / 中间件管理数据库会话连接, 保证数据库的性能和稳定在一台机器上超过 5000 进程会使机器变的不稳定在实验室很容易模拟大量的数据库连接, 但是在生产系统上要使用则非常困难 logon/logoff 对性能的影响进程的建立和撤销开销非常大连接和去连接到共享内存的开销也非常大共享 SQL, 在 OLTP 应该中使用绑定变量应用应该尽量避免对数据库对象进行 creation drop truncate 等操作 2009 Oracle 版权所有第 18 页

21 数据库性能基础容量规划 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 Oracle 版权所有第 21 页

23 并行并行查询 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 页

31 数据分区技术大大提高访问速度有的放矢并行处理 select sum(amount) From 销售情况表 Where 日期 between 20-JAN-2004 and 5-FEB-2004 客户计费表分区可以显著提高访问大表时的性能分区的存在对应用系统是透明的分区忽略技术系统核心自动根据分区情况优化数据访问, 忽略无关的数据分区 January 销售情况表 Partition February 销售情况表 Partition March 销售情况表 Partition 2009 Oracle 版权所有第 31 页

32 强大的并行处理能力 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 页

33 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 页

34 EXPLAIN PLAN 命令 : 示例 2 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); Plan hash value: Id Operation Name Rows Bytes Cost (%CPU SELECT STATEMENT (17 1 MERGE JOIN (17 2 TABLE ACCESS BY INDEX ROWID DEPARTMENTS (0 3 INDEX FULL SCAN DEPT_ID_PK 27 1 (0 * 4 SORT JOIN (25 5 TABLE ACCESS FULL EMPLOYEES ( Predicate Information (identified by operation id): access("e"."department_id"="d"."department_id") filter("e"."department_id"="d"."department_id") 18 rows selected Oracle 版权所有第 34 页

36 SQL*Plus 中设置 AUTOTRACE: 示例 2 set autotrace traceonly statistics SELECT * FROM products; Statistics recursive calls 0 db block gets 9 consistent gets 3 physical reads 0 redo size 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 页

40 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 页

42 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 页

44 TKPROF 格式化后输出 : 示例... select max(cust_credit_limit) from customers where cust_city ='Paris' call count cpu elapsed disk query current rows Parse Execute Fetch total Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 61 Rows Row Source Operation 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 页

45 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 Oracle 版权所有第 45 页

46 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 Oracle 版权所有第 46 页

Oracle 4

Oracle 4 01 04 Oracle 07 Oracle Oracle Instance Oracle Instance Oracle Instance Oracle Database Oracle Database Instance Parameter File Pfile Instance Instance Instance Instance Oracle Instance System