ORACLE进阶与提高专题讲义

ORACLE进阶与提高王忠海1/11/2023主要内容数据库优化RMANRAC(如果有时间的话）数据库优化操作系统设置不当数据库参数设置不当库结构设计不合理应用程序语句不当可能影响数据库性能的原因有哪些？AIX5.3中操作系统优化Oracle用户资源限制操作系统核心参数补丁AIX中一些基本的查看资源的命令如何查看CPU数量如何查看内存数量如何查看交换空间文件系统使用情况AIX基本命令：版本信息#oslevel#oslevel-r5300-07#oslevel-s5300-07-01-0748AIX基本命令：查看CPU信息#lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processor#lsattr-EHlproc0attributevaluedescriptionuser_settablefrequency2096901000ProcessorSpeedFalsesmt_enabledtrueProcessorSMTenabledFalsesmt_threads2ProcessorSMTthreadsFalsestateenableProcessorstateFalsetypePowerPC_POWER5ProcessortypeFalseAIX基本命令：查看内存信息bootinfo–r16318464#lsdev-CcmemoryL2cache0AvailableL2Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalseAIX基本命令：管理交换空间查看交换空间#lsps-aPageSpacePhysicalVolumeVolumeGroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv设置交换空间smitchps交换空间设置建议文件系统的设置看看下面的输出。您认为最该调整哪个文件系统大小？#df-mFilesystemMBblocksFree%UsedIused%IusedMountedon/dev/hd4256.00252.121%23531%//dev/hd24096.002613.5937%381386%/usr/dev/hd9var4096.004003.593%8821%/var/dev/hd3128.00120.165%19381%/tmp/dev/hd11024.00514.9550%37424%/home正是tmp文件系统！如果不调整，安装数据库时将无法成功。AIX5.3核核心参数数调整适用范围围在AIX5.3-01以上上需要调整整的参数数：lru_file_repageminperm%、maxperm%和和maxclient%等等等。。参考文档档：在AIX中中运行Oracle的优化化技巧.mhtoracle用用户的资资源限制制修改/etc/security/limits看一个实实例：oracle:fsize=209715100data=20971510stack=20971510core=20971510rss=25165824AIX中中Oracle参数设设置SGA设设置建议议SGA不不要超过过总内存存数*maxperm%回顾：重重要的SGA内内存参数数Shared_pool_sizeDb_cache_sizeDb_keep_cache_sizeLarge_pool_sizeLog_buffer回顾：重重要的PGA内内存参数数PGA_AGGREGATE_TARGET如何在AIX5.3中中将SGA定在在内存中中？修改系统统参数：：v_pinshm修改Oracle参数数：LOCK_SGA参考文档档：如何在Aix5.3中中将OracleSGA定定在内存存中.docAIX中中其他的的需要调调整的Oracle参参数TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj大量导入入数据前前需要做做些什么么？是否需要要备份？？估计数据据量考虑设置置为非归归档模式式考虑删除除一些索索引，导导完之后后再创建建大量导入入数据之之后应该该做些什什么？分析表。。DBMS_STATS.GATHER_SCHEMA_STATS考虑设置置归档备份哪些因素素最影响响性能？？CPU消消耗内存磁盘IO排序提高数据据库性能能的方法法用更好的的硬件！！说服用户户将就着着用优化数据据库优化客户户端程序序案例：解解决CPU100%占用步骤一：：检查cpu信信息：vmstat步骤二：：定位CPU高高消耗进进程psaux|head-1;psaux|sort+2-rn|head-5案例：解解决CPU100%占用（（续）步骤三：：定位有有问题的的语句SELECT/*+ORDERED*/sql_textFROMv$sqltextaWHERE(a.hash_value,a.address)IN(SELECTDECODE(sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address)FROMv$sessionbWHEREb.paddr=(SELECTaddrFROMv$processcWHEREc.spid='&pid'))ORDERBYpieceASC/案例：解解决CPU100%占用（（续）步骤四：：定位有有问题的的会话SELECTSID,SERIAL#,USERNAME,TERMINALFROMv$sessionbWHEREb.paddr=(SELECTaddrFROMv$processcWHEREc.spid='&pid')/案例：解解决CPU100%占用（（续）步骤五：：采取相相关行动动1.杀掉掉会话altersystemkillsession‘sid,serial#’;2.分析析原因优化SQL语句句，最具具挑战的的工作为什么要要优化？？OracleSQL调整过过程1.确定定由高影影响力的的SQL2.抽抽取和和解释释SQL语语句3.调调整SQL语句句用V$SQLAREA确确定影影响力力高的的语句句executionsdisk_readsbuffer_getssorts哪些工工具可可以来来查看看SQL执执行计计划autotracealtersessionsetsql_trace=true;dbms_system.set_sql_trace_in_sessionexplainplanEtc.查看语语句执执行计计划的的简单单办法法SetautotraceonSetautotracetraceonly前提：：存在在plan_table表。。如果果不存存在，，可执执行@?/rdbms/admin/utlxplan.sql执行语语句相关技技巧：：settimingonsettimeon案例：：解读读sql语语句执执行计计划SQL>selectcount(*)fromlpmnt;COUNT(*)1155ExecutionPlanPlanhashvalue:3530445977|Id|Operation|Name|Rows|Cost(%CPU)|Time||0|SELECTSTATEMENT||1|3(0)|00:00:01||1|SORTAGGREGATE||1||||2|INDEXFASTFULLSCAN|LPMNT_DBID_IDX|1102|3(0)|00:00:01|Note-dynamicsamplingusedforthisstatementStatistics509recursivecalls0dbblockgets190consistentgets105physicalreads0redosize412bytessentviaSQL*Nettoclient381bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient6sorts(memory)0sorts(disk)1rowsprocessedsetautotrace的局限性性必须执行一一遍语句explainplan使使用方法Explainplan{setstatement_id=’<yourID>’}{intotable<tablename>}for<SQLstatement>查看explainplan过的语语句的执行行计划先做些格式式化工作：：setpagesize1000coloperationformata20coloptionsformata20colobject_nameformata20colpositionformat999执行语句：：selectlpad('',2*(level-1))||operationoperation,options,object_name,positionfromplan_tableCONNECTBYPRIORid=parent_idandstatement_id='statement_id';案例：分析析winsvr执行行的语句工具：altersystemsetsql_trace=true;(如果想看看每个执行行步骤地时时间信息，，要设置timed_statistics参参数）查看生成的的trace文件在user_dump_dest环境境变量所对对目录下。。ls-ln用tkprof命令令来格式化化输出解读输出。。set_sql_trace_in_sessiondbms_system.set_sql_trace_in_session优化数据库库的工具：：statspack通过statspack可以以很容易做做出Oracle系系统性能的的全面报告告，是OracleDBA管理Oracle9i必必须掌握的的性能调优优工具。安装statspack创建一个statspack专用表空空间运行?/rdbms/admin/spcreate.sql如果是windows平台，，则运行：：?\rdbms\admin\spcreate.sql使用Statspack准备备工作规划自动STATSPACK数据搜集集。运行?\rdbms\admin\spauto.sql可可以设置自自动搜集statspack数据。这这个脚本创创建了一个个作业。为为了运行这这个作业，，要保证job_queue_processes参数数大于0，，而且要使使用statspack所属属用户来执执行。例如如下面的脚脚本设置每每1小时进进行一次statspack：variablejobnonumber;variableinstnonumber;beginselectinstance_numberinto:instnofromv$instance;dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate+1/24,'HH'),'trunc(SYSDATE+1/24,''HH'')',TRUE);commit;end;/调整statspack参数数executions_th：SQL语句执行行的数量（（默认100）disk_reads_th：sql语句执行行的磁盘读读入数量（（默认1000）parse_calls_th：sql语句执执行的解析析调用数量量（默认1000））buffer_gets_th：sql语句执执行缓冲区区获取的数数量（默认认10000）通过statspack..modify_statspack_parameter函数数可以改变变阈值的默默认值。阈阈值存放在在stats$statspack_parameter中。改变阈值举举例：sql>execstatspack.modify_statspack_parameter(i_buffer_gets_th=>20000);用statspack搜集信信息execstatspack.snap;等待一会……或者执行行一些语句句execstatspack.snap;生成statspack报告告@?/rdbms/admin/spreport解读statspack报告告移除自动执执行STATSPACK收集集--先查看当前前自动收集集的jobsSQL>selectjob,log_user,priv_user,last_date,next_date,intervalfromuser_jobs;--移除任任务1SQL>executedbms_job.remove('1');删除统计资资料SQL>selectmax(snap_id)fromstats$snapshot;SQL>@C:\oracle\ora92\rdbms\admin\sptrunc.sql;发现占资源源高的语句句后怎么办办？改变语句写写法（最好好）适当增加索索引改变操作系系统参数分析表使用大纲优化案例：：通过改变变语句写法法来提高性性能举一个日期期查询的例例子优化案例：：通过增加加索引来提提高性能优化案例：：通过分析析表来提高高性能RMANRMAN备备份的优点点：RMAN会会检测和报报告损坏的的数据块不需要将表表空间置于于热备模式式，RMAN就可以以备份数据据库。因此此热备期间间生成的重重做会减少少RMAN自自动跟踪更更新新的数数据文件和和表空间，，这样就不不再需要在在脚本中添添加新的表表空间和数数据文件RMAN只备备份使用过的的数据块，这这样RMAN备份通常小小于联机脚本本备份RMAN可以以与第三方介介质管理产品品一起无缝地地工作RMAN支持持增量备份可以测试备份份而不需要实实际还原。如何配置RMAN将数据库配置置为ARCHIVELOG模式创建恢复目录录第一步，在目目录数据库中中创建恢复目目录所用表空空间：SQL>createtablespacerman_tsdatafile"d:\oracle\oradata\rman\rman_ts.dbf"size20M;第二步，在目目录数据库中中创建RMAN用户并并授权：SQL>createuserrmanidentifiedbyrmandefaulttablespacerman_tstemporarytablespacetempquotaunlimitedonrman_ts;SQL>grantrecovery_catalog_owner,connect,resourcetorman;第三步，在目目录数据库中中创建恢复目目录C:\>rmancatalogrman/rman恢复管理器：：版本8.1.6.0.0-ProductionRMAN-06008：：连接到恢复复目录数据库库RMAN-06428：：未安装恢复复目录RMAN>createcatalogtablespacerman_ts;RMAN-06431：：恢复目录已已创建注册数据库CONNECTTARGETSYS/PWD@TESTDB;REGISTERDATABASE配置RMAN的默认设置置CONFIGURE：用于RMAN操作的默认认设备类型，，如磁盘或者者磁带(SBT)执行自动备份份和恢复操作作时自动分配配的通道数配置备份时的的排斥策略配置自动备份份时给定备份份片的最大空空间和任何备备份集的大小小配置备份最优优化的默认设设置。可以是是ON或者OFF.快照控制文件件的默认名称称是否自动备份份控制文件辅助数据文件件的默认名称称默认的保存策策略显示默认设置置举例：SHOWDEFAULTDEVICETYPE;SHOWMAXSETSIZE;SHOWRETENTIONPOLICY;SHOWALL;（显显示全部配置置）CONFGIURE命令令举例下面语句设置置默认磁盘类类型和并发度度CONFIGUREDEVICETYPEDISKPARALLELISM2;下面设置默认认备份文件大大小。CONFIGURECHANNELDEVICETYPEDISKMAXPIECESIZE500M;下面语句配置置默认存储位位置等信息：：CONFIGURECHANNELDEVICETYPEDISKFORMAT'D:\oracle\backup\GAXZ\RMAN\%d_%s_%p_%c';下面面语语句句设设置置CHANNEL1和和CHANNEL2,用用于于RAC数数据据库库CONFIGURECHANNEL1DEVICETYPEdiskCONNECT'sys/oracle@ora921'MAXPIECESIZE10g;CONFIGURECHANNEL2DEVICETYPEdiskCONNECT'sys/oracle@ora922'MAXPIECESIZE10g;FORMAT格格式式%c备备份份片片的的拷拷贝贝数数%d数数据据库库名名称称%D位位于于该该月月中中的的第第几几天天(DD)%M位位于于该该年年中中的的第第几几月月(MM)%F一一个个基基于于DBID唯唯一一的的名名称称%n数数据据库库名名称称，，向向右右填填补补到到最最大大八八个个字字符符%u一一个个八八个个字字符符的的名名称称代代表表备备份份集集与与创创建建时时间间%p该该备备份份集集中中的的备备份份片片号号，，从从1开开始始到到创创建建的的文文件件数数%U一一个个唯唯一一的的文文件件名名，，代代表表%u_%p_%c%s备备份份集集的的号号%t备备份份集集时时间间戳戳备份份整整个个数数据据库库backupdatabaseformat'D:\oracle\backup\GAXZ\RMAN\ora_d%d_s%s_s%p_f%t'filesperset4plusarchivelogdeleteinput;增量备份份差异(Differential)备份是是默认的的增量备备份类型型，差异异备份会会备份上上一次进进行的同级或者者低级备份以来来所有变变化的数数据块，，而累积积(cumulative)备份，，则备份份上次低级级备份以来所有有的块。。例如，，星期一一进行了了一次2级备份份，星期期二进行行了一次次3级备备份，如如果星期期四进行行3级差差异增量量备份，，那么只只备份上上次3级级备份以以来变化化过的数数据块；；如果进进行累积积3级备备份，那那么就会会备份上上次2级级备份以以来变化化的数据据块。案例：增增量备份份首先要做做一个0级备份份backupINCREMENTALLEVEL0databaseplusarchivelogdeleteinput;做一个1级备份份：backupINCREMENTALLEVEL1databaseplusarchivelogdeleteinput;做一个1级累计计备份：：backupINCREMENTALLEVEL1CUMULATIVEDATABASEdatabaseplusarchivelogdeleteinput;备份特特定内内容备份表表空间间备份数数据文文件backupdatafile7format'/backup/rman/ora_d%d_s%s_s%p_f%t';备份归归档日日志backuparchivelogalldeleteinput;RMAN报报告LISTRMAN的的list命令是一一种在数据据库控制文文件或者恢恢复目录中中查询备份份的历史信信息的方法法。列表提提供了一组组信息，可可以提供各各种备份的的信息，如如对应物、、备份集、、归档日志志备份、控控制文件备备份等等。。REPORTRMAN的的report命令令被用于判判断数据库库的当前可可恢复状态态和提供数数据库备份份的特定信信息、报告告最近没有有备份的数数据文件等等信息。常用LIST命令列出所有备备份：Listbackupset;列出所有备备份简要信信息：Listbackupsetsummary;列出指定备备份集备份份信息Listbackupsetbs#;列出过期期的备份份：listexpiredbackup;列出指定定表空间间的备份份信息：：Listbackupoftablespaceusers;列出所有有已备份份的归档档：listbackupofarchivelogallsummary列出所有有需要备备份的归归档：Listarchivelogall;常用REPORT命令令报告最近近10天天没有备备份的数数据文件件reportneedbackupdays=10;报告按照照默认策策略需要要备份的的文件Reportneedbackup;按照指定定策略报报告需要要备份的的文件reportneedbackupredundancy=2;报告数据据库信息息：Reportschema;报告过期期的备份份Reportobsolete;备份集的的维护删除过期期备份：：deleteobsolete;用deletenopromptobsolete可无需需提示。。删除指定定的文件件集：deletebackupsetbs#;删除所有有备份：：deletebackup;验证备份份集，如如果备份份集不复复存在，，将被标标记为expired：crosscheckbackup;删除expired备备份集：：Deleteexpiredbackup;恢复数据据库恢复案例例1：丢丢失SYSTEM表空空间restoredatafile1;recoverdatabase;alterdatabaseopen;恢复案例例2：丢丢失参数数文件1.编辑辑一个init.ora，内内容包括括：db_name=GAXZinstance_name=GAXZcontrol_files='D:\oracle\product\10.2.0\oradata\GAXZ\CONTROL01.CTL'db_block_size=8192shared_pool_size=1048576002.RMAN连连接到目目标数据据库。3.startupnomountpfile='D:\RMAN\INIT.ORA';4.restorespfile;5.Shutdownimmediate;6.startup恢复案例例3：恢恢复控制制文件Startupnomount;Restorecontrolfile;Recoverdatabase;Alterdatabaseopenresetlogs;注意，在在Oracle9i中中，用resetlogs选选项打开开数据库库后，备备份就不不再有效效了，需需要重新新备份。。恢复案例例4：恢恢复误删删除的表表数据基本模拟拟过程：：1.先备份数数据2.创建一个个TEST数数据表。createtabletestasselectowner,table_name,column_name,data_typefromall_tab_columns;3.查看一下下时间。SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL;4.删除表中中的数据。Truncatetab