Re: Why is query time SLOW on NT?

One thing that can cause small full table scans to take a very long time is if the table has at one time held many many rows and has since had these deleted. This causes the high water mark to be set way above the last actual data block. Oracle always full table scans up to the high water mark hence it takes a lot of time to return very little data. To check this see how many extents your table has (DBA_EXTENTS or DBA_SEGMENTS will tell you). If you have a lot of extents then it suggests the high water mark is the problem. Alternatively, you can use DBMS_SPACE.FREE_SPACE. If you find this is the cause recereate your table or copy out the data, TRUNCATE the table and copy the data back.

In article <6rcj1m$6qb$1_at_sun500.nas.nasa.gov>,   "PERRY" <PMEADE_at_MAIL.ARC.NASA.GOV> wrote:
> Hi all,
>
> I am running Personal Oracle 8 on a dual 200 Pentium pro NT system. It has
> plenty of fast disk storage and 128mb RAM. Should be fast, right? Not with
> Oracle!
>
> I have a sample table with 1337 rows. When I use SQL*Plus to issue the
> command
>
> select count(rowid) from ca2p1;
>
> it takes 45 seconds to return the response
>
> COUNT(ROWID)
> ------------
> 1337
>
> This seems a bit excessive and leads me to believe there is something
> seriously amiss with the default database tuning parameters. Can anyone
> suggest a 'reasonable' level to assign some of the tuning parameters so that
> this pig actually flies?
>
> Ultimately, I will have a single fairly large database with multiple
> tables - I would hate to think how long a simple query on a table with
> 100,000 records would take!
>
> Thanks,
>
> Perry
> PMeade_at_mail.arc.nasa.gov
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 19 1998 - 02:04:22 CDT