Oracle数据库基础与优化技术解析

1、Oracle数据库基础与优化技术解析技术创新，变革未来培训目标与概述2Oracle的性能优化实施流程概述系统性能优化实施流程性能最优化咨询提供高性能的SQL提供高效的战略索引提供性能优化指南系统性能优化培训开发人员数据库知识培训数据库设计应用技 术培训DeveloperDBA检查物理数据模型制定索引构建战略提供DBMS性能优化指南提供最优数据库指南优化批处理程序优化低效率SQL资深的系统性能优化专家系统资源使用 现状CPU负担DISK I/OBottleneckDB Object AdministrationStorage UsageDBMSEfficiencySQL Execution St

2、atisticsInefficient SQL Detection访问数据库索引优化个别SQL优化批处理程序优化性能测试及 成果评估DBMSParameter 调整DA# IDORepository系统 分析索引 分析SQL分析系统性能优化，成 果物编写综合 搜集3培训目标与概述4Oracle体系结构5Oracle服务器由两部分组成Oracle数据库（Oracle Database）Oracle例程（Oracle Instance）Oracle服务器是基于例程机制的服务器系统磁盘上(数据库)：RDBMS、一组文件内存中(例程)：内存结构、一组后台进程共享池系统全局区(System Global

3、 Area，SGA)。SGA是由所有 用户进程共享的一块内存区 域。日志缓冲区Java池大型池数据字典 存储区共享SQL 区程序全局 区数据库缓 冲存储区6ORACLE体系结构7Oracle数据库是安装在磁盘上的Oracle 数据库文 件和相关的数据库管理系统(DBMS)的集合。数据文件（Data files）控制文件（Control files）重做日志文件（Redo log files）初始化参数文件（Parameter file）口令文件（Password file）归档重做日志文件（Archived log files）等ORACLE体系结构8Oracle例程后台进程数据库写入进程（D

4、BWn）日志写入进程（LGWR）日志归档进程（ARCn）检查点进程（CKPT）系统监控进程（SMON）进程监控进程（PMON）等。ORACLE体系结构Oracle例程内存结构组织称为系统全局区 (System Global Area，SGA)。SGA是由所有用户 进程共享的一块内存区域。数据库缓冲存储区（Database Buffer Cache）重做日志缓冲区（Redo Log Buffer）共享池（Shared Pool）Java池（Java Pool）大型池（Large Pool）等可选内存结构9ORACLE体系结构10用户进程发布并执行SQL语句的用户环境用户进程（User proce

5、ss）服务器进程（Server process）程序全局区（Program Global Area，PGA）当用户连接到Oracle服务器时，Oracle便创建一个服 务器进程与之交互，并代表该用户进程完成与 Oracle数据库间的交互。程序全局区PGA是用户专用的内存结构，存储该用 户连接期间与SQL语句执行相关的信息ORACLE体系结构11Oracle数据库有两种内存结构系统全局区（System Global Area，SGA）程序全局区（Program Global Area，PGA）系统全局区是一组共享内存结构，存放一个Oracle 数据库例程的控制信息和各共享用户的数据。程序全局区是

6、用户进程连接到数据库并创建一个会 话时，由Oracle服务器进程分配的专门用于当前用 户会话的内存区，该区域是私有的。ORACLE体系结构12(1) 数据库缓冲存储区用于存放最近访问的数据块大小由初始化参数定义DB_BLOCK_SIZE用于定义标准块的尺寸DB_CACHE_SIZE用于定义标准块大小的数据库缓冲 存储区DB_nK_CACHE_SIZE（n为2、4、8、16或32）定义非 标准块大小的数据库缓冲存储区Oracle采用LRU算法管理数据库缓冲存储区ORACLE体系结构13(2) 重做日志缓冲区存放数据库事务提交的操作信息，这些信息对数据库的恢复有着重要作用。当重 做日志缓冲区被添满

