Re: wierd performance problem

On Thu, Jul 10, 2008 at 12:32 PM, Dba DBA <oracledbaquestions_at_gmail.com> wrote:

> I am going to try to respond to several emails in one post.
>
>
> 4. we did do a few million deletes, but its a relatively small number of
> deletes relative to the size of the table

If the deletes were from blocks at the top of the table, that could cause your 2 second wait, as
your query is scanning the table until it gets data.

>
> 5. I did large insert selects across a db link that died several times and
> rolled back. I think that may be the problem. Lots of fragmentation. lost
> connection across the database link after pulling many GBs of data. so I
> made my pulls smaller (got less data with each call).

If so, the blocks allocated are retained, even if the data was not committed.

> 6*. I am currently running an alter table shrink space operation to see if
> this fixes the problem*

That won't help. Your queries are not reading that part of the datafile.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l