Exadata一體機最佳實踐

1、婁恒: APAC Exadata specialist,BOC ACRM數(shù)據(jù)庫物理模型和開發(fā)最佳實踐,提綱,數(shù)據(jù)庫空間管理 數(shù)據(jù)倉庫物理表設計（表壓縮、表分區(qū)） 數(shù)據(jù)加載 并行執(zhí)行 開發(fā)注意事項（集合、關聯(lián)操作） 索引管理 統(tǒng)計信息收集,2, 2010 Oracle Corporation Proprietary and Confidential,Tips 1:設置合適的DB_Block_Size,對Exadata而言，MAX_IO_SIZE缺省為1MB 所以推薦DB_BLOCK_SIZE設置為8192 同時推薦DB_FILE_MULTI_BLOCK_READ_COUNT設置為128 因為_

2、MAX_IO_SIZE = DB_FILE_MULTI_BLOCK_READ_COUNT * DB_block_size。,3, 2010 Oracle Corporation Proprietary and Confidential,Tips：采用locally managed, Bigfile創(chuàng)建表空間,4,Tips：選擇表空間合適的Extent管理方法,采用Auto-Allocate方式，Oracle自動管理表的Extent的大小，開始時初始的Extent大小為64KB(除Partition表外)，當表或索引的段(Segment)大小超過1MB，Extent以1MB為單位增長段空間，一旦

3、段大小超過64MB，Extent以8MB為單位增長段空間。 采用Uniform方式，所有段的Extent大小都是統(tǒng)一大小。對于Auto-Allocate方式和Uniform方式的選擇，Oracle推薦采用Auto-Allocate作為Extent的管理方式。,5, 2010 Oracle Corporation Proprietary and Confidential,Sales Table,Uniform Extent：數(shù)據(jù)并行加載特點,假設有4個并行進程進行數(shù)據(jù)加載，Uniform extend 大小為8MB,6, 2010 Oracle Corporation Proprietary a

4、nd Confidential,Auto-Allocate Extent：數(shù)據(jù)并行加載特點,假設有4個并行進程進行數(shù)據(jù)加載，initial ,select p1 File #, p2 Block #, p3 Reason Code from v$session_wait where event = buffer busy waits;,為了避免文件頭爭用情況，可以采用多個Big file tablespace來均勻存放Partition table。例如：下面語句使用4個表空間來均勻存放128個Subpartition表：,8, 2010 Oracle Corporation Propriet

5、ary and Confidential,Tips：對于分區(qū)表（Partition）加載，啟用Large Extent,推薦大的對象使用Large Extent方式，以便減少Extent的數(shù)量。當設置CELL_PARTITION_LARGE_EXTENTS=TRUE時，所有分區(qū)表在創(chuàng)建時，將自動啟動Large Extent模式（8MB）,即創(chuàng)建時Initial Extent大小為8MB。 限制條件： CELL_PARTITION_LARGE_EXTENTS僅適用于：分區(qū)表、locally managed和AUTO-ALLOCATE的表空間。 不適合于：非分區(qū)表、Uniform的表空間的對象。,

6、9, 2010 Oracle Corporation Proprietary and Confidential,Tips：針對非分區(qū)表（Non-Partition）加載，使用Large Extent,如果對于非分區(qū)表，想使用Large Extent(8MB)。需要在Create table的DDL中設置INITIAL和NEXT子句。 當對非分區(qū)表進行并行數(shù)據(jù)加載時，數(shù)據(jù)在Temp段中被生成，然后再合并到要加載的數(shù)據(jù)表的Extent中，缺省按照64KB Extent大小進行數(shù)據(jù)增長。但從11.1.0.7開始，在加載數(shù)據(jù)時（Load），NEXT用來控制新的Extent的大小。所以對于大的非分區(qū)表加

7、載，可以直接將INITIAL和NEXT都設置為8MB。SQL如下：,Create Table sales(.) parallel storage (INITIAL 8M NEXT 8M) (.),10, 2010 Oracle Corporation Proprietary and Confidential,Tips：關閉deferred_segment_creation（段延遲創(chuàng)建）功能,從11g R2開始，當在Locally Managed Tablespace上，創(chuàng)建一個非分區(qū)表時，缺省情況表的空間分配會啟用延遲分配方式，即當首行被Insert到表中時，表的Extent才會被逐漸分配。建

