Oracle培训(人力资源部).ppt

1、Oracle培训培训2022/11/42比较常见的一些问题写SQL的时候表顺序调整有区别吗？查询条件的书写有先后顺序吗？系统中的SQL之前执行都没问题，最近执行很慢怎么回事？我的系统发生内存溢出，跟数据库有没有关系？应用怎么会产生锁堵塞？我的系统存在数据库连接泄漏怎么办？表数据量很大怎么办？3Oracle体系结构4Oracle体系结构5数据库调优的手段最初级的数据库调优是硬件级别的调优数据库的硬件设计第二个级别的数据库性能调优是数据库系统本身数据库的设计第三个级别的数据库性能调优是模式和实物级别的软件设计对数据库的影响6优化重点表空间存储设计索引和SQL重写分区表7磁盘以及分割提高磁盘访问性能

2、技术就是并行利用多个磁盘在设备之间散布数据的方法称为“数据分割”分割方法：硬件分割、操作系统分割以及数据库分割8RAID 0一系列磁盘之间的简单分割，条块化数据使用循环方式写入磁盘改进读取访问效率，但是可靠性差b5b3b1b6b4b29RAID 1引入镜像提高可靠性,每个块被存储两次存储空间只有一半，可靠性高b3b2b1b3b2b110RAID 5奇偶校验分布在磁盘之间可靠性高Stripe 4 parityb7b4b1Stripe 3 parityb5b2b8Stripe 2 parityb3b9b6Stripe 1 parity11RAID 1+0镜像和分割可靠性高b5b3b1b5b3b1b

3、6b4b2b6b4b212Oracle访问表的方式全表扫描索引扫描（通过RID访问表）13B+树索引B+树索引是Oracle数据库的基本索引方法优点是快速访问，动态维护一个B+树有一层或多层，顶层称为根节点，底层由叶节点组成，根节点层和叶节点层之间的那些层称为中间节点。每个非叶子节点包含p个指针和p-1个关键数值每个叶子节点包含3个重要内容：关键数值、记录标识符和下一个叶子节点指针14B+树索引2153927285(53,rid)(59,rid)(64,rid)(71,rid)(72,rid)(80,rid)5997374425(56,rid)(57,rid)(83,rid)根节点中间节点页节

4、点15B+树索引B+树的顺序是4，高度为3p表示B+树的顺序高度是指叶子节点的层次16确定B+树的顺序和高度实例假设数据库拥有50万行记录，每行记录200个字节，每个搜索关键数值为15字节，数据指针为5字节，索引节点为1024字节。非叶节点 p5+(p-1)15=1024(p-1)=50叶节点 (p-1)*(15+5)+5=1024(p-1)=50ph Nn=500000 p=50h3.35或h=417开销估算查询数据=h+1块数据访问更新数据=搜索开销+重写数据块=(h+1)+1插入数据=检索开销+重写数据块+重写索引块=(h+1)+1+1删除数据=检索开销+重写数据块+重写索引块=(h+1

5、)+1+118开销估算实例实例插入关键值77插入开销=检索开销+重写数据块+重写索引块+拆分重写 =(h+1)+1+1+2=8块访问对于百万级记录的大规模更新操作，插入和更新操作采用批处理方式19复合索引开销实例假设有一个大型商业机构有1000万行客户订单记录，每条记录有客户名称、客户编号、地址、城市、邮编、电话、总订购费用属性。假设每条记录250个字节，数据库每个块大小5000个字节，指针长度为5个字节；复和键为职位、城市和总订购费用，长度为35字节。(假设磁盘传速度320MB/秒，评价转速延迟是2ms)SELECT empNo,empName,empAddress,empPhone,emp

6、EmailFROM customer WHERE jobTitle=software engineerAND city=Chicago and totalPur 1000;假设检索结果是假设检索结果是750条记录条记录p5+(p-1)35=5000p=125 log10000000/log125h=4750个目标行指针的块数个目标行指针的块数=75035/5000=6块块访问块消耗时间访问块消耗时间=2毫秒旋转延迟毫秒旋转延迟+转换时间转换时间=2+5000/320/1024/1024=2.02ms查询开销查询开销=复合索引检索开销复合索引检索开销+目标记录指针检索目标记录指针检索+最终数据访

7、问最终数据访问=h+6+750=4+6+750查询查询I/O时间时间=7542.02+6(5000/320/1024/1024)=1.52s20全表扫描开销实例预提缓存为预提缓存为64KB预提缓存的预提缓存的I/O操作数操作数=10000000250/64KB=38147查询时间查询时间=38147(2+64KB/320MB)=38147(2ms+19ms)=83.5秒秒21索引的经验规则（一）规则一：索引数据库中的每个主键和大多数外键利用主键（或唯一索引）的查询，性能随数据量增长不会发生很大的改变；主键所采用列应该认真考虑。22索引的经验规则（二）规则二：在SQL的where条件中被经常使用

8、的属性是潜在的好的索引候选项。根据查询SQL的谓词部分来设计；找到“切入点”，搜索条件很重要；23索引的经验规则（三）规则三：创建能让多数查询都能带来收益的复合索引。多列组合索引可以非常有效的执行特定复杂查询；组合索引减少了多个单个索引检索造成的大量排序和合并操作，额外的列有助于执行跨表连接操作；考虑WHERE从句中被引用到的列，令非匹配访问的谓词也能受益；也可以考虑把 SELECT 从句中的所有列也加上，使查询成为只使用索引的访问方式；24索引的经验规则（四）规则四：创建索引的键顺序按照列的基数降序排列；假定列对应的基数分别是 CARD(COM_ID)=1000，CARD(CRT_DATE)

