Exadata一体机最佳实践

娄恒:[e-mail]BOCACRM数据库物理模型和开发最佳实践提纲•数据库空间管理•数据仓库物理表设计（表压缩、表分区）•数据加载•并行执行•开发注意事项（集合、关联操作）•索引管理•统计信息收集2Tips1:设置合适的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。Tips：采用locallymanaged,Bigfile创建表空间Tips：选择表空间合适的Extent管理方法•采用Auto-Allocate方式，Oracle自动管理表的Extent的大小，开始时初始的Extent大小为64KB(除Partition表外)，当表或索引的段(Segment)大小超过1MB，Extent以1MB为单位增长段空间，一旦段大小超过64MB，Extent以8MB为单位增长段空间。•采用Uniform方式，所有段的Extent大小都是统一大小。对于Auto-Allocate方式和Uniform方式的选择，Oracle推荐采用Auto-Allocate作为Extent的管理方式。SalesTableUniformExtent：数据并行加载特点假设有4个并行进程进行数据加载，Uniformextend大小为8MB当多块加载后，将会有很多未被填满的Extent存在，也就是说Extent中可能会有很多空间空洞在表扫描时，每个空间空洞会被扫描到，造成IO的浪费Auto-AllocateExtent：数据并行加载特点假设有4个并行进程进行数据加载，initial&nextextend大小为8MBSalesTable当加载完成后，最后一些Extent大小会比其他Extent小，但是所有Extent都会被填满每个表或分区扫描将至扫描数据，而没有空闲空间被扫描•采用Auto-allocate的优点在于大数据加载时，有最少的空间浪费，因为最后加载的Extent会被Trim到64KB的整数倍的大小，所以几乎没有太大的空间浪费，同时表扫描时也可以提高IO效率•采用Auto-Allocate方式时，在创建分区表时，可以自动支持LargeExtent（8MBExtent），而对于uniform方式，则无法支持Tips：创建多个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’)));selectp1File#,p2Block#,p3ReasonCodefromv$session_waitwhereevent='bufferbusywaits';•为了避免文件头争用情况，可以采用多个Bigfiletablespace来均匀存放Partitiontable。例如：下面语句使用4个表空间来均匀存放128个Subpartition表：Tips：对于分区表（Partition）加载，启用LargeExtent•推荐大的对象使用LargeExtent方式，以便减少Extent的数量。当设置CELL_PARTITION_LARGE_EXTENTS=TRUE时，所有分区表在创建时，将自动启动LargeExtent模式（8MB）,即创建时InitialExtent大小为8MB。•限制条件：-CELL_PARTITION_LARGE_EXTENTS仅适用于：分区表、locallymanaged和AUTO-ALLOCATE的表空间。-不适合于：非分区表、Uniform的表空间的对象。Tips：针对非分区表（Non-Partition）加载，使用LargeExtent•如果对于非分区表，想使用LargeExtent(8MB)。需要在Createtable的DDL中设置INITIAL和NEXT子句。•当对非分区表进行并行数据加载时，数据在Temp段中被生成，然后再合并到要加载的数据表的Extent中，缺省按照64KBExtent大小进行数据增长。但从11.1.0.7开始，在加载数据时（Load），NEXT用来控制新的Extent的大小。所以对于大的非分区表加载，可以直接将INITIAL和NEXT都设置为8MB。SQL如下：CreateTablesales(.....)parallelstorage(INITIAL8MNEXT8M)(........)Tips：关闭deferred_segment_creation（段延迟创建）功能•从11gR2开始，当在LocallyManagedTablespace上，创建一个非分区表时，缺省情况表的空间分配会启用延迟分配方式，即当首行被Insert到表中时，表的Extent才会被逐渐分配。建议关闭延迟空间空间分配，可以通过如下SQL关闭：Altersystemsetdeferred_segment_creation=FALSEscope=bothTips:空间回收ShrinkingSegmentsHWMHWMHWMALTERTABLEemployeesSHRINKSPACECOMPACT;1ALTERTABLEemployeesSHRINKSPACE;2DMLoperationsandqueriescanbeissuedduringcompaction.DMLoperationsareblockedwhentheHWMisadjusted.提纲•数据库空间管理•数据仓库物理表设计（表压缩、表分区）•数据加载•并行执行•开发注意事项（集合、关联操作）•索引管理•统计信息收集14Tips：数据仓库设计•一个典型的数据仓库是一个ManyRowsSystem，但经常会被用FewRowsSystem的方式来开发(RowByRow)•在大多数情况下，ManyRowsSystem还是FewRowsSystem可以通过SQL语句的执行频度决定：-1/Sec代表manyrows-1000/Sec代表fewrows-备注：在ManyRowsSystem中，可能存在一种误导：批量集合操作（SetBasedOperation）通过RowByRow的操作方式开发效率更高Tips：数据仓库物理模型设计原则•ManyRows-并行（Parallelism）-分区（Partitioning）-压缩（Compression）-集合批量操作（Set-basedtechniques）-通过数据转换代替实现数据更新（Datamodifiedbytransformation）•FewRows-索引（Indexdesign）-物化视图（Materializedviewsandaggregates）Tips：数据压缩考虑•压缩技术是数据仓库物理设计最重要的因素之一•压缩的影响:-极大加速表扫描速度、减少磁盘空间占用、加速数据备份-但是，压缩对大大降低UPDATE和DELETE的效率•一个好的数据仓库模型，对于压缩表而言，将只采用Append方式操作，避免采用修改（Update、Delete、Merge）方式对数据的操作，以保证对压缩表操作的效率•但是如果一些经常更新的大表确实有压缩的需求，可以考虑使用OLTP压缩技术。•无论HCC压缩还是OLTP压缩，其本质都是去重，只是其算法上有些差异。因而如果在数据加载时，对数据预先排序，那么可带来明显的压缩比率的提升。•一般来说，压缩级别QUERYHIGH既可以得到一个理想的压缩比率，也能提供很好的查询性能，可以考虑作为初始测试的压缩级别.Tips：EHCC混合列压缩•EHCC(ExadataHybridColumnarCompression)不是完全的列压缩，而是混合列存储•EHCC具备多种压缩类型-Querylow-Queryhigh-Archivelow-Archivehigh•设计用于不频繁修改的数据•设计用于低并发的场景•支持数据库各种功能-DMLs/DDLs,Partitioning,PQ,PDML,Onlineredefinition,CTAS,IAS,SQLLoader,Externaltables,Context,MVsetc.•支持索引•只有在数据批量加载时被压缩Tips：如何创建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)enablerowmovementasselect*fromprev_orders;Tips：动态启用或停用表的EHCC-Existingtables/partitionscanbeconvertedtouseEHCC-altertablebarmovecompressforqueryhigh-altertableordersmodifypartitionp1movecompressforarchivelow-Newdataloadedinexistingtables/par