Exadata一体机最佳实践学习教案

1、会计学1Exadata一体机最佳实践一体机最佳实践2数据库空间管理数据库空间管理数据仓库物理表设计（表压缩、表分区）数据仓库物理表设计（表压缩、表分区）数据加载数据加载并行执行并行执行开发注意事项（集合、关联操作）开发注意事项（集合、关联操作）索引管理索引管理统计信息收集统计信息收集第1页/共89页第2页/共89页第3页/共89页第4页/共89页假设有4个并行进程进行数据加载，Uniform extend 大小为8MB当多块加载后，将会有很多未被填满的Extent存在，也就是说Extent中可能会有很多空间空洞在表扫描时，每个空间空洞会被扫描到，造成IO的浪费第5页/共89页假设有4个并行进程

2、进行数据加载，initial & next extend 大小为8MB当加载完成后，最后一些Extent大小会比其他Extent小，但是所有Extent都会被填满每个表或分区扫描将至扫描数据，而没有空闲空间被扫描采用Auto-allocate的优点在于大数据加载时，有最少的空间浪费，因为最后加载的Extent会被Trim到64KB的整数倍的大小，所以几乎没有太大的空间浪费，同时表扫描时也可以提高IO效率采用Auto-Allocate方式时，在创建分区表时，可以自动支持Large Extent（8MB Extent），而对于uniform方式，则无法支持第6页/共89页CREATE TA

3、BLE 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),PARTITIO

4、N 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 File #, p2

5、 Block #, p3 Reason Code from v$session_wait where event = buffer busy waits;为了避免文件头争用情况，可以采用多个为了避免文件头争用情况，可以采用多个Big file tablespace来均匀存放来均匀存放Partition table。例如：下面。例如：下面语句使用语句使用4个表空间来均匀存放个表空间来均匀存放128个个Subpartition表：表：第7页/共89页第8页/共89页Create Table sales(.) parallel storage (INITIAL 8M NEXT 8M)(.)第9页/共

6、89页Alter system set deferred_segment_creation = FALSE scope=both第10页/共89页HWMHWMHWMALTER 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.第11页/共89页ALTER SHRINK SPACE

7、 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 OVERFLOW SHRINK SPACE;4第

8、12页/共89页14数据库空间管理数据库空间管理数据仓库物理表设计（表压缩、表分区）数据仓库物理表设计（表压缩、表分区）数据加载数据加载并行执行并行执行开发注意事项（集合、关联操作）开发注意事项（集合、关联操作）索引管理索引管理统计信息收集统计信息收集第13页/共89页第14页/共89页第15页/共89页第16页/共89页第17页/共89页第18页/共89页第19页/共89页第20页/共89页第21页/共89页第22页/共89页第23页/共89页SELECT sum(amount_sold) FROM sales s, customer cWHERE s.cust_id=c.cust_id;B

9、oth 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 1Sub part 2Sub part 3Sub part 4Part

10、2Part 3Part 4Sub part 2Sub part 3Sub part 4Sub part 1Part 1Part 2Part 3Part 4对于partition-wise join而言，查询的并行度必须是等于或成倍于表分区的数量第24页/共89页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-YYYY);Q: What was the total sales for the year

11、1999?Only the 4 relevant partitions are accessed第25页/共89页Only 4 partitions are touched 9, 10, 11, & 12, , 第26页/共89页Partition 1Partition 5Partition 10:129101920Overall partition #range partition #Sub-partition #第27页/共89页29数据库空间管理数据库空间管理数据仓库物理表设计（表压缩、表分区）数据仓库物理表设计（表压缩、表分区）数据加载数据加载并行执行并行执行开发注意事项（集合

12、、关联操作）开发注意事项（集合、关联操作）索引管理索引管理统计信息收集统计信息收集第28页/共89页第29页/共89页第30页/共89页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 ();第31页/共8

