Exadata一體機(jī)最佳實(shí)踐

婁恒:heng.lou@APACExadataspecialistBOCACRM數(shù)據(jù)庫物理模型和開發(fā)最佳實(shí)踐提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集2Tips1:設(shè)置合適的DB_Block_Size對(duì)Exadata而言，MAX_IO_SIZE缺省為1MB所以推薦DB_BLOCK_SIZE設(shè)置為8192同時(shí)推薦DB_FILE_MULTI_BLOCK_READ_COUNT設(shè)置為128因?yàn)開MAX_IO_SIZE=DB_FILE_MULTI_BLOCK_READ_COUNT*DB_block_size。Tips：采用locallymanaged,Bigfile創(chuàng)建表空間Tips：選擇表空間合適的Extent管理方法采用Auto-Allocate方式，Oracle自動(dòng)管理表的Extent的大小，開始時(shí)初始的Extent大小為64KB(除Partition表外)，當(dāng)表或索引的段(Segment)大小超過1MB，Extent以1MB為單位增長段空間，一旦段大小超過64MB，Extent以8MB為單位增長段空間。采用Uniform方式，所有段的Extent大小都是統(tǒng)一大小。對(duì)于Auto-Allocate方式和Uniform方式的選擇，Oracle推薦采用Auto-Allocate作為Extent的管理方式。SalesTableUniformExtent：數(shù)據(jù)并行加載特點(diǎn)假設(shè)有4個(gè)并行進(jìn)程進(jìn)行數(shù)據(jù)加載，Uniformextend大小為8MB當(dāng)多塊加載后，將會(huì)有很多未被填滿的Extent存在，也就是說Extent中可能會(huì)有很多空間空洞在表掃描時(shí)，每個(gè)空間空洞會(huì)被掃描到，造成IO的浪費(fèi)Auto-AllocateExtent：數(shù)據(jù)并行加載特點(diǎn)假設(shè)有4個(gè)并行進(jìn)程進(jìn)行數(shù)據(jù)加載，initial&nextextend大小為8MBSalesTable當(dāng)加載完成后，最后一些Extent大小會(huì)比其他Extent小，但是所有Extent都會(huì)被填滿每個(gè)表或分區(qū)掃描將至掃描數(shù)據(jù)，而沒有空閑空間被掃描采用Auto-allocate的優(yōu)點(diǎn)在于大數(shù)據(jù)加載時(shí)，有最少的空間浪費(fèi)，因?yàn)樽詈蠹虞d的Extent會(huì)被Trim到64KB的整數(shù)倍的大小，所以幾乎沒有太大的空間浪費(fèi)，同時(shí)表掃描時(shí)也可以提高IO效率采用Auto-Allocate方式時(shí)，在創(chuàng)建分區(qū)表時(shí)，可以自動(dòng)支持LargeExtent（8MBExtent），而對(duì)于uniform方式，則無法支持Tips：創(chuàng)建多個(gè)Bigfile表空間存儲(chǔ)不同的分區(qū)在對(duì)大的Partition表進(jìn)行并行數(shù)據(jù)加載時(shí)，應(yīng)該盡可能避免文件頭塊爭用（FileHeaderBlockercontention）情況出現(xiàn)。文件頭塊爭用（FileHeaderBlockercontention）可以在AWR報(bào)告中檢查“gcbufferbusyenqueuewaitevent”?；蛘邫z查“bufferbusywaits”的統(tǒng)計(jì)信息，以判斷是否存在FileHeaderBlockercontention，可以使用如下SQL：CREATETABLEsales_composite(salesman_idNUMBER(5),salesman_nameVARCHAR2(30),sales_amountNUMBER(10),sales_dateDATE)PARTITIONBYRANGE(sales_date)SUBPARTITIONBYHASH(salesman_id)subpartitions128storein(ts1,ts2,ts3,ts4)(PARTITIONsales_jan2000VALUESLESSTHAN(TO_DATE('02/01/2000','MM/DD/YYYY')),PARTITIONsales_feb2000VALUESLESSTHAN(TO_DATE('03/01/2000','MM/DD/YYYY')),PARTITIONsales_mar2000VALUESLESSTHAN(TO_DATE('04/01/2000','MM/DD/YYYY')),PARTITIONsales_apr2000VALUESLESSTHAN(TO_DATE('05/01/2000','MM/DD/YYYY')),PARTITIONsales_may2000VALUESLESSTHAN(TO_DATE(‘06/01/2000’,‘MM/DD/YYYY’)));selectp1"File#",p2"Block#",p3"ReasonCode"fromv$session_waitwhereevent='bufferbusywaits';為了避免文件頭爭用情況，可以采用多個(gè)Bigfiletablespace來均勻存放Partitiontable。例如：下面語句使用4個(gè)表空間來均勻存放128個(gè)Subpartition表：Tips：對(duì)于分區(qū)表（Partition）加載，啟用LargeExtent推薦大的對(duì)象使用LargeExtent方式，以便減少Extent的數(shù)量。當(dāng)設(shè)置CELL_PARTITION_LARGE_EXTENTS=TRUE時(shí)，所有分區(qū)表在創(chuàng)建時(shí)，將自動(dòng)啟動(dòng)LargeExtent模式（8MB）,即創(chuàng)建時(shí)InitialExtent大小為8MB。限制條件：CELL_PARTITION_LARGE_EXTENTS僅適用于：分區(qū)表、locallymanaged和AUTO-ALLOCATE的表空間。不適合于：非分區(qū)表、Uniform的表空間的對(duì)象。Tips：針對(duì)非分區(qū)表（Non-Partition）加載，使用LargeExtent如果對(duì)于非分區(qū)表，想使用LargeExtent(8MB)。需要在Createtable的DDL中設(shè)置INITIAL和NEXT子句。當(dāng)對(duì)非分區(qū)表進(jìn)行并行數(shù)據(jù)加載時(shí)，數(shù)據(jù)在Temp段中被生成，然后再合并到要加載的數(shù)據(jù)表的Extent中，缺省按照64KBExtent大小進(jìn)行數(shù)據(jù)增長。但從開始，在加載數(shù)據(jù)時(shí)（Load），NEXT用來控制新的Extent的大小。所以對(duì)于大的非分區(qū)表加載，可以直接將INITIAL和NEXT都設(shè)置為8MB。SQL如下：CreateTablesales(.....)parallelstorage(INITIAL8MNEXT8M)(........)Tips：關(guān)閉deferred_segment_creation（段延遲創(chuàng)建）功能從11gR2開始，當(dāng)在LocallyManagedTablespace上，創(chuàng)建一個(gè)非分區(qū)表時(shí)，缺省情況表的空間分配會(huì)啟用延遲分配方式，即當(dāng)首行被Insert到表中時(shí)，表的Extent才會(huì)被逐漸分配。建議關(guān)閉延遲空間空間分配，可以通過如下SQL關(guān)閉：Altersystemsetdeferred_segment_creation=FALSEscope=bothTips:空間回收ShrinkingSegmentsHWMHWMHWM ALTERTABLEemployeesSHRINKSPACECOMPACT;1 ALTERTABLEemployeesSHRINKSPACE;2DMLoperationsandqueriescanbeissuedduringcompaction.DMLoperationsareblockedwhentheHWMisadjusted.ShrinkingSegmentsbyUsingSQLALTER…SHRINKSPACE[CASCADE]TABLE[OVERFLOW]INDEXMATERIALIZEDVIEWMATERIALIZEDVIEWLOGMODIFYPARTITIONMODIFYSUBPARTITIONMODIFYLOBALTERTABLEemployeesSHRINKSPACECASCADE;ALTERTABLEemployeesENABLEROWMOVEMENT;12ALTERTABLEemployeesMODIFYLOB(resume)(SHRINKSPACE);3ALTERTABLEemployeesOVERFLOWSHRINKSPACE;4提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集14Tips：數(shù)據(jù)倉庫設(shè)計(jì)一個(gè)典型的數(shù)據(jù)倉庫是一個(gè)ManyRowsSystem，但經(jīng)常會(huì)被用FewRowsSystem的方式來開發(fā)(RowByRow)在大多數(shù)情況下，ManyRowsSystem還是FewRowsSystem可以通過SQL語句的執(zhí)行頻度決定：1/Sec代表manyrows1000/Sec代表fewrows備注：在ManyRowsSystem中，可能存在一種誤導(dǎo)：批量集合操作（SetBasedOperation）通過RowByRow的操作方式開發(fā)效率更高Tips：數(shù)據(jù)倉庫物理模型設(shè)計(jì)原則ManyRows并行（Parallelism）

