電腦基礎(chǔ)知識(shí)02-04-應(yīng)用優(yōu)化的其他方法-60-頁(yè)

OracleRDBMS

大型應(yīng)用優(yōu)化其他方法

麥思博(北京)軟件科技有限公司2011.102025/1/211內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/212Oracle臨時(shí)表臨時(shí)表的目的：應(yīng)用中的有的數(shù)據(jù)只是臨時(shí)的存儲(chǔ)，使用后不再使用；永久表要存放在表中，給磁盤(pán)空間帶來(lái)碎片；臨時(shí)比變量更有效，可以用SQL語(yǔ)句。Oracle允許在會(huì)話或事務(wù)其間創(chuàng)建臨時(shí)表，以保存會(huì)話或事務(wù)的數(shù)據(jù)：臨時(shí)表就是內(nèi)存表，它不保存到表空間上；臨時(shí)表的數(shù)據(jù)僅在會(huì)話其間有效；用CREATEGLOBALTEMPORARY完成臨表的創(chuàng)建；創(chuàng)建臨時(shí)表可指定事務(wù)級(jí)后會(huì)話級(jí)有效；不專(zhuān)門(mén)指定，則在會(huì)話結(jié)束后才釋放；對(duì)臨時(shí)表做DML操作不產(chǎn)生重做日志(REDOLOG)。2025/1/213Oracle臨時(shí)表-語(yǔ)法createglobaltemporarytabletable_name[tabledefinition]oncommitdeleterows;在提交后刪除行數(shù)據(jù)：臨時(shí)表可在提交后刪除，語(yǔ)法為：為會(huì)話保持?jǐn)?shù)據(jù)：臨時(shí)表可為會(huì)話保持?jǐn)?shù)據(jù)，語(yǔ)法為：createglobaltemporarytabletable_name[tabledefinition]oncommitpreserverows;2025/1/214Oracle臨時(shí)表-例子SQL>createglobaltemporarytablemyempasselect*fromemp;表已創(chuàng)建。

SQL>select*frommyemp;未選定行

SQL>insertintomyempvalues('趙元杰',32456.99,10,'12');已創(chuàng)建1行。

SQL>select*frommyemp;ENAMESALDEPTNOTEL-------------------------------------------------------趙元杰32456.991012

SQL>connectsys/sys已連接。SQL>connectzhao/zhao已連接。SQL>select*frommyemp;未選定行創(chuàng)建臨時(shí)表：帶GLOBALTEMPORARY創(chuàng)建；會(huì)話結(jié)束自動(dòng)清除：2025/1/215內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/216Logging&Nologging什么是redo當(dāng)塊改變時(shí)，Oracle記錄改變向量，并發(fā)將這些存放SGA日志緩沖區(qū)由LGWR寫(xiě)到日志文件中簡(jiǎn)單說(shuō)：Redo=Transactions2025/1/217Logging&Nologging何時(shí)設(shè)置為NOLOGGING

下面情況可將表設(shè)置為NOLOGGINGDIRECTLOAD(SQL*Loader)DIRECTLOADINSERT(usingAPPENDhint)CREATETABLE...ASSELECTCREATEINDEXALTERTABLEMOVEALTERTABLE...MOVEPARTITIONALTERTABLE...SPLITPARTITIONALTERTABLE…ADDPARTITION(ifHASHpartition)2025/1/218Logging&Nologging下面情況可將表設(shè)置為NOLOGGINGALTERTABLE…MERGEPARTITIONALTERTABLE…MODIFYPARTITIONADDSUBPARTITONCOALESCESUBPARTITONREBUILDUNUSABLEINDEXESALTERINDEX...SPLITPARTITIONALTERINDEX...REBUILDALTERINDEX...REBUILDPARTITION2025/1/219Logging&Nologging設(shè)置索引為NOLOGGING可加快處理速度ALTERTABLE…NOLOGGING;INSERT/*+nologging*/…下面情況可采用NOLOGGING--先設(shè)置為NOLOGGINGSQL>ALTERINDEXnew_indexNOLOGGING;SQL>ALTERINDEXnew_indexREBUILD;--當(dāng)暫時(shí)將表或索引設(shè)置成NOLOGGING時(shí)，別忘了再將它們?cè)O(shè)置loggingselect'altertable'||table_name||'logging;'fromuser_tableswheretable_namelike'%STATS%';

