數(shù)據(jù)庫程序員面試分類真題21

數(shù)據(jù)庫程序員面試分類真題21簡答題1.

如何遷移SYS.AUD$表到其他表空間?正確答案：在日常的數(shù)據(jù)庫維護(hù)中，經(jīng)常出現(xiàn)SYSTEM表空間被撐滿，在絕大多數(shù)情況下是因?yàn)閿?shù)據(jù)庫登錄審計(jì)的功能被啟動了（江南博哥），此時(shí)一般建議把SYS.AUD$相關(guān)對象遷移到其他表空間，從而避免SYSTEM被用完的風(fēng)險(xiǎn)。

在Oracle11g之前遷移方法如下：

ALTERTABLESYS.AUDIT$MOVETABLESPACEUSERS;

ALTERTABLESYS.AUDIT_ACTIONSMOVETABLESPACEUSERS;

ALTERTABLESYS.AUD$MOVETABLESPACEUSERS;

ALTERTABLESYS.AUD$MOVELOB(SQLBIND)STOREASSYS_IL0000000384C00041$$(TABLESPACEUSERS);

ALTERTABLESYS.AUD$MOVELOB(SQLTEXT)STOREASSYS_IL0000000384C00041$$(TABLESPACEUSERS);

ALTERINDEXSYS.I_AUDITREBUILDONLINETABLESPACEUSERS;

ALTERINDEXSYS.I_AUDIT_ACTIONSREBUILDONLINETABLESPACEUSERS;

從Oracle11g開始可以使用DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION進(jìn)行遷移：

EXECDBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE=＞DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,AUDIT_TRAIL_LOCATION_VALUE=＞'USERS');

如下示例為授予審計(jì)的一些權(quán)限：

GRANTAUDITANYTOLHR_TEST;

GRANTAUDITSYSTEMTOLHR_TEST;

在Oracle11g之前通過手工清理的方式或自定義作業(yè)來定期清理SYS.AUD$表，如下：

TRUNCATEFROMSYS.AUD$;

DELETEFROMSYS.AUD$WHEREOBJ$NAME='EMP';[考點(diǎn)]審計(jì)

2.

如何正確地清理SYS.AUD$表?正確答案：如果AUD$表過大，那么直接TRUNCATEAUD$表，系統(tǒng)要立即釋放大量的EXTENTS，會嚴(yán)重影響系統(tǒng)性能?？梢酝ㄟ^如下2個(gè)步驟逐步釋放EXTENTS。

1)清空數(shù)據(jù)并且保留原來的EXTENTS：

TRUNCATETABLESYS.AUD$REUSESTORAGE;

在這里，REUSESTORAGE是TRUNCATE的一個(gè)參數(shù)，表示保持原來的存儲不變。一般情況下，SQL命令“TRUNCATETABLETABLE_NAME;”其實(shí)就是“TRUNCATETABLETABLE_NAMEDROPSTORAGE;”。DROPSTORAGE是TRUNCATETABLE的默認(rèn)參數(shù)。

2)逐步回縮EXTENTS：

ALTERTABLESYS.AUD$DEALLOCATEUNUSEDKEEP5000M;

ALTERTABLESYS.AUD$DEALLOCATEUNUSEDKEEP2000M;

......

ALTERTABLESYS.AUD$DEALLOCATEUNUSEDKEEP10M;

需要注意的是，在執(zhí)行的時(shí)候，可以根據(jù)實(shí)際情況調(diào)整每次回縮空間的大小。

若審計(jì)在OS和XML選項(xiàng)下進(jìn)行手動刪除審計(jì)文件。在Oracle11g中通過DBMS_AUDIT_MGMT包下的子過程進(jìn)行手動或定期清理。[考點(diǎn)]審計(jì)

3.

審計(jì)有哪些類型?正確答案：Oracle中審計(jì)總體上可分為“標(biāo)準(zhǔn)審計(jì)”和“細(xì)粒度審計(jì)”，后者也稱為“基于政策的審計(jì)”，在Oracle10g之后功能得到很大增強(qiáng)。其中，標(biāo)準(zhǔn)審計(jì)可分為用戶級審計(jì)和系統(tǒng)級審計(jì)。用戶級審計(jì)是任何Oracle用戶都可設(shè)置的審計(jì)，主要是用戶針對自己創(chuàng)建的數(shù)據(jù)庫表或視圖進(jìn)行審計(jì)，記錄所有用戶對這些表或視圖的一切成功和(或)不成功的訪問以及各種類型的SQL操作。系統(tǒng)級審計(jì)只能由DBA設(shè)置，用以監(jiān)測成功或失敗的登錄要求、監(jiān)測GRANT和REVOKE操作以及其他數(shù)據(jù)庫級權(quán)限下的操作。

在Oracle中分別支持以下三種標(biāo)準(zhǔn)審計(jì)類型，或者說，可以從3個(gè)角度去啟用審計(jì)：

1)語句審計(jì)(StatementAuditing)，對某種類型的SQL語句審計(jì)，不指定結(jié)構(gòu)或?qū)ο?。審?jì)SQL語句的成功執(zhí)行或不成功執(zhí)行。這里從SQL語句的角度出發(fā)，進(jìn)行指定。審計(jì)只關(guān)心執(zhí)行的語句。例如，AUDITCREATETABLE語句，其中，AUDIT為使用審計(jì)的關(guān)鍵字。該語句表示對CREATETABLE語句的執(zhí)行進(jìn)行記錄，不管這條語句是否為針對某個(gè)對象的操作。

