Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slowing other users/sessions - resource mgt

Re: Slowing other users/sessions - resource mgt

From: Joe Smith <nospam_at_nospam.com>
Date: Thu, 17 Mar 2005 14:59:16 +0100
Message-ID: <42398d32$0$1231$8fcfb975@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(*) :)

Thanks! Received on Thu Mar 17 2005 - 07:59:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US