select'alterindex'||index_name||'logging;'fromuser_indexes;2025/1/2110Logging&Nologging創(chuàng)建表可指定NOLOGGING

--創(chuàng)建時(shí)設(shè)置為NOLOGGINGSQL>CREATE表指定NOLOGGING后，下面語(yǔ)句不產(chǎn)生日志：SQL>INSERTINTOnew_table_nolog_test...SQL>UPDATEnew_table_nolog_testSET...SQL>DELETEFROMnew_table_nolog_test...SQL>INSERTINTOnew_table_nolog_test...SQL>UPDATEnew_table_nolog_testSET...SQL>DELETEFROMnew_table_nolog_test...--INSERT/*+APPEND+/…ALTERTABLEnew_table_nolog_testMOVE…ALTERTABLEnew_table_nolog_testMOVEPARTITION…--創(chuàng)建時(shí)設(shè)置為NOLOGGINGSQL>CREATETABLEnew_table_nolog_testNOLOGGING(….);2025/1/2111NOLOGGING特殊的創(chuàng)建表可指定NOLOGGING限制寫(xiě)日志修改不可恢復(fù)修改不能傳遞到備用庫(kù)謹(jǐn)慎使用NOLOGGING處于主要的生產(chǎn)數(shù)據(jù)庫(kù)不要設(shè)置NOLOGGING經(jīng)常被其他數(shù)據(jù)庫(kù)讀取的數(shù)據(jù)庫(kù)不要設(shè)置NOLOGGING千萬(wàn)不能dataguard上用（如果已經(jīng)做了database

forcelogging就沒(méi)有事)2025/1/2112NOLOGGING用提示使用NOLOGGINGinsert/*+append+/intomytablevalues（1，‘a(chǎn)lan’）；

Oracle歸檔模式下當(dāng)表為logging狀態(tài)時(shí)，append/noappend都生成redo當(dāng)表為nologging狀態(tài)時(shí)，只有append不生成redoOracle非歸檔模式下表在logging/nologing下:append模式都不生成redo，表在logging/nologing下:noappend模式下都生成redo2025/1/2113在提示中使用NOLOGGING可在提示中使用NOLOGGING可提示使用NOLOGGING和并行：insert/*+APPENDNOLOGGINGPARALLEL(table_name,4)*/intotable_nameselect/*+PARALLEL(table_name,4)*/fromtable_name@dblink;2025/1/2114數(shù)據(jù)庫(kù)級(jí)取消NOLOGGING從9.2開(kāi)始，可將數(shù)據(jù)庫(kù)或表空間設(shè)置為L(zhǎng)OGGING(取消NOLOGGING)數(shù)據(jù)庫(kù)級(jí)取消(強(qiáng)制產(chǎn)生日志):表空間級(jí)取消(強(qiáng)制產(chǎn)生日志):將數(shù)據(jù)庫(kù)或表空間設(shè)置為L(zhǎng)OGGING所有的NOLOGGING將無(wú)效ALTERDATABASEFORCELOGGING;

ALTERDATABASENOFORCELOGGING;ALTERTABLESPACEtablespace_nameFORCELOGGING;

ALTERTABLESPACEtablespace_nameNOFORCELOGGING;2025/1/2115內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/2116Oracle熱塊(hotblock)hotblock指的是同時(shí)有若干個(gè)session，同時(shí)對(duì)一個(gè)block進(jìn)行操作，導(dǎo)致此block比較忙；導(dǎo)致block比較忙的原因是：Oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)存放的最小單位就是block，數(shù)據(jù)每次讀取數(shù)據(jù)的時(shí)候只能以block為單位；如果有多條記錄存放在一個(gè)block上的話，那么導(dǎo)致多個(gè)session訪問(wèn)同一個(gè)block的可能性就越大；2025/1/2117Oracle熱塊(hotblock)hotblock解決方法：1、增大databuffer；2、增加freelist，減小pctused(將一個(gè)block上可以使用的空間減少)，引起block數(shù)據(jù)存放的較少，可以提高應(yīng)用的訪問(wèn)并發(fā)率，減少hotblock的產(chǎn)生；3、增加回滾段數(shù)目，增大initrans，考慮使用LMT,確認(rèn)是不是由于熱點(diǎn)塊造成(如果是可以用反轉(zhuǎn)索引,或者用更小塊大小)；4、可以建立block較小的表空間，將熱點(diǎn)對(duì)象移動(dòng)到此表空間上去；5、優(yōu)化應(yīng)用，優(yōu)化索引，提高索引的命中率；2025/1/2118Oracle熱塊(hotblock)Hotblock診斷

