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

1、婁恒婁恒: APAC Exadata specialist 數(shù)據(jù)庫(kù)空間管理數(shù)據(jù)庫(kù)空間管理 數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)） 數(shù)據(jù)加載數(shù)據(jù)加載 并行執(zhí)行并行執(zhí)行 開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作） 索引管理索引管理 統(tǒng)計(jì)信息收集統(tǒng)計(jì)信息收集2Tips 1:設(shè)置合適的設(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)開(kāi) MAX_IO_SIZE = DB_FILE

2、_MULTI_BLOCK_READ_COUNT * DB_block_size。Tips：采用采用locally managed, Bigfile創(chuàng)建表空間創(chuàng)建表空間Tips：選擇表空間選擇表空間合適的合適的Extent管理方法管理方法采用Auto-Allocate方式，Oracle自動(dòng)管理表的Extent的大小，開(kāi)始時(shí)初始的Extent大小為64KB(除Partition表外)，當(dāng)表或索引的段(Segment)大小超過(guò)1MB，Extent以1MB為單位增長(zhǎng)段空間，一旦段大小超過(guò)64MB，Extent以8MB為單位增長(zhǎng)段空間。采用Uniform方式，所有段的Extent大小都是統(tǒng)一大小。對(duì)于A

3、uto-Allocate方式和Uniform方式的選擇，Oracle推薦采用推薦采用Auto-Allocate作為作為Extent的管理方式。的管理方式。Uniform Extent：數(shù)據(jù)并行加載特點(diǎn)：數(shù)據(jù)并行加載特點(diǎn)假設(shè)有4個(gè)并行進(jìn)程進(jìn)行數(shù)據(jù)加載，Uniform extend 大小為8MB當(dāng)多塊加載后，將會(huì)有很多未被填滿的Extent存在，也就是說(shuō)Extent中可能會(huì)有很多空間空洞在表掃描時(shí)，每個(gè)空間空洞會(huì)被掃描到，造成IO的浪費(fèi)Auto-Allocate Extent：數(shù)據(jù)并行加載特點(diǎn)：數(shù)據(jù)并行加載特點(diǎn)假設(shè)有4個(gè)并行進(jìn)程進(jìn)行數(shù)據(jù)加載，initial & next extend 大小

4、為8MB當(dāng)加載完成后，最后一些Extent大小會(huì)比其他Extent小，但是所有Extent都會(huì)被填滿每個(gè)表或分區(qū)掃描將至掃描數(shù)據(jù)，而沒(méi)有空閑空間被掃描采用Auto-allocate的優(yōu)點(diǎn)在于大數(shù)據(jù)加載時(shí)，有最少的空間浪費(fèi)，因?yàn)樽詈蠹虞d的Extent會(huì)被Trim到64KB的整數(shù)倍的大小，所以幾乎沒(méi)有太大的空間浪費(fèi)，同時(shí)表掃描時(shí)也可以提高IO效率采用Auto-Allocate方式時(shí)，在創(chuàng)建分區(qū)表時(shí)，可以自動(dòng)支持Large Extent（8MB Extent），而對(duì)于uniform方式，則無(wú)法支持Tips：創(chuàng)建多個(gè)創(chuàng)建多個(gè)Bigfile表空間存儲(chǔ)不同的分區(qū)表空間存儲(chǔ)不同的分區(qū)在對(duì)大的在對(duì)大的Part

5、ition表進(jìn)行并行數(shù)據(jù)加載時(shí)，應(yīng)該盡可能避免文件頭塊爭(zhēng)用（表進(jìn)行并行數(shù)據(jù)加載時(shí)，應(yīng)該盡可能避免文件頭塊爭(zhēng)用（File Header Blocker contention）情況出現(xiàn)。文件頭塊爭(zhēng)用（）情況出現(xiàn)。文件頭塊爭(zhēng)用（File Header Blocker contention）可以在）可以在AWR報(bào)告中檢查報(bào)告中檢查“gc buffer busy enqueue wait event”?；蛘邫z查?；蛘邫z查“buffer busy waits”的的統(tǒng)計(jì)信息，以判斷是否存在統(tǒng)計(jì)信息，以判斷是否存在File Header Blocker contention，可以使用如下，可以使用如下SQL：

