topic Re: eva performance w/Oracle logical partitions? in MSA Storage

eva performance w/Oracle logical partitions?

CA1048164 — Mon, 06 Dec 2004 10:31:44 GMT

We have an HP 9000 running hp-ux 11.11, which is attached to an EVA 5000 SAN, and have taken advantage of to the virtualization of disks, blah, blah, blah.

I've recently created an Oracle partitioned table, which contains approximately 63 million rows of data, spread out over 25 logical partitions using an “Effective Date” to determine which partition each row resides. In a more traditional environment, doing this has resulted in fairly significant improvements in query performance against this table. However, the differences in this case do not suggest that there is anything to gain by implementing Oracle partitioning in this environment.

Has anyone done any work in this area and to they have experiences they would like to shar

Re: eva performance w/Oracle logical partitions?

Sheldon Smith — Tue, 07 Dec 2004 11:40:58 GMT

"25 logical partitions". Does that mean 25 virtual disks? How many disk groups are you using? How many disks in each group?
Spindle performance will average across all virtual disks within a given disk group. Got any particularly "hot" Oracle partitions?

Re: eva performance w/Oracle logical partitions?

CA1048164 — Tue, 07 Dec 2004 13:08:31 GMT

Sheldon,
When I say logical partitions, I mean Oracle constructs that are totally invisible to this or any disk subsystem. From this external perspective, there are data files that collectively make up an Oracle tablespace. Internally, there may be partitions defined within one or more tablespaces. In previous generations, one might place datafiles in separate disk arrays in an attempt to spread i/o across many spindles. However, this particular system is virtual meaning that no matter how many or how few arrays we define the i/o is spread accross all disks, of which there are something like 178.

Therefore, I question whether or not creating Oracle logical partitions is worth the effort. I'm hoping that somewhere there is expertise in this specific area.

Re: eva performance w/Oracle logical partitions?

Bill Davison — Tue, 07 Dec 2004 19:16:55 GMT

A couple things to consider:

1. When you query or update a partitioned table, the optimizer can sometimes use a subset of the total # of partitions ("partition pruning"). This can speed performance by reducing I/O.

2. Many people partition to improve performance during maintenance operations. For example, you can instantly drop the oldest partition vs. the time it takes to do a delete with a where clause. Index rebuilds can also be done a partition at a time.

Bill

Re: eva performance w/Oracle logical partitions?

Edgar_8 — Wed, 08 Dec 2004 02:35:55 GMT

Hi,

We have a DW database that has daily tables(approx.80million records) which is range partitioned
by date/time & are loaded via sqlldr direct path. Partitioning has improved load; sql query performance; and
database storage manageability. If you have one fact table which is partitioned, I preseume that the fact
table is also having partitioned indexes?

hth

Re: eva performance w/Oracle logical partitions?

CA1048164 — Wed, 08 Dec 2004 08:18:50 GMT

Edgar,
This table has partitioned local indexes.