Oracle数据库性能调优课件

Oracle数据库性能优化中软培训中心何明引入复杂体系结构的原因资源不足有效地使用稀有资源内存与外存的比较总体上要优化社么？性能优化的领域包括：应用系统优化： # 糟糕的SQL语句 #资源的顺序使用 #糟糕的会话管理实例优化：#内存#数据库#实例的配置操作系统优化：#输入/输出（I/O）#交换区（swap）#操作系统参数在实例中优化什么？内存：#内存不足#糟糕的内存分配输入/输出（I/O)#带宽不足#糟糕的磁盘分配#糟糕的数据库配置实例配置#不适当的实例参数#糟糕的恢复和可获得性配置优化方法优化步骤：从上往下优化

#在优化应用代码之前优化设计#在优化实例之前优化代码优化潜在效益最大的区域#标识最长的等待#标识最大的服务时间达到目标时即刻停止优化定义问题发现并定义问题：倾听用户的反馈检查报警和追踪文件中的错误检查参数文件以发现不适当的参数配置检查内存、I/O、CPU的使用，标识资源使用异常的进程标识并优化消耗大量CPU或I/O的SQL语句收集实例和操作系统的统计信息设定优化的先后次序选择冲击最大的问题根据服务时间相对等待时间来分析系统的性能判断哪个部件消耗了最多的时间如果可能，向下挖掘并优化相应的部件普遍的优化问题最普遍的优化问题如下：SQL语句会话（链接）管理共享池的大小和竞争数据库缓冲区的大小和竞争数据块的竞争重做日志文件和重做日志缓冲区的优化还原（回滚）的优化输入/输出（I/O）问题（issues）锁的问题（issues）在整个生命周期中优化的步骤优化设计优化应用程序优化内存优化输入/输出(I/O)优化竞争优化操作系统应用系统的设计与开发应用系统的优化可以从设计和开发阶段就开始：检查主要功能的规范化（normalization）检查与数据结构相对应的访问时间仔细检查顺序处理的部分优化主要的报表优化数据量大的处理收集一组基线的统计信息一组基线的统计信息被用做：提供了一组系统运行在正常范围之内的统计信息将当前的统计信息与基线统计信息进行比较建立有关系统变化的假设性能与安全之间的折衷影响性能的因素：控制文件的多重映像在一个重做日志组中的成员的多重映像检查点的频率数据文件的备份数据库的归档数据块的校验（checksums）并行的用户数和并行的交易（事务）数系统体系架构优化两层架构？#优点#缺点三层（多层）架构？#优点#缺点两层架构的优化数据库服务器客户端应用程序？应用程序？三层（多层）架构的优化数据库服务器客户端应用程序？应用程序？应用服务器应用程序？逻辑（E-R模式）设计的优化数据库系统是联机事务处理系统吗？数据库系统是数据仓库（决策支持）系统吗？联机事务处理（OLTP）系统高吞吐量，

大量的插入和修改操作数据量大而且是持续增长许多用户并行访问优化目标是保证:可获得性高速型大量并行可恢复性数据仓库(决策支持）系统

频繁的在大数据量上查询频繁的使用全表扫描优化目标是保证:快速的响应时间重点放在SQL语句

的优化上

并行查询的特性主要是为数据仓库环境设计的Data两种系统逻辑设计的主要差别联机事务处理（OLTP)一般为三范式表的列数比较少尽可能少的数据冗余一般不存储导出数据数据仓库（DataWarehouse)经常带达不到三范式表的列数经常很多为了提高效率经常会有人为的数据冗余为了提高效率存储常用的导出数据事务处理与数据仓库系统的比较利用数据字典获取系统的设计设计的考虑标识活动表（ActiveTables）活动表应该设计的越小越好