2)權(quán)限審計(jì)(PrivilegeAuditing)，對執(zhí)行相應(yīng)動作的系統(tǒng)特權(quán)的使用審計(jì)，對涉及某些權(quán)限的操作進(jìn)行審計(jì)，這里強(qiáng)調(diào)“系統(tǒng)權(quán)限”，例如，“AUDITCREATETABLE;”命令，可以表咀對涉及“CREATETABLE”權(quán)限的操作進(jìn)行審計(jì)。所以，在這種命令的情況下，既產(chǎn)生一個(gè)語句審計(jì)，又產(chǎn)生了一個(gè)權(quán)限審計(jì)。有時(shí)候語句審計(jì)和權(quán)限審計(jì)是相互重復(fù)的。

3)對象審計(jì)(ObjectAuditing)，對一特殊模式對象上的指定對象的審計(jì)。對一個(gè)特殊模式對象上的DML語句進(jìn)行審計(jì)。記錄作用在指定對象上的操作。例如，AUDITSELECTONSCOTT.DEPT語句，表示指定SCOTT用戶的DEPT表，審計(jì)對其進(jìn)行的SELECT語句。

[考點(diǎn)]審計(jì)

4.

審計(jì)中BYACCESS和BYSESSION的區(qū)別是什么?正確答案：BYACCESS表示每一個(gè)被審計(jì)的操作都會生成一條AUDITTRAIL，而BYSESSION表示一個(gè)會話里面同類型的操作只會生成一條AUDITTRAIL，默認(rèn)為BYSESSION。[考點(diǎn)]審計(jì)

5.

什么是細(xì)粒度審計(jì)?正確答案：從Oracle9i開始，引入了細(xì)粒度的對象審計(jì)，使得審計(jì)變得更為關(guān)注某個(gè)方面，并且更為精確。細(xì)粒度的審計(jì)可以在訪問某些行和列時(shí)審計(jì)對表的訪問，從而極大地減少審計(jì)表的記錄數(shù)量。使用標(biāo)準(zhǔn)的審計(jì)，可以發(fā)現(xiàn)訪問了哪些對象，以及由誰訪問，但是無法知道訪問了哪些行或列。細(xì)粒度審計(jì)被稱為FGA(Fine-GrainedAudit)，由DBMS_FGA的PL/SQL程序來實(shí)現(xiàn)。[考點(diǎn)]審計(jì)

6.

如何查看某一個(gè)會話是否被其他會話阻塞?正確答案：SQL語句如下：

SELECTABLOCKING_SESSION_STATUS,ABLOCKING_INSTANCE,ABLOCKING_SESSION,A.EVENTFROMGV$SESSIONAWHEREA.SID=1070;

由上圖可知，1070會話被2號實(shí)例上的970會話阻塞。[考點(diǎn)]會話

7.

如何查到會話正在執(zhí)行的SQL語句?正確答案：SQL語句如下：

SELECTB.SQL_ID,B.SQL_TEXTFROMGV$SESSIONA,GV$SQLBWHEREA.SQL_ID=B.SQL_IDANDA.INST_ID=B.INST_IDANDA.SID=1070;

通過SQL_ID這個(gè)字段，可以獲取到某個(gè)會話正在執(zhí)行的SQL語句。[考點(diǎn)]會話

8.

如何徹底殺掉會話?V$SESSION的STATUS為KILLED的情況下如何找到相關(guān)的后臺OS進(jìn)程?正確答案：一般情況下，可以通過執(zhí)行SQL語句“ALTERSYSTEMKILLSESSION'SID,SERIAL#'”直接殺掉會話，當(dāng)SESSION是ACTIVE的時(shí)候，ALTERSYSTEMKILLSESSION只是將SESSION的狀態(tài)標(biāo)識為KILLED，SERVER變?yōu)镻SEUDO狀態(tài)，但并不會釋放SESSION持有的資源，所以，在執(zhí)行完ALTERSYSTEMKILLSESSION后，會話還是一直存在。執(zhí)行命令KILLSESSION的時(shí)候，后邊加上IMMEDIATE，這樣在沒有事務(wù)的情況下，相關(guān)會話就會立即釋放而不會變?yōu)镵ILLED的狀態(tài)；當(dāng)有事務(wù)存在的情況下，會先進(jìn)行回滾相關(guān)的事務(wù)，然后釋放會話所占有的資源。

另外，由于變?yōu)镵ILLED狀態(tài)的會話的PADDR列都變成了另外一個(gè)值，因此，通過平常的連接方式就沒有辦法關(guān)聯(lián)到后臺進(jìn)程，在Oracle11g下提供了CREATOR_ADDR列，該列可以關(guān)聯(lián)到后臺進(jìn)程，對于Oracle10g可以通過特殊的SQL找到后臺的進(jìn)程號。

Oracle10g可以使用如下的腳本：

SEEECTINST_ID，SPID，PROGRAM，'KILL-9'||SPIDKILL9FROMGV$PROCESSA

WHEREPROGRAM!='PSEUDO'

AND(INST_ID,ADDR)NOTIN(SELECTINST_ID,PADDRFROMGV$SESSION)

