Oracle进阶与提高

ORACLE进阶与提高王忠海2019/8/1主要内容•数据库优化•RMAN•RAC(如果有时间的话）数据库优化•操作系统设置不当•数据库参数设置不当•库结构设计不合理•应用程序语句不当可能影响数据库性能的原因有哪些？AIX5.3中操作系统优化•Oracle用户资源限制•操作系统核心参数•补丁AIX中一些基本的查看资源的命令•如何查看CPU数量•如何查看内存数量•如何查看交换空间•文件系统使用情况AIX基本命令：版本信息•#oslevel5.3.0.0•#oslevel-r5300-07•#oslevel-s5300-07-01-0748AIX基本命令：查看CPU信息•#lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processor•#lsattr-EHlproc0attributevaluedescriptionuser_settablefrequency2096901000ProcessorSpeedFalsesmt_enabledtrueProcessorSMTenabledFalsesmt_threads2ProcessorSMTthreadsFalsestateenableProcessorstateFalsetypePowerPC_POWER5ProcessortypeFalseAIX基本命令：查看内存信息•bootinfo–r16318464•#lsdev-CcmemoryL2cache0AvailableL2Cachemem0AvailableMemory•lsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalseAIX基本命令：管理交换空间•查看交换空间•#lsps-aPageSpacePhysicalVolumeVolumeGroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv•设置交换空间•smitchps•交换空间设置建议文件系统的设置•看看下面的输出。您认为最该调整哪个文件系统大小？•#df-m•FilesystemMBblocksFree%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_repage•minperm%、maxperm%和maxclient%等等。•参考文档：在AIX中运行Oracle的优化技巧.mhtoracle用户的资源限制•修改/etc/security/limits看一个实例：•oracle:fsize=209715100data=20971510stack=20971510core=20971510rss=25165824AIX中Oracle参数设置•SGA设置建议•SGA不要超过总内存数*maxperm%回顾：重要的SGA内存参数•Shared_pool_size•Db_cache_size•Db_keep_cache_size•Large_pool_size•Log_buffer回顾：重要的PGA内存参数•PGA_AGGREGATE_TARGET如何在AIX5.3中将SGA定在内存中？•修改系统参数：v_pinshm•修改Oracle参数：LOCK_SGA•参考文档：如何在Aix5.3中将OracleSGA定在内存中.docAIX中其他的需要调整的Oracle参数•TIMED_STATISTICS•DB_CACHE_ADVICE•optimizer_index_caching•optimizer_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.确定由高影响力的SQL•2.抽取和解释SQL语句•3.调整SQL语句用V$SQLAREA确定影响力高的语句•executions•disk_reads•buffer_gets•sorts哪些工具可以来查看SQL执行计划•autotrace•altersessionsetsql_trace=true;•dbms_system.set_sql_trace_in_session•explainplan•Etc.查看语句执行计划的简单办法•Setautotraceon•Setautotracetraceonly•前提：存在plan_table表。如果不存在，可执行@?/rdbms/admin/utlxplan.sql•执行语句•相关技巧：settimingonsettimeon案例：解读sql语句执行计划•SQLselectcount(*)fromlpmnt;•COUNT(*)•----------•1155•ExecutionPlan•----------------------------------------------------------•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•-dynamicsamplingusedforthisstatement•Statistics•509recursivecalls•0dbblockgets•190consistentgets•105physicalreads•0redosize•412bytessentviaSQL*Nettoclient•381bytesreceivedviaSQL*Netfromclient•2SQL*Netroundtripsto/fromclient•6sorts(memory)•0sorts(disk)•1rowsprocessedsetautotrace的局限性•必须执行一遍语句explainplan使用方法Explainplan{setstatement_id=’yourID’}{intotabletablename}forSQLstatement查看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;•begin•selectinstance_numberinto:instnofromv$instance;•dbms