7、时，由日志写入进程把重做日志缓冲区的内容写到磁盘的重 做日志文件中保存。重做日志缓冲区log_buffer的大小在参数文件中设置。值越大，重做日志缓冲区 就可以存放更多的事务提交的记录，减少了数据被频繁写入到重做日志文件中的 次数。ORACLE体系结构14(3) 共享池主要包括共享SQL区、PL/SQL程序代码区(均包含在 库高速缓存中)和数据字典存储区，保存最近使用过的 SQL命令和数据字典信息。共享SQL区：包括许多可执行版本的SQL语句。可执行版本的 SQL语句是指经过了语法检查、编译，并给出最佳执行步骤以 便获得最优查询性能的SQL语句。这些可执行版本的SQL语句 可以被多个用户所共享

8、。数据字典存储区：存放系统中定义的数据库对象的信息，如 表的名称、表结构描述、表拥有者的权限情况等。数据库服 务器在运行期间，需要经常查询数据字典信息。ORACLE体系结构15(4) 大型池大型池用于为大的内存需求提供内存空间，大小由初始化参数LARGE_POOL_SIZE定义如果使用RMAN（恢复管理器）执行备份、转储和恢复， 或者需要执行并行复制，或者需要使用I/O Slaves提高I/O 性能，则应该配置大(5) Java池用于存放Java代码、Java语句的语法分析表、Java语句的执 行方案和支持Java程序开发大小由初始化参数JAVA_POOL_SIZE定义ORACLE体系结构16

9、(1) 排序区存放执行包含排序操作所产生的临时数据 大小由初始化参数SORT_AREA_SIZE设置SORT_AREA_RETAINED_SIZE保留不释放的内存大小(2) 会话区存储该会话所具有的权限、角色、性能统计等信息。(3) 游标状态区存储用户会话中当前使用的各游标所处的状态。(4) 堆栈区堆栈区存储该会话中的绑定变量（Bind Variable）和会话变量（Session Variable）及SQL运行时的内存结构信息。ORACLE体系结构17数据库写入进程（DBWR）数据库写入进程的作用是将已更改的数据块从内存 写入数据文件。默认情况下，启动例程时只启动了一个数据库写入 进程，即为

10、DBWR初始化参数DB_WRITER_PROCESSES最多定义20个数 据库写入进程执行写入操作每个数据库写入进程都分配了09或aj编号ORACLE体系结构18日志写入进程（LGWR）日志写入进程负责把重做日志缓冲区的数据写入重做日志文件中永久保 存。数据库写入进程在工作之前，需要了解日志写入进程是否已经把相关的 日志缓冲区中记载的数据写入硬盘中，如果相关的日志缓冲区中的记录 还没有被写入，DBWR会通知LGWR完成相应的工作，然后DBWR才开始 写入。触发条件：a) 1 meg of data appears in the log buffer: 当log buffer填满1M时触发 LG

11、WRb) every 3 seconds:每三秒触发一次c) when it is 1/3 full:当log buffer三分之一满时。d) upon commit：当执行提交的时候e) dbwr：当触发DBWR前,也就是数据写到数据文件之前。ORACLE体系结构19日志归档进程（ARCn）日志归档进程是一个可选进程该后台进程只有在ARCHIVELOG(归档日志)模式下才有效默认情况下只有两个归档日志进程(ARC0和ARC1)设置LOG_ARCHIVE_MAX_PROCESSES初始化参数最多可定 义30个日志归档进程每个日志归档进程都分配了09或at的编号在ARCHIVELOG模式下，当进

12、行日志切换时会自动生成归档 日志文件ORACLE体系结构20检查点进程（CKPT）作用是发出检查点(Checkpoint)，实现同步数据库的 数据文件、控制文件和重做日志确保数据文件、控制文件和重做日志文件的一致性ORACLE体系结构21其它后台进程系统监控进程(SMON)在数据库系统启动时执行恢复工作合并空间碎片并释放临时段进程监控进程(PMON)监控服务器进程的执行，并在服务器进程失败时清除该 服务器进程用于恢复失败的数据库用户的强制性进程可在V$BGPROCESS中查询当前启动的后台进程ORACLE体系结构22Oracle数据库的存储结构，包括数据的物理存 储结构和逻辑存储结构，两者是互

