Oracle tcpsocket kgas,Bug 5490208 : TIME WAIT TCP SOCKET KGAS

Hdr: 5490208 10.2.0.1 RDBMS 10.2.0.1 KGA/KQA PRODID-5 PORTID-23 Abstract: TIME WAIT  TCP SOCKET KGAS *** 08/24/06 10:20 pm *** TAR: ---- SR :5701091.992 Problem: -------- Customer upgrade from 10.1.0.4 to 10.2.0.1. in the past weekend. They are experiencing very bad OLTP performance problem. Theyfound the following very high wait event. select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.sample_time between sysdate - 60/2880 and sysdate group by active_session_history.event order by 2; EVENT                                    TTL_WAIT_TIME ---------------------------------------- ------------- TCP Socket (KGAS)                          6621333013 Versions: --------- 10.2.0.1 -Oracle Solaris 64 bit RHEL 4 Diagnostic Analysis: -------------------- a. Run the database on dedicated mode b. run UTL_HTTP package Reproducibility: ---------------- Yes, both on customers site and in my test machine. Test Case: ---------- /upload/bug5490208 Workaround: ----------- Workaround of removing the event is possible, but the performance is same even after removing the event from the active session history table. Related Bug(s): --------------- Location Of Information (i.e. WRVMS): ------------------------------------- Topography: ----------- Stack Trace and Errors: ----------------------- Configuration Files: --------------------- 24-hour Contact Information For P1 Bugs: ---------------------------------------- Dial-In Information: -------------------- Additional Environment Information: ----------------------------------- Memory Leak Information: ------------------------ Network Tracing: ---------------- Havent asked customer to enable the client/server tracing. But I hope its happening in my test machine too. SO i guess this is not to be put on to the customer. If you need I can ask the customer to provide it. *** 08/24/06 10:24 pm *** I created a test environment on 10.2.0.1 with following : I tried executing a UTL_HTTP package which seems to be showing  the sockets KGAS  waits. So, I migrated to shared server and  noted the following : a. one dispatcher running with presentation=kgas output:  client connections refused  when connected server=shared (TNS-12520) b. One dispatcher running with presentation=kgas output: got connected using server=dedicated , but still the event waits were high. c. one dispatcher running with presentation=kgas   and other without presentation=kgas. output:  sucess.  and the event TCP SOCKET KGAS disappears from active session history. d. One dispatcher with no presentation=kgas output: sucess  & the event TCP SOCKET KGAS disappears from active session history. When dispatchers (irrespective of presentation=kgas  present or not) are used , the event TCP SOCKET KGAS disappears from active session history. If we are changing our servers to shared mode to improve the performance, then shouldnt the active session history still show the event and reduce the time wait on this event ? e.What is this Event TCP Socket KGAS ? f.Is this a new feaure in 10gr2 ? if not why this wasnt noticied in earlier releases ? g.What sort of tuning can be done to reduce these waits? Can this be achieved by SQLNET level tunning ? h. should we check for closure of connection in the PLSQL code ? doesnt the UTL pacage have an implicit close on connections ? i. Is there any difference in server being on shared / dedicated for such scenarios ? One more additional point : The same UTL package when run on 10.1.0.5 (dedicated) , i found no rows were returned for the active_session_history view !! Thanks ! Sathya *** 08/25/06 12:43 am *** (CHG: Sta->10 Asg->ESTOLLE2) *** 08/25/06 12:43 am *** *** 08/25/06 01:05 am *** database :sys/oracle *** 08/25/06 01:05 am *** (CHG: Sta->16) *** 09/04/06 07:00 am *** (CHG: Sta->10) *** 09/04/06 07:00 am *** *** 09/04/06 07:11 pm *** Einar, The instanace db10gr2 was configured with dispatchers presentation=kgas, thats why you were not able to reproduce the issue. I have now removed the dispatchers from this isntance, issue reproduces now. If you need to check the issue with dispatchers placed, then start the instance with kgas /u01/ora10gr2/dbs/kgasdispatcherpresent.ora Currently the spfile has the event too placed in it. Uploaded /upload/bug5490208\db10gr2_ora_14790.trc (event trace file) One query (may be not related to this bug) , when i set the event for pmon, the trace got generated at bdump_dest. For this KGAS event, the trace got generated at userdump_dest. Why is it so ? Thanks ! Sathya *** 09/04/06 07:11 pm *** (CHG: Sta->16) *** 09/07/06 06:04 am *** (CHG: Sta->11 Asg->NETREP Prod->991 Comp->NZ) *** 09/07/06 06:04 am *** *** 09/07/06 06:06 am *** *** 09/07/06 06:06 am *** *** 09/07/06 07:06 pm *** *** 09/11/06 05:49 am *** *** 09/11/06 05:49 am *** *** 09/13/06 06:04 pm *** *** 09/13/06 06:04 pm *** *** 09/20/06 11:46 am *** *** 10/02/06 08:10 pm *** *** 10/02/06 08:10 pm *** *** 10/04/06 03:04 pm *** *** 10/04/06 03:08 pm *** (CHG: Sta->32) *** 02/08/07 08:42 am *** (CHG: Sta->92) *** 04/19/13 07:20 am *** *** 08/13/14 03:08 am ***