2024年-Exadata一体机最佳实践

娄恒:heng.lou@APACExadataspecialistBOCACRM数据库物理模型和开发最佳实践提纲数据库空间管理数据仓库物理表设计（表压缩、表分区）数据加载并行执行开发注意事项（集合、关联操作）索引管理统计信息收集2©2010OracleCorporation–ProprietaryandConfidentialTips1:设置合适的DB_Block_Size对Exadata而言，MAX_IO_SIZE缺省为1MB所以推荐DB_BLOCK_SIZE设置为8192同时推荐DB_FILE_MULTI_BLOCK_READ_COUNT设置为128因为_MAX_IO_SIZE=DB_FILE_MULTI_BLOCK_READ_COUNT*DB_block_size。3©2010OracleCorporation–ProprietaryandConfidentialTips：采用locallymanaged,Bigfile创建表空间4Tips：选择表空间合适的Extent管理方法采用Auto-Allocate方式，Oracle自动管理表的Extent的大小，开始时初始的Extent大小为64KB(除Partition表外)，当表或索引的段(Segment)大小超过1MB，Extent以1MB为单位增长段空间，一旦段大小超过64MB，Extent以8MB为单位增长段空间。采用Uniform方式，所有段的Extent大小都是统一大小。对于Auto-Allocate方式和Uniform方式的选择，Oracle推荐采用Auto-Allocate作为Extent的管理方式。5©2010OracleCorporation–ProprietaryandConfidentialSalesTableUniformExtent：数据并行加载特点假设有4个并行进程进行数据加载，Uniformextend大小为8MB当多块加载后，将会有很多未被填满的Extent存在，也就是说Extent中可能会有很多空间空洞在表扫描时，每个空间空洞会被扫描到，造成IO的浪费6©2010OracleCorporation–ProprietaryandConfidentialAuto-AllocateExtent：数据并行加载特点假设有4个并行进程进行数据加载，initial&nextextend大小为8MBSalesTable当加载完成后，最后一些Extent大小会比其他Extent小，但是所有Extent都会被填满每个表或分区扫描将至扫描数据，而没有空闲空间被扫描采用Auto-allocate的优点在于大数据加载时，有最少的空间浪费，因为最后加载的Extent会被Trim到64KB的整数倍的大小，所以几乎没有太大的空间浪费，同时表扫描时也可以提高IO效率采用Auto-Allocate方式时，在创建分区表时，可以自动支持LargeExtent（8MBExtent），而对于uniform方式，则无法支持7©2010OracleCorporation–ProprietaryandConfidentialTips：创建多个Bigfile表空间存储不同的分区在对大的Partition表进行并行数据加载时，应该尽可能避免文件头块争用（FileHeaderBlockercontention）情况出现。文件头块争用（FileHeaderBlockercontention）可以在AWR报告中检查“gcbufferbusyenqueuewaitevent”。或者检查“bufferbusywaits”的统计信息，以判断是否存在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';为了避免文件头争用情况，可以采用多个Bigfiletablespace来均匀存放Partitiontable。例如：下面语句使用4个表空间来均匀存放128个Subpartition表：8©2010OracleCorporation–ProprietaryandConfidentialTips：对于分区表（Partition）加载，启用LargeExtent推荐大的对象使用LargeExtent方式，以便减少Extent的数量。当设置CELL_PARTITION_LARGE_EXTENTS=TRUE时，所有分区表在创建时，将自动启动LargeExtent模式（8MB）,即创建时InitialExtent大小为8MB。限制条件：CELL_PARTITION_LARGE_EXTENTS仅适用于：分区表、locallymanaged和AUTO-ALLOCATE的表空间。不适合于：非分区表、Uniform的表空间的对象。9©2010OracleCorporation–ProprietaryandConfidentialTips：针对非分区表（Non-Partition）加载，使用LargeExtent如果对于非分区表，想使用LargeExtent(8MB)。需要在Createtable的DDL中设置INITIAL和NEXT子句。当对非分区表进行并行数据加载时，数据在Temp段中被生成，然后再合并到要加载的数据表的Extent中，缺省按照64KBExtent大小进行数据增长。但从开始，在加载数据时（Load），NEXT用来控制新的Extent的大小。所以对于大的非分区表加载，可以直接将INITIAL和NEXT都设置为8MB。SQL如下：CreateTablesales(.....)parallelstorage(INITIAL8MNEXT8M)(........)10©2010OracleCorporation–ProprietaryandConfidentialTips：关闭deferred_segment_creation（段延迟创建）功能从11gR2开始，当在LocallyManagedTablespace上，创建一个非分区表时，缺省情况表的空间分配会启用延迟分配方式，即当首行被Insert到表中时，表的Extent才会被逐渐分配。建议关闭延迟空间空间分配，可以通过如下SQL关闭：Altersystemsetdeferred_segment_creation=FALSEscope=both11©2010OracleCorporation–ProprietaryandConfidentialTips:空间回收ShrinkingSegmentsHWMHWMHWM ALTERTABLEemployeesSHRINKSPACECOMPACT;1 ALTERTABLEemployeesSHRINKSPACE;2DMLoperationsandqueriescanbeissuedduringcompaction.DMLoperationsareblockedwhentheHWMisadjusted.12©2010OracleCorporation–ProprietaryandConfidentialShrinkingSegmentsbyUsingSQLALTER…SHRINKSPACE[CASCADE]TABLE[OVERFLOW]INDEXMATERIALIZEDVIEWMATERIALIZEDVIEWLOGMODIFYPARTITIONMODIFYSUBPARTITIONMODIFYLOBALTERTABLEemployeesSHRINKSPACECASCADE;ALTERTABLEemployeesENABLEROWMOVEMENT;12ALTERTABLEemployeesMODIFYLOB(resume)(SHRINKSPACE);3ALTERTABLEemployeesOVERFLOWSHRINKSPACE;413©2010OracleCorporation–ProprietaryandConfidential提纲数据库空间管理数据仓库物理表设计（表压缩、表分区）数据加载并行执行开发注意事项（集合、关联操作）索引管理统计信息收集14©2010OracleCorporation–ProprietaryandConfidentialTips：数据仓库设计一个典型的数据仓库是一个ManyRowsSystem，但经常会被用FewRowsSystem的方式来开发(RowByRow)在大多数情况下，ManyRowsSystem还是FewRowsSystem可以通过SQL语句的执行频度决定：1/Sec代表manyrows1000/Sec代表fewrows备注：在ManyRowsSystem中，可能存在一种误导：批量集合操作（SetBasedOperation）通过RowByRow的操作方式开发效率更高15©2010OracleCorporation–ProprietaryandConfidentialTips：数据仓库物理模型设计原则ManyRows并行（Parallelism）