熱塊與“bufferbusywaits”等待事件有關(guān)1)查詢(xún)V$session_wait視圖：2)如果上面塊在忙等待，則查詢(xún)這一塊的名稱(chēng)和類(lèi)型Selectp1"File#",p2"Block#",p3"ReasonCode"fromv$session_waitwhereevent='bufferbusywaits';Selectowner,segment_name,segment_typeFromdba_extentswherefile_id=&P1and&P2betweenblock_id

andblock_id+blocks-1;2025/1/2119Oracle熱塊(hotblock)Hotblock診斷(續(xù))熱塊一旦被確定，則使用v$segment_statistics性能視圖促使塊水平統(tǒng)計(jì)的實(shí)時(shí)監(jiān)控。這一過(guò)程使得DBA識(shí)別與獨(dú)立列表與索引有關(guān)的問(wèn)題也可以查詢(xún)dba_data_files以確定卷入等待的文件的file_name，方法是使用v$session_wait中的P1查詢(xún)V$SEGMENT_STATISTICS視圖SELECTowner,object_type,object_name,valueFROMV$SEGMENT_STATISTICSWHERE(statistic_namelike'%read%‘ORstatistic_namelike'%write%')ORDERBYvaluedesc2025/1/2120內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/212122/90索引碎片分析當(dāng)表的數(shù)據(jù)不斷被刪除后，與之相關(guān)的索引仍然保留那么這些無(wú)用的索引數(shù)據(jù)項(xiàng)是多少呢？Analyzeindex…validatestructure檢查索引塊沖突校驗(yàn)索引的大小結(jié)果放入index_stats

表中，并生成有關(guān)索引的詳細(xì)視圖索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題2025/1/2122ANALYZEINDEX…validatestructure命令SELECTDEL_LF_ROWS/LF_ROWS“Wasted”FROMINDEX_STATS如果浪費(fèi)數(shù)值“Wasted”>0.2

或索引節(jié)點(diǎn)利用率<60%(PCT_USED<60)索引有效性分析SQL>ANALYZEINDEX&index_nameVALIDATESTRUCTURE;colnameheading'IndexName'formata30col

del_lf_rowsheading'Deleted|LeafRows'format99999999col

lf_rows_usedheading'Used|LeafRows'format99999999colratioheading'%Deleted|LeafRows'format999.99999--SELECTname,del_lf_rows,lf_rows-del_lf_rows

lf_rows_used,to_char(del_lf_rows/(lf_rows)*100,'999.99999')ratioFROMindex_statswherename=upper('&index_name');--當(dāng)刪除的比率(ratio)大于15-20%時(shí)，需要對(duì)索引進(jìn)行重建2025/1/212310g/11g基于CBO模式，這種模式需要訪問(wèn)數(shù)據(jù)字典統(tǒng)計(jì)數(shù)據(jù)；表/索引統(tǒng)計(jì)要盡量及時(shí)，準(zhǔn)確當(dāng)統(tǒng)計(jì)數(shù)據(jù)不要太舊，誤導(dǎo)優(yōu)化器當(dāng)統(tǒng)計(jì)數(shù)據(jù)太舊，要及時(shí)重新搜集!統(tǒng)計(jì)數(shù)據(jù)過(guò)時(shí)問(wèn)題2025/1/2124內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/2125DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)：

表示conference磁盤(pán)讀取的塊數(shù)量；通常發(fā)生在全表掃描（FullTableScan）和快速全索引掃描（FastFullIndexScan）時(shí)

