…from the mail bag…
I've seen a situation several times lately that I need your interpretation of.
There was a query running and I was watching it in Toad. It showed the session as ACTIVE, the wait event as “SQL*Net message from client” and the consistent read I/Os were increasing at a rapid rate. Dan replies>>>The “SQL*Net message from client” is passing a large volume of information to at your processes request. My guess is that your process is doing row-at-a-time processing. If this is from PL/SQL, consider changing this area of the code to a collection (Associative Arrays are the fastest) and using bulk collect technology to populate the collection. Do this at the client side and you will get huge but much faster downloads of your information to PL/SQL. I cover this material in my Advanced PL/SQL course, another good name for this course would be ‘New Features for Developers’. Dan replies>>>If this is running via SQL*Plus and a SQL statement, try increasing the array size parameter. Dan replies>>>My thought, not seeing the code or having any other input, is that this process could benefit from a collection using bulk collect to present the rows to the client application. The wait event stayed on the same sequence number and had a total wait time of over 30 minutes so it didn't seem that the process was switching back and forth but staying on the same event and the same sequence number of that event.
What would you say is happening with this query?
Why would I see that wait event when the process seems to be generating many consistent gets? Dan replies>>>Consistent Gets are logical reads. Your SQL is processing data blocks that are already in the buffer cache. This is a good thing.
Dan replies>>>I have a SQL Tuning class designed for developers. I cover wait events and various issues. Watch for my series of articles where I visit various tuning issues I’ve encountered and solved. You can watch my videos at: http://informit.com/hotka or https://www.safaribooksonline.com/search/?query=Dan+Hotka . You can have me run the course for your site over the web or I can come there! It’s pretty much the same class but it’s always better to be on-site to help with labs and questions. Over the web, I do allow more people into the class.
Keep the questions coming in! I don’t mind at all and Toad World allows me to share the answer with the masses. I’ll never reference your name unless you tell me it’s OK to do so.
Oracle ACE Director