13、相关联的。物理存储结构是实际的数据存储单元，如文件或数 据块逻辑存储结构是数据概念上的组织，如数据库或表ORACLE逻辑结构23段(Segment)的管理区间(Extent)数据块(Data Block)的管理一个Oracle数据库可以拥有多个表空间，每个表空间可包含多个段，每个段 由若干个区间组成，每个区间包含多个数据块，每个Oracle数据块由多个OS 物理磁盘块组成。表空间由多个物理文件支持，具体存储表空间中的各对象ORACLE物理与逻辑结构24培训目标与概述25分区表何为分区表：即把数据分散保存，默认情况下一个表是一 个段，实现分区后一个表可有多个段(有多少个分区就有 多少个段)一个分

14、区使用一个表空间,一张表中的数据就可以保存到多 个表空间,默认一张表的数据只能保存一个表空间分区表的优势：在维护性方面，可以在分区级别，针对单独的分区，进行索引的维护、数据的 加载以及备份恢复等操作。大大降低了维护时长。在可用性方面，由于各个分区相对独立，当一个分区处于维护或者出现故障时， 不会影响到其他分区的正常使用。在性能方面，oracle对于用户的请求，只检索需要的分区，从而提升性能。在其他方面，由于分区表对于用户是透明的，因此，不需要在分区后，对代码 进行修改26常见分区表使用场景27数据随业务时间堆积增长，业务系统在运作时越来 越慢需要做历史数据清理的表对象 delete 和 ins

15、ert操作有明显性能优化需要例如经常有明确的谓词条件，如部门，常作与其他 对象作关联条件分区表先创建4个表空间，每个分区的数据可以保存到不同 的表空间，要求所有的表空间具有相同的块大小。 create tablespace a1 datafile/u01/app/oracle/oradata/orcl/a1.dbf size 50M;create tablespace a2 datafile /u01/app/oracle/oradata/orcl/a2.dbf size 50M;create tablespace a3 datafile /u01/app/oracle/oradata/orc

