1、ORACLE 進階與提高王忠海10/12/2022主要內(nèi)容容數(shù)據(jù)庫優(yōu)優(yōu)化RMANRAC(如果有有時間的的話）數(shù)據(jù)庫優(yōu)優(yōu)化操作系統(tǒng)統(tǒng)設置不不當數(shù)據(jù)庫參參數(shù)設置置不當庫結(jié)構(gòu)設設計不合合理應用程序序語句不不當可能影響響數(shù)據(jù)庫庫性能的的原因有有哪些？AIX5.3中中操作系系統(tǒng)優(yōu)化化Oracle用用戶資源源限制操作系統(tǒng)統(tǒng)核心參參數(shù)補丁AIX中中一些基基本的查查看資源源的命令令如何查看看CPU數(shù)量如何查看看內(nèi)存數(shù)數(shù)量如何查看看交換空空間文件系統(tǒng)統(tǒng)使用情情況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基基本命令令：查看看內(nèi)存信信息bootinfor16318464# lsdev -CcmemoryL2cache0AvailableL2 Cachemem0AvailableMemorylsattr-EHlmem0attributevaluedescriptionuser_settablegoodsize15936Amount of usablephysicalmemoryinMbytes Falsesize15936Total

4、amount of physical memoryinMbytesFalseAIX基基本命令令：管理理交換空空間查看交換換空間# lsps-aPage SpacePhysicalVolumeVolume GroupSize%UsedActiveAutoTypehd6hdisk0rootvg3072MB1yesyeslv設置交換換空間smit chps交換空間間設置建建議文件系統(tǒng)統(tǒng)的設置置看看下面面的輸出出。您認認為最該該調(diào)整哪哪個文件件系統(tǒng)大大??？# 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文件件系統(tǒng)！如果不不調(diào)整，安裝數(shù)數(shù)據(jù)庫時時將無法法成功。AIX5.3核核心參數(shù)數(shù)調(diào)整適用范圍圍在AIX5.3-01以上上需要調(diào)整整的參數(shù)數(shù)：lru_file_repageminperm%、maxperm%和和 maxclient%等等等。參考文檔檔：在 AIX中中運行Oracle的優(yōu)化化技

6、巧.mhtoracle用用戶的資資源限制制修改/etc/security/limits看一個實實例：oracle:fsize= 209715100data =20971510stack= 20971510core =20971510rss= 25165824AIX中中Oracle參數(shù)設設置SGA設設置建議議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_TARGE

7、T如何在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大量導入入數(shù)據(jù)前前需要做做些什么么？是否需要要備份？估計數(shù)據(jù)據(jù)量考慮設置置為非歸歸檔模式式考慮刪除除一些索索引，導導完之后后再創(chuàng)建建大量導入入數(shù)據(jù)之之后應該該做些什什么？分析表。DBMS_STATS.

8、GATHER_SCHEMA_STATS考慮設置置歸檔備份哪些因素素最影響響性能？CPU消消耗內(nèi)存磁盤IO排序提高數(shù)據(jù)據(jù)庫性能能的方法法用更好的的硬件！說服用戶戶將就著著用優(yōu)化數(shù)據(jù)據(jù)庫優(yōu)化客戶戶端程序序案例：解解決CPU100%占用步驟一：檢查cpu信信息：vmstat步驟二：定位CPU高高消耗進進程psaux|head-1;psaux|sort+2-rn|head-5案例：解解決CPU100%占用（續(xù)）步驟三：定位有有問題的的語句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%占用（續(xù)）步驟四：定位有有問題的的會話SELECTSID,SERIAL#,USERNAME,TERMINALFROM v$sessionbWHEREb.paddr =(SELECTaddr

10、FROM v$processcWHEREc.spid=&pid)/案例：解解決CPU100%占用（續(xù)）步驟五：采取相相關行動動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í)執(zhí)行計劃劃autotracealtersession setsql_t

11、race=true;dbms_system.set_sql_trace_in_sessionexplain planEtc.查看語句句執(zhí)行計計劃的簡簡單辦法法Setautotrace onSetautotrace traceonly前提：存存在plan_table表表。如果果不存在在，可執(zhí)執(zhí)行?/rdbms/admin/utlxplan.sql執(zhí)行語句句相關技巧巧：settiming onsettimeon案例：解解讀sql語句句執(zhí)行計計劃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的局限限性必須執(zhí)行行一遍語語句explain plan使使用方法法Explain plansetstatement_id=intotablefor查看explainplan過的的語句的的執(zhí)行計計劃先做些格格式化工工作：setpagesize1000coloperation formata20coloptionsf