AND(INST_ID,ADDR)NOTIN(SELECTINST_ID,PADDRFROMGV$BGPROCESS)

AND(iNST_ID,ADDR)NOTIN(SELECTINST_ID,PADDRFROMGV$SHARED_SERVER);

Oracle11g可以使用如下的腳本：

SELECTA.INST_ID,A.SID||','||A.SERIAL#||','||(SELECTSPIDFROMGV$PROCESSBWHEREB.INST_ID=A.INST_ID

ANDA.CREATOR_ADDR=B.ADDR--ANDDECODE(A.STATUS,'KILLED',A.CREATOR_ADDR,A.PADDR)=B.ADDR)SESSIONID,

A.PADDR,A.STATUS,A.PROGRAM,

ALTERSYSTEMDISCONNECTSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'KILL_SESSION

FROMGV$SESSIONAWHEREA.USERNAME='SYS'ANDA.STATUS='KILLED';[考點(diǎn)]會話

9.

如何讓普通用戶可以殺掉自己用戶的會話?正確答案：普通用戶想要?dú)⒌魰挶仨氁哂蠥LTERSYSTEM的權(quán)限，但是由于該權(quán)限過大，用戶可能使用該權(quán)限錯(cuò)殺其他用戶的會話，所以，有沒有其他辦法可以實(shí)現(xiàn)該功能呢?該類問題也是DBA工作中常遇到的問題，下面給出一種解決方案。

首先，可以創(chuàng)建一個(gè)查詢自己會話信息的視圖，將該視圖創(chuàng)建公共同義詞，然后創(chuàng)建一個(gè)存儲過程，該存儲過程實(shí)現(xiàn)殺掉會話的需要，最后將該存儲過程的執(zhí)行權(quán)限賦給PUBLIC即可解決這個(gè)問題。[考點(diǎn)]會話

10.

SESSIONS和PROCESSES有什么關(guān)系?正確答案：在數(shù)據(jù)庫安裝完成后，常常需要設(shè)置SESSIONS和PROCESSES的大小。其中，SESSIONS指定了一個(gè)實(shí)例中允許的會話數(shù)，即能同時(shí)登錄到數(shù)據(jù)庫的并發(fā)用戶數(shù)；PROCESSES指定了一個(gè)實(shí)例在操作系統(tǒng)級別能同時(shí)運(yùn)行的進(jìn)程數(shù)，包括后臺進(jìn)程與服務(wù)器進(jìn)程。由于一個(gè)后臺進(jìn)程可能同時(shí)對應(yīng)多個(gè)會話，所以，通常SESSIONS的值大于PROCESSES的值。

通過查找官方文檔，可以知道SESSIONS參數(shù)的值在Oracle10g和11g中是不同的，見下表。屬性O(shè)racle10gOracle11gR2參數(shù)值的類型IntegerInteger默認(rèn)值Derived：(1.1*PROCESSES)+5Derived：(1.5*PROCESSES)+22是否可以動態(tài)修改否否范圍1～2311～216(即1～65536)注：在Oracle11gR1中，該參數(shù)值和10g足一樣的，修改語句為“ALTERSYSTEMSETPROCESSES|SESSIONS=200SCOPE=SPFILE;”。

由于SESSIONS的值是根據(jù)PROCESSES的值計(jì)算得到的，所以，一般情況下只需要設(shè)置PROCESSES的值即可。在Oracle11gR2以下版本中，SESSIONS大小的計(jì)算公式為(1.1*PROCESSES)+5；在Oracle11gR2中，SESSIONS大小的計(jì)算公式為(1.5*PROCESSES)+22。若SESSIONS的當(dāng)前值比計(jì)算值大，則SESSIONS的值可能保持不變；若SESSIONS的當(dāng)前值比計(jì)算值小，則SESSIONS取計(jì)算值，即SESSIONS的值總是取MAX(當(dāng)前值，計(jì)算值)，但是這個(gè)也不是絕對的。

當(dāng)數(shù)據(jù)庫連接的并發(fā)用戶已經(jīng)達(dá)到SESSIONS的值時(shí)，又有新會話連接進(jìn)來，就會報(bào)錯(cuò)：“ORA-00018,"maximumnumberofsessionsexceeded"”。

當(dāng)Oracle需要啟動新的PROCESS，而當(dāng)前的進(jìn)程數(shù)又已經(jīng)達(dá)到PROCESSES參數(shù)時(shí)，就會報(bào)錯(cuò)：“ORA-00020:maximumnumberofprocesses(2048)exceeded”。

如果數(shù)據(jù)庫上連接被占用完，當(dāng)新的連接過來時(shí)，那么就會在客戶端產(chǎn)生“ORA-12519,TNS:noappropriateservicehandlerfound”的報(bào)錯(cuò)信息。[考點(diǎn)]會話

11.

如何根據(jù)OS進(jìn)程快速獲得DB進(jìn)程信息與正在執(zhí)行的語句?正確答案：在OS上執(zhí)行top命令之后就可以得到OS進(jìn)程號，有了OS進(jìn)程號后，就可以在數(shù)據(jù)庫中直接查詢：

SELECTB.SID,B.SERIAL#,C.SPID,B.SQL_IDFROMV$SESSIONB,V$PROCESSCWHEREB.PADDR=C.ADDRANDC.SPID=XXXX;[考點(diǎn)]會話

