Oracle slow after adding datafile

Jake S

unread,

Oct 31, 2002, 7:53:48 AM10/31/02

to

Hi,
There is a production database server (SunOS) with Oracle 8.1.7. installed.
There was only a 500M datafile that was filled during testing phase. I added
another datafile, this time 5000M. Right after that, I was told that it's
not enough and I added a third one, this time 30 000M.

All data was deleted and another testing was started. This time they tell me
that Oracle is exceptionally slow. They noticed it right away but all I did
was that I added two datafiles with alter tablespace. Any ideas why this
might have happened or how to fix it?

- Jake -

Pete Sharman

unread,

Oct 31, 2002, 1:06:45 PM10/31/02

to

In article <wJ9w9.352$mA4....@read2.inet.fi>, "Jake says...

When you say "All data was deleted", my immediate response is how? If they've
done a delete from table, rather than a drop and recreate or a truncate, the
highwater mark is still where it was from the previous test. End result could
be worse performance, depending on number of rows in the earlier test.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements;

François Bourdages

unread,

Oct 31, 2002, 3:11:35 PM10/31/02

to

"Jake S" <nos...@nospam.net> wrote in message news:<wJ9w9.352$mA4....@read2.inet.fi>...

Does the datafiles created on the same disk speed that the other one ?

Daniel Morgan

unread,

Oct 31, 2002, 4:46:05 PM10/31/02

to

François Bourdages wrote:

I also notice that we appear to be talking about one 30GB file. Not knowing which version of
SunOS I am left wondering how well it handles files larger than 2GB?

Daniel Morgan

Jake S

unread,

Nov 1, 2002, 4:37:51 AM11/1/02

to

"François Bourdages" <francois....@mobilair.qc.ca> wrote in message
news:aadece84.02103...@posting.google.com...

Yes. It would be better to create datafile to another disk, but the
performance was awful. They told about 5-20 seconds delays when a one
message is inserted! One message includes a few queries and two inserts.
Another insert is adds a blob also.

- Jake -

Jake S

unread,

Nov 1, 2002, 4:41:23 AM11/1/02

to

"Pete Sharman" <peter....@oracle.com> wrote in message
news:aprrf...@drn.newsguy.com...

That was something I didn't know. I think they might have used delete
instead of truncate. Production staff reported about 5-20 second delays when
one message was inserted. One message includes a few queries and two
inserts.
Another insert is adds a blob also. Could it be that the performance drops
so much?

- Jake -

Norman Dunbar

unread,

Nov 1, 2002, 5:23:54 AM11/1/02

to

Jake,

did you analyse the table after the delete - the stats will probably be
sitting at the previous settings.

If you did delete rather than truncate, then try :

alter table xxxx deallocate unused keep nn;

and see if that makes a difference. But, before you do, try a quick

select count(*) from xxxx

and see how long it takes to complete and what result you get back. If
it takes 5 mins and says something like 10 rows, then you are hitting
the old 'scan up to the HWM' problem where most of the blocks are empty
- but still get scanned. The deallocate command above should fix that.

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------

Jake S

unread,

Nov 1, 2002, 7:06:42 AM11/1/02

to

Solved, thanks for all! Because testing phase was still in progress I just
dropped tablespace and installed everything with my scripts. This time I
created datafile that hopefully is large enough (32000M). Performance was OK
as it used to be.

- Jake -