Re: Performance

A copy of this was sent to "Nicky" <nickyterwel_at_hotmail.com> (if that email address didn't require changing) On Fri, 3 Sep 1999 15:55:52 +0200, you wrote:

>Hello,
>
>We have an application on an Oracle 8 database which is very slow. I know
>the problem isn't my application, because we've tested it with the
>sql-worksheet and sql-plus which show the same performance. My query's
>(select * from tablename where name='something' and logtime between
>'27-jul-99' and '28-jul-99') returns about 6000 records. The tablestructure
>is simple:
> logtime date;
> name varchar2;
> value number;
>
>Every minute 50 records are added to the database, and she holds about 1.5
>years of records.(total about 25,000,000 records)
>I've tried an index on (logtime(1) ,name(2)), but the speed difference is
>minimal.
>The network also isn't the slowing factor, so I'm asking you: what is?

well, we don't know that...

you don't say how slow slow is or how wide the result set it.

ideas:

1. have you explain planned the query? maybe the easiest way to do that is with autotrace in sqlplus. see http://govt.us.oracle.com/~tkyte/article1/autotrace.html for quick directions on setting that up. Make sure its using the index. It might not if you are using CBO and you analyzed the table eons ago or analyzed it before the index. Also, a table that grows like that is a moving target for CBO -- it might work best to not analyze this table and HINT queries that go against it. Or use the Rule Based optimizer for it.
2. whats your array fetch size. In sqlplus try this:

set arraysize 100

and see what kind of results you get. play around with different arraysize settings -- then use the optimal one as the array fetch size you use in your application.

>
>Thanks,
>
>Micha Huybrechts
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 03 1999 - 09:18:43 CDT