前期优化的方法简单的设计（Simpledesign）数据模型（Datamodeling）仔细设计表和索引利用视图写高效率的SQL语句游标（Cursor）共享使用绑定变量选择性地使用SQL或PL/SQL动态SQL简化应用的设计简单的表好的SQL语句只在需要时创建索引只提前那些所需要的信息数据模型（DataModeling）精确地表示业务流程（做法）将重点放在最常见和最重要的营业事项上使用模型工具将数据规范化表的设计在适应性与性能之间进行折衷以规范化为主选择性地去规范化使用Oracle提高性能的特性默认值检查（Check）约束物化视图（Materializedviews）聚类(Clusters)将注意力放在对业务至关重要的表上索引的设计索引关键字主键唯一键外键只在经常查询的数据上加索引利用SQL语句来指导索引的设计使用视图简化应用的设计对终端用户使透明的可能造成执行计划优化的问题SQL语句执行的效率好的数据库连接使用游标最小化编译使用绑定变量怎样写共享游标的SQL语句？使用如下的方法创建通用的代码:使用存储过程和软件包使用数据库触发器使用其它的库例程和过程书写的格式标准:大小写空白符注释对象的引用绑定变量控制共享游标ControllingSharedCursorsCURSOR_SHARING

初始化参数可以设置为:EXACT(默认)SIMILAR(不推荐)FORCE性能优化的清单设置初始化参数和存储选项。核实SQL语句的资源使用

。验证中间件的连接。验证游标的关系。验证所需的所有对象的迁移。确认优化器所需的统计信息是有效的和可获得的。执行计划是什么？一个执行计划是：

在执行一条SQL语句和进行相关的操作时，优化器所执行的一组步骤。观察执行计划的方法EXPLAINPLAN

SQLTraceStatspackAutomaticWorkloadRepositoryV$SQL_PLANSQL*PlusAUTOTRACE使用执行计划决定当前的执行计划标识索引的效果绝对存取的路径验证索引的使用核实哪一个执行计划可以使用EXPLAIN

PLAN

命令产生一个优化器所使用的执行计划将该执行计划存储在PLAN

表中并不执行语句本身EXPLAIN

PLAN

命令的语法SETSTATEMENT_ID

='text'EXPLAINPLANINTOyourplantableFORstatementEXPLAINPLAN

命令:例子EXPLAINPLANSETSTATEMENT_ID='demo01'FORSELECTe.last_name,d.department_name

FROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_id;Explained.注意:EXPLAINPLAN

命令并不真正地执行这一语句。EXPLAINPLAN