12.

怎么殺掉特定的數(shù)據(jù)庫會話?正確答案：“ALTERSYSTEMKILLSESSION'SID,SERIAL#'IMMEDIATE;”或者“ALTERSYSTEMDISCONNECTSESSION'SID,SERIAL#'IMMEDIATE;”。

在Windows上還可以采用Oracle提供的orakill殺掉一個(gè)線程(其實(shí)就是一個(gè)Oracle進(jìn)程)。在Linux上，可以直接利用kill-9殺掉數(shù)據(jù)庫進(jìn)程對應(yīng)的OS進(jìn)程。[考點(diǎn)]會話

13.

如何快速地清理Oracle的進(jìn)程?正確答案：若想要快速清理掉Oracle的進(jìn)程，最直接的辦法是殺pmon進(jìn)程。有如下3條命令可供選擇，其中加粗的orcl替換成ORACLE_SID的值即可。

kill-9'ps-ef|greporcl|grep-vgrep|awk'{print$2}''

ps-ef|greporcl|grep-vgrep|awk'{print$2}'|xargskill-9

ipcs-m|greporacle|awk'{print$2}'|xargsipcrmshm

若想要快速殺掉集群的進(jìn)程，則可以執(zhí)行如下命令：

km-9'ps-ef|grepd.bin|grep-vgrep|awk'{print$2}''

注意，生產(chǎn)庫上嚴(yán)禁使用，否則可能導(dǎo)致集群不能正常啟動。[考點(diǎn)]會話

14.

等待事件有哪些分類?正確答案：Oracle的等待事件主要可以分為兩類：空閑(Idle)等待事件和非空閑(Non-Idle)等待事件。

1)空閑等待事件指Oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫的時(shí)候，不用過多注意這部分事件。

2)非空閑等待事件專門針對Oracle的活動，指數(shù)據(jù)庫任務(wù)或應(yīng)用運(yùn)行過程中發(fā)生的等待，這些等待事件是在調(diào)整數(shù)據(jù)庫的時(shí)候需要關(guān)注與研究的。

通過如下的SQL語句可以查詢等待事件的類型：

SELECTWAIT_CLASS#,WAIT_CLASS_ID,WAIT_CLASS,COUNT(*)AS"COUNT"FROM

V$EVENT_NAME

GROUPBYWAIT_CLASS#,WAIT_CLASS_ID,WAIT_CLASSORDERBYWAIT_CLASS#;[考點(diǎn)]等待事件

15.

什么是AdaptiveLogFileSync?正確答案：當(dāng)前臺進(jìn)程提交事務(wù)(COMMIT)后，LGWR需要執(zhí)行日志寫出操作，而前臺進(jìn)程因此進(jìn)入logfilesync等待。

在Oracle11g之前的版本中，LGWR執(zhí)行寫入操作完成后，會通知前臺進(jìn)程，這就是Post/Wait模式；在Oracle11gR2中，為了優(yōu)化這個(gè)過程，前臺進(jìn)程通知LGWR寫之后，可以通過定時(shí)獲取的方式來查詢寫出進(jìn)度，這被稱為Polling模式。在Oracle中，這個(gè)特性被默認(rèn)開啟，通過隱含參數(shù)“_use_adaptive_log_file_sync”來控制(默認(rèn)值為true)，這個(gè)參數(shù)的含義是，數(shù)據(jù)庫可以自適應(yīng)地在Post/Wait和Polling模式間選擇和切換。正是由于這個(gè)原因，帶來了很多Bug，反而使得logfilesync的等待異常得高。因此，如果在Oracle版本中觀察到這樣的特征，那么就極有可能與此特性的Bug有關(guān)。

在Post/Wait和Polling機(jī)制之間的切換，Oracle會記錄到LGWR進(jìn)程的trace中，如下：

Logfilesyncswitchingtopolling

......

Logfilesyncswitchingtopost/wait

若遇到此問題，則通常將隱含參數(shù)“_use_adaptive_log_file_sync”設(shè)置為false，回歸到以前的Post/Wait模式，這將會有助于問題的解決。關(guān)閉Polling模式的命令為

altersystemset"_use_adaptive_log_file_sync"=falsesid='*';[考點(diǎn)]等待事件

16.

請列舉一次你曾經(jīng)處理過的故障。正確答案：沒有唯一答案，讀者根據(jù)自己的維護(hù)經(jīng)驗(yàn)講解即可，下面給出一個(gè)作者曾經(jīng)處理過的故障診斷案例。

