oracle性能优化

oracle性能优化 --性能的定位 --原则 尽可能从小范围分析问题 sql层 ：能定位到sql，就不要从会话层面分析 工具 执行计划 10053、10046 会话层：从系统层面分析 v$session v$sesstat v$session_wait v$sql v$lock sql_trace 系统层 AWR os tools 高效的sql来自于对业务的理解和sql执行过程的理解 业务逻辑 - 优化器无能为力 create table mytable( id number ,value varchar2(50) ) select * from mytable for update; set autotrace on --打开执行计划查看 set autotrace off--关闭执行计划查看 set autotrace on explain --只显示查询结果和执行计划 set autotrace on statistics --只显示结果和统计信息 set autotrace traceonly --不显示查询输出，显示执行计划和统计信息 set autot traceonly explain --只显示执行计划 set autot traceonly statistics --只显示统计信息 --和前面一次的数做叠加 select t1.id,t1.value,sum(t2.value) from mytable t1 join mytable t2 on t2.id <= t1.id group by t1.id,t1.value --查看sql的执行计划 explain plan for select t1.id,t1.value,sum(t2.value) from mytable t1 join mytable t2 on t2.id <= t1.id group by t1.id,t1.value select * from table(dbms_xplan.display); --使用分析函数改写sql explain plan for select id,value,sum(value) over(order by id) from mytable; select * from table(dbms_xplan.display); sql语言本质上是集合的操作 -tablesan -index range scan -index fast scan -nested loop join -merge join -hash join ============= 性能优化 lock 么可以并发就没有锁 drop table t purge; create table t(id int primary key); --开两个窗口插入下面数据 insert into t values(1); --在第一个窗口选择是否提交或回滚，观察第二个页面变化 oracle中锁的分类 enqueues 队列类型的锁，通常和业务相关的 latches 系统资源方面的锁，比如内存结构，sql解析 锁的原则 1 只有被修改时，行才会被锁定 2 一条语句修改了一条记录，只有这条记录被锁定，oracle数据库不存在锁升级 3 当某行被修改时，它阻塞被人对它的修改 4 当一个事务修改一行时，将在这个行上加上行锁（tx），用于阻止其它事务对相同行的修改 5 读不会阻止写 例外select ...for update 6 写不会阻塞读 7 当一行被修改后，oracle通过回滚段提供给数据的一致性读 oracle中锁的类型 select type,name from v$lock_type; tm表锁：发生在insert\update\delete 目的是保证操作能够正常进行，阻止其它人对表执行ddl操作 tx锁 事务锁（行锁）对于正在修改的数据，阻止其它会话进行修改。 select sid,type,id1,id2,lmode,request,ctime,block from v$lock where type in (‘TM‘,‘TX‘) order by 1,2; select object_name from dba_objects where object_id=13501; select sid,event from v$session_wait where sid in (22,23); select ... for update 锁定查询到的数据 tm锁的几种模式 lock mode 模式 锁定的sql 排斥的模式 允许的sql 2 lock table t in row share mode; 6 select \insert \update \delete \for update 3 lock table t in row exclusive mode;4,5,6 select /insert/.... 4 lock table t in share mode; 3,5,6 select 5 lock table t in share row exclusive mode; 3,4,5,6 select 6 lock table t in exclusive mode; 2,3,4,5,6 select 为什么需要手工锁定表： ================= 05 基于引用关系的锁定 RI锁 create table p(id int primary key); create table c(id references p(id)); select sid,type,id1,id2,lmode,request,block from v$lock where type in (‘TM‘,‘TX‘) order by 1,2; select object_name from dba_objects where object_id in (13504,13506); 插入数据时在从表上面加表级锁 BI锁和外键索引 死锁：两个会话互相持有对方的资源 会话1： create table t(id int primary key); insert into t values(1); 会话2： insert into t values(2); insert into t values(1); --此时出现阻塞等待 会话1： insert into t values(2); 会话1出现阻塞等待，会话2报错 ORA-00060: 等待资源时检测到死锁 ==================== 06 latch门闩 latch的目的 保证资源的串行访问 -保证sga的资源访问 -保护内存的分配 保证执行的串行化 -保护关键资源的串行执行 -防止内存结构损坏 latch --sga 资源的请求和分配 共享池 -sql解析 sql重用 数据缓冲池 -数据访问、数据写入磁盘、数据读入内存 -修改数据块 -数据段扩展 oracle有哪些latch select * from v$latchname; latch的获取 wait方式--如果无法获取请求的latch -spin 当一个会话无法获得需要的latch时，会继续使用cpu，达到一个间隔后，再次尝试，达到最大重试次数 -sleep 当一个会话无法获得需要的latch时，会等待一段时间 no wait方式-如果无法获取请求的latch - 不会发生sleep或者spin - 转而去获取其它可用的latch shared pool里的latch争用-绑定变量 declare l_cnt number; begin for i in 1..10000 loop execute immediate ‘select count(*) from t where x =‘||i into l_cnt; end loop; end; / declare l_cnt number; begin for i in 1..10000 loop select count(*) into l_cnt from t where x = i; end loop; end; buffer cache 数据区 desc x$bh 数据块头 会话访问数据块 step1 latch step2 hash bucket step3 数据块 步骤： 1 hash the block address 2 get bucket latch 3 look for header 4 found read block in cache 5 not found read block off disk data buffer中的latch争用 -热块 表数据块争用 热块索引数据热块 文件头数据块-并发修改 latch相关的视图 - v$latch v$latch 每个latch的统计信息的一个汇总，每一条记录表示一种latch select name,gets,misses,sleeps from v$latch where name like ‘cache%‘; misses:请求不成功次数 sleeps：成功获取前sleeping次数 immediate_gets:以immediate模式latch请求数 immediate_misses:以immediate模式请求失败次数 v$latchholder 包含了当前latch持有者的信息 通过视图中的pid和sid信息 v$latch_children 存储latch信息的视图 AWR报告中的latch部分 latch优化思路 AWR报告 通过动态视图v$latch 分析当前latch资源情况 确定争用最大的latch 分析可能的原因 应用层面和数据库层面考虑 ========= 08 优化器和执行计划 执行计划 sql语句访问和处理数据的方式 执行计划 数据的访问 直接表的访问 -并行 -多数据块 通过索引访问 -index unique scan 索引唯一扫描 -index range scan 索引范围扫描 -index full scan 索引全部扫描 -index fast full scan 索引快速全部扫描 -index skip scan 索引切块扫描 数据的处理 order by group by 数据的关联处理 -nested loop join 嵌套循环连接 小表有索引 -merge join 先排序 -hash join 大表和小表关联 通过表访问数据 --索引唯一扫描 SQL> explain plan for 2 select * from t where id=2; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2719494768 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 26 | 2 (0)| |* 2 | INDEX UNIQUE SCAN | SYS_C003776 | 1 | | 1 (0)| -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=2) 14 rows selected --索引范围扫描 SQL> explain plan for 2 select * from t where id>5; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1744232805 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 26 | 1 (0)| |* 2 | INDEX RANGE SCAN | SYS_C003776 | 1 | | 1 (0)| -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">5) Note ----- - dynamic sampling used for this statement (level=2) 18 rows selected --索引快速扫描 SQL> explain plan for 2 select count(*) from t; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2375626531 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| SYS_C003776 | 3 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 13 rows selected --整个索引的扫描 SQL> explain plan for 2 select id from t order by id; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3503263006 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 39 | 2 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | SYS_C003776 | 3 | 39 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) 12 rows selected --索引跳跃扫描 数据集的关联 --hash join --先把小表hash到内存中，然后过来关联 SQL> explain plan for 2 select t.*,t2.* from t,t2 where t.id = t2.id; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 106979157 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 260 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 260 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| T | 5 | 130 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T2 | 5 | 130 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."ID"="T2"."ID") Note ----- - dynamic sampling used for this statement (level=2) 19 rows selected --nested loops --从其中的一张表拿数据到另外一张表中去匹配 --sort merge join --并行 ================== 09 oracle的优化器 cbo 依据一套数据模型，计算数据访问和处理的成本，择最优成本为执行方案 CBO的工作模式 all_rows 以结果集的全部处理完毕为目的 first_rows(n) 以最快返回n行为目的 分页 优化器模式的设置方式 参数设置 optimizer_mode 会话设置 alter session set optimizer_mode=all_rows; sql设置 select /*+ all_rows */ count(*) from t; cost 代价 选择性 selectivity - user_tab_col_statistics 索引的选择性 - user_indexes cardinality 集的市 在执行计划中每一步操作返回的记录数 CBO通过这个值的权重计算，决定使用哪一种方式访问数据 10g后 rows 评估出来的数 exec dbms_stats.gather_table_stats(user,‘t‘,method_opt=>‘for all columns size 1‘); 不做直方图 索引 clustering factor 簇 集群因子 集群因子 索引代价的评估 出现一次数据块的跳转 集群因子 +1 CBO的核心 成本的计算 数据访问的成本的估算 I/O成本的估算 全表扫描 索引（单数据块 多数据块） CPU成本的估算 数据处理的成本 CPU成本的估算 ============== 10 hints oracle的hints hints是用来约束优化器行为的一种技术 优化器模式 all_rows first_rows 数据访问路径 基于表的数据访问 基于索引的数据访问 表关联的方式 NL MJ HJ hints的使用范畴 -尽量避免在开发中使用 -辅助dba用来做性能排查 访问路径相关的hints /* full */全表扫描 --全表扫描可以多块读的方式 --全表扫描可以用并行 SQL> explain plan for 2 select /* full(t) */ * from t where id < 8; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 78 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T | 3 | 78 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<8) Note ----- - dynamic sampling used for this statement (level=2) 17 rows selected --index no_index /* index(t id) */ 使用索引 /* no_index(t id) */ 不适用索引 /* index_ffs */ 与全表扫描机制相似 索引快速全部扫描 /* + index_ss */ index skip scan -用于替代全表扫描的一种数据访问方法 -对于前导重复率高的联合索引 index skip scan性能好一些 =============== 11 hints /*+ use_nl */ nested loop joins --两个结果集的关联 --内部表外部表 从一张表取数据与另外一张表取 --nl的场景 --关联中有一个表比较小 --被关联表的关联字段上有索引 --索引的键值不应该重复率高 --小表作为外部表 小表去大表进行关联 SQL> explain plan for 2 select /*+ use_nl(t,t2) */ t.* 3 from t,t2 4 where t.id = t2.id ; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 125942274 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 195 | 8 (0) | 1 | NESTED LOOPS | | | | | 2 | NESTED LOOPS | | 5 | 195 | 8 (0) | 3 | TABLE ACCESS FULL | T2 | 5 | 65 | 3 (0) |* 4 | INDEX UNIQUE SCAN | SYS_C003776 | 1

• < 上一篇

  SQL查询语句优化的实用方法

• 下一篇 >

  使用plsql 连接oracle数据库