6、CREATE TABLE sales_composite(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE)PARTITION BY RANGE(sales_date)SUBPARTITION BY HASH(salesman_id) subpartitions 128store in (ts1, ts2, ts3, ts4)(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE(02/01/2000,MM/DD/YYYY)

7、,PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(03/01/2000,MM/DD/YYYY),PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(04/01/2000,MM/DD/YYYY),PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(05/01/2000,MM/DD/YYYY),PARTITION sales_may2000 VALUES LESS THAN(TO_DATE(06/01/2000,MM/DD/YYYY);select p1 F

8、ile #, p2 Block #, p3 Reason Code from v$session_wait where event = buffer busy waits;為了避免文件頭爭(zhēng)用情況，可以采用多個(gè)為了避免文件頭爭(zhēng)用情況，可以采用多個(gè)Big file tablespace來(lái)均勻存放來(lái)均勻存放Partition table。例如：下面語(yǔ)句使用例如：下面語(yǔ)句使用4個(gè)表空間來(lái)均勻存放個(gè)表空間來(lái)均勻存放128個(gè)個(gè)Subpartition表：表：Tips：對(duì)于分區(qū)表（對(duì)于分區(qū)表（Partition）加載，啟用）加載，啟用Large Extent推薦大的對(duì)象使用推薦大的對(duì)象使用Large Ext

9、ent方式，以便減少方式，以便減少Extent的數(shù)量。當(dāng)設(shè)置的數(shù)量。當(dāng)設(shè)置CELL_PARTITION_LARGE_EXTENTS=TRUE時(shí)，所有分區(qū)表在創(chuàng)建時(shí)，將自動(dòng)啟時(shí)，所有分區(qū)表在創(chuàng)建時(shí)，將自動(dòng)啟動(dòng)動(dòng)Large Extent模式（模式（8MB）,即創(chuàng)建時(shí)即創(chuàng)建時(shí)Initial Extent大小為大小為8MB。限制條件：限制條件：-CELL_PARTITION_LARGE_EXTENTS僅適用于：分區(qū)表、locally managed和AUTO-ALLOCATE的表空間。-不適合于：非分區(qū)表、Uniform的表空間的對(duì)象。Tips：針對(duì)非分區(qū)表（針對(duì)非分區(qū)表（Non-Partition）

10、加載，使用）加載，使用Large Extent如果對(duì)于非分區(qū)表，想使用Large Extent(8MB)。需要在Create table的DDL中設(shè)置INITIAL和NEXT子句。當(dāng)對(duì)非分區(qū)表進(jìn)行并行數(shù)據(jù)加載時(shí)，數(shù)據(jù)在Temp段中被生成，然后再合并到要加載的數(shù)據(jù)表的Extent中，缺省按照64KB Extent大小進(jìn)行數(shù)據(jù)增長(zhǎng)。但從11.1.0.7開(kāi)始，在加載數(shù)據(jù)時(shí)（Load），NEXT用來(lái)控制新的Extent的大小。所以對(duì)于大的非分區(qū)表加載，可以直接將INITIAL和NEXT都設(shè)置為8MB。SQL如下：Create Table sales(.) parallel storage (INITI

11、AL 8M NEXT 8M)(.)Tips：關(guān)閉關(guān)閉deferred_segment_creation（段延遲創(chuàng)建）功能（段延遲創(chuàng)建）功能從從11g R2開(kāi)始，當(dāng)在開(kāi)始，當(dāng)在Locally Managed Tablespace上，創(chuàng)建一個(gè)非分區(qū)表上，創(chuàng)建一個(gè)非分區(qū)表時(shí)，缺省情況表的空間分配會(huì)啟用延遲分配方式，即當(dāng)首行被時(shí)，缺省情況表的空間分配會(huì)啟用延遲分配方式，即當(dāng)首行被Insert到表中時(shí)，表的到表中時(shí)，表的Extent才會(huì)被逐漸分配。建議關(guān)閉延遲空間空間分配才會(huì)被逐漸分配。建議關(guān)閉延遲空間空間分配，可以通過(guò)如下，可以通過(guò)如下SQL關(guān)閉：關(guān)閉：Alter system set deferre

12、d_segment_creation = FALSE scope=bothTips : 空間回收空間回收Shrinking SegmentsHWMHWMHWMALTER TABLE employees SHRINK SPACE COMPACT;1ALTER TABLE employees SHRINK SPACE;2DML operations and queries can be issued during compaction.DML operations are blocked when the HWM is adjusted. Shrinking Segments by Using S

13、QLALTER SHRINK SPACE CASCADETABLE OVERFLOWINDEXMATERIALIZED VIEWMATERIALIZED VIEW LOGMODIFY PARTITIONMODIFY SUBPARTITIONMODIFY LOBALTER TABLE employees SHRINK SPACE CASCADE;ALTER TABLE employees ENABLE ROW MOVEMENT;12ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);3ALTER TABLE employees OVER

14、FLOW SHRINK SPACE;4 數(shù)據(jù)庫(kù)空間管理數(shù)據(jù)庫(kù)空間管理 數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)） 數(shù)據(jù)加載數(shù)據(jù)加載 并行執(zhí)行并行執(zhí)行 開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作） 索引管理索引管理 統(tǒng)計(jì)信息收集統(tǒng)計(jì)信息收集14Tips：數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)：數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)一個(gè)典型的數(shù)據(jù)倉(cāng)庫(kù)是一個(gè)一個(gè)典型的數(shù)據(jù)倉(cāng)庫(kù)是一個(gè)Many Rows System ，但經(jīng)常會(huì)但經(jīng)常會(huì)被用被用Few Rows System的方式來(lái)開(kāi)發(fā)的方式來(lái)開(kāi)發(fā)(Row By Row)在大多數(shù)情況下，在大多數(shù)情況下，Many Rows System還是還是Few Rows