盡管這個(gè)參數(shù)已經(jīng)由DBA設(shè)置，但開(kāi)發(fā)人員可根據(jù)目前的具體需要在會(huì)話設(shè)置，如：DB_FILE_MULTIBLOCK_READ_COUNTSQL>connectscott/tiger@orclSQL>altersessionsetDB_FILE_MULTIBLOCK_READ_COUNT=16;2025/1/2126DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)的大小探測(cè)方法：

大小要根據(jù)當(dāng)前OS運(yùn)行的I/O能力來(lái)設(shè)置如何確定這個(gè)參數(shù)的呢？，只有手工辦法：1.查詢(xún)Oracle塊大小和會(huì)話級(jí)設(shè)置：DB_FILE_MULTIBLOCK_READ_COUNT--目前8i的db_file_multiblock_read_count參數(shù)為：db_file_multiblock_read_count8db_block_sizeinteger8192--修改db_file_multiblock_read_count參數(shù)前要進(jìn)行下面的測(cè)試--以確定db_file_multiblock_read_count參數(shù)的大小SQL>settimingonSQL>setautotraceon--1.設(shè)置會(huì)話級(jí)參數(shù)：altersessionsetdb_file_multiblock_read_count=32;2025/1/2127DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)的大小探測(cè)方法：

2.設(shè)置會(huì)話跟蹤和做一個(gè)實(shí)際查詢(xún)操作：DB_FILE_MULTIBLOCK_READ_COUNT--2.設(shè)置會(huì)話跟蹤：altersessionsetevents'10046tracenamecontextforever,level10';--altersessionsetevents'10046tracenamecontextforever,level12';--3.全表掃描測(cè)試：

select/*+full(a)*/count(*)frombaan.TTFGLD416888a;--4.取消會(huì)話跟蹤：altersessionsetevents'10046tracenamecontextoff';2025/1/2128DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)的大小探測(cè)方法：

3.分析會(huì)話跟蹤數(shù)據(jù)-看是否能采用這個(gè)參數(shù)：DB_FILE_MULTIBLOCK_READ_COUNT--5.檢查跟蹤文件：SQL>showparameterUSER_DUMP_DEST$文件名為：baanivc4_ora_18052.trc/oracle/admin817/baanIVc4/udump/baanivc4_ora_18052.trcOracle8iEnterpriseEditionRelease8.1.7.0.0-64bitProductionWiththePartitioningoptionJServerRelease8.1.7.0.0-64bitProductionORACLE_HOME=/oracle/817Systemname: SunOSNodename: BCA-ERPRelease: 5.8Version: Generic_108528-16Machine: sun4uInstancename:baanIVc4Redothreadmountedbythisinstance:1Oracleprocessnumber:11Unixprocesspid:18052,image:oracle@BCA-ERP(TNSV1-V3)2025/1/2129DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)的大小探測(cè)方法：

3.分析會(huì)話跟蹤數(shù)據(jù)-看是否能采用這個(gè)參數(shù)(續(xù))：DB_FILE_MULTIBLOCK_READ_COUNT--5.檢查跟蹤文件：***2010-01-1009:34:08.389WAIT#0:nam='SQL*Netmessagefromclient'ela=0p1=1413697536p2=1p3=0=====================PARSINGINCURSOR#1len=56dep=0uid=0oct=3lid=0tim=0hv=1986181661ad='bdea11e8'select/*+full(a)*/count(*)frombaan.TTFGLD416888aENDOFSTMTPARSE#1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=0EXEC#1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0WAIT#1:nam='SQL*Netmessagetoclient'ela=0p1=1413697536p2=1p3=0……WAIT#1:nam='fileopen'ela=0p1=0p2=0p3=0WAIT#1:nam='dbfilescatteredread'ela=0p1=9p2=61562p3=30WAIT#1:nam='dbfilescatteredread'ela=0p1=13p2=1093577p3=13WAIT#1:nam='dbfilescatteredread'ela=0p1=13p2=1093591p3=31WAIT#1:nam='dbfilescatteredread'ela=0p1=16p2=854824p3=32WAIT#1:nam='dbfilescatteredread'ela=0p1=16p2=854856p3=32WAIT#1:nam='dbfilesequentialread'ela=0p1=16p2=854888p3=1WAIT#1:nam='dbfilescatteredread'ela=0p1=17p2=3025147p3=32WAIT#1:nam='dbfilescatteredread'ela=0p1=17p2=3025179p3=32表示系統(tǒng)使用過(guò)32多塊讀2025/1/2130db_file_multiblock_read_count參數(shù)調(diào)整：

