13 Replies Latest reply on Dec 21, 2018 4:12 PM by Mark D Powell

    Too many wait events

    Vvs

      Hi All,

       

      i am using Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production  . i found too many wait event in database from number of seconds. so it affect on os memory ?  because i am facing memory issue as well  or can i kill those all session ?

       

      select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

       

      events                                                  state                count(*)

      rdbms ipc message                           WAITING          15

      SQL*Net message from client          WAITING          8

      EMON slave idle wait                        WAITING          5

      .........so many.....

        • 1. Re: Too many wait events
          JohnWatson2

          What if you adjust the query to suppress the events that don't matter:

          select event, state, count(*) from v$session_wait WHERE WAIT_CLASS <> 'Idle' group by event, state order by 3 desc;

          1 person found this helpful
          • 2. Re: Too many wait events
            Vvs

            Thanks for the quick reply

            on your query i got the below output

             

            select event, state, count(*) from v$session_wait WHERE WAIT_CLASS <> 'Idle' group by event, state order by 3 desc;

            event                                        state                                   count(*)

            PGA memory operation    WAITED SHORT TIME                   4

            SQL*Net message to client    WAITED SHORT TIME               1

            • 3. Re: Too many wait events
              Stefan Abraham

              Hello,

               

              It is the same query that is waiting on "PGA memory operation"?

              Please share the output of below,

               

              select sid, state, status, event, last_call_et, sql_id, logon_time, program, username, blocking_session, prev_sql_id from gv$session where username is not null and status!='INACTIVE' order by username, sql_id;

               

              Thanks

              Stefan

              • 4. Re: Too many wait events
                AndrewSayer

                Vvs wrote:

                 

                Hi All,

                 

                i am using Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production . i found too many wait event in database from number of seconds. so it affect on os memory ? because i am facing memory issue as well or can i kill those all session ?

                 

                select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

                 

                events state count(*)

                rdbms ipc message WAITING 15

                SQL*Net message from client WAITING 8

                EMON slave idle wait WAITING 5

                .........so many.....

                You seem confused.

                 

                A session is either waiting or working. Waiting to be told what to do is a wait event.

                You would normally expect to see a wait event for most of your sessions.

                 

                If you have a memory issue, then you should be looking at where the memory is going rather than what sessions are not doing anything right now. You can achieve this by looking at the pga_ columns in v$process and joining that to v$session on v$process.addr = v$session.paddr. Ultimately you will need to look at what code is being executed by the session and fix it so that it doesn’t require so much memory (it’s likely to be poorly crafted pl/sql that shoves a lot of data into collections).

                 

                Please explain what exactly you observed and how you observed it which made you come to the conclusion that you have memory issues. I am very suspicious that this is just a guess.

                 

                If you have an adequately sized SGA but processes are obtaining more PGA than you have capacity for then one thing you could do would be to tell them to stop it. In 12c you have the pga_aggregate_limit parameter  https://docs.oracle.com/database/121/REFRN/GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3.htm#REFRN10328 to achieve this.

                • 5. Re: Too many wait events
                  Vvs

                  Here is the output of your query :-

                  select sid, state, status, event, last_call_et, sql_id, logon_time, program, username, blocking_session, prev_sql_id from gv$session where username is not null and status!='INACTIVE' order by username, sql_id;

                   

                  those session are which shows killed due to Profile set

                              

                              

                  sidstatestatuseventlast_call_etsql_idlogon_timeprogramusernameblocking_sessionprev_sql_id
                  428WAITED SHORT TIMEACTIVESQL*Net message to client0fzf2pshjn1vm920/12/18SQL DeveloperADMINgypnfv5nzurb0
                  188WAITED SHORT TIMEKILLEDPGA memory operation61720/12/18SQL DeveloperPS3gypnfv5nzurb0
                  238WAITINGACTIVEOFS idle438633130/10/18oracle@sa1 (OFSD)SYS
                  369WAITED SHORT TIMEKILLEDPGA memory operation616120/12/18SQL DeveloperSA10zk57zgph6sdf
                  425WAITED SHORT TIMEKILLEDPGA memory operation345120/12/18SQL DeveloperSA11p46har5p7x7v
                  • 6. Re: Too many wait events
                    Stefan Abraham

                    Please check if process are there for the killed sessions if you think they are causing issues for your operation as Andrew has advised above..

                     

                    SELECT spid, osuser, s.program FROM v$process p, v$session s WHERE p.addr=s.paddr;

                     

                    How to remove KILLED session

                    • 7. Re: Too many wait events
                      Vvs

                      yes that killed session are using some memory.

                      • 8. Re: Too many wait events
                        John Thorton

                        Vvs wrote:

                         

                        Hi All,

                         

                        i am using Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production . i found too many wait event in database from number of seconds. so it affect on os memory ? because i am facing memory issue as well

                        post SQL & results that show memory is an issue that needs to be solved.

                         

                        Do you realize & understand that memory inside a computer is designed to be actually used?

                        Do you realize & understand that free memory inside a computer benefits NOBODY!

                        On *NIX, free memory will always be under 5%of total RAM; regardless of the amount of memory in the system.

                        Any memory  not used by Oracle will be used by OS as file system cache.

                        1 person found this helpful
                        • 9. Re: Too many wait events
                          Mark D Powell

                          Vvs, to agree with Andrew in Oracle you are always waiting.   That is how Oracle is designed to show activity, by what a session is waiting on.  Oracle long ago made the V$SESSION_WAIT information part of V$SESSION so every session shows WAIT information.  The key is figuring out which WAITS matter.  Many of the waits are classified as IDLE Waits and normally can be ignored.  Others such as those related to SQL*Net may identify that a process is making too many round trips to the database or could just show that most the time the session is waiting for a command from the client which may well be a screen.  In other words the session spends most of its time waiting on user input to process.

                          - -

                          From what you posted I am more concerned on why 3 of 5 shown sessions show as KILLED.  Why are sessions being KILLED?

                          - -

                          HTH -- Mark D Powell --

                          • 10. Re: Too many wait events
                            Vvs

                            @Mark D Powell , those killed session are just because of Profile set "Idle time" on schema. as you said "The key is figuring out which WAITS matter"

                            Can i know which kind of wait events does cause problem?

                             

                             

                            Regards

                            vvs

                            • 11. Re: Too many wait events
                              JohnWatson2

                              Vvs wrote:

                               

                              @Mark D Powell , those killed session are just because of Profile set "Idle time" on schema. as you said "The key is figuring out which WAITS matter"

                              Can i know which kind of wait events does cause problem?

                               

                               

                              Regards

                              vvs

                              I've already told you how to select only the events that might be a problem.

                              • 12. Re: Too many wait events
                                Vvs

                                ok JohnWatson2 understood now & thanks for same

                                • 13. Re: Too many wait events
                                  Mark D Powell

                                  Vvs, the only wait events that matter are those that impact your system/query performance.  If you have the EM Diagnostic Pack license then you can see your top waits on the AWR report.  Consider the waits compared to your baseline and to what activity is taking place (Top N queries by CPU, Logical IO, etc....)

                                  - -

                                  If you do not have the Diagnostic Pack License then rather than worry about wait events look for your heavy hitter SQL.  Make sure it is tuned and your waits should be fine unless you hit a bug of some type or have a process with unusual processing requirements, which in turn is probably another tuning opportunity.

                                  - -

                                  HTH -- Mark D Powell --