分區(qū)（Partitioning）壓縮（Compression）

集合批量操作（Set-basedtechniques）

通過數(shù)據(jù)轉(zhuǎn)換代替實(shí)現(xiàn)數(shù)據(jù)更新（Datamodifiedbytransformation）

FewRows索引（Indexdesign）

物化視圖（Materializedviewsandaggregates）

Tips：數(shù)據(jù)壓縮考慮壓縮技術(shù)是數(shù)據(jù)倉庫物理設(shè)計(jì)最重要的因素之一壓縮的影響:極大加速表掃描速度、減少磁盤空間占用、加速數(shù)據(jù)備份但是，壓縮對(duì)大大降低UPDATE和DELETE的效率一個(gè)好的數(shù)據(jù)倉庫模型，對(duì)于壓縮表而言，將只采用Append方式操作，避免采用修改（Update、Delete、Merge）方式對(duì)數(shù)據(jù)的操作，以保證對(duì)壓縮表操作的效率但是如果一些經(jīng)常更新的大表確實(shí)有壓縮的需求，可以考慮使用OLTP壓縮技術(shù)。無論HCC壓縮還是OLTP壓縮，其本質(zhì)都是去重，只是其算法上有些差異。因而如果在數(shù)據(jù)加載時(shí)，對(duì)數(shù)據(jù)預(yù)先排序，那么可帶來明顯的壓縮比率的提升。一般來說，壓縮級(jí)別QUERYHIGH既可以得到一個(gè)理想的壓縮比率，也能提供很好的查詢性能，可以考慮作為初始測試的壓縮級(jí)別.Tips：EHCC混合列壓縮EHCC(ExadataHybridColumnarCompression)不是完全的列壓縮，而是混合列存儲(chǔ)EHCC具備多種壓縮類型QuerylowQueryhighArchivelowArchivehigh設(shè)計(jì)用于不頻繁修改的數(shù)據(jù)設(shè)計(jì)用于低并發(fā)的場景支持?jǐn)?shù)據(jù)庫各種功能DMLs/DDLs,Partitioning,PQ,PDML,Onlineredefinition,CTAS,IAS,SQLLoader,Externaltables,Context,MVsetc.支持索引只有在數(shù)據(jù)批量加載時(shí)被壓縮Tips：如何創(chuàng)建EHCC表CTAS(createtableasselect)createtablefoocompressforqueryasselect*frombar1;IDL(insertdirectload)createtablefoocompressforarchivelow;insert/*+APPEND*/intofooselect*frombar2;壓縮可以在Segment一級(jí)指定：每個(gè)分區(qū)可以有不同的壓縮類型例如：createtableorders(cid,pid,sid,price,discount,odate) partitionbyrange(cid) (partitionp1valueslessthan(100000)nocompress, partitionp2valueslessthan(200000)compressforarchivelow, partitionp3valueslessthan(300000)compressforqueryhigh, partitionp4valueslessthan(maxvalue)compressforquerylow) enablerowmovement asselect*fromprev_orders;Tips：動(dòng)態(tài)啟用或停用表的EHCCExistingtables/partitionscanbeconvertedtouseEHCCaltertablebarmovecompressforqueryhighaltertableordersmodifypartitionp1movecompressforarchivelowNewdataloadedinexistingtables/partitionscangotoEHCCblocks,keepingtheexistingdataasisaltertablebarcompressforqueryhighaltertableordersmodifypartitionp3compressforquerylowEHCCcanbedisabledaltertablebarnocompressaltertablebarmovenocompressOnlineredefinitionpackagecanbeusedforenabling/disablingEHCCTips：分區(qū)(Partition)設(shè)計(jì)目的數(shù)據(jù)管理Exchangedatain/outBreakdownoperationsintosmallerpieces查詢優(yōu)化PartitionpruningHashbasedjoinsandsortsTips：數(shù)據(jù)倉庫分區(qū)（Partition）策略一級(jí)分區(qū)：首先按日期對(duì)大表進(jìn)行分區(qū)主要目標(biāo)是啟動(dòng)分區(qū)修剪（PartitionPruning），簡化數(shù)據(jù)管理主要對(duì)于日期字段進(jìn)行Range或interval分區(qū)選擇大部分查詢查詢用到的日期字段作為分區(qū)列這個(gè)日期字段（用于分區(qū)列）應(yīng)該是不易變（不被更新）二級(jí)子分區(qū)（Subpartition）可以按照：

