Oracle进阶与提高.ppt

ORACLE进阶与提高 20 03 2020 主要内容 数据库优化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 r16318464 lsdev CcmemoryL2cache0AvailableL2Cachemem0AvailableMemorylsattr EHlmem0attributevaluedescriptionuser settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalse AIX基本命令 管理交换空间 查看交换空间 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的优化技巧 mht oracle用户的资源限制 修改 etc security limits看一个实例 oracle fsize 209715100data 20971510stack 20971510core 20971510rss 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 1155ExecutionPlan Planhashvalue 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 intotable for 查看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 session dbms 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 发现占资源高的语句后怎么办 改变语句写法 最好 适当增加索引改变操作系统参数分析表使用大纲 优化案例 通过改变语句写法来提高性能 举一个日期查询的例子 优化案例 通过增加索引来提高性能 优化案例 通过分析表来提高性能 RMAN RMAN备份的优点 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 删除表中的数据 Truncatetabletest 5 恢复文件 Restoredatabase 6 recover到指定的时间点recoverdatabaseunti