My environment is Oracle 9i on Linux. My problem: closing
connections to this DB
takes up to 40 seconds. We have several apps that open a connection, grab some data, then close the connection. However the apps are often timing out because closing the connections take so long. It's not just the apps. Closing connections from Toad and other tools takes just as long. Any ideas where to begin looking?
Does the database have a Before Logoff trigger? If it does the problem could be there. Another thing to check, see if Auditing is enabled. If Auditing is enabled, Oracle logs all user disconnects by updating the aud$ table with the logout time. Your aud$ table could be large and if it is doing a table scan this would cause the delay. The best suggestion I have is to start a session trace on your session and then log off. On your database server, go to the UDUMP directory and get that trace file for your session. The trace file will show any and all code that your session executes in the database. Use tkprof to format the trace file and from there you should be able to see what is taking the time.
Dig Deeper on Oracle database performance problems and tuning
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four...
A user complains that every time he tries to access a table (select only), it takes more than two hours to get
the results. There are no DML ...
I have always used TKPROF to do SQL tuning in previous versions of
Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2?