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

ORACLE進(jìn)階與提高王忠海5/10/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基本命令：版本信息#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基本命令：查看內(nèi)存信息bootinfo–r#lsdev-CcmemoryL2cache0AvailableL2Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936AmountofusablephysicalmemoryinMbytesFalsesize15936TotalamountofphysicalmemoryinMbytesFalse第八頁，共八十二頁。AIX基本命令：管理交換空間查看交換空間#lsps-aPageSpacePhysicalVolumeVolumeGroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv設(shè)置交換空間smitchps交換空間設(shè)置建議第九頁，共八十二頁。文件系統(tǒng)的設(shè)置看看下面的輸出。您認(rèn)為最該調(diào)整哪個(gè)文件系統(tǒng)大?。?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文件系統(tǒng)！如果不調(diào)整，安裝數(shù)據(jù)庫時(shí)將無法成功。第十頁，共八十二頁。AIX5.3核心參數(shù)調(diào)整適用范圍在AIX5.3-01以上需要調(diào)整的參數(shù)：lru_minperm%、maxperm%和maxclient%等等。參考文檔：在AIX中運(yùn)行Oracle的優(yōu)化技巧.mht第十一頁，共八十二頁。oracle用戶的資源限制修改/etc/security/limits看一個(gè)實(shí)例：oracle:

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

rss=25165824第十二頁，共八十二頁。AIX中Oracle參數(shù)設(shè)置SGA設(shè)置建議SGA不要超過總內(nèi)存數(shù)*maxperm%第十三頁，共八十二頁?；仡櫍褐匾腟GA內(nèi)存參數(shù)Shared_pool_sizeDb_cache_sizeDb_keep_cache_sizeLarge_pool_sizeLog_buffer第十四頁，共八十二頁?；仡櫍褐匾腜GA內(nèi)存參數(shù)PGA_AGGREGATE_TARGET第十五頁，共八十二頁。如何在AIX5.3中將SGA定在內(nèi)存中？

修改系統(tǒng)參數(shù)：v_pinshm修改Oracle參數(shù)：LOCK_SGA參考文檔：

