ORACLE进阶与提高专题讲义

ORACLE进阶与提高王忠海5/10/2023第一页，共八十二页。主要内容数据库优化RMANRAC(如果有时间的话）第二页，共八十二页。数据库优化操作系统设置不当数据库参数设置不当库结构设计不合理应用程序语句不当可能影响数据库性能的原因有哪些？第三页，共八十二页。AIX5.3中操作系统优化Oracle用户资源限制操作系统核心参数补丁第四页，共八十二页。AIX中一些基本的查看资源的命令如何查看CPU数量如何查看内存数量如何查看交换空间文件系统使用情况第五页，共八十二页。AIX基本命令：版本信息#oslevel5.3.0.0#oslevel-r5300-07#oslevel-s5300-07-01-0748第六页，共八十二页。AIX基本命令：查看CPU信息#lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processor#lsattr-EHlproc0attributevaluedescriptionuser_settablefrequency2096901000ProcessorSpeedFalsesmt_enabledtrueProcessorSMTenabledFalsesmt_threads2ProcessorSMTthreadsFalsestateenableProcessorstateFalsetypePowerPC_POWER5ProcessortypeFalse第七页，共八十二页。AIX基本命令：查看内存信息bootinfo–r#lsdev-CcmemoryL2cache0AvailableL2Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalse第八页，共八十二页。AIX基本命令：管理交换空间查看交换空间#lsps-aPageSpacePhysicalVolumeVolumeGroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv设置交换空间smitchps交换空间设置建议第九页，共八十二页。文件系统的设置看看下面的输出。您认为最该调整哪个文件系统大小？#df-mMBblocksFree%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_minperm%、maxperm%和maxclient%等等。参考文档：在AIX中运行Oracle的优化技巧.mht第十一页，共八十二页。oracle用户的资源限制修改/etc/security/limits看一个实例：oracle:

fsize=209715100data=20971510stack=20971510core=20971510

rss=25165824第十二页，共八十二页。AIX中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定在内存中.doc第十六页，共八十二页。AIX中其他的需要调整的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)1rowsprocessed第三十一页，共八十二页。setautotrace的局限性必须执行一遍语句第三十二页，共八十二页。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'4plusarchivelogdeleteinput;第五十八页，共八十二页。增量备份

差异(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.删除表中的数据。Truncatetabletest;5.恢复文件。Restoredatabase;6.recover到指定的时间点recoverdatabaseuntiltime"to_date('240','yyyymmddhh24miss')"或者recover到指定的scn:recoverdatabaseuntilscnscn#;7.alterdatabaseopenresetlogs;8.验证：SQL>selectcount(*)fromtest;COUNT(*)26788第七十页，共八十二页。如果不完全恢复没有做好怎么办？比如说，恢复到的位置早于或者晚于希望恢复的时间点？直接重新恢复是否生效？Let’stry!结论：这样做不可以，那该怎么办？第七十一页，共八十