14、ormata20colobject_nameformat a20colpositionformat 999執(zhí)行語句句：selectlpad( ,2*(level-1)|operationoperation,options,object_name,positionfrom plan_tableCONNECT BY PRIOR id =parent_id andstatement_id=statement_id;案例：分分析winsvr執(zhí)行行的語句句工具：altersystemset sql_trace=true;(如果想想看每個個執(zhí)行步步驟地時時間信息息，要設設置timed_statistics

15、參參數(shù)）查看生成成的trace文件在user_dump_dest環(huán)環(huán)境變量量所對目目錄下。ls-ln用tkprof命令來來格式化化輸出解讀輸出出。set_sql_trace_in_sessiondbms_system.set_sql_trace_in_session優(yōu)化數(shù)據(jù)據(jù)庫的工工具：statspack通過statspack可以以很容易易做出Oracle系系統(tǒng)性能能的全面面報告，是OracleDBA管管理Oracle9i必須須掌握的的性能調(diào)調(diào)優(yōu)工具具。安裝statspack創(chuàng)建一個個statspack專用表表空間運行?/rdbms/admin/spcreate.sql如果是windows平臺

16、，則運行行：?rdbmsadminspcreate.sql使用Statspack準備備工作規(guī)劃自動動STATSPACK數(shù)據(jù)搜搜集。運運行?rdbmsadminspauto.sql可以以設置自自動搜集集statspack數(shù)據(jù)。這個腳腳本創(chuàng)建建了一個個作業(yè)。為了運運行這個個作業(yè)，要保證證job_queue_processes參數(shù)數(shù)大于0，而且且要使用用statspack所屬用用戶來執(zhí)執(zhí)行。例例如下面面的腳本本設置每每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;/調(diào)整statspack參數(shù)數(shù)executions_th：SQL語句句執(zhí)行的的數(shù)量（默認100）disk_reads_th：sql語句句執(zhí)行的的磁盤讀讀入數(shù)量量（默認認1000）parse_calls_th：sql語語句執(zhí)行行的解析析調(diào)用數(shù)數(shù)量（默默認1000）buffer_gets_th：sql語語句執(zhí)行行緩沖區(qū)區(qū)獲取的的數(shù)量（默認10000）

18、通過statspack.modify_statspack_parameter函函數(shù)可以以改變閾閾值的默默認值。閾值存存放在stats$statspack_parameter中。改變閾值值舉例：sqlexecstatspack.modify_statspack_parameter(i_buffer_gets_th=20000);用statspack搜集信信息exec statspack.snap;等待一會會或者者執(zhí)行一一些語句句exec statspack.snap;生成statspack報告告?/rdbms/admin/spreport解讀statspack報告告移除自動動執(zhí)行STATSPAC

19、K收收集-先查看當當前自動動收集的的jobsSQLselect job,log_user,priv_user,last_date,next_date,intervalfrom user_jobs;-移除除任務1SQL executedbms_job.remove(1);刪除統(tǒng)計計資料SQLselect max(snap_id)from stats$snapshot;SQL C:oracleora92rdbmsadminsptrunc.sql;發(fā)現(xiàn)占資資源高的的語句后后怎么辦辦？改變語句句寫法（最好）適當增加加索引改變操作作系統(tǒng)參參數(shù)分析表使用大綱綱優(yōu)化案例例：通過過改變語語句寫法法來提高高性能

20、舉一個日日期查詢詢的例子子優(yōu)化案例例：通過過增加索索引來提提高性能能優(yōu)化案例例：通過過分析表表來提高高性能RMANRMAN備份的的優(yōu)點：RMAN會檢測測和報告告損壞的的數(shù)據(jù)塊塊不需要將將表空間間置于熱熱備模式式，RMAN就就可以備備份數(shù)據(jù)據(jù)庫。因因此熱備備期間生生成的重重做會減減少RMAN自動跟跟蹤更新新新的數(shù)數(shù)據(jù)文件件和表空空間，這這樣就不不再需要要在腳本本中添加加新的表表空間和和數(shù)據(jù)文文件RMAN只備份份使用過過的數(shù)據(jù)據(jù)塊，這這樣RMAN備備份通常常小于聯(lián)聯(lián)機腳本本備份RMAN可以與與第三方方介質(zhì)管管理產(chǎn)品品一起無無縫地工工作RMAN支持增增量備份份可以測試試備份而而不需要要實際還還原。如

21、何配置置RMAN將數(shù)據(jù)庫庫配置為為ARCHIVELOG模式式 創(chuàng)建恢復復目錄第一步，在目錄錄數(shù)據(jù)庫庫中創(chuàng)建建恢復目目錄所用用表空間間：SQL createtablespace rman_tsdatafiled:oracleoradatarmanrman_ts.dbf size20M;第二步，在目錄錄數(shù)據(jù)庫庫中創(chuàng)建建RMAN用用戶并授授權(quán)：SQL createuser rmanidentifiedbyrman defaulttablespacerman_ts temporarytablespace tempquotaunlimited on rman_ts;SQL grant recovery_

22、catalog_owner, connect, resource to rman;第三步，在目錄錄數(shù)據(jù)庫庫中創(chuàng)建建恢復目目錄C:rmancatalog rman/rman恢復管理理器：版版本8.1.6.0.0- ProductionRMAN-06008：連接接到恢復復目錄數(shù)數(shù)據(jù)庫RMAN-06428：未安安裝恢復復目錄RMANcreatecatalog tablespacerman_ts;RMAN-06431：恢復復目錄已已創(chuàng)建注冊數(shù)據(jù)據(jù)庫CONNECT TARGETSYS/PWDTESTDB;REGISTERDATABASE配置RMAN的的默認設設置CONFIGURE：用于RMAN操操作的默

23、默認設備備類型，如磁盤盤或者磁磁帶(SBT)執(zhí)行自動動備份和和恢復操操作時自自動分配配的通道道數(shù)配置備份份時的排排斥策略略配置自動動備份時時給定備備份片的的最大空空間和任任何備份份集的大大小配置備份份最優(yōu)化化的默認認設置。可以是是ON或或者OFF.快照控制制文件的的默認名名稱是否自動動備份控控制文件件輔助數(shù)據(jù)據(jù)文件的的默認名名稱默認的保保存策略略顯示默認認設置舉舉例：SHOW DEFAULTDEVICE TYPE;SHOW MAXSETSIZE;SHOW RETENTIONPOLICY;SHOW ALL;（顯示全全部配置置）CONFGIURE命命令舉例例下面語句句設置默默認磁盤盤類型和和并發(fā)度

24、度CONFIGUREDEVICE TYPEDISKPARALLELISM2;下面設置置默認備備份文件件大小。CONFIGURECHANNELDEVICETYPEDISK MAXPIECESIZE500M;下面語句句配置默默認存儲儲位置等等信息：CONFIGURECHANNELDEVICETYPEDISK FORMATD:oraclebackupGAXZRMAN%d_%s_%p_%c;下面語句句設置CHANNEL 1和和CHANNEL2,用于于RAC數(shù)據(jù)庫庫CONFIGURECHANNEL1 DEVICETYPE diskCONNECTsys/oracleora921 MAXPIECESIZE1

25、0g;CONFIGURECHANNEL2 DEVICETYPE diskCONNECTsys/oracleora922 MAXPIECESIZE10g;FORMAT格格式%c備備份片的的拷貝數(shù)數(shù)%d數(shù)數(shù)據(jù)庫名名稱%D位位于該該月中的的第幾天天 (DD)%M 位于于該年中中的第幾幾月(MM)%F一一個基于于DBID唯一一的名稱稱%n 數(shù)據(jù)據(jù)庫名稱稱，向右右填補到到最大八八個字符符%u一一個八個個字符的的名稱代代表備份份集與創(chuàng)創(chuàng)建時間間%p該該備份集集中的備備份片號號，從1開始到到創(chuàng)建的的文件數(shù)數(shù)%U 一個個唯一的的文件名名，代表表%u_%p_%c%s 備份份集的號號%t備備份集時時間戳備份整個個

26、數(shù)據(jù)庫庫backupdatabaseformat D:oraclebackupGAXZRMANora_d%d_s%s_s%p_f%tfilesperset4 plusarchivelogdeleteinput;增量備份份差異(Differential)備份是是默認的的增量備備份類型型，差異異備份會會備份上上一次進進行的同級或者者低級備份以來來所有變變化的數(shù)數(shù)據(jù)塊，而累積積(cumulative)備份，則備份份上次低級級備份以來所有有的塊。例如，星期一一進行了了一次2級備份份，星期期二進行行了一次次3級備備份，如如果星期期四進行行3級差差異增量量備份，那么只只備份上上次3級級備份以以來變化化過的

27、數(shù)數(shù)據(jù)塊；如果進進行累積積3級備備份，那那么就會會備份上上次2級級備份以以來變化化的數(shù)據(jù)據(jù)塊。案例：增增量備份份首先要做做一個0級備份份backupINCREMENTALLEVEL0databaseplus archivelogdelete input;做一個1級備份份：backupINCREMENTALLEVEL1databaseplus archivelogdelete input;做一個1級累計計備份：backupINCREMENTALLEVEL1CUMULATIVE DATABASE database plusarchivelogdeleteinput;備份特定定內(nèi)容備份表空空間備份數(shù)

28、據(jù)據(jù)文件backupdatafile7format/backup/rman/ora_d%d_s%s_s%p_f%t;備份歸檔檔日志backuparchivelogalldelete input;RMAN報告LISTRMAN的list命命令是一一種在數(shù)數(shù)據(jù)庫控控制文件件或者恢恢復目錄錄中查詢詢備份的的歷史信信息的方方法。列列表提供供了一組組信息，可以提提供各種種備份的的信息，如對應應物、備備份集、歸檔日日志備份份、控制制文件備備份等等等。REPORTRMAN的report命令令被用于于判斷數(shù)數(shù)據(jù)庫的的當前可可恢復狀狀態(tài)和提提供數(shù)據(jù)據(jù)庫備份份的特定定信息、報告最最近沒有有備份的的數(shù)據(jù)文文件等信信息

29、。常用LIST命命令列出所有有備份：List backupset;列出所有有備份簡簡要信息息：List backupsetsummary;列出指定定備份集集備份信信息List backupsetbs#;列出過期期的備份份：list expiredbackup;列出指定定表空間間的備份份信息：List backupoftablespace users;列出所有有已備份份的歸檔檔：list backupofarchivelog allsummary列出所有有需要備備份的歸歸檔：List archivelogall;常用REPORT命令令報告最近近10天天沒有備備份的數(shù)數(shù)據(jù)文件件reportneed

30、backupdays=10;報告按照照默認策策略需要要備份的的文件Reportneedbackup;按照指定定策略報報告需要要備份的的文件reportneedbackupredundancy=2;報告數(shù)據(jù)據(jù)庫信息息：Reportschema;報告過期期的備份份Reportobsolete;備份集的的維護刪除過期期備份：deleteobsolete;用delete noprompt obsolete可無需需提示。刪除指定定的文件件集：deletebackupset bs#;刪除所有有備份：deletebackup;驗證備份份集，如如果備份份集不復復存在，將被標標記為expired：crossch

31、eck backup;刪除expired備備份集：Deleteexpiredbackup;恢復數(shù)據(jù)據(jù)庫恢復案例例1：丟丟失SYSTEM表空空間restore datafile 1;recover database;alterdatabaseopen;恢復案例例2：丟丟失參數(shù)數(shù)文件1.編輯輯一個init.ora，內(nèi)內(nèi)容包括括：db_name=GAXZinstance_name=GAXZcontrol_files=D:oracleproduct10.2.0oradataGAXZCONTROL01.CTLdb_block_size=8192shared_pool_size=1048576002.RM

32、AN連連接到目目標數(shù)據(jù)據(jù)庫。3.startup nomountpfile=D:RMANINIT.ORA;4.restorespfile;5.Shutdown immediate;6.startup恢復案例例3：恢恢復控制制文件Startup nomount;Restore controlfile;Recover database;Alterdatabaseopen resetlogs;注意，在在Oracle9i中中，用resetlogs選選項打開開數(shù)據(jù)庫庫后，備備份就不不再有效效了，需需要重新新備份。恢復案例例4：恢恢復誤刪刪除的表表數(shù)據(jù)基本模擬擬過程：1.先備備份數(shù)據(jù)據(jù)2.創(chuàng)建建一個TEST數(shù)據(jù)表表。createtabletestasselectowner,table_name,column_name,data_typefromall_tab_columns;3.查看看一下時時間。SELECTTO_CHAR(SYSDATE,YYYYMMDDHH24MISS)FROMDUAL;4.刪除除表中的的數(shù)據(jù)。Truncatetabletest;5.恢復復文件。Restore database;6.recover到到指定的的時間點點recover database until timeto_da