4 Replies Latest reply on Jun 1, 2010 6:10 PM by user10482912

    Oracle 11g SE One Performance Monitoring and Tuning

    user10482912
      I've recently "downgraded" a couple databases from Enterprise Edition to Standard Edition One and in doing so lost the use of Grid Control, specifically Performance Pack. Now, of course, monitoring and tuning, is a bit more cumbersome.

      Can anyone offer advice on methods specific to Standard Edition One for monitoring/tuning databases? Specific SQL statements you use, 3rd party software, built-in packages in the database that can be used?
        • 1. Re: Oracle 11g SE One Performance Monitoring and Tuning
          Srini Chavali-Oracle
          Is Database Control not an option ? http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/em_manage.htm#ADMQS031

          HTH
          Srini
          • 2. Re: Oracle 11g SE One Performance Monitoring and Tuning
            user10482912
            It is, but I've found that within database control the performance monitoring tab is unavailable just like in Grid Control. I get a message that basically states this version of Oracle is not licensed to use the performance pack. That makes sense, because it's SE One. I'm not new to managing databases (18 years of experience), but I've not managed SE One until now and I'm not sure what should and should not be available in it. Thanks for the feedback.
            • 3. Re: Oracle 11g SE One Performance Monitoring and Tuning
              Charles Hooper
              There are a large number of performance views that can be queried, and if you capture the current values, wait a known amount of time (whether 1 minute or an hour) and then recapture the current values, you can determine by how much the statistics have changed over the time period. Do not query the views that begin with either "DBA_HIST_" or "DBA_ADVISOR_", since those views belong to the extra cost licenses that are only available with the Enterprise Edition (access to the views is audited).

              You could, for instance, track the changes in the time model statistics, and use that as a basis to determine if anything else should be examined. It would probably also make sense to track the changes in the various non-idle wait events. If you have a Windows client PC, you can experiment with one of the scripts that I wrote which tracks changes to the time model statistics, changes to the OS statistics, and changes to the wait events:
              http://hoopercharles.wordpress.com/2010/02/09/working-with-oracle%e2%80%99s-time-model-data-3/

              If you do not have a Windows client PC, you might be able to determine how the script works and build your own solution.

              Charles Hooper
              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
              http://hoopercharles.wordpress.com/
              IT Manager/Oracle DBA
              K&M Machine-Fabricating, Inc.
              1 person found this helpful
              • 4. Re: Oracle 11g SE One Performance Monitoring and Tuning
                user10482912
                Thanks Charles. That is helpful, but I'm still hopeful that others have input. I'm aware of the data dictionary and various performance views, but it's one of those situations where it's been years since I've really heavily used them, not to mention I'm new to 11g and not familiar with any new views/features in this version, and I'm trying to avoid spending a lot of time writing my own SQL statements to monitoring the database. I was hoping there might be an online resource or repository of helpful statements that DBAs can use or better yet -- that I'm mistaken and there are performance features inside DB Console that I just need to enable.

                Thanks!