|
Charles, Consider looking at what the sessions are doing? Display the SQL_ID and CHILD_NUMBER values from V$SESSION while event = <whatever> and STATE = 'WAITING'. Perhaps query recent history in V$ACTIVE_SESSION_HISTORY this way as well? If the SQL_ID is constantly different and changing rapidly, consider perhaps that you might have a SQL sharing problem, where someone isn't using bind-variables? Correct the application code or consider setting CURSOR_SHARING? If you find that all of these sessions have similar (or the same) SQL_IDs while waiting, please consider looking at the CHILD_NUMBER, to see if that is changing often? That might indicate that the client-side application code is "soft-parsing", or calling OPEN-EXECUTE-FETCH-CLOSE at each execution where it should be calling OPEN of the cursor once at the beginning of session, then calling EXECUTE-FETCH whenever needed, then calling CLOSE at the end of the session. Lots of applications fail to use bind-variables, causing excessive hard-parsing, evidenced by changing SQL_ID values for essentially the same SQL text. Lots of applications (generally "object-oriented" in nature) create-use-destroy their cursors for each execution, the methods mirroring the use of the "object", causing excessive soft-parsing, evidenced by same/similar SQL_ID values but changing CHILD_NUMBER. Look to what the application is doing, understand what the application is firing at the database. Library cache pin/lock contention, library cache latch contention, whatever -- most often it is a symptom, not a cause. Don't look for an Oracle bug, don't look to tune the database instance, instead look for cause within the application code. Hope this helps.... Tim Gorman consultant - Evergreen Database Technologies, Inc. P.O. Box 630791, Highlands Ranch CO 80163-0791 website = http://www.EvDBT.com/ email = Tim@xxxxxxxxx mobile = +1-303-885-4526 fax = +1-303-484-3608 Yahoo IM = tim_evdbt Charles Schultz wrote: Good day, list,-- //www.freelists.org/webpage/oracle-l |