Hash分區(qū)，更好地支持join和SortRANGE或LIST分區(qū)，更好的支持分區(qū)修剪（PartitionPruning）

Tips:定義合理的Hash分區(qū)數(shù)量Oracle推薦大的分區(qū)表使用Hash分區(qū)作為分區(qū)方法，為了保證數(shù)據(jù)在HashPartition間均勻分布，Hash分區(qū)的數(shù)量建議是2的指數(shù)冪，或者設(shè)置Partition數(shù)量為CPU數(shù)量的2倍。然而每個(gè)hashPartition應(yīng)該至少不小于16MB。小于16MB將在并行執(zhí)行下不會(huì)有較好的掃描效率。首先考慮分區(qū)數(shù)是2的整數(shù)冪的情況：當(dāng)Oracle的分區(qū)數(shù)從2個(gè)變?yōu)?個(gè)，Oracle并不需要將所有數(shù)據(jù)重新打亂，而是將原有的2個(gè)分區(qū)每個(gè)都一分為二。同樣的道理，如果將分區(qū)數(shù)設(shè)置為8，Oracle會(huì)將原有的4個(gè)分區(qū)一分為二。Oracle的HASH分區(qū)就像是一棵大的二叉樹。每個(gè)分區(qū)就相當(dāng)于二叉樹的一個(gè)葉節(jié)點(diǎn)。二叉樹的第一層，只有一個(gè)根節(jié)點(diǎn)，對(duì)應(yīng)只有1個(gè)分區(qū)的情況。二叉樹的第二層，兩個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)2個(gè)分區(qū)的情況。二叉樹的第三層，4個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)4個(gè)分區(qū)的情況。二叉樹的第n層，2^(n-1)個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)2^(n-1)個(gè)分區(qū)情況。Tips：使用Partition-wisejoins對(duì)于兩個(gè)大表關(guān)聯(lián)操作，推薦使用partiton-wisejoins來減少并行進(jìn)程間數(shù)據(jù)交換，減少查詢的執(zhí)行時(shí)間。對(duì)于使用fullpartition-wisejoin，兩個(gè)關(guān)聯(lián)的表必須是相同的Partition鍵值（即兩個(gè)表必須有相同的Partition列、相同的Partition方法、相同的Partition數(shù)量）。如下是采用partiton-wisejoins的兩個(gè)表關(guān)聯(lián)的執(zhí)行計(jì)劃，Sales表和Customers表具有相同的并行度、HashPartition方法（Cust_id列作為HashPartitionKey）、Join列是PartitionKey。PartitionHashAllabove在Join方法之上，說明這是一個(gè)partition-wisejoinSELECTsum(amount_sold)FROMsaless,customercWHEREs.cust_id=c.cust_id;Bothtableshavethesamedegreeofparallelismandarepartitionedthesamewayonthejoincolumn(cust_id)SalesRangepartitionMay18th2008CustomerHashPartitionedSubpart1Alargejoinisdividedintomultiplesmallerjoins,eachjoinsapairofpartitionsinparallelPart1Subpart2Subpart3Subpart4Part2Part3Part4Subpart2Subpart3Subpart4Subpart1Part1Part2Part3Part4PartitionWisejoin對(duì)于partition-wisejoin而言，查詢的并行度必須是等于或成倍于表分區(qū)的數(shù)量SalesTableSALES_Q3_1998SELECTsum(s.amount_sold)FROMsalessWHEREs.time_idBETWEENto_date(’01-JAN-1999’,’DD-MON-YYYY’)ANDto_date(’31-DEC-1999’,’DD-MON-YYYY’);Q:Whatwasthetotalsalesfortheyear1999?Tips：分區(qū)裁剪（PartitionPruning）SALES_Q4_1998SALES_Q1_1999SALES_Q2_1999SALES_Q3_1999SALES_Q4_1999SALES_Q1_2000Onlythe4relevantpartitionsareaccessedTips：如何檢查分區(qū)修剪是否生效SampleplanOnly4partitionsaretouched–9,10,11,&12SALES_Q1_1999,SALES_Q2_1999,SALES_Q3_1999,SALES_Q4_1999TablePartition1Partition5Partition10Sub-part1Sub-part2Sub-part1Sub-part2Sub-part1Sub-part2::129101920Tips：如何檢查分區(qū)修剪是否生效SimpleQuery:SELECTCOUNT(*)FROMRHP_TABWHERECUST_ID=9255ANDTIME_ID=‘2008-01-01’;Overallpartition#rangepartition#Sub-partition#提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集29Tips：使用ExternalTable進(jìn)行數(shù)據(jù)加載Oracle推薦使用ExternalTable方式加載數(shù)據(jù)，而不第一推薦SQL*Loader，因?yàn)镾QL*Loader做并行數(shù)據(jù)加載時(shí)，數(shù)據(jù)首先被加載到TEMPExtent中，只有在transaction被Commit時(shí)，TempExtent會(huì)被合并到實(shí)體表的段（Segment）中，而在原來表中的部分滿的Extent（partiallyfullextents）會(huì)被跳過，所以到導(dǎo)致空間浪費(fèi)和性能浪費(fèi)。所以對(duì)于大量加載的Partition表，這樣可能會(huì)導(dǎo)致較大的空間浪費(fèi)情況。采用ExternalTable方式加載時(shí)，會(huì)有如下好處：FullusageofSQLcapabilitiesdirectlyonthedataAutomaticuseofparallelcapabilitiesNoneedtostagethedataagaininthedatabaseBetterallocationofspacewhenstoringdataHighwatermarkbrokeringAutoallocatetablespacewilltrimextentsaftertheloadAdditionalcapabilitieslikeTheusageofdatapumpfilesTheusageofpre-processingExternalTableTips：ExternalTable預(yù)處理允許文件在加載過程中被自動(dòng)化預(yù)處理例如：對(duì)壓縮文件進(jìn)行解壓縮、排序等預(yù)處理不支持對(duì)文件的自動(dòng)的小顆粒化需要提供多份文件，文件的數(shù)量決定了處理的并行度需要授權(quán)對(duì)目錄的讀和執(zhí)行的權(quán)限（Grantread,executeprivilegesdirectories）CREATETABLEsales_external(…)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYdata_dir1ACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINE

