Oracle 数据仓库性能优化(上)Extreme Performance with Oracle Data Warehousing

Implement Best Practices for Extreme Performance with Oracle Data Warehousing
Partner Solution Center(Shen Zhen)
Agenda
• Core Data Warehousing Performance Features • Database Design and Data Loading • Performance Management
Partition Pruning
Q: What was the total sales for the weekend of May 20 - 22 2008?
Sales Table
May 18th 2008 May 19th 2008 May 20th 2008
May 21st 2008 May 22nd 2008 May 23rd 2008 May 24th 2008
2
/
PLAN_TABLE_OUTPUT Plan hash value: 722415528
----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
– Partition pruning for queries • What range of data do the queries touch - a quarter, a year? – Consider data loading frequency • Is an incremental load required?
from dual connect by level <= date '2003-01-01' - date '2000-01-01'
10
/
SQL> begin
2
3 4
dbms_stats.gather_table_stats(
ownname => user, tabname => 't_sales');
SELECT a.* FROM table_a a, big_partitioned_table b WHERE a.calendar_date > :a AND a.calendar_date = b.calendar_date
Partition Pruning • What indicate Partition Pruning ?
Partition Pruning
• How to fix SQLs that won't Partition Prune ?
– The syntax WHERE partition_key oper (sub-query), Try putting the sub-query into a PL/SQL function, and change your syntax to WHERE partition_key oper my_func() – Use ranges instead of functions of partition keys
6
end;
Partition Pruning case study
SQL> explain plan for 2 3 select * from t_sales where to_char(sales_date,’YYYY’) ='2000'
4
/
Explained. SQL> select * from table(dbms_xplan.display)
Partition Pruning case study
SQL> create table t_sales (sales_date not null) 2 3 partition by range (sales_date) (partition Y2000 values less than (date '2001-01-01'),
-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------
• Partition by hash to improve join performance between fact tables and / or dimension tables
– If all dimension have different join columns use join column for most common join in the queries or columns have most distinct values – Hash partition use partition wise join – Number of hash partitions should be power of 2 (#CPU X 2 X Nodes/Default DOP)
4
5 6
partition Y2001 values less than (date '2002-01-01'),
partition Y2002 values less than (date '2003-01-01')) as
7
8 9
select date '2000-01-01' + rownum - 1
• Parallel Execution should be used
– Instead of one process doing all the work multiple processes working concurrently on smaller units
Partitioning Design Common Sense • Range/List partition large fact tables
– Statistics management – Initialization Parameters – Workload Monitoring
Core Data Warehousing Performance Features
3 Ps - Power, Partitioning, Parallelism
Select sum(sales_amount)
From SALES Where sales_date between
ຫໍສະໝຸດ Baidu
to_date(‘05/20/2008’,’MM/DD/YYYY’)
And to_date(‘05/23/2008’,’MM/DD/YYYY’);
Only the 3 relevant partitions are accessed
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
Partition Pruning • When can I use Partition Pruning ?
– Range partiton key =, <[=], >[=], BETWEEN, or LIKE predicates,comparing the key to either literals, bind variables, literal / bind variable expressions – For List and Hash partitions, use = or IN comparing literals/bind variables – Perform a join to a partitoned table using the partition key in an equals clause
4
/
Explained. SQL> select * from table(dbms_xplan.display)
2
/
PLAN_TABLE_OUTPUT Plan hash value: 722415528
----------------------------------------------------------------------------| Id | Operation | Pstart| Pstop | | Name | Rows | Bytes | Cost (%CPU)| Time
| 0 | SELECT STATEMENT | | 587K| 14M| 805 (2)| 00:00:10 | | |
| 1 | PARTITION RANGE ITERATOR| | 587K| 14M| 805 (2)| 00:00:10 | 6 | 8 | |* 2 | TABLE ACCESS FULL | BIG_ALBUM_SALES | 587K| 14M| 805 (2)| 00:00:10 | 6 | 8 |
• Balanced Hardware Configuration
– Weakest link defines the throughput
• larger tables or fact tables should be partitioned
– Facilitates data load, data elimination and join performance – Enables easier Information Lifecycle Management
– Explain Plan lines showing • PARTITION RANGE ITERATOR • PARTITION RANGE OR • PARTITION RANGE SINGLE
SQL> select * from big_album_sales where release_date > '01-JUN-2006'; 587694 rows selected. Execution Plan
---------------------------------------------------------------------------------------------| | | | |* | 0 | SELECT STATEMENT | | | | | 3 | 3 | 3 | 24 | 24 | 24 | 2 2 2 (0)| 00:00:01 (0)| 00:00:01 (0)| 00:00:01
1 | PARTITION RANGE ALL| 1 | 3 |
2 | TABLE ACCESS FULL | T_SALES | 1 | 3 |
Partition Pruning case study
SQL> explain plan for 2 3 select * from t_sales where sales_date between date '2000-01-01' and date '2000-12-31'