8、議關閉延遲空間空間分配，可以通過如下SQL關閉：,Alter system set deferred_segment_creation = FALSE scope=both,11, 2010 Oracle Corporation Proprietary and Confidential,Tips : 空間回收Shrinking Segments,HWM,HWM,ALTER TABLE employees SHRINK SPACE COMPACT;,1,ALTER TABLE employees SHRINK SPACE;,2,DML operations and queries can be

9、issued during compaction.,DML operations are blocked when the HWM is adjusted.,12, 2010 Oracle Corporation Proprietary and Confidential,Shrinking Segments by Using SQL,ALTER SHRINK SPACE CASCADE,TABLE OVERFLOW,INDEX,MATERIALIZED VIEW,MATERIALIZED VIEW LOG,MODIFY PARTITION,MODIFY SUBPARTITION,MODIFY

10、LOB,ALTER TABLE employees SHRINK SPACE CASCADE;,ALTER TABLE employees ENABLE ROW MOVEMENT;,1,2,ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);,3,ALTER TABLE employees OVERFLOW SHRINK SPACE;,4,13, 2010 Oracle Corporation Proprietary and Confidential,提綱,數(shù)據(jù)庫空間管理 數(shù)據(jù)倉庫物理表設計（表壓縮、表分區(qū)） 數(shù)據(jù)加載 并行執(zhí)行 開發(fā)

11、注意事項（集合、關聯(lián)操作） 索引管理 統(tǒng)計信息收集,14, 2010 Oracle Corporation Proprietary and Confidential,Tips：數(shù)據(jù)倉庫設計,一個典型的數(shù)據(jù)倉庫是一個Many Rows System ，但經(jīng)常會被用Few Rows System的方式來開發(fā)(Row By Row) 在大多數(shù)情況下，Many Rows System還是Few Rows System可以通過SQL語句的執(zhí)行頻度決定： 1/Sec 代表 many rows 1000/Sec代表 few rows 備注：在Many Rows System中，可能存在一種誤導：批量集合操作

12、（Set Based Operation）通過Row By Row的操作方式開發(fā)效率更高,15, 2010 Oracle Corporation Proprietary and Confidential,Tips：數(shù)據(jù)倉庫物理模型設計原則,Many Rows 并行（Parallelism） 分區(qū)（Partitioning） 壓縮（Compression） 集合批量操作（Set-based techniques） 通過數(shù)據(jù)轉換代替實現(xiàn)數(shù)據(jù)更新（Data modified by transformation） Few Rows 索引（Index design） 物化視圖（Materialized

13、views and aggregates）,16, 2010 Oracle Corporation Proprietary and Confidential,Tips：數(shù)據(jù)壓縮考慮,壓縮技術是數(shù)據(jù)倉庫物理設計最重要的因素之一 壓縮的影響: 極大加速表掃描速度、減少磁盤空間占用、加速數(shù)據(jù)備份 但是，壓縮對大大降低UPDATE和DELETE的效率 一個好的數(shù)據(jù)倉庫模型，對于壓縮表而言，將只采用Append方式操作，避免采用修改（Update、Delete、Merge）方式對數(shù)據(jù)的操作，以保證對壓縮表操作的效率 但是如果一些經(jīng)常更新的大表確實有壓縮的需求，可以考慮使用OLTP壓縮技術。 無論HCC壓

14、縮還是OLTP壓縮，其本質都是去重，只是其算法上有些差異。因而如果在數(shù)據(jù)加載時，對數(shù)據(jù)預先排序，那么可帶來明顯的壓縮比率的提升。 一般來說，壓縮級別QUERY HIGH既可以得到一個理想的壓縮比率，也能提供很好的查詢性能，可以考慮作為初始測試的壓縮級別.,17, 2010 Oracle Corporation Proprietary and Confidential,Tips：EHCC 混合列壓縮,EHCC (Exadata Hybrid Columnar Compression) 不是完全的列壓縮，而是混合列存儲 EHCC具備多種壓縮類型 Query low Query high Archi

15、ve low Archive high 設計用于不頻繁修改的數(shù)據(jù) 設計用于低并發(fā)的場景 支持數(shù)據(jù)庫各種功能 DMLs/DDLs, Partitioning, PQ, PDML, Online redefinition, CTAS, IAS, SQL Loader, External tables, Context, MVs etc. 支持索引 只有在數(shù)據(jù)批量加載時被壓縮,18, 2010 Oracle Corporation Proprietary and Confidential,Tips：如何創(chuàng)建EHCC表,CTAS (create table as select) create tabl