PREPROCESSORexec_dir:'gunzip'OPTIONS'-c' FIELDSTERMINATEDBY'|') LOCATION(…));Tips：直接路徑加載（DirectPathLoad）在每次IO異步請(qǐng)求時(shí)，數(shù)據(jù)被直接寫入數(shù)據(jù)庫存儲(chǔ)CTAS語句總是使用DirectPath在Insert

AsSelect語句中使用APPEND暗示方法也是DirectPathInsert/*+APPEND*/intoSalespartition(p2)Select*Fromext_tab_for_sales_data;在一個(gè)對(duì)象上，只有一個(gè)directpath操作能夠起作用通過指定一個(gè)表的分區(qū)表的名字方法，可以同時(shí)將多個(gè)DirectPach加載操作到一個(gè)表Tips：并行加載（ParallelLoad）為了確保DirectPath加載成為并行操作，需要通過如下方式：在Hint中指定并行度、或者在表上指定并行度通過AlterSession語句啟動(dòng)會(huì)話級(jí)并行當(dāng)并行度被指定后，CTAS會(huì)自動(dòng)并行化加載IAS不會(huì)自動(dòng)并行化，他需要parallelDML的支持：ALTERSESSIONENABLEPARALLELDML;Tips：使用Paralleldirectpath數(shù)據(jù)加載使用“directpathload”可以帶來很好的數(shù)據(jù)加載性能，因?yàn)閿?shù)據(jù)會(huì)繞過BufferCache，直接寫入數(shù)據(jù)庫存儲(chǔ)。CTAS總會(huì)使用“directpathload”，但是‘InsertAsSelect’(IAS)并不是總會(huì)使用“directpathload”，為了使IAS使用“directpathload”，需要在IAS中加入APPENDhint，SQL如下：Insert/*+APPEND*/intoSalespartition(p2)Select*Fromext_tab_for_sales_data;

ALTERSESSIONENABLEPARALLELDML;Insert/*+APPEND*/intoSalespartition(p2)Select*fromext_tab_for_sales_data;