命令的输出Planhashvalue:2933537672-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||106|2862|6(17||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|--------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")18rowsselected.SELECTPLAN_TABLE_OUTPUTFROMTABLE(DBMS_XPLAN.DISPLAY());

编译树（ParseTree）0SELECTSTATEMENTSORTJOIN12435MERGEJOINFULLTABLESCAN

of

EMPLOYEESTABLEACCESSBYINDEXROWID

of

DEPARTMENTSINDEXFULLSCANDEPT_ID_PK查询

V$SQL_PLANSQL_ID47ju6102uvq5q,childnumber0-------------------------------------SELECTe.last_name,d.department_nameFROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_idPlanhashvalue:2933537672--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||||6(100||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|--------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")24rowsselected.SELECTPLAN_TABLE_OUTPUTFROM

TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'));SQL*PlusAUTOTRACE语法OFFTRACE[ONLY]EXPLAINSTATISTICSSHOWAUTOTRACESETAUTOTRACEONSQL*PlusAUTOTRACE:

例子利用AUTOTRACE开始追踪语句:隐藏语句的输出:只显示执行计划:控制列设置的布局setautotraceonsetautotracetraceonlysetautotracetraceonlyexplainSQL*PlusAUTOTRACE:

统计信息setautotracetraceonlystatistics

SELECT*FROMproducts;Statistics------------------------------------------------------1recursivecalls0dbblockgets9consistentgets3physicalreads0redosize15028bytessentviaSQL*Nettoclient556bytesreceivedviaSQL*Netfromclient6SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)72rowsprocessedSpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

sizeLarge

poolJava

poolStreams

poolDefault

buffer

cacheKeep

buffer

cacheRecycle

buffer

cachenK

buffer

cachesRedo

log

bufferASH

bufferSort

extent

poolGlobal

context

poolSGAFlash

back

bufferInstanceFlashback

logsRedolog

filesArchive

logfilesControl

filesSYSTEMSYSAUXChange

tracking

filePassword

fileShared

pool动态SGA特性实现了一种基础结构，这种结构允许服务器在不关闭实例的情况下改变SGA的配置SGA的大小（尺寸）受限于参数

SGA_MAX_SIZE:被用作在实例启动时预留的虚拟内存不能动态地改变允许一些确定的SGA组成部分动态地调整大小。SELECTbytes

FROMV$SGAINFO

WHEREname='FreeSGAMemoryAvailable';自动共享内存管理:概述使用自动SGA和内存指导（advisors）来自动适应工作量的变化最大程度地利用内存帮助消除内存耗尽

的错误当使用SPFILE时，

可以避免再学习OnlineusersBatchjobsBuffercacheLargepoolSharedpoolJavapoolBuffercacheLargepoolSharedpoolJavapoolOnlineusersBatchjobsStreamspoolStreamspoolSGA大小（尺寸）:概述使用自动共享内存管理（ASMM），五个重要的SGA组件（部分）将会自动调整。非默认的缓冲（池）区不能自动调整。日志缓冲区是不能动态地调整的，但是它有一个好的默认值。DB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_nK_CACHE_SIZELOG_BUFFER_SIZESHARED_POOL_SIZEDB_CACHE_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZE自动调整的参数手工动态参数手工静态参数SGA_TARGETSGA_MAX_SIZE自动共享内存管理的好处DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZETotalSGAsizeSGA_TARGET

内存代理体系结构StatisticdeltasacrossdifferenttimeperiodsCircularSGAbufferofstatscapturedbyMMONMemoryBrokerPolicyModuleAdd

twogranules

toshared

pool.Output:resizerequestsTrade-offdifferentcomponentsbenefit/lostMMANtransfers

thememory.resizequeueMMANMMON手工动态重新调整系统

全局区（SGA）

参数对自动调整参数，手动调整大小:如果新值大于当前值，该组件（部分）的大小立即自动调整如果新值小于当前值，则修改尺寸的最小值手动调整参数会影响SGA的可调整部分。自动调整SGA参数的行为

当没有设置SGA_TARGET，或者它的值被设置为0时:要显式地设置自动调整参数注意:SHARED_POOL_SIZE

包括了内部启动的开销。与之前的版本比较，其值可能会需要增加当设置了SGA_TARGET

时:所有自动调整参数的默认值被设为零:非零值为下边界通过使用如下方法可以以MB来显示当前值:SELECTcomponent,current_size/1024/1024FROMV$SGA_DYNAMIC_COMPONENTS;手动调整SGA参数的行为手动调整的部分（组件）是:KEEP

和

RECYCLE

缓冲区（buffercaches）非默认数据块的内存缓冲区LOG_BUFFER手动调整的部分（组件）由用户说明。手工调整

SGA_TARGET中的组件可以精确地控制SGA的尺寸。使用V$PARAMETER

视图SGA_TARGET=8GSELECTname,value,isdefaultFROMV$PARAMETERWHEREnameLIKE'%size';DB_CACHE_SIZE=0JAVA_POOL_SIZE=0LARGE_POOL_SIZE=0SHARED_POOL_SIZE=0STREAMS_POOL_SIZE=0手动重新调整

SGA_TARGETSGA_TARGET初始化参数:是动态的可以增加到最大为

SGA_MAX_SIZE当所有的部分达到最小值时可以减少其大小这些变化只影响自动调整部分（组件）SGA中所包含的部分:固定SGA部分和其它内部分配部分自动调整SGA部分手动SGA部分允许Oracle服务器精确地分配总的共享内存的尺寸SGAsize=8GB关闭自动共享内存管理将

SGA_TARGET

设置为零改变自动调整功能。将自动调整参数设置为她们的当前值。SGA的大小作为整体不受影响，Parameters:SGA_TARGET=8GSHARED_POOL_SIZE=1GParameters:SGA_TARGET=0DB_CACHE_SIZE=4GSHARED_POOL_SIZE=1.5GLARGE_POOL_SIZE=512MJAVA_POOL_SIZE=512MSTREAMS_POOL_SIZE=512MSGAsize=8GBOriginalvaluesSGA_TARGET=0SGA指导（Advisor）设定共享池的尺寸使用自动共享内存管理。当数据有操作历史时，使用共享池指导（Advisor）来确认其它工具的诊断结果。当没有历史数据时，使用40%的SGA作为起点。监督并在需要时进行调整。当有可获得的空闲内存时，不用减少共享池的尺寸。SQL>SELECT*FROMV$SGASTAT2WHERENAME='freememory'3ANDPOOL='sharedpool';共享池指导（Advisory）SQL>SELECTshared_pool_size_for_estimateAS2pool_size,estd_lc_size,3estd_lc_time_saved4FROMV$SHARED_POOL_ADVICE;POOL_SIZEESTD_LC_SIZEESTD_LC_TIME_SAVED----------------------------------------328786840157868481778685617786864177868721778688017786888177868动态数据缓冲区指导（Advisory）参数数据缓冲区指导（advisory）特性可以开启和关闭收集预测不同缓冲区大小对系统性能影响的统计信息。使用这些统计所提供的信息来设定缓冲区的大小以优化某一指导的工作量

。通过使用

DB_CACHE_ADVICE初始化参数来开启数据缓冲区指导（advisory）:这一参数是动态的，它可以通过

ALTER

SYSTEM

命令来修改。该参数可以设定为以下三个值:OFF,ON，和READY。观察数据缓冲区指导（Advisory）数据缓冲区指导（advisory）的信息被收集在V$DB_CACHE_SIZE

视图中。该视图包含了不同的数据行，这些数据行是从当前缓冲区尺寸的10%到200%之间的不同缓冲区大小所对应的物理读的估算值。使用V$DB_CACHE_ADVICE

视图SELECTsize_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_readsFROMV$DB_CACHE_ADVICEWHEREname='DEFAULT'ANDadvice_status='ON'ANDblock_size=(SELECTvalueFROMV$PARAMETERWHEREname='db_block_size');

EstdPhysEstdPhysCacheSize(MB)BuffersReadFactorReads---------------------------------------------------(10%)303,80218.70192,317,943…24330,4161.3313,720,14927334,2181.1311,583,180(Current)30438,0201.0010,282,47533441,822.939,515,878…57772,238.676,895,122(200%)60876,040.666,739,731以企业管理器使用数据缓冲区指导（Advisory）共享池指导（Advisory）将查询的结果缓存在内存Oracle11g可将查询的结果常驻内存只要缓存的查询结果是有效的，其他的语句和会话就可以共享它们Oracle11g

内存参数使用索引指南只在需要时创建索引。为优化某一语句所创建是索引可能会影响其它的一些语句。最好是删除没用的索引可以使用EXPLAIN

PLAN

来决定一个索引是否被优化器所使用。索引的类型唯一和非唯一的索引复合索引索引存储技术:B*-tree–

正常（Normal）–

反转关键字（Reversekey）–

递减（Descending)–