1.故障環(huán)境項(xiàng)目sourcedbdb類型RACdbversion.0db存儲ASMOS版本及kernel版本AIX64位2.故障發(fā)生現(xiàn)象及報(bào)錯(cuò)信息有一套數(shù)據(jù)庫做測試的時(shí)候，CPU利用率很高，當(dāng)時(shí)抓取了CPU和AWR的信息。發(fā)生問題的時(shí)間段是19～23點(diǎn)，其中，nmon數(shù)據(jù)截圖如下圖所示。可以看到CPU的利用率是非常高的，下邊來看看AWR中的數(shù)據(jù)。從等待事件中可以很明顯地看出，“enq:SQ-contention”和“DFSlockhandle”這兩個(gè)等待事件異常?！癟op5TimedEvents”部分也是AWR報(bào)告中非常重要的部分，從這里可以看出等待事件中排在前五位的是哪些事件，從而基本上就可以判斷出性能瓶頸在什么地方。在這里，“enq:SQ-contention”等待了172254次，等待時(shí)間為69652s，平均等待時(shí)間為69652/172254s=404ms，等待類別為Configuration即配置上的等待問題。3.有關(guān)序列等待的基礎(chǔ)知識根據(jù)AWR報(bào)告的內(nèi)容基本可以斷定，只要解決了“enq:SQ-contention”和“DFSlockhandle”這兩個(gè)等待事件即可解決問題。其實(shí)，“enq:SQ-contention”“rowcachelock”“DFSlockhandle”和“enq:SV-contention”這4個(gè)等待事件都與Oracle的序列有關(guān)，如下：SELECT*FROMV$EVENT_NAMEWHERENAMEIN('rowcachelock';'enq:SQ-contention','DFSlockhandle','enq:SV-contention');其中，PAREMETER1的值為“name|mode”或“type|mode”的事件為隊(duì)列等待。在這類等待事件中，name代表隊(duì)列的名稱，type代表隊(duì)列的類型，mode代表隊(duì)列的模式。使用如下的SQL可以查詢到鎖的名稱和請求的mode值：SELECTCHR(BITAND(P1,-16777216)/16777215)||CHR(BITAND(P1,16711680)/65535)"LOCK",BITAND(P1,65535)"MODE"FROMV$SESSION_WAITWHEREEVENTIN('enq:SQ-contention','DFSlockhandle','enq:SV-contention');其中，MODE值見下表。模式代碼解釋1NullMode2Sub-Shoe3Sub-Exclusive4Share5Share/Sub-Exclusive6Exclusive使用如下的SQL可以查詢SQ和SV這兩種鎖的解釋：SELECT*FROMV$LOCK_TYPEDWHERED.TYPEIN('SV','SQ');事實(shí)上，Oracle為了管理序列使用了如下表所示的三種鎖。鎖rowcachelockSQ鎖(SequenceCache)SV鎖(SequenceOrdering)產(chǎn)生的條件NOCACHECACHE+NOORDER或CACHE+ORDER(單實(shí)例)CACHE+ORDER(RAC)擁有的鎖模式6-X(Exclusive)6-X(Exclusive)5-SSX(Share/Sub-Exclusive)表現(xiàn)出的等待事件rowcachelockenq：SQ-contentionOracle10g表現(xiàn)為DFSlockhandle，而Oracle11g中表現(xiàn)為enq：SV-contention簡介在賦予了NOCACHE屬性的序列上，在調(diào)用SEQUNECE.NEXTVAL過程中，將數(shù)據(jù)字典信息進(jìn)行物理修改時(shí)擁有該鎖，等待事件表現(xiàn)為rowcachelock賦予了CACHE屬性的序列調(diào)刖NEXTVAL期間，應(yīng)該以SSX模式獲得SQ鎖。若許多會話同時(shí)為了獲取SQ鎖而發(fā)生爭用，則等待enq：SQ-contention事件在RAC上節(jié)點(diǎn)之間順序得到保障的情況下，調(diào)用SEQUENCE.NEXTVAL期間擁有該鎖。在RAC環(huán)境中，賦予CACHE+ORDER屬性的序列上發(fā)生，在Oracle10g表現(xiàn)為DFSlockhandle，而在Oracle11g中表現(xiàn)為enq：SV-contention。解決辦法：盡量設(shè)置為NOORDER并增大其CACHE值參數(shù)含義P1代表V$ROWCACHE中的CACHE#P1可以查詢到鎖的名稱和請求的MODE值。P2值是序列的OBJECT_ID。因此，若利用P2值與DBA_OBJECTS的結(jié)合，就可以知道對哪個(gè)序列發(fā)生了等待現(xiàn)象P1可以查詢到鎖的名稱和請求的MODE值解決辦法盡量設(shè)置為NOORDER屬性并增大其CACHE值，一般情況下可以增大到1000

在RAC上創(chuàng)建序列時(shí)，在賦予了CACHE屬性的狀態(tài)下，若沒有賦予ORDER屬性，則各節(jié)點(diǎn)將會把不同范圍的序列值CACHE到內(nèi)存上。比如，在擁有兩個(gè)節(jié)點(diǎn)的RAC環(huán)境下，創(chuàng)建CACHE值為100的序列，則節(jié)點(diǎn)1使用1～100，節(jié)點(diǎn)2使用101～200。若兩個(gè)節(jié)點(diǎn)之間都通過遞增方式使用序列，則必須賦予ORDER屬性。Oracle序列默認(rèn)是NOORDER，如果設(shè)置為ORDER，那么在單實(shí)例環(huán)境沒有影響，在RAC環(huán)境中，多實(shí)例實(shí)際緩存相同的序列，此時(shí)在多個(gè)實(shí)例并發(fā)取該序列的時(shí)候，會有短暫的資源競爭來在多實(shí)例之間進(jìn)行同步。因此，性能相比NOORDER要差，所以RAC環(huán)境非必須的情況下不要使用ORDER，尤其要避免NOCACHEORDER組合。

