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

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 for queries • What range of data do the queries touch - a quarter, a year? – Consider data loading frequency • Is an incremental load required?
• 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
| 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 |
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'
Select sum(sales_amount)
From SALES Where sales_date between
to_date(‘05/20/2008’,’MM/DD/YYYY’)
And to_date(‘05/23/2008’,’MM/DD/YYYY’);
Only the 3 relevant partitions are accessed
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)
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
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'),
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');
• 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
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.
---------------------------------------------------------------------------------------------| | | | |* | 0 | SELECT STATEMENT | | | | | 3 | 3 | 3 | 24 | 24 | 24 | 2 2 2 (0)| 00:00:01 (0)| 00:00:01 (0)| 00:00:01
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
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
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
2
/
PLAN_TABLE_OUTPUT Plan hash value: 722415528
----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
– 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
Partition Pruning • When can I use Partition Pruning ?