15、System可以通過(guò)可以通過(guò)SQL語(yǔ)句的執(zhí)行頻度決定：語(yǔ)句的執(zhí)行頻度決定：- 1/Sec 代表 many rows - 1000/Sec代表 few rows - 備注：在Many Rows System中，可能存在一種誤導(dǎo)：批量集合操作（Set Based Operation）通過(guò)Row By Row的操作方式開(kāi)發(fā)效率更高Tips：數(shù)據(jù)倉(cāng)庫(kù)物理模型設(shè)計(jì)原則：數(shù)據(jù)倉(cāng)庫(kù)物理模型設(shè)計(jì)原則Many Rows- 并行（Parallelism） - 分區(qū)（Partitioning）- 壓縮（Compression） - 集合批量操作（Set-based techniques） - 通過(guò)數(shù)據(jù)轉(zhuǎn)換代替實(shí)現(xiàn)數(shù)

16、據(jù)更新（Data modified by transformation） Few Rows - 索引（Index design） - 物化視圖（Materialized views and aggregates） Tips：數(shù)據(jù)壓縮考慮：數(shù)據(jù)壓縮考慮壓縮技術(shù)是數(shù)據(jù)倉(cāng)庫(kù)物理設(shè)計(jì)最重要的因素之一壓縮技術(shù)是數(shù)據(jù)倉(cāng)庫(kù)物理設(shè)計(jì)最重要的因素之一壓縮的影響壓縮的影響: -極大加速表掃描速度、減少磁盤(pán)空間占用、加速數(shù)據(jù)備份-但是，壓縮對(duì)大大降低UPDATE和DELETE的效率一個(gè)好的數(shù)據(jù)倉(cāng)庫(kù)模型，對(duì)于壓縮表而言，將只采用一個(gè)好的數(shù)據(jù)倉(cāng)庫(kù)模型，對(duì)于壓縮表而言，將只采用Append方式操作方式操作，避免采用修改（

17、，避免采用修改（Update、Delete、Merge）方式對(duì)數(shù)據(jù)的操作，以保）方式對(duì)數(shù)據(jù)的操作，以保證對(duì)壓縮表操作的效率證對(duì)壓縮表操作的效率但是如果一些經(jīng)常更新的大表確實(shí)有壓縮的需求，可以考慮使用但是如果一些經(jīng)常更新的大表確實(shí)有壓縮的需求，可以考慮使用OLTPOLTP壓縮技術(shù)。壓縮技術(shù)。無(wú)論無(wú)論HCCHCC壓縮還是壓縮還是OLTPOLTP壓縮，其本質(zhì)都是去重，只是其算法上有些差壓縮，其本質(zhì)都是去重，只是其算法上有些差異。因而異。因而如果在數(shù)據(jù)加載時(shí)，對(duì)數(shù)據(jù)預(yù)先排序，那么可帶來(lái)明顯的壓如果在數(shù)據(jù)加載時(shí)，對(duì)數(shù)據(jù)預(yù)先排序，那么可帶來(lái)明顯的壓縮比率的提升?？s比率的提升。一般來(lái)說(shuō)，一般來(lái)說(shuō)，壓縮級(jí)別壓

18、縮級(jí)別QUERY HIGHQUERY HIGH既可以得到一個(gè)理想的壓縮比率，也既可以得到一個(gè)理想的壓縮比率，也能提供很好的查詢性能，可以考慮作為初始測(cè)試的壓縮級(jí)別能提供很好的查詢性能，可以考慮作為初始測(cè)試的壓縮級(jí)別. .Tips：EHCC 混合列壓縮混合列壓縮EHCC (Exadata Hybrid Columnar Compression) 不是完全的列壓縮，而是混不是完全的列壓縮，而是混合列存儲(chǔ)合列存儲(chǔ)EHCC具備多種壓縮類型具備多種壓縮類型-Query low-Query high-Archive low-Archive high設(shè)計(jì)用于不頻繁修改的數(shù)據(jù)設(shè)計(jì)用于不頻繁修改的數(shù)據(jù)設(shè)計(jì)用于低

19、并發(fā)的場(chǎng)景設(shè)計(jì)用于低并發(fā)的場(chǎng)景支持?jǐn)?shù)據(jù)庫(kù)各種功能支持?jǐn)?shù)據(jù)庫(kù)各種功能-DMLs/DDLs, Partitioning, PQ, PDML, Online redefinition, CTAS, IAS, SQL Loader, External tables, Context, MVs etc.支持索引支持索引只有在數(shù)據(jù)批量加載時(shí)被壓縮只有在數(shù)據(jù)批量加載時(shí)被壓縮Tips：如何創(chuàng)建：如何創(chuàng)建EHCC表表l CTAS (create table as select)- create table foo compress for query as select * from bar1;l IDL (i

20、nsert direct load)- create table foo compress for archive low;- insert /*+APPEND*/ into foo select * from bar2;l 壓縮可以在壓縮可以在Segment一級(jí)指定：一級(jí)指定：- 每個(gè)分區(qū)可以有不同的壓縮類型- 例如： create table orders (cid, pid, sid, price, discount, odate) partition by range (cid) (partition p1 values less than (100000) nocompress, p

21、artition p2 values less than (200000) compress for archive low, partition p3 values less than (300000) compress for query high, partition p4 values less than (maxvalue) compress for query low) enable row movement as select * from prev_orders;Tips：動(dòng)態(tài)啟用或停用表的：動(dòng)態(tài)啟用或停用表的EHCC- Existing tables/partitions c

22、an be converted to use EHCC- alter table bar move compress for query high- alter table orders modify partition p1 move compress for archive low- New data loaded in existing tables/partitions can go to EHCC blocks, keeping the existing data as is- alter table bar compress for query high- alter table