分区（Partitioning）压缩（Compression）

集合批量操作（Set-basedtechniques）

通过数据转换代替实现数据更新（Datamodifiedbytransformation）

FewRows索引（Indexdesign）

物化视图（Materializedviewsandaggregates）

16©2010OracleCorporation–ProprietaryandConfidentialTips：数据压缩考虑压缩技术是数据仓库物理设计最重要的因素之一压缩的影响:极大加速表扫描速度、减少磁盘空间占用、加速数据备份但是，压缩对大大降低UPDATE和DELETE的效率一个好的数据仓库模型，对于压缩表而言，将只采用Append方式操作，避免采用修改（Update、Delete、Merge）方式对数据的操作，以保证对压缩表操作的效率但是如果一些经常更新的大表确实有压缩的需求，可以考虑使用OLTP压缩技术。无论HCC压缩还是OLTP压缩，其本质都是去重，只是其算法上有些差异。因而如果在数据加载时，对数据预先排序，那么可带来明显的压缩比率的提升。一般来说，压缩级别QUERYHIGH既可以得到一个理想的压缩比率，也能提供很好的查询性能，可以考虑作为初始测试的压缩级别.17©2010OracleCorporation–ProprietaryandConfidentialTips：EHCC混合列压缩EHCC(ExadataHybridColumnarCompression)不是完全的列压缩，而是混合列存储EHCC具备多种压缩类型QuerylowQueryhighArchivelowArchivehigh设计用于不频繁修改的数据设计用于低并发的场景支持数据库各种功能DMLs/DDLs,Partitioning,PQ,PDML,Onlineredefinition,CTAS,IAS,SQLLoader,Externaltables,Context,MVsetc.支持索引只有在数据批量加载时被压缩18©2010OracleCorporation–ProprietaryandConfidentialTips：如何创建EHCC表CTAS(createtableasselect)createtablefoocompressforqueryasselect*frombar1;IDL(insertdirectload)createtablefoocompressforarchivelow;insert/*+APPEND*/intofooselect*frombar2;压缩可以在Segment一级指定：每个分区可以有不同的压缩类型例如：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;19©2010OracleCorporation–ProprietaryandConfidentialTips：动态启用或停用表的EHCCExistingtables/partitionscanbeconvertedtouseEHCCaltertablebarmovecompressforqueryhighaltertableordersmodifypartitionp1movecompressforarchivelowNewdataloadedinexistingtables/partitionscangotoEHCCblocks,keepingtheexistingdataasisaltertablebarcompressforqueryhighaltertableordersmodifypartitionp3compressforquerylowEHCCcanbedisabledaltertablebarnocompressaltertablebarmovenocompressOnlineredefinitionpackagecanbeusedforenabling/disablingEHCC20©2010OracleCorporation–ProprietaryandConfidentialTips：分区(Partition)设计目的数据管理Exchangedatain/outBreakdownoperationsintosmallerpieces查询优化PartitionpruningHashbasedjoinsandsorts21©2010OracleCorporation–ProprietaryandConfidentialTips：数据仓库分区（Partition）策略一级分区：首先按日期对大表进行分区主要目标是启动分区修剪（PartitionPruning），简化数据管理主要对于日期字段进行Range或interval分区选择大部分查询查询用到的日期字段作为分区列这个日期字段（用于分区列）应该是不易变（不被更新）二级子分区（Subpartition）可以按照：