16、e foo compress for query as select * from bar1; IDL (insert direct load) create table foo compress for archive low; insert /*+APPEND*/ into foo select * from bar2; 壓縮可以在Segment一級指定： 每個分區(qū)可以有不同的壓縮類型 例如： create table orders (cid, pid, sid, price, discount, odate) partition by range (cid) (partition p1

17、values less than (100000) nocompress, partition 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;,19, 2010 Oracle Co

18、rporation Proprietary and Confidential,Tips：動態(tài)啟用或停用表的EHCC,Existing tables/partitions can 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, kee

19、ping the existing data as is alter table bar compress for query high alter table 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 EHCC,20, 2010 Oracle Corpo

20、ration Proprietary and Confidential,Tips：分區(qū)(Partition)設計目的,數(shù)據(jù)管理 Exchange data in/out Break down operations into smaller pieces 查詢優(yōu)化 Partition pruning Hash based joins and sorts,21, 2010 Oracle Corporation Proprietary and Confidential,Tips ：數(shù)據(jù)倉庫分區(qū)（Partition）策略,一級分區(qū)：首先按日期對大表進行分區(qū) 主要目標是啟動分區(qū)修剪（Partition

21、Pruning），簡化數(shù)據(jù)管理 主要對于日期字段進行Range 或interval分區(qū) 選擇大部分查詢查詢用到的日期字段作為分區(qū)列 這個日期字段（用于分區(qū)列）應該是不易變（不被更新） 二級子分區(qū)（Subpartition）可以按照： Hash分區(qū)，更好地支持join和Sort RANGE或LIST分區(qū)，更好的支持分區(qū)修剪（ Partition Pruning）,22, 2010 Oracle Corporation Proprietary and Confidential,Tips:定義合理的Hash分區(qū)數(shù)量,Oracle推薦大的分區(qū)表使用Hash分區(qū)作為分區(qū)方法，為了保證數(shù)據(jù)在Hash Pa

22、rtition間均勻分布，Hash分區(qū)的數(shù)量建議是2的指數(shù)冪，或者設置Partition數(shù)量為CPU數(shù)量的2倍。然而每個hash Partition應該至少不小于16MB。小于16MB將在并行執(zhí)行下不會有較好的掃描效率。 首先考慮分區(qū)數(shù)是2的整數(shù)冪的情況：當Oracle的分區(qū)數(shù)從2個變?yōu)?個，Oracle并不需要將所有數(shù)據(jù)重新打亂，而是將原有的2個分區(qū)每個都一分為二。同樣的道理，如果將分區(qū)數(shù)設置為8，Oracle會將原有的4個分區(qū)一分為二。 Oracle的HASH分區(qū)就像是一棵大的二叉樹。每個分區(qū)就相當于二叉樹的一個葉節(jié)點。二叉樹的第一層，只有一個根節(jié)點，對應只有1個分區(qū)的情況。二叉樹的第二層

23、，兩個葉節(jié)點，對應2個分區(qū)的情況。二叉樹的第三層，4個葉節(jié)點，對應4個分區(qū)的情況。二叉樹的第n層，2(n-1)個葉節(jié)點，對應2(n-1)個分區(qū)情況。,23, 2010 Oracle Corporation Proprietary and Confidential,Tips：使用Partition-wise joins,對于兩個大表關聯(lián)操作，推薦使用partiton-wise joins來減少并行進程間數(shù)據(jù)交換，減少查詢的執(zhí)行時間。對于使用full partition-wise join，兩個關聯(lián)的表必須是相同的Partition鍵值（即兩個表必須有相同的Partition列、相同的Partit

24、ion方法、相同的Partition數(shù)量）。如下是采用partiton-wise joins的兩個表關聯(lián)的執(zhí)行計劃，Sales表和Customers表具有相同的并行度、Hash Partition方法（Cust_id列作為Hash Partition Key）、Join列是Partition Key。,24, 2010 Oracle Corporation Proprietary and Confidential,SELECT sum(amount_sold) FROM sales s, customer c WHERE s.cust_id=c.cust_id;,Both tables hav

