用友软件股份有限公司2010年10月15日数据库知识以及优化数据的存储和访问数据的存储和访问oracle数据库体系架构oracle数据库体系架构临时表、索引临时表、索引sql执行计划sql执行计划等待事件等待事件Oracle组织结构PasswordfileInstanceSGARedoLogBufferSharedPoolDataDictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersUserprocessServerprocessPGAControlfilesDatafilesDatabaseDatabaseBufferCacheRedoLogfilesJavaPoolLargePoolParameterfileArchivedLogfilesOSNetDISKRAMOracle实例实例=内存分配+一组后台进程如果把Oracle比作一个图书馆，instance相当于图书馆的服务窗口和管理员一样,启动oracle前提应先启动instance.内存被分配，进程被启动SGA后台进程Oracle物理文件结构一个Oracle数据库是一个数据单位的集合，Oracle数据库可以分为逻辑(logical)结构和物理(physical)结构。物理结构指数据库中的操作系统文件的集合，包含以下类型的文件：数据文件(datafile)包含数据库中实际数据。数据包含在用户定义的表中，而且数据文件还包含数据词典(datadictionary)，数据修改以前的映象(before-imagesofmodifiedData)索引(indexes)和其他类型的结构(othertypesofstructures)。一个数据库中至少包含一个数据文件重作日志(Redologs)包含对数据库的修改记录，可以在数据失败后恢复。一个数据需要至少两组重作日志文件。控制文件(Controlfiles)包含维护和检验数据库一致性的信息。例如：控制文件用来检验数据文件和重作日志文件。一个数据库需要至少一个控制文件。其它文件其他文件参数文件用来定义Oracle实例的特性。例如：他包含SGA内存的大小。口令验证文件用来验证启动和关闭Oracle实例的用户。存档重做日志文件是重做日志的备份，用以恢复数据。告警和跟踪文件等：Alert*.log*.trcOracle实例组成结构Oracle实例:IsameanstoaccessanOracledatabase总是打开且仅仅与一个数据库关联由内存结构和管理数据库的一些后台进程组成后台进程内存结构实例SGARedoLogBufferSharedPoolDataDictionaryCacheLibraryCacheDBWRSMONPMONCKPTLGWROthersDatabaseBufferCacheJavaPoolLargePoolPGA_AGGREGATE_TARGET建议值OLTP系统SGA_target==(*80%)*80%PGA_AGGREGATE_TARGET=(*80%)*20%DSS系统SGA_target==(*80%)*50%PGA_AGGREGATE_TARGET=(*80%)*50%临时表空间的使用一般在内存排序空间不足使用临时段CREATEINDEXSELECT...ORDERBYSELECTDISTINCTSELECT...GROUPBYSELECT...UNIONSortmergejoinIntersectMinusanalyze数据的存储和访问数据的存储和访问oracle数据库体系架构oracle数据库体系架构临时表、索引临时表、索引sql执行计划sql执行计划等待事件等待事件空间管理可以在三个级别上对空间进行管理：使用文件（OS、ASM、裸分区）将磁盘空间分配给表空间。使用区将文件空间分配给表空间中的段。使用块来组织数据对象中的空间。Oracle存储的逻辑结构:Tablespace-Segment–Extent–Block数据库存储结构DatabasePRODDISK1/SYS1.dbfDISK2/nnc_data01.bdfDISK3/nnc_index01.dbfDISK1/UNDO1.dbfDISK1/TEMP.dbfD.D.TableDataSegD.D.IndexIndexSegRBSeggl_detailDataSeggl_voucherDataSegTempTempSeg12121211221FREE11221EXTENTSGl_detail(index)indexSegGl_voucher(index)IndexSegOracleDATABLOCKSRBS1(cont'd)RBSegRBS2(cont'd)RBSegRBS1RBSegRBS2RBSegSEGMENTSSYSTEMUSER_DATARBSTEMPTABLESPACESDATAFILES系统存储结构图TemporaryTemporaryIndexIndexCacheCacheClusterClusterRollbackRollbackDataDataSegmentSegmentTablespaceTablespaceDatabaseDatabaseFreeFreeExtentExtentUsedUsedInstanceInstanceDBBlockDBBlockOSBlockOSBlockFileFileLOBLOBLobIndexLobIndex段的分类1TableClusterTablepartitionIndex段的分类2Index-organizedtableIndexpartitionUndosegmentTemporarysegment段的分类3LOBsegmentBootstrapsegmentNestedtable数据库存储结构区管理可以采用两种方式对区进行分配：字典管理仅出于向后兼容才支持此方式在UET$和FET$字典表中对区进行管理为空间管理生成递归SQL本地管理在文件头位图中对区进行管理未在区操作上创建还原文件头块中可能发生争用不会为空间管理生成递归SQL本地管理下区的参数extent的参数initial最初分配的空间next下一步分配的空间数maxextents最大分配的extent数minextents最小分配的extnet数pctincrease增长率，指数级增长，optimal尽量设小，或为0(缺省为空，仅用于回滚段)freelist100()n1+区（Extent）一个区由一组数据库块组成，区是由段分配的，分配的第一个范围称初始区（initial），以后分配的范围称增量区（next）。下述情况下分配子段+段创建时+扩展时+修改时下述情况下释放子段删除时修改时清空时（Truncated）自动改变大小时(仅对回滚段）大型区：注意事项优点：不太可能以动态方式进行扩展提供少量性能优势通过单个I/O操作，使服务器进程能够读取整个区映射缺点：可能无法使用可用空间可能包含未使用的空间数据库块的内容Header(包含块地址、表目录、行目录、事务槽等信息)FreespaceData数据块(block)最小的I/O单元由一个或多个操作系统块组成在数据库创建时设定DB_BLOCK_SIZE是默认的块大小9i开始支持最多5种不同大小的block(2k,4k,8k,16k,32k)不能轻易进行更改应是操作系统(OS)块大小的整数倍较小的块大小：注意事项优点：减少块争用适合于较小的行适合于随机访问缺点：空间开销相对较高每块的行数较少可能导致要读取的索引块增多较大的块大小：注意事项优点：空间开销较低适合连续访问适合于非常大的行索引读取性能更好缺点：块争用增加在缓冲区高速缓存中使用较多的空间块分配当INSERT或UPDATE操作需要更多空间时，必须找到具有足够空间的块。可以使用两种方法来分配空间：手动段空间管理使用空闲列表自动段空间管理(ASSM)使用位图块空闲列表空闲列表管理的空间的特征：段头块保存所有空闲列表。可以将块添加到空闲列表或从空闲列表中删除。可以搜索空闲列表以查找可用的块。段头已被固定以便对空闲列表进行搜索和更新。块空间管理每个段都有用于控制块的内部空间使用情况的参数。对于表：PCTFREE：为更新保留的空间数量PCTUSED：在将块放置到空闲列表之前，块中可用空间的最低级别。对于索引：PCTFREE：创建索引时为新索引条目保留的空间数量PCTUSED：对于索引始终为0数据块空间的使用80%80%40%Inserts1234InsertsPCTFREE=20PCTUSED=40Insert(onfreelist)Inserts(offfreelist)ITL-InterestedTransactionList事务用户用户AA用户用户BBBlockHeaderDataBlockDataBlockLockedRowStep1Step1GetsanITLGetsanITLStep2Step2Locksarow.Locksarow.LockbytepointstoITLLockbytepointstoITLStep3Step3GetsanotherITLGetsanotherITLifavailableifavailableStep4Step4AttemptstolocktheAttemptstolockthesamerowandseessamerowandseeslockbyteforAlockbyteforAStep5Step5ChecksforChecksforcommittedflagincommittedflaginfirstITLfirstITLStep6Step6ChecksRBSheader(fromXID)tillChecksRBSheader(fromXID)tilltxnAcommits.ThenlocksrowtxnAcommits.Thenlocksrow自动段空间管理自动段空间管理(ASSM)的特征：使用位图块(BMB)管理空间。多个进程搜索不同的BMB。使用块满标志位表示块的可用性。块满程度用代表使用比率（25%、50%、75%和100%）的块满标志位来表示。使用ASSM的块空间管理插入插入插入1324插入25%50%75%25%50%75%创建自动段空间管理段SEGMENTSPACEMANAGEMENT是用于创建表空间的属性，以后不能对该属性进行修改。段空间管理是在表空间级别上声明的。表空间必须是永久性的和本地管理的。自动空间管理段是通过AUTO关键字指定的。对于空闲列表段，会使用默认值MANUAL。对于ASSM，在创建表时将忽略PCTUSED、FREELIST和FREELISTGROUPS。迁移和链接索引表移植链接设置PCTFREE和PCTUSED的准则PCTFREE默认：10如果没有UPDATE活动，则为零PCTFREE=100xUPD/（平均行长）PCTUSED仅用于空闲列表默认：40如果删除多个行，则设置此参数PCTUSED=100–PCTFREE–（100x行数x平均行长/块大小）检测迁移和链接使用ANALYZE命令检测迁移和链接：StatisticTotalPertransaction...---------------------------------------------...tablefetchcontinuedrow495.02…SQL>ANALYZETABLEoe.ordersCOMPUTESTATISTICS;TableAnalyzed.SQL>SELECTnum_rows,avg_row_len,chain_cnt2FROMDBA_TABLES3WHEREtable_name='ORDERS';NUM_ROWSAVG_ROW_LENCHAIN_CNT-------------------------------11716783使用Statspack/AWR检测迁移和链接：选择已迁移的行SQL>ANALYZETABLEoe.ordersLISTCHAINEDROWS;Tableanalyzed.SQL>SELECTowner_name,table_name,head_rowid2FROMchained_rows3WHEREtable_name='ORDERS';OWNER_NAMETABLE_NAMEHEAD_ROWID--------------------------------------SALESORDER_HISTAAAAluAAHAAAAA1AAASALESORDER_HISTAAAAluAAHAAAAA1AAB...消除已迁移的行导出/导入：导出表。删除或截断表。导入表。MOVE表命令：ALTERTABLEEMPLOYEESMOVE重新定义联机表复制已迁移的行：使用ANALYZE查找迁移的行。将迁移的行复制到新表。从原始表删除迁移行。将行从新表复制到原始表。收缩段：概览HWMHWM收缩操作数据未使用的空间数据未使用的空间回收的空间收缩段：注意事项收缩操作是联机的就地操作。它仅适用于位于ASSM表空间中的段。候选段类型为：按堆组织的表和按索引组织的表索引分区和子分区实体化的视图和实体化的视图日志LOB段对索引进行维护。不会触发触发器。通过使用SQL收缩段ALTER…SHRINKSPACE[COMPACT][CASCADE]TABLE[OVERFLOW]INDEXMATERIALIZEDVIEWMATERIALIZEDVIEWLOGMODIFYPARTITIONMODIFYSUBPARTITIONMODIFYLOBALTERTABLEemployeesSHRINKSPACECASCADE;ALTERTABLEemployeesENABLEROWMOVEMENT;ALTERTABLEemployeesMODIFYLOB(resume)(SHRINKSPACE);ALTERTABLEemployeesOVERFLOWSHRINKSPACE;1234段收缩：基本执行HWMALTERTABLEemployeesSHRINKSPACECOMPACT;HWMHWMALTERTABLEemployeesSHRINKSPACE;12段收缩：执行的注意事项仅在下列情况下使用压缩：为避免不必要的游标失效在高峰时段在压缩期间，可以执行DML操作和查询。在调整HWM时，会阻止DML操作。数据的存储和访问数据的存储和访问oracle数据库体系架构oracle数据库体系架构临时表、索引临时表、索引执行计划执行计划等待事件等待事件临时表两种类型:数据只有自己可见，当事务结束的时候清除数据SQL>Createglobaltemporarytabletemp1(colanumber)oncommitdeleterows;数据只有用户自己可见，当session退出的时候清除数据SQL>Createglobaltemporarytabletemp2(colanumber)oncommitpreserverows;临时表加随机数select/*+0.8480944423571652*/gl_voucher.pk_glorgbook,gl_detail.pk_accsubj,gl_detail.assid,sum(gl_detail.debitquantity)debitquantitysum,sum(gl_detail.creditquantity)creditquantitysum,sum(gl_detail.debitamount)debitamountsum,sum(gl_detail.creditamount)creditamountsum,sum(gl_detail.fracdebitamount)fracdebitamountsum,sum(gl_detail.fraccreditamount)fraccreditamountsum,sum(gl_detail.localdebitamount)localdebitamountsum,sum(gl_detail.localcreditamount)localcreditamountsumfromgl_detailgl_detail,gl_vouchergl_voucher,glTMP_assORAID,tmptabsubjORAwheregl_detail.pk_accsubj=tmptabsubjORA.pk_accsubjandgl_voucher.year='2010'andgl_voucher.free1>='01'andgl_voucher.free1<='07'andgl_voucher.pk_glorgbook='0001AA10000000032AH5'andgl_detail.pk_glorgbook='0001AA10000000032AH5'andgl_detail.dr=0andgl_voucher.discardflag='N'andgl_voucher.dr=0andgl_voucher.voucherkind<>255andgl_voucher.pk_manager='N/A'andgl_detail临时表空间的监控根据V$sort_usage定位哪些session正在使用临时表空间，使用了多少，分别使用了什么SQLSELECTSE.USERNAME,SE.SID,SU.EXTENTS,SU.BLOCKS*TO_NUMBER(RTRIM(P.VALUE))ASSPACE,TABLESPACE,SEGTYPE,SQL_TEXTFROMV$SORT_USAGESU,V$PARAMETERP,V$SESSIONSE,V$SQLSWHEREP.NAME='db_block_size'ANDSU.SESSION_ADDR=SE.SADDRANDS.HASH_VALUE=SU.SQLHASHANDS.ADDRESS=SU.SQLADDRORDERBYSE.USERNAME,SE.SID;索引逻辑上–单列或组合索引–唯一非唯一索引物理上–分区或非分区–B树–位图–正常或反向DML操作对索引的影响插入操作导致在适当的块中插入索引项删除行只导致逻辑删除索引项，删除的行所占用的空间难以用于新项，直到删除块中的所有项PctUsed对索引无效索引重建不稳定表的索引会引起性能问题.可能需要重建索引.监视索引SQL>ANALYZEINDEXacct_no_idxVALIDATESTRUCTURE;Indexanalyzed.SQL>SELECT(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*1002ASwastage3FROMindex_stats;WASTAGE-----------24如果Wastage>20%就应该考虑重建！SQL>ALTERINDEXacct_no_idxREBUILD;Indexaltered.收集索引信息使用系统包收集SQL>ExecuteDBMS_STATS.GATHER_INDEX_STATS(‘HR’,’LOC_COUNTRY_IX’);创建时收集SQL>Createindexhr.loc_country_ix………………………computestatistics;重建时收集SQL>Alterindexhr.loc_country_ixrebuildcomputestatistics;监视索引使用开始监视ALTERINDEXHR.EMP_NAME_IXMONITORINGUSAGE;停止监控ALTERINDEXHR.EMP_NAME_IXNOMONITORINGUSAGE;查看结果SELECTINDEX_NAME,USEDFROMV$OBJECT_USAGE;调整索引删除不必要的索引以减低DML的额外消耗SQL>alterindexHSPK_USERmonitoringusage;Indexaltered.SQL>alterindexHSPK_USERnomonitoringusage;Indexaltered.SQL>selectindex_name,usedfromv$object_usage;INDEX_NAMEUSE---------------------------------HSPK_USERNOB树索引和位图索引B树适合高基数的列更新关键字列的费用相对较低使用OR谓词的查询效率低对OLTP有用位图适合低基数的列更新关键字列的费用非常昂贵使用OR谓词的查询效率高对数据仓库有用创建大索引分配一个较大的临时表空间或者创建一个新的大尺寸的临时表空间用ALTERUSER语句让用户使用该临时表空间为用户设置大的排序区使用nologging/parallel数据的存储和访问数据的存储和访问oracle数据库体系架构oracle数据库体系架构临时表、索引临时表、索引等待事件等待事件执行计划执行计划等待时间dbfilescatteredread-DB文件分散读取这种情况通常显示与全表扫描相关的等待。当数据库进行全表扫时，基于性能的考虑，数据会分散(scattered)读入BufferCache。如果这个等待事件比较显著，可能说明对于某些全表扫描的表，没有创建索引或者没有创建合适的索引，我们可能需要检查这些数据表已确定是否进行了正确的设置。dbfilesequentialread-DB文件顺序读取。这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。在大多数情况下我们说，通过索引可以更为快速的获取记录，所以对于一个编码规范、调整良好的数据库，这个等待很大是很正常的。但是在很多情况下，使用索引并不是最佳的选择，比如读取较大表中大量的数据，全表扫描可能会明显快于索引扫描，所以在开发中我们就应该注意，对于这样的查询应该进行避免使用索引扫描。FreeBuffer-释放缓冲区这个等待事件表明系统正在等待内存中的可用空间，这说明当前Buffer中已经没有Free的内存空间。如果应用设计良好，SQL书写规范，充分绑定变量，那这种等待可能说明BufferCache设置的偏小，你可能需要增大DB_BUFFER_CACHE。FreeBuffer等待可能说明DBWR的写出速度不够，或者磁盘存在严重的竞争，可以需要考虑增加检查点、使用更多的DBWR进程，或者增加物理磁盘的数量,分散负载，平衡IO。BufferBusy-缓冲区忙该等待事件表示正在等待一个以unshareable方式使用的缓冲区，或者表示当前正在被读入buffercachelatchfree-latch释放latch是一种低级排队机制，用于保护SGA中共享内存结构。latch就像是一种快速地被获取和释放的内存锁。用于防止共享内存结构被多个用户同时访问。如果latch不可用，就会记录latch释放失败(latchfreemiss)。没有很好的是用绑定变量(librarycachelatch)、重作生成问题(redoallocationlatch)、缓冲存储竞争问题(cachebuffersLRUchain)，以及buffercache中的存在"热点"块(cachebufferschain)。FreeBuffer-释放缓冲区这个等待事件表明系统正在等待内存中的可用空间，这说明当前Buffer中已经没有Free的内存空间。如果应用设计良好，SQL书写规范，充分绑定变量，那这种等待可能说明BufferCache设置的偏小，你可能需要增大DB_BUFFER_CACHE。LogFileSwitch-日志文件切换当这个等待出现时，表示所有的提交(commit)的请求都需要等待"日志文件切换"的完成。数据的存储和访问数据的存储和访问oracle数据库体系架构oracle数据库体系架构临时表、索引临时表、索引执行计划执行计划等待事件等待事件SQL的解析过程SQL语句的解析执行步骤1语法分析分析语句的语法是否符合规范，衡量语句中各表达式的意义。2语义分析检查语句中涉及的所有数据库对象是否存在,及用户权限。3视图转换将涉及视图的查询语句转换为相应的对基表查询语句。4表达式转换将复杂的SQL表达式转换为较简单的等效连接表达式。5选择优化器不同的优化器一般产生不同的“执行计划”6选择连接方式对多表连接ORACLE可选择适当的连接方式。7选择连接顺序对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为驱动表。8选择数据的搜索路径根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。9产生“执行计划”并执行怎样查看执行计划用户运行$ORACLE_HOME/rdbms/admin/utlxplan.sqlSqlplus下setautotraceon(setautotracetraceonly)SQL>setautotraceonSQL>selectcount(*)fromt;COUNT(*)----------81344ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=99Card=1)10SORT(AGGREGATE)21TABLEACCESS(FULL)OF'T'(Cost=99Card=81344)怎样查看执行计划explainplanforselect…….from….where…..select*fromtable(dbms_xplan.display);select*fromtable(dbms_xplan.display_cursor('c9cxqvr3q4tjd'));基本表连接方式NestedloopsSortmergejoinHashjoin访问数据方式rowidIndexrangescanIndexfullscanSkipindexscanBitmapjoinindexHashFulltablescanCartesian……CBO与RBO什么是CBO和RBOoptimizer_modeFIRST_ROWSALL_ROWSCHOOSERULEDbms_stats分析表Analyzetable分析表表统计信息分析analyzetabletcomputestatistics;analyzetabletcomputestatisticsforallindexes;analyzetableirp_policybillcomputestatisticsforcolumnsAPPROVEDDATEsize254;begindbms_stats.gather_table_stats(user,'fa_card',estimate_percent=>null,method_opt=>'forallindexedcolumnssize100');end;表连接与子查询可以通过表连接取代子查询InNotin统计信息(Histogram)Histogram主要是为了计算列的选择性SQL>descdba_histogramsNameNull?Type-----------------------------------------------------------------------------OWNERVARCHAR2(30)TABLE_NAMEVARCHAR2(30)COLUMN_NAMEVARCHAR2(4000)ENDPOINT_NUMBERNUMBERENDPOINT_VALUENUMBERENDPOINT_ACTUAL_VALUEVARCHAR2(1000)柱状图信息绑定变量select*fromtwherecol=123;select*fromtwherecol=234;select*fromtwherecol=:v;Cursor_sharing=force/exact/similarOraclehintsHints主要用来改变SQL执行计划:指定优化模式指定表数据的获取方式指定表连接的顺序指定表连接的方式SQL性能与执行计划稳定性SQL性能的大幅度变化往往和执行计划的变化有关。一方面我们需要理解SQL的执行计划，为SQL选择最优的执行计划，另外一方面，在复杂的环境下，我们可能需要稳定执行计划。ORALCEStoredOutlinehintSQL优化基本原则降低I/O合理访问路径使用合理的表连接方式SQL优化基本原则创建索引修改sql语句，走合理的表关联一些数据库相关的隐含参数优化I/O的方法第一：最小化数据访问对于很多OLTP系统，大规模的IO访问常常是性能的瓶颈。在应用设计初期，尽量减少不必要的IO操作。优化大规模访问数据的SQL优化I/O的方法第二：均衡数据文件的分布根据访问频度，尽量分散不同的文件到不同的磁盘控制器。分离数据和索引(通常同时被访问，容易产生I/O瓶颈)分离大对象到独立的表空间分离数据文件和redolog文件.redo文件执行串行写入，如果放置在并发操作少的磁盘，可以大大提高LGWR的效率优化I/O的方法第三：合理使用临时表空间指定缺省的全局临时表空间CREATETEMPORARYTABLESPACEtempTEMPFILE'/u01/oradata/temp01.dbf'SIZE500MEXTENTMANAGEMENTLOCALUNIFORMSIZE10MALTERDATABASEDEFAULTTEMPORARYTABLESPACEtemp;为数据库重新指定临时表空间.所有用户临时表空间重新指向
