Re: Slowing other users/sessions - resource mgt

> > this is the scenario I'm testing:
> >
> > One application writes, via a pool of jdbc connections, 40 records per
> > second (one write=one commit), limited by the disk speed (I know because
> > I'm
> > asking to write the double, 80).
> >
> > When I do a count on the table (select count(*) from myTable), the query
> > runs for more than 15 minutes.
> >
> > However, when the application is stopped, this count can be done in less
> > than 6 seconds.
> >
> > I'd like to prioratize the count, so response time is better. So far,
I've
> > tried:
> >
> > - Locking the table before the count, counting, and releasing with a
> > commit.
> > It works ok, but this is not acceptable if the count starts taking
longer,
> > as the application is completely frozen in the meantime.
> > - Using the resource manager: the problem is that in this case, CPU is
not
> > limiting (less than 5% used), so the manager is not used. I've tried
> > giving
> > the pool users 1% CPU, and the user performing the count 99%: no
> > improvement.
> > - I've also looked at the use of profiles, but the type of limitations
> > they
> > provide (logical_reads_per_session, logical_reads_per_call, etc) doesn't
> > seem adapted to the connection pool/small transaction case.
> >
> > Do you have any other ideas?
> >
> > Thanks in advance.
> >
> >
>
> Why do you need the count ? Does the select count use an index scan ? How
> big is the table ?
>

Hi,

the count is an example of a "statistical" query. I'm thinking about gathering other types of data.
Yes, the select is using an index scan:

Execution Plan

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1622 Card=1)     1 0 SORT (AGGREGATE)

    2    1     INDEX (FAST FULL SCAN) OF 'PK_ID' (UNIQUE) (Cost=1622 Ca
           rd=2183991)

and the table is around 2 million records of less than 100 bytes. BTW, using Oracle 9.2.
When I looked into materialized views some time ago, I tried to do an automatic refresh on commit, but it slowed a lot the insertions. And if I refreshed on demand, I think it's like doing the request as I'm doing it now.

Thanks! Received on Thu Mar 17 2005 - 02:45:55 CST