Loading ...
Sorry, an error occurred while loading the content.
 

Typo in Job Scheduler Message....

Expand Messages
  • hickend
    After executing a scheduler job through a right-click the Toad Message that comes up says: Jobs have been executed. Check Job lob for details.
    Message 1 of 23 , May 3, 2010
      After executing a scheduler job through a right-click the "Toad Message" that comes up says:

      Jobs have been executed. Check Job lob for details.
      <<OK>>

      Although lob might be appropriate, I think that log is the word desired.

      David H
      Utahtoad at gmail dot com
    • George.Meltser@aigfpc.com
      I quite often switch between Group by none: and Group by machine views. It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
      Message 2 of 23 , May 3, 2010
        I quite often switch between "Group by none: and "Group by machine" views.
        It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
        Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.
      • John Dorlon
        What database version are you connected to? If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO. I have never heard of any problems with it, but we
        Message 3 of 23 , May 3, 2010

          What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

           

          If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

           

          As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

           

           

           

          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
          Sent: Monday, May 03, 2010 11:33 AM
          To: toad@yahoogroups.com
          Subject: [toad] Problem with 10.5.0.41 Session Browser

           

           

          I quite often switch between "Group by none: and "Group by machine" views.
          It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
          Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

        • Bert Scalzo
          As always - have you collected system stats - because anytime a query goes from seconds to minutes when all we do is add one more system table/view - then this
          Message 4 of 23 , May 3, 2010

            As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

             

            See the toad wiki faq on this ….

             

             

            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
            Sent: May 03, 2010 11:46
            To: toad@yahoogroups.com
            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

             

             

            What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

             

            If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

             

            As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

             

             

             

            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
            Sent: Monday, May 03, 2010 11:33 AM
            To: toad@yahoogroups.com
            Subject: [toad] Problem with 10.5.0.41 Session Browser

             

             

            I quite often switch between "Group by none: and "Group by machine" views.
            It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
            Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

          • George.Meltser@aigfpc.com
            10.2.0.4 From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon Sent: Monday, May 03, 2010 12:46 PM To: toad@yahoogroups.com
            Message 5 of 23 , May 3, 2010

              10.2.0.4

               

              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
              Sent: Monday, May 03, 2010 12:46 PM
              To: toad@yahoogroups.com
              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

               




              What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

               

              If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

               

              As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

               

               

               

              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
              Sent: Monday, May 03, 2010 11:33 AM
              To: toad@yahoogroups.com
              Subject: [toad] Problem with 10.5.0.41 Session Browser

               

               

              I quite often switch between "Group by none: and "Group by machine" views.
              It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
              Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

               






            • George.Meltser@aigfpc.com
              Yes, I collect systems stats every week. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo Sent: Monday, May 03, 2010 12:48 PM
              Message 6 of 23 , May 3, 2010

                Yes, I collect systems stats every week.

                 

                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                Sent: Monday, May 03, 2010 12:48 PM
                To: toad@yahoogroups.com
                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                 




                As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                 

                See the toad wiki faq on this ….

                 

                 

                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                Sent: May 03, 2010 11:46
                To: toad@yahoogroups.com
                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                 

                 

                What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                 

                If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                 

                As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                 

                 

                 

                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                Sent: Monday, May 03, 2010 11:33 AM
                To: toad@yahoogroups.com
                Subject: [toad] Problem with 10.5.0.41 Session Browser

                 

                 

                I quite often switch between "Group by none: and "Group by machine" views.
                It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                 






              • John Dorlon
                Use SQL Spooling to find out what query is taking so long and let me know. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
                Message 7 of 23 , May 3, 2010

                  Use SQL Spooling to find out what query is taking so long and let me know.

                   

                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                  Sent: Monday, May 03, 2010 12:20 PM
                  To: toad@yahoogroups.com
                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                   

                   

                  Yes, I collect systems stats every week.

                   

                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                  Sent: Monday, May 03, 2010 12:48 PM
                  To: toad@yahoogroups.com
                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                   





                  As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                   

                  See the toad wiki faq on this ….

                   

                   

                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                  Sent: May 03, 2010 11:46
                  To: toad@yahoogroups.com
                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                   

                   

                  What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                   

                  If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                   

                  As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                   

                   

                   

                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                  Sent: Monday, May 03, 2010 11:33 AM
                  To: toad@yahoogroups.com
                  Subject: [toad] Problem with 10.5.0.41 Session Browser

                   

                   

                  I quite often switch between "Group by none: and "Group by machine" views.
                  It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                  Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                   





                   


                • Bert Scalzo
                  Ok - sned the spooled query then :) From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@aigfpc.com Sent: May 03, 2010 12:20
                  Message 8 of 23 , May 3, 2010

                    Ok – sned the spooled query then J

                     

                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                    Sent: May 03, 2010 12:20
                    To: toad@yahoogroups.com
                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                     

                     

                    Yes, I collect systems stats every week.

                     

                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                    Sent: Monday, May 03, 2010 12:48 PM
                    To: toad@yahoogroups.com
                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                     





                    As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                     

                    See the toad wiki faq on this ….

                     

                     

                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                    Sent: May 03, 2010 11:46
                    To: toad@yahoogroups.com
                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                     

                     

                    What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                     

                    If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                     

                    As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                     

                     

                     

                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                    Sent: Monday, May 03, 2010 11:33 AM
                    To: toad@yahoogroups.com
                    Subject: [toad] Problem with 10.5.0.41 Session Browser

                     

                     

                    I quite often switch between "Group by none: and "Group by machine" views.
                    It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                    Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                     





                     


                  • George.Meltser@aigfpc.com
                    I keep forgetting, where does TOAD store the debugged sql? From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo Sent: Monday, May
                    Message 9 of 23 , May 3, 2010

                      I keep forgetting, where does TOAD store the debugged sql?

                       

                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                      Sent: Monday, May 03, 2010 1:22 PM
                      To: toad@yahoogroups.com
                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                       




                      Ok – sned the spooled query then J

                       

                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                      Sent: May 03, 2010 12:20
                      To: toad@yahoogroups.com
                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                       

                       

                      Yes, I collect systems stats every week.

                       

                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                      Sent: Monday, May 03, 2010 12:48 PM
                      To: toad@yahoogroups.com
                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                       




                      As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                       

                      See the toad wiki faq on this ….

                       

                       

                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                      Sent: May 03, 2010 11:46
                      To: toad@yahoogroups.com
                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                       

                       

                      What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                       

                      If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                       

                      As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                       

                       

                       

                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                      Sent: Monday, May 03, 2010 11:33 AM
                      To: toad@yahoogroups.com
                      Subject: [toad] Problem with 10.5.0.41 Session Browser

                       

                       

                      I quite often switch between "Group by none: and "Group by machine" views.
                      It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                      Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                       




                       


                       






                    • Bert Scalzo
                      Spool to screen is easiest From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@aigfpc.com Sent: May 03, 2010 12:27 To:
                      Message 10 of 23 , May 3, 2010

                        Spool to screen is easiest

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                        Sent: May 03, 2010 12:27
                        To: toad@yahoogroups.com
                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                         

                         

                        I keep forgetting, where does TOAD store the debugged sql?

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                        Sent: Monday, May 03, 2010 1:22 PM
                        To: toad@yahoogroups.com
                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                         





                        Ok – sned the spooled query then J

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                        Sent: May 03, 2010 12:20
                        To: toad@yahoogroups.com
                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                         

                         

                        Yes, I collect systems stats every week.

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                        Sent: Monday, May 03, 2010 12:48 PM
                        To: toad@yahoogroups.com
                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                         





                        As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                         

                        See the toad wiki faq on this ….

                         

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                        Sent: May 03, 2010 11:46
                        To: toad@yahoogroups.com
                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                         

                         

                        What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                         

                        If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                         

                        As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                         

                         

                         

                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                        Sent: Monday, May 03, 2010 11:33 AM
                        To: toad@yahoogroups.com
                        Subject: [toad] Problem with 10.5.0.41 Session Browser

                         

                         

                        I quite often switch between "Group by none: and "Group by machine" views.
                        It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                        Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                         





                         


                         





                         


                      • John Dorlon
                        It s not stored automatically, you have to enable it. Database - Spool SQL - Spool to screen. Then you ll see it in the output panel. From:
                        Message 11 of 23 , May 3, 2010

                          It’s not stored automatically, you have to enable it.

                           

                          Database -> Spool SQL -> Spool to screen.

                           

                          Then you’ll see it in the output panel.

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: Monday, May 03, 2010 12:27 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          I keep forgetting, where does TOAD store the debugged sql?

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                          Sent: Monday, May 03, 2010 1:22 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           





                          Ok – sned the spooled query then J

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: May 03, 2010 12:20
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          Yes, I collect systems stats every week.

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                          Sent: Monday, May 03, 2010 12:48 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           





                          As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                           

                          See the toad wiki faq on this ….

                           

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                          Sent: May 03, 2010 11:46
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                           

                          If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                           

                          As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                           

                           

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: Monday, May 03, 2010 11:33 AM
                          To: toad@yahoogroups.com
                          Subject: [toad] Problem with 10.5.0.41 Session Browser

                           

                           

                          I quite often switch between "Group by none: and "Group by machine" views.
                          It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                          Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                           





                           


                           





                           


                        • George.Meltser@aigfpc.com
                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon Sent: Monday, May 03, 2010 1:28 PM To: toad@yahoogroups.com Subject: [toad]
                          Message 12 of 23 , May 3, 2010

                           

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                          Sent: Monday, May 03, 2010 1:28 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           




                          It’s not stored automatically, you have to enable it.

                           

                          Database -> Spool SQL -> Spool to screen.

                           

                          Then you’ll see it in the output panel.

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: Monday, May 03, 2010 12:27 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          I keep forgetting, where does TOAD store the debugged sql?

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                          Sent: Monday, May 03, 2010 1:22 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           




                          Ok – sned the spooled query then J

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: May 03, 2010 12:20
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          Yes, I collect systems stats every week.

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                          Sent: Monday, May 03, 2010 12:48 PM
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           




                          As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                           

                          See the toad wiki faq on this ….

                           

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                          Sent: May 03, 2010 11:46
                          To: toad@yahoogroups.com
                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                           

                           

                          What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                           

                          If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                           

                          As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                           

                           

                           

                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                          Sent: Monday, May 03, 2010 11:33 AM
                          To: toad@yahoogroups.com
                          Subject: [toad] Problem with 10.5.0.41 Session Browser

                           

                           

                          I quite often switch between "Group by none: and "Group by machine" views.
                          It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                          Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                           




                           


                           




                           


                           






                        • Gregory Liss
                          DEBUG.SQL in your User Files dir. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@aigfpc.com Sent: Monday, May 03, 2010
                          Message 13 of 23 , May 3, 2010

                            DEBUG.SQL in your User Files dir.

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                            Sent: Monday, May 03, 2010 1:27 PM
                            To: toad@yahoogroups.com
                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                             




                            I keep forgetting, where does TOAD store the debugged sql?

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                            Sent: Monday, May 03, 2010 1:22 PM
                            To: toad@yahoogroups.com
                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                             



                            Ok – sned the spooled query then J

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                            Sent: May 03, 2010 12:20
                            To: toad@yahoogroups.com
                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                             

                             

                            Yes, I collect systems stats every week.

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                            Sent: Monday, May 03, 2010 12:48 PM
                            To: toad@yahoogroups.com
                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                             



                            As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                             

                            See the toad wiki faq on this ….

                             

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                            Sent: May 03, 2010 11:46
                            To: toad@yahoogroups.com
                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                             

                             

                            What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                             

                            If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                             

                            As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                             

                             

                             

                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                            Sent: Monday, May 03, 2010 11:33 AM
                            To: toad@yahoogroups.com
                            Subject: [toad] Problem with 10.5.0.41 Session Browser

                             

                             

                            I quite often switch between "Group by none: and "Group by machine" views.
                            It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                            Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                             



                             


                             



                             

                             





                          • John Dorlon
                            Weird. I see the same statement executed over and over for about 75 seconds. I have no idea why it would be doing that, but something is definitely going
                            Message 14 of 23 , May 3, 2010

                              Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                               

                              If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                              Sent: Monday, May 03, 2010 12:31 PM
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                               

                               

                              [Attachment(s) from George.Meltser@... included below]

                               

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                              Sent: Monday, May 03, 2010 1:28 PM
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               





                              It’s not stored automatically, you have to enable it.

                               

                              Database -> Spool SQL -> Spool to screen.

                               

                              Then you’ll see it in the output panel.

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                              Sent: Monday, May 03, 2010 12:27 PM
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               

                               

                              I keep forgetting, where does TOAD store the debugged sql?

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                              Sent: Monday, May 03, 2010 1:22 PM
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               





                              Ok – sned the spooled query then J

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                              Sent: May 03, 2010 12:20
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               

                               

                              Yes, I collect systems stats every week.

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                              Sent: Monday, May 03, 2010 12:48 PM
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               





                              As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                               

                              See the toad wiki faq on this ….

                               

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                              Sent: May 03, 2010 11:46
                              To: toad@yahoogroups.com
                              Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                               

                               

                              What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                               

                              If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                               

                              As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                               

                               

                               

                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                              Sent: Monday, May 03, 2010 11:33 AM
                              To: toad@yahoogroups.com
                              Subject: [toad] Problem with 10.5.0.41 Session Browser

                               

                               

                              I quite often switch between "Group by none: and "Group by machine" views.
                              It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                              Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                               





                               


                               





                               


                               





                               


                            • George.Meltser@aigfpc.com
                              The problem is very obvious though. The new version (10.5.0.41) executes this query: SELECT s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait,
                              Message 15 of 23 , May 3, 2010

                                The problem is very obvious though.

                                 

                                The new version (10.5.0.41) executes this query:

                                 

                                SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                FROM V$SESSION S

                                WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                 AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                 

                                which does full scan of sys.x$ksuse

                                 

                                Plan

                                SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                 

                                3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                 

                                 

                                1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                 

                                 

                                2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                 

                                The old version queries v$session using sid:

                                 

                                select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                       , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                from v$session

                                where sid = :sid;

                                 

                                which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                 

                                Plan

                                SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                 

                                3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                 

                                 

                                1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                 

                                 

                                2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                 

                                 

                                 

                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                Sent: Monday, May 03, 2010 2:20 PM
                                To: toad@yahoogroups.com
                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                 




                                Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                 

                                If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                 

                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                Sent: Monday, May 03, 2010 12:31 PM
                                To: toad@yahoogroups.com
                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Atta

                                (Message over 64 KB, truncated)

                              • John Dorlon
                                That query is not in the file you sent me, but the first query did not replace the 2nd. The first query you are showing is what we use to load the RHS
                                Message 16 of 23 , May 3, 2010

                                  That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                   

                                  Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                  Sent: Monday, May 03, 2010 1:28 PM
                                  To: toad@yahoogroups.com
                                  Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   

                                   

                                  The problem is very obvious though.

                                   

                                  The new version (10.5.0.41) executes this query:

                                   

                                  SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                  FROM V$SESSION S

                                  WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                   AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                   

                                  which does full scan of sys.x$ksuse

                                   

                                  Plan

                                  SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                   

                                  3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                   

                                   

                                  1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                   

                                   

                                  2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                   

                                  The old version queries v$session using sid:

                                   

                                  select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                         , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                  from v$session

                                  where sid = :sid;

                                   

                                  which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                   

                                  Plan

                                  SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                   

                                  3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                   

                                   

                                  1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                   

                                   

                                  2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                   

                                   

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                  Sent: Monday, May 03, 2010 2:20 PM
                                  To: toad@yahoogroups.com
                                  Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   





                                  Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                   

                                  If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                  Sent: Monday, May 03, 2010 12:31 PM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                   

                                   

                                  [Attachment(s) from George.Meltser@... included below]

                                   

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                  Sent: Monday, May 03, 2010 1:28 PM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   





                                  It’s not stored automatically, you have to enable it.

                                   

                                  Database -> Spool SQL -> Spool to screen.

                                   

                                  Then you’ll see it in the output panel.

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                  Sent: Monday, May 03, 2010 12:27 PM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   

                                   

                                  I keep forgetting, where does TOAD store the debugged sql?

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                  Sent: Monday, May 03, 2010 1:22 PM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   





                                  Ok – sned the spooled query then J

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                  Sent: May 03, 2010 12:20
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   

                                   

                                  Yes, I collect systems stats every week.

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                  Sent: Monday, May 03, 2010 12:48 PM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   





                                  As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                   

                                  See the toad wiki faq on this ….

                                   

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                  Sent: May 03, 2010 11:46
                                  To: toad@yahoogroups.com
                                  Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                   

                                   

                                  What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                   

                                  If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                   

                                  As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                   

                                   

                                   

                                  From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                  Sent: Monday, May 03, 2010 11:33 AM
                                  To: toad@yahoogroups.com
                                  Subject: [toad] Problem with 10.5.0.41 Session Browser

                                   

                                   

                                  I quite often switch between "Group by none: and "Group by machine" views.
                                  It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                                  Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                                   





                                   


                                   





                                   


                                   





                                   


                                   





                                   


                                • George.Meltser@aigfpc.com
                                  It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines
                                  Message 17 of 23 , May 3, 2010

                                    It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                    156000 milliseconds or over 2.5 minutes.

                                     

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

                                    Session: CLIENT_ADMIN@CTPROD

                                    Timestamp: 13:23:27.330

                                    SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                    FROM V$SESSION S

                                    WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                     AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                     

                                     

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                    Sent: Monday, May 03, 2010 2:35 PM
                                    To: toad@yahoogroups.com
                                    Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     




                                    That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                     

                                    Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                    Sent: Monday, May 03, 2010 1:28 PM
                                    To: toad@yahoogroups.com
                                    Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     

                                     

                                    The problem is very obvious though.

                                     

                                    The new version (10.5.0.41) executes this query:

                                     

                                    SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                    FROM V$SESSION S

                                    WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                     AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                     

                                    which does full scan of sys.x$ksuse

                                     

                                    Plan

                                    SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                     

                                    3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                     

                                     

                                    1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                     

                                     

                                    2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                     

                                    The old version queries v$session using sid:

                                     

                                    select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                           , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                    from v$session

                                    where sid = :sid;

                                     

                                    which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                     

                                    Plan

                                    SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                     

                                    3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                     

                                     

                                    1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                     

                                     

                                    2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                     

                                     

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                    Sent: Monday, May 03, 2010 2:20 PM
                                    To: toad@yahoogroups.com
                                    Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     




                                    Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                     

                                    If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                    Sent: Monday, May 03, 2010 12:31 PM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                     

                                     

                                    [Attachment(s) from George.Meltser@... included below]

                                     

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                    Sent: Monday, May 03, 2010 1:28 PM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     




                                    It’s not stored automatically, you have to enable it.

                                     

                                    Database -> Spool SQL -> Spool to screen.

                                     

                                    Then you’ll see it in the output panel.

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                    Sent: Monday, May 03, 2010 12:27 PM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     

                                     

                                    I keep forgetting, where does TOAD store the debugged sql?

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                    Sent: Monday, May 03, 2010 1:22 PM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     




                                    Ok – sned the spooled query then J

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                    Sent: May 03, 2010 12:20
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     

                                     

                                    Yes, I collect systems stats every week.

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                    Sent: Monday, May 03, 2010 12:48 PM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     




                                    As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                     

                                    See the toad wiki faq on this ….

                                     

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                    Sent: May 03, 2010 11:46
                                    To: toad@yahoogroups.com
                                    Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                     

                                     

                                    What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                     

                                    If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                     

                                    As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                     

                                     

                                     

                                    From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                    Sent: Monday, May 03, 2010 11:33 AM
                                    To: toad@yahoogroups.com
                                    Subject: [toad] Problem with 10.5.0.41 Session Browser

                                     

                                     

                                    I quite often switch between "Group by none: and "Group by machine" views.
                                    It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                                    Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                                     




                                     


                                     




                                     


                                     




                                     


                                     




                                     


                                     






                                  • John Dorlon
                                    Oops, my mistake, sorry. The query is there in the file. First one. Did the debug come from a database where it runs quickly, but it runs slowly on some
                                    Message 18 of 23 , May 3, 2010

                                      Oops, my mistake, sorry.  The query is there in the file.  First one.

                                       

                                      Did the debug come from a database where it runs quickly, but it runs slowly on some other database?  In the file you sent, the query takes about 2 seconds to execute.  That query executed at timestamp 13:23:27.330.  The one after it is 13:23:29.752.  

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: Monday, May 03, 2010 1:41 PM
                                      To: toad@yahoogroups.com
                                      Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                      156000 milliseconds or over 2.5 minutes.

                                       

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

                                      Session: CLIENT_ADMIN@CTPROD

                                      Timestamp: 13:23:27.330

                                      SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                      FROM V$SESSION S

                                      WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                       AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                       

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                      Sent: Monday, May 03, 2010 2:35 PM
                                      To: toad@yahoogroups.com
                                      Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       





                                      That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                       

                                      Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: Monday, May 03, 2010 1:28 PM
                                      To: toad@yahoogroups.com
                                      Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      The problem is very obvious though.

                                       

                                      The new version (10.5.0.41) executes this query:

                                       

                                      SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                      FROM V$SESSION S

                                      WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                       AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                       

                                      which does full scan of sys.x$ksuse

                                       

                                      Plan

                                      SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                       

                                      3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                       

                                       

                                      1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                       

                                       

                                      2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                       

                                      The old version queries v$session using sid:

                                       

                                      select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                             , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                      from v$session

                                      where sid = :sid;

                                       

                                      which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                       

                                      Plan

                                      SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                       

                                      3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                       

                                       

                                      1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                       

                                       

                                      2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                       

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                      Sent: Monday, May 03, 2010 2:20 PM
                                      To: toad@yahoogroups.com
                                      Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       





                                      Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                       

                                      If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: Monday, May 03, 2010 12:31 PM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                       

                                       

                                      [Attachment(s) from George.Meltser@... included below]

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                      Sent: Monday, May 03, 2010 1:28 PM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       





                                      It’s not stored automatically, you have to enable it.

                                       

                                      Database -> Spool SQL -> Spool to screen.

                                       

                                      Then you’ll see it in the output panel.

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: Monday, May 03, 2010 12:27 PM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      I keep forgetting, where does TOAD store the debugged sql?

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                      Sent: Monday, May 03, 2010 1:22 PM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       





                                      Ok – sned the spooled query then J

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: May 03, 2010 12:20
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      Yes, I collect systems stats every week.

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                      Sent: Monday, May 03, 2010 12:48 PM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       





                                      As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                       

                                      See the toad wiki faq on this ….

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                      Sent: May 03, 2010 11:46
                                      To: toad@yahoogroups.com
                                      Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                       

                                      If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                       

                                      As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                       

                                       

                                       

                                      From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                      Sent: Monday, May 03, 2010 11:33 AM
                                      To: toad@yahoogroups.com
                                      Subject: [toad] Problem with 10.5.0.41 Session Browser

                                       

                                       

                                      I quite often switch between "Group by none: and "Group by machine" views.
                                      It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                                      Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                                       





                                       


                                       





                                       


                                       





                                       


                                       





                                       


                                       





                                       


                                    • George.Meltser@aigfpc.com
                                      The debug came from the same database. I just tried two different version of TOAD. They both select the same exact columns (Machine, OSProgram, OSUser,
                                      Message 19 of 23 , May 3, 2010

                                        The debug came from the same database. I just tried two different version of TOAD. They both select the same exact columns

                                        (Machine, OSProgram, OSUser, Program, Server, SID, Status) and the detaild grid points to the Session Tab.

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                        Sent: Monday, May 03, 2010 2:47 PM
                                        To: toad@yahoogroups.com
                                        Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        Oops, my mistake, sorry.  The query is there in the file.  First one.

                                         

                                        Did the debug come from a database where it runs quickly, but it runs slowly on some other database?  In the file you sent, the query takes about 2 seconds to execute.  That query executed at timestamp 13:23:27.330.  The one after it is 13:23:29.752.  

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: Monday, May 03, 2010 1:41 PM
                                        To: toad@yahoogroups.com
                                        Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                        156000 milliseconds or over 2.5 minutes.

                                         

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

                                        Session: CLIENT_ADMIN@CTPROD

                                        Timestamp: 13:23:27.330

                                        SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                        FROM V$SESSION S

                                        WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                         AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                         

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                        Sent: Monday, May 03, 2010 2:35 PM
                                        To: toad@yahoogroups.com
                                        Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                         

                                        Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: Monday, May 03, 2010 1:28 PM
                                        To: toad@yahoogroups.com
                                        Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        The problem is very obvious though.

                                         

                                        The new version (10.5.0.41) executes this query:

                                         

                                        SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                        FROM V$SESSION S

                                        WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                         AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                         

                                        which does full scan of sys.x$ksuse

                                         

                                        Plan

                                        SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                         

                                        3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                         

                                         

                                        1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                         

                                         

                                        2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                         

                                        The old version queries v$session using sid:

                                         

                                        select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                               , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                        from v$session

                                        where sid = :sid;

                                         

                                        which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                         

                                        Plan

                                        SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                         

                                        3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                         

                                         

                                        1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                         

                                         

                                        2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                         

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                        Sent: Monday, May 03, 2010 2:20 PM
                                        To: toad@yahoogroups.com
                                        Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                         

                                        If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: Monday, May 03, 2010 12:31 PM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                         

                                         

                                        [Attachment(s) from George.Meltser@... included below]

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                        Sent: Monday, May 03, 2010 1:28 PM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        It’s not stored automatically, you have to enable it.

                                         

                                        Database -> Spool SQL -> Spool to screen.

                                         

                                        Then you’ll see it in the output panel.

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: Monday, May 03, 2010 12:27 PM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        I keep forgetting, where does TOAD store the debugged sql?

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                        Sent: Monday, May 03, 2010 1:22 PM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        Ok – sned the spooled query then J

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: May 03, 2010 12:20
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        Yes, I collect systems stats every week.

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                        Sent: Monday, May 03, 2010 12:48 PM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         




                                        As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                         

                                        See the toad wiki faq on this ….

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                        Sent: May 03, 2010 11:46
                                        To: toad@yahoogroups.com
                                        Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                         

                                        If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                         

                                        As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                         

                                         

                                         

                                        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                        Sent: Monday, May 03, 2010 11:33 AM
                                        To: toad@yahoogroups.com
                                        Subject: [toad] Problem with 10.5.0.41 Session Browser

                                         

                                         

                                        I quite often switch between "Group by none: and "Group by machine" views.
                                        It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                                        Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                                         




                                         


                                         




                                         


                                         




                                         


                                         




                                         


                                         




                                         


                                         






                                      • John Dorlon
                                        I was just able to reproduce the repeated query execution here. Looking into it. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
                                        Message 20 of 23 , May 3, 2010

                                          I was just able to reproduce the repeated query  execution here.  Looking into it. 

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 1:50 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          The debug came from the same database. I just tried two different version of TOAD. They both select the same exact columns

                                          (Machine, OSProgram, OSUser, Program, Server, SID, Status) and the detaild grid points to the Session Tab.

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                          Sent: Monday, May 03, 2010 2:47 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          Oops, my mistake, sorry.  The query is there in the file.  First one.

                                           

                                          Did the debug come from a database where it runs quickly, but it runs slowly on some other database?  In the file you sent, the query takes about 2 seconds to execute.  That query executed at timestamp 13:23:27.330.  The one after it is 13:23:29.752.  

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 1:41 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                          156000 milliseconds or over 2.5 minutes.

                                           

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

                                          Session: CLIENT_ADMIN@CTPROD

                                          Timestamp: 13:23:27.330

                                          SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                          FROM V$SESSION S

                                          WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                           AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                           

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                          Sent: Monday, May 03, 2010 2:35 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                           

                                          Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 1:28 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          The problem is very obvious though.

                                           

                                          The new version (10.5.0.41) executes this query:

                                           

                                          SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                          FROM V$SESSION S

                                          WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                           AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                           

                                          which does full scan of sys.x$ksuse

                                           

                                          Plan

                                          SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                           

                                          3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                           

                                           

                                          1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                           

                                           

                                          2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                           

                                          The old version queries v$session using sid:

                                           

                                          select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                                 , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                          from v$session

                                          where sid = :sid;

                                           

                                          which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                           

                                          Plan

                                          SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                           

                                          3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                           

                                           

                                          1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                           

                                           

                                          2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                           

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                          Sent: Monday, May 03, 2010 2:20 PM
                                          To: toad@yahoogroups.com
                                          Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                           

                                          If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 12:31 PM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                           

                                           

                                          [Attachment(s) from George.Meltser@... included below]

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                          Sent: Monday, May 03, 2010 1:28 PM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          It’s not stored automatically, you have to enable it.

                                           

                                          Database -> Spool SQL -> Spool to screen.

                                           

                                          Then you’ll see it in the output panel.

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 12:27 PM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          I keep forgetting, where does TOAD store the debugged sql?

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                          Sent: Monday, May 03, 2010 1:22 PM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          Ok – sned the spooled query then J

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: May 03, 2010 12:20
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          Yes, I collect systems stats every week.

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                          Sent: Monday, May 03, 2010 12:48 PM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           





                                          As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                           

                                          See the toad wiki faq on this ….

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                          Sent: May 03, 2010 11:46
                                          To: toad@yahoogroups.com
                                          Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                           

                                          If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                           

                                          As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                           

                                           

                                           

                                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                          Sent: Monday, May 03, 2010 11:33 AM
                                          To: toad@yahoogroups.com
                                          Subject: [toad] Problem with 10.5.0.41 Session Browser

                                           

                                           

                                          I quite often switch between "Group by none: and "Group by machine" views.
                                          It used to take a few seconds to switch, but after upgrade it takes 5-8 minutes.
                                          Yes, there are a lot of sessions (around 2000). Has something changed? 10.1.1.8 was fine.

                                           





                                           


                                           





                                           


                                           





                                           


                                           





                                           


                                           





                                           


                                           





                                           




                                          (Message over 64 KB, truncated)

                                        • John Dorlon
                                          Hi George, I ve found the problem and it will be fixed in the next beta. Unfortunately, there is nothing you can do to fix it on your end at this time.
                                          Message 21 of 23 , May 3, 2010

                                            Hi George,

                                             

                                              I’ve found the problem and it will be fixed in the next beta.     Unfortunately,  there is nothing you can do to fix it on your end at this time.  One workaround that might work would be to come up with a filter that excludes everything, and apply it before you change groupings, then remove it afterwards.  That’s kind of a pain, I know, but better than waiting several minutes.

                                             

                                            -John

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 1:50 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            The debug came from the same database. I just tried two different version of TOAD. They both select the same exact columns

                                            (Machine, OSProgram, OSUser, Program, Server, SID, Status) and the detaild grid points to the Session Tab.

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                            Sent: Monday, May 03, 2010 2:47 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            Oops, my mistake, sorry.  The query is there in the file.  First one.

                                             

                                            Did the debug come from a database where it runs quickly, but it runs slowly on some other database?  In the file you sent, the query takes about 2 seconds to execute.  That query executed at timestamp 13:23:27.330.  The one after it is 13:23:29.752.  

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 1:41 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                            156000 milliseconds or over 2.5 minutes.

                                             

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

                                            Session: CLIENT_ADMIN@CTPROD

                                            Timestamp: 13:23:27.330

                                            SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                            FROM V$SESSION S

                                            WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                             AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                             

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                            Sent: Monday, May 03, 2010 2:35 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                             

                                            Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 1:28 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            The problem is very obvious though.

                                             

                                            The new version (10.5.0.41) executes this query:

                                             

                                            SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                            FROM V$SESSION S

                                            WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                             AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                             

                                            which does full scan of sys.x$ksuse

                                             

                                            Plan

                                            SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                             

                                            3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                             

                                             

                                            1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                             

                                             

                                            2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                             

                                            The old version queries v$session using sid:

                                             

                                            select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                                   , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                            from v$session

                                            where sid = :sid;

                                             

                                            which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                             

                                            Plan

                                            SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                             

                                            3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                             

                                             

                                            1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                             

                                             

                                            2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                             

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                            Sent: Monday, May 03, 2010 2:20 PM
                                            To: toad@yahoogroups.com
                                            Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                             

                                            If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 12:31 PM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                             

                                             

                                            [Attachment(s) from George.Meltser@... included below]

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                            Sent: Monday, May 03, 2010 1:28 PM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            It’s not stored automatically, you have to enable it.

                                             

                                            Database -> Spool SQL -> Spool to screen.

                                             

                                            Then you’ll see it in the output panel.

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 12:27 PM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            I keep forgetting, where does TOAD store the debugged sql?

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                            Sent: Monday, May 03, 2010 1:22 PM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            Ok – sned the spooled query then J

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: May 03, 2010 12:20
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            Yes, I collect systems stats every week.

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                            Sent: Monday, May 03, 2010 12:48 PM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             





                                            As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                             

                                            See the toad wiki faq on this ….

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                            Sent: May 03, 2010 11:46
                                            To: toad@yahoogroups.com
                                            Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                             

                                             

                                            What database version are you connected to?  If 11g or newer, we did add a join to V$SESSION_CONNECT_INFO.  I have never heard of any problems with it, but we can definitely make this an option if that is the cause of the problem.

                                             

                                            If you aren’t sure, just turn on SQL Spooling to see what query is taking so long.

                                             

                                            As a workaround, you might try selecting a different tab on the RHS/Bottom before you change the grouping.

                                             

                                             

                                             

                                            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                            Sent: Monday, May 03, 2010 11:33 AM
                                            To: toad@yahoogroups.com
                                            Subject: [toad] Problem with 10.5.0.41 Session Browser
                                            <

                                            (Message over 64 KB, truncated)

                                          • John Dorlon
                                            Thanksb. It sb fixedb for nextb betab. From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of hickend Sent: Monday, May 03, 2010 10:30 AM To:
                                            Message 22 of 23 , May 3, 2010

                                              Thanksb.  It’sb fixedb for nextb betab.

                                               

                                               

                                              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of hickend
                                              Sent: Monday, May 03, 2010 10:30 AM
                                              To: toad@yahoogroups.com
                                              Subject: [toad] Typo in Job Scheduler Message....

                                               

                                               

                                              After executing a scheduler job through a right-click the "Toad Message" that comes up says:

                                              Jobs have been executed. Check Job lob for details.
                                              <<OK>>

                                              Although lob might be appropriate, I think that log is the word desired.

                                              David H
                                              Utahtoad at gmail dot com

                                            • George.Meltser@aigfpc.com
                                              John, thanks for looking into it. I will just stay away from 10.5 for time being as I use this query many times a day. From: toad@yahoogroups.com
                                              Message 23 of 23 , May 3, 2010

                                                John, thanks for looking into it.

                                                I will just stay away from 10.5 for time being as I use this query many times a day.

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                                Sent: Monday, May 03, 2010 3:34 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                Hi George,

                                                 

                                                  I’ve found the problem and it will be fixed in the next beta.     Unfortunately,  there is nothing you can do to fix it on your end at this time.  One workaround that might work would be to come up with a filter that excludes everything, and apply it before you change groupings, then remove it afterwards.  That’s kind of a pain, I know, but better than waiting several minutes.

                                                 

                                                -John

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: Monday, May 03, 2010 1:50 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 

                                                 

                                                The debug came from the same database. I just tried two different version of TOAD. They both select the same exact columns

                                                (Machine, OSProgram, OSUser, Program, Server, SID, Status) and the detaild grid points to the Session Tab.

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                                Sent: Monday, May 03, 2010 2:47 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                Oops, my mistake, sorry.  The query is there in the file.  First one.

                                                 

                                                Did the debug come from a database where it runs quickly, but it runs slowly on some other database?  In the file you sent, the query takes about 2 seconds to execute.  That query executed at timestamp 13:23:27.330.  The one after it is 13:23:29.752.  

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: Monday, May 03, 2010 1:41 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 

                                                 

                                                It is definitely in the file that I sent you. And the query is not that fast, it took 156 milliseconds for 1 machine and we have over a thousand machines connected to oracle, so this query would take

                                                156000 milliseconds or over 2.5 minutes.

                                                 

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

                                                Session: CLIENT_ADMIN@CTPROD

                                                Timestamp: 13:23:27.330

                                                SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                                FROM V$SESSION S

                                                WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                                 AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )

                                                 

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                                Sent: Monday, May 03, 2010 2:35 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                That query is not in the file you sent me, but the first query did not replace the 2nd.   The first query you are showing is what we use to load the RHS session tab.   The 2nd query is what we use to get the current SQL.  

                                                 

                                                Did you try running that query in the editor to be sure it is the one running slowly?    That plan looks like it has a fast running query to me (cost = 0 and bytes = 516 is pretty insignificant)

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: Monday, May 03, 2010 1:28 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 

                                                 

                                                The problem is very obvious though.

                                                 

                                                The new version (10.5.0.41) executes this query:

                                                 

                                                SELECT  s.AUDSID, s.Client_Info, s.Command, s.Last_Call_ET, s.LockWait, s.Logon_Time, s.Machine, s.Module, s.OSUser, s.PADDR, s.Process, s.Program, s.SchemaName, s.Serial#, s.Server, s.SID, s.Status, s.Terminal, s.Type, s.Action, s.UserName, s.Failover_Method, s.Failed_Over, s.Failover_Type, s.PDML_Enabled, s.PDDL_Status, s.PDML_Status, s.PQ_Status, s.Resource_Consumer_Group, s.SQL_Child_Number, s.SQL_ID, s.Service_Name, s.SQL_Trace

                                                FROM V$SESSION S

                                                WHERE ( UPPER(MACHINE) = 'AIGFP3' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'CLIENT_ADMIN' or  UPPER(MACHINE) = 'OPS$ORACLE' or  UPPER(MACHINE) = 'CLIENT_ADMIN')

                                                 AND  ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') );

                                                 

                                                which does full scan of sys.x$ksuse

                                                 

                                                Plan

                                                SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 529 Cardinality: 1

                                                 

                                                3 NESTED LOOPS Cost: 0 Bytes: 529 Cardinality: 1

                                                 

                                                 

                                                1 FIXED TABLE FULL TABLE (FIXED) SYS.X$KSUSE Cost: 0 Bytes: 516 Cardinality: 1

                                                 

                                                 

                                                2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                                 

                                                The old version queries v$session using sid:

                                                 

                                                select decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash

                                                       , decode(sql_id, null, prev_sql_id, sql_id) sql_id

                                                from v$session

                                                where sid = :sid;

                                                 

                                                which DOES NOT DO FULL SCAN OF sys.x$ksuse

                                                 

                                                Plan

                                                SELECT STATEMENT ALL_ROWS Cost: 0 Bytes: 120 Cardinality: 1

                                                 

                                                3 NESTED LOOPS Cost: 0 Bytes: 120 Cardinality: 1

                                                 

                                                 

                                                1 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSUSE (ind:1) Cost: 0 Bytes: 107 Cardinality: 1

                                                 

                                                 

                                                2 FIXED TABLE FIXED INDEX TABLE (FIXED) SYS.X$KSLED (ind:2) Cost: 0 Bytes: 13 Cardinality: 1

                                                 

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                                Sent: Monday, May 03, 2010 2:20 PM
                                                To: toad@yahoogroups.com
                                                Subject: RE: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                Weird.  I see the same statement executed  over and over for about 75 seconds.    I have no idea why it would be doing that, but something is definitely going wrong.

                                                 

                                                If you zip up your user files folder and send it to me offline, I’ll see if I can reproduce the problem.

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: Monday, May 03, 2010 12:31 PM
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser [1 Attachment]

                                                 

                                                 

                                                [Attachment(s) from George.Meltser@... included below]

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of John Dorlon
                                                Sent: Monday, May 03, 2010 1:28 PM
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                It’s not stored automatically, you have to enable it.

                                                 

                                                Database -> Spool SQL -> Spool to screen.

                                                 

                                                Then you’ll see it in the output panel.

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: Monday, May 03, 2010 12:27 PM
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 

                                                 

                                                I keep forgetting, where does TOAD store the debugged sql?

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                                Sent: Monday, May 03, 2010 1:22 PM
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                Ok – sned the spooled query then J

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of George.Meltser@...
                                                Sent: May 03, 2010 12:20
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 

                                                 

                                                Yes, I collect systems stats every week.

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Bert Scalzo
                                                Sent: Monday, May 03, 2010 12:48 PM
                                                To: toad@yahoogroups.com
                                                Subject: [toad] RE: Problem with 10.5.0.41 Session Browser

                                                 




                                                As always – have you collected system stats – because anytime a query goes from seconds to minutes when all we do is add one more system table/view – then this is a reasonable question

                                                 

                                                See the toad wiki faq on this ….

                                                 

                                                 

                                                From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of <

                                                (Message over 64 KB, truncated)

                                              Your message has been successfully submitted and would be delivered to recipients shortly.