如何在Aix5.3中將OracleSGA定在內(nèi)存中.doc第十六頁，共八十二頁。AIX中其他的需要調(diào)整的Oracle參數(shù)TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj第十七頁，共八十二頁。大量導(dǎo)入數(shù)據(jù)前需要做些什么？是否需要備份？估計(jì)數(shù)據(jù)量考慮設(shè)置為非歸檔模式考慮刪除一些索引，導(dǎo)完之后再創(chuàng)建第十八頁，共八十二頁。大量導(dǎo)入數(shù)據(jù)之后應(yīng)該做些什么？分析表。DBMS_STATS.GATHER_SCHEMA_STATS考慮設(shè)置歸檔備份第十九頁，共八十二頁。哪些因素最影響性能？CPU消耗內(nèi)存磁盤IO排序第二十頁，共八十二頁。提高數(shù)據(jù)庫性能的方法用更好的硬件！說服用戶將就著用優(yōu)化數(shù)據(jù)庫優(yōu)化客戶端程序第二十一頁，共八十二頁。案例：解決CPU100%占用步驟一：檢查cpu信息：vmstat步驟二：定位CPU高消耗進(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)整SQL語句第二十七頁，共八十二頁。用V$SQLAREA確定影響力高的語句executionsdisk_readsbuffer_getssorts第二十八頁，共八十二頁。哪些工具可以來查看SQL執(zhí)行計(jì)劃autotracealtersessionsetsql_trace=true;dbms_system.set_sql_trace_in_sessionexplainplanEtc.第二十九頁，共八十二頁。查看語句執(zhí)行計(jì)劃的簡單辦法SetautotraceonSetautotracetraceonly前提：存在plan_table表。如果不存在，可執(zhí)行@?/rdbms/admin/utlxplan.sql執(zhí)行語句相關(guān)技巧：settimingonsettimeon第三十頁，共八十二頁。案例：解讀sql語句執(zhí)行計(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)1rowsprocessed第三十一頁，共八十二頁。setautotrace的局限性必須執(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è)置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)工具。第三十七頁，共八十二頁。安裝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)建了一個(gè)作業(yè)。為了運(yùn)行這個(gè)作業(yè)，要保證job_queue_processes參數(shù)大于0，而且要使用statspack所屬用戶來執(zhí)行。例如下面的腳本設(shè)置每1小時(shí)進(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ù)executions_th：SQL語句執(zhí)行的數(shù)量（默認(rèn)100）disk_reads_th：sql語句執(zhí)行的磁盤讀入數(shù)量（默認(rèn)1000）parse_calls_th：sql語句執(zhí)行的解析調(diào)用數(shù)量（默認(rèn)1000）buffer_gets_th：sql語句執(zhí)行緩沖區(qū)獲取的數(shù)量（默認(rèn)10000）通過statspack..modify_statspack_parameter函數(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í)行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)：RMAN會檢測和報(bào)告損壞的數(shù)據(jù)塊不需要將表空間置于熱備模式，RMAN就可以備份數(shù)據(jù)庫。因此熱備期間生成的重做會減少RMAN自動跟蹤更新新的數(shù)據(jù)文件和表空間，這樣就不再需要在腳本中添加新的表空間和數(shù)據(jù)文件RMAN只備份使用過的數(shù)據(jù)塊，這樣RMAN備份通常小于聯(lián)機(jī)腳本備份RMAN可以與第三方介質(zhì)管理產(chǎn)品一起無縫地工作RMAN支持增量備份可以測試備份而不需要實(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ù)目錄數(shù)據(jù)庫RMAN-06428：未安裝恢復(fù)目錄RMAN>createcatalogtablespacerman_ts;RMAN-06431：恢復(fù)目錄已創(chuàng)建第五十二頁，共八十二頁。注冊數(shù)據(jù)庫CONNECTTARGETSYS/PWD@TESTDB;REGISTERDATABASE第五十三頁，共八十二頁。配置RMAN的默認(rèn)設(shè)置CONFIGURE：用于RMAN操作的默認(rèn)設(shè)備類型，如磁盤或者磁帶(SBT)執(zhí)行自動備份和恢復(fù)操作時(shí)自動分配的通道數(shù)配置備份時(shí)的排斥策略配置自動備份時(shí)給定備份片的最大空間和任何備份集的大小配置備份最優(yōu)化的默認(rèn)設(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)備份文件大小。CONFIGURECHANNELDEVICETYPEDISKMAXPIECESIZE500M;下面語句配置默認(rèn)存儲位置等信息：CONFIGURECHANNELDEVICETYPEDISKFORMAT'D:\oracle\backup\GAXZ\RMAN\%d_%s_%p_%c';下面語句設(shè)置CHANNEL1和CHANNEL2,用于RAC數(shù)據(jù)庫CONFIGURECHANNEL1DEVICETYPEdiskCONNECT'sys/oracle@ora921'MAXPIECESIZE10g;CONFIGURECHANNEL2DEVICETYPEdiskCONNECT'sys/oracle@ora922'MAXPIECESIZE10g;第五十六頁，共八十二頁。FORMAT格式%c備份片的拷貝數(shù)

%d數(shù)據(jù)庫名稱

%D位于該月中的第幾天(DD)

%M位于該年中的第幾月(MM)

%F一個(gè)基于DBID唯一的名稱

%n數(shù)據(jù)庫名稱，向右填補(bǔ)到最大八個(gè)字符

%u一個(gè)八個(gè)字符的名稱代表備份集與創(chuàng)建時(shí)間

%p該備份集中的備份片號，從1開始到創(chuàng)建的文件數(shù)

%U一個(gè)唯一的文件名，代表%u_%p_%c

%s備份集的號

%t備份集時(shí)間戳

第五十七頁，共八十二頁。備份整個(gè)數(shù)據(jù)庫backupdatabaseformat'D:\oracle\backup\GAXZ\RMAN\ora_d%d_s%s_s%p_f%t'4plusarchivelogdeleteinput;第五十八頁，共八十二頁。增量備份