10g版本為自動(dòng)調(diào)整(不設(shè)置就表示自動(dòng)管理)默認(rèn)為I/O可執(zhí)行的最大值與平臺(tái)有關(guān)，不能超過(guò)緩沖區(qū)的10%通常相當(dāng)于1MB對(duì)數(shù)據(jù)倉(cāng)庫(kù)/DSS可能不合適DB_FILE_MULTIBLOCK_READ_COUNT2025/1/2131DB_FILE_MULTIBLOCK_READ_COUNTOracle10gR2版本與這個(gè)參數(shù)相關(guān)的功能有：V$PROCESS_MEMORY輔助監(jiān)視自動(dòng)PGA管理與確定PGA_AGGREGATE_TARGET參數(shù)的最佳設(shè)置如果沒(méi)有明確設(shè)置DB_FILE_MULTIBLOCK_READ_COUNT參數(shù)，則表示為自動(dòng)調(diào)整大小與平臺(tái)有關(guān)，但文檔經(jīng)常說(shuō)明為1MB

SYS@orcl>selectisdefault,description2fromv$parameter3wherename='db_file_multiblock_read_count';ISDEFAULTDESCRIPTION----------------------------------------FALSEdbblocktobereadeachIO2025/1/2132內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/2133Optimizer_index_cost_adj參數(shù)：

用來(lái)調(diào)整“基于代價(jià)的優(yōu)化”相對(duì)于全表掃描訪問(wèn)代表一個(gè)百分比,取值范圍在1到10000之間；可理解為Oracle執(zhí)行多塊（MultiBlock）I/O（比如全表掃描）的代價(jià)與執(zhí)行單塊（Single-block）I/O代價(jià)的相對(duì)比例缺省值100：表示索引掃描成本等價(jià)轉(zhuǎn)換與全表掃描成本若減小，則CBO傾向于使用索引（即單塊I/O），反之則傾向于全表掃描（多塊I/O）OPTIMIZER_INDEX_COST_ADJ2025/1/2134Optimizer_index_cost_adj參數(shù)調(diào)整：

多數(shù)OLTP系統(tǒng)：OPTIMIZER_INDEX_COST_ADJ可以設(shè)置在10到50之間對(duì)于數(shù)據(jù)倉(cāng)庫(kù)和DSS系統(tǒng)，需要反復(fù)調(diào)整取得一個(gè)合理值

更為具體的可以根據(jù)統(tǒng)計(jì)信息,dbfilescatteredreads/dbfilesequentialreads來(lái)計(jì)算OPTIMIZER_INDEX_COST_ADJ2025/1/2135索引與多塊讀取數(shù)量select*fromt2wheren1=45;--表掃描代價(jià)15altersessionsetDB_FILE_MULTIBLOCK_READ_COUNT=4;SELECTSTATEMENTOptimizer=CHOOSE(Cost=16Card=15)TABLEACCESS(BYINDEXROWID)OF'T2'(Cost=16Card=15)INDEX(RANGESCAN)OF'T_I2'(NON-UNIQUE)(Cost=1Card=15)(表掃描代價(jià)23)altersessionsetDB_FILE_MULTIBLOCK_READ_COUNT=16;SELECTSTATEMENTOptimizer=CHOOSE(Cost=10Card=15)TABLEACCESS(FULL)OF'T2'(Cost=10Card=15)2025/1/2136索引與單塊讀取調(diào)整select*fromt2wheren1=45; --indexaccesscostwas16altersessionsetOPTIMIZER_INDEX_COST_ADJ=50;SELECTSTATEMENTOptimizer=CHOOSE(Cost=8Card=15)TABLEACCESS(BYINDEXROWID)OF'T2'(Cost=8Card=15)INDEX(RANGESCAN)OF'T_I2'(NON-UNIQUE)(Cost=1Card=15)altersessionsetOPTIMIZER_INDEX_COST_ADJ=25;SELECTSTATEMENTOptimizer=CHOOSE(Cost=4Card=15)TABLEACCESS(BYINDEXROWID)OF'T2'(Cost=4Card=15)INDEX(RANGESCAN)OF'T_I2'(NON-UNIQUE)(Cost=1Card=15)2025/1/2137OPTIMIZER_INDEX_CACHING參數(shù)：

