Re: number of logical partitions in an extended parition

From: Nuno Souto <nsouto_at_optushome.com.au>
Date: 2 Mar 2003 21:27:37 -0800
Message-ID: <dd5cc559.0303022127.744a5fd3@posting.google.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<pan.2003.03.02.18.51.45.549744_at_yahoo.com.au>...
> > Have you ever seen a Peoplesoft on Oracle implementation?
>
> Funny you should mention that. Just after posting the above, I had a new
> Peoplesoft user on my course, and he was describing the mess he has to
> administer. Much along the lines you've just outlined!!

With Financials, it's 5500 tables and around 7500 indexes. Without doing any optimal or custom indexing! I call it a sad joke, but they call it something designed and developed to be "portable" by an "Oracle genius". Before your time, he used to work for Oracle here. Left for a R&D job in the US and joined Peoplesoft to go and help design/develop their stuff. Made it portable alright but created one of the biggest messes I've ever seen in the process...

> It's a shocker, no?! My poor student got all excited about Materialized
> Views. Until the slide came up that said 'not available for tables without
> a Primary Key', and I made the flippant coment 'which shouldn't be too
> much of a restriction, since this is a relational database we're managing
> here'. The poor bloke nearly cried!!

Well, part of the reason for the abnormal number of tablespaces is that it's supposed to be "portable" to DB2 and Informix. In those you NEED the high number of tablespaces. To squeeze out performance from their tablespace-partitioned caches.

Another problem with PS/Oracle is if you want for example to allocate LMTs sized by object class (or vicky-the-versa, depending on your approach).
Which is fine with PS: you are not restricted in the naming/number of tablespaces. Until you have to upgrade or patch ANYTHING in their software, that is. At which point NO scripts will work: they all expect tablespace names to be the original...

Don't get me started on their tuning "recommendations" and the use of 16 indexes on a highly volatile table.

And deranged concat indexes, something like:

create index x1 on tabx(col1,col2,col3);
create index x2 on tabx(col1,col2);
create index x3 on tabx(col1,col3);

create unique index xp on tabx(col1);
(!!!!!!)etc,etc.
NO WONDER the optimizer gets mixed up...

ARRRRRRGGHH!!!!
<patience is the biggest virtue of the Oracle/Peoplesoft DBA...>

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au Received on Sun Mar 02 2003 - 23:27:37 CST