Hello friends,

Recently I made a reorganization on two tablespaces in my database: I have Oracle 8.1.7.4, with Baan IVc2 in a Solaris 8 server. The reorg was ok. Basically I worked with two tablespaces with a lot of wasted space, to shrink them. The data tablespace and the index tablespaces both had 32 GB and I have them now in 16 GB each.

For this I made a temporary tablespace, move the tables with the command: alter table <table_name> move tablespace <temp_tablespace>;
Droped and recreate tje original tablespace with less space and bring back the tables with the same command.

Similar process for the index tablespace, with the command: alter index <index_name> rebuild tablespace <temp_tablespace>;

The problem now is thar Baan is performing really slow. Closing a project takes more than 30 minutes!! and other process are as well very slow, even in the sales floor, it takes too long to make an invoice.

I wonder if anybody can send me any suggestion about what else should I review in order to get back the performance time we had before the reorganization. According to Oracle support, the process made was ok and there's no reason for the degradation time.

I think there should be some parameter or something with shared memory maybe, that is causing the slow performance.

Any suggestion will be highly apreciated.

Thanks in advance.

Did you re-analyze the tables after you moved them? you probably need
to.

Also, make sure that the tablespace name is the same as it used to be, or change your ora_storage... don't know what PS you're on but back in 1997 that bit me in the butt.

Dave

Hi Dave,

I haven't reanalyze tables nor indexes yet. Baan support is recomending to reorganize some tables, via ttaad4225m000 "Reorganize tables", so this is maybe what I will do. But even so, any other suggestion would be great.
By the way, tablespaces names are the same, and I made the operation in the same filesystems. The only difference is that the new tablespace has less datafiles.

One point, just after completed the tablespace reorganization, I ran a script that analyzed the indexes of the bigger tables, about 52 indexes. But I ran the analyze for some tables, not all corresponding to the indexes. And in both cases I only estimante a sample of 30 percent.

My actual PS is 6.1c.05.02

Thanks!

Hi there,


a) with your amount of limited data I recommend to analyze all tables and forget about a sample size, just do "compute statistics".

b) How many extends do your new tables and indexes have? If you did not specify anything, then they should use the storage paramters of the tablespace, which are by default very small. As a consequence you get many extends, which is not handled well in Oracle 8 and below.

c) Do not forget to set the "optimizer_max_permutations" parameter for oracle.

d) Forget about "Reorganize tables" in Baan. I am suprised SSA is still recommending this. This session is just dumping a table and loading it again. So if you do not specify decent values in ora_storage, it is just a waste of time. This session was created to be used in connection with tbase, where deleting a row, just marked it deleted, but did not really free the space.

Hope this helped a bit,

Regards

Markus

Hello,

I have made several test in this days, and everything is pointing to a reorganization of tables. Definitlly I'll take your suggestions, and I'll make also an analyze of all tables as well.

Thanks Markus and Dave, I'll let you know how it ends.

Have you consider running update statistics?

Have you consider running update statistics?
This is most likely the problem. Else start Oracle tracing. Are you using dictionary managed tablespaces or localy managed?

Kind regards,
Dick