為了讓“directpathload”以并行方式運(yùn)行，可以以下兩種方式設(shè)置并行度：1）在CTAS和IAS中加入PARALLELhint；2）在Externaltable和目標(biāo)加載Table上，設(shè)置PARALLEL子句。一旦設(shè)置了并行度，CTAS將自動(dòng)執(zhí)行并行化的“directpathload”，而IAS將不會(huì)。為了讓IAS能夠并行化執(zhí)行“directpathload”，出了設(shè)置并行度外，還必須通過alterthesessiontoenableparallelDML。SQL如下：Tips：在數(shù)據(jù)加載時(shí)，對(duì)于常訪問的字段，可以進(jìn)行排序，以便增加Storageindex的效率對(duì)在使用INSERT/*+APPEND*/做數(shù)據(jù)加載時(shí)，可以對(duì)查詢中Where條件中使用較多的列進(jìn)行OrderBy排序，以便增加數(shù)據(jù)壓縮率和Storageindex查詢效率。Selectname,value/1024/1024asstat_valuefromv$mystats,v$statnamenWhereS.Statistic#=n.statistic#andnamelike‘%cellphysical%’Insertintotableaselect*fromext_tableaorderbycol1,col2…可以通過查詢v$mystat,v$statname視圖，通過看"cellphysicalI0bytessavedbystorageindex"統(tǒng)計(jì)值，來檢查多少I/O被減少。SQL如下：Tips：通過表分區(qū)交換做數(shù)據(jù)加載(Partitionexchangeload)Oracle建議對(duì)大的數(shù)據(jù)表采用分區(qū)表，分區(qū)表的好處之一就是可以通過分區(qū)交換方式（PartitionExchange）進(jìn)行數(shù)據(jù)加載，這樣可以最小化的對(duì)業(yè)務(wù)影響情況下進(jìn)行快速數(shù)據(jù)加載。PartitionExchange可以允許用戶交換非分區(qū)表數(shù)據(jù)到一個(gè)表的分區(qū)中。分區(qū)交換命令并不真正移動(dòng)兩個(gè)表的數(shù)據(jù)，而是簡單的Update數(shù)據(jù)字典信息。由于沒有進(jìn)行數(shù)據(jù)移動(dòng)，所以數(shù)據(jù)非分區(qū)表需要與分區(qū)表有相同的存儲(chǔ)結(jié)構(gòu)（存儲(chǔ)在合適的表空間、相同的ExtentSize）。SQL如下：CreateTabletmp_sales2(.....)

parallelstorage(INITIAL8MNEXT8M)tablespacemain_fact_tbs...........AltertableSalesexchangepartitionp2withtabletmp_sales2includingindexeswithoutvalidation;SalesTableMay22nd2008May23rd2008May24th2008May18th2008May19th2008May20th2008May21st2008DBA1.Createexternaltableforflatfiles5.AltertableSalesexchangepartitionMay_24_2008withtabletmp_sales2.UseCTAScommandtocreatenon-partitionedtableTMP_SALESTmp_salesTableSalesTableMay22nd2008May23rd2008May24th2008May18th2008May19th2008May20th2008May21st2008Salestablenowhasallthedata3.Createindexes4.GatherStatisticsTmp_salesTableTips：PartitionExchangeloading操作過程

提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集39Tips：并行執(zhí)行的適用情況并行執(zhí)行是Exadata最強(qiáng)大的能力之一，充分利用Exadata的并行計(jì)算能力可以極大提高系統(tǒng)利用率。但是我們必須要確認(rèn)并行使用的時(shí)機(jī)，以及并行使用的程度。

并行適用場景：并行執(zhí)行應(yīng)該在所有的資源密集型操作中被使用，例如：復(fù)雜查詢、大量數(shù)據(jù)處理DML、大表建索引、收集統(tǒng)計(jì)信息、大量數(shù)據(jù)加載等。只有在表數(shù)據(jù)小于64MB或者同時(shí)有上百個(gè)并發(fā)用戶執(zhí)行操作時(shí)，不建議使用并行執(zhí)行操作。并行不適用場景：短交易（幾秒鐘或更少）。在這些環(huán)境中并行執(zhí)行是沒有什么用處，因?yàn)閰f(xié)調(diào)并行執(zhí)行服務(wù)器會(huì)增加相關(guān)的成本，這種協(xié)調(diào)的成本可能超過并行帶來的好處。已經(jīng)大量使用CPU、內(nèi)存或I/O資源的環(huán)境。并行執(zhí)行旨在利用其它可用的硬件資源，如果沒有這樣的資源可用，那么并行執(zhí)行不產(chǎn)生任何效益，并且可能損害性能。Tips：并行執(zhí)行工作過程用戶連接到數(shù)據(jù)庫User后臺(tái)進(jìn)程被派生當(dāng)用戶發(fā)出一個(gè)并行SQL，后臺(tái)進(jìn)程就變成為QC（QueryCoordinator）QC從并行進(jìn)程組中獲得Parallelservers，然后QC分配任務(wù)給Parallelservers進(jìn)程Parallelservers–是一個(gè)獨(dú)立的Session，從并行進(jìn)程組中分配，并完成指定的具體任務(wù)Parallelservers通過SharedPool的內(nèi)存?zhèn)鬟f通信消息，與QC進(jìn)程和并行進(jìn)程進(jìn)行互相通信MessagesQCconnectionParallelserverconnectionProducersConsumersQuerycoordinatorP1P2P3P4Hashjoin會(huì)首先選擇小表作為驅(qū)動(dòng)表，進(jìn)行掃描，在這個(gè)例子中Customers表示小表，4個(gè)Producer并行進(jìn)程并行掃描，并且返回就過給Consumers進(jìn)程P8P7P6P5SALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行執(zhí)行的工作機(jī)制ProducersConsumersQuerycoordinatorP1P2P3P4一旦4個(gè)Producer進(jìn)程完成了小表的掃描，他們開始對(duì)大表（Sales表）進(jìn)行并行掃描，并返回結(jié)果給Consumer進(jìn)程P8P7P6P5SALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行執(zhí)行的工作機(jī)制ProducersConsumersP1P2P3P4P8P7P6P5一旦Consumer進(jìn)程收到了兩個(gè)表的數(shù)據(jù)，他們開始做并行的Join操作，一旦完成join操作，就將結(jié)果返回給QC進(jìn)程QuerycoordinatorSALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行執(zhí)行的工作機(jī)制SELECT c.cust_last_name,s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;QueryCoordinatorProducersProducersConsumersConsumersTips：檢查并行執(zhí)行情況selectsum(revenue),storefromline_itemsWhereprofit(price,units)>

