ORACLE培训资料PPT学习课件

,ORACLE培训,ORACLE,ORACLE,实际开发应用及效率,1,基础概念,2,注意问题,3,基础概念,01,关系型数据库：,延迟符,数据库产品介绍,非关系型数据库：,数据库语音（DML、DDL、DCL、DQL、TCL）,延迟符,DML（DataManipulationLanguage）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。DDL（DataDefinitionLanguage）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。DQL（DataQueryLanguage）是数据查询语言，例如：SELECT语句。TCL（TransactionControlLanguage）是事务控制语言，例如：COMMIT、ROLLBACK等语句。,ORACLE执行机制,延迟符,当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：1、语法检查检查此sql的拼写是否语法。2、语义检查诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析（Parse）利用内部算法对sql进行解析，生成解析树及执行计划。4、执行sql，返回结果。其中，软、硬解析就发生在第3个过程里。,ORACLE的硬解析（hardparse）和软解析（softparse）,延迟符,硬解析：就是上面提到的对提交的Sql完全重新从头进行解析(当在共享池中找不到时候将会进行此操作)，总共有一下5个执行步骤：1：语法分析2：权限与对象检查3：在共享池中检查是否有完全相同的之前完全解析好的。如果存在，直接跳过4和5，运行Sql,此时算软解析。如果不存在，继续执行。4：选择执行计划5：产生执行计划软解析：就如果是在共享池中找到了与之完全相同的Sql解析好的结果后会跳过硬解析中的后面的两个步骤。注：创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。,ORACLE的硬解析（hardparse）和软解析（softparse）,延迟符,有如下两句查询语句：1.SELECT*FROMEMPWHEREEMPNO=123;2.SELECT*FROMEMPWHEREEMPNO=:EMP_NO;如果使用绑定变量，提交引用相同变量的完全相同的查询的人将会使用共享池中的编译方案，只需编译子例程一次，就可以重复使用。这样不仅可以使用较少的时间，而且可以减少锁存时间，降低锁存频率。这将会提高软件性能，大大提高可伸缩性。,ORACLE的硬解析（hardparse）和软解析（softparse）,延迟符,DECLARETYPErcISREFCURSOR;l_rcrc;l_dummyall_objects.object_name%TYPE;l_startNUMBERDEFAULTdbms_utility.get_time;BEGINFORiIN1.1000LOOPOPENl_rcFORSELECTobject_namefromall_objectswhereobject_id=:xUSINGi;FETCHl_rcINTOl_dummy;CLOSEl_rc;ENDLOOP;dbms_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);END;,ALTERSYSTEMFLUSHSHARED_POOL;DECLARETYPErcISREFCURSOR;l_rcrc;l_dummyall_objects.object_name%TYPE;l_startNUMBERDEFAULTdbms_utility.get_time;BEGINFORiIN1.1000LOOPOPENl_rcFORselectobject_namefromall_objectswhereobject_id=|i;FETCHl_rcINTOl_dummy;CLOSEl_rc;ENDLOOP;dbms_output.put_line(round(dbms_utility.get_time-l_start)/100,2)|seconds.);END;,什么是事务？什么是锁？,延迟符,事务(Transaction)从通讯的角度看：是用户定义的数据库操作序列，这些操作要么全做、要么全不做，是不可分割的一个工作单元。事务控制语句称为TCL，一般包括Commit和Rollback。锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。锁机制的了解可参考：,事务和锁：,原子性（Atomicity）：事务是数据库的逻辑工作单元，原子性即不可分割性，事务要么全部被执行，要么就全部不被执行；一致性（Consistency）：事务的执行使得数据库从一种正确状态转换成另一种正确状态；隔离性（Isolation）：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务；持续性（Durability）：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存,事务的ACID特性：,提交事务Commit：就是结束当前事务的运行并确认其所做的数据修改（使之永久生效、不可撤销）。使用COMMIT指令可以实现事务的提交。回滚事务Rollback：回滚事务是指结束撤销当前事务所做的数据修改，使数据恢复到事务开始之前的状态，并释放相关的记录锁和表级锁，回滚范围内的保存点（见下文）同时被清除。使用ROLLBACK指令可以实现事务的回滚。如果需要，还可以在事务中使用保存点（Savepoint）在当前的事务中的关键点处创建标记，将来就可以回退到指定的标记（保存点）处，以实现事务的部分回滚。保存点可以有多个。例如：SAVEPOINTp1;ROLLBACKTOp1;,事物的结束方式：,避免锁表,延迟符,此处不管是数据库操作或者是java程序操作导致的锁表问题，都是很严重的开发缺陷，要杜绝!在数据库中，注意：1、insert、update、delete等语句时避免锁表2、写存储过程避免锁表CREATEORREPLACEPROCEDUREPRO_AAAA(P_1INVARCHAR2)ISL_NUMBERNUMBER:=0;BEGININSERTINTOTABLE1(FIELD1,FIELD2)VALUE(P_1,L_NUMBER);COMMIT;-操作完数据要COMMITEXCEPTIONWHENOTHERSTHENROLLBACK;-此处报错后要ROLLBACKENDPRO_AAAA;,查看锁表及解锁,延迟符,解锁及KillSession:使用下面的语法查出锁并杀掉Session。SELECTA.SID,A.SERIAL#,A.USERNAME,B.TYPEFROMV$SESSIONA,V$LOCKBWHEREA.SID=B.SID;ALTERSYSTEMKILLSESSIONSID,SERIAL#;,实际开发应用及效率,02,索引的设计与使用,使用索引是数据库减少磁盘I/O最有效的方法。除了在表中数据量非常少和需要返回表中大部分行的情况，正确使用索引的查询性能比全表扫描要高得多，有时简单新建一个合适的索引，就能把查询性能提高几十倍，在查询语句出现性能问题时，通常第一时间都会检查是否正确设计和使用了索引。但是，事物都是有两面性的，索引的建立和维护需要消耗额外的CPU时间、额外的内存和存储空间，在对数据库表进行insert、update、delete操作时，都需要同时维护该表的所有索引，如果索引设计不当，不但不会提高性能，反而会降低性能。遵循以下原则，可以让我们充分利用索引带来的性能方面的提升。详细可参考：,延迟符,索引的设计与使用,延迟符,in和exists、notin和notexists的区别及效率问题,in和exists,应用场景,in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。如：表A（小表），表B（大表），如何使用效率更高？1、select*fromBwhereB1in(selectA1fromA);2、select*fromAwhereexists(selectB1fromBwhereB.B1=A.A1)用到的都是B表上B1列的索引,notin和notexists,如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而notextsts的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比notin要快。,延迟符,03,注意事项,SQL语句编写的注意事项,延迟符,数据库系统中，索引、碎片、统计信息和锁等很多因素都会影响性能，对这些特性进行优化都可能提高查询语句的执行效率。但是，如果SQL语句编写不合理，就可能大幅度增加本可避免的开销。以下是编写SQL语句需要注意的一些事项：一、建立并遵循SQL编码规范，SQL语句应简洁、易读、便于维护二、为提高查询性能，应限制操作的数据量，尽量在小结果集上操作，减少行数和列数1、不要使用“select*from”返回所有列，只检索需要的列，可避免后续因表结构变化导致的不必要的程序修改，还可降低额外消耗的资源2、不要检索已知的列，如selectcust_no,cust_namefromCustInfowherecust_no=10000050使用高选择性的where子句，让数据库返回必须的数据集，而不是返回大量数据给应用程序，再由应用程序进行筛选，返回大数据集的代价很高三、有效地使用索引：where子句引用的列决定了索引的使用，需要重点关注1.Where子句中列的顺序与需使用的索引顺序保持一致，不是所有数据库的优化器都能对此顺序进行优化，保持良好编程习惯使用可参数化的搜索条件，如=,=,!=,!,!0and(id=162FDB4D5ECB59E4E050007F01002A2Eor(idin(selecta.idfrommall_goodsawhere(a.type_id=ChinaUnicom.Account.Numberora.type_id=ChinaUnicom.Account.Number.Prepay)anda.title=(selectb.titlefrommall_goodsbwhereb.id=162FDB4D5ECB59E4E050007F01002A2Eand(b.type_id=ChinaUnicom.Account.Numberora.type_id=ChinaUnicom.Account.Number.Prepay)-效率高select*frommall_goodswherestock0andidin(select162FDB4D5ECB59E4E050007F01002A2Efromdualunionallselecta.idfrommall_goodsawherea.type_idin(ChinaUnicom.Account.Number,ChinaUnicom.Account.Number.Prepay)anda.title=(selectb.titlefrommall_goodsbwhereb.id=162FDB4D5ECB59E4E050007F01002A2Eandb.type_idin(ChinaUnicom.Account.Number,ChinaUnicom.Account.Number.Prepay),SQL语句编写的注意事项,延迟符,2、不要在where子句中对字段进行运算，如whereamount/2100，即使amount字段有索引，也无法使用，改成whereamount100*2就可使用amount列上的索引3、不要在where子句中对字段使用函数，如wheresubstring(Lastname,1,1)=F就无法使用Lastname列上的索引，而whereLastnamelikeF%或者whereLastname=FandLastname=和=和=条件组合，如果不能改写将导致无法使用索引四、分页显示的处理：在有大量满足条件的结果集，但界面上需要分页展示的情况，最好的方法是限制数据库每次返回的记录数，如每页展示50条记录，可使用selectfetchfirst50rowsonly五、避免不必要的资源开销：1、尽量用相同数据类型进行比较，避免发生数据类型转换，如select5678+32、在有min、max、distinct、orderby、groupby操作的列上建索引，避免额外的排序开销3、如果参与union子句的select结果集互斥或者允许结果集中有重复记录，可使用unionall代替union，可避免检测和删除重复记录的开销4、尽量使用单条语句完成操作，如多条单笔insert语句可以合并为一条多笔insert语句，使用case子句等,SQL语句编写的注意事项,延迟符,六、降低事务开销1、使用明确的事务控制语句来控制事务的开始和commit/rollback，将原子性扩展到整个事务，否则，每个语句都是一个原子操作，日志开销大2、及时commit，如果有insert、update和delete操作没有commit，数据上的锁会一直保持，及时commit有利于尽快释放锁，可提高并发能力，尤其是在批量操作时，分段commit还有利于出错时的重新处理，可从最后一次commit操作继续处理，如果一直没有commit，就必须从头开始了3、尽量保持短事务，尽量不要在事务中处理与数据库操作无关的工作，如群发邮件等4、使用insertintoselectfrom等块操作方式，可大幅度降低日志开销