13、9页第32页/共89页第33页/共89页Insert /*+ APPEND */ into Sales partition(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”以并行方式运行，可以以下两种方式设置并行度：以并行方式运行，可以以下两种方式设置并行度：1）在）在CTAS和和

14、IAS中加入中加入PARALLEL hint；2）在）在External table和目标加载和目标加载Table上，设置上，设置PARALLEL子句。一旦设置了并行度，子句。一旦设置了并行度，CTAS将自动执行并行化的将自动执行并行化的“direct path load”，而，而IAS将不会。为了让将不会。为了让IAS能够并行化执行能够并行化执行“direct path load”，出了设置并行度外，还必须通过，出了设置并行度外，还必须通过alter the session to enable parallel DML。SQL如下：如下：第34页/共89页Select name,value/

15、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统计值，来检查多少统计值，来检查多少I/O被减少。被减少。SQL如下：如

16、下：第35页/共89页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;第36页/共89页DBA1. Create external table for flat files5. Alter table Sales exchange partition May_24_2

17、008 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操作过程操作过程 第37页/共89页39数据库空间管理数据库空间管理数据仓库物理表设计（表压缩、表分区）数据仓库物理表设计（表压缩、表分区）数据加载数据加载并行执行并行执行开发注意事项（集合、关联操作）开发注意事项（集合、关联操作

18、）索引管理索引管理统计信息收集统计信息收集第38页/共89页第39页/共89页用户连接到数据库User后台进程被派生当用户发出一个并行SQL，后台进程就变成为QC（Query Coordinator）QC从并行进程组中获得Parallel servers ，然后QC分配任务给Parallel servers进程Parallel servers 是一个独立的Session，从并行进程组中分配，并完成指定的具体任务Parallel servers通过Shared Pool的内存传递通信消息，与QC进程和并行进程进行互相通信第40页/共89页ProducersConsumersQuery coord

19、inatorP1P2P3P4Hash join 会首先选择小表作为驱动表，进行扫描，在这个例子中Customers表示小表，4个Producer 并行进程并行扫描，并且返回就过给Consumers进程P8P7P6P5SALESTableCUSTOMERSTableFROM sales s, customers c;并行执行的工作机制并行执行的工作机制第41页/共89页ProducersConsumersQuery coordinatorP1P2P3P4一旦4个Producer进程完成了小表的扫描，他们开始对大表（Sales表）进行并行扫描，并返回结果给Consumer进程P8P7P6P5SAL

20、ESTableCUSTOMERSTableFROM sales s, customers c;并行执行的工作机制并行执行的工作机制第42页/共89页ProducersConsumersP1P2P3P4P8P7P6P5一旦Consumer进程收到了两个表的数据，他们开始做并行的Join操作，一旦完成join操作，就将结果返回给QC进程Query coordinatorSALESTableCUSTOMERSTableFROM sales s, customers c;并行执行的工作机制并行执行的工作机制第43页/共89页SELECT c.cust_last_name, s.time_id, s.a

21、mount_soldFROM sales s, customers c;Query CoordinatorTips：检查并行执行情况：检查并行执行情况第44页/共89页select sum(revenue), storefrom line_itemsWhere profit(price,units) order by storeData on DiskQuery ServersProducers or scannersConsumersOr Aggregators)Coordinator第45页/共89页进程1进程2进程3进程4进程5进程6进程1进程2进程3进程4进程5进程6Hash分布Has

22、h key 1Hash key 2Hash key 3Hash key 4Hash key 5Hash key 6进程1进程2进程3进程4进程5进程6进程1进程2进程3进程4进程5进程6Range分布A-HI-MN-RS-UV-XY-ZGroup ByOrder ByHash分区效率最高Range分区效率最高第46页/共89页select count(*) from yellow y, green g where y.deptno = g.deptno 第47页/共89页分布方式说明Hash, Hash使用Hash函数映射Join字段，映射完成后，每个查询引擎执行Join，建议Join表siz

