首页 / ORACLE / oracle性能优化
oracle性能优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13983字，纯文字阅读大概需要20分钟。
内容图文
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 内容总结
以上是互联网集市为您收集整理的oracle性能优化全部内容，希望文章能够帮你解决oracle性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】