Oracle slow after adding datafile

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 1 Nov 2002 10:23:54 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702DE89F9@lnewton.leeds.lfs.co.uk>

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.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Fri Nov 01 2002 - 04:23:54 CST