Oracle性能优化0.pptx

第9章Oracle性能优化9.1调整SQL9.1.1检查不合理的SQL语句Setautotraceonselectlast_name,hire_datefromhr.employeeswheresalary<5000unionselectlast_name,hire_datefromhr.employeeswheresalary>10000;SetautotraceoffSetautotraceonselectlast_name,hire_datefromhr.employeeswheresalary<5000orsalary>10000;Setautotraceoffselectdirector.named_namefromscott.student,scott.directorwherestudent.director_id=director.director_idandstudent.name='萧峰';selectdirector.named_namefromscott.directorwheredirector_id=(selectdirector_idfromscott.studentwherename='萧峰');理解Oracle访问路径访问路径就是从数据库中检索数据的方式。通常来说，检索一个表中少量的数据行应该使用索引访问，但是检索大量数据时全表扫描可能优于索引。全表扫描（FullTableScans）全表扫描将读取表的所有数据块，访问表中的所有行，每一行都要经WHERE子句判断是否满足检索条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，因此如果能够一次读取多个数据块，可以提高扫描效率，初始化参数B_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取数据块的最大数量。优化器何时会使用全表扫描在以下情况中优化器会使用全表扫描：1、无可用索引如下面例子：SELECTlast_name,first_nameFROMemployeesWHEREUPPER(last_name)=’TOM’12last_name字段有索引，但在查询中使用了函数，因此该查询不会使用索引。如果想让这个查询走索引，则需要建立函数索引createindexind_upper_lastnameonlast_name(upper(last_name))。特别要注意的是隐式转换，比如colx字段是varchar2型但存放数字：wherecolx=123456，这时会发生隐式转换TO_NUMBER(colx)，此时colx上的索引也会失效。2、大量数据如果优化器认为查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用全表扫描。3、小表如果一个表HWM之下的数据块比DB_FILE_MULTIBLOCK_READ_COUNT要少，只需要一次I/O就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。如果有这样小表访问频率又高，通常把它固定在内存中为好altertabletable_namestorage(buffer_poolkeep)。3使用SQLScratchpad动态观察3使用SQLScratchpad动态观察3使用SQLScratchpad动态观察3使用SQLScratchpad动态观察3使用SQLScratchpad动态观察9.1.2使用索引9.1.2使用索引9.1.2使用索引9.1.2使用索引9.1.2使用索引9.1.2使用索引9.1.2使用索引9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍9.1.3Oracle9i优化器介绍Oracle根据可用的访问路径和表、索引等对象的统计信息来确定当前SQL的哪个执行计划是最高效的或成本最低的；同时Oracle也会考虑Hints的建议。Oracle执行下列步骤：1、优化器根据可用的访问路径和Hints为SQL语句产生一组潜在的执行计划。2、优化器根据数据字典的统计信息评估每个计划的成本。成本就是一个评估值，它与SQL语句按照某个计划执行所消耗的计算机资源是成正比的。优化器基于对计算机资源(I/O、CPU、内存)的评估，计算访问路径和连接顺序的成本。3、优化器对比执行计划的成本，从而选择一个成本最低的执行计划。成本成本是用来描述工作单元或资源使用的。Oracle是用磁盘I/O、CPU和内存的使用情况来作为工作单元的，因此Oracle使用的成本可以描述为，在一次操作的执行过程中所用的磁盘I/O数量以及CPU和内存的总使用量。这里的操作可以是扫描一张表、通过索引访问表、连接两个