有一點(diǎn)必須要注意，當(dāng)沒有賦予CACHE屬性時(shí)，不管ORDER屬性使用與否或RAC環(huán)境與否，一直等待rowcachelock事件。rowcachelock是可以在全局范圍內(nèi)使用的鎖，單實(shí)例環(huán)境或多實(shí)例環(huán)境同樣可以發(fā)生。如果使用了CACHE，而此時(shí)DB崩潰了，那么序列會從CACHE值之后重新開始，在CACHE中沒有使用的序列會被跳過，這樣就會導(dǎo)致序列不連續(xù)。在創(chuàng)建序列時(shí)，CACHE的默認(rèn)值設(shè)定為較小的20。因此創(chuàng)建并發(fā)量多的序列時(shí)，CACHE值應(yīng)該取1000以上的較大值。

另外，若一次性同時(shí)創(chuàng)建許多會話時(shí)，有時(shí)會發(fā)生enq：SQ-contention等待事件。其原因是V$SESSION.AUDSID列值是利用序列創(chuàng)建的。Oracle在創(chuàng)建新的會話后，利用名為SYS.AUDSES$的序列的NEXTVAL來創(chuàng)建AUDSID值。在Oracle10g下SYS.AUDSES$的CACHE值默認(rèn)為20，但在Oracle11g下SYS.AUDSES$的CACHE值默認(rèn)為10000，通過如下的SQL可以查詢：

SELECT*FROMDBA_SEQUENCESDWHERED.SEQUENCE_NAME='AUDSES$';

4.故障解決過程

首先查洵出現(xiàn)問題時(shí)間段的ASH視圖DBA_HIST_ACTIVE_SESS_HISTORY，通過該視圖可以找到需要的序列名稱?？梢杂卸喾N查詢方法：

SELECTD.SQL_ID,COUNT(1)FROMDBA_HISL_ACTIVE_SESS_HISTORYD

WHERED.SAMPLE_TIMEBETWEENTO_DATE('20160823170000','YYYYMMDDHH24MISS')AND

TO_DATE('20160823230000','YYYYMMDDHH24MISS')

ANDD.EVENT=enq:SQ-contention'GROUPBYD.SQL_ID;

可以看到SQL_ID為3jhvjgj7kbpmt的SQL最多，查看具體SQL內(nèi)容：

SELECT*FROMV$SQLAWHEREA.SQL_IDIN('3jhvjgj7kbpmt');

由此可以知道，產(chǎn)生等待的序列名稱為ONLNID，另外，也可以從DBA_HIST_ACTIVE_SESS_HISTORY視圖的P2值獲取到序列的名稱，SQL語句如下：

SELECTD.EVENT,D.P1TEXLD.P1,D.P2TEXLD.P2,

CHR(BITAND(P1,-16777216)/16777215)||

CHR(BITAND(P1,16711680)/65535)"Lock",

BITAND(P1,65535)"Mode",D.BLOCKlNG_SESSION,D.BLOCKING_SESSION_STATUS,D.BLOCKING_SESSION_SERIAL#,

D.SQL_ID,TO_CHAR(D.SAMPLE_TIME,'YYYYMMDDHH24MISS')SAMPLE_TIME,D.*

FROMDBA_HIST_ACTIVE_SESS_HISTORYD

WHERED.SAMPLE_TIMEBETWEENTO_DATE('20160823170000','YYYYMMDDHH24MISS')AND

TO_DATE('20160823230000','YYYYMMDDHH24MISS')

ANDD.EVENT='enq:SQ-contention';

由以上的查詢結(jié)果可知，序列的OBJECT_ID為47989，通過DBA_OBJECTS就可以查詢到序列的名稱了。另外，LOCK為SQ代表的是序列的CACHE鎖(SEQUENCECACHE)，MODE為6代表EXCLUSIVE排它鎖。

SELECT*FROMDBA_OBJECTSDWHERED.object_id='47989';

知道了序列名稱后，通過DBA_SEQUENCES視圖就可以查詢到序列的屬性了：

SELECT*FROMDBA_SEQUENCESDWHERED.sequence_name='ONLNID';

可以看到，該序列是NOORDER屬性，CACHE值為默認(rèn)的20，對于并發(fā)值很高的系統(tǒng)而言，該默認(rèn)值太低，所以需要調(diào)整到1000。可以執(zhí)行SQL語句“ALTERSEQUENCEONLNlDCACHE1000;”調(diào)整其CACHE值即可解決該問題。[考點(diǎn)]等待事件

17.

ROWID和ROWNUM有什么區(qū)別?正確答案：Oracle有兩個(gè)著名的偽列ROWID和ROWNUM，下面分別來介紹。

1.ROWID

ROWID是一個(gè)偽列，既然是偽列，那么這個(gè)列就不是用戶定義，而是系統(tǒng)自己給加上的。對每個(gè)表都有一個(gè)ROWID的偽列，但是表中并不物理存儲ROWID列的值。不過可以像使用其他列那樣使用它，但是不能刪除該列，也不能對該列的值進(jìn)行修改、插入。

ROWID對訪問一個(gè)表中的給定的行提供了最快的訪問方法，通過ROWID可以直接定位到相應(yīng)的數(shù)據(jù)塊上，然后將其讀到內(nèi)存。當(dāng)創(chuàng)建一個(gè)索引時(shí)，該索引不但存儲索引列的值，而且也存儲索引值所對應(yīng)的行的ROWID，這樣通過索引就可以快速找到相應(yīng)行的ROWID，通過該ROWID，就可以迅速將數(shù)據(jù)查詢出來。這也就是在使用索引查詢時(shí)，速度比較快的原因。

