ORACLE進(jìn)階與提高專題講義

ORACLE進(jìn)階與提高王忠海1/11/2023主要內(nèi)容數(shù)據(jù)庫優(yōu)化RMANRAC(如果有時(shí)間的話）數(shù)據(jù)庫優(yōu)化操作系統(tǒng)設(shè)置不當(dāng)數(shù)據(jù)庫參數(shù)設(shè)置不當(dāng)庫結(jié)構(gòu)設(shè)計(jì)不合理應(yīng)用程序語句不當(dāng)可能影響數(shù)據(jù)庫性能的原因有哪些？AIX5.3中操作系統(tǒng)優(yōu)化Oracle用戶資源限制操作系統(tǒng)核心參數(shù)補(bǔ)丁AIX中一些基本的查看資源的命令如何查看CPU數(shù)量如何查看內(nèi)存數(shù)量如何查看交換空間文件系統(tǒng)使用情況AIX基本命令：版本信息#oslevel#oslevel-r5300-07#oslevel-s5300-07-01-0748AIX基本命令：查看CPU信息#lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processor#lsattr-EHlproc0attributevaluedescriptionuser_settablefrequency2096901000ProcessorSpeedFalsesmt_enabledtrueProcessorSMTenabledFalsesmt_threads2ProcessorSMTthreadsFalsestateenableProcessorstateFalsetypePowerPC_POWER5ProcessortypeFalseAIX基本命令：查看內(nèi)存信息bootinfo–r16318464#lsdev-CcmemoryL2cache0AvailableL2Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalseAIX基本命令：管理交換空間查看交換空間#lsps-aPageSpacePhysicalVolumeVolumeGroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv設(shè)置交換空間smitchps交換空間設(shè)置建議文件系統(tǒng)的設(shè)置看看下面的輸出。您認(rèn)為最該調(diào)整哪個(gè)文件系統(tǒng)大?。?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文件系統(tǒng)！如果不調(diào)整，安裝數(shù)據(jù)庫時(shí)將無法成功。AIX5.3核核心參數(shù)數(shù)調(diào)整適用范圍圍在AIX5.3-01以上上需要調(diào)整整的參數(shù)數(shù)：lru_file_repageminperm%、maxperm%和和maxclient%等等等。。參考文檔檔：在AIX中中運(yùn)行Oracle的優(yōu)化化技巧.mhtoracle用用戶的資資源限制制修改/etc/security/limits看一個(gè)實(shí)實(shí)例：oracle:fsize=209715100data=20971510stack=20971510core=20971510rss=25165824AIX中中Oracle參數(shù)設(shè)設(shè)置SGA設(shè)設(shè)置建議議SGA不不要超過過總內(nèi)存存數(shù)*maxperm%回顧：重重要的SGA內(nèi)內(nèi)存參數(shù)數(shù)Shared_pool_sizeDb_cache_sizeDb_keep_cache_sizeLarge_pool_sizeLog_buffer回顧：重重要的PGA內(nèi)內(nèi)存參數(shù)數(shù)PGA_AGGREGATE_TARGET如何在AIX5.3中中將SGA定在在內(nèi)存中中？修改系統(tǒng)統(tǒng)參數(shù)：：v_pinshm修改Oracle參數(shù)數(shù)：LOCK_SGA參考文檔檔：如何在Aix5.3中中將OracleSGA定定在內(nèi)存存中.docAIX中中其他的的需要調(diào)調(diào)整的Oracle參參數(shù)TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj大量導(dǎo)入入數(shù)據(jù)前前需要做做些什么么？是否需要要備份？？估計(jì)數(shù)據(jù)據(jù)量考慮設(shè)置置為非歸歸檔模式式考慮刪除除一些索索引，導(dǎo)導(dǎo)完之后后再創(chuàng)建建大量導(dǎo)入入數(shù)據(jù)之之后應(yīng)該該做些什什么？分析表。。DBMS_STATS.GATHER_SCHEMA_STATS考慮設(shè)置置歸檔備份哪些因素素最影響響性能？？CPU消消耗內(nèi)存磁盤IO排序提高數(shù)據(jù)據(jù)庫性能能的方法法用更好的的硬件?。≌f服用戶戶將就著著用優(yōu)化數(shù)據(jù)據(jù)庫優(yōu)化客戶戶端程序序案例：解解決CPU100%占用步驟一：：檢查cpu信信息：vmstat步驟二：：定位CPU高高消耗進(jìn)進(jìn)程psaux|head-1;psaux|sort+2-rn|head-5案例：解解決CPU100%占用（（續(xù)）步驟三：：定位有有問題的的語句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%占用（（續(xù)）步驟四：：定位有有問題的的會話SELECTSID,SERIAL#,USERNAME,TERMINALFROMv$sessionbWHEREb.paddr=(SELECTaddrFROMv$processcWHEREc.spid='&pid')/案例：解解決CPU100%占用（（續(xù)）步驟五：：采取相相關(guān)行動動1.殺掉掉會話altersystemkillsession‘sid,serial#’;2.分析析原因優(yōu)化SQL語句句，最具具挑戰(zhàn)的的工作為什么要要優(yōu)化？？OracleSQL調(diào)整過過程1.確定定由高影影響力的的SQL2.抽抽取和和解釋釋SQL語語句3.調(diào)調(diào)整SQL語句句用V$SQLAREA確確定影影響力力高的的語句句executionsdisk_readsbuffer_getssorts哪些工工具可可以來來查看看SQL執(zhí)執(zhí)行計(jì)計(jì)劃autotracealtersessionsetsql_trace=true;dbms_system.set_sql_trace_in_sessionexplainplanEtc.查看語語句執(zhí)執(zhí)行計(jì)計(jì)劃的的簡單單辦法法SetautotraceonSetautotracetraceonly前提：：存在在plan_table表。。如果果不存存在，，可執(zhí)執(zhí)行@?/rdbms/admin/utlxplan.sql執(zhí)行語語句相關(guān)技技巧：：settimingonsettimeon案例：：解讀讀sql語語句執(zhí)執(zhí)行計(jì)計(jì)劃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的局限性性必須執(zhí)行一一遍語句explainplan使使用方法Explainplan{setstatement_id=’<yourID>’}{intotable<tablename>}for<SQLstatement>查看explainplan過的語語句的執(zhí)行行計(jì)劃先做些格式式化工作：：setpagesize1000coloperationformata20coloptionsformata20colobject_nameformata20colpositionformat999執(zhí)行語句：：selectlpad('',2*(level-1))||operationoperation,options,object_name,positionfromplan_tableCONNECTBYPRIORid=parent_idandstatement_id='statement_id';案例：分析析winsvr執(zhí)行行的語句工具：altersystemsetsql_trace=true;(如果想看看每個(gè)執(zhí)行行步驟地時(shí)時(shí)間信息，，要設(shè)置timed_statistics參參數(shù)）查看生成的的trace文件在user_dump_dest環(huán)境境變量所對對目錄下。。ls-ln用tkprof命令令來格式化化輸出解讀輸出。。set_sql_trace_in_sessiondbms_system.set_sql_trace_in_session優(yōu)化數(shù)據(jù)庫庫的工具：：statspack通過statspack可以以很容易做做出Oracle系系統(tǒng)性能的的全面報(bào)告告，是OracleDBA管理Oracle9i必必須掌握的的性能調(diào)優(yōu)優(yōu)工具。安裝statspack創(chuàng)建一個(gè)statspack專用表空空間運(yùn)行?/rdbms/admin/spcreate.sql如果是windows平臺，，則運(yùn)行：：?\rdbms\admin\spcreate.sql使用Statspack準(zhǔn)備備工作規(guī)劃自動STATSPACK數(shù)據(jù)搜集集。運(yùn)行?\rdbms\admin\spauto.sql可可以設(shè)置自自動搜集statspack數(shù)據(jù)。這這個(gè)腳本創(chuàng)創(chuàng)建了一個(gè)個(gè)作業(yè)。為為了運(yùn)行這這個(gè)作業(yè)，，要保證job_queue_processes參數(shù)數(shù)大于0，，而且要使使用statspack所屬屬用戶來執(zhí)執(zhí)行。例如如下面的腳腳本設(shè)置每每1小時(shí)進(jìn)進(jìn)行一次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;/調(diào)整statspack參數(shù)數(shù)executions_th：SQL語句執(zhí)行行的數(shù)量（（默認(rèn)100）disk_reads_th：sql語句執(zhí)行行的磁盤讀讀入數(shù)量（（默認(rèn)1000）parse_calls_th：sql語句執(zhí)執(zhí)行的解析析調(diào)用數(shù)量量（默認(rèn)1000））buffer_gets_th：sql語句執(zhí)執(zhí)行緩沖區(qū)區(qū)獲取的數(shù)數(shù)量（默認(rèn)認(rèn)10000）通過statspack..modify_statspack_parameter函數(shù)數(shù)可以改變變閾值的默默認(rèn)值。閾閾值存放在在stats$statspack_parameter中。改變閾值舉舉例：sql>execstatspack.modify_statspack_parameter(i_buffer_gets_th=>20000);用statspack搜集信信息execstatspack.snap;等待一會……或者執(zhí)行行一些語句句execstatspack.snap;生成statspack報(bào)告告@?/rdbms/admin/spreport解讀statspack報(bào)告告移除自動執(zhí)執(zhí)行STATSPACK收集集--先查看當(dāng)前前自動收集集的jobsSQL>selectjob,log_user,priv_user,last_date,next_date,intervalfromuser_jobs;--移除任任務(wù)1SQL>executedbms_job.remove('1');刪除統(tǒng)計(jì)資資料SQL>selectmax(snap_id)fromstats$snapshot;SQL>@C:\oracle\ora92\rdbms\admin\sptrunc.sql;發(fā)現(xiàn)占資源源高的語句句后怎么辦辦？改變語句寫寫法（最好好）適當(dāng)增加索索引改變操作系系統(tǒng)參數(shù)分析表使用大綱優(yōu)化案例：：通過改變變語句寫法法來提高性性能舉一個(gè)日期期查詢的例例子優(yōu)化案例：：通過增加加索引來提提高性能優(yōu)化案例：：通過分析析表來提高高性能RMANRMAN備備份的優(yōu)點(diǎn)點(diǎn)：RMAN會會檢測和報(bào)報(bào)告損壞的的數(shù)據(jù)塊不需要將表表空間置于于熱備模式式，RMAN就可以以備份數(shù)據(jù)據(jù)庫。因此此熱備期間間生成的重重做會減少少RMAN自自動跟蹤更更新新的數(shù)數(shù)據(jù)文件和和表空間，，這樣就不不再需要在在腳本中添添加新的表表空間和數(shù)數(shù)據(jù)文件RMAN只備備份使用過的的數(shù)據(jù)塊，這這樣RMAN備份通常小小于聯(lián)機(jī)腳本本備份RMAN可以以與第三方介介質(zhì)管理產(chǎn)品品一起無縫地地工作RMAN支持持增量備份可以測試備份份而不需要實(shí)實(shí)際還原。如何配置RMAN將數(shù)據(jù)庫配置置為ARCHIVELOG模式創(chuàng)建恢復(fù)目錄錄第一步，在目目錄數(shù)據(jù)庫中中創(chuàng)建恢復(fù)目目錄所用表空空間：SQL>createtablespacerman_tsdatafile"d:\oracle\oradata\rman\rman_ts.dbf"size20M;第二步，在目目錄數(shù)據(jù)庫中中創(chuàng)建RMAN用戶并并授權(quán)：SQL>createuserrmanidentifiedbyrmandefaulttablespacerman_tstemporarytablespacetempquotaunlimitedonrman_ts;SQL>grantrecovery_catalog_owner,connect,resourcetorman;第三步，在目目錄數(shù)據(jù)庫中中創(chuàng)建恢復(fù)目目錄C:\>rmancatalogrman/rman恢復(fù)管理器：：版本8.1.6.0.0-ProductionRMAN-06008：：連接到恢復(fù)復(fù)目錄數(shù)據(jù)庫庫RMAN-06428：：未安裝恢復(fù)復(fù)目錄RMAN>createcatalogtablespacerman_ts;RMAN-06431：：恢復(fù)目錄已已創(chuàng)建注冊數(shù)據(jù)庫CONNECTTARGETSYS/PWD@TESTDB;REGISTERDATABASE配置RMAN的默認(rèn)設(shè)置置CONFIGURE：用于RMAN操作的默認(rèn)認(rèn)設(shè)備類型，，如磁盤或者者磁帶(SBT)執(zhí)行自動備份份和恢復(fù)操作作時(shí)自動分配配的通道數(shù)配置備份時(shí)的的排斥策略配置自動備份份時(shí)給定備份份片的最大空空間和任何備備份集的大小小配置備份最優(yōu)優(yōu)化的默認(rèn)設(shè)設(shè)置?？梢允鞘荗N或者OFF.快照控制文件件的默認(rèn)名稱稱是否自動備份份控制文件輔助數(shù)據(jù)文件件的默認(rèn)名稱稱默認(rèn)的保存策策略顯示默認(rèn)設(shè)置置舉例：SHOWDEFAULTDEVICETYPE;SHOWMAXSETSIZE;SHOWRETENTIONPOLICY;SHOWALL;（顯顯示全部配置置）CONFGIURE命令令舉例下面語句設(shè)置置默認(rèn)磁盤類類型和并發(fā)度度CONFIGUREDEVICETYPEDISKPARALLELISM2;下面設(shè)置默認(rèn)認(rèn)備份文件大大小。CONFIGURECHANNELDEVICETYPEDISKMAXPIECESIZE500M;下面語句配置置默認(rèn)存儲位位置等信息：：CONFIGURECHANNELDEVICETYPEDISKFORMAT'D:\oracle\backup\GAXZ\RMAN\%d_%s_%p_%c';下面面語語句句設(shè)設(shè)置置CHANNEL1和和CHANNEL2,用用于于RAC數(shù)數(shù)據(jù)據(jù)庫庫CONFIGURECHANNEL1DEVICETYPEdiskCONNECT'sys/oracle@ora921'MAXPIECESIZE10g;CONFIGURECHANNEL2DEVICETYPEdiskCONNECT'sys/oracle@ora922'MAXPIECESIZE10g;FORMAT格格式式%c備備份份片片的的拷拷貝貝數(shù)數(shù)%d數(shù)數(shù)據(jù)據(jù)庫庫名名稱稱%D位位于于該該月月中中的的第第幾幾天天(DD)%M位位于于該該年年中中的的第第幾幾月月(MM)%F一一個(gè)個(gè)基基于于DBID唯唯一一的的名名稱稱%n數(shù)數(shù)據(jù)據(jù)庫庫名名稱稱，，向向右右填填補(bǔ)補(bǔ)到到最最大大八八個(gè)個(gè)字字符符%u一一個(gè)個(gè)八八個(gè)個(gè)字字符符的的名名稱稱代代表表備備份份集集與與創(chuàng)創(chuàng)建建時(shí)時(shí)間間%p該該備備份份集集中中的的備備份份片片號號，，從從1開開始始到到創(chuàng)創(chuàng)建建的的文文件件數(shù)數(shù)%U一一個(gè)個(gè)唯唯一一的的文文件件名名，，代代表表%u_%p_%c%s備備份份集集的的號號%t備備份份集集時(shí)時(shí)間間戳戳備份份整整個(gè)個(gè)數(shù)數(shù)據(jù)據(jù)庫庫backupdatabaseformat'D:\oracle\backup\GAXZ\RMAN\ora_d%d_s%s_s%p_f%t'filesperset4plusarchivelogdeleteinput;增量備份份差異(Differential)備份是是默認(rèn)的的增量備備份類型型，差異異備份會會備份上上一次進(jìn)進(jìn)行的同級或者者低級備份以來來所有變變化的數(shù)數(shù)據(jù)塊，，而累積積(cumulative)備份，，則備份份上次低級級備份以來所有有的塊。。例如，，星期一一進(jìn)行了了一次2級備份份，星期期二進(jìn)行行了一次次3級備備份，如如果星期期四進(jìn)行行3級差差異增量量備份，，那么只只備份上上次3級級備份以以來變化化過的數(shù)數(shù)據(jù)塊；；如果進(jìn)進(jìn)行累積積3級備備份，那那么就會會備份上上次2級級備份以以來變化化的數(shù)據(jù)據(jù)塊。案例：增增量備份份首先要做做一個(gè)0級備份份backupINCREMENTALLEVEL0databaseplusarchivelogdeleteinput;做一個(gè)1級備份份：backupINCREMENTALLEVEL1databaseplusarchivelogdeleteinput;做一個(gè)1級累計(jì)計(jì)備份：：backupINCREMENTALLEVEL1CUMULATIVEDATABASEdatabaseplusarchivelogdeleteinput;備份特特定內(nèi)內(nèi)容備份表表空間間備份數(shù)數(shù)據(jù)文文件backupdatafile7format'/backup/rman/ora_d%d_s%s_s%p_f%t';備份歸歸檔日日志backuparchivelogalldeleteinput;RMAN報(bào)報(bào)告LISTRMAN的的list命令是一一種在數(shù)據(jù)據(jù)庫控制文文件或者恢恢復(fù)目錄中中查詢備份份的歷史信信息的方法法。列表提提供了一組組信息，可可以提供各各種備份的的信息，如如對應(yīng)物、、備份集、、歸檔日志志備份、控控制文件備備份等等。。REPORTRMAN的的report命令令被用于判判斷數(shù)據(jù)庫庫的當(dāng)前可可恢復(fù)狀態(tài)態(tài)和提供數(shù)數(shù)據(jù)庫備份份的特定信信息、報(bào)告告最近沒有有備份的數(shù)數(shù)據(jù)文件等等信息。常用LIST命令列出所有備備份：Listbackupset;列出所有備備份簡要信信息：Listbackupsetsummary;列出指定備備份集備份份信息Listbackupsetbs#;列出過期期的備份份：listexpiredbackup;列出指定定表空間間的備份份信息：：Listbackupoftablespaceusers;列出所有有已備份份的歸檔檔：listbackupofarchivelogallsummary列出所有有需要備備份的歸歸檔：Listarchivelogall;常用REPORT命令令報(bào)告最近近10天天沒有備備份的數(shù)數(shù)據(jù)文件件reportneedbackupdays=10;報(bào)告按照照默認(rèn)策策略需要要備份的的文件Reportneedbackup;按照指定定策略報(bào)報(bào)告需要要備份的的文件reportneedbackupredundancy=2;報(bào)告數(shù)據(jù)據(jù)庫信息息：Reportschema;報(bào)告過期期的備份份Reportobsolete;備份集的的維護(hù)刪除過期期備份：：deleteobsolete;用deletenopromptobsolete可無需需提示。。刪除指定定的文件件集：deletebackupsetbs#;刪除所有有備份：：deletebackup;驗(yàn)證備份份集，如如果備份份集不復(fù)復(fù)存在，，將被標(biāo)標(biāo)記為expired：crosscheckbackup;刪除expired備備份集：：Deleteexpiredbackup;恢復(fù)數(shù)據(jù)據(jù)庫恢復(fù)案例例1：丟丟失SYSTEM表空空間restoredatafile1;recoverdatabase;alterdatabaseopen;恢復(fù)案例例2：丟丟失參數(shù)數(shù)文件1.編輯輯一個(gè)init.ora，內(nèi)內(nèi)容包括括：db_name=GAXZinstance_name=GAXZcontrol_files='D:\oracle\product\10.2.0\oradata\GAXZ\CONTROL01.CTL'db_block_size=8192shared_pool_size=1048576002.RMAN連連接到目目標(biāo)數(shù)據(jù)據(jù)庫。3.startupnomountpfile='D:\RMAN\INIT.ORA';4.restorespfile;5.Shutdownimmediate;6.startup恢復(fù)案例例3：恢恢復(fù)控制制文件Startupnomount;Restorecontrolfile;Recoverdatabase;Alterdatabaseopenresetlogs;注意，在在Oracle9i中中，用resetlogs選選項(xiàng)打開開數(shù)據(jù)庫庫后，備備份就不不再有效效了，需需要重新新備份。?；謴?fù)案例例4：恢恢復(fù)誤刪刪除的表表數(shù)據(jù)基本模擬擬過程：：1.先備份數(shù)數(shù)據(jù)2.創(chuàng)建一個(gè)個(gè)TEST數(shù)數(shù)據(jù)表。createtabletestasselectowner,table_name,column_name,data_typefromall_tab_columns;3.查看一下下時(shí)間。SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL;4.刪除表中中的數(shù)據(jù)。Truncatetab