告訴Oracle

索引在內(nèi)存的數(shù)據(jù)緩沖區(qū)中的可能性有多大，即向優(yōu)化器暗示位于Oracle數(shù)據(jù)緩沖區(qū)的索引的數(shù)量參數(shù)設(shè)置將會(huì)影響到“基于代價(jià)的優(yōu)化”做出的對(duì)一個(gè)表連接（嵌套循環(huán)）使用索引還是使用全表掃描選擇參數(shù)optimizer_index_caching是一個(gè)百分比參數(shù)，有效取值范圍是0到100OPTIMIZER_INDEX_CACHING2025/1/21382025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能39/90OPTIMIZER_INDEX_CACHING參數(shù)調(diào)整：

這個(gè)參數(shù)使得我們能夠調(diào)節(jié)“基于代價(jià)的SQL優(yōu)化器”的行為，從而選擇最好的方式來(lái)訪問(wèn)想要的SQL查詢(xún)結(jié)果：嵌套循環(huán)連接哈希連接訪問(wèn)全索引掃描全表掃描訪問(wèn)

optimizer_index_caching設(shè)置為較大的百分比，可以使嵌套循環(huán)連接在優(yōu)化器看起來(lái)更便宜，從而優(yōu)化器更有可能選擇進(jìn)行嵌套循環(huán)連接而不是哈希連接或者歸并排序連接OPTIMIZER_INDEX_CACHING2025/1/21392025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能40/90Oracle9i/10g/11g的變化：從Oracle9i開(kāi)始，運(yùn)行配置多種塊大?。簞?chuàng)建表空間的塊大小為2K,4K,8K,16K和32K并使用相似大小的表和索引來(lái)匹配表空間我們應(yīng)該理解這些新變化：大數(shù)據(jù)塊的輸入輸出操作的邊緣開(kāi)銷(xiāo)可忽略不計(jì)取一個(gè)32K大小的數(shù)據(jù)塊的開(kāi)銷(xiāo)僅比取一個(gè)2K大小的數(shù)據(jù)塊的開(kāi)銷(xiāo)多1％，因?yàn)?9％的磁盤(pán)輸入輸出操作都是用來(lái)把磁盤(pán)讀寫(xiě)頭的移動(dòng)到相應(yīng)的磁盤(pán)柱面和軌道OPTIMIZER_INDEX_CACHING2025/1/21402025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能41/90索引與大塊的表空間：當(dāng)索引被存儲(chǔ)在大塊表空間中時(shí)使用起來(lái)更有效。更深層的原因是：B-樹(shù)可以更好地被平衡并且對(duì)順序索引節(jié)點(diǎn)訪問(wèn)而言有更小的磁盤(pán)開(kāi)銷(xiāo)著名作家RobinSchumacher的研究表明32K塊大小的Oracle索引在執(zhí)行索引范圍掃描時(shí)需要更少的邏輯輸入輸出，并且性能更高OPTIMIZER_INDEX_CACHING2025/1/21412025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能42/90創(chuàng)建索引緩沖：可在數(shù)據(jù)庫(kù)處于活動(dòng)狀態(tài)創(chuàng)建索引緩沖首先，把所有索引都移至某個(gè)單獨(dú)的表空間，定義成一個(gè)單獨(dú)的數(shù)據(jù)緩沖區(qū)然后將參數(shù)optimizer_index_caching設(shè)置成正確的值分配一個(gè)32K的緩沖區(qū)空間altersystemsetdb_32k_cache_size=100m;創(chuàng)建塊大小32K的表空間createtablespaceindex_ts_32k…blocksize32k;OPTIMIZER_INDEX_CACHING2025/1/21422025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能43/90創(chuàng)建索引緩沖(續(xù))：把所有索引移至這個(gè)32K的表空間中該命令把所有索引移至這個(gè)32K的表空間而無(wú)需中斷當(dāng)前存在的索引查詢(xún)。它把索引重建成臨時(shí)段，并確保新索引可用后在刪除舊索引alterindexcust_idxrebuildonlinetablespaceindex_ts_32k;OPTIMIZER_INDEX_CACHING2025/1/21432025/1/21Oracle數(shù)據(jù)庫(kù)設(shè)計(jì)與性能44/90創(chuàng)建索引緩沖(續(xù))：既然所有索引已經(jīng)被隔離到一個(gè)單獨(dú)的表空間和索引緩沖區(qū)中，我們就可以執(zhí)行數(shù)據(jù)字典腳本來(lái)預(yù)見(jiàn)相對(duì)精確度，即我們期望在內(nèi)存索引緩沖區(qū)中看到的索引的數(shù)量：OPTIMIZER_INDEX_CACHINGSelectvalue-blocksoptimizer_index_cachingFromv$parameterp,