基于函数（Functionbased）位图（Bitmap）域索引（Domainindexes）关键字压缩（Keycompression）什么时候使用索引IndexDoNotIndexKeysfrequentlyusedinsearchorqueryexpressionsKeysandexpressionswithfewdistinctvaluesexceptbitmapindexesindatawarehousingKeysusedtojointablesFrequentlyupdatedcolumnsHigh-selectivitykeysColumnsusedonlywith

functionsorexpressionsunlesscreatingfunction-basedindexesForeignkeysColumnsbasedonlyonquery

performanceDML操作对索引的影响插入操作会造成对相应的（索引）块进行索引项的插入（可能会造成索引块的分裂）。删除数据行会造成索引项的删除修改索引关键字列造成对索引的逻辑删除和插入。索引与外键（ForeignKeys）并不自动创建索引。automatically.在主表（父表）上进行DML操作会隐含地上锁。CUSTOMERS#cust_idSALESPRODUCTS#prod_idCHANNELS#channel_id标识没用的索引Oracle数据库提供了收集某一个索引使用情况的统计信息的能力。:避免空间的浪费通过消除在DML操作期间不必要的额外开销来改进系统的性能。开启和关闭索引使用的监督开始监督索一个引的使用:停止监督索一个引的使用:V$OBJECT_USAGE

视图包含了有关一个索引使用的信息ALTERINDEXcustomers_pkMONITORINGUSAGE;ALTERINDEXcustomers_pkNOMONITORINGUSAGE;INVISIBLE

Index不可见索引概述VISIBLE