16、l/a3.dbf size 50M;create tablespace a4 datafile /u01/app/oracle/oradata/orcl/a4.dbf size 50M;28分区表创建基于范围的分区，把某个字段作为分区键create table b1 (id number,name varchar2(20)partition by range(id) (partition part1 values less than (20) tablespace p1, partition part2 values less than (40) tablespace p2, partitio

17、n part3 values less than (maxvalue) tablespace p3);29分区表30beginfor i in 1.60 loopinsert into pt1 values (i, i | _abcd1234); end loop;commit;end;/insert into pt1 values (null,java2);insert into pt1 (name) values (java1); -空值是录入到maxvale所在分 区commit;分区表查询，看下效果select count(*) from pt1;select count(*) fro

18、m pt1 partition (part1); select count(*) from pt1 partition (part3);查看表是否分区，分区类型select * from user_part_tables分区的详细信息，分区名称select * from user_tab_partitions31分区表按时间分区select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual; create table pt2(id int, birth date);insert into pt2 values (1,to_date(1999-10

19、-11 23:15:19,yyyy-mm-dd hh24:mi:ss);commit;select to_char(birth,yyyy-mm-dd hh24:mi:ss) from pt2;32分区表33按列表分区create table pt3 (id int,name varchar2(20), gender char(1)partition by list(gender) (partition pt31 values (M) tablespace p1, partition pt32 values (F) tablespace p2);insert into pt3 values (1

20、,java1,M); insert into pt3 values (2,java2,F); insert into pt3 values (3,java3,M); commit;select * from pt3 partition (pt31); select * from pt3 partition (pt32);分区表34散列分区：保存数据最平均提供的分区 是偶数。create table pt4 (id int,name varchar2(20)partition by hash(id) partitions 4store in (p1,p2,p3,p4);beginfor i in

21、 1.600 loopinsert into pt4 values (i, i | _abc); end loop;commit; end;/select count(*) from pt4 partition (sys_p24); select count(*) from pt4 partition (sys_p25); select count(*) from pt4 partition (sys_p26); select count(*) from pt4 partition (sys_p27);分区表35符合类型：范围列表create table pt6 (id int,name va

22、rchar2(20), gender char(1)partition by range(id) subpartition by list(gender) (partition pt61 values less than(20)(subpartition pt611 values(M)tablespace p1, subpartition pt612 values(F)tablespace p2),partition pt62 values less than (maxvalue) (subpartition pt621 values(M) tablespace p3,subpartition

23、 pt622 values(F) tablespace p4);select * from user_part_tables;分区表符合类型：范围hashcreate table pt7 (id int,name varchar2(20), gender char(1)partition by range(id) subpartition by hash(name) subpartitions 4 store in (p1,p2,p3,p4)(partition pt71 values less than (20), partition pt72 values less than (maxva

24、lue);36分区表37默认情况下，不允许修改分区键字段的值表默认是不启动行移动的，若要修改先启动行移动 启动行移动命令。alter table b3 enable row movement;实验是否可改变默认键值先查询select * from pt3; 看键值是多少。update pt3 set gender = F where id = 1;分区表如何变成分区表 1.exp/imp,OGG 2.Select insert 3.dbms_redefinition38培训目标与概述39性能诊断工具40系统性能诊断工具vmstat ,top/topas,sar,iostat,freeAwr性能

25、分析工具$ORACLE_HOME/rdbms/admin/awrrpt.sqlAsh性能分析工具$Addm性能优化建议工具$ORACLE_HOME/rdbms/admin/addmrpt.sql培训目标与概述41内容概述42课程主要讨论：SQL语句执行的过程、ORACLE优化器，表之间的关 联，如何得到SQL执行计划，如何分析执行计划等内 容，从而由浅到深的方式了解SQL优化的过程，使大 家逐步掌握SQL优化。性能管理43尽早开始设立合适目标边调整边监控相关人员进行合作及时处理过程中发生的意外和变化80/20定律SQL 优化衡量指标44随着软件技术的不断发展，系统性能越来越重要。系统性能主要用

26、：系统响应时间和并发性来衡量。造成SQL语句性能不佳大致有两个原因：开发人员只关注查询结果的正确性，忽视查询语句的效率。开发人员只关注SQL语句本身的效率，对SQL语句执行原理、影响SQL执行效率的主要因素不清楚。前者可以通过深入学习SQL语法及各种SQL调优技巧进行解决。 SQL调优是一个系统工程，熟悉SQL语法、掌握各种内嵌函数、分 析函数的用法只是编写高效SQL的必要条件。后者从分析SQL语句执行原理入手，指出SQL调优应在优化SQL解 析和优化CBO上。不同调整产生相应性能收益45调整的角色46SQL语句优化是提高性能的重要环节47开发人员不能只注重功能的实现，不管性能如何开发人员不能

27、把Oracle当成一个黑盒子，必须了解其结构、处 理SQL和数据的方法必需遵守既定的开发规范未经过SQL语句优化的模块不要上线SQL语句优化的过程48定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划什么是好的SQL语句？49尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁SQL共享原理50ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一

28、个SQL语句(有时被称为一个游标)时,如果它和之前 的执行过的语句完全相同,ORACLE就能很快获得已经被解析的 语句以及最好的 执行路径. 这个功能大大地提高了SQL的执行 性能并节省了内存的使用。SQL共享原理51为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中。这块位于系统全局区域 SGA(systemglobal area)的共享池(shared buffer poo1)中的 内存可以被所有的数据库用户共享。因此，当你执行一个SQL 语句(有时被称为一个游标)时，如果它和之前执行过的语句完全 相同，Oracle就能很快获得已经被解析的语句以及最好

29、的执行 方案。Oracle的这个功能大大地提高了SQL的执行性能并节省 了内存的使用。SQL共享的三个条件52当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同 （同义词与表是 不同的对象）两个SQL语句中必须使用相同的名字的绑定变量(bind variables)共享SQL语句53注意：Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必 须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个 条件： 字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。例如：SELECT * FROM ATABLE；和下面每

30、一个SQL语句都不同：SELECT* from ATABLESelect* From Atable； 语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同 的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变 量以相同的值：第一组 select pin，name from people where pin = ：blk1.pin；select pin，name from people where pin =：blk1.pin；第二组 select pin，name from people where

31、 pin =：blk1.ot_jnd； select pin，name from people where pin = ：blk1.ov_jnd；SQL语句的处理过程共享SQL区域54为什么要bind variables?55字符级的比较:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 与SELECT * FROM USER_FILESWHERE USER_NO = 10004321;检查：select name,executionsfrom v$db_object_cachewhere name like select * from us

32、er_files%什么叫做重编译问题56什么叫做重编译？下面这个语句每执行一次就需要在SHARE POOL 硬解析一 次，一百万用户就是一百万次，消耗CPU和内存，如果业务 量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可select * from dConMsgwhere contract_no = 32013484095139绑定变量解决重编译问题57未使用绑定变量的语句sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2);EXEC SQL EXECUTE I

33、MMEDIATE :sqlstr ; EXEC SQL COMMIT;使用绑定变量的语句strcpy(sqlstr, insert into test (num1, num2) values (:v1, :v2); EXEC SQL PREPARE sql_stmt FROM :sqlstr;EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2; EXEC SQL COMMIT;绑定变量的注意事项58注意：1、不要使用数据库级的变量绑定参数 cursor_sharing来强制绑定，无论其值为 force 还是 similar2、有些带 0性能优于se

34、lect count(*)from tab；尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多 or运算的查询，建议分成多个查询，用union all联结起来；多表查询 的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从 右到左，所以记录少的表放在右边。尽量多用commit语句提交事务，可以及时释放资源、解锁、释放 日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可 以常驻内存：alter tablecache；在Oracle中动态执行SQL，尽量用execute方式，不用dbms_sql包。自动选择索引66

35、如果表中有两个以上（包括两个）索引，其中有一个唯一性 索引，而其他是非唯一性在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯 一性索引举例:SELECT ENAME FROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;这里，只有EMPNO上的索引是唯一性的，所以EMPNO索 引将用来检索记录TABLE ACCESS BY ROWID ON EMPINDEX UNIQUE SCAN ON EMP_NO_IDX至少要包含组合索引的第一列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用

36、该索引.67SQL create table multiindexusage ( inda number , indb number , descr varchar2(10); Table created.SQL create index multindex on multiindexusage(inda,indb); Index created.SQL set autotrace traceonlySQL select * from multiindexusage where inda = 1; Execution Plan-SELECT STATEMENT Optimizer=CHOOSE

37、0TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE1INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE) SQL select * from multiindexusage where indb = 1;Execution Plan-SELECT STATEMENT Optimizer=CHOOSE0TABLE ACCESS (FULL) OF MULTIINDEXUSAGE很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引避免在索引列上使用函数68WHERE子句中，如果索引列是函数的一部分优

38、化器将不 使用索引而使用全表扫描举例:低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符69WHERE子句中, 如果索引列所对应的值的第一个字符由通 配符(WILDCARD)开始, 索引将不被采用.SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILESWHERE USER_NO LIKE %109204421;在这种情况下，ORACLE将使用全表扫描.避免在索引列上使用NOT70通常，我们要避免在索引列上使用NOT, NOT会产生

39、在和在 索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就 会停止使用索引转而执行全表扫描.举例:低效: (这里,不使用索引) SELECT FROM DEPTWHERE DEPT_CODE NOT = 0;高效: (这里,使用了索引) SELECT FROM DEPTWHERE DEPT_CODE 0;避免在索引列上使用 IS NULL和IS NOT NULL71避免在索引中使用任何可以为空的列，ORACLE将无法使用该 索引 对于单列索引，如果列包含空值，索引中将不存在此记 录. 对于复合索引，如果每个列都为空，索引中同样不存在此 记录.如果至少有一个列不为空，则记录存在于索

40、引中如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记 录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为 空，ORACLE将认为整个键值为空而空不等于空. 因此你可以 插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行 空值比较将使ORACLE停用该索引.任何在where子句中使用is null或is not null的语句优化器是 不允许使用索引的。避免出现索引列自动转换72当比较不同数据类型的数据时, ORACLE自动对列进行简单

41、 的类型转换.假设EMP_TYPE是一个字符类型的索引列.SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILESWHERE USER_NO = 109204421这个语句被ORACLE转换为:SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILESWHERE TO_NUMBER(USER_NO) = 109204421因为内部发生的类型转换, 这个索引将不会被用到!在查询时尽量少用格式转换73如用 WHERE a.order_no = b.order_no不用WHERE TO_NUMBER (substr(a.o

42、rder_no, instr(b.order_no, .) - 1)= TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1)使用DECODE来减少处理时间74例如:SELECT COUNT(*)，SUM(SAL) FROMEMPWHERE DEPT_NO = 0020AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMPWHERE DEPT_NO = 0030AND ENAME LIKESMITH%;你可以用DECODE函数高效地得到相同结果SELECT COUNT(DECODE(D

43、EPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH%;减少对表的查询75在含有子查询的SQL语句中,要特别注意减少对表的查询.例如:低效SELECT TAB_NAME FROM TABLESWHERE TAB_NAME = ( SELECT TAB_N

44、AME FROM TAB_COLUMNS WHERE VERSION = 604)ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)高效SELECT TAB_NAME FROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VERFROM TAB_COLUMNS WHERE VERSION = 604)Order by语句76ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语 句对要排序的列没有什么特别的限制，也可以将函数加入列

45、中（象联 接或者附加等）。任何在Order by语句的非索引项或者有计算表达式 都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。 解决这个问题的办法就是重写order by语句以使用索引，也可以为所 使用的列建立另外一个索引，同时应绝对避免在order by子句中使用 表达式。联接列77对于有联接的列，即使最后的联接值为一个静态值，优化器 是不会使用索引的。select * from employss wherefirst_name|last_name =Beill Cliton;系统优化器对基于last_name创建的索引没有使用。当采用下面这种SQL

46、语句的编写，Oracle系统就可以采用基 于last_name创建的索引。select * from employee wherefirst_name =Beill and last_name =Cliton;带通配符（%）的like语句78通配符（%）在搜寻词首出现，Oracle系统不使用last_name的索引。select * from employee where last_name like %cliton%;在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字 符串其他位置时，优化器就能利用索引。在下面的查询中索 引得到了使用：s

47、elect * from employee where last_name like c%;用索引提高效率79索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使 用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表 扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以 提高效率. 另一个使用索引的好处是,它提供了主键(primary key) 的唯一性验证。通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小 表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的

48、提 高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要 定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也 会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理, 那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引 是有必要的。避免在索引列上使用计算80WHERE子句中，如果索引列是函数的一部分优化 器将不使用索引而使用全表扫描低效：SELECT FROMDEPTWHERE SAL * 12 25000;高效:SELECT FROM DEPT WHERE SAL

49、 25000/12;用= 替代 81如果DEPTNO上有一个索引。高效:SELECT * FROM EMPWHERE DEPTNO 3通过使用=、=等，避免使用NOT命令82例子：select * from employee where salary 3000;对这个查询，可以改写为不使用NOT：select * from employee where salary3000;虽然这两种查询的结果一样，但是第二种查询方案会比第 一种查询方案更快些。第二种查询允许Oracle对salary列使 用索引，而第一种查询则不能使用索引。NOT NULL优化83低效: (索引失效)SELECT * FRO

50、M emp WHERE empno IS NOT NULL;高效: (索引有效)SELECT * FROMemp WHERE empno =0;尽量多使用COMMIT84事务是消耗资源的，大事务还容易引起死锁COMMIT所释放的资源:回滚段上用于恢复数据的信息.被程序语句获得的锁redo log buffer 中的空间ORACLE为管理上述3种资源中的内部花费用TRUNCATE替代DELETE85当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准 确地说是恢复

51、到执行删除命令之前的状况)而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短.计算记录条数86和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可 以通过索引检索,对索引列的计数仍旧是最快的.例如COUNT(EMPNO)比如有的表PHONE_NO字段是CHAR型,而且创建有索引， 但在WHERE条件中忘记了加引号，就不会用到索引。WHERE PHONE_NOWHERE PHONE_NO=1392020202287字符型字段的引号SQL select ename

52、,dnamefrom emp, deptwhere emp.deptno=dept.deptnoand dept.dname in (ACCOUNTING,RESEARCH,SALES,OPERATIONS);Execution Plan-SELECT STATEMENT Optimizer=CHOOSE0NESTED LOOPS1TABLE ACCESS (FULL) OF EMP1TABLE ACCESS (BY INDEX ROWID) OF DEPT3INDEX (UNIQUE SCAN) OF PK_DEPT (UNIQUE)88优化器与执行计划访问路径(方法) - access p

53、ath89优化器在形成执行计划时需要做的一个重要选择是如何从数 据库查询出需要的数据。对于SQL语句存取的任何表中的任何行，可能存在许多存取路径(存取方法)，通过它们可以定位和查询出需要的 数据。优化器选择其中自认为是最优化的路径。在物理层，oracle读取数据，一次读取的最小单位为数据库块(由 多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O 的最大值与multiblock参数共同决定，所以即使只需要一行数据，也 是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：1全表扫描（Full Table Scans, FTS）2通过ROWID的表存取（Tab

54、le Access by ROWID或rowid lookup）3索引扫描（Index Scan或index lookup）全表扫描为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足 语句的WHERE限制条件。Oracle顺序读取分配给表的每个数据块，直 到读到表的最高水线处(high water mark, HWM，标识表的最后一个数 据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_bl参数设定 ，而非只读取一个数据块，读的使用所一0G这极 方法 多块 HWM以一 般我 后，由FTSock_multi block_read_count)大的减少了I/O总次数，提

55、高了系统的吞吐量，所以利用多块 可以高效实现全表扫描，而且只有在全表扫描的情况下才能 读操作。在这种访问模式下，每个数据块只被读一次。由于 标识最后一块被读入的数据，而delete操作不影响HWM值， 个表的所有数据被delete后，其全表扫描的时间不会有改善， 们需要使用truncate命令来使HWM值归为0。幸运的是oracle 1 可以人工收缩HWM的值。模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表使用全表扫描的例子：90SQL explain plan for select * from dual;Query Plan的尾部，这样-可-以-使-其

56、-快-速-交-换-出-内-存-，-从-而-不-使-内-存重要的数据被 交换出内存。使用FTS的前提S：EL在EC较T大S的TA表T上E不M建EN议T使用C全HO表O扫S描E， C除o非st取=出数据 的比较多，超T过A总BL量E的A5C%C-E1S0S%，FU或L你L 想D使UA用L并行查询功能时。通过ROWID的表存取（Table Access by ROWID或rowid lookup）92行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位 置，所以通过ROWID来存取数据可以快速定位到目标数据上，是 Oracle存取单行数据的最快方法。为通过ROWID存取表，Oracle

57、首先要获取被选择行的ROWID，或从语 句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得 到。Oracle然后以得到的ROWID为依据定位每个被选择的行。此存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们 经常在执行计划中看到该存取方法，如通过索引查询数据。使用ROWID存取的方法：SQL explain plan for select * from deptwhere rowid = AAAAyGAADAAAAATAAF;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID DEPT ANA

58、LYZED索引扫描（Index Scan或index lookup）通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得 到的，该次i/o只会读取一个数据库块。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应 的ROWID值。索引扫由2步组成：(1) 扫描索引得到对应的rowid值。(2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但对于索引，由于经常使用，绝大多数都 已经CACHE到内存中，所以第1步的I/O经

59、常是逻辑I/O，即数据可以 从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可如下列所示： SQL explain plan for select empno, ename from empwhere empno=10;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID EMP ANALYZEDINDEX UNIQUE SCAN EMP_I192注意TABLE ACCESS BY ROWID EMP部分，这表明这不是通过FTS存 取路径访问数据，而是通过rowid lookup存取路径访问数据的。 此例中，所需

60、要的rowid是由于在索引查找empno列的值得到的， 这种方式是INDEX UNIQUE SCAN查找，后面给予介绍，EMP_I1为能使全用在的内进存行中索，引则查其找I/O的很索有引可名能字是。物理I/O，这是一个机械操作，相对逻辑I/O来说，极其费时间。所以如果对大表进行索引扫描，取出 的数据如果大于总量的5% - 10%，使用索引扫描会效率下降很多。根据索引的类型与where限制条件的不 同，有4种类型的索引扫描：索引唯一扫描(index unique scan)索引范围扫描(index range scan)索引全扫描(index full scan)索引快速扫描(index fas