25、e the same degree of parallelism and are partitioned the same way on the join column (cust_id),Sales,Range partition May 18th 2008,Sub part 2,Sub part 3,Sub part 4,Sub part 1,Part 1,Part 2,Part 3,Part 4,Partition Wise join,對于partition-wise join而言，查詢的并行度必須是等于或成倍于表分區(qū)的數(shù)量,25,SELECT sum(s.amount_sold) FR

26、OM sales s WHERE s.time_id BETWEEN to_date(01-JAN-1999,DD-MON-YYYY) AND to_date(31-DEC-1999,DD-MON-YYYY);,Q: What was the total sales for the year 1999?,Tips：分區(qū)裁剪（Partition Pruning）,SALES_Q4_1998,SALES_Q1_1999,SALES_Q2_1999,SALES_Q3_1999,SALES_Q4_1999,SALES_Q1_2000,26,Tips：如何檢查分區(qū)修剪是否生效,Sample plan,O

27、nly 4 partitions are touched 9, 10, 11, ,Overall partition #,range partition #,Sub-partition #,28, 2010 Oracle Corporation Proprietary and Confidential,提綱,數(shù)據(jù)庫空間管理 數(shù)據(jù)倉庫物理表設計（表壓縮、表分區(qū)） 數(shù)據(jù)加載 并行執(zhí)行 開發(fā)注意事項（集合、關聯(lián)操作） 索引管理 統(tǒng)計信息收集,29, 2010 Oracle Corporation Proprietary and Confidential,Tips：使用External Table進行

28、數(shù)據(jù)加載,Oracle推薦使用External Table方式加載數(shù)據(jù)，而不第一推薦SQL*Loader，因為SQL*Loader做并行數(shù)據(jù)加載時，數(shù)據(jù)首先被加載到TEMP Extent中，只有在transaction 被Commit時，Temp Extent會被合并到實體表的段（Segment）中，而在原來表中的部分滿的Extent（partially full extents）會被跳過，所以到導致空間浪費和性能浪費。所以對于大量加載的Partition表，這樣可能會導致較大的空間浪費情況。 采用External Table方式加載時，會有如下好處： Full usage of SQL ca

29、pabilities 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 will trim extents after the load Additional capabilities like The usage of data pump fi

30、les The usage of pre-processing,30, 2010 Oracle Corporation Proprietary and Confidential,External Table,31, 2010 Oracle Corporation Proprietary and Confidential,Tips：External Table預處理,允許文件在加載過程中被自動化預處理 例如：對壓縮文件進行解壓縮、排序等 預處理不支持對文件的自動的小顆?；?需要提供多份文件，文件的數(shù)量決定了處理的并行度 需要授權對目錄的讀和執(zhí)行的權限（Grant read, execute pr

31、ivileges 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 () );,32, 2010 Oracle Corporation Proprietary and Con

32、fidential,Tips：直接路徑加載（Direct Path Load）,在每次IO異步請求時，數(shù)據(jù)被直接寫入數(shù)據(jù)庫存儲 CTAS 語句總是使用 Direct Path 在Insert As Select 語句中使用 APPEND暗示方法也是Direct Path Insert /*+ APPEND */ into Sales partition(p2) Select * From ext_tab_for_sales_data; 在一個對象上，只有一個direct path 操作能夠起作用 通過指定一個表的分區(qū)表的名字方法，可以同時將多個Direct Pach加載操作到一個表,33, 2

33、010 Oracle Corporation Proprietary and Confidential,Tips：并行加載（Parallel Load）,為了確保Direct Path加載成為并行操作，需要通過如下方式： 在Hint中指定并行度、或者在表上指定并行度 通過Alter Session語句啟動會話級并行 當并行度被指定后，CTAS 會自動并行化加載 IAS 不會自動并行化，他需要parallel DML的支持： ALTER SESSION ENABLE PARALLEL DML;,34, 2010 Oracle Corporation Proprietary and Confide

34、ntial,Tips：使用Parallel direct path 數(shù)據(jù)加載,使用“direct path load”可以帶來很好的數(shù)據(jù)加載性能，因為數(shù)據(jù)會繞過Buffer Cache，直接寫入數(shù)據(jù)庫存儲。CTAS總會使用“direct path load”，但是Insert As Select (IAS)并不是總會使用“direct path load”，為了使IAS使用“direct path load”，需要在IAS中加入APPEND hint，SQL如下：,Insert /*+ APPEND */ into Sales partition(p2) Select * From ext_t