9、=2000，CARD(CUST_ID)=1500。那么我们设计出的索引键的顺序应为：(CRT_DATE,CUST_ID,COM_ID)。25索引的经验规则（五）规则五：把 GROUP BY 和 ORDER BY 从句中的所有列加到索引中，可以减少访问计划中的排序操作26索引的经验规则（六）规则六：避免或删除冗余的索引，如非所需请勿增加索引。SQL的编写应该考虑表上存在的索引，复杂SQL建议改写；索引能提高部分SQL的查询性能，但也会相应降低insert性能，也有可能降低部分SQL查询性能；所以考虑表上的索引不要太多；27索引的经验规则（七）规则七：函数会导致索引失效|是字符连接函数+是数学函数

10、28一些不推荐的SQL写法参照浪潮烟草V6系统设计总体要求与基本规范V1.0 29Oracle优化器RBO，基于规则CBO，基于成本CHOOSE，选择性30RBO实例SQL select id,count(*)from t group by id;IDCOUNT(*)-151080 991SQLselect/*+rule*/*from t where id=99;-Plan hash value:4013845416-|id|Operation|Name|-|0|SELECT STATEMENT|1|TABLE ACCESS BY INDEX ROWID|T|*2|INDEX RANGE SC

11、AN|IND_T|31RBO实例SQLselect/*+rule*/*from t where id=1;-Plan hash value:4013845416-|id|Operation|Name|-|0|SELECT STATEMENT|1|TABLE ACCESS BY INDEX ROWID|T|*2|INDEX RANGE SCAN|IND_T|32连接方法嵌套循环连接对于外部表中的每个被存取的行，扫描内部表：例如：外部表T1:列A内部表T2:列A23323231执行嵌套循环连接，数据库管理器执行下列步骤：1、从T1中读取第一行。A的值是“2”2、扫描T2，直到发现一个匹配项（”2”

12、），然后连接这两行3、扫描T2，直到发现下一个匹配项（”2”），然后连接这两行4、扫描T2，直至该表结尾5、返回至T1，并读取下一行（”3”）33连接方法嵌套循环连接6、从第一行开始，扫描T2，直到发现匹配项（”3”），然后连接这两行7、扫描T2，直到发现下一个匹配项（”3”），然后连接这两行8、扫描T2，直至该表结尾9、返回至T1，并读取下一行（”3”）10、像之前一样扫描T2，连接匹配（“3”）的所有行。34嵌套循环连接实例表大小250行，每条记录200字节块大小4KB预取缓冲池大小64KB缓存数=250200/64KB=1项目表任务分配表表大小50000行，每条记录200字节块大小4KB

13、预取缓冲池大小64KB缓存数=50000100/64KB=77Select p.projectName,p.projecLeader,a.empId FROM project as p,assignedTo as aWHERE p.projectName=a.projectName;P做外部表连接I/O时间：=（1缓存+250扫描77缓存）5.8毫秒=117.4秒A做外部表连接I/O时间：=（77缓存+50000扫描1缓存）5.8毫秒=290.4秒硬盘平均寻址时间3.6ms，平均转速延迟是2ms，传送速度320MB/s35块嵌套连接实例表大小250行，每条记录200字节块大小4KB预取缓冲池大

14、小64KB缓存数=250200/64KB=1项目表任务分配表表大小50000行，每条记录200字节块大小4KB预取缓冲池大小64KB缓存数=50000100/64KB=77Select p.projectName,p.projecLeader,a.empId FROM project as p,assignedTo as aWHERE p.projectName=a.projectName;P做外部表连接I/O时间：=（1缓存+1扫描77缓存）5.8毫秒=0.45秒A做外部表连接I/O时间：=（77缓存+77扫描1缓存）5.8毫秒=0.89秒硬盘平均寻址时间3.6ms，平均转速延迟是2ms，传

15、送速度320MB/s36索引嵌套循环连接实例表大小250行，每条记录200字节块大小4KB预取缓冲池大小64KB缓存数=250200/64KB=1项目表任务分配表表大小50000行，每条记录200字节块大小4KB预取缓冲池大小64KB缓存数=50000100/64KB=77Select p.projectName,p.projecLeader,a.empId FROM project as p,assignedTo as aWHERE p.projectName=a.projectNameAnd p.projectName=financial analysis;首先索引主键表，接下来索引外键表

16、：=(h+1)+(h+1+ntr)5.8毫秒=(3+104)*5.8=0.62s扫描整个外键表，索引主键表：=(77缓存+(h+1)5.8毫秒=(77+3)*5.8=0.46s目标行数ntr=100，项目表索引高度h=2，任务分配表索引高度h=337连接方法合并连接例如：外部表 T1:列A内部表 T2:列A21323233执行合并连接，数据库管理器执行下面步骤：1、从T1中读取第一行。A的值是“2”2、扫描T2，直到发现匹配项，然后连接这两行。3、连续扫描T2，当列匹配时，连接那些行。4、当读取T2中的“3”时，返回至T1并读取下一行。5、T1中的下一个值是“3”，它与T2匹配，因此连接那些行。6、连续扫描T2，当列匹配时，连接那些行。7、到达T2结尾8、返回至T1以读取下一行注意T1中的下一个值与T1中的上一个值相同，因此从T2中的第一个“3”开始再次扫描T2。数据库管理器会记住这个位置。38合并排序连接实例表大小250行，每条记录200字节块大小4KB预取缓冲池大小64KB缓存数=250200/64KB=1项目表任务分配表表大小50000行，每条记录200字节块大小4KB预取缓冲池