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

Oracle培训培训2024/5/21 周二2024/5/21 周二12比较常见的一些问题写SQL的时候表顺序调整有区别吗？查询条件的书写有先后顺序吗？系统中的SQL之前执行都没问题，最近执行很慢怎么回事？我的系统发生内存溢出，跟数据库有没有关系？应用怎么会产生锁堵塞？我的系统存在数据库连接泄漏怎么办？表数据量很大怎么办？2024/5/21 周二23Oracle体系结构2024/5/21 周二34Oracle体系结构2024/5/21 周二45数据库调优的手段最初级的数据库调优是硬件级别的调优数据库的硬件设计第二个级别的数据库性能调优是数据库系统本身数据库的设计第三个级别的数据库性能调优是模式和实物级别的软件设计对数据库的影响2024/5/21 周二56优化重点表空间存储设计索引和SQL重写分区表2024/5/21 周二67磁盘以及分割提高磁盘访问性能技术就是并行利用多个磁盘在设备之间散布数据的方法称为“数据分割”分割方法：硬件分割、操作系统分割以及数据库分割2024/5/21 周二78RAID 0一系列磁盘之间的简单分割，条块化数据使用循环方式写入磁盘改进读取访问效率，但是可靠性差b5b3b1b6b4b22024/5/21 周二89RAID 1引入镜像提高可靠性,每个块被存储两次存储空间只有一半，可靠性高b3b2b1b3b2b12024/5/21 周二910RAID 5奇偶校验分布在磁盘之间可靠性高Stripe 4 parityb7b4b1Stripe 3 parityb5b2b8Stripe 2 parityb3b9b6Stripe 1 parity2024/5/21 周二1011RAID 1+0镜像和分割可靠性高b5b3b1b5b3b1b6b4b2b6b4b22024/5/21 周二1112Oracle访问表的方式全表扫描索引扫描（通过RID访问表）2024/5/21 周二1213B+树索引B+树索引是Oracle数据库的基本索引方法优点是快速访问，动态维护一个B+树有一层或多层，顶层称为根节点，底层由叶节点组成，根节点层和叶节点层之间的那些层称为中间节点。每个非叶子节点包含p个指针和p-1个关键数值每个叶子节点包含3个重要内容：关键数值、记录标识符和下一个叶子节点指针2024/5/21 周二1314B+树索引2153927285(53,rid)(59,rid)(64,rid)(71,rid)(72,rid)(80,rid)5997374425(56,rid)(57,rid)(83,rid)根节点中间节点页节点2024/5/21 周二1415B+树索引B+树的顺序是4，高度为3p表示B+树的顺序高度是指叶子节点的层次2024/5/21 周二1516确定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=42024/5/21 周二1617开销估算查询数据=h+1块数据访问更新数据=搜索开销+重写数据块=(h+1)+1插入数据=检索开销+重写数据块+重写索引块=(h+1)+1+1删除数据=检索开销+重写数据块+重写索引块=(h+1)+1+12024/5/21 周二1718开销估算实例实例插入关键值77插入开销=检索开销+重写数据块+重写索引块+拆分重写 =(h+1)+1+1+2=8块访问对于百万级记录的大规模更新操作，插入和更新操作采用批处理方式2024/5/21 周二1819复合索引开销实例假设有一个大型商业机构有1000万行客户订单记录，每条记录有客户名称、客户编号、地址、城市、邮编、电话、总订购费用属性。假设每条记录250个字节，数据库每个块大小5000个字节，指针长度为5个字节；复和键为职位、城市和总订购费用，长度为35字节。(假设磁盘传速度320MB/秒，评价转速延迟是2ms)SELECT empNo,empName,empAddress,empPhone,empEmailFROM 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查询开销查询开销=复合索引检索开销复合索引检索开销+目标记录指针检索目标记录指针检索+最终数据访问最终数据访问=h+6+750=4+6+750查询查询I/O时间时间=7542.02+6(5000/320/1024/1024)=1.52s2024/5/21 周二1920全表扫描开销实例预提缓存为预提缓存为64KB预提缓存的预提缓存的I/O操作数操作数=10000000250/64KB=38147查询时间查询时间=38147(2+64KB/320MB)=38147(2ms+19ms)=83.5秒秒2024/5/21 周二2021索引的经验规则（一）规则一：索引数据库中的每个主键和大多数外键利用主键（或唯一索引）的查询，性能随数据量增长不会发生很大的改变；主键所采用列应该认真考虑。2024/5/21 周二2122索引的经验规则（二）规则二：在SQL的where条件中被经常使用的属性是潜在的好的索引候选项。根据查询SQL的谓词部分来设计；找到“切入点”，搜索条件很重要；2024/5/21 周二2223索引的经验规则（三）规则三：创建能让多数查询都能带来收益的复合索引。多列组合索引可以非常有效的执行特定复杂查询；组合索引减少了多个单个索引检索造成的大量排序和合并操作，额外的列有助于执行跨表连接操作；考虑WHERE从句中被引用到的列，令非匹配访问的谓词也能受益；也可以考虑把 SELECT 从句中的所有列也加上，使查询成为只使用索引的访问方式；2024/5/21 周二2324索引的经验规则（四）规则四：创建索引的键顺序按照列的基数降序排列；假定列对应的基数分别是 CARD(COM_ID)=1000，CARD(CRT_DATE)=2000，CARD(CUST_ID)=1500。那么我们设计出的索引键的顺序应为：(CRT_DATE,CUST_ID,COM_ID)。2024/5/21 周二2425索引的经验规则（五）规则五：把 GROUP BY 和 ORDER BY 从句中的所有列加到索引中，可以减少访问计划中的排序操作2024/5/21 周二2526索引的经验规则（六）规则六：避免或删除冗余的索引，如非所需请勿增加索引。SQL的编写应该考虑表上存在的索引，复杂SQL建议改写；索引能提高部分SQL的查询性能，但也会相应降低insert性能，也有可能降低部分SQL查询性能；所以考虑表上的索引不要太多；2024/5/21 周二2627索引的经验规则（七）规则七：函数会导致索引失效|是字符连接函数+是数学函数2024/5/21 周二2728一些不推荐的SQL写法参照浪潮烟草V6系统设计总体要求与基本规范V1.0 2024/5/21 周二282930Oracle优化器RBO，基于规则CBO，基于成本CHOOSE，选择性2024/5/21 周二3031RBO实例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 SCAN|IND_T|2024/5/21 周二3132RBO实例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|2024/5/21 周二3233连接方法嵌套循环连接对于外部表中的每个被存取的行，扫描内部表：例如：外部表T1:列A内部表T2:列A23323231执行嵌套循环连接，数据库管理器执行下列步骤：1、从T1中读取第一行。A的值是“2”2、扫描T2，直到发现一个匹配项（”2”），然后连接这两行3、扫描T2，直到发现下一个匹配项（”2”），然后连接这两行4、扫描T2，直至该表结尾5、返回至T1，并读取下一行（”3”）2024/5/21 周二3334连接方法嵌套循环连接6、从第一行开始，扫描T2，直到发现匹配项（”3”），然后连接这两行7、扫描T2，直到发现下一个匹配项（”3”），然后连接这两行8、扫描T2，直至该表结尾9、返回至T1，并读取下一行（”3”）10、像之前一样扫描T2，连接匹配（“3”）的所有行。2024/5/21 周二3435嵌套循环连接实例表大小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.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/s2024/5/21 周二3536块嵌套连接实例表大小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.projectName;P做外部表连接I/O时间：=（1缓存+1扫描77缓存）5.8毫秒=0.45秒A做外部表连接I/O时间：=（77缓存+77扫描1缓存）5.8毫秒=0.89秒硬盘平均寻址时间3.6ms，平均转速延迟是2ms，传送速度320MB/s2024/5/21 周二3637索引嵌套循环连接实例表大小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;首先索引主键表，接下来索引外键表：=(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=32024/5/21 周二3738连接方法合并连接例如：外部表 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。数据库管理器会记住这个位置。2024/5/21 周二3839合并排序连接实例表大小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.projectName;合并排序连接I/O时间：=(77缓存+1缓存)5.8毫秒=0.45s2024/5/21 周二3940问题问题名称问题名称问题描述问题描述不正常工作部分不正常工作部分JSP 编译导致服务器挂起在大量负载情况下 JSP 编译造成服务器挂起全部JVM 错误导致服务器挂起SUN JVM 错误，比如轻量型线程库、造成线程死锁等执行线程队列JDBC 中的服务器挂起死锁造成 JDBC 挂起执行线程队列JSP 导致服务器挂起servlet 时间的 JSP 错误设置，比如 PageCheckSeconds全部垃圾回收导致服务器挂起垃圾回收花费太多时间全部线程占用导致服务器挂起线程全部被占用，没有线程可用于新工作执行线程队列应用程序死锁导致服务器挂起应用程序死锁 线程锁定资源 1，然后等待锁定资源 2。另一个线程锁定资源 2，然后等待锁定资源 1执行线程队列EJB_RMI 服务器挂起RMI、RJVM 响应 所有绑定线程等待 RJVM、RMI 响应weblogic.admin.RMI网络IO资源限制如等待网络IO资源socket reader线程大量高并发请求大量高并发请求执行线程队列大量耗时操作导致执行线程用尽如从数据库中加载大量数据执行线程队列2024/5/21 周二4041系统内存溢出问题2024/5/21 周二4142SOA内存溢出（大结果集）2024/5/21 周二4243heapdump日志分析720,158,656(51%)40 2 java/util/ArrayList 0 x9dfeffe8720,158,656(51%)40 2 java/util/ArrayList 0 x9dfeffe8 720,158,616(51%)10,222,120 2,555,525 array of 720,158,616(51%)10,222,120 2,555,525 array of java/lang/Object 0 xcd2642c0java/lang/Object 0 xcd2642c0 328(0%)40 2 java/util/ArrayList 0 xa9000b38 328(0%)40 2 java/util/ArrayList 0 xa9000b38 328(0%)40 2 java/util/ArrayList 0 xa9000c78 328(0%)40 2 java/util/ArrayList 0 xa9000c78 328(0%)40 2 java/util/ArrayList 0 xa9000db8 328(0%)40 2 java/util/ArrayList 0 xa9000db8 328(0%)40 2 java/util/ArrayList 0 xa9000ef8 328(0%)40 2 java/util/ArrayList 0 xa9000ef8 328(0%)40 2 java/util/ArrayList 0 xa9001038 328(0%)40 2 java/util/ArrayList 0 xa9001038 328(0%)40 2 java/util/ArrayList 0 xa9001178 328(0%)40 2 java/util/ArrayList 0 xa90011782024/5/21 周二4344连接池泄漏2024/5/21 周二4445连接池泄漏2024/5/21 周二4546锁堵塞问题定位脚本2024/5/21 周二4647OracleSession StatusINACTIVE-非活动状态ACTIVE-活动状态2024/5/21 周二4748Oracle-v$lockSID：会话（SESSION）标识TYPE：区分该锁保护对象的类型；TM表操作锁，TX事务锁ID1 (for DML locks,the object_id being locked)ID2LMODE (if 1 then lock has been aquired)REQUESTCTIMEBLOCK2024/5/21 周二4849Oracle的TM锁类型锁模式锁模式 锁描述锁描述 解释解释 SQLSQL操作操作0none1NULL空Select2SS(Row-S)行级共享锁，其他对象只能查询这些数据行Select for update、Lock for update、Lock row share3SX(Row-X)行级排它锁，在提交前不允许做DML操作Insert、Update、Delete、Lock row share4S(Share)共享锁Create index、Lock share5SSX(S/Row-X)共享行级排它锁Lock share row exclusive6 X(Exclusive)排它锁Alter table、Drop able、Drop index、Truncate table、Lock exclusive2024/5/21 周二4950Oracle-v$sessionSID,SERIAL#,USERNAME,LOCKWAIT,STATUS,BLOCKING_SESSION_STATUS,BLOCKING_SESSION,MACHINE,PROGRAM,SQL_ID,PREV_SQL_ID,/之前执行；EVENT/事件LAST_CALL_ET/当前状态持续时间2024/5/21 周二5051定位占用资源的SQLselect c.sql_text,a.last_call_etfrom v$session a,v$sqltext cwhere a.sql_hash_value=c.hash_valueand a.sid=1489order by piece;select a.sid,a.last_call_et,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.status=ACTIVE order by a.last_call_et desc;2024/5/21 周二5152Oracle访问计划sqlplus/as sysdbaconn user/passworddatabaseexplain plan for SELECT*FROM(SELECT ROW_.*,ROWNUM ROWNUM_ FROM(SELECT A.DOMAIN_ID,A.SERV_GROUP,COUNT(C.CO_NUM)AS CO_NUM,SUM(C.QTY_SUM)AS QTY_SUM,SUM(C.AMT_SUM)AS AMT_SUM FROM CC_ORDERDOMAIN A,CC_CCUST B,SD_TELE_CO C WHERE A.DOMAIN_ID=B.DOMAIN_ID AND B.CUST_ID=C.CUST_ID AND C.STATUS in(01,02,06)AND C.TYPE IN(10,12,13,14,18,15)and 1=1 and born_date=20120607 AND A.SERV_GROUP IN (GZ0000000000063,GZ0000000002397,0)GROUP BY A.DOMAIN_ID,A.SERV_GROUP )ROW_)WHERE ROWNUM_ :2;spool test1.logselect*from table(dbms_xplan.display);spool off2024/5/21 周二5253分区表分区表是一种数据组织方案，它根据一列或多列中的值把表数据划分为多个称为数据分区的存储对象；分区是数据库中对海量数据存储管理提供的一个应用很广泛的技术2024/5/21 周二5354分区优势一是在某些情况的查询，通过利用分区消除，只对需要处理的分区进行扫描，这样扫描的数据块会大大减少，使查询比针对非分区表运行时执行的速度更快，磁盘I/O更少；分区消除在于分区键的良好设计；二是分区更利于数据维护，可以只针对单独分区进行备份、恢复、数据加载、索引重建等操作；同时分区可以轻松的删除并存档老数据。2024/5/21 周二5455分区种类范围分区/时间范围哈希分区列表分区/公司编号组合分区2024/5/21 周二5556谢 谢2024/5/21 周二56