ORACLE进阶与提高专题讲义

1、ORACLE 进阶与提高王忠海10/12/2022主要内容容数据库优优化RMANRAC(如果有有时间的的话）数据库优优化操作系统统设置不不当数据库参参数设置置不当库结构设设计不合合理应用程序序语句不不当可能影响响数据库库性能的的原因有有哪些？AIX5.3中中操作系系统优化化Oracle用用户资源源限制操作系统统核心参参数补丁AIX中中一些基基本的查查看资源源的命令令如何查看看CPU数量如何查看看内存数数量如何查看看交换空空间文件系统统使用情情况AIX基基本命令令：版本本信息#oslevel5.3.0.0#oslevel -r5300-07#oslevel -s5300-07-01-0748AI

2、X基基本命令令：查看看CPU信息#lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processor#lsattr-EHlproc0attributevaluedescriptionuser_settablefrequency2096901000ProcessorSpeedFalsesmt_enabledtrueProcessorSMT enabledFalsesmt_threads2ProcessorS

3、MT threadsFalsestateenableProcessorstateFalsetypePowerPC_POWER5ProcessortypeFalseAIX基基本命令令：查看看内存信信息bootinfor16318464# lsdev -CcmemoryL2cache0AvailableL2 Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936Amount of usablephysicalmemoryinMbytes Falsesize15936Total

4、amount of physical memoryinMbytesFalseAIX基基本命令令：管理理交换空空间查看交换换空间# lsps-aPage SpacePhysicalVolumeVolume GroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv设置交换换空间smit chps交换空间间设置建建议文件系统统的设置置看看下面面的输出出。您认认为最该该调整哪哪个文件件系统大大小？# df -mFilesystemMBblocksFree %UsedIused %IusedMounted on/dev/hd4256.00252

5、.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的优化化技

6、巧.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_TARGE

7、T如何在AIX5.3中中将SGA定在在内存中中？修改系统统参数：v_pinshm修改Oracle参数数：LOCK_SGA参考文档档：如何在Aix5.3中中将OracleSGA定定在内存存中.docAIX中中其他的的需要调调整的Oracle参参数TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj大量导入入数据前前需要做做些什么么？是否需要要备份？估计数据据量考虑设置置为非归归档模式式考虑删除除一些索索引，导导完之后后再创建建大量导入入数据之之后应该该做些什什么？分析表。DBMS_STATS.

8、GATHER_SCHEMA_STATS考虑设置置归档备份哪些因素素最影响响性能？CPU消消耗内存磁盘IO排序提高数据据库性能能的方法法用更好的的硬件！说服用户户将就着着用优化数据据库优化客户户端程序序案例：解解决CPU100%占用步骤一：检查cpu信信息：vmstat步骤二：定位CPU高高消耗进进程psaux|head-1;psaux|sort+2-rn|head-5案例：解解决CPU100%占用（续）步骤三：定位有有问题的的语句SELECT/*+ORDERED*/sql_textFROM v$sqltextaWHERE(a.hash_value,a.address) IN (SELECTDE

9、CODE (sql_hash_value,0,prev_hash_value,sql_hash_value),DECODE(sql_hash_value,0,prev_sql_addr,sql_address)FROM v$sessionbWHEREb.paddr =(SELECTaddrFROM v$processcWHEREc.spid=&pid)ORDERBYpieceASC/案例：解解决CPU100%占用（续）步骤四：定位有有问题的的会话SELECTSID,SERIAL#,USERNAME,TERMINALFROM v$sessionbWHEREb.paddr =(SELECTaddr

10、FROM v$processcWHEREc.spid=&pid)/案例：解解决CPU100%占用（续）步骤五：采取相相关行动动1.杀掉掉会话altersystemkillsession sid,serial#;2.分析析原因优化SQL语句句，最具具挑战的的工作为什么要要优化？OracleSQL调整过过程1.确定定由高影影响力的的SQL2.抽取取和解释释SQL语句3.调整整SQL语句用V$SQLAREA确定影影响力高高的语句句executionsdisk_readsbuffer_getssorts哪些工具具可以来来查看SQL执执行计划划autotracealtersession setsql_t

11、race=true;dbms_system.set_sql_trace_in_sessionexplain planEtc.查看语句句执行计计划的简简单办法法Setautotrace onSetautotrace traceonly前提：存存在plan_table表表。如果果不存在在，可执执行?/rdbms/admin/utlxplan.sql执行语句句相关技巧巧：settiming onsettimeon案例：解解读sql语句句执行计计划SQL selectcount(*)from lpmnt;COUNT(*)-1155ExecutionPlan-Plan hashvalue: 353044

12、5977-| Id| Operation| Name|Rows|Cost (%CPU)|Time|-|0| SELECTSTATEMENT|1|3(0)|00:00:01|1|SORT AGGREGATE|1|2|INDEXFASTFULL SCAN| LPMNT_DBID_IDX |1102|3(0)|00:00:01|-Note- dynamicsamplingusedforthisstatementStatistics509recursivecalls0dbblockgets190consistentgets105physical reads0redo size412bytes sent

13、via SQL*Nettoclient381bytes received viaSQL*Net fromclient2SQL*Net roundtripsto/fromclient6sorts(memory)0sorts(disk)1rows processedsetautotrace的局限限性必须执行行一遍语语句explain plan使使用方法法Explain plansetstatement_id=intotablefor查看explainplan过的的语句的的执行计计划先做些格格式化工工作：setpagesize1000coloperation formata20coloptionsf

14、ormata20colobject_nameformat a20colpositionformat 999执行语句句：selectlpad( ,2*(level-1)|operationoperation,options,object_name,positionfrom plan_tableCONNECT BY PRIOR id =parent_id andstatement_id=statement_id;案例：分分析winsvr执行行的语句句工具：altersystemset sql_trace=true;(如果想想看每个个执行步步骤地时时间信息息，要设设置timed_statistics

15、参参数）查看生成成的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平台

16、，则运行行：?rdbmsadminspcreate.sql使用Statspack准备备工作规划自动动STATSPACK数据搜搜集。运运行?rdbmsadminspauto.sql可以以设置自自动搜集集statspack数据。这个脚脚本创建建了一个个作业。为了运运行这个个作业，要保证证job_queue_processes参数数大于0，而且且要使用用statspack所属用用户来执执行。例例如下面面的脚本本设置每每1小时时进行一一次statspack：variablejobnonumber;variableinstnonumber;beginselectinstance_number into:

17、instnofrom v$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）

18、通过statspack.modify_statspack_parameter函函数可以以改变阈阈值的默默认值。阈值存存放在stats$statspack_parameter中。改变阈值值举例：sqlexecstatspack.modify_statspack_parameter(i_buffer_gets_th=20000);用statspack搜集信信息exec statspack.snap;等待一会会或者者执行一一些语句句exec statspack.snap;生成statspack报告告?/rdbms/admin/spreport解读statspack报告告移除自动动执行STATSPAC

19、K收收集-先查看当当前自动动收集的的jobsSQLselect job,log_user,priv_user,last_date,next_date,intervalfrom user_jobs;-移除除任务1SQL executedbms_job.remove(1);删除统计计资料SQLselect max(snap_id)from stats$snapshot;SQL C:oracleora92rdbmsadminsptrunc.sql;发现占资资源高的的语句后后怎么办办？改变语句句写法（最好）适当增加加索引改变操作作系统参参数分析表使用大纲纲优化案例例：通过过改变语语句写法法来提高高性能

20、举一个日日期查询询的例子子优化案例例：通过过增加索索引来提提高性能能优化案例例：通过过分析表表来提高高性能RMANRMAN备份的的优点：RMAN会检测测和报告告损坏的的数据块块不需要将将表空间间置于热热备模式式，RMAN就就可以备备份数据据库。因因此热备备期间生生成的重重做会减减少RMAN自动跟跟踪更新新新的数数据文件件和表空空间，这这样就不不再需要要在脚本本中添加加新的表表空间和和数据文文件RMAN只备份份使用过过的数据据块，这这样RMAN备备份通常常小于联联机脚本本备份RMAN可以与与第三方方介质管管理产品品一起无无缝地工工作RMAN支持增增量备份份可以测试试备份而而不需要要实际还还原。如

21、何配置置RMAN将数据库库配置为为ARCHIVELOG模式式 创建恢复复目录第一步，在目录录数据库库中创建建恢复目目录所用用表空间间：SQL createtablespace rman_tsdatafiled:oracleoradatarmanrman_ts.dbf size20M;第二步，在目录录数据库库中创建建RMAN用用户并授授权：SQL createuser rmanidentifiedbyrman defaulttablespacerman_ts temporarytablespace tempquotaunlimited on rman_ts;SQL grant recovery_

22、catalog_owner, connect, resource to rman;第三步，在目录录数据库库中创建建恢复目目录C:rmancatalog rman/rman恢复管理理器：版版本8.1.6.0.0- ProductionRMAN-06008：连接接到恢复复目录数数据库RMAN-06428：未安安装恢复复目录RMANcreatecatalog tablespacerman_ts;RMAN-06431：恢复复目录已已创建注册数据据库CONNECT TARGETSYS/PWDTESTDB;REGISTERDATABASE配置RMAN的的默认设设置CONFIGURE：用于RMAN操操作的默

23、默认设备备类型，如磁盘盘或者磁磁带(SBT)执行自动动备份和和恢复操操作时自自动分配配的通道道数配置备份份时的排排斥策略略配置自动动备份时时给定备备份片的的最大空空间和任任何备份份集的大大小配置备份份最优化化的默认认设置。可以是是ON或或者OFF.快照控制制文件的的默认名名称是否自动动备份控控制文件件辅助数据据文件的的默认名名称默认的保保存策略略显示默认认设置举举例：SHOW DEFAULTDEVICE TYPE;SHOW MAXSETSIZE;SHOW RETENTIONPOLICY;SHOW ALL;（显示全全部配置置）CONFGIURE命命令举例例下面语句句设置默默认磁盘盘类型和和并发度

24、度CONFIGUREDEVICE TYPEDISKPARALLELISM2;下面设置置默认备备份文件件大小。CONFIGURECHANNELDEVICETYPEDISK MAXPIECESIZE500M;下面语句句配置默默认存储储位置等等信息：CONFIGURECHANNELDEVICETYPEDISK FORMATD:oraclebackupGAXZRMAN%d_%s_%p_%c;下面语句句设置CHANNEL 1和和CHANNEL2,用于于RAC数据库库CONFIGURECHANNEL1 DEVICETYPE diskCONNECTsys/oracleora921 MAXPIECESIZE1

25、0g;CONFIGURECHANNEL2 DEVICETYPE diskCONNECTsys/oracleora922 MAXPIECESIZE10g;FORMAT格格式%c备备份片的的拷贝数数%d数数据库名名称%D位位于该该月中的的第几天天 (DD)%M 位于于该年中中的第几几月(MM)%F一一个基于于DBID唯一一的名称称%n 数据据库名称称，向右右填补到到最大八八个字符符%u一一个八个个字符的的名称代代表备份份集与创创建时间间%p该该备份集集中的备备份片号号，从1开始到到创建的的文件数数%U 一个个唯一的的文件名名，代表表%u_%p_%c%s 备份份集的号号%t备备份集时时间戳备份整个个

26、数据库库backupdatabaseformat D:oraclebackupGAXZRMANora_d%d_s%s_s%p_f%tfilesperset4 plusarchivelogdeleteinput;增量备份份差异(Differential)备份是是默认的的增量备备份类型型，差异异备份会会备份上上一次进进行的同级或者者低级备份以来来所有变变化的数数据块，而累积积(cumulative)备份，则备份份上次低级级备份以来所有有的块。例如，星期一一进行了了一次2级备份份，星期期二进行行了一次次3级备备份，如如果星期期四进行行3级差差异增量量备份，那么只只备份上上次3级级备份以以来变化化过的

27、数数据块；如果进进行累积积3级备备份，那那么就会会备份上上次2级级备份以以来变化化的数据据块。案例：增增量备份份首先要做做一个0级备份份backupINCREMENTALLEVEL0databaseplus archivelogdelete input;做一个1级备份份：backupINCREMENTALLEVEL1databaseplus archivelogdelete input;做一个1级累计计备份：backupINCREMENTALLEVEL1CUMULATIVE DATABASE database plusarchivelogdeleteinput;备份特定定内容备份表空空间备份数

28、据据文件backupdatafile7format/backup/rman/ora_d%d_s%s_s%p_f%t;备份归档档日志backuparchivelogalldelete input;RMAN报告LISTRMAN的list命命令是一一种在数数据库控控制文件件或者恢恢复目录录中查询询备份的的历史信信息的方方法。列列表提供供了一组组信息，可以提提供各种种备份的的信息，如对应应物、备备份集、归档日日志备份份、控制制文件备备份等等等。REPORTRMAN的report命令令被用于于判断数数据库的的当前可可恢复状状态和提提供数据据库备份份的特定定信息、报告最最近没有有备份的的数据文文件等信信息

29、。常用LIST命命令列出所有有备份：List backupset;列出所有有备份简简要信息息：List backupsetsummary;列出指定定备份集集备份信信息List backupsetbs#;列出过期期的备份份：list expiredbackup;列出指定定表空间间的备份份信息：List backupoftablespace users;列出所有有已备份份的归档档：list backupofarchivelog allsummary列出所有有需要备备份的归归档：List archivelogall;常用REPORT命令令报告最近近10天天没有备备份的数数据文件件reportneed

30、backupdays=10;报告按照照默认策策略需要要备份的的文件Reportneedbackup;按照指定定策略报报告需要要备份的的文件reportneedbackupredundancy=2;报告数据据库信息息：Reportschema;报告过期期的备份份Reportobsolete;备份集的的维护删除过期期备份：deleteobsolete;用delete noprompt obsolete可无需需提示。删除指定定的文件件集：deletebackupset bs#;删除所有有备份：deletebackup;验证备份份集，如如果备份份集不复复存在，将被标标记为expired：crossch

31、eck backup;删除expired备备份集：Deleteexpiredbackup;恢复数据据库恢复案例例1：丢丢失SYSTEM表空空间restore datafile 1;recover database;alterdatabaseopen;恢复案例例2：丢丢失参数数文件1.编辑辑一个init.ora，内内容包括括：db_name=GAXZinstance_name=GAXZcontrol_files=D:oracleproduct10.2.0oradataGAXZCONTROL01.CTLdb_block_size=8192shared_pool_size=1048576002.RM

32、AN连连接到目目标数据据库。3.startup nomountpfile=D:RMANINIT.ORA;4.restorespfile;5.Shutdown immediate;6.startup恢复案例例3：恢恢复控制制文件Startup nomount;Restore controlfile;Recover database;Alterdatabaseopen resetlogs;注意，在在Oracle9i中中，用resetlogs选选项打开开数据库库后，备备份就不不再有效效了，需需要重新新备份。恢复案例例4：恢恢复误删删除的表表数据基本模拟拟过程：1.先备备份数据据2.创建建一个TEST数据表表。createtabletestasselectowner,table_name,column_name,data_typefromall_tab_columns;3.查看看一下时时间。SELECTTO_CHAR(SYSDATE,YYYYMMDDHH24MISS)FROMDUAL;4.删除除表中的的数据。Truncatetabletest;5.恢复复文件。Restore database;6.recover到到指定的的时间点点recover database until timeto_da