dba_segmentsswherename='db_32k_cache_size'Andtablespace_name=INDEX_TS_32K';2025/1/2144OPTIMIZER_INDEX_CACHING創(chuàng)建索引緩沖與嵌套連接：只有采用(USE_NL)時(shí)才使用設(shè)置OPTIMIZER_INDEX_CACHING

參數(shù)，如：SQL>altersessionsetoptimizer_index_caching=90;

Sessionaltered.SQL>showparameteroptimizer_index_caching

NAMETYPEVALUE---------------------------------------------------------------------------optimizer_index_cachinginteger902025/1/2145SKIP_UNUSABLE_INDEXESskip_unusable_indexes參數(shù)：在會(huì)話中設(shè)置，表示要跳過(guò)無(wú)效的索引

在EXP中表示要跳過(guò)無(wú)效的索引(不導(dǎo)出)在IMP中表示要跳過(guò)無(wú)效的索引(不維護(hù))索引為什么無(wú)效：當(dāng)將表從一個(gè)表空間遷移到另外新表空間后，原來(lái)的索引自動(dòng)變?yōu)闊o(wú)效

可用alterindexidxtunusable;命令使索引失效無(wú)效的索引的狀態(tài)在user_indexes.status列的值為“UNUSABLE”2025/1/21462025/1/2147/90SKIP_UNUSABLE_INDEXES索引無(wú)效(UNUSABLE

)的處理：可設(shè)置skip_unusable_indexes跳過(guò)失效的索引這個(gè)參數(shù)可在系統(tǒng)或會(huì)話級(jí)上設(shè)置，如：SQL>select*fromawhereid=1;select*fromawhereid=1*第1行出現(xiàn)錯(cuò)誤:ORA-01502:索引'TEST.IDX_A_ID'或這類(lèi)索引的分區(qū)處于不可用狀態(tài)SQL>altersystemsetskip_unusable_indexes=truescope=memory;系統(tǒng)已更改。SQL>select*fromawhereid=1;2025/1/2147SKIP_UNUSABLE_INDEXES索引無(wú)效處理建議：經(jīng)常檢查關(guān)鍵索引是否失效直接查詢(xún)dba_indexes的status列是否為“UNUSABLE”即可對(duì)于處于無(wú)效的索引，建議找時(shí)間要重建，而不是一味的跳過(guò)，跳過(guò)只是一種臨時(shí)的辦法2025/1/2148內(nèi)容提要臨時(shí)表的使用方法Logging與Nologging適當(dāng)使用關(guān)于熱塊問(wèn)題索引統(tǒng)計(jì)數(shù)據(jù)陳舊問(wèn)題多塊讀方法索引與相關(guān)參數(shù)會(huì)話級(jí)參數(shù)使用方法2025/1/2149參數(shù)設(shè)置與API用“ALTERSYSTEM”

命令修改實(shí)例參數(shù)：<param>是參數(shù)的完整名稱(chēng)，而<value>是要設(shè)置的具體值，如Audit_trail=TRUEcomment是注釋字串deferred表示參數(shù)延遲生效MEMORY[Default]:在當(dāng)前實(shí)例內(nèi)存有效SPFILE:只寫(xiě)到spfileBOTH:memory+spfileALTERSYSTEMSET<param>[=]<value>[comment'<text>'][deferred][SCOPE={memory|spfile|both}][sid={'<sid>'|'*'}];2025/1/2150參數(shù)設(shè)置與API用“ALTERSYSTEM”