Hash分区，更好地支持join和SortRANGE或LIST分区，更好的支持分区修剪（PartitionPruning）

22©2010OracleCorporation–ProprietaryandConfidentialTips:定义合理的Hash分区数量Oracle推荐大的分区表使用Hash分区作为分区方法，为了保证数据在HashPartition间均匀分布，Hash分区的数量建议是2的指数幂，或者设置Partition数量为CPU数量的2倍。然而每个hashPartition应该至少不小于16MB。小于16MB将在并行执行下不会有较好的扫描效率。首先考虑分区数是2的整数幂的情况：当Oracle的分区数从2个变为4个，Oracle并不需要将所有数据重新打乱，而是将原有的2个分区每个都一分为二。同样的道理，如果将分区数设置为8，Oracle会将原有的4个分区一分为二。Oracle的HASH分区就像是一棵大的二叉树。每个分区就相当于二叉树的一个叶节点。二叉树的第一层，只有一个根节点，对应只有1个分区的情况。二叉树的第二层，两个叶节点，对应2个分区的情况。二叉树的第三层，4个叶节点，对应4个分区的情况。二叉树的第n层，2^(n-1)个叶节点，对应2^(n-1)个分区情况。23©2010OracleCorporation–ProprietaryandConfidentialTips：使用Partition-wisejoins对于两个大表关联操作，推荐使用partiton-wisejoins来减少并行进程间数据交换，减少查询的执行时间。对于使用fullpartition-wisejoin，两个关联的表必须是相同的Partition键值（即两个表必须有相同的Partition列、相同的Partition方法、相同的Partition数量）。如下是采用partiton-wisejoins的两个表关联的执行计划，Sales表和Customers表具有相同的并行度、HashPartition方法（Cust_id列作为HashPartitionKey）、Join列是PartitionKey。PartitionHashAllabove在Join方法之上，说明这是一个partition-wisejoin24©2010OracleCorporation–ProprietaryandConfidentialSELECTsum(amount_sold)FROMsaless,customercWHEREs.cust_id=c.cust_id;Bothtableshavethesamedegreeofparallelismandarepartitionedthesamewayonthejoincolumn(cust_id)SalesRangepartitionMay18th2008CustomerHashPartitionedSubpart1Alargejoinisdividedintomultiplesmallerjoins,eachjoinsapairofpartitionsinparallelPart1Subpart2Subpart3Subpart4Part2Part3Part4Subpart2Subpart3Subpart4Subpart1Part1Part2Part3Part4PartitionWisejoin对于partition-wisejoin而言，查询的并行度必须是等于或成倍于表分区的数量25SalesTableSALES_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：分区裁剪（PartitionPruning）SALES_Q4_1998SALES_Q1_1999SALES_Q2_1999SALES_Q3_1999SALES_Q4_1999SALES_Q1_2000Onlythe4relevantpartitionsareaccessed26Tips：如何检查分区修剪是否生效SampleplanOnly4partitionsaretouched–9,10,11,&12SALES_Q1_1999,SALES_Q2_1999,SALES_Q3_1999,SALES_Q4_199927©2010OracleCorporation–ProprietaryandConfidentialTablePartition1Partition5Partition10Sub-part1Sub-part2Sub-part1Sub-part2Sub-part1Sub-part2::129101920Tips：如何检查分区修剪是否生效SimpleQuery:SELECTCOUNT(*)FROMRHP_TABWHERECUST_ID=9255ANDTIME_ID=‘2008-01-01’;Overallpartition#rangepartition#Sub-partition#28©2010OracleCorporation–ProprietaryandConfidential提纲数据库空间管理数据仓库物理表设计（表压缩、表分区）数据加载并行执行开发注意事项（集合、关联操作）索引管理统计信息收集29©2010OracleCorporation–ProprietaryandConfidentialTips：使用ExternalTable进行数据加载Oracle推荐使用ExternalTable方式加载数据，而不第一推荐SQL*Loader，因为SQL*Loader做并行数据加载时，数据首先被加载到TEMPExtent中，只有在transaction被Commit时，TempExtent会被合并到实体表的段（Segment）中，而在原来表中的部分满的Extent（partiallyfullextents）会被跳过，所以到导致空间浪费和性能浪费。所以对于大量加载的Partition表，这样可能会导致较大的空间浪费情况。采用ExternalTable方式加载时，会有如下好处：FullusageofSQLcapabilitiesdirectlyonthedataAutomaticuseofparallelcapabilitiesNoneedtostagethedataagaininthedatabaseBetterallocationofspacewhenstoringdataHighwatermarkbrokeringAutoallocatetablespacewilltrimextentsaftertheloadAdditionalcapabilitieslikeTheusageofdatapumpfilesTheusageofpre-processing30©2010OracleCorporation–ProprietaryandConfidentialExternalTable31©2010OracleCorporation–ProprietaryandConfidentialTips：ExternalTable预处理允许文件在加载过程中被自动化预处理例如：对压缩文件进行解压缩、排序等预处理不支持对文件的自动的小颗粒化需要提供多份文件，文件的数量决定了处理的并行度需要授权对目录的读和执行的权限（Grantread,executeprivilegesdirectories）CREATETABLEsales_external(…)ORGANIZATIONEXTERNAL(TYPEORACLE_LOADERDEFAULTDIRECTORYdata_dir1ACCESSPARAMETERS(RECORDSDELIMITEDBYNEWLINE