23、orders modify partition p3 compress for query low- EHCC can be disabled - alter table bar nocompress- alter table bar move nocompress- Online redefinition package can be used for enabling/disabling EHCCTips：分區(qū)：分區(qū)(Partition)設(shè)計(jì)目的設(shè)計(jì)目的 數(shù)據(jù)管理數(shù)據(jù)管理- Exchange data in/out - Break down operations into smaller

24、pieces 查詢優(yōu)化查詢優(yōu)化- Partition pruning - Hash based joins and sorts Tips ：數(shù)據(jù)倉(cāng)庫(kù)分區(qū)（：數(shù)據(jù)倉(cāng)庫(kù)分區(qū)（Partition）策略）策略一級(jí)分區(qū)：首先按日期對(duì)大表進(jìn)行分區(qū)一級(jí)分區(qū)：首先按日期對(duì)大表進(jìn)行分區(qū)-主要目標(biāo)是啟動(dòng)分區(qū)修剪（Partition Pruning），簡(jiǎn)化數(shù)據(jù)管理-主要對(duì)于日期字段進(jìn)行Range 或interval分區(qū)-選擇大部分查詢查詢用到的日期字段作為分區(qū)列-這個(gè)日期字段（用于分區(qū)列）應(yīng)該是不易變（不被更新）二級(jí)子分區(qū)（二級(jí)子分區(qū)（Subpartition）可以按照：）可以按照： -Hash分區(qū)，更好地支持jo

25、in和Sort-RANGE或LIST分區(qū)，更好的支持分區(qū)修剪（ Partition Pruning） Tips:Tips:定義合理的定義合理的HashHash分區(qū)數(shù)量分區(qū)數(shù)量Oracle推薦大的分區(qū)表使用推薦大的分區(qū)表使用Hash分區(qū)作為分區(qū)方法，為了保證數(shù)據(jù)在分區(qū)作為分區(qū)方法，為了保證數(shù)據(jù)在Hash Partition間均勻分布，間均勻分布，Hash分區(qū)的數(shù)量建議是分區(qū)的數(shù)量建議是2的指數(shù)冪，或者設(shè)置的指數(shù)冪，或者設(shè)置Partition數(shù)量為數(shù)量為CPU數(shù)量的數(shù)量的2倍。倍。然而每個(gè)然而每個(gè)hash Partition應(yīng)該至少不小于應(yīng)該至少不小于16MB。小于。小于16MB將在并行執(zhí)行下不會(huì)

26、有較好的掃描效率將在并行執(zhí)行下不會(huì)有較好的掃描效率。首先考慮分區(qū)數(shù)是首先考慮分區(qū)數(shù)是2的整數(shù)冪的情況：當(dāng)?shù)恼麛?shù)冪的情況：當(dāng)Oracle的分區(qū)數(shù)從的分區(qū)數(shù)從2個(gè)變?yōu)閭€(gè)變?yōu)?個(gè)，個(gè)，Oracle并不需要將所有數(shù)據(jù)重新打亂，而是將原有的并不需要將所有數(shù)據(jù)重新打亂，而是將原有的2個(gè)分區(qū)每個(gè)都一分個(gè)分區(qū)每個(gè)都一分為二。同樣的道理，如果將分區(qū)數(shù)設(shè)置為為二。同樣的道理，如果將分區(qū)數(shù)設(shè)置為8，Oracle會(huì)將原有的會(huì)將原有的4個(gè)分區(qū)個(gè)分區(qū)一分為二。一分為二。Oracle的的HASH分區(qū)就像是一棵大的二叉樹(shù)。每個(gè)分區(qū)就相當(dāng)于二叉樹(shù)的分區(qū)就像是一棵大的二叉樹(shù)。每個(gè)分區(qū)就相當(dāng)于二叉樹(shù)的一個(gè)葉節(jié)點(diǎn)。二叉樹(shù)的第一層，只

27、有一個(gè)根節(jié)點(diǎn)，對(duì)應(yīng)只有一個(gè)葉節(jié)點(diǎn)。二叉樹(shù)的第一層，只有一個(gè)根節(jié)點(diǎn)，對(duì)應(yīng)只有1個(gè)分區(qū)的個(gè)分區(qū)的情況。二叉樹(shù)的第二層，兩個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)情況。二叉樹(shù)的第二層，兩個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)2個(gè)分區(qū)的情況。二叉樹(shù)的個(gè)分區(qū)的情況。二叉樹(shù)的第三層，第三層，4個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)4個(gè)分區(qū)的情況。二叉樹(shù)的第個(gè)分區(qū)的情況。二叉樹(shù)的第n層，層，2(n-1)個(gè)葉個(gè)葉節(jié)點(diǎn)，對(duì)應(yīng)節(jié)點(diǎn)，對(duì)應(yīng)2(n-1)個(gè)分區(qū)情況。個(gè)分區(qū)情況。Tips：使用使用Partition-wise joins對(duì)于兩個(gè)大表關(guān)聯(lián)操作，推薦使用partiton-wise joins來(lái)減少并行進(jìn)程間數(shù)據(jù)交換，減少查詢的執(zhí)行時(shí)間。對(duì)于使用對(duì)于使用full par