0.2orderbystoreDataonDiskQueryServersscanscanscansortA-KsortL-SsortT-Zdispatchwork;assembleresultsProducersorscannersConsumersOrAggregators)Coordinator并行進(jìn)程的任務(wù)Tips：不同操作并行方式–Groupby,Orderby進(jìn)程1進(jìn)程2進(jìn)程3進(jìn)程4進(jìn)程5進(jìn)程6進(jìn)程1進(jìn)程2進(jìn)程3進(jìn)程4進(jìn)程5進(jìn)程6Hash分布Hashkey1Hashkey2Hashkey3Hashkey4Hashkey5Hashkey6進(jìn)程1進(jìn)程2進(jìn)程3進(jìn)程4進(jìn)程5進(jìn)程6進(jìn)程1進(jìn)程2進(jìn)程3進(jìn)程4進(jìn)程5進(jìn)程6Range分布A--HI--MN--RS--UV--XY--ZGroupByOrderByHash分區(qū)效率最高Range分區(qū)效率最高Tips:數(shù)據(jù)分布的例子selectcount(*)fromyellowy,greengwherey.deptno=g.deptnoTips：并行進(jìn)程中不同的數(shù)據(jù)分布方式分布方式說明Hash,Hash使用Hash函數(shù)映射Join字段，映射完成后，每個(gè)查詢引擎執(zhí)行Join，建議Join表size相近，使用hash-join或者sortmergejoinBroadcast,None外表的行廣播到每個(gè)查詢進(jìn)程，內(nèi)表隨機(jī)分區(qū)，建議外表size大大小于內(nèi)表。通用規(guī)則：innertablesize*numberofqueryservers>outertablesizeNone,Broadcase內(nèi)表的行廣播到每個(gè)查詢進(jìn)程，建議內(nèi)表的size大大小于外表。通用規(guī)則：innertablesize*numberofqueryservers<outertablesizePartition,None使用內(nèi)表的分區(qū)方式映射外表的行，內(nèi)部表必須在join字段上分區(qū)。建議外表的分區(qū)數(shù)量大約等于查詢進(jìn)程數(shù)。注意：在內(nèi)部表沒有分區(qū)或者不是join字段情況下被忽略None,Partiition和PartitionNone正好相反None,None查詢引擎在join分區(qū)字段上連接，必須在join字段上分區(qū)Tips：并行度設(shè)置建議采用默認(rèn)傳統(tǒng)手工管理方式(PARALLEL_DEGREE_POLICY=LIMITED/MANUAL)。在這種情況下需要對(duì)數(shù)據(jù)量有一個(gè)預(yù)估，然后設(shè)置的并行度應(yīng)該給每個(gè)進(jìn)程分配到的至少為上百兆數(shù)據(jù)。一個(gè)典型的初始預(yù)估模式：200MB以下的小對(duì)象：不并行200MB-5GB的對(duì)象：并行度45GB以上的大對(duì)象：并行度32*最終采用的并行度需要經(jīng)過測試才能確定Tips：啟用并行執(zhí)行1.在表上執(zhí)行并行

altertablesalesparallel;altertablecustomersparallel;select/*+parallel(c)parallel(s)*/c.state_province,sum(s.amount)revenuefromcustomersc,salesswheres.customer_id=c.id//并行查詢altersessionforce/enableparallelquery;altersessiondisableparallelquery;

//并行DMLaltersessionforce/enableparallelDML;altersessiondisableparalleldml;

//并行DDLaltersessionforce/enableparallelDDL;altersessiondisableparallelDDL;2.在SQL級(jí)使用Hint啟用并行3.在會(huì)話（Session）上，啟用并行Tips：如何檢查并行執(zhí)行的情況1.檢查系統(tǒng)并行進(jìn)程使用情況：

2.檢查系統(tǒng)各個(gè)并行任務(wù)分布情況

