Oracle高效开发技术总结Word文档格式.docx
- 文档编号:22402268
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:25
- 大小:27.61KB
Oracle高效开发技术总结Word文档格式.docx
《Oracle高效开发技术总结Word文档格式.docx》由会员分享,可在线阅读,更多相关《Oracle高效开发技术总结Word文档格式.docx(25页珍藏版)》请在冰豆网上搜索。
B.高可用的数据库设计
好的数据库设计,可以保证数据库的可扩充性和良好的性能
1.
oracle的高可用产品,如:
RAC,dataguard,stream,advancereplication,
serverHA,,还有MV/rman/oraclelogminer/oracleflashbackquery等
2.
数据库的应用设计(范式设计,反范式设计,数据库分布式设计,索引表和簇表的设计等)
C.高可用的网络设计
可靠性,包括链路与硬件的冗余
高速性,包括响应速度和吞吐量
健壮性,比如在Dos攻击的表现
安全性,有很强的安全策略,防止入侵
D.高可用的主机设计
主机的HA(双机互备/双机热备)
oracle的RAC
在因主机失败或主机网络失败,可以保证系统的可用性。
HA和RAC最大的区别在于,HA是os提供的active/standby解决方案,RAC是oracle提供的active/standby解决方案
E.高可用存储设计
存储的选择(存储架构/存储的cache技术/存储主要性能指标)
选择合理的raid的级别
选择合理的存储容灾方案(oracle高可用产品stream,logicalstandby;
非oracle技术,如复制软件shareplex)
2.数据库选型
在数据库设计中,要确定数据库的类型(OLAP/OLTP),使用合适优化设计方法,要
熟悉所操作数据的特点(连续操作,随机操作),更重要的是要熟悉自己的业务类型
A.
OLTP系统是事务性非常多的系统,都是以小型查询为主,评估这样的系统的时候,一般看秒事务数与执行的sql数量。
典型的OLTP系统有电子商务,银行,证券等;
OLTP系统最容易出现瓶颈的地方是CPU和磁盘子系统
CPU出现瓶颈需要调整(调整应用一般可以解决80%以上的性能问题)
1.调整应用(表现在高逻辑读总量与计算性函数或者过程)
2.整理表的碎片,重建索引,添加适当索引,优化sql
3.减少sql的分析(session_cached_cursors缓存游标,使用绑定变量)
4.合理调整共享池
磁盘I/O出现瓶颈
1.存储性能瓶颈:
控制器不足,cache偏小,cache设置不合理,io通道容量不足等
2.磁盘性能瓶颈:
磁盘数量过少,使用了速度比较慢的磁盘等
3.使用了不合理的raid模式
4.在使用raid的情况下,存在io热点,多个热点文件使用同一个磁盘
5.异步io配置不正确
6.数据库各种缓冲区设置不合理,缓冲命中率很低
7.PGA的各种缓冲设置过小(自动PGA管理时,PGA设置过小)
8.重做日志存在性能瓶颈
9.重做缓冲设置不合理
10.存在热点数据
11.表空间碎片严重
12.表和索引的存储参数不合理
13.行迁移比较严重
14.存在大量大表扫描的情况
15.sql选择了不好的执行计划
在当今的IT技术发展中,磁盘IO是相对发展最慢的,为了弥补磁盘io在整个系统的的不足,可以使用大量cache(减少动态pv/增加cache命中率)和B-tree索引,在OLTP系统尽量参考如下规则
1.语句越简单越好,这样执行计划比较稳定
2.一定要使用绑定变量,减少语句解析
3.减少表关联,减少分布式事务,不要使用mv技术,并行技术,位图索引
4.使用批量操作快速提交,尽量把大事务转化小事务,避免阻塞的发生
OLAP系统,也称DSS系统,就是我们说的数据仓库,评估这样系统的时候,一般看磁盘系统的吞吐量(带宽)
3.如何估算系统设计规模
如果要为新系统选择硬件,就要明确一些参数指标概念,下面是样例系统的相关指标参数:
pv/天,动态pv率,逻辑读/DPV,高峰系数,cache命中率,读写的比例
1000000
1/10
2000
5
98%
(15:
1)
不同厂家评测,1GHZ的CPU,在满载的情况下对oracle数据库的平均处理能力
逻辑读/每秒
物理读/每秒
100000
25000
通过这些参数可以估算需要多少磁盘iops?
或者说根据现有硬件可以预测系统所能承载的压力
高峰时期每秒物理读个数:
1000000/(24*3600)*1/10*2000*5*2/100*15/16
高峰时期每秒物理写个数
1000000/(24*3600)*1/10*2000*5*2/100*1/16
这部分信息隐去
索引的创建与使用(*)
Sql部分(*)
在我们的数据库结构中主要用了两种存储结构,普通堆表和B*索引;
为了充分考虑数据存储优化,我们还要引入IOT和聚簇表;
针对表使用频繁度、并发度,数据增长量等因素为表和索引设计,pctfree、inittrans等存储参数;
还有如何创建合适索引,这些都是我们在数据库设计阶段要考虑的。
表的类型:
堆组织表
B×
树索引集群表
散列集群表
外部表
因为堆组织表我常用,外部表很少用到,所以我们这里主要极少集群表和IOT表
1.B*树索引集群表与散列集群表
创建B×
树索引集群:
createclusteruser_objects_cluster_btree(usernamevarchar2(30))size1024;
createindexuser_object_idxonclusteruser_objects_cluster_btree;
创建散列群:
createclusteruser_objects_cluster_hash(usernamevarchar2(30))hashkeys100size3168;
B*集群表使用样例:
createtableuser_info(
username,user_id,account_status,lock_date)
clusteruser_objects_cluster_btree(username)
as
select
username,user_id,account_status,lock_datefromdba_userswhere1=0;
散列集群表使用样例:
createtableuser_objects(
owner,object_name,object_id,object_type)
cluster
user_objects_cluster_btree(owner)
selectowner,object_name,object_id,object_typefromdba_objectswhere1=0
集群表可以降低IO并提高缓冲区高速缓存的效率,B*群表适合数据仓库,也适合OLTP系统的父子表(要同时更新的)。
单表散列群表
顾名思义就是集群表里只有一个表
定义与使用样例:
createclusterobject_id_lookup(object_idnumber)
singletable
hashkeys50000
size100;
测试性能对比:
1.创建单表散列群表
createtablesingle_table_hash_cluster
(
owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status
)
clusterobject_id_lookup(object_id)
as
selectowner,object_name,rownum,object_type,created,last_ddl_time,timestamp,status
from
(select*fromdba_objects
unionall
select*fromdba_objects)
whererownum<
=50000
2.创建普通堆表
createtableheap_table
owner,object_name,object_id,object_type,created,last_ddl_time,timestamp,status,
constraintheap_table_pkprimarykey(object_id)
比较运行时间特性
altersessionsetevents'
10046tracenamecontextforever,level12'
;
declare
l_recsingle_table_hash_cluster%rowtype;
begin
forxin1..3
loop
for
iin1..5000
select*intol_rec
fromsingle_table_hash_cluster
whereobject_id=i;
fromheap_table
endloop;
end;
10046tracenamecontext
off'
用tkprof或runstats查看运行结果对比。
单表散列群表主要用于只读表或者是按键值读取一个表的数量远大于修改的应用程序
集群表的优点是:
物理集中放置数据,提高高速缓冲的效率,减少逻辑io,减少索引的需求;
缺点是:
仔细考虑size的大小,控制插入增加集群效果,比堆表插入慢
索引组织表(IOT)
索引组织表基本是一个存储在索引中表,和集群表类似,但也有它的不同,如下:
他存在一个数据结构和一个索引结构中,而B树群有一个索引和数据段
IOT存储的数据按主键值排序,而B树群存储的键值本身是不排序的
IOT对于关联表和数据需要集中放置非常有用,不像集群表那样要考虑插入顺序
IOT表同样有集群表的优点,但也有传统表插入慢的缺点,比较适合瘦长表,即行短表
定义与使用样例
创建IOT测试表:
reatetableiot_table(
usernamevarchar2(80),
nicknamevarchar2(80),
memochar(100),
constraintiot_pkprimarykey(username,nickname)
organizationindex;
创建普通堆表:
createtableheap_table(
constraintheap_pkprimarykey(username,nickname)
装载测试数据:
declare
foriin1..100
loop
forxin(selectusernamefromall_users)
insertintoheap_table
values(x.username,x.username||'
_'
||i,'
X'
);
insertintoiot_table
commit;
模拟查询并查看性能效果:
10046tracenamecontextforever,level12'
typearray_typeistableofvarchar2(100);
l_array1array_type;
l_array2array_type;
l_array3array_type;
foriin1..10loop
forxin(selectusernamefromall_users)loop
foryin(select*
fromheap_tablesingle_row
whereusername=x.username)loop
null;
fromiot_tablesingle_row
select*bulkcollect
intol_array1,l_array2,l_array3
fromheap_tablebulk_collect
whereusername=x.username;
select*bulkcollect
fromiot_tablebulk_collect
/
10046tracenamecontextoff'
在上一次培训的内容介绍的很详细,可以参考
1.索引表
2.嵌套表
3.变长数组
4.批绑定
5.集合的异常处理
6pl/sql优化总结
为什么使用pl/sql?
1.pl/sql是数据操作的最高效的语言(oracle自己的产品也在用pl/sql,如:
高级复制,applicationsuite,工作流引擎等)
2.pl/sql具有可移植性和可重复性
在实际代码中使用程序包,那程序包有哪些优点?
1.程序包减少或消除了级联失效
2.增加了名字空间
3.支持重载和封装
4.允许把相关功能组织在一起
当第一次调用打包子程序时,整个包会被加载到共享内存池。
所以,以后调用包内相关子程序时,就不再需要读取磁盘了,这样会加快我们的代码会执行速度,因此要把相关功能的代码放到一个包里。
独立的过程和函数什么时候可以用?
在不被其他代码调用,有独立的功能时,可以使用它们
函数和过程的区别:
存储过程:
用于数据库中完成特定的操作或任务(如插入,删除等)
程序包头部声明为proceudre
程序包头部声明时不需要返回类型
可以使用in/out/inout三种模式的参数
可以作为一个独立的pl/sql语句执行
可以通过out/inout返回零个或多个值
sql语句中不可调用存储过程
函数:
用户特定的数据(选择)
程序包头部声明为function
程序包头部声明时要有返回类型,而且pl/sql块中至少要包括一个有效的return语句
可以使用in/out/inout三种模式的参数,不能独立的执行,必须作为表达式的一部分
通过return语句返回一个值,切该值要与声明部分一致,也可以通过out类型的参数带出变量
sql语句中可以调用函数
集合类型:
在处理单行单列数据时,可以使用标量变量(pl/sql变量)
在处理单行多列数据时,可以使用pl/sql记录
在处理单列多行数据时,可以使用pl/sql集合
在处理多行多列数据时,可以pl/sql记录表(集合pl/sql记录和pl/sql集合的优点)
pl/sql记录
定义:
typea_login_record_typeisrecord(
usernamea_login.user_name%type,
userid
a_login.user_id%type
);
a_login_recorda_login_record_type;
...
或
a_login_recorda_login%rowtype;
简单实用(可以使用记录变量或记录元素)
setserverputputon;
begin
selectuser_name,user_id
intoa_login_record
froma_login
whereuser_id=&
v_userid;
dbms_output.put_line(a_login_record.username);
pl/sql集合
pl/sql集合包括索引表(pl/sql表),嵌套表,变长数组
用于处理pl/sql数组的数据类型,他的元素个数没有限制,并且元素下标可以为负值(下标类型可以为binary_integer,pls_integer,varchar2)
typea_login_table_typeistableofa_login.user_name%typeindex
bybinary_integer;
a_login_table
a_login_table_type;
selectuser_nameintoa_login_table
(1)
dbms_output.put_line('
用户名:
'
||a_login_table
(1));
用于处理pl/sql数组的数据类型,元素下标从1开始,并且元素的个数没有限制,元素值可以稀疏的不需要排序;
嵌套表类型可以作为表列的类型,但索引表类型不能作为表列的类型;
在使用嵌套表的时候,必须使用其构造方法初始化嵌套表
decalre
typea_login_table_type
istableof
a_login.user_name%type;
a_login_table_type;
a_login_table:
=a_login_table_type('
username1'
'
username2'
作为表列中类型的定义与使用样例
createtypeusername_typeistableofvarchar2(50);
createtabletable_nest(
idnumber(4),
namevarchar2(50),
usernameusername_type
)nestedtableusernamestoreasusername_table;
插入数据:
insertintotable_nestvalues(1,'
22'
username_type('
qwewq'
qwewqe'
12312'
));
检索数据:
username_tablesusername_type;
selectusernameintousername_tablesfromtable_nestwhereid=1;
foriin
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 高效 开发 技术 总结