命令修改實(shí)例參數(shù)：實(shí)例級(jí)參數(shù)的修改需要具有SYSDBA：--系統(tǒng)級(jí)半永久化參數(shù):ALTERSYSTEMSETparam=valueSCOPE=MEMORY;--只存儲(chǔ)在參數(shù)文件的參數(shù)：ALTERSYSTEMSETparam=valueSCOPE=SPFILE;--修改后必須重啟動(dòng)才有效的參數(shù)：ALTERSYSTEMSETparam=valueSCOPE=BOTH;--SQL>altersystemsetdb_cache_size=512Mscope=both;系統(tǒng)已更改。2025/1/2151參數(shù)設(shè)置與APIDBA對(duì)參數(shù)的修改與影響范圍：DBA用ALTERSYSTEM修改參數(shù)影響整個(gè)實(shí)例DBA也可用ALTERSESSION修改參數(shù)只影響該會(huì)話Oracle系統(tǒng)參數(shù)的會(huì)話級(jí)修改問(wèn)題：國(guó)內(nèi)多數(shù)開(kāi)發(fā)人員認(rèn)為修改參數(shù)應(yīng)該是DBA的工作，這是對(duì)Oracle參數(shù)的誤解Oracle系統(tǒng)設(shè)計(jì)早考慮到參數(shù)影響范圍這個(gè)問(wèn)題會(huì)話級(jí)的修改=按照該會(huì)話的需要進(jìn)行設(shè)置(不強(qiáng)加將參數(shù)實(shí)施到別的會(huì)話)會(huì)話級(jí)參數(shù)的修改有2：命令方式與API方法

2025/1/2152參數(shù)設(shè)置與API會(huì)話級(jí)修改參數(shù)方法1：方法1：ALTERSESSIONset<parameter>=<value>;會(huì)話級(jí)修改參數(shù)方法2：DBMS_SYSTEM包：DBMS_SYSTEM.set_bool_param_in_sessionDBMS_SYSTEM.set_int_param_in_session--會(huì)話級(jí)參數(shù)設(shè)置:altersessionsetworkarea_size_policy=manualEXECUTEDBMS_SYSTEM.SET_INT_PARAM_IN_SESSION

(9,27,'sort_area_size',65535000);2025/1/2153會(huì)話級(jí)可修改的參數(shù)可用ALTERSESSION修改的參數(shù)：ISSES_MODIFIABLE=TRUE會(huì)話級(jí)可修改查詢(xún)會(huì)話級(jí)可修改的參數(shù)：--11gR1colnamefora50colvaluefora20selectname,value,ISSES_MODIFIABLEFROMV$PARAMETERWHEREISSES_MODIFIABLE='TRUE';NAMEVALUEISSES---------------------------------------------------------------------------timed_statisticsTRUETRUEtimed_os_statistics0TRUEnls_languageAMERICANTRUEnls_territoryAMERICATRUE……asm_power_limit1TRUEsqltune_categoryDEFAULTTRUEtracefile_identifierTRUEmax_dump_file_sizeunlimitedTRUE已選擇106行。2025/1/2154會(huì)話級(jí)可修改的參數(shù)可用ALTERSESSION修改的參數(shù)：ISSES_MODIFIABLE=TRUE會(huì)話級(jí)可修改查詢(xún)會(huì)話級(jí)可修改的參數(shù)：SQL>select*fromv$version;BANNER------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.1.0-ProductionPL/SQLRelease11.2.0.1.0-ProductionCORE 11.2.0.1.0 ProductionTNSfor32-bitWindows:Version11.2.0.1.0-ProductionNLSRTLVersion11.2.0.1.0-ProductionSQL>selectname,value,ISSES_MODIFIABLE2FROMV$PARAMETERWHEREISSES_MODIFIABLE='TRUE';NAMEVALUEISSES----------------------------------------------------------------------timed_statisticsTRUETRUEtimed_os_statistics0TRUE……m