3.檢查并行的系統(tǒng)統(tǒng)計(jì)Selectinst_id,status,count(*)fromgv$px_processgroupbyinst_id,status;INST_IDSTATUSPX_SERVERS#-------------------------------------------1AVAILABLE41INUSE122AVAILABLE82INUSE8Selectinst_id,sid,degree,req_degree,server#fromgv$px_session;selectname,valuefromv$sysstatwhereupper(name)like'%PARALLELOPERATIONS%'orupper(name)like‘%PARALLELIED’orupper(name)like‘%PX%‘;Tips：并行服務(wù)相應(yīng)的參數(shù)參數(shù)名值說明parallel_adaptive_multi_userFALSEPerformanceimpact:PQdegreewillbereducedforsomequeriesespeciallywithconcurrentworkloads.parallel_execution_message_size16384*ImprovesPQperformanceParallel_threads_per_cpu1Checkthatthisvalueisat1.Settingthistoaccountforhyperthreadingparallel_max_servers240forX2-2Checktoensurenotmorethantherecommendedvalue.Settingthishigherthanthisrecommendedvaluecandepletememoryandimpactperformance.parallel_min_servers96forX2-2Reduceoverheadofallocatinganddeallocatingparallelserversunncessarily.parallel_degree_policyManualEvaluateworkloadmanagementbeforedeploying;otherwisesettomanualbydefault.parallel_degree_limit16forX2-2Checkthatthisislessthanparallel_servers_target.parallel_servers_target128forX2-2Checktoensurenothigherthanparallel_max_servers.Settingthishigherthanthisrecommendedvaluecandepletememoryandimpactperformance.提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集54Tips：表Join方法（1）--SortMergeJoin1.排序合并連接(sortmergejoin)：排序合并連接(sortmergejoin):是一個(gè)集合操作,分類合并連接應(yīng)該在索引不能用于查詢的情況下考慮.在于把兩個(gè)大表讀入內(nèi)存并進(jìn)行排序的成本，成本計(jì)算如下：costofmergejoin=accesscostofA+accesscostofB+(sortcostofA+sortcostofB)適用于查詢整體返回大量結(jié)果集，兩個(gè)大表做連接，且表已經(jīng)排過序的情況下。當(dāng)兩個(gè)表已經(jīng)排過序時(shí)，使用排序合并連接的性能可能會(huì)優(yōu)于散列連接。HASH_AREA_SIZE和SORT_AREA_SIZE設(shè)置過小，可能會(huì)導(dǎo)致優(yōu)化器避開散列連接而選擇排序合并連接。使用HINTS：use_merge，可以是表使用SortMergeJoin關(guān)聯(lián)。Tips：表Join方法（2）--NestedLoopJoin2.嵌套循環(huán)連接(nestedloopjoin)：嵌套循環(huán)連接(nestedloopjoin):是一個(gè)行操作,這種連接是OLTP應(yīng)用中最常見的連接操作,并且通常很有效，但對(duì)于批量數(shù)據(jù)操作往往效率不高。因?yàn)榇诉B接方法高度利用了索引。在于外表返回的每一行都要在內(nèi)表中進(jìn)行匹配的成本，成本計(jì)算如下：costofnestedloop=accesscostofouter+(numberofrowsfromouter*accesscostofinner)適用于外表有效基數(shù)較小，內(nèi)表連接字段含有索引，且查詢整體返回結(jié)果集不太大（小于1萬行）的情況下。使用HINTS：use_nl可以使執(zhí)行計(jì)劃按照NestedLoop方式執(zhí)行。Tips：表Join方法（3）--HashJoin3.散列連接(hashjoin)：幾乎是使用被連接表的全表掃描而來完成的.散列連接對(duì)于大表的相互連接,或者一個(gè)小表和一個(gè)超大型表的連接.在于將小表讀入內(nèi)存分成若干散列表，然后由大表對(duì)每個(gè)散列表都進(jìn)行一次匹配的成本，成本計(jì)算如下：costofhashjoin=accesscostofsmaller+(accesscostofbigger*numberofhashpartitionsofsmaller)適用于查詢整體返回大量結(jié)果集，且有較小的連接表可以放入內(nèi)存作為散列表的情況下。適用散列連接要注意HASH_AREA_SIZE要足夠大，可以容下散列表。如果散列表無法完全放入內(nèi)存，要設(shè)置較大的臨時(shí)段，從而盡量提高I/O性能。HINTS：use_hashTips:修改RowbyRow操作為集合操作declarecurrec_cur;rectype_table%rowtype;beginopencurforselect*fromtable1;loopfetchcurinto…ifcondition(rec)theninsertintotable2…elseinsertintotable3…endif;endloop;end;Moveawayfromrowbyrowprocessinginsert/*+append*/intotable2select*fromtable1wherecondition…insert/*+append*/intotable3select*fromtable1wherenotcondition…insert/*+append*/firstwhenconditionthenintotable2values…elseintotable3values…select*fromtable1TosetbasedprocessingTips：改寫Delete操作altersessionenableparalleldml/deletefromtx_logwhereSymbol=‘JAVA’

/commit/ createtabletx_log_newnologgingparallelcompressforalloperationsasselect*fromtx_logwhereSymbol!=‘JAVA’

/altertabletx_logrenametotx_log_old/altertabletx_log_newtotx_log/Tips：改寫UPDATE操作altersessionenableparalleldml/updatesales_ledgersettax_rate=9.9wheretax_rate=9.3andsales_date>‘01-Jan-09’/commit/ createtabletx_log_newnologgingparallelcompressforalloperationsasselect.,casesales_date>‘01-Jan-09’andtax_rate=9.3then9.9elsetax_rateend,.fromsales_ledger/altertabletx_logrenametotx_log_old/altertabletx_log_newtotx_log/Tips：對(duì)Merge的改寫Tips：多表Insert

UnconditionalINSERT

ALLINSERTALL

INTOsal_historyVALUES(EMPID,HIREDATE,SAL)

INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,

salarySAL,manager_idMGRFROMemployees

WHEREemployee_id>200;8rowscreated.

Tips：多表Insert

ConditionalINSERT

ALLINSERTALL

WHENSAL>10000THEN

INTOsal_historyVALUES(EMPID,HIREDATE,SAL)

WHENMGR>200THEN

INTOmgr_historyVALUES(EMPID,MGR,SAL)

SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployees

WHEREemployee_id>200;4rowscreated.

Tips：多表Insert

ConditionalINSERTFIRST

INSERTFIRST

WHENSAL>25000THEN

INTOspecial_salVALUES(DEPTID,SAL)

WHENHIREDATElike('%00%')THEN

INTOhiredate_history_00VALUES(DEPTID,HIREDATE)

