Re: Slowing other users/sessions - resource mgt

Joe Smith wrote:
> 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.
>
> Thanks!
>

Hi Joe,

I second Richards suggestion. Try a 10046 trace level 8 (to capture the waits) when it's slow and when it's fast.

BTW, I tried to simulate your environement (5 jobs inserting into the table commiting each line in parallel) using a two column table and doing a count(*) in a separate session, but the difference when the 5 jobs where running and when not where neglectible - all around 1-1.5 secs. And that was on my small an' ole test hardware with lousy I/O. Test run on 10g (linux).

When you say your application writes 40 records per second is that insert or update activity?

HTH,
Holger Received on Thu Mar 17 2005 - 10:00:23 CST