Oracle進(jìn)階與提高ppt課件

1、ORACLE 進(jìn)階與提高王忠海5/23/2022.主要內(nèi)容數(shù)據(jù)庫優(yōu)化RMANRAC(假設(shè)有時間的話.數(shù)據(jù)庫優(yōu)化操作系統(tǒng)設(shè)置不當(dāng)數(shù)據(jù)庫參數(shù)設(shè)置不當(dāng)庫構(gòu)造設(shè)計不合理運用程序語句不當(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 5.3.0.0# oslevel -r 5300-07# oslevel -s 5300-07-01-0748.AIX根本命令：查看CPU信息# lsdev -Cc pro

2、cessor proc0 Available 00-00 Processor proc2 Available 00-02 Processor proc4 Available 00-04 Processor proc6 Available 00-06 Processor# lsattr -EHl proc0 attribute value description user_settable frequency 2096901000 Processor Speed False smt_enabled true Processor SMT enabled False smt_threads 2 Pr

3、ocessor SMT threads False state enable Processor state False type PowerPC_POWER5 Processor type False.AIX根本命令：查看內(nèi)存信息bootinfo r16318464# lsdev -Cc memoryL2cache0 Available L2 Cachemem0 Available Memorylsattr -EHl mem0attribute value description user_settablegoodsize 15936 Amount of usable physical me

4、mory in Mbytes Falsesize 15936 Total amount of physical memory in Mbytes False.AIX根本命令：管理交換空間查看交換空間# lsps -aPage Space Physical Volume Volume Group Size %Used Active Auto Type hd6 hdisk0 rootvg 3072MB 1 yes yes lv設(shè)置交換空間smit chps交換空間設(shè)置建議.文件系統(tǒng)的設(shè)置看看下面的輸出。您以為最該調(diào)整哪個文件系統(tǒng)大?。? df -mFilesystem MB blocks Free

5、 %Used Iused %Iused Mounted on/dev/hd4 256.00 252.12 1% 2353 1% /dev/hd2 4096.00 2613.59 37% 38 6% /usr/dev/hd9var 4096.00 4003.59 3% 882 1% /var/dev/hd3 128.00 120.16 5% 1938 1% /tmp/dev/hd1 1024.00 514.95 50% 3742 4% /home正是tmp文件系統(tǒng)！假設(shè)不調(diào)整，安裝數(shù)據(jù)庫時將無法勝利。.AIX5.3中心參數(shù)調(diào)整適用范圍在AIX5.3-01以上需求調(diào)整的參數(shù)：lru_file_re

6、page minperm%、maxperm% 和 maxclient% 等等。參考文檔： 在 AIX 中運轉(zhuǎn) Oracle的優(yōu)化技巧.mht.oracle用戶的資源限制修正/etc/security/limits看一個實例：oracle: fsize = 209715100 data = 20971510 stack = 20971510 core = 20971510 rss = 25165824.AIX中Oracle參數(shù)設(shè)置SGA設(shè)置建議 SGA不要超越總內(nèi)存數(shù)*maxperm%.回想：重要的SGA內(nèi)存參數(shù)Shared_pool_sizeDb_cache_sizeDb_keep_cache

7、_sizeLarge_pool_sizeLog_buffer.回想：重要的PGA內(nèi)存參數(shù)PGA_AGGREGATE_TARGET.如何在AIX5.3中將SGA定在內(nèi)存中？修正系統(tǒng)參數(shù)：v_pinshm 修正Oracle參數(shù)：LOCK_SGA參考文檔： 如何在Aix5.3中將Oracle SGA定在內(nèi)存中.doc.AIX中其他的需求調(diào)整的Oracle參數(shù)TIMED_STATISTICSDB_CACHE_ADVICEoptimizer_index_cachingoptimizer_index_cost_adj.大量導(dǎo)入數(shù)據(jù)前需求做些什么？能否需求備份？估計數(shù)據(jù)量思索設(shè)置為非歸檔方式思索刪除一些索引

8、，導(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)化客戶端程序.案例：處理CPU 100%占用步驟一：檢查cpu信息：vmstat步驟二：定位CPU高耗費進(jìn)程ps aux|head -1;ps aux|sort +2 -rn|head -5.案例：處理CPU 100%占用續(xù)步驟三：定位有問題的語句SELECT /*+ ORDERED */ sql_text FROM v$sqltext a WHERE

9、(a.hash_value, a.address) IN ( SELECT DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid)ORDER BY piece ASC/.案例：處理CPU 100%占用續(xù)步驟四：定位有問題的會話SELECT SID,SER

10、IAL#,USERNAME,TERMINAL FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid)/.案例：處理CPU 100%占用續(xù)步驟五：采取相關(guān)行動1.殺掉會話 alter system kill session sid,serial#;2.分析緣由.優(yōu)化SQL語句，最具挑戰(zhàn)的任務(wù)為什么要優(yōu)化？.Oracle SQL調(diào)整過程1.確定由高影響力的SQL2.抽取和解釋SQL語句3.調(diào)整SQL語句.用V$SQLAREA確定影響力高的語句executionsdisk_readsbuf

11、fer_getssorts.哪些工具可以來查看SQL執(zhí)行方案autotracealter session set sql_trace=true;dbms_system.set_sql_trace_in_sessionexplain planEtc.查看語句執(zhí)行方案的簡一方法Set autotrace onSet autotrace traceonly前提：存在plan_table表。假設(shè)不存在，可執(zhí)行?/rdbms/admin/utlxplan.sql 執(zhí)行語句相關(guān)技巧： set timing on set time on.案例：解讀sql語句執(zhí)行方案SQL select count(*) f

12、rom lpmnt; COUNT(*) 1155Execution PlanPlan hash value: 3530445977| Id | Operation | Name | Rows | Cost (%CPU)| Time | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | 1 | SORT AGGREGATE | | 1 | | | 2 | INDEX FAST FULL SCAN| LPMNT_DBID_IDX | 1102 | 3 (0)| 00:00:01 |Note - dynamic sampling used for this

13、 statementStatistics 509 recursive calls 0 db block gets 190 consistent gets 105 physical reads 0 redo size 412 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed.set autotrace的局限性必需執(zhí)行一遍語句.e

14、xplain plan運用方法Explain plan set statement_id=into table for .查看explain plan過的語句的執(zhí)行方案先做些格式化任務(wù)： set pagesize 1000 col operation format a20 col options format a20 col object_name format a20 col position format 999執(zhí)行語句：select lpad( ,2*(level-1)|operation operation,options,object_name,position from plan_

15、tableCONNECT BY PRIOR id = parent_id andstatement_id=statement_id;.案例：分析winsvr執(zhí)行的語句工具：alter system set sql_trace=true;(假設(shè)想看每個執(zhí)行步驟地時間信息，要設(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ù)庫的工具：statspac

16、k經(jīng)過statspack可以很容易做出Oracle系統(tǒng)性能的全面報告，是Oracle DBA管理Oracle 9i必需掌握的性能調(diào)優(yōu)工具。.安裝statspack創(chuàng)建一個statspack公用表空間運轉(zhuǎn)?/rdbms/admin/spcreate.sql假設(shè)是windows平臺，那么運轉(zhuǎn)：?rdbmsadminspcreate.sql.運用Statspack預(yù)備任務(wù)規(guī)劃自動STATSPACK數(shù)據(jù)搜集。運轉(zhuǎn)?rdbmsadminspauto.sql可以設(shè)置自動搜集statspack數(shù)據(jù)。這個腳本創(chuàng)建了一個作業(yè)。為了運轉(zhuǎn)這個作業(yè)，要保證job_queue_processes參數(shù)大于0，而且要運用s

17、tatspack所屬用戶來執(zhí)行。例如下面的腳本設(shè)置每1小時進(jìn)展一次statspack：variable jobno number;variable instno number;begin select instance_number into :instno from 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ù)executions_th：SQL語句執(zhí)行的數(shù)量默許100di

18、sk_reads_th：sql語句執(zhí)行的磁盤讀入數(shù)量默許1000parse_calls_th：sql語句執(zhí)行的解析調(diào)用數(shù)量默許1000buffer_gets_th：sql語句執(zhí)行緩沖區(qū)獲取的數(shù)量默許10000經(jīng)過statspack.modify_statspack_parameter函數(shù)可以改動閾值的默許值。閾值存放在stats$statspack_parameter中。改動閾值舉例：sqlexec statspack.modify_statspack_parameter(i_buffer_gets_th=20000);.用statspack搜集信息exec statspack.snap; 等

19、待一會或者執(zhí)行一些語句exec statspack.snap;.生成statspack報告?/rdbms/admin/spreport.解讀statspack報告.移除自動執(zhí)行STATSPACK搜集-先查看當(dāng)前自動搜集的jobsSQLselect job,log_user,priv_user,last_date,next_date,interval from user_jobs; -移除義務(wù)1SQL execute dbms_job.remove(1); .刪除統(tǒng)計資料SQLselect max(snap_id) from stats$snapshot;SQL C:oracleora92rdb

20、msadminsptrunc.sql;.發(fā)現(xiàn)占資源高的語句后怎樣辦？改動語句寫法最好適當(dāng)添加索引改動操作系統(tǒng)參數(shù)分析表運用大綱.優(yōu)化案例：經(jīng)過改動語句寫法來提高性能舉一個日期查詢的例子.優(yōu)化案例：經(jīng)過添加索引來提高性能.優(yōu)化案例：經(jīng)過分析表來提高性能.RMANRMAN備份的優(yōu)點：RMAN會檢測和報告損壞的數(shù)據(jù)塊不需求將表空間置于熱備方式，RMAN就可以備份數(shù)據(jù)庫。因此熱備期間生成的重做會減少RMAN自動跟蹤更新新的數(shù)據(jù)文件和表空間，這樣就不再需求在腳本中添加新的表空間和數(shù)據(jù)文件RMAN只備份運用過的數(shù)據(jù)塊，這樣RMAN備份通常小于聯(lián)機(jī)腳本備份RMAN可以與第三方介質(zhì)管理產(chǎn)品一同無縫地任務(wù)RMA

21、N支持增量備份可以測試備份而不需求實踐復(fù)原。.如何配置RMAN將數(shù)據(jù)庫配置為ARCHIVELOG方式 .創(chuàng)建恢復(fù)目錄第一步，在目錄數(shù)據(jù)庫中創(chuàng)建恢復(fù)目錄所用表空間：SQL create tablespace rman_ts datafile d:oracleoradatarmanrman_ts.dbf size 20M;第二步，在目錄數(shù)據(jù)庫中創(chuàng)建RMAN 用戶并授權(quán)：SQL create user rman identified by rman default tablespace rman_ts temporary tablespace temp quota unlimited on rman

22、_ts;SQL grant recovery_catalog_owner, connect, resource to rman;第三步，在目錄數(shù)據(jù)庫中創(chuàng)建恢復(fù)目錄C:rman catalog rman/rman恢復(fù)管理器：版本8.1.6.0.0 - ProductionRMAN-06008：銜接到恢復(fù)目錄數(shù)據(jù)庫RMAN-06428：未安裝恢復(fù)目錄RMANcreate catalog tablespace rman_ts;RMAN-06431：恢復(fù)目錄已創(chuàng)建.注冊數(shù)據(jù)庫CONNECT TARGET SYS/PWDTESTDB; REGISTER DATABASE.配置RMAN的默許設(shè)置CONFI

23、GURE：用于RMAN操作的默許設(shè)備類型，如磁盤或者磁帶(SBT)執(zhí)行自動備份和恢復(fù)操作時自動分配的通道數(shù)配置備份時的排斥戰(zhàn)略配置自動備份時給定備份片的最大空間和任何備份集的大小配置備份最優(yōu)化的默許設(shè)置?？梢允荗N或者OFF.快照控制文件的默許稱號能否自動備份控制文件輔助數(shù)據(jù)文件的默許稱號默許的保管戰(zhàn)略.顯示默許設(shè)置舉例：SHOW DEFAULT DEVICE TYPE;SHOW MAXSET SIZE;SHOW RETENTION POLICY;SHOW ALL;顯示全部配置.CONFGIURE命令舉例 下面語句設(shè)置默許磁盤類型和并發(fā)度CONFIGURE DEVICE TYPE DISK P

24、ARALLELISM 2;下面設(shè)置默許備份文件大小。CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 500M;下面語句配置默許存儲位置等信息：CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT D:oraclebackupGAXZRMAN%d_%s_%p_%c;下面語句設(shè)置CHANNEL 1和CHANNEL 2,用于RAC數(shù)據(jù)庫CONFIGURE CHANNEL 1 DEVICE TYPE disk CONNECT sys/oracleora921 MAXPIECESIZE 10g;CONFIGURE CHANNE

25、L 2 DEVICE TYPE disk CONNECT sys/oracleora922 MAXPIECESIZE 10g;.FORMAT格式%c 備份片的拷貝數(shù) %d 數(shù)據(jù)庫稱號 %D 位于該月中的第幾天 (DD) %M 位于該年中的第幾月 (MM) %F 一個基于DBID獨一的稱號%n 數(shù)據(jù)庫稱號，向右填補(bǔ)到最大八個字符 %u 一個八個字符的稱號代表備份集與創(chuàng)建時間 %p 該備份集中的備份片號，從1開場到創(chuàng)建的文件數(shù)%U 一個獨一的文件名，代表%u_%p_%c %s 備份集的號 %t 備份集時間戳 .備份整個數(shù)據(jù)庫backup database format D:oraclebackup

26、GAXZRMANora_d%d_s%s_s%p_f%t filesperset 4 plus archivelog delete input;.增量備份差別(Differential)備份是默許的增量備份類型，差別備份會備份上一次進(jìn)展的同級或者低級備份以來一切變化的數(shù)據(jù)塊，而累積(cumulative)備份，那么備份上次低級備份以來一切的塊。例如，星期一進(jìn)展了一次2級備份，星期二進(jìn)展了一次3級備份，假設(shè)星期四進(jìn)展3級差別增量備份，那么只備份上次3級備份以來變化過的數(shù)據(jù)塊；假設(shè)進(jìn)展累積3級備份，那么就會備份上次2級備份以來變化的數(shù)據(jù)塊。.案例：增量備份首先要做一個0級備份backup INCRE

27、MENTAL LEVEL 0 database plus archivelog delete input;做一個1級備份：backup INCREMENTAL LEVEL 1 database plus archivelog delete input;做一個1級累計備份：backup INCREMENTAL LEVEL 1 CUMULATIVE DATABASE database plus archivelog delete input;.備份特定內(nèi)容備份表空間備份數(shù)據(jù)文件backup datafile 7 format /backup/rman/ora_d%d_s%s_s%p_f%t;備份歸

28、檔日志backup archivelog all delete input;.RMAN報告LISTRMAN的list命令是一種在數(shù)據(jù)庫控制文件或者恢復(fù)目錄中查詢備份的歷史信息的方法。列表提供了一組信息，可以提供各種備份的信息，如對應(yīng)物、備份集、歸檔日志備份、控制文件備份等等。REPORTRMAN的report命令被用于判別數(shù)據(jù)庫的當(dāng)前可恢復(fù)形狀和提供數(shù)據(jù)庫備份的特定信息、報告最近沒有備份的數(shù)據(jù)文件等信息。.常用LIST命令列出一切備份：List backupset;列出一切備份簡要信息：List backupset summary;列出指定備份集備份信息List backupset bs#;列

29、出過期的備份：list expired backup;列出指定表空間的備份信息：List backup of tablespace users;列出一切已備份的歸檔：list backup of archivelog all summary列出一切需求備份的歸檔：List archivelog all;.常用REPORT命令報告最近10天沒有備份的數(shù)據(jù)文件report need backup days=10;報告按照默許戰(zhàn)略需求備份的文件Report need backup;按照指定戰(zhàn)略報告需求備份的文件 report need backup redundancy=2;報告數(shù)據(jù)庫信息：Repo

30、rt schema;報告過期的備份Report obsolete;.備份集的維護(hù)刪除過期備份：delete obsolete;用delete noprompt obsolete可無需提示。刪除指定的文件集：delete backupset bs#;刪除一切備份：delete backup;驗證備份集，假設(shè)備份集不復(fù)存在，將被標(biāo)志為expired：crosscheck backup;刪除expired備份集：Delete expired backup;.恢復(fù)數(shù)據(jù)庫.恢復(fù)案例1：喪失SYSTEM表空間restore datafile 1;recover database;alter databas

31、e open;.恢復(fù)案例2：喪失參數(shù)文件1.編輯一個init.ora，內(nèi)容包括：db_name=GAXZinstance_name=GAXZcontrol_files=D:oracleproduct10.2.0oradataGAXZCONTROL01.CTLdb_block_size=8192shared_pool_size=1048576002.RMAN銜接到目的數(shù)據(jù)庫。3. startup nomount pfile=D:RMANINIT.ORA;4.restore spfile;5.Shutdown immediate;6.startup.恢復(fù)案例3：恢復(fù)控制文件Startup nomo

32、unt;Restore controlfile;Recover database;Alter database open resetlogs;留意，在Oracle9i中，用resetlogs選項翻開數(shù)據(jù)庫后，備份就不再有效了，需求重新備份。.恢復(fù)案例4：恢復(fù)誤刪除的表數(shù)據(jù)根本模擬過程：1.先備份數(shù)據(jù)2.創(chuàng)建一個TEST數(shù)據(jù)表。create table test as select owner,table_name,column_name,data_type from all_tab_columns;3.查看一下時間。 SELECT TO_CHAR(SYSDATE,YYYYMMDDHH24MISS) FROM DUAL;4.刪除表中的數(shù)據(jù)。Truncate table test;5.恢復(fù)文件。Restore database;6.recover到指定的時間點recover database until time to_date(20211127