28、tition-wise join，兩個(gè)關(guān)聯(lián)的表必須是，兩個(gè)關(guān)聯(lián)的表必須是相同的相同的Partition鍵值（即兩個(gè)表必須有相同的鍵值（即兩個(gè)表必須有相同的Partition列、相同的列、相同的Partition方法方法、相同的、相同的Partition數(shù)量）數(shù)量）。如下是采用partiton-wise joins的兩個(gè)表關(guān)聯(lián)的執(zhí)行計(jì)劃，Sales表和Customers表具有相同的并行度、Hash Partition方法（Cust_id列作為Hash Partition Key）、Join列是Partition Key。SELECT sum(amount_sold) FROM sales s,

29、customer cWHERE s.cust_id=c.cust_id;Both tables have the same degree of parallelism and are partitioned the same way on the join column (cust_id)Range partition May 18th 2008Hash PartitionedSub part 1A large join is divided into multiple smaller joins, each joins a pair of partitions in parallelPart

30、 1Sub part 2Sub part 3Sub part 4Part 2Part 3Part 4Sub part 2Sub part 3Sub part 4Sub part 1Part 1Part 2Part 3Part 4Partition Wise join對(duì)于partition-wise join而言，查詢的并行度必須是等于或成倍于表分區(qū)的數(shù)量SELECT sum(s.amount_sold)FROM sales sWHERE s.time_id BETWEENto_date(01-JAN-1999,DD-MON-YYYY)ANDto_date(31-DEC-1999,DD-MON-

31、YYYY);Q: What was the total sales for the year 1999?Tips：分區(qū)裁剪（：分區(qū)裁剪（Partition Pruning）Tips：如何檢查分區(qū)修剪是否生效：如何檢查分區(qū)修剪是否生效 Sample planOnly 4 partitions are touched 9, 10, 11, & 12, , Partition 1Partition 5Partition 10:129101920Tips：如何檢查分區(qū)修剪是否生效：如何檢查分區(qū)修剪是否生效Simple Query : SELECT COUNT(*)FROM RHP_TABWHE

32、RE CUST_ID = 9255AND TIME_ID = 2008-01-01;Overall partition #range partition #Sub-partition # 數(shù)據(jù)庫(kù)空間管理數(shù)據(jù)庫(kù)空間管理 數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)） 數(shù)據(jù)加載數(shù)據(jù)加載 并行執(zhí)行并行執(zhí)行 開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作） 索引管理索引管理 統(tǒng)計(jì)信息收集統(tǒng)計(jì)信息收集29Tips：使用使用External Table進(jìn)行數(shù)據(jù)加載進(jìn)行數(shù)據(jù)加載Oracle推薦使用推薦使用External Table方式加載數(shù)據(jù)，而不第一推薦方式加載數(shù)據(jù)

33、，而不第一推薦SQL*Loader，因?yàn)椋驗(yàn)镾QL*Loader做并行數(shù)據(jù)加載時(shí)，數(shù)據(jù)首先被加載到做并行數(shù)據(jù)加載時(shí)，數(shù)據(jù)首先被加載到TEMP Extent中，只有在中，只有在transaction 被被Commit時(shí)，時(shí)，Temp Extent會(huì)被合并到實(shí)體表的會(huì)被合并到實(shí)體表的段（段（Segment）中，而在原來(lái)表中的部分滿的）中，而在原來(lái)表中的部分滿的Extent（partially full extents）會(huì)被跳過(guò)，所以到導(dǎo)致空間浪費(fèi)和性能浪費(fèi)。所以對(duì)于大量）會(huì)被跳過(guò)，所以到導(dǎo)致空間浪費(fèi)和性能浪費(fèi)。所以對(duì)于大量加載的加載的Partition表，這樣可能會(huì)導(dǎo)致較大的空間浪費(fèi)情況。表，這

34、樣可能會(huì)導(dǎo)致較大的空間浪費(fèi)情況。采用采用External Table方式加載時(shí)，會(huì)有如下好處：方式加載時(shí)，會(huì)有如下好處：-Full usage of SQL capabilities directly on the data- Automatic use of parallel capabilities - No need to stage the data again in the database-Better allocation of space when storing data- High watermark brokering- Autoallocate tablespace w

35、ill trim extents after the load-Additional capabilities like -The usage of data pump files-The usage of pre-processingExternal Table Tips：External Table預(yù)處理預(yù)處理允許文件在加載過(guò)程中被自動(dòng)化預(yù)處理允許文件在加載過(guò)程中被自動(dòng)化預(yù)處理-例如：對(duì)壓縮文件進(jìn)行解壓縮、排序等預(yù)處理不支持對(duì)文件的自動(dòng)的小顆?；A(yù)處理不支持對(duì)文件的自動(dòng)的小顆粒化-需要提供多份文件，文件的數(shù)量決定了處理的并行度需要授權(quán)對(duì)目錄的讀和執(zhí)行的權(quán)限（需要授權(quán)對(duì)目錄的讀和執(zhí)行的權(quán)限（

36、Grant read, execute privileges directories）CREATE TABLE sales_external()ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE PREPROCESSOR exec_dir: gunzip OPTIONS -c FIELDS TERMINATED BY |) LOCATION ();Tips：直接路徑加載（：直接路徑加載（Direct Path

