Re: HIGH CPU WITH MULTIPLE CONCURRENT USERS (long) Solution

Guys,

The query was rewritten with only one thing in mind - Logical IO. As suggested, I was able to recode the query and what a difference it was. The cpu utilization is now only 10% of the same query executing before. Thanks all for the timely help. Infact, when Farrukh suggested the index is when we did the same and was able to see appreciable difference. As the sql statements are in our control, it is easier to modify the same.

Thanks once again for the timely help. This is not a bug, just the inefficiency showing in higher proportions when stressed.

I am now modifying the most important queries with logical IO and Physical IO is mind.

Thanks once again.

Vivek

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, April 18, 2002 4:48 AM

>
> There appear to be a number of contradictory items
> in your posting; presumably due to the passage of
> time and the number of variations and experiments
> that have take place.
>
> You seem to indicate that a simple select on a single
> table using an IN list takes 2 seconds to complete,
> but the time escalates to 7 seconds when you run 10
> concurrent copies. Also that there is a suggestion that
> this 2 seconds is due to a wait between parsing and
> fetching.
>
> Two seconds is a very long time for a simple query.
> How long is the IN list, what is the execution plan,
> are you using bind variables, is there an nvl() function
> involved in columns referenced in the WHERE clause,
> and have you tried a 10053 trace ?
>
> How are you determining that there is a two-second
> wait between the parse and the fetch, and when you
> say WAIT, can I infer from your comments about CPU
> usage that you mean that there is some indication of
> 2 seconds of lost time but
> a) Oracle does not show a wait in v$session_wait
> b) the CPU does not go idle.
>
>
> There are a number of possible anomalies in the
> information that you have sent to Oracle, and your
> init.ora has a number of strange settings which may
> be affecting things (possibly because of bugs,
> possibly because of resource demands and forced
> code paths). However, based on your initial description,
> I think Oracle is chewing up CPU trying to optimize
> your query, and I would take steps to check whether
> this is actually the case (e.g. keep reducing the size
> of the IN list).
>
>
>
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
> -----Original Message-----
> Sent: Wednesday, April 17, 2002 1:58 AM
> To: Multiple recipients of list ORACLE-L
>
>
> A co-worker is having a fairly serious issue with performance tuning
> of a system. The system is in the stress testing phase prior to
> rolling out into production. I have not included all the information
> as so far they have exceeded three TARs and are working on the fourth
> one right now. Oracle has become fairly heavily involved and is
> sending in the Advanced services team is now involved. He has
> identified that the main issue is a wait after the parsing of the SQL
> and during the fetch portion of the execution. The short version is
> running the same SQL statement ( basically nothing more than a simple
> query against a single table) the machine starts bogging down with a
> simulated 20+ users sessions and the system starts to choke at 100+
> user sessions. We are talking a fairly decent midrange system. The
> query is a select with 5 columns extracted and a where clause that
> uses the in clause to select the same rows for each query. The
> question is has anyone seen this type of behavior before? If you have
> seen this before what was the root cause? Did you find a solution?
>
> Oracle acknowledges that the scenario is reproducible within their
> test environment, but the core team is stating that it is working as
> designed. Oracle is working with us, but why not check with other
> sources.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Richard Eastham
  INET: eastham_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).