35、ab_for_sales_data;,ALTER SESSION ENABLE PARALLEL DML; Insert /*+ APPEND */ into Sales partition(p2)Select * from ext_tab_for_sales_data;,為了讓“direct path load”以并行方式運行，可以以下兩種方式設置并行度：1）在CTAS和IAS中加入PARALLEL hint；2）在External table和目標加載Table上，設置PARALLEL子句。一旦設置了并行度，CTAS將自動執(zhí)行并行化的“direct path load”，而IAS將不會。為

36、了讓IAS能夠并行化執(zhí)行“direct path load”，出了設置并行度外，還必須通過alter the session to enable parallel DML。SQL如下：,35, 2010 Oracle Corporation Proprietary and Confidential,Tips：在數(shù)據(jù)加載時，對于常訪問的字段，可以進行排序，以便增加Storage index的效率,對在使用 INSERT /*+ APPEND */ 做數(shù)據(jù)加載時，可以對查詢中Where條件中使用較多的列進行Order By排序，以便增加數(shù)據(jù)壓縮率和Storage index查詢效率。,Select

37、 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 ext_tablea order by col1,col2,可以通過查詢v$mystat,v$statname視圖，通過看cell physical I0 bytes saved by storage index統(tǒng)計值，來檢查多少I/O被減少。SQL如下：,36, 2010 Or

38、acle Corporation Proprietary and Confidential,Tips：通過表分區(qū)交換做數(shù)據(jù)加載(Partition exchange load),Oracle建議對大的數(shù)據(jù)表采用分區(qū)表，分區(qū)表的好處之一就是可以通過分區(qū)交換方式（Partition Exchange）進行數(shù)據(jù)加載，這樣可以最小化的對業(yè)務影響情況下進行快速數(shù)據(jù)加載。Partition Exchange可以允許用戶交換非分區(qū)表數(shù)據(jù)到一個表的分區(qū)中。分區(qū)交換命令并不真正移動兩個表的數(shù)據(jù)，而是簡單的Update數(shù)據(jù)字典信息。由于沒有進行數(shù)據(jù)移動，所以數(shù)據(jù)非分區(qū)表需要與分區(qū)表有相同的存儲結構（存儲在合適的表

39、空間、相同的Extent 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;,37, 2010 Oracle Corporation Proprietary and Confidential,DBA,1. Create external tab

40、le for flat files,5. Alter table Sales exchange partition May_24_2008 with table tmp_sales,Tips：Partition Exchange loading操作過程,38,提綱,數(shù)據(jù)庫空間管理 數(shù)據(jù)倉庫物理表設計（表壓縮、表分區(qū)） 數(shù)據(jù)加載 并行執(zhí)行 開發(fā)注意事項（集合、關聯(lián)操作） 索引管理 統(tǒng)計信息收集,39, 2010 Oracle Corporation Proprietary and Confidential,Tips：并行執(zhí)行的適用情況,并行執(zhí)行是Exadata最強大的能力之一，充分利用Exada

41、ta的并行計算能力可以極大提高系統(tǒng)利用率。但是我們必須要確認并行使用的時機，以及并行使用的程度。 并行適用場景： 并行執(zhí)行應該在所有的資源密集型操作中被使用，例如：復雜查詢、大量數(shù)據(jù)處理DML、大表建索引、收集統(tǒng)計信息、大量數(shù)據(jù)加載等。只有在表數(shù)據(jù)小于64MB或者同時有上百個并發(fā)用戶執(zhí)行操作時，不建議使用并行執(zhí)行操作。 并行不適用場景： 短交易（幾秒鐘或更少）。在這些環(huán)境中并行執(zhí)行是沒有什么用處，因為協(xié)調(diào)并行執(zhí)行服務器會增加相關的成本，這種協(xié)調(diào)的成本可能超過并行帶來的好處。 已經(jīng)大量使用CPU、內(nèi)存或I / O資源的環(huán)境。并行執(zhí)行旨在利用其它可用的硬件資源，如果沒有這樣的資源可用，那么并行執(zhí)行