PREPROCESSORexec_dir:'gunzip'OPTIONS'-c' FIELDSTERMINATEDBY'|') LOCATION(…));32©2010OracleCorporation–ProprietaryandConfidentialTips：直接路径加载（DirectPathLoad）在每次IO异步请求时，数据被直接写入数据库存储CTAS语句总是使用DirectPath在Insert

AsSelect语句中使用APPEND暗示方法也是DirectPathInsert/*+APPEND*/intoSalespartition(p2)Select*Fromext_tab_for_sales_data;在一个对象上，只有一个directpath操作能够起作用通过指定一个表的分区表的名字方法，可以同时将多个DirectPach加载操作到一个表33©2010OracleCorporation–ProprietaryandConfidentialTips：并行加载（ParallelLoad）为了确保DirectPath加载成为并行操作，需要通过如下方式：在Hint中指定并行度、或者在表上指定并行度通过AlterSession语句启动会话级并行当并行度被指定后，CTAS会自动并行化加载IAS不会自动并行化，他需要parallelDML的支持：ALTERSESSIONENABLEPARALLELDML;34©2010OracleCorporation–ProprietaryandConfidentialTips：使用Paralleldirectpath数据加载使用“directpathload”可以带来很好的数据加载性能，因为数据会绕过BufferCache，直接写入数据库存储。CTAS总会使用“directpathload”，但是‘InsertAsSelect’(IAS)并不是总会使用“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”以并行方式运行，可以以下两种方式设置并行度：1）在CTAS和IAS中加入PARALLELhint；2）在Externaltable和目标加载Table上，设置PARALLEL子句。一旦设置了并行度，CTAS将自动执行并行化的“directpathload”，而IAS将不会。为了让IAS能够并行化执行“directpathload”，出了设置并行度外，还必须通过alterthesessiontoenableparallelDML。SQL如下：35©2010OracleCorporation–ProprietaryandConfidentialTips：在数据加载时，对于常访问的字段，可以进行排序，以便增加Storageindex的效率对在使用INSERT/*+APPEND*/做数据加载时，可以对查询中Where条件中使用较多的列进行OrderBy排序，以便增加数据压缩率和Storageindex查询效率。Selectname,value/1024/1024asstat_valuefromv$mystats,v$statnamenWhereS.Statistic#=n.statistic#andnamelike‘%cellphysical%’Insertintotableaselect*fromext_tableaorderbycol1,col2…可以通过查询v$mystat,v$statname视图，通过看"cellphysicalI0bytessavedbystorageindex"统计值，来检查多少I/O被减少。SQL如下：36©2010OracleCorporation–ProprietaryandConfidentialTips：通过表分区交换做数据加载(Partitionexchangeload)Oracle建议对大的数据表采用分区表，分区表的好处之一就是可以通过分区交换方式（PartitionExchange）进行数据加载，这样可以最小化的对业务影响情况下进行快速数据加载。PartitionExchange可以允许用户交换非分区表数据到一个表的分区中。分区交换命令并不真正移动两个表的数据，而是简单的Update数据字典信息。由于没有进行数据移动，所以数据非分区表需要与分区表有相同的存储结构（存储在合适的表空间、相同的ExtentSize）。SQL如下：CreateTabletmp_sales2(.....)

