Re: Slowing other users/sessions - resource mgt

I`m using wait events to identify what is case of slowing down. You can use somethink like this:

SELECT program,username,sw.*
  FROM v$session_wait sw,

       v$session s
	WHERE sw.sid=s.sid;

Run it while simultaneously with an application and your select.

And then you can see from wait events what can be the root problem.

Jan

Joe Smith wrote:
> Hi everybody,
>
> 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.
Received on Fri Mar 18 2005 - 02:31:00 CST