Oracle statistics

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 13 Sep 2002 16:39:19 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702A580E0@lnewton.leeds.lfs.co.uk>

Hi Richard,

That's quite an incredible performance benefit. I shall implement that on all my databases here in Leeds ASAP.
I tried your suggestions out on a test database - as ever - and I have discovered that exporting and importing mow also take far less time, index rebuilds etc - they just fly !!
The only problem I have found, and it isn't a major one, is that all my queries seem to have stopped using the indexes - they all use a full table scan - but because of the increased performance, this takes much less time that they used to do before - so I don't really care.

I am off on a 9i upgrade course next week, so I've left a job to run overnight to carry out the above modifications to all my user databases, so everything will be fine when I get back.

Cheers,
Norman.

:o)

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
-------------------------------------

-----Original Message-----
From: Richard Foote [mailto:richard.foote_at_bigpond.com] Posted At: Friday, September 13, 2002 3:13 PM Posted To: server
Conversation: Oracle statistics
Subject: Re: Oracle statistics

Hi Norman,

I have *proven* that having data in one extent is preferable and improves
performance.

I had a table with 10,000,000 rows in 2,500 extents. It took ages to perform
a full table scan, obviously because of the number of extents, right.

So guess what I did.

I deleted 9,999,990 rows, exported the table, re-imported the table in only
*1* extent.

Now the full table scan runs heaps and heaps quicker, again obviously because now the table is only in the *1* extent.

*PROOF* !!
Can't help thinking I've overlooked something though ...

Richard ;) Received on Fri Sep 13 2002 - 10:39:19 CDT