Re: Slowing other users/sessions - resource mgt

"Joe Smith" <nospam_at_nospam.com> wrote in message news:42398d32$0$1231$8fcfb975_at_news.wanadoo.fr...
>
>
>> >> > 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
> Hi,
>
> well, as I understand it, when I ask to do a count, oracle tries to count
> the records in that exact moment, and for that, he needs to use the
> rollback
> segment to undo the changes operated in the table afterwards; as there's a
> lot of I/O activity, this takes too long.
>
> Is this correct?
>
> I wouldn't mind having a faster, less precise count. Something like select
> /*+ DIRTY */ count(*) :)
>

You can do this if you have up-to-date statistics :

 SELECT table_name, num_rows FROM all_tables WHERE table_name = '...';

HTH
Matthias Received on Thu Mar 17 2005 - 11:11:42 CST