Re: Slowing other users/sessions - resource mgt

"Joe Smith" <nospam_at_nospam.com> wrote in message news:423943c1$0$19321$8fcfb975_at_news.wanadoo.fr...
>
>> > 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.
>

Hi Joe,

Have you determined exactly *why* this application is causing such a massive blow-out in your poor little count(*) ? Solve the root cause and you'll fix your problem correctly. I could make a few guesses, but they'll just be that, guesses. I would perform an extended trace of your count(*) session, see where all the time is going and focus your attention accordingly.

Do a google search on Oracle extended SQL tracing for all the necessary details.

Cheers

Richard Received on Thu Mar 17 2005 - 07:30:49 CST