Re: Help with query performance

Kopek,

Thanks for your help.

The hardware is a Solaris server with 2 CPU's and 2 GB RAM.

This query returns 500,000 but I limited the return with a rownum weher clause on the outside.

What is a FTS? How can I change the sort_area_size ?

Yes, I analyzed the indexes and run the statistics.

Stefanos

matezuka_at_yahoo.com (kopek) wrote in message news:<f7cb1d69.0208052129.63558876_at_posting.google.com>...
> 3 minutes? I would be happy with it if I get that kind of number on my
> home PC. What's your hardware? There is only one table in this query,
> should be easy to tune.
> 1. Accessing buckets table. Is the table analyzed? How many rows are
> returned? If a large percentage of the rows in the table are returned,
> a FTS may be more appropriate than index scan.
> 2. The sorting. Check your sort_area_size and temp tablespace
> configuration. Search this news group on this FAQ.
>
> - K.M.
>
> tses_at_hotmail.com (Stefanos) wrote in message news:<5883c7c4.0208051140.462e2ede_at_posting.google.com>...
> > Hi,
> >
> > I need some help improving performance in the following query:
> >
> > Select Sum(HITS), ACCOUNTNO
> > from buckets
> > where element = 'CP'
> > and DATED >= '1-Jan-2000'
> > and DATED <= '30-Jun-2002'
> > group by ACCOUNTNO
> > order by 1 Desc
> >
> > The BUCKETS table has indexes on DATED, ELEMENT and ACCOUNTO and
> > has about 5,000,000 records and growing, and the total records
> > where element = 'CP' are about 3,500,000 records.
> >
> > When execute this query it takes about 3 minutes to return data.
> >
> > These are the optimizer results:
> >
> > SELECT STATEMENT Optimizer=CHOOSE (Cost=3211 Card=206551 Bytes=8675142)
> > SORT (ORDER BY) (Cost=3211 Card=206551 Bytes=8675142)
> > SORT (GROUP BY) (Cost=3211 Card=206551 Bytes=8675142)
> > TABLE ACCESS (FULL) OF BUCKETS (Cost=1197 Card=279328 Bytes=11731776)
> >
> >
> > Is this normal, to take that long?
> > How can I improve it?
> > How can I avoid a table full scan?
> >
> >
> > Any help will be appreciated.
> >
> > Stefanos
Received on Tue Aug 06 2002 - 18:12:18 CEST