差異(Differential)備份是默認(rèn)的增量備份類型，差異備份會備份上一次進(jìn)行的同級或者低級備份以來所有變化的數(shù)據(jù)塊，而累積(cumulative)備份，則備份上次低級備份以來所有的塊。例如，星期一進(jìn)行了一次2級備份，星期二進(jìn)行了一次3級備份，如果星期四進(jìn)行3級差異增量備份，那么只備份上次3級備份以來變化過的數(shù)據(jù)塊；如果進(jìn)行累積3級備份，那么就會備份上次2級備份以來變化的數(shù)據(jù)塊。第五十九頁，共八十二頁。案例：增量備份首先要做一個(gè)0級備份backupINCREMENTALLEVEL0databaseplusarchivelogdeleteinput;做一個(gè)1級備份：backupINCREMENTALLEVEL1databaseplusarchivelogdeleteinput;做一個(gè)1級累計(jì)備份：backupINCREMENTALLEVEL1CUMULATIVEDATABASEdatabaseplusarchivelogdeleteinput;第六十頁，共八十二頁。備份特定內(nèi)容備份表空間備份數(shù)據(jù)文件backupdatafile7format'/backup/rman/ora_d%d_s%s_s%p_f%t';備份歸檔日志backuparchivelogalldeleteinput;第六十一頁，共八十二頁。RMAN報(bào)告LISTRMAN的list命令是一種在數(shù)據(jù)庫控制文件或者恢復(fù)目錄中查詢備份的歷史信息的方法。列表提供了一組信息，可以提供各種備份的信息，如對應(yīng)物、備份集、歸檔日志備份、控制文件備份等等。REPORTRMAN的report命令被用于判斷數(shù)據(jù)庫的當(dāng)前可恢復(fù)狀態(tài)和提供數(shù)據(jù)庫備份的特定信息、報(bào)告最近沒有備份的數(shù)據(jù)文件等信息。第六十二頁，共八十二頁。常用LIST命令列出所有備份：Listbackupset;列出所有備份簡要信息：Listbackupsetsummary;列出指定備份集備份信息Listbackupsetbs#;列出過期的備份：listexpiredbackup;列出指定表空間的備份信息：Listbackupoftablespaceusers;列出所有已備份的歸檔：listbackupofarchivelogallsummary列出所有需要備份的歸檔：Listarchivelogall;第六十三頁，共八十二頁。常用REPORT命令報(bào)告最近10天沒有備份的數(shù)據(jù)文件reportneedbackupdays=10;報(bào)告按照默認(rèn)策略需要備份的文件Reportneedbackup;按照指定策略報(bào)告需要備份的文件reportneedbackupredundancy=2;報(bào)告數(shù)據(jù)庫信息：Reportschema;報(bào)告過期的備份Reportobsolete;第六十四頁，共八十二頁。備份集的維護(hù)刪除過期備份：deleteobsolete;用deletenopromptobsolete可無需提示。刪除指定的文件集：deletebackupsetbs#;刪除所有備份：deletebackup;驗(yàn)證備份集，如果備份集不復(fù)存在，將被標(biāo)記為expired：crosscheckbackup;刪除expired備份集：Deleteexpiredbackup;第六十五頁，共八十二頁。恢復(fù)數(shù)據(jù)庫第六十六頁，共八十二頁?；謴?fù)案例1：丟失SYSTEM表空間restoredatafile1;recoverdatabase;alterdatabaseopen;第六十七頁，共八十二頁?；謴?fù)案例2：丟失參數(shù)文件1.編輯一個(gè)init.ora，內(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ù)庫。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ù)據(jù)2.創(chuàng)建一個(gè)TEST數(shù)據(jù)表。createtabletestasselectowner,table_name,column_name,data_typefromall_tab_columns;3.查看一下時(shí)間。SELECTTO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')FROMDUAL;4.刪除表中的數(shù)據(jù)。Truncatetabletest;5.恢復(fù)文件。Restoredatabase;6.recover到指定的時(shí)間點(diǎn)recoverdatabaseuntiltime"to_date('240','yyyymmddhh24miss')"或者recover到指定的scn:recoverdatabaseuntilscnscn#;7.alterdatabaseopenresetlogs;8.驗(yàn)證：SQL>selectcount(*)fromtest;COUNT(*)26788第七十頁，共八十二頁。如果不完全恢復(fù)沒有做好怎么辦？比如說，恢復(fù)到的位置早于或者晚于希望恢復(fù)的時(shí)間點(diǎn)？直接重新恢復(fù)是否生效？Let’stry!結(jié)論：這樣做不可以，那該怎么辦？第七十一頁，共八十