1 / 36

ORACLE 性能调整

ORACLE 性能调整. 跟踪与分析 索引、蔟与全表扫描 使用优化器调整性能. 跟踪与分析. SQL_Trace 实用工具 EXPLAIN PLAN 方法 TKPROF 实用程序 使用 dbv/RMAN 磁盘读和缓冲区获取. SQL_Trace 实用工具. SQL_Trace 有效则对每条语句有 : 解析、执行、取数据的计数； CPU 时间和占用时间； 物理读和逻辑读； 处理行数目； 所解析的用户名； 每次提交和回滚的情况。. SQL_Trace 实用工具. 只在 SQL 语句内启用 SQL_TRACE 使用 :

stamos
Download Presentation

ORACLE 性能调整

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ORACLE性能调整 • 跟踪与分析 • 索引、蔟与全表扫描 • 使用优化器调整性能

  2. 跟踪与分析 • SQL_Trace实用工具 • EXPLAIN PLAN方法 • TKPROF实用程序 • 使用dbv/RMAN • 磁盘读和缓冲区获取

  3. SQL_Trace实用工具 SQL_Trace有效则对每条语句有: • 解析、执行、取数据的计数； • CPU时间和占用时间； • 物理读和逻辑读； • 处理行数目； • 所解析的用户名； • 每次提交和回滚的情况。

  4. SQL_Trace实用工具 • 只在SQL语句内启用SQL_TRACE使用: SQL>Alter session SQL_TRACE=TRUE; • 在整个实例内启用SQL_TRACE则需使用system: SQL>ALTER system SET SQL_TRACE=TRUE; 注意：由于SQL_TRACE实用程序会增加系统的开销，建议用完后及时设置为FALSE。

  5. EXPLAIN_PLAN方法 1.用TKPROF生成解释计划 select * from emp,dept where emp.deptno=dept.deptno; 2.用EXPLAIN PLAN FOR生成解释计划 创建 PLAN_TABLE表以存放结果： 在UNIX环境，运行$ORACLE_HOME/rdbms/admin/utlxplan.sql ； 在NT环境：运行%ORACLE_HOME%\rdbms\admin\utlxplan.sql。 SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql ； 在SQL语句执行时用上 EXPLAIN PLAN FOR ，如： SQL> EXPLAIN PLAN FOR select * from emp,dept where emp.deptno=dept.deptno;

  6. TKPROF实用程序 1.跟踪文件将被输出到由服务器的初始化文件（或spfile user_dump_dest参数指定的目录中。也可如下改变: alter session set user_dump_dest="c:\kflosstrace"; alter session set tracefile_identifier ="kfloss_test"; 2.建立跟踪 对模快:SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'testenv', module_name=>'product_update'); 对客户机:SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'kimberly');

  7. TKPROF实用程序 SQL> select sid, serial#, username from v$session; SID SERIAL# USERNAME ------ ------- ------------ 133 4152 SYS 137 2418 SYSMAN 139 53 KIMBERLY 140 561 DBSNMP 141 4 DBSNMP . . . 168 1 169 1 170 1 28 rows selected.

  8. TKPROF实用程序 3.分析跟踪结果 C:\...\udump> trcsess output="kfloss.trc" service="testenv" module="product update" action="batch insert" 对整合的跟踪文件运行TKPROF，以生成一份报告。 C:\...\udump> tkprof kfloss.trc output=kfloss_trace_report SORT=(EXEELA, PRSELA, FCHELA)

  9. 用dbv/RMAN检查数据文件中的坏块 1.使用dbv检查 D:\oradata\eygle>dbv file=EYGLE.DBF blocksize=8192 2.使用RMAN检查坏块 D:\oradata\eygle>rman target SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 4 219 1 0 FRACTURED 4 1258 1 0 FRACTURED

  10. 磁盘读和缓冲区获取 在命令方式下用 SET autotrace on stat来设置 磁盘读和缓冲区获取的自动统计显示 SQL> set autotrace on stat; SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Statistics ---------------------------------------------------- 0 recursive calls 12 db block gets 6 consistent gets 0 physical reads 0 redo size 678 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) sorts (disk) 4 rows processed

  11. 索引、蔟与全表扫描 • 索引好处 索引可以减少精确定位的访问次数. • 索引代价 索引任何一个数据都要经过log2N次访问,如果要索引到的数据太多那就不划算了.这是用全表扫描.

  12. 如何避免全表扫描 • 使用AND和=,千万不要使用<>这样的符号 • 避免判定式崩溃.这是由判定约束而定的. • 避免混合类型表达式. 例如charcol列为VARCHAR2类型的索引列:AND charcol = <numexpr> 就会导致charcol类型转换,从而导致全表扫描. • 连接顺序很重要.例如 SELECT info FROM taba a, tabb b, tabc c WHERE a.acol BETWEEN :alow AND :ahigh AND b.bcol BETWEEN :blow AND :bhigh AND c.ccol BETWEEN :clow AND :chigh AND a.key1 = b.key1AND a.key2 = c.key2; 应该先选择再连接.

  13. 如何避免全表扫描 • 值访问就用值索引,函数访问就用函数索引. 例如:BETWEEN DECODE(:loval, ‘ALL’,somecolumn, :loval) AND DECODE(:hival, ‘ALL’, somecolumn, :hival) 就应该拆分. 又如,如下使用NYL()是不对的: SELECT employee_num, full_name NAME,employee_id FROM mtl_employees_current_view WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1) ORDER BY employee_num; • (NOT)IN最好用(NOT)EXIST代替.

  14. 视图的使用 • 视图的本质是对某些表的萃取操作. • 使用试图应谨慎 1.视图不可以插入数据. 2.连接视图最好不用了GROUP BY,例如: CREATE VIEW dx(deptno, dname, totsal) AS SELECT d.deptno, d.dname, e.sum(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY deptno, dname ; 3.用NO_UNNEST来禁止子查询嵌套操作 4.尽量不要使用视图的外连接

  15. 使用簇的原则 簇是一组表的集合，这些表有一个共同的列，对表进行簇时需要注意下面的原则： １．对于应用程序连接语句中经常用在一起访问的表可以考虑创建簇； ２．那些很少进行连接的表不要创建簇； ３．如果应用程序经常对某个表进行全表扫描也不要创建簇； ４．如果经常选择主表和子表，则要创建簇； ５．如果表中具有相同的键值，但是这些键值占空间超过 1个或２个数据块时，不宜创建簇； ６．如果各个键值对应的行差异太大就不宜创建簇。

  16. 创建Hash 簇的原则 • 使用hash在数据量很多的时候会提高性能．但是如果数据量少而访问又不频繁那就没必要了． • 是否创建哈希簇，主要考虑下面原则： １．对那些在使用中经常被访问的列，并且这些列经常使用等式的一组表可以创建哈希簇。 ２．对于非定的簇键，如果空间能容纳下将来所存放的所有行才能创建哈希簇。 ３．如果空间不足，不要考虑创建哈希簇。 ４．如果在应用经常采用全表扫描，则不要创建哈希簇。 ５．那些经常被修改聚合键值的表不宜创建哈希簇。

  17. Hash 簇 • 如果忽略了 HASH IS 子句，Oracle就使用内部哈希函数来创建聚合 ，例如： CREATE CLUSTER personnel ( department_number NUMBER ) SIZE 512 HASHKEYS 500 STORAGE (INITIAL 100K NEXT 50K);

  18. 使用优化器调整性能 • 什么是优化器

  19. 什么是优化器 • Oracle 的优化器（Optimizer）实际上是数据库环境的参数设置。可以在INITsid.ORA文件内的OPTIMZER_MODE=RULE或OPTIMZER_MODE=COST或OPTIMZER_MODE=CHOOSE来设置优化目标。用户也可以在会话和查询方式下更改优化器的默认操作模式。 • OPTIMZER_MODE=RULE　激活基于规则的优化器(RBO) • OPTIMZER_MODE=COST　激活基于成本的优化器(CBO) • OPTIMZER_MODE=CHOOSE　在表被分析的情况下激活基于成本的优化器

  20. SQL处理体系结构

  21. 如何选择优化 选择优化器主要是基 于应用的需要： • 对于批处理应用，如报表输出应用，需要采用吞吐量优化。因为吞吐量对于批处理来说更重要。 • 对于交互式应用，如Oracle Forms 应用或SQL*PLUS查询，需要采用最佳时间响应优化。因为交互式用户等着看到第一行的数据。 • 对于用ROWNUM来限制查询结果的查询，优化首先要考虑的响应时间。因为它要求的是得到最快的结果。 ＊．最大吞吐量优化类似于宁愿不要索引的全表扫描的结果或者相当于不要嵌套循环的分类合并连接。最佳响应时间的优化有点类似于索引扫描或嵌套循环连接。

  22. 决定优化器性能的因数 • OPTIMIZER_MODE 初始参数 • 数据字典的统计数据 • ALTER SESSION 语句的OPTIMIZER_GOAL 参数 • 在提示中改变目标

  23. OPTIMIZER_MODE／OPTIMIZER_GOAL OPTIMIZER_MODE初始参数或ALTER SESSION语句的OPTIMIZER_GOAL参数 取值如下： • choose表示优化器在基于代价和基于规则两种之间进行选择。如果数据字典有访问表的至少一行的统计数据，则优化器使用的基于代价和最佳吞吐量方法。如果访问表没有统计数据，则优化使用基于规则的路径。默认为基于规则的路径。 • All_rows 对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回整个行。 • FIRST_ROWS对整个SQL语句，优化器使用基于代价的路径。使用最小资源返回第一行。例如： • RULE 对整个SQL语句，优化器使用基于规则的路径。

  24. 使用dbms_stats包收集统计数据 options参数 • 使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式： • gather——重新分析整个架构（Schema）。 • gather empty——只分析目前还没有统计的表。 • gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。 • gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。

  25. 使用dbms_stats包收集统计数据 estimate_percent选项 以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：estimate_percent => dbms_stats.auto_sample_size method_opt选项 dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。

  26. 使用dbms_stats包收集统计数据 例如： execdbms_stats.gather_schema_stats( ownname  => ‘SCOTT’, options => ‘GATHER AUTO’, estimate_percent =>dbms_stats.auto_sample_size, method_opt => ‘for all columns sizerepeat', degree=> 15 )

  27. Oracle访问数据的基本方法 • 全表扫描 Oracle是读表中相继的数据块。 • 样本表扫描 １．在select 语句后加 SAMPLE BLOCK 来对表进行部分的扫描查询。这种查询要求是只能对单表进行，不能在连接查询上使用样本扫描。也不能在远程进行样本扫描。 ２．如果希望在远程上进行样本扫描的话，可以先用CREATE TABLE AS SELECT 语句将远程表复制到本地，然后在使用 SAMPLE BLOCK 语句。 　例如： select count(*)*100 from emp sample block(1)

  28. CBO访问路径 • 用Rowid访问表 • 簇(Cluster)扫描 所有具有相同簇键值的行都被存储进相同的数据库块中。为了执行簇扫描，Oracle首先通过扫描簇的索引，并从 中得到所选择的rowid值，然后再基于该rowid 对所有选择行进行定位。

  29. RBO访问路径 • 路径1：根据rowid 访问行 SELECT * from emp where rowid=’AAAA7BAA5AAA1UAAA’; • 路径2：通过cluster 联结访问行 SELECT *FROM emp, deptWHERE emp.deptno = dept.deptnoAND emp.empno = 7900; • 路径3：根据唯一性主键或主键的哈西簇访问单行 selecta.table_name,a.index_name,a.uniqueness,b.column_namefrom user_indexes a, user_ind_columns b* where a.index_name=b.index_name and a.table_name='EMP' • 路径4：根据唯一键或主键访问单行

  30. RBO访问路径 • 路径5：cluster 连接 • 路径6：哈西cluster 键 • 路径7：索引化cluster 键 • 路径8：复合索引 • 路径9：单列索引 • 路径10：索引化有界搜索 • 路径11：索引化无界搜索 • 路径12：合并排序联结 • 路径9：单列索引 • 路径10：索引化有界搜索 • 路径11：索引化无界搜索 • 路径12：合并排序联结

  31. 可优化的SQL语句 • 简单的SQL语句，即只设计单个表的insert,update,select • 简单的查询 • 等式连接 • 非等式连接 • 外连接 • 笛卡尔乘积 • 复合语句 • 组合查询 • 访问视图 • 分布式语句

  32. SQL语句优化的原理 • 把SQL语句分成三步执行：取出语句、分析拆解语句、执行语句，这就使得SQL能够达到纵向并行性． • 把嵌套语句、合并连接语句分成：对于Ａ中逐一个记录，都从B中进行查找，查找内容不一定要在Ｂ中结果返回以前就对Ａ的下一条记录进行同样的任务．这就达到了横向的并行性．

  33. 提示的指定 • [ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */或[ SELECT | DELETE|UPDATE ] --+ [hint | text ] 例如： SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b) USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id ,b.set_of_books_id ,b.personnel_id, p.vendor_id Personnel,p.segment1 PersonnelNumber, p.vendor_name NameFROM jl_br_journals j, jl_br_balances b,gl_code_combinations glcc, fnd_flex_values_vl glf,gl_periods gp, gl_sets_of_books gsb,po_vendors p WHERE . . . . . . . . . . . .

  34. 关于访问方法的提示 • FULL • ROWID 例：SELECT /*+ROWID(emp)*/ * FROM emp WHERE rowid>’AAAATKAABAAAFNTAAA’ AND empno=155 • CLUSTER • HASH • INDEX • INDEX_ASC • INDEX_COMBINE

  35. 关于访问方法的提示 • INDEX_JOIN • INDEX_DESC • INDEX_FFS • NO_INDEX　禁止选择索引访问方法 • AND_EQUAL • USE_CONCAT　对OR条件进行转换 • NO_EXPANDNO_EXPAND对于具有OR或IN 查询语句，它将阻止基于开销的优化器对其进行OR扩展。 • REWRITE • NOREWRITE

  36. 关于连接次序的提示 • /*+ORDERED*/ 例：SELECT /*+ORDERED*/tab1.col1,tabl2.col2,tab3.col3FROM tab1,tab2,tab3WHEREtab1.col1=tab2.col1 ANDtab2.col1=tab3.col1 依tab1,tab2,tab3次序连接 • STAR强行让优化器使用星型查询规划

More Related