3 Replies Latest reply: Sep 25, 2013 4:58 AM by Shiva Shivanoori RSS

    huge session inactive

    Harsh_v

      hi experts,

       

      I am facing issue, when i check my server it's has lot of session is inactive and these session running maximum this query "SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'".   what is that and why its running take huge session and how to resolve it ???


       

       

      [oracle@sun4 ~]$ ps -ef|grep ora |wc -l

      118

       

       

      SQL> SELECT   /*+ ORDERED */

        2           sql_text

        3      FROM v$sqltext a

        4     WHERE (a.hash_value, a.address) IN (

        5              SELECT DECODE (sql_hash_value,

        6                             0, prev_hash_value,

        7                             sql_hash_value

        8                            ),

        9                     DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

      10                FROM v$session b

      11               WHERE b.paddr = (SELECT addr

      12                                  FROM v$process c

      13                                 WHERE c.spid = '7146'))

      14  ORDER BY piece ASC;

       

      SQL_TEXT

      ----------------------------------------------------------------

      SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_

      DATE_FORMAT';



      STATUS   SPID   LAST_LOAD_TIME         TILL_DD:HH      CURRENT_RUNNING_QUERY

      -------- ------ ---------------------- --------------- --------------------------------------------------------------------------------------------------------------

      INACTIVE 16613                         0:0             oracleorcl@sun4

      INACTIVE 2349                          0:0             oracleorcl@sun4

      INACTIVE 845                           0:0             oracleorcl@sun4

                        INACTIVE 7146                          0:0             oracleorcl@sun4

      INACTIVE 27962                         0:0             oracleorcl@sun4

      INACTIVE 8516                          0:0             oracleorcl@sun4

      INACTIVE 18327                         0:0             oracleorcl@sun4

      INACTIVE 32268                         0:1             oracleorcl@sun4

      INACTIVE 4222                          0:0             oracleorcl@sun4

      INACTIVE 15629                         0:0             oracleorcl@sun4

      INACTIVE 4615                          0:23            oracleorcl@sun4

       

       

      Reg,

      Harsh

        • 1. Re: huge session inactive
          Nikolay Savvinov

          Hi,

           

          1) when posting questions on forums, avoid words and expressions like "huge", "a lot", "very, very slow". Be specific. How many inactive sessions you are seeing? How many inactive sessions you would consider normal and why?

          2) database sessions are running this SQL because someone (or something) launched that SQL. V$SESSION view should provide some basic information about sessions running this SQL (like machine, terminal, program, module, database username, OS username etc.). If it's not enough, look in audit trail (if the corresponding audit is enabled). Don't ask us, complete strangers to you and your database, about queries it's running -- we can't explain something we don't see, hear and don't have access to.

           

          Best regards,

            Nikolay

          • 2. Re: huge session inactive
            saurabh

            check in v$session for machine,program column to check where this all session are coming from.

            • 3. Re: huge session inactive
              Shiva Shivanoori

              Saurabh,

               

              --> From when are you seeing such sessions? and What are changes on the environment after this observation?

              --> Any changes in database parameters?

              --> Any other services running on this server? If yes, what are they?

              --> Also pull down couple of AWR reports, a 30 min snap shot interval would be fine... and upload the AWR reports here??

               

              regards,

              Shiva.