42、不產(chǎn)生任何效益，并且可能損害性能。,40, 2010 Oracle Corporation Proprietary and Confidential,Tips：并行執(zhí)行工作過程,用戶連接到數(shù)據(jù)庫,User,后臺進程被派生,當用戶發(fā)出一個并行SQL，后臺進程就變成為QC（Query Coordinator）,Messages,QC connection,Parallel server connection,41,Producers,Consumers,Query coordinator,Hash join 會首先選擇小表作為驅動表，進行掃描，在這個例子中Customers表示小表，4個Produ

43、cer 并行進程并行掃描，并且返回就過給Consumers進程,P8,P7,P6,P5,SALES Table,CUSTOMERS Table,SELECT c.cust_last_name, s.time_id, s.amount_sold FROM sales s, customers c WHERE s.cust_id = c.cust_id;,并行執(zhí)行的工作機制,42,Producers,Consumers,Query coordinator,一旦4個Producer進程完成了小表的掃描，他們開始對大表（Sales表）進行并行掃描，并返回結果給Consumer進程,P8,P7,P6,P

44、5,SALES Table,CUSTOMERS Table,SELECT c.cust_last_name, s.time_id, s.amount_sold FROM sales s, customers c WHERE s.cust_id = c.cust_id;,并行執(zhí)行的工作機制,43,Producers,Consumers,P8,P7,P6,P5,一旦Consumer進程收到了兩個表的數(shù)據(jù)，他們開始做并行的Join操作，一旦完成join操作，就將結果返回給QC進程,Query coordinator,SALES Table,CUSTOMERS Table,SELECT c.cust_

45、last_name, s.time_id, s.amount_sold FROM sales s, customers c WHERE s.cust_id = c.cust_id;,并行執(zhí)行的工作機制,44,SELECT c.cust_last_name, s.time_id, s.amount_sold FROM sales s, customers c WHERE s.cust_id = c.cust_id;,Tips：檢查并行執(zhí)行情況,45, 2010 Oracle Corporation Proprietary and Confidential,select sum(revenue),

46、 store from line_items Where profit(price,units) 0.2 order by store,Data on Disk,Query Servers,scan,scan,scan,sort A-K,sort L-S,sort T-Z,dispatch work; assemble results,Producers or scanners,Consumers Or Aggregators),Coordinator,并行進程的任務,46,Tips：不同操作并行方式Group by,Order by,進程1,進程2,進程3,進程4,進程5,進程6,進程1,進

47、程2,進程3,進程4,進程5,進程6,Hash分布,Hash key 1,Hash key 2,Hash key 3,Hash key 4,Hash key 5,Hash key 6,進程1,進程2,進程3,進程4,進程5,進程6,進程1,進程2,進程3,進程4,進程5,進程6,Range分布,A-H,I-M,N-R,S-U,V-X,Y-Z,Group By,Order By,Hash分區(qū)效率最高,Range分區(qū)效率最高,47,Tips:數(shù)據(jù)分布的例子,select count(*) from yellow y, green g where y.deptno = g.deptno,48,Tip

48、s：并行進程中不同的數(shù)據(jù)分布方式,49,Tips：并行度設置,建議采用默認傳統(tǒng)手工管理方式 (PARALLEL_DEGREE_POLICY=LIMITED/MANUAL)。在這種情況下需要對數(shù)據(jù)量有一個預估，然后設置的并行度應該給每個進程分配到的至少為上百兆數(shù)據(jù)。一個典型的初始預估模式： 200MB以下的小對象：不并行 200MB-5GB的對象：并行度4 5GB以上的大對象：并行度32 * 最終采用的并行度需要經(jīng)過測試才能確定,50, 2010 Oracle Corporation Proprietary and Confidential,Tips：啟用并行執(zhí)行,1.在表上執(zhí)行并行,alter

49、 table sales parallel ; alter table customers parallel ;,select /*+ parallel(c) parallel(s) */ c.state_province, sum(s.amount) revenue from customers c, sales s where s.customer_id = c.id,/并行查詢 alter session force/enable parallel query ; alter session disable parallel query ; /并行DML alter session fo

50、rce/enable parallel DML; alter session disable parallel dml; /并行DDL alter session force/enable parallel DDL; alter session disable parallel DDL;,2.在SQL級使用Hint啟用并行,3.在會話（Session）上，啟用并行,51, 2010 Oracle Corporation Proprietary and Confidential,Tips：如何檢查并行執(zhí)行的情況,1.檢查系統(tǒng)并行進程使用情況： 2.檢查系統(tǒng)各個并行任務分布情況 3.檢查并行的系統(tǒng)