parallelstorage(INITIAL8MNEXT8M)tablespacemain_fact_tbs...........AltertableSalesexchangepartitionp2withtabletmp_sales2includingindexeswithoutvalidation;37©2010OracleCorporation–ProprietaryandConfidentialSalesTableMay22nd2008May23rd2008May24th2008May18th2008May19th2008May20th2008May21st2008DBA1.Createexternaltableforflatfiles5.AltertableSalesexchangepartitionMay_24_2008withtabletmp_sales2.UseCTAScommandtocreatenon-partitionedtableTMP_SALESTmp_salesTableSalesTableMay22nd2008May23rd2008May24th2008May18th2008May19th2008May20th2008May21st2008Salestablenowhasallthedata3.Createindexes4.GatherStatisticsTmp_salesTableTips：PartitionExchangeloading操作过程

38提纲数据库空间管理数据仓库物理表设计（表压缩、表分区）数据加载并行执行开发注意事项（集合、关联操作）索引管理统计信息收集39©2010OracleCorporation–ProprietaryandConfidentialTips：并行执行的适用情况并行执行是Exadata最强大的能力之一，充分利用Exadata的并行计算能力可以极大提高系统利用率。但是我们必须要确认并行使用的时机，以及并行使用的程度。

并行适用场景：并行执行应该在所有的资源密集型操作中被使用，例如：复杂查询、大量数据处理DML、大表建索引、收集统计信息、大量数据加载等。只有在表数据小于64MB或者同时有上百个并发用户执行操作时，不建议使用并行执行操作。并行不适用场景：短交易（几秒钟或更少）。在这些环境中并行执行是没有什么用处，因为协调并行执行服务器会增加相关的成本，这种协调的成本可能超过并行带来的好处。已经大量使用CPU、内存或I/O资源的环境。并行执行旨在利用其它可用的硬件资源，如果没有这样的资源可用，那么并行执行不产生任何效益，并且可能损害性能。40©2010OracleCorporation–ProprietaryandConfidentialTips：并行执行工作过程用户连接到数据库User后台进程被派生当用户发出一个并行SQL，后台进程就变成为QC（QueryCoordinator）QC从并行进程组中获得Parallelservers，然后QC分配任务给Parallelservers进程Parallelservers–是一个独立的Session，从并行进程组中分配，并完成指定的具体任务Parallelservers通过SharedPool的内存传递通信消息，与QC进程和并行进程进行互相通信MessagesQCconnectionParallelserverconnection41ProducersConsumersQuerycoordinatorP1P2P3P4Hashjoin会首先选择小表作为驱动表，进行扫描，在这个例子中Customers表示小表，4个Producer并行进程并行扫描，并且返回就过给Consumers进程P8P7P6P5SALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行执行的工作机制42ProducersConsumersQuerycoordinatorP1P2P3P4一旦4个Producer进程完成了小表的扫描，他们开始对大表（Sales表）进行并行扫描，并返回结果给Consumer进程P8P7P6P5SALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行执行的工作机制43ProducersConsumersP1P2P3P4P8P7P6P5一旦Consumer进程收到了两个表的数据，他们开始做并行的Join操作，一旦完成join操作，就将结果返回给QC进程QuerycoordinatorSALESTableCUSTOMERSTableSELECTc.cust_last_name, s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;并行执行的工作机制44SELECT c.cust_last_name,s.time_id,s.amount_soldFROMsaless,customerscWHEREs.cust_id=c.cust_id;QueryCoordinatorProducersProducersConsumersConsumersTips：检查并行执行情况45©2010OracleCorporation–ProprietaryandConfidentialselectsum(revenue),storefromline_itemsWhereprofit(price,units)>

