RE: Upgrade 8.1.6 - 8.1.7 Patch 4
The last time I went through the documentation from Oracle on doing an upgrade. Planning for one of my 8.1.6.3 to be upgraded to 8.1.7 the installation manual and Metalink's articles on upgrading specifically called for the migration to be done (for all instances that were to be migrated) first, then to apply the patches to bring it up to the latest revision 8.1.7.4 at least on Sun Solaris. -Original Message- [EMAIL PROTECTED] Sent: Monday, January 27, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Patch first then upgrade. Dick Goulet [EMAIL PROTECTED] wrote on 1/25/03 8:38 pm: Im doing an upgrade from 8.1.6. to 8.1.7.4. Is there any benfit of doing the upgrade from 8.1.6 to 8.1.7, using ODMA then patching to 8.1.7.4 or installing 8.1.7, patch it to 8.1.7.4, then ODMA the database from 8.1.6. any other comments on this upgrade path are welcomed thanks Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Eastham INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
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 [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Eastham INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access
Re: HIGH CPU WITH MULTIPLE CONCURRENT USERS (long)
Vivek's feedback: (on your questions) Richard, I agree that over time this incident has been tested with various scenarios that it is getting confusing. However, the objective that we started with is still the same. Query: I have a query that does a select from 1 table (uses first_rows and index hint). This index is the one that gives us the best possible time with least possible consistent gets. The IN clause contain 50 individual literals. The query for 1 user to execute takes 1.67 seconds. This includes the time it also takes to display the results on the client. In our case the sql plus window on the database server. I had generated the trace file and did a TKPROF on the trace file. I am attaching the results of the trace file for your perusal. I had tried to _spin_count as default and various values from 4 to 4. The most optimal response time was obtained at _spin_count of 1. This is the value currently set. This was also recommended by Oracle as the CPU seems to be doing something (I believe due to Oracle) and is clearly visible as the user load is increased. To provide more clarity, I am attaching a word document that lists the trace status of parse, execute and fetch for 1 and 20 simultaneous users. Please note that while for 1 user the total elapsed time is very close the fetch time, for 20 concurrent users, the disparity is high. This disparity increases more than linearly as the stress is increased. I hope this helps. You are correct in your observation that Oracle does not show a wait in the v$session_wait and the CPU idle time is 0%, usage 98% user, 2% kernel. This can be observed clearly for as small as 100 concurrent users. There is no data functions or conversion on any of the columns both in the select and in the where clause. I want to be careful here. As I keep reducing the number of literals in the IN clause, the query works faster. However, the degradation factor (response time for 20 simultaneous queries to response time of 1 query) is the same hovering around 1 to 3.6. This degradation factor becomes very large as the stress in increased. Our first scenario was an IN clause with 800 literals. Then we had reduced it to 200. Then to 100. Now we are at 50. However, since our application response is for 800, now we have that many simultaneous queries accessing the database. This contributes to increased load and the overall degradation factor is still the high level. I will try the truss and send you the observation soon. Thanks in advance. Vivek Vijayaraghavan 1 USER: call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.03 0.02 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.73 1.74 0 334 0 31--- -- -- -- -- -- --total 6 1.76 1.76 0 334 0 31 20 Simultaneous Users:-- USER #1:---call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.91 6.48 0 334 0 31--- -- -- -- -- -- --total 6 1.91 6.48 0 334 0 31 USER #2:---call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.01 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.80 9.02 0 334 0 31--- -- -- -- -- -- --total 6 1.81 9.02 0 334 0 31 USER #3:call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.01 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.86 9.81 0 334 0 31--- -- -- -- -- -- --total 6 1.87 9.82 0 334 0 31 USER #4:---call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.88 7.71 0 334 0 31--- -- -- -- -- -- --total 6 1.88 7.71 0 334 0 31 USER #5:---call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 4 1.84 7.53 0 334 0 31--- -- -- -- -- -- --total 6 1.84 7.53 0 334 0 31 - Original Message - From: Aponte, Tony To: Multiple recipients of list ORACLE-L Sent: Wednesday, April 17, 2002 11:48 PM Subject: RE: HIGH CPU WITH MULTIPLE CONCURRENT USERS (long)
HIGH CPU WITH MULTIPLE CONCURRENT USERS (long)
s (table and index) into memory (buffer pool keep).The last suggestion was to set events 10046 for 1 user and 20 users. The trace files was supposed to provide clues of what wait events are there in addition to the ones for the 1 user. Not heard any feedback on the same.I have been able simulate the same situation in a Windows environment. I had uploaded the structures of the tables involved. Have you been able to simulate the same scenario in your lap. Richard Eastham