一般來說，當(dāng)表中的行確定后，ROWID就不會發(fā)生變化，一旦一行數(shù)據(jù)插入數(shù)據(jù)庫，ROWID在該行的生命周期內(nèi)是唯一的，即使該行產(chǎn)生行遷移，行的ROWID也不會改變，UPDATE不會改變ROWID，INSERT更不會。從ROWID定義可知，只有當(dāng)數(shù)據(jù)行的物理位置改變時(shí)才會導(dǎo)致ROWID改變，所以，只需要關(guān)心那些會導(dǎo)致數(shù)據(jù)物理位置變化的操作即可。

ROWID可以分為以下幾種類型：

1)物理ROWID：存儲堆組織表、表簇、表分區(qū)和索引分區(qū)中的行地址。

2)邏輯ROWID：存儲索引組織表中的行地址。

3)外部ROWID：是外來表(如通過網(wǎng)關(guān)訪問的DB2表)中的標(biāo)識符。它們不是標(biāo)準(zhǔn)的Oracle數(shù)據(jù)庫ROWID。

有一種數(shù)據(jù)類型稱為通用ROWID或UROWID，支持各種ROWID。

當(dāng)如下情況發(fā)生時(shí)，ROWID將發(fā)生改變，即當(dāng)數(shù)據(jù)遷移到其他塊的時(shí)候，ROWID就會改變：

1)對一個(gè)表做表空間的移動或重建后。

2)對一個(gè)表進(jìn)行了exp/imp或expdp/impdp后。

3)MOVE、FLASHBACKTABLE、修改分區(qū)鍵值到另一個(gè)分區(qū)、分區(qū)表的分區(qū)數(shù)據(jù)轉(zhuǎn)移到其他分區(qū)、SHRINKTABLE等。

通過DBMS_ROWID可以獲取文件號、塊號等信息，如下：

2.ROWNUM

ROWNUM是一個(gè)偽列，不是真正的列，在表中并不真實(shí)存在，它是Oracle數(shù)據(jù)庫從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序。切勿理解成記錄的行號(這是很多人一直這樣認(rèn)為的)，例如想查詢第二行記錄，按下面的方法是查詢不到的：

SELECT*FROMSCOTT.TABLE_LHRWHEREROWNUM=2;

ROWNUM主要應(yīng)用于Top-N查詢中。[考點(diǎn)]性能診斷

18.

Oracle健康檢查有哪些方面?正確答案：要想對數(shù)據(jù)庫進(jìn)行全面檢查，內(nèi)容比較多，下面列舉部分檢查項(xiàng)目：

1)數(shù)據(jù)庫的實(shí)例是否運(yùn)行，最近是否有自動重啟現(xiàn)象。

2)ASM實(shí)例是否正常運(yùn)行，剩余ASM磁盤空間有多大。

3)數(shù)據(jù)庫的參數(shù)是否正常，數(shù)據(jù)庫的參數(shù)近期是否被修改過。

4)數(shù)據(jù)庫的表空間大小，是否有表空間快滿了，表空間增長是否過快(系統(tǒng)表空間是否增長過快)。

5)是否有業(yè)務(wù)表創(chuàng)建在了SYSTEM表空間上，審計(jì)表是否在SYSTEM表空間上。

6)RMAN備份是否過期，備份是否可用，是否有控制文件的備份。

7)數(shù)據(jù)庫JOB是否有運(yùn)行錯(cuò)誤。

8)數(shù)據(jù)庫的告警日志是否有異常告警，例如ORA-4030、ORA-4031、ORA-60、ORA-600、ORA-01555等。

9)數(shù)據(jù)庫歸檔空間、閃回恢復(fù)區(qū)是否足夠。

10)是否有非常耗費(fèi)資源的SQL曾經(jīng)運(yùn)行過，系統(tǒng)是否有VERSIONCOUNT過高的SQL。

11)DG、OGG是否運(yùn)行正常，歸檔日志是否正常傳遞到TARGET端。

12)數(shù)據(jù)庫是否開啟了審計(jì)?

13)數(shù)據(jù)庫有哪些普通索引、分區(qū)索引是失效的，系統(tǒng)是否有很大的索引從未使用過。

14)系統(tǒng)有哪些大表沒有進(jìn)行分區(qū)，哪些分區(qū)表的分區(qū)數(shù)過多，哪些分區(qū)表的各分區(qū)大小嚴(yán)重不均勻。

15)系統(tǒng)有哪些外鍵沒有創(chuàng)建索引，系統(tǒng)組合索引列個(gè)數(shù)過多。

16)系統(tǒng)有哪些表使用了過時(shí)字段，例如LONG、CHAR。

17)系統(tǒng)有哪些表上創(chuàng)建的索引數(shù)過多。

18)系統(tǒng)擁有DBA角色的用戶是否有變動。

19)近期是否有用戶頻繁使用錯(cuò)誤密碼進(jìn)行登錄系統(tǒng)。

20)表或索引是否含有很高的并行度。

21)系統(tǒng)是否含有無效的觸發(fā)器。