0.2orderbystoreDataonDiskQueryServersscanscanscansortA-KsortL-SsortT-Zdispatchwork;assembleresultsProducersorscannersConsumersOrAggregators)Coordinator并行进程的任务46Tips：不同操作并行方式–Groupby,Orderby进程1进程2进程3进程4进程5进程6进程1进程2进程3进程4进程5进程6Hash分布Hashkey1Hashkey2Hashkey3Hashkey4Hashkey5Hashkey6进程1进程2进程3进程4进程5进程6进程1进程2进程3进程4进程5进程6Range分布A--HI--MN--RS--UV--XY--ZGroupByOrderByHash分区效率最高Range分区效率最高47Tips:数据分布的例子selectcount(*)fromyellowy,greengwherey.deptno=g.deptno48Tips：并行进程中不同的数据分布方式分布方式说明Hash,Hash使用Hash函数映射Join字段，映射完成后，每个查询引擎执行Join，建议Join表size相近，使用hash-join或者sortmergejoinBroadcast,None外表的行广播到每个查询进程，内表随机分区，建议外表size大大小于内表。通用规则：innertablesize*numberofqueryservers>outertablesizeNone,Broadcase内表的行广播到每个查询进程，建议内表的size大大小于外表。通用规则：innertablesize*numberofqueryservers<outertablesizePartition,None使用内表的分区方式映射外表的行，内部表必须在join字段上分区。建议外表的分区数量大约等于查询进程数。注意：在内部表没有分区或者不是join字段情况下被忽略None,Partiition和PartitionNone正好相反None,None查询引擎在join分区字段上连接，必须在join字段上分区49Tips：并行度设置建议采用默认传统手工管理方式(PARALLEL_DEGREE_POLICY=LIMITED/MANUAL)。在这种情况下需要对数据量有一个预估，然后设置的并行度应该给每个进程分配到的至少为上百兆数据。一个典型的初始预估模式：200MB以下的小对象：不并行200MB-5GB的对象：并行度45GB以上的大对象：并行度32*最终采用的并行度需要经过测试才能确定50©2010OracleCorporation–ProprietaryandConfidentialTips：启用并行执行1.在表上执行并行

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级使用Hint启用并行3.在会话（Session）上，启用并行51©2010OracleCorporation–ProprietaryandConfidentialTips：如何检查并行执行的情况1.检查系统并行进程使用情况：

2.检查系统各个并行任务分布情况