23、e相近，使用hash-join或者sort merge joinBroadcast,None外表的行广播到每个查询进程，内表随机分区，建议外表size大大小于内表。通用规则：inner table size * number of query servers outer table sizeNone,Broadcase内表的行广播到每个查询进程，建议内表的size大大小于外表。通用规则：inner table size * number of query servers 0 1-Jan-0 9 / commit / create table tx_log_new nologging paral

24、lel 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, . from sales_ledger / alter table tx_log rename to tx_log_old / alter table tx_log_new to tx_log /第59页/共89页第60页/共89页INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_hist

25、ory VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;8 rows created. 第61页/共89页INSERT ALL WHEN SAL 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT

26、employee_id EMPID,hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;4 rows created. 第62页/共89页 INSERT FIRST WHEN SAL 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like (%00%) THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like (%9

27、9%) 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.第63页/共89页 INSERT ALL INTO sales_info VALUES (employee_id,week_id,s

28、ales_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,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_

29、THUR,sales_FRI FROM sales_source_data;5 rows created. 第64页/共89页GETcandidate row from outer queryEXECUTEinner query using candidate row valueUSEvalues from inner query to qualify or disqualify candidate row第65页/共89页 SELECT column1, column2, . FROM table1 WHERE column1 operator (SELECT column1, column

30、2 FROM table2 WHERE expr1 = .expr2);outerouter第66页/共89页SELECT last_name, salary, department_idFROM employees outerWHERE salary (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id);Each time a row from the outer queryis processed, theinner query isevaluated.select last_name,s

31、alary,a.department_id from employees a,(select department_id,avg(salary) avg_sal from employees group by department_id) b第67页/共89页第68页/共89页select a,b from t1, t2 where t1.a =t2.a (+) - if a is a nullable column, then nvl(t1.a,9) = nvl(t2.a (+),9) and t1.b = t2.b(+) - if b is nullable char column the

32、n nvl(t1.b,?)= nvl(t2.b(+),?) and t2.a is null and t2.b is null / select a, b from t1 minus select a,b from t2;第69页/共89页FROM employees empWHERE emp.employee_id NOT IN FROM employees mgr);no rows selected第70页/共89页AND TRUE FALSE NULL TRUE TRUE FALSE NULL FALSE FALSE FALSE FALSE NULL NULL FALSE NULL OR

33、 TRUE FALSE NULL TRUE TRUE TRUE TRUE FALSE TRUE FALSE NULL NULL TRUE NULL NULL NOT TRUE FALSE NULL FALSE TRUE NULL 第71页/共89页SELECT AVG(commission_pct)FROM employees;SELECT AVG(NVL(commission_pct, 0)FROM employees;12第72页/共89页隐式数据类型转换容易导致如下问题隐式数据类型转换容易导致如下问题: Bad Plans because index cannot be used Bad

34、 Plans because partition pruning may not take place 1722 Errors if there is non numerical characters Increased resource usage converting data 1.Poor cardinality estimates 第73页/共89页Non-equi joins present a number of challenges Massive data expansion which means more rows which mean more CPU needed wh

35、ich means potentially slow Difficult getting good join cardinality estimates 1.Difficult in using parallel and hash based join techniques 第74页/共89页第75页/共89页77数据库空间管理数据库空间管理数据仓库物理表设计（表压缩、表分区）数据仓库物理表设计（表压缩、表分区）数据加载数据加载并行执行并行执行开发注意事项（集合、关联操作）开发注意事项（集合、关联操作）索引管理索引管理统计信息收集统计信息收集第76页/共89页第77页/共89页select t1.object_name, t2.object_name from t t1, t t2 where t1.object_id = t2.object_id and t1.owner = WMSYS Rows Row Source Operation - - 528384 HASH JOIN 8256 TABLE ACCESS FULL T 1833856 TABLE ACCESS FULL T 第78