WHENHIREDATElike('%99%')THEN

INTO

hiredate_history_99

VALUES(DEPTID,

HIREDATE)

ELSE

INTOhiredate_historyVALUES(DEPTID,HIREDATE)SELECTdepartment_idDEPTID,SUM(salary)SAL,

MAX(hire_date)HIREDATE

FROMemployeesGROUPBYdepartment_id;8rowscreated.Tips：多表Insert

PivotingINSERT

INSERTALL

INTOsales_infoVALUES(employee_id,week_id,sales_MON)

INTOsales_infoVALUES(employee_id,week_id,sales_TUE)

INTOsales_infoVALUES(employee_id,week_id,sales_WED)

INTOsales_infoVALUES(employee_id,week_id,sales_THUR)

INTOsales_infoVALUES(employee_id,week_id,sales_FRI)

SELECTEMPLOYEE_ID,week_id,sales_MON,sales_TUE,

sales_WED,sales_THUR,sales_FRI

FROMsales_source_data;5rowscreated.

Tips：避免關(guān)聯(lián)子查詢Correlatedsubqueriesareusedforrow-by-rowprocessing.Eachsubqueryisexecutedonceforeveryrowoftheouterquery.GETcandidaterowfromouterqueryEXECUTEinnerqueryusingcandidaterowvalueUSEvaluesfrominnerquerytoqualifyordisqualifycandidaterow關(guān)聯(lián)子查詢Thesubqueryreferencesacolumnfromatableintheparentquery.

SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator (SELECTcolumn1,column2FROMtable2WHEREexpr1= .expr2);outerouterSELECTlast_name,salary,department_idFROMemployeesouterWHEREsalary>

(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);Tips：改寫關(guān)聯(lián)子查詢Findallemployeeswhoearnmorethantheaveragesalaryintheirdepartment.Eachtimearowfrom

theouterqueryisprocessed,theinnerqueryisevaluated.selectlast_name,salary,a.department_idfromemployeesa,(selectdepartment_id,avg(salary)avg_salfromemployeesgroupbydepartment_id)bwherea.department_id=b.department_idanda.salary>b.avg_salTips：對(duì)Minus操作的改寫MinusisaSERIALoperationUseOuterJoininconjunctionwithISNULLoperatorDifference:>37minutesvs7:28Tips：對(duì)Minus操作改寫的例子selecta,bfromt1,t2wheret1.a=t2.a(+)

-->ifaisanullablecolumn,thennvl(t1.a,9)=nvl(t2.a(+),9)andt1.b=t2.b(+)-->ifbisnullablecharcolumnthennvl(t1.b,'?')=nvl(t2.b(+),'?')andt2.aisnullandt2.bisnull/

selecta,bfromt1minusselecta,bfromt2;SELECTemp.last_nameFROMemployeesempWHEREemp.employee_idNOTIN(SELECTmgr.manager_idFROMemployeesmgr);norowsselectedTips：注意Null值影響Tips：Null值與邏輯表達(dá)式Groupfunctionsignorenullvaluesinthecolumn:TheNVLfunctionforcesgroupfunctionstoincludenullvalues:SELECTAVG(commission_pct)FROMemployees;SELECTAVG(NVL(commission_pct,0))FROMemployees;Tips：Null值對(duì)Group函數(shù)的影響12Tips：避免隱式數(shù)據(jù)類型轉(zhuǎn)換REMREMImplicitDataTypeConversion(NUMBER)REMselect*fromemployeewheretelnoREMREMBindVariableisofCorrectdatatypeREMselect*fromemployeewheretelno=;隱式數(shù)據(jù)類型轉(zhuǎn)換容易導(dǎo)致如下問題:BadPlansbecauseindexcannotbeusedBadPlansbecausepartitionpruningmaynottakeplace1722ErrorsifthereisnonnumericalcharactersIncreasedresourceusageconvertingdataPoorcardinalityestimatesTips：盡可能避免非等值Join（NonEquiJoins）REMREMEquiJoinselectcount(*)fromtickets,bookingswheretickets.tdate=bookings.enddate;REMREMFuzzyjoinselectcount(*)fromtickets,bookingswheretickets.tdatebetweenbookings.startdateandbookings.enddate;Non-equijoinspresentanumberofchallengesMassivedataexpansion?whichmeansmorerows?whichmeanmoreCPUneeded?whichmeanspotentiallyslowDifficultgettinggoodjoincardinalityestimatesDifficultinusingparallelandhashbasedjointechniquesTips：注意數(shù)據(jù)邊界問題提綱數(shù)據(jù)庫空間管理數(shù)據(jù)倉庫物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)加載并行執(zhí)行開發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）索引管理統(tǒng)計(jì)信息收集77Tips：ExadataIndexDesignIndexingwithExadatahasbecomeacontroversialandmisunderstoodissueIndexingisverymuchanOLTPtechniqueIndexmaintenanceisverydifficulttoscalewithlargedatasetsizesandevenharderwithRACIndexingslowsdownorspeedsupincrementalloadsandtransformationsCustomerssimplydonotbelieveyouwhenyousuggestrunningwithoutindexesIfaqueryretrieves10rowsfroma10millionrowtable,whatisthecorrectaccessmethod?INDEXIfaqueryretrieves9millionrowsfroma10millionrowtable,whatisthecorrectaccessmethod?SCANShouldIReallyUseAnIndex?Supposetheyranitorexplainplannedit--andsawthatplan."StupidstupidCBO"theysay--"Ihaveindexes,whywon'titusethem.Weallknowtha