37、Load）在每次在每次IO異步請(qǐng)求時(shí)，數(shù)據(jù)被直接寫(xiě)入數(shù)據(jù)庫(kù)存儲(chǔ)異步請(qǐng)求時(shí)，數(shù)據(jù)被直接寫(xiě)入數(shù)據(jù)庫(kù)存儲(chǔ)CTAS 語(yǔ)句總是使用語(yǔ)句總是使用 Direct Path 在在Insert As Select 語(yǔ)句中使用語(yǔ)句中使用 APPEND暗示方法也是暗示方法也是Direct PathInsert /*+ APPEND */ into Sales partition(p2) Select * From ext_tab_for_sales_data;在一個(gè)對(duì)象上，只有一個(gè)在一個(gè)對(duì)象上，只有一個(gè)direct path 操作能夠起作用操作能夠起作用-通過(guò)指定一個(gè)表的分區(qū)表的名字方法，可以同時(shí)將多個(gè)Direct

38、 Pach加載操作到一個(gè)表Tips：并行加載（：并行加載（Parallel Load）為了確保為了確保Direct Path加載成為并行操作，需要通過(guò)如下加載成為并行操作，需要通過(guò)如下方式：方式：- 在Hint中指定并行度、或者在表上指定并行度- 通過(guò)Alter Session語(yǔ)句啟動(dòng)會(huì)話級(jí)并行當(dāng)并行度被指定后，當(dāng)并行度被指定后，CTAS 會(huì)自動(dòng)并行化加載會(huì)自動(dòng)并行化加載IAS 不會(huì)自動(dòng)并行化，他需要不會(huì)自動(dòng)并行化，他需要parallel DML的支持：的支持：ALTER SESSION ENABLE PARALLEL DML;Tips：使用使用Parallel direct path 數(shù)據(jù)加

39、載數(shù)據(jù)加載使用使用“direct path load”可以帶來(lái)很好的數(shù)據(jù)加載性能，因?yàn)閿?shù)據(jù)會(huì)繞過(guò)可以帶來(lái)很好的數(shù)據(jù)加載性能，因?yàn)閿?shù)據(jù)會(huì)繞過(guò)Buffer Cache，直接寫(xiě)入數(shù)據(jù)庫(kù)存儲(chǔ)。，直接寫(xiě)入數(shù)據(jù)庫(kù)存儲(chǔ)。CTAS總會(huì)使用總會(huì)使用“direct path load”，但是，但是Insert As Select (IAS)并不是總會(huì)使用并不是總會(huì)使用“direct path load”，為了使，為了使IAS使用使用“direct path load”，需要在，需要在IAS中加入中加入APPEND hint，SQL如下：如下：Insert /*+ APPEND */ into Sales part

40、ition(p2) Select * From ext_tab_for_sales_data; ALTER SESSION ENABLE PARALLEL DML;Insert /*+ APPEND */ into Sales partition(p2)Select * from ext_tab_for_sales_data; 為了讓為了讓“direct path load”以并行方式運(yùn)行，可以以下兩種方式設(shè)置并行度：以并行方式運(yùn)行，可以以下兩種方式設(shè)置并行度：1）在）在CTAS和和IAS中加入中加入PARALLEL hint；2）在）在External table和目標(biāo)加載和目標(biāo)加載Tabl

41、e上，上，設(shè)置設(shè)置PARALLEL子句。一旦設(shè)置了并行度，子句。一旦設(shè)置了并行度，CTAS將自動(dòng)執(zhí)行并行化的將自動(dòng)執(zhí)行并行化的“direct path load”，而，而IAS將不會(huì)。為了讓將不會(huì)。為了讓IAS能夠并行化執(zhí)行能夠并行化執(zhí)行“direct path load”，出，出了設(shè)置并行度外，還必須通過(guò)了設(shè)置并行度外，還必須通過(guò)alter the session to enable parallel DML。SQL如下：如下：Tips：在數(shù)據(jù)加載時(shí)，對(duì)于常訪問(wèn)的字段，可以進(jìn)行排序，以在數(shù)據(jù)加載時(shí)，對(duì)于常訪問(wèn)的字段，可以進(jìn)行排序，以便增加便增加Storage index的效率的效率對(duì)在使用對(duì)

42、在使用 INSERT /*+ APPEND */ 做數(shù)據(jù)加載時(shí)，可以對(duì)查詢中做數(shù)據(jù)加載時(shí)，可以對(duì)查詢中Where條條件中使用較多的列進(jìn)行件中使用較多的列進(jìn)行Order By排序，以便增加數(shù)據(jù)壓縮率和排序，以便增加數(shù)據(jù)壓縮率和Storage index查詢效率。查詢效率。Select name,value/1024/1024 as stat_value from v$mystat s,v$statname n Where S.Statistic#=n.statistic# and name like %cell physical%Insert into tablea select * from

