7 Replies Latest reply: Nov 22, 2013 11:40 AM by rp0428 RSS

    how do i monitor activity in sql developer?

    3fca34d0-9ad2-464a-adcb-67111ce9c5a9

      Hello,

       

      I'm wondering if there's a way to monitor database activity in SQL Developer.

      Specifically, I need a way to tell how many times a certain stored procedure is called and what parameter values are passed in for each call. Is there a way to do this?

        • 2. Re: how do i monitor activity in sql developer?
          rp0428
          I'm wondering if there's a way to monitor database activity in SQL Developer.

          Specifically, I need a way to tell how many times a certain stored procedure is called and what parameter values are passed in for each call. Is there a way to do this?

          Your question isn't clear. There is no 'database activity' in Sql Developer; it doesn't execute stored procedures the database server does.

           

          So while you may have sql developer open and be using it the server may be executing code that calls that procedure a hundred times but it isn't going to notify you.

           

          Are you asking how to monitor or audit database activity?

          • 3. Re: how do i monitor activity in sql developer?
            3fca34d0-9ad2-464a-adcb-67111ce9c5a9

            TSharma,

             

            Thanks for the links.

             

            rp0428,

             

            I didn't mean "database activity in SQL Developer", I meant "a way to monitor... in SQL Developer". I'm wondering if there's a tool in SQL Developer (or a plugin I can download) that allows me to monitor the database activity going on at the server.

             

            My situation is this: I'm working in BIRT Report Design and I have a feeling the datasets are querying the database twice every time they're invoked. There's no need for them to hit the database twice. But I need some way of verifying that this is what's happening. So I'm looking for a way to monitor database activity and find out if the queries associated with the datasets are being run twice for every dataset invocation.

             

            Does that make sense?

            • 4. Re: how do i monitor activity in sql developer?
              Jeff Smith Sqldev Pm-Oracle

              You might be able to get what you need using Tools > Monitor Sessions.

               

              You can see the sql being executed for a selected session. But I think you might get a better answer if you do a trace for one of your application sessions. You can then take the trace file and confirm your suspicion as each of the sql statements executed will be in there.

              • 5. Re: how do i monitor activity in sql developer?
                rp0428
                I'm working in BIRT Report Design and I have a feeling the datasets are querying the database twice every time they're invoked. There's no need for them to hit the database twice. But I need some way of verifying that this is what's happening. So I'm looking for a way to monitor database activity and find out if the queries associated with the datasets are being run twice for every dataset invocation.

                Have you tried monitoring the session using 'Tools - Monitor Sessions'?

                 

                Other than that I'm not aware of anything in sql developer. So maybe you should post your question in the SQL and PL/SQL forum instead:

                https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

                 

                Generally 'feelings' are not a good way to determine performance issues.

                 

                In your new post describe the actual PROBLEM you are trying to solve. That is, what, exactly, leads you to believe that the database is being hit twice?

                 

                More importantly what difference does it make? Some tools will hit the database once in order to get the metadata they need to properly prepare for the result set. The second hit is the actual query or procedure execution. Some report tools (business objects, Crystal) can use stored procedures as data sources and those procedures typically return a generic REF CURSOR.

                 

                The tool needs to query the DB to get the metadata for that cursor so it knows how many columns the result set will include and the datatypes of those columns.

                 

                For the SQL, audit or trace issues related to your question you need to post in the correct forum.

                • 6. Re: how do i monitor activity in sql developer?
                  3fca34d0-9ad2-464a-adcb-67111ce9c5a9

                  [quote]You might be able to get what you need using Tools > Monitor Sessions.

                   

                  You can see the sql being executed for a selected session. But I think you might get a better answer if you do a trace for one of your application sessions. You can then take the trace file and confirm your suspicion as each of the sql statements executed will be in there.
                  [/quote]

                   

                  I tried Monitor Session, but as it seems only to monitor my session as an SQL Developer user, it shows the same results before and after I run a report in BIRT Report Designer. I guess this is why you recommend doing a trace of BIRT Report Designer instead. Am I right?

                   

                  [quote]In your new post describe the actual PROBLEM you are trying to solve.[/quote]

                   

                  OK. The problem is that we're running a report that's too big. It either consistently times out or runs out of heap memory.

                   

                  What we've noticed is that for one of our datasets, it seems to be initialized or invoked twice any time it's needed. We know this because we get the value for one of its parameters from a function in the script. In that function, we display a message like "getting value X". X should never be the same value twice, so when we see this value being displayed two times in a row consistently, we conclude that the dataset is being initialized or invoked twice.

                   

                  Now, I realize this doesn't mean that the database is being [i]hit[/i] two times every time the dataset is needed, but that's what I want to verify. But now that you've mentioned the initial gathering of metadata, this makes a lot of sense, especially since we [i]are[/i] using a REF_CURSOR in our stored procedure (incidentally, this means that the database [i]is[/i] being hit twice, but not that the [i]query[/i] is being run twice).

                   

                  At the end of the day, we'd like to be able to cut down on the time it takes and the amount of memory being used so that we can actually produce a report without the application crashing.

                   

                  [quote]For the SQL, audit or trace issues related to your question you need to post in the correct forum.[/quote]

                   

                  And that would be the SQL and PL/SQL forum, correct?

                  • 7. Re: how do i monitor activity in sql developer?
                    rp0428
                    And that would be the SQL and PL/SQL forum, correct?

                     

                    Yes - I gave you the link above. But based on what you just posted there is still not enough info for anyone there to help you with except showing you how to do tracing.

                    The problem is that we're running a report that's too big. It either consistently times out or runs out of heap memory.

                    Well - that's a better start.

                     

                    But now it sounds like your problem is related more to BIRT or Java than SQL or sql developer. There are BIRT forums you can post to and there is an Oracle Java forum you can post to.

                     

                    If the problem isn't directly related to sql developer this forum probably won't be much help to you.

                     

                    Before you post in one of the other forums you might just try to 'add more heap space' and see if that solves the problem.

                     

                    Remember - no one on the forums can see your monitor or see what you are doing. So we have no idea how much heap space you are allocating now. When you ask for help it is YOUR responbilibity to provide as much info as possible:

                     

                    1. exactly what you are doing

                    2. exactly how you are doing it

                    3. exactly what results you are getting

                     

                    You haven't provided any of that.

                     

                    SHOW us - don't just tell us.