22)系統(tǒng)是否含有CACHE值小于20并且已經(jīng)產(chǎn)生等待的序列。

23)系統(tǒng)近期是否含有異常的等待事件。

24)系統(tǒng)是否含有登錄時(shí)間很長卻沒有響應(yīng)的會話。

25)系統(tǒng)是否含有統(tǒng)計(jì)信息過舊或從未收集過統(tǒng)計(jì)信息的表和索引。

26)系統(tǒng)有哪些全局臨時(shí)表被收集了統(tǒng)計(jì)信息。

27)系統(tǒng)自動收集統(tǒng)計(jì)信息的JOB是否被禁用。

28)系統(tǒng)AWR、ASH功能是否被禁用。[考點(diǎn)]性能診斷

19.

在高并發(fā)、高負(fù)載的情況下，如何給表添加字段并設(shè)置DEFAULT值?正確答案：當(dāng)Oracle表數(shù)據(jù)量上億時(shí)，對表執(zhí)行“ALTERTABLEXXXADDCOLUMN_XXVARCHAR2(2)DEFAULT'XXXt';”操作時(shí)，效率及安全性是必須要考慮的因素。若直接執(zhí)行，則會在該過程中給表加上6級表鎖，也就是連查詢都需要等待，這在生產(chǎn)庫上是相當(dāng)危險(xiǎn)的操作。因?yàn)镺racle在執(zhí)行上述操作過程中，不僅要更新數(shù)據(jù)字典，還會刷新全部的記錄，并且會使得Undo表空間暴漲，所以，正確的做法是將更新數(shù)據(jù)字典和更新字段值分開。

例如，表LKILL.T_KILL約有4500W的數(shù)據(jù)，直接添加一個(gè)字段C_LHR需要花費(fèi)21min，如下：

修改為如下的方式，可以顯著提高這個(gè)操作的性能，但表中原有的記錄對于新添加的列為空，新增記錄默認(rèn)值會設(shè)置為LHR，那么原有記錄的默認(rèn)值就需要在系統(tǒng)空閑的時(shí)候進(jìn)行批量更新、批量提交或采用系統(tǒng)包DBMS_PARALLEL_EXECUTE來更新，這樣不至于大批量鎖表。

[考點(diǎn)]性能診斷

20.

你們公司的數(shù)據(jù)庫有多大?大一點(diǎn)的表有多大?有多少行?正確答案：對于數(shù)據(jù)庫的大小，需要注意的問題是數(shù)據(jù)庫的大小不能以表空間的分配大小而論，而應(yīng)該以表空間的占用空間大小而論，并且需要減掉SYSTEM、SYSAUX、TEMP和Undo這些表空間占用的空間。因?yàn)橛械南到y(tǒng)Undo空間可能分配得很大，比如500GB，所以，計(jì)算數(shù)據(jù)庫大小的時(shí)候應(yīng)該排除這些表空間。

對于表空間大小，若有如下的結(jié)果：

可以說數(shù)據(jù)庫大約有(2205-751-629-14-2)MB=809MB，而并非是2.2GB。

至于大一點(diǎn)的表有多大?有多少行?這個(gè)問題考查DBA對自己庫的熟悉程度，通過下面的SQL語句可以查詢一個(gè)表的大?。?/p>

可以看到最大的表是LKILL用戶下的T_KILL表，大約7GB，約有4400W條的數(shù)據(jù)量，讀者應(yīng)該以自己實(shí)際管理的庫為準(zhǔn)。[考點(diǎn)]性能診斷

21.

如何監(jiān)控?cái)?shù)據(jù)庫中的非常耗費(fèi)性能SQL語句?正確答案：由于V$SQL_MONITOR和VSSQL_PLAN_MONITOR收集的信息每秒刷新一次，接近實(shí)時(shí)。當(dāng)SQL執(zhí)行完畢，信息并不會立即從V$SQL_MONITOR中刪除，至少會保留1min。所以，根據(jù)這兩個(gè)視圖的數(shù)據(jù)來源及保留策略，可以寫一個(gè)輕量級的JOB來針對性地監(jiān)控這兩個(gè)視圖，從而可以實(shí)現(xiàn)：執(zhí)行時(shí)間超過N小時(shí)、笛卡兒積SQL監(jiān)控、分區(qū)表全分區(qū)掃描、SQL執(zhí)行次數(shù)、解析次數(shù)過大、占用CPU過大等類型的SQL監(jiān)控。至于消耗小于5s的CPU或I/O時(shí)間的SQL語句一般都是非常高效的，所以不用監(jiān)控。

考慮到定時(shí)任務(wù)對Oracle數(shù)據(jù)庫性能的影響，所以，可以通過Oracle的輕量級JOB來實(shí)現(xiàn)。Oracle的JOB分為一般性的JOB和輕量級的JOB(LightweightJobs)。使用輕量級的JOB可以提高JOB的性能，因?yàn)檩p量級JOB適合于在短時(shí)間內(nèi)執(zhí)行非常頻繁的JOB。

若想直接查詢數(shù)據(jù)庫耗費(fèi)性能的SQL語句，可以直接使用視圖VW_SQL_PP_LHR進(jìn)行查詢。若想查詢歷史記錄，則可以通過表XB_SQL_MONITOR_PP_LHR來查詢。另外，對于監(jiān)控中