43、ext_tablea order by col1,col2可以通過(guò)查詢可以通過(guò)查詢v$mystat,v$statname視圖，通過(guò)看視圖，通過(guò)看cell physical I0 bytes saved by storage index統(tǒng)計(jì)值，來(lái)檢查多少統(tǒng)計(jì)值，來(lái)檢查多少I(mǎi)/O被減少。被減少。SQL如下：如下：Tips：通過(guò)表分區(qū)交換做數(shù)據(jù)加載通過(guò)表分區(qū)交換做數(shù)據(jù)加載(Partition exchange load)Oracle建議對(duì)大的數(shù)據(jù)表采用分區(qū)表，分區(qū)表的好處之一就是可以通建議對(duì)大的數(shù)據(jù)表采用分區(qū)表，分區(qū)表的好處之一就是可以通過(guò)分區(qū)交換方式（過(guò)分區(qū)交換方式（Partition Excha

44、nge）進(jìn)行數(shù)據(jù)加載，這樣可以最小）進(jìn)行數(shù)據(jù)加載，這樣可以最小化的對(duì)業(yè)務(wù)影響情況下進(jìn)行快速數(shù)據(jù)加載。化的對(duì)業(yè)務(wù)影響情況下進(jìn)行快速數(shù)據(jù)加載。Partition Exchange可以允可以允許用戶交換非分區(qū)表數(shù)據(jù)到一個(gè)表的分區(qū)中。分區(qū)交換命令并不真正許用戶交換非分區(qū)表數(shù)據(jù)到一個(gè)表的分區(qū)中。分區(qū)交換命令并不真正移動(dòng)兩個(gè)表的數(shù)據(jù)，而是簡(jiǎn)單的移動(dòng)兩個(gè)表的數(shù)據(jù)，而是簡(jiǎn)單的Update數(shù)據(jù)字典信息。由于沒(méi)有進(jìn)行數(shù)據(jù)字典信息。由于沒(méi)有進(jìn)行數(shù)據(jù)移動(dòng)，所以數(shù)據(jù)非分區(qū)表需要與分區(qū)表有相同的存儲(chǔ)結(jié)構(gòu)（存儲(chǔ)數(shù)據(jù)移動(dòng)，所以數(shù)據(jù)非分區(qū)表需要與分區(qū)表有相同的存儲(chǔ)結(jié)構(gòu)（存儲(chǔ)在合適的表空間、相同的在合適的表空間、相同的Exten

45、t Size）。）。SQL如下：如下：Create Table tmp_sales2(.)parallel storage (INITIAL 8M NEXT 8M) tablespace main_fact_tbs . Alter table Sales exchange partition p2 with table tmp_sales2 including indexes without validation;DBA1. Create external table for flat files5. Alter table Sales exchange partition May_24_20

46、08 with table tmp_sales2. Use CTAS command to create non-partitioned table TMP_SALESSales table now has all the data3. Create indexes4. Gather StatisticsTips：Partition Exchange loading操作過(guò)程操作過(guò)程 數(shù)據(jù)庫(kù)空間管理數(shù)據(jù)庫(kù)空間管理 數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)）數(shù)據(jù)倉(cāng)庫(kù)物理表設(shè)計(jì)（表壓縮、表分區(qū)） 數(shù)據(jù)加載數(shù)據(jù)加載 并行執(zhí)行并行執(zhí)行 開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作）開(kāi)發(fā)注意事項(xiàng)（集合、關(guān)聯(lián)操作） 索引管理索引

47、管理 統(tǒng)計(jì)信息收集統(tǒng)計(jì)信息收集39Tips：并行執(zhí)行的適用情況并行執(zhí)行的適用情況并行執(zhí)行是并行執(zhí)行是Exadata最強(qiáng)大的能力之一，充分利用最強(qiáng)大的能力之一，充分利用Exadata的并行計(jì)算的并行計(jì)算能力可以極大提高系統(tǒng)利用率。但是我們必須要確認(rèn)并行使用的時(shí)機(jī)能力可以極大提高系統(tǒng)利用率。但是我們必須要確認(rèn)并行使用的時(shí)機(jī)，以及并行使用的程度。，以及并行使用的程度。 并行并行適用場(chǎng)景：適用場(chǎng)景：-并行執(zhí)行應(yīng)該在所有的資源密集型操作中被使用，例如：復(fù)雜查詢、大量數(shù)據(jù)處理DML、大表建索引、收集統(tǒng)計(jì)信息、大量數(shù)據(jù)加載等。只有在表數(shù)據(jù)小于只有在表數(shù)據(jù)小于64MB或者同時(shí)有上百個(gè)并發(fā)用戶執(zhí)行操或者同時(shí)有上

48、百個(gè)并發(fā)用戶執(zhí)行操作時(shí)，不建議使用并行執(zhí)行操作作時(shí)，不建議使用并行執(zhí)行操作。并行不適用場(chǎng)景：并行不適用場(chǎng)景：-短交易（幾秒鐘或更少）。在這些環(huán)境中并行執(zhí)行是沒(méi)有什么用處，因?yàn)閰f(xié)調(diào)并行執(zhí)行服務(wù)器會(huì)增加相關(guān)的成本，這種協(xié)調(diào)的成本可能超過(guò)并行帶來(lái)的好處。-已經(jīng)大量使用CPU、內(nèi)存或I / O資源的環(huán)境。并行執(zhí)行旨在利用其它可用的硬件資源，如果沒(méi)有這樣的資源可用，那么并行執(zhí)行不產(chǎn)生任何效益，并且可能損害性能。Tips：并行執(zhí)行工作過(guò)程：并行執(zhí)行工作過(guò)程用戶連接到數(shù)據(jù)庫(kù)User后臺(tái)進(jìn)程被派生當(dāng)用戶發(fā)出一個(gè)并行SQL，后臺(tái)進(jìn)程就變成為QC（Query Coordinator）QC從并行進(jìn)程組中獲得Para

