Re: Accessing the data is kind-of slow here

Sybrand Bakker wrote:
>
> "Martin Pein" <martin.pein_at_fernuni-hagen.de> wrote in message
> news:aalhi6$4ud$1_at_beech.fernuni-hagen.de...
> > Hi there,
> >
> > I've got a generated table here (in an Oracla 8 db) with about 400 columns
> > and about 4000 sets. About 95% of the fields are empty. One of the columns
> > is a unique and indexed and not-null'd ID.
> >
> > Accessing the data is kind-of slow: a "select count(*)" takes about 5
> > seconds.
> > It also takes about 5 seconds for a "select id from .. where id=17" to
> > return with the result (17 :-). The same is basically true for the other
> > fields in the row.
> >
> > All the other tables with fewer columns are much quicker.
> > From you experience, is that likely to be an architectural or a technical
> > problem?
> >
> > Thanks,
> >
> > Martin
> >
> >
> >
> >
> >
> >
>
> Might be both.
> If you design a table to have records bigger than db_block_size, you will
> get all your records chained in multiple blocks. This will result in 2 I/Os
> for one record.
> Also, without any further proof your select count(*) is executing a index
> fast full scan, your full table scan will always continue to the highwater
> mark of the table, and deleting records doesn't reset the highwater mark.
> Also, as you don't provide any details on the rest of your configuration:
> your system might be slow, with insufficient memory, an insufficient buffer
> cache (what is your hit ratio?) and all your database files on one single
> disk.
>
> Too few clues, too many things that could have been causing it.
> Try running explain plan first on the affected statement.
>
> Hth
>

Another possibility is that this was the first time the table had been analyzed. If the CBO is being used, it will estimate statistics from the table's physical parameters, which can result in some really strange (and slow) plans. 7.1 & 7.2 were horrible with this. 7.3 will still do it. Don't know about 8.0. 8i has a different set of problems.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!