数据库性能调整.

•调整概述•调整Oracle的内存使用•调整Oracle的磁盘利用•调整数据库的应用数据库性能调整•应用需求和SQL调整•监控锁冲突调整数据库的应用数据库管理的角色•应用调整是调整中最重要的部分•数据库管理员并不直接参与应用调整•数据库管理员必须熟悉不好的SQL语句对数据库性能的所产生的重要影响诊断工具概述•EXPLAINPLAN•SQL跟踪和TKPROF•SQL*Plus的AUTOTRACE•OracleSQL的ANALYZE命令ExplainPlan•使用explainplan时，可以不使用跟踪•使用explainplan：1.使用utlxplan.sql脚本创建PLAN_TABLE表2.运行EXPLAINPLANSQL命令3.查询PLAN_TABLE表，显示SQL语句的执行计划SQL@$ORACLE_HOME/rdbms/admin/utlxplanSQL跟踪和TKPROF1.设置初始化参数2.调用SQL跟踪3.运行应用4.关闭SQL跟踪5.使用TKPROF格式化跟踪文件6.解释输出SQL跟踪的使能及使不能–实例级:SQL_TRACE={TRUE|FALSE}–会话级:SQLaltersessionsetSQL_TRACE={true|false};SQLexecuteDBMS_SESSION.SET_SQL_TRACE2({true|false});SQLexecuteDBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION2(session_id,serial_id,{true|false});使用TKPROF格式化跟踪文件$tkproftracefile.trcoutput.txt[options]tracefile.trcoutput.txtUSER_DUMP_DESTTKPROF统计–Count:Numberofexecutioncalls–CPU:CPUsecondsused–Elapsed:Totalelapsedtime–Disk:Physicalreads–Query:Logicalreadsforconsistentread–Current:Logicalreadsincurrentmode–Rows:RowsprocessedSQL*Plus的AUTOTRACE–创建PLAN_TABLE表–运行ORACLE_HOME/sqlplus/admin目录中的plustrce.sql脚本–AUTOTRACE语法SQL@ORACLE_HOME/sqlplus/admin/plustrce.sqlSQLgrantplustracetoscott;setautotrace[off|on|traceonly][explain|statistics]优化器模式–Rule-based:•使用一个分级系统•语法和数据字典驱动的–Cost-based:•选择代价最小的路径•统计驱动的优化器模式的设置–实例级:optimizer_mode={choose|rule|first_rows|all_rows}–会话级:altersessionsetoptimizer_mode={choose|rule|first_rows|all_rows}–语句级:使用提示管理统计–使用ANALYZE命令可以收集或删除统计–使用DBMS_STATS包:•GATHER_TABLE_STATS•GATHER_INDEX_STATS•GATHER_SCHEMA_STATS•GATHER_DATABASE_STATS表统计–表的行数–表的数据块个数和空的数据块数–可用的平均空闲空间–链接或迁移的行数–行的平均长度–最后的ANALYZE日期和样本大小–数据字典视图:DBA_TABLES索引统计–索引层次(高度)–叶块和不同键的个数–每个键的叶块平均个数–每个键的数据块平均个数–索引条目的个数–聚集因子–数据字典视图:DBA_INDEXES列统计–不同值的个数–最小值，最大值–最后的ANALYZE日期和样本大小–数据字典视图:USER_TAB_COL_STATISTICS直方图–以更详细的方式描述一个特殊列的数据分布–为不均衡分布数据做更好的选择性评估–使用ANALYZETABLE...FORCOLUMNS…命令创建直方图–数据字典视图:DBA_HISTOGRAMSOracleCorporationConfidential1数据库之间统计复制CopytousertableUser-definedstatisticstableDatadictionaryUser-definedstatisticstableDatadictionaryExportImportCopyusertabletoDD432OracleCorporationConfidential举例:统计复制DBMS_STATS.EXPORT_TABLE_STATS(‘TRAIN’/*schemaname*/,‘COURSES’/*tablename*/,NULL/*nopartitions*/,‘STATS’/*statisticstablename*/,‘CRS990601’/*idforstatistics*/,TRUE/*indexstatistics*/);DBMS_STATS.CREATE_STAT_TABLE('TRAIN'/*schemaname*/,'STATS'/*statisticstablename*/,'USERS'/*tablespace*/);优化器计划的稳定性–允许应用强制使用一个所希望的SQL访问路径–通过数据库的改变维护一致性的执行计划–使用由提示构成的存储纲要来实现计划同等–SQL语句的文本必须匹配–计划的维护，通过:•新的Oracle版本•新的对象统计•初始化参数改变•数据库重组织•模式改变创建存储纲要SQLaltersession2setCREATE_STORED_OUTLINES=train;SQLselect...from...;SQLselect...from...;SQLcreateorreplaceOUTLINEco_cl_join2FORCATEGORYtrainON3selectco.crs_id,...4fromcoursesco5,classescl6whereco.crs_id=cl.crs_id;使用存储纲要–将USE_STORED_OUTLINES参数设置成TRUE或一个目录名–CREATE_STORED_OUTLINES和USE_STORED_OUTLINES都可以在实例级或会话级设置SQLaltersession2setUSE_STORED_OUTLINES=train;SQLselect...from...;维护存储纲要–使用OUTLN_PKG包可以:•删除纲要或纲要的目录•重命名目录–使用ALTEROUTLINE命令可以:•重命名一个纲要•重建一个纲要•改变一个纲要的目录–纲要存储在OUTLN模式中数据访问方法•为了提高性能，可以使用以下数据访问方法:–索引(B-树,位图,颠倒键)–索引组织表–聚集–直方图–物化视图B-树索引IndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry位图索引Blue,10.0.3,12.8.3,1000100100010010100Green,10.0.3,12.8.3,0001010000100100000Red,10.0.3,12.8.3,0100000011000001001Yellow,10.0.3,12.8.3,0010001000001000010KeyStartROWIDEndROWIDBitmapTableIndexBlock10Block11Block12File3位图索引–用于低基数的列–适合多个谓词–使用最少的存储空间–适合只读的系统–适合非常大的表创建和维护位图索引SQLcreateBITMAPINDEXord_region_id_idx2onord(region_id)3storage(initial200knext200k4pctincrease0maxextents50)5tablespaceindx01;B-树索引与位图索引的比较B-TreeindexesBitmapindexesSuitableforhigh-cardinalitySuitableforlow-cardinalitycolumnscolumnsUpdatesonkeysrelativelyUpdatestokeycolumnsveryinexpensiveexpensiveInefficientforqueriesusingEfficientforqueriesusingORpredicatesORpredicatesRow-levellockingBitmapsegment-levellockingMorestorageLessstorageUsefulforOLTPUsefulforDSSKEYROWID------------------------12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001......颠倒键索引IndexonEMPNOEMPTableEMPNOENAMEJOB...---------------------7499ALLENSALESMAN7369SMITHCLERK7521WARDSALESMAN...7566JONESMANAGER7654MARTINSALESMAN7698BLAKEMANAGER7782CLARKMANAGER.........创建颠倒键索引SQLcreateuniqueindexi1_t1ONt1(c1)2REVERSEpctfree303storage(initial200knext200k4pctincrease0maxextents50)5tablespaceindx01;SQLcreateuniqueindexi2_t1ONt1(c2);SQLalterindexi2_t1REBUILDREVERSE;常规表的访问ROWID索引组织表索引组织表的访问Non-keycolumnsKeycolumnRowheader索引组织表与常规表的比较–更快地基于键值访问表数据–减少存储的需求–次要索引和逻辑ROWID–主要限制:•必须有一个主键•不能使用唯一性约束•不能被聚集创建索引组织表SQLcreatetablesales2(office_cdnumber(3)3,qtr_enddate4,revenuenumber(10,2)5,reviewvarchar2(1000)6,constraintsales_pk7PRIMARYKEY(office_cd,qtr_end)8)9ORGANIZATIONINDEXtablespaceindx10PCTTHRESHOLD2011INCLUDINGrevenue12OVERFLOWTABLESPACEuser_data;Segment=SYS_IOT_OVER_nIOT_type=IOT_OVERFLOWSegment_type=TABLESegment=SALES_PKIOT_type=IOTSegment_type=INDEXIndex_type=IOT-TOP索引组织表的行溢出RemainingrowpartRowswithinPCTTHRESHOLDINDXtablespaceUSER_DATAtablespace索引组织表的字典视图SQLselecttable