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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