49、llel servers ，然后QC分配任務(wù)給Parallel servers進(jìn)程Parallel servers 是一個(gè)獨(dú)立的Session，從并行進(jìn)程組中分配，并完成指定的具體任務(wù)Parallel servers通過(guò)Shared Pool的內(nèi)存?zhèn)鬟f通信消息，與QC進(jìn)程和并行進(jìn)程進(jìn)行互相通信ProducersConsumersQuery coordinatorP1P2P3P4Hash join 會(huì)首先選擇小表作為驅(qū)動(dòng)表，進(jìn)行掃描，在這個(gè)例子中Customers表示小表，4個(gè)Producer 并行進(jìn)程并行掃描，并且返回就過(guò)給Consumers進(jìn)程P8P7P6P5SALESTableCUSTOM

50、ERSTableSELECT c.cust_last_name, s.time_id, s.amount_soldFROM sales s, customers cWHERE s.cust_id = c.cust_id;并行執(zhí)行的工作機(jī)制并行執(zhí)行的工作機(jī)制ProducersConsumersQuery coordinatorP1P2P3P4一旦4個(gè)Producer進(jìn)程完成了小表的掃描，他們開(kāi)始對(duì)大表（Sales表）進(jìn)行并行掃描，并返回結(jié)果給Consumer進(jìn)程P8P7P6P5SALESTableCUSTOMERSTableSELECT c.cust_last_name, s.time_id,

51、s.amount_soldFROM sales s, customers cWHERE s.cust_id = c.cust_id;并行執(zhí)行的工作機(jī)制并行執(zhí)行的工作機(jī)制ProducersConsumersP1P2P3P4P8P7P6P5一旦Consumer進(jìn)程收到了兩個(gè)表的數(shù)據(jù)，他們開(kāi)始做并行的Join操作，一旦完成join操作，就將結(jié)果返回給QC進(jìn)程Query coordinatorSALESTableCUSTOMERSTableSELECT c.cust_last_name, s.time_id, s.amount_soldFROM sales s, customers cWHERE s.

52、cust_id = c.cust_id;并行執(zhí)行的工作機(jī)制并行執(zhí)行的工作機(jī)制SELECT c.cust_last_name, s.time_id, s.amount_soldFROM sales s, customers cWHERE s.cust_id = c.cust_id;Query CoordinatorTips：檢查并行執(zhí)行情況：檢查并行執(zhí)行情況select sum(revenue), storefrom line_itemsWhere profit(price,units) 0.2order by storeData on DiskQuery ServersProducers or

53、 scannersConsumersOr Aggregators)Coordinator并行進(jìn)程的任務(wù)并行進(jìn)程的任務(wù)Tips：不：不同操作并行方式同操作并行方式Group by,Order by進(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分布Hash key 1Hash key 2Hash key 3Hash key 4Hash key 5Hash key 6進(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-ZGroup ByOrder ByHash分區(qū)效率最高Range分區(qū)效率最高Tip

54、s:數(shù)據(jù)分布的例子數(shù)據(jù)分布的例子select count(*) from yellow y, green g where y.deptno = g.deptno TipsTips：并：并行進(jìn)程中不同的數(shù)據(jù)分布方式行進(jìn)程中不同的數(shù)據(jù)分布方式分布方式說(shuō)明Hash, Hash使用Hash函數(shù)映射Join字段，映射完成后，每個(gè)查詢引擎執(zhí)行Join，建議Join表size相近，使用hash-join或者sort merge joinBroadcast,None外表的行廣播到每個(gè)查詢進(jìn)程，內(nèi)表隨機(jī)分區(qū)，建議外表size大大小于內(nèi)表。通用規(guī)則：inner table size * number of que

55、ry servers outer table sizeNone,Broadcase內(nèi)表的行廣播到每個(gè)查詢進(jìn)程，建議內(nèi)表的size大大小于外表。通用規(guī)則：inner table size * number of query servers 0 1-Jan-0 9 / commit / create table tx_log_new nologging parallel compress for all operations as select .,case sales_date0 1-Jan-0 9 and tax_rate=9.3 then 9.9 else tax_rate end, . f

56、rom sales_ledger / alter table tx_log rename to tx_log_old / alter table tx_log_new to tx_log /Tips：對(duì)：對(duì)Merge的改寫(xiě)的改寫(xiě)TipsTips：多表：多表InsertInsertUnconditional Unconditional INSERT ALLINSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hi

57、re_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;8 rows created. TipsTips：多表：多表Insert Insert Conditional Conditional INSERT ALLINSERT ALL WHEN SAL 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT e

58、mployee_id EMPID,hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;4 rows created. TipsTips：多表：多表Insert Insert Conditional Conditional INSERT FIRST INSERT FIRST WHEN SAL 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like (%00%) THEN INTO hiredate_his

59、tory_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like (%99%) THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;8 rows created.TipsTips：多表：

60、多表Insert Insert Pivoting Pivoting INSERT INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id