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?
>

Hi Joe,

Well yes and no. Yes Oracle will require the undo of changes made during the select however the fact that the query goes from 6 secs to 15 mins suggests there's something else going on. I mean, it really shouldn't take Oracle 14 mins and 54 secs to undo roughly 6 secs worth of work ...

You have a theory on why you *suspect* something is happening and you're endeavouring to find a solution based on your theory. However, what you need to do is determine *exactly* what is going on and then base your solution on these facts and not on guesses and assumptions. You could be right but then again ...

Trace your session, determine exactly what's going on and work on from there.

Cheers

Richard Received on Fri Mar 18 2005 - 05:20:29 CST