6 Replies Latest reply on Apr 24, 2017 8:06 PM by SteveB

    Session Monitor not working properly in 4.2

    SteveB

      I've recently upgraded to 4.2 (Version 4.2.0.17.089) from 4.1.5 on my Mac (10.12.4) and when using the session monitor (Tools, Monitor Sessions), if the active tab in the lower panel is 'Active SQL' (as per default), the first time I click on a row in the top panel I get this:

       

      Screen Shot 2017-04-19 at 9.20.55 am.png

      If I right click on my connection and choose Reconnect I get this:

       

      Screen Shot 2017-04-19 at 9.22.08 am.png

      If another tab is active (e.g. 'Application'), I don't get the 'your connection has been reset' dialog.  However, there is some weird stuff going on.  Sometimes I get multiple rows selected in the lower pane (e.g. in the Application tab) when I've got a single session selected in the top pane.  Ours is a RAC environment so I wonder if it is not using the instance ID in the lower query?

       

      I also sometimes seem to get multiple statements in the 'Active SQL' tab (when it works that is).

       

      Not sure how to go about diagnosing this.  I tried starting sqldeveloper from the command line - thinking there may be some java diagnostics, but there weren't.  My java version is:

       

      au-c02sr279gvc8:bin stbaldwin$ java -showversion

      java version "1.8.0_111"

       

      My connection type is 'TNS'.

       

      Any clues?

       

      Thanks,

      Steve

        • 1. Re: Session Monitor not working properly in 4.2
          thatJeffSmith-Oracle

          The first msg means your connection died but we were able to reestablish it, hence the 2nd dialog when you ask for the reconnect - you're already connected.

           

          The issue on the multiple entries Def sounds rac related.

           

          What version of Oracle, how many rac nodes in your cluster?

          • 2. Re: Session Monitor not working properly in 4.2
            SteveB

            Thanks Jeff,

             

            Oracle 11.2.0.4 SE, 2 node cluster.

             

            The odd thing is that even though as you say the connection was re-established, it won't show the contents of the 'Active SQL' tab.  For example:

             

            Screen Shot 2017-04-19 at 12.38.30 pm.png

            If I immediately click on one of the other tabs, I see this dialog:

            Screen Shot 2017-04-19 at 12.40.34 pm.png

            Is there any way to see why the connection died?  Maybe increase logging level via a command line param when starting sqldeveloper?

             

            Is there any way to show internal queries being executed like you can in Toad?

             

            Cheers,

             

            Steve

            • 3. Re: Session Monitor not working properly in 4.2
              thatJeffSmith-Oracle

              You should be able to just refresh the report.

               

              view - log - statements...this will show all of the queries

              • 4. Re: Session Monitor not working properly in 4.2
                SteveB

                Thanks again.

                 

                I found this query when clicking on the Active SQL tab:

                 

                select replace(q.SQL_FULLTEXT,chr(0)) sql_text

                                                        from gv$session s,gv$sql q

                                            where s.sql_address = q.address

                                                         and s.sql_hash_value = q.hash_value

                                                         and s.sid = :SID

                 

                Doesn't it need a bind var for inst_id, and a join on child_number ?  For example this seems to do the trick:

                 

                select replace(q.SQL_FULLTEXT,chr(0)) sql_text

                                                        from gv$session s,gv$sql q

                                            where s.sql_address = q.address

                                                         and s.sql_hash_value = q.hash_value

                                                         and s.sql_child_number = q.child_number

                                                         and s.sid = :SID

                                                         and s.inst_id = :inst_id

                 

                Where the bind value for :inst_id obviously comes from the top pane.

                • 5. Re: Session Monitor not working properly in 4.2
                  SteveB

                  Jeff,

                   

                  I think there are other statements that are not quite right in a RAC environment.  Apart from the statements behind the 'Server' and 'Client' tabs, I think the statements behind all other tabs are missing a bind for :inst_id.  The ones that bind :sql_id _may_ be ok.  I'm not sure if that is guaranteed unique across cluster nodes, but it is probably safer to assume it is not.

                   

                  Cheers,

                  Steve

                  • 6. Re: Session Monitor not working properly in 4.2
                    SteveB

                    I just checked the queries behind the SQL Monitor lower panel in version 4.1.3 and it seems they were broken for RAC there too.  I never noticed it before - shame on me.

                     

                    However, the older version doesn't get the 'Your connection has been reset...' issue.

                     

                    Anyway, I've logged it with MOS, so hopefully I've gone through the correct procedure and it will soon be addressed.

                     

                    Thanks,

                    Steve