IndexOptimizerviewpointDataviewpoint使用索引不使用索引修改索引修改索引修改表修改表OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE不可见索引的例子将索引更改为优化器不可见（即不使用）:优化器不会考虑以下的索引:优化器将总会考虑以下的索引:创建一个不可见的索引:ALTERINDEXdog_name_idx

INVISIBLE;SELECT/*+index(TABn.INDXn)*/COLiFROMTABnWHERE…;ALTERINDEXdog_name_idxVISIBLE;CREATEINDEXdog_age_idxONdog(age)INVISIBLE;复合索引（CompositeIndexes）复合索引的一些特性如下。索引的前导列在列组合中:cust_last_name

cust_last_namecust_first_namecust_last_name

cust_first_name

cust_gender

索引的前导列不在列组合中:cust_first_name

cust_gendercust_first_namecust_genderCREATEINDEXcust_last_first_gender_idx

ONcustomers(cust_last_name,cust_first_name,cust_gender);复合索引指南在WHERE子句中经常一起使用的那些键（列）上创建复合索引。创建复合索引以便WHERE子句中所使用的键组成前导部分。将哪些最经常使用的查询列放入复合索引的前导部分(leadingpart)。将哪些限制最多的列放入复合索引的前导部分(leadingpart)。BitmapIndex与正常的B*-tree索引相比，位图索引更快并且使用更少的空间。每个位图索引都是由一些被称为位图的小存储区域所组成。每个位图包含了每个索引列的特定值的信息。位图是以B*-tree结构存储，但在存储之前先进行了压缩。B-树索引IndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry位图索引B-树索引和位图索引的比较B-树（B-tree） 适合于high-cardinality

列对关建字列的修改相对不

算昂贵使用谓词OR/AND查询效率不高

用于OLTP位图（Bitmap）适合于low-cardinality

列对关建字列的修改非常昂贵使用谓词OR/AND查询效率高

用于datawarehousing（DSS）创建B-树索引CREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;创建B-树索引CREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;位图索引的优点当使用适当时，位图索引将提供:减少许多专门查询的响应时间与其它的索引技术相比，将节省数量可观的磁盘空间极大地提高性能位图索引指南通过以下措施来减少位图的存储空间:尽可能将索引列声明为非空（

NOT

NULL）尽可能将数据声明为定长类型使用如下命令:

ALTERTABLE…MINIMIZERECORDS_PER_BLOCK通过增加PGA_AGGREGATE_TARGET的值来提高位图索引的性能。位图连接（BitmapJoin）索引SalesCustomersCREATEBITMAPINDEXcust_sales_bjiONsales(c.cust_city)FROMsaless,customerscWHEREc.cust_id=s.cust_id;位图连接（BitmapJoin）索引不需要与CUSTOMERS表的连接。在执行如下的查询语句时，仅仅使用了位图连接索引和

