Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Global/partition level statistics
Hi,
I need some help on how to gather the database statistcs ?
I have close to 250 GB of database spread into 150 partitions, each partitions is close to 1.5 to 2 GB. And each partition has 16 tables. These partitions are locally managed partitions and partitions are based on column ID and it is RANGE partitioned.
Example:
Column ID Range
101 - 200 201 - 300 301 - 400
In case of only PARTITION LEVEL STATISTICS queries like below had a very good performance
/* Hitting only 1 partition */
SELECT col1, col2
FROM tableA
WHERE ID > 100 and ID < 200
while the following query had a bad performace.
/* Hitting more than 1 partition */
SELECT col1, col2
FROM tableA
WHERE ID > 100 and ID < 300
In case of GLOBAL STATISTICS + PARTITION LEVEL STATISTICS, the above performance almost reversed. I get good performace with queries hitting more than 1 partition, while poor performace when queries are hitting only one partition.
What strategy to used to get the best performace in both the scenerios ?
Other facts:
Statistics is gathered using the DBMS_STATS.GATHER_TABLE_STATS... with
CASCASE, AUTO SIZE etc.
Any Help...
Thanks in Advance.