3.检查并行的系统统计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%‘;52©2010OracleCorporation–ProprietaryandConfidentialTips：并行服务相应的参数参数名值说明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.53©2010OracleCorporation–ProprietaryandConfidential提纲数据库空间管理数据仓库物理表设计（表压缩、表分区）数据加载并行执行开发注意事项（集合、关联操作）索引管理统计信息收集54©2010OracleCorporation–ProprietaryandConfidentialTips：表Join方法（1）--SortMergeJoin1.排序合并连接(sortmergejoin)：排序合并连接(sortmergejoin):是一个集合操作,分类合并连接应该在索引不能用于查询的情况下考虑.在于把两个大表读入内存并进行排序的成本，成本计算如下：costofmergejoin=accesscostofA+accesscostofB+(sortcostofA+sortcostofB)适用于查询整体返回大量结果集，两个大表做连接，且表已经排过序的情况下。当两个表已经排过序时，使用排序合并连接的性能可能会优于散列连接。HASH_AREA_SIZE和SORT_AREA_SIZE设置过小，可能会导致优化器避开散列连接而选择排序合并连接。使用HINTS：use_merge，可以是表使用SortMergeJoin关联。55©2010OracleCorporation–ProprietaryandConfidentialTips：表Join方法（2）--NestedLoopJoin2.嵌套循环连接(nestedloopjoin)：嵌套循环连接(nestedloopjoin):是一个行操作,这种连接是OLTP应用中最常见的连接操作,并且通常很有效，但对于批量数据操作往往效率不高。因为此连接方法高度利用了索引。在于外表返回的每一行都要在内表中进行匹配的成本，成本计算如下：costofnestedloop=accesscostofouter+(numberofrowsfromouter*accesscostofinner)适用于外表有效基数较小，内表连接字段含有索引，且查询整体返回结果集不太大（小于1万行）的情况下。使用HINTS：use_nl可以使执行计划按照NestedLoop方式执行。56©2010OracleCorporation–ProprietaryandConfidentialTips：表Join方法（3）--HashJoin3.散列连接(hashjoin)：几乎是使用被连接表的全表扫描而来完成的.散列连接对于大表的相互连接,或者一个小表和一个超大型表的连接.在于将小表读入内存分成若干散列表，然后由大表对每个散列表都进行一次匹配的成本，成本计算如下：costofhashjoin=accesscostofsmaller+(accesscostofbigger*numberofhashpartitionsofsmaller)适用于查询整体返回大量结果集，且有较小的连接表可以放入内存作为散列表的情况下。适用散列连接要注意HASH_AREA_SIZE要足够大，可以容下散列表。如果散列表无法完全放入内存，要设置较大的临时段，从而尽量提高I/O性能。HINTS：use_hash57©2010OracleCorporation–ProprietaryandConfidentialTips:修改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*fromtable1Tosetbasedprocessing58Tips：改写Delete操作altersessionenableparalleldml/deletefromtx_logwhereSymbol=‘JAVA’

/commit/ createtabletx_log_newnologgingparallelcompressforalloperationsasselect*fromtx_logwhereSymbol!=‘JAVA’

/altertabletx_logrenametotx_log_old/altertabletx_log_newtotx_log/59Tips：改写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/60Tips：对Merge的改写61Tips：多表Insert

UnconditionalINSERT

ALLINSERTALL

INTOsal_historyVALUES(EMPID,HIREDATE,SAL)

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

salarySAL,manager_idMGRFROMemployees

WHEREemployee_id>200;8rowscreated.

62©2010OracleCorporation–ProprietaryandConfidentialTips：多表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.

63Tips：多表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.64Tips：多表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.

65Tips：避免关联子查询Correlatedsubqueriesareusedforrow-by-rowprocessing.Eachsubqueryisexecutedonceforeveryrowoftheouterquery.GETcandidaterowfromouterqueryEXECUTEinnerqueryusingcandidaterowvalueUSEvaluesfrominnerquerytoqualifyordisqualifycandidaterow66©2010OracleCorporation–ProprietaryandConfidential关联子查询Thesubqueryreferencesacolumnfromatableintheparentquery.

SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator (SELECTcolumn1,column2FROMtable2WHEREexpr1= .expr2);outerouter67©2010OracleCorporation–ProprietaryandConfidentialSELECTlast_name,salary,department_idFROMemployeesouterWHEREsalary>

(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);Tips：改写关联子查询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_s