SALES表:54SELECTSUM(s.amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idANDc.cust_city='Sully';位图连接索引的优缺点优点对连接查询性能非常好，而且节省空间对星型模型中大的维表非常有用缺点需要更多的索引:可以达到维表的每个列一个索引，而不是

每个维表一个索引。维护成本较高:建立或刷新位图连接索引时，需要进行连接。基于函数的索引CREATEINDEXFBI_UPPER_LASTNAMEONCUSTOMERS(upper(cust_last_name));SELECT*

FROMcustomersWHEREUPPER(cust_last_name)='SMITH';ALTERSESSION

SETQUERY_REWRITE_ENABLED=TRUE;基于函数的索引:使用基于函数的索引:适用于计算密集型的表达式方便与大小写无关的搜寻提供了数据压缩的简单方式可以用于NLS排序索引KEYROWID------------------------12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001... ...EMPLOYEE_IDLAST_NAME...--------------------7499ALLEN7369SMITH7521WARD...7566JONES7654MARTIN7698BLAKE7782CLARK.........反转关键字（ReverseKey）索引Indexonemployee_idcolumnemployeestable创建反转关键字索引创建一个反转关键字的唯一索引:创建一个唯一索引，然后将它改为反转关键字索引:SQL>CREATEUNIQUEINDEXi1_t1ONt1(c1)2REVERSEPCTFREE303STORAGE(INITIAL200KNEXT200K4PCTINCREASE0MAXEXTENTS50)5TABLESPACEindx;SQL>CREATEUNIQUEINDEXi2_t1ONt1(c2);SQL>ALTERINDEXi2_t1REBUILDREVERSE;IndexedaccessontableROWID索引表（Index-OrganizedTables）:概述Accessingindex-organizedtableRowheaderNon-keycolumnsKeycolumn索引表的特点索引表（Index-organizedtables）:必须有主键（primarykey）不能包含LONG类型的列可以重建可以通过主键或前导列（leadingcolumns）存取索引表（IOTs）的优缺点优点（Advantages）

对于涉及精确匹配和范围搜寻，IOTs提供了

基于使用关键字的快速存取。DML操作仅仅造成索引结构的修改。存储空间的需求减少。IOTs在如下的情况下很有用:–

应用程序是基于主键来检索数据的–

涉及基于目录信息的应用程序缺点（Disadvantages）

不适合于在判断中没有使用主键的查询创建索引表SQL>CREATETABLEcountry2(country_idCHAR(2)3CONSTRAINTcountry_id_nnNOTNULL,4country_nameVARCHAR2(40),5currency_nameVARCHAR2(25),6currency_symbolVARCHAR2(3),7mapBLOB,8flagBLOB,9CONSTRAINTcountry_c_id_pk10PRIMARYKEY(country_id))11ORGANIZATIONINDEX12TABLESPACEindx13PCTTHRESHOLD2014OVERFLOWTABLESPACEusers;Segment=SYS_IOT_OVER_nIOT_type=IOT_OVERFLOWSegment_type=TABLESegment=COUNTRY_C_ID_PKIOT_type=IOTSegment_type=INDEXIndex_type=IOT-TOP索引表（IOT）行溢出（Overflow）RemainingpartoftherowRowswithinPCTTHRESHOLDindxtablespaceuserstablespace从dba_tables

查询IOT的信息SQL>SELECTtable_name,iot_name,iot_type2FROMdba_tables;TABLE_NAMEIOT_NAMEIOT_TYPE-------------------------------------COUNTRYIOTSYS_IOT_OVER_2268COUNTRYIOT_OVERFLOW从dba_indexes

和dba_segments中查询IOT的信息SQL>SELECTindex_name,index_type,2tablespace_name,table_name2FROMdba_indexes;INDEX_NAMEINDEX_TYPETABLESPACETABLE_NAME---------------------------------------------COUNTRY_C_ID_PKIOT-TOPINDXCOUNTRYSQL>SELECTsegment_name,tablespace_name,2segment_type3FROMdba_segments;SEGMENT_NAME TABLESPACE_NAMESEGMENT_TYPE----------------- ---------------------------SYS_IOT_OVER_2268USERTABLECOUNTRY_C_ID_PKINDXINDEX进程对文件的I/O操作进行I/O优化的基本原则进行I/O优化的基本原则如下：尽量减少硬盘I/O操作将磁盘的I/O操作平衡（均匀）地分布到所有的硬盘和I/O控制器上使用合适的表空间将数据分布到不同的硬盘上数据文件与重做日志文件放在不同的硬盘上归档日志文件与重做日志文件放在不同的硬盘上不同的重做日志（成员）文件放在不同的硬盘上不同的控制文件最好也分别放在不同的硬盘上不同的归档日志文件最好也放在不同的硬盘上不同表空间对应的文件分别放在不同的硬盘上不同类型的数据存放在不同的表空间中减少与数据库无关操作的磁盘I/O表空间的使用system表空间只存sys用户的数据字典对象使用本地管理表空间以避免磁盘空间管理问题表和索引应该分别存放在不同的表空间中创建一个或多个单独存放回滚段的还原表空间大对象（LOB）应放在单独的LOB表空间创建一个或多个临时表空间检查I/O统计信息诊断工具ServerI/OutilizationSystemI/Outilization数据文件优化工具v$filestatv$tempstatv$datafile使用v$filestat视图SQL>SELECTphyrds,phywrts,2FROMv$datafiled,v$filestatf3WHEREd.file#=f.file#4ORDERBY;PHYRDSPHYWRTSNAME--------------------------------------806116/…/u01/system01.dbf168675/…/u04/temp01.dbf88/…/u02/sample01.dbf26257/…/u02/undots01.dbf65012564/…/u03/users01.dbf88/…/u01/query01.dbf6rowsselected输入/输出统计信息SQL>SELECTd.tablespace_nameTABLESPACE,2d.file_name,f.phyrds,f.phywrts3FROMv$filestatf,dba_data_filesd4WHEREf.file#=d.file_id;TABLESPACEFILE_NAMEPHYRDSPHYWRTS-------------------------------------------UNDO1/u02/undots01.dbf26257SAMPLE/u02/sample01.dbf65012564USERS/u03/users01.dbf88SYSTEM/u01/system01.dbf806116TEMP/u04/temp01.dbf168675QUERY_DATA/u01/query01.dbf886rowsselected.空间管理空间管理有三个层次:文件(OS,ASM,裸分区)将磁盘空间分配给表空间。.区段被用来将文件的磁盘空间分配给表空间中的段。数据块被用于在数据对象内部组织磁盘空间。区段（Extent）管理区段以两种方式分配:数据字典管理仅仅为了支持向前兼容区段在

EXT$

和

FET$

数据字典表中管理使用递归（Recursive）SQL语句管理

本地管理区段在文件头的位图中管理在区段的操作中不产生还原数据有可能造成文件头数据块的竞争本地管理的区段创建本地管理的表空间:默认区段的管理为本的的。SQL>CREATETABLESPACEuser_data_12DATAFILE3‘/oracle11goradata/db1/lm_1.dbf’4SIZE100M5EXTENTMANAGEMENTLOCAL6UNIFORMSIZE2M;大区段的赞成与反对的理由赞成（Pros）:动态扩展到可能较少可以改进一些性能能够使服务器进程以一次I/O读入整个区段的位图反对（Cons）:空闲磁盘空间可能无法获得可能会包含没用的磁盘空间将

SYSTEM

表空间迁移为本地管理的表空间

使用

DBMS_SPACE_ADMIN

软件包:

迁移过程对本地管理的系统（SYSTEM）表空间强加了一些限制。只有数据库系统是运行在限制（RESTRICTED）模式并且除了系统表空间、还原表空间、和临时表空间之外的所有表空间都处在只读（READ

ONLY）模式时，迁移才有可能。一定要在系统表空间迁移之前将其它需要转换的数据字典管理的表空间进行迁移。SQL>EXECUTEDBMS_SPACE_ADMIN.-

2TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');表是如何存储的表空间（Tablespace）TableATableBSegmentSegmentRowsColumnsTableBlocksRowpiece区段（Extent）数据块的解剖（Anatomy）BlockheaderFreespaceRowdataGrowth最小化数据块的访问通过一下方法最小化数据块的访问:

使用较大的块尺寸

数据行存放的紧凑（Packingrowstightly）

防止数据行的迁移TablespaceBlocksSegmentsExtentsDB_BLOCK_SIZE

参数数据块的尺寸（大小）:由

DB_BLOCK_SIZE参数所定义是在创建数据库时设置将成为表空间和内存缓冲区默认数据块的尺寸是数据文件读操作的最小I/O单位默认值为8KB；对于绝大多数IT平台最大可达32KB不能轻易改变应该为操作系统块尺寸的整数倍小数据块尺寸的考虑好处（Advantages）:减少块的竞争非常适合小的数据行非常适合随机访问短处（Disadvantages）:具有较大的磁盘空间的额外开销Hasarelativelylargespaceoverhead每个数据库只能存储少量的数据行Hasasmallnumberofrowsperblock可能会引起较多的索引块的读操作大数据块尺寸的考虑好处（Advantages）:较少的磁盘空间的额外开销适合于顺序访问适合于非常大的数据行较好的索引读性能短处（Disadvantages）:增加了数据块的竞争在内存缓冲区中使用较多的内存空间数据块空间的管理每个段都有控制数据块内部空间使用的参数。对于表:PCTFREE:为修改操作预留的空间量PCTUSED:在一个数据块被放入空闲列表之前该块中空闲空间的最小量对于所有:PCTFREE:为创建新索引记录所预留的空间量PCTUSED:对于索引总是为0利用空闲列表来管理的数据块空间的管理InsertsInsertsInserts1324In