Printable View
There is a big fact table (70 gig) in our database. There are 11 years of data on the table. The table is paritioned at year. If I split the table into two tables, one for archiving data ( about 7 years of data), another one for the new data (about 4 years of data), is it going to improve the query performance? The new data table is usually the query access the most.
How could I find it out?
Thanks!
how is this table accessed?
does partition pruning occure?
why do you partition such a large table only per year?
is the table often joinded with another large table?
which version of Oracle do you use?
why do you think that the IO generated by this table is a performance problem?
1. There are full table scans on this table sometimes. Even I created indexes on all the columns for the query, there are still full table scan on the excution path.
2. Partition pruning does occure.
3. It's the nature of the data requires us to partition the data by year.
4. THe table join often with other tables. Those tables are relatively small, like 8 gig, 4gig...
5. 9.2.0.6
6. After we load history data, the performance seems get bad.
Is the table partitioned by a YEAR identifier, or is there a date column with a lower level of granularity by which it is partitioned (eg. month, day, hour)?
Do you understand why full scans sometimes occur, even if everything is indexed?
Have you measured performance problems after loading historical data, and understood what has changed to cause any problems?
1. The table is partitioned by YEAR identifier. What would be the difference if it's in other ways? Why do you ask?
2. No, I don't know why full scans occur. I assume it's selectivity of the indexes, or the cost of doing full scan is less than index scan. How could I find out why optimizer takes full scan instead of picking up indexes?
3. The performance problems was measured by response time. The change is the data volumn. What would be changed after data volumn changed?
Well, if you are getting true full table scans, then how do you propose to run those queries if you split the table into multiple tables? If you are getting full partition scans (ie. partition pruning but no index-based access) then the total size of the table is not relevant, it's just the size of the partitions being accessed that matters.
Your difference in performance might be due to a number of issues. For example, the optimizer might be making a bad choice about the best access method because you haven't updated both global and partition statistics. Alternatively the optimizer might just be responding to the different data volumes correctly, and an increased data volume gives longer response times.
I don't think that the answer is to split the table - you have already effectively done that by partitioning. The key would just be to make sure that the optimizer is leveraging the partitioning by giving you the pruning that you expect.
I guess that it is not the reading of the data which is taking most time but the "group by", "order by" and join with the dimension tables.
Are you using table compression? It's an easy way to reduce the the IO.
1. The global and partition statistics updated right after data loading. The volumn is only 30% more, but the response time is like 10 times slower.
2. How often should the index get analyzed? Indexes are all created with COMPUTE STATISTICS. How big is the impact on the database performance if analyzing at day time? (using dbms_stats package).
3. The big fact table is range-hash partitioned. Oracle so far does not support table compression on a composite partitioned table, does it? Have you ever done a table compression on a composite partitioned table? How much it would improve the performance? What's the disadvantange if just compress the partitions without update?
Thanks!
where have you seen that oracle doesn't support compression on composite partitioned table?
As far as I remember you can't define it at subpartition level but that dosen't mean that the subpartition is not compressed if compression has been enabled at partition level.
1. Then you need to run a trace on the SQL before and after the load, and see what the extra response time is attributable to. Maybe a change in execution plan, for example, or maybe something else.
2. When you analyze the table use DBMS_STATS and specify cascade => true
Only you can answer on the impact of analyzing during the day time, but if you have made no effort to physically order the rows you are adding then you can most likely use a much lower estimate percentage than you think. Have a look at this: http://www.jlcomp.demon.co.uk/stats_i.html
3. "What's the disadvantange if just compress the partitions without update?" What do you mean here?
1, The following about composite table compress is from metalink:
While attempting to move a subpartition of a composite table with "compress" option, it fails with the following errors:
ORA-14160: this physical attribute may not be specified for a table subpartition Trying to move at the partition level with "compress" option fails with
ORA-14020: this physical attribute may not be specified for a table partition
SQL> alter table fct_incoming_traffic move subpartition SALES_01_JAN2003
tablespace users compress;
alter table fct_incoming_traffic move subpartition SALES_01_JAN2003
tablespace users compress
*
ERROR at line 1:
ORA-14160: this physical attribute may not be specified for a table
subpartition
SQL> alter table fct_incoming_traffic move partition SALES_JAN2003
compress
2 (subpartition SALES_01_JAN2003 tablespace users,
3 subpartition SALES_02_JAN2003 tablespace users);
(subpartition SALES_01_JAN2003 tablespace users,
*
ERROR at line 2:
ORA-14020: this physical attribute may not be specified for a table partition
Cause
Compress physical attribute is not available for the Subpartitions at the moment.
Currently an Enhancement Request Bug:2210439 exists for the same.
Fix
Compress physical attribute cannot be used for the Subpartitions. So this operation is not possible.
References
Bug 2210439 - To Add Compress Option For Subpartitions
Note 214168.1 - Logging an Enhancement Request
Thanks again!
The columns are described here: http://download-west.oracle.com/docs...52.htm#1303953
You can not define compression at subpartition level.
But you can define compression at table or partition level.
e.g.
ALTER TABLE sales_2 modify PARTITION sales_2000 COMPRESS
even if sales_2 is subpartitioned.
To compress subpartitions you could do something like this (I have no 9iR2 DB available so I did it on 10g):
SQL> CREATE TABLE sales_2
2 (
3 prod_id NUMBER,
4 cust_id NUMBER,
5 time_id DATE,
6 channel_id NUMBER,
7 promo_id NUMBER,
8 quantity_sold NUMBER(10,2),
9 amount_sold NUMBER(10,2)
10 )
11 NOCOMPRESS
12 PARTITION BY RANGE (time_id)
13 SUBPARTITION BY HASH (cust_id)
14 SUBPARTITIONS 4
15 (
16 PARTITION sales_1999 VALUES LESS THAN (TO_DATE('20000101' , 'YYYYMMDD')),
17 PARTITION sales_2000 VALUES LESS THAN (TO_DATE('20010101' , 'YYYYMMDD')),
18 PARTITION sales_2001 VALUES LESS THAN (TO_DATE('20020101' , 'YYYYMMDD'))
19 )
20 /
Table created.
SQL>
SQL> INSERT /*+ append */
2 INTO sales_2
3 SELECT *
4 FROM sales
5 /
918846 rows created.
SQL>
SQL> COMMIT
2 /
Commit complete.
SQL>
SQL> BEGIN
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => 'SH'
4 ,TabName => 'SALES_2'
5 ,Granularity => 'SUBPARTITION'
6 ,Estimate_Percent => 10
7 ,Block_sample => TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT table_name
2 , partition_name
3 , subpartition_position
4 , blocks
5 FROM user_tab_subpartitions
6 WHERE table_name = 'SALES_2'
7 /
TABLE_NAME PARTITION_NAME SUBPARTITION_POSITION BLOCKS
------------------------------ ------------------------------ --------------------- ----------
SALES_2 SALES_1999 1 535
SALES_2 SALES_1999 2 527
SALES_2 SALES_1999 3 549
SALES_2 SALES_1999 4 519
SALES_2 SALES_2000 1 295
SALES_2 SALES_2000 2 294
SALES_2 SALES_2000 3 309
SALES_2 SALES_2000 4 283
SALES_2 SALES_2001 1 311
SALES_2 SALES_2001 2 335
SALES_2 SALES_2001 3 328
SALES_2 SALES_2001 4 336
12 rows selected.
SQL>
SQL> CREATE TABLE sales_2_tmp
2 (
3 prod_id NUMBER,
4 cust_id NUMBER,
5 time_id DATE,
6 channel_id NUMBER,
7 promo_id NUMBER,
8 quantity_sold NUMBER(10,2),
9 amount_sold NUMBER(10,2)
10 )
11 COMPRESS
12 PARTITION BY HASH (CUST_ID)
13 PARTITIONS 4
14 /
Table created.
SQL>
SQL> INSERT /*+ append */
2 INTO sales_2_tmp
3 SELECT *
4 FROM sales_2 partition (sales_2000)
5 /
232646 rows created.
SQL>
SQL> COMMIT
2 /
Commit complete.
SQL>
SQL> BEGIN
2 SYS.DBMS_STATS.GATHER_TABLE_STATS (
3 OwnName => 'SH'
4 ,TabName => 'SALES_2_TMP'
5 ,Granularity => 'PARTITION'
6 ,Estimate_Percent => 10
7 ,Block_sample => TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT table_name
2 , partition_name
3 , blocks
4 FROM user_tab_partitions
5 WHERE table_name = 'SALES_2_TMP'
6 /
TABLE_NAME PARTITION_NAME BLOCKS
------------------------------ ------------------------------ ----------
SALES_2_TMP SYS_P797 117
SALES_2_TMP SYS_P798 116
SALES_2_TMP SYS_P799 121
SALES_2_TMP SYS_P800 111
SQL>
SQL> ALTER TABLE sales_2 EXCHANGE PARTITION sales_2000 WITH TABLE sales_2_tmp
2 /
Table altered.
SQL>
SQL> SELECT table_name
2 , partition_name
3 , subpartition_position
4 , blocks
5 FROM user_tab_subpartitions
6 WHERE table_name = 'SALES_2'
7 /
TABLE_NAME PARTITION_NAME SUBPARTITION_POSITION BLOCKS
------------------------------ ------------------------------ --------------------- ----------
SALES_2 SALES_1999 1 535
SALES_2 SALES_1999 2 527
SALES_2 SALES_1999 3 549
SALES_2 SALES_1999 4 519
SALES_2 SALES_2000 1 117
SALES_2 SALES_2000 2 116
SALES_2 SALES_2000 3 121
SALES_2 SALES_2000 4 111
SALES_2 SALES_2001 1 311
SALES_2 SALES_2001 2 335
SALES_2 SALES_2001 3 328
SALES_2 SALES_2001 4 336
12 rows selected.
SQL> spool off