51、統(tǒng)計,Select inst_id,status,count(*) from gv$px_process group by inst_id,status; INST_ID STATUS PX_SERVERS# - - - 1 AVAILABLE 4 1 IN USE 12 2 AVAILABLE 8 2 IN USE 8,Select inst_id,sid,degree,req_degree,server# from gv$px_session;,select name,value from v$sysstat where upper(name) like %PARALLEL OPERATI

52、ONS% or upper(name) like %PARALLELIED or upper(name) like %PX%;,52, 2010 Oracle Corporation Proprietary and Confidential,Tips：并行服務相應的參數(shù),53, 2010 Oracle Corporation Proprietary and Confidential,提綱,數(shù)據(jù)庫空間管理 數(shù)據(jù)倉庫物理表設計（表壓縮、表分區(qū)） 數(shù)據(jù)加載 并行執(zhí)行 開發(fā)注意事項（集合、關聯(lián)操作） 索引管理 統(tǒng)計信息收集,54, 2010 Oracle Corporation Proprietary

53、 and Confidential,Tips：表Join方法（1）-Sort Merge Join,1.排序合并連接(sort merge join)： 排序合并連接(sort merge join):是一個集合操作,分類合并連接應該在索引不能用于查詢的情況下考慮. 在于把兩個大表讀入內(nèi)存并進行排序的成本，成本計算如下：cost of merge join = access cost of A + access cost of B + (sort cost of A + sort cost of B) 適用于查詢整體返回大量結果集，兩個大表做連接，且表已經(jīng)排過序的情況下。當兩個表已經(jīng)排過序時，

54、使用排序合并連接的性能可能會優(yōu)于散列連接。HASH_AREA_SIZE和SORT_AREA_SIZE設置過小，可能會導致優(yōu)化器避開散列連接而選擇排序合并連接。使用HINTS：use_merge，可以是表使用Sort Merge Join關聯(lián)。,55, 2010 Oracle Corporation Proprietary and Confidential,Tips：表Join方法（2）-Nested Loop Join,2.嵌套循環(huán)連接(nested loop join)： 嵌套循環(huán)連接(nested loop join):是一個行操作,這種連接是OLTP應用中最常見的連接操作,并且通常很有效

55、，但對于批量數(shù)據(jù)操作往往效率不高。因為此連接方法高度利用了索引。在于外表返回的每一行都要在內(nèi)表中進行匹配的成本，成本計算如下：cost of nested loop = access cost of outer + (number of rows from outer * access cost of inner) 適用于外表有效基數(shù)較小，內(nèi)表連接字段含有索引，且查詢整體返回結果集不太大（小于1萬行）的情況下。使用HINTS：use_nl可以使執(zhí)行計劃按照Nested Loop方式執(zhí)行。,56, 2010 Oracle Corporation Proprietary and Confident

56、ial,Tips：表Join方法（3）-Hash Join,3.散列連接(hash join)： 幾乎是使用被連接表的全表掃描而來完成的.散列連接對于大表的相互連接,或者一個小表和一個超大型表的連接. 在于將小表讀入內(nèi)存分成若干散列表，然后由大表對每個散列表都進行一次匹配的成本，成本計算如下：cost of hash join = access cost of smaller + (access cost of bigger * number of hash partitions of smaller) 適用于查詢整體返回大量結果集，且有較小的連接表可以放入內(nèi)存作為散列表的情況下。適用散列連接

57、要注意HASH_AREA_SIZE要足夠大，可以容下散列表。如果散列表無法完全放入內(nèi)存，要設置較大的臨時段，從而盡量提高I/O性能。HINTS：use_hash,57, 2010 Oracle Corporation Proprietary and Confidential,Tips:修改Row by Row 操作為集合操作,declare cur rec_cur; rec type_table%rowtype; begin open cur for select * from table1; loop fetch cur into if condition(rec) then insert

58、into table2 else insert into table3 end if; end loop; end;,Move away from row by row processing,insert /*+ append */ into table2 select * from table1 where condition insert /*+ append */ into table3 select * from table1 where not condition ,insert /*+ append */ first when condition then into table2 values else into table3 values select * from table1,To set based processing,58,Tips：改寫Delete操作,59,Tips：改寫UPDATE 操作,60,Tips：對Merge的改寫,61,Tips：多表InsertUnconditional INSERT ALL,INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, h