1 2 3 Previous Next 41 Replies Latest reply on Jan 23, 2013 11:20 AM by Rob_J

    Database Memory Usage

    yxes2013
      Hi all,

      Oracle 9.2.0.6
      OEL 5.6

      How do I check if the database is runnning low of memory? Is there a command to select the V$ performance table to show it?
      or in the staspack?


      Thanks a lot,

      Edited by: yxes2013 on Jan 16, 2013 9:00 AM
        • 1. Re: Database Memory Usage
          sb92075
          yxes2013 wrote:
          Hi all,

          Oracle 9.2.0.6


          How do I check if the database is runnning low of memory? Is there a command to select the V$ performance table to show it?
          or in the staspack?


          Thanks a lot,
          on any NIX memory will ALWAYS* be closed to 100% used.
          The DB can not provide a definitive answer to memory utilization.

          What is OS name & version?
          1 person found this helpful
          • 2. Re: Database Memory Usage
            JustinCave
            What memory, specifically, are you talking about?

            I believe v$db_cache_advice should be available back in Oracle 9.2 if you are asking for assistance determining if the database could benefit from a larger SGA. Unfortunately, I don't believe the PGA advisor was around in the Oracle 9.2 days.

            If you are asking whether the database is running out of memory on the physical database server (i.e. whether there is swapping going on on the server), you'd need to work with the server admins or at least tell us what operating system you are using.

            Incidentally using a version of Oracle that is 4 major revisions out of date and has been desupported in many years and in addition to all that using a non-terminal patchset implies that you aren't really concerned with this database. Is there a reason that you haven't installed patchsets or upgraded the database in, what, probably 6 or 7 years?

            Justin
            1 person found this helpful
            • 3. Re: Database Memory Usage
              Aman....
              yxes2013 wrote:
              Hi all,

              Oracle 9.2.0.6


              How do I check if the database is runnning low of memory? Is there a command to select the V$ performance table to show it?
              or in the staspack?

              As Justin mentioned, there is no such view that's precisely only to show that if the database is running out of the memory. But if it is indeed, there would be other wait events that would start to arise in the database. For example, free buffer wait if you are running out of buffer cache or more number of hard parses , indicated by the drop in the library cache hit% and more amount of shared pool latch contention being reported. So it would be better if you take a Statspack report and see if you are really having any issue with the database or not. If it doesn't show anything, your db is fine and so is it's memroy consumption.

              Aman....
              1 person found this helpful
              • 4. Re: Database Memory Usage
                846231
                check your vmstat
                1 person found this helpful
                • 5. Re: Database Memory Usage
                  Aman....
                  KinsaKaUy? wrote:
                  check your vmstat
                  Hmm IMO even that would not directly mention that you are running out of memory purely from the Oracle databsae side. The vmstat is a good option to check the run queue statistics and in general to have a look at the swapping going on but if you ask specifically that whether the buffer cache is properly sized or not, I don't think it's the way to get the answer. Am I missing something?

                  Aman....
                  1 person found this helpful
                  • 6. Re: Database Memory Usage
                    Billy~Verreynne
                    Aman.... wrote:

                    Am I missing something?
                    Don't think so. Oracle grabs a chunk of memory as configured. vmstat and o/s reports will show the size of the chunk. Not how effectively that chunk is utilised by Oracle. The chunk is like a glass. The o/s sees the glass. It has no idea whether it is half empty, nicely filled, or filled to the brim with no space left. For that you need to ask the person filling and drinking from the glass - in this case, Oracle.
                    • 7. Re: Database Memory Usage
                      Aman....
                      Billy  Verreynne  wrote:
                      Aman.... wrote:

                      Am I missing something?
                      Don't think so. Oracle grabs a chunk of memory as configured. vmstat and o/s reports will show the size of the chunk. Not how effectively that chunk is utilised by Oracle. The chunk is like a glass. The o/s sees the glass. It has no idea whether it is half empty, nicely filled, or filled to the brim with no space left. For that you need to ask the person filling and drinking from the glass - in this case, Oracle.
                      Fly and the doctor and the glass filled or not filled, I always get so amazed and impressed with the analogies that you use Billy :) . Thanks a bunch!

                      Regards
                      Aman....
                      • 8. Re: Database Memory Usage
                        yxes2013
                        Thanks all,

                        I am really confused how to attack my issue. Since statspack has nothing to show, in my case.
                        What is worse is that the users are telling me that even if there is more process running the server still seems hanging, or
                        they are just getting paranoid.

                        Edited by: yxes2013 on Jan 16, 2013 8:58 AM
                        • 9. Re: Database Memory Usage
                          JustinCave
                          yxes2013 wrote:
                          I am really confused how to attack my issue.
                          What issue? Are you, perhaps, referring to Re: Performance Issue where Jonathan Lewis and a bunch of others have already weighed in with a number of observations, suggestions?
                          Since statspack has nothing to show, in my case.
                          Or are you referring to a different issue? Because the Statspack report snippets that you posted in the earlier thread seem to have quite a bit of useful information.
                          What is worse is that the users are telling me that even if there is more process running the server still seems hanging, or
                          they are just getting paranoid.
                          This sentence doesn't make sense. It sounds like you are saying that users are complaining that when they run more processes, they get results more slowly. That is a perfectly normal and expected result of asking any system to do do more work in parallel.

                          Justin
                          • 10. Re: Database Memory Usage
                            sb92075
                            yxes2013 wrote:
                            Hi all,

                            Oracle 9.2.0.6
                            OEL 5.6

                            How do I check if the database is runnning low of memory? Is there a command to select the V$ performance table to show it?
                            or in the staspack?


                            Thanks a lot,

                            Edited by: yxes2013 on Jan 16, 2013 9:00 AM
                            [oracle@localhost ~]$ vmstat 6 10
                            procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
                             r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
                             0  0 106648  89748  67964 472396    1    1     9    29  218   88  1  2 97  0  0
                             0  0 106648  93948  67976 472392    0    0     0    23 1070 1001  1  4 95  0  0
                             0  0 106648  93964  67992 472400    0    0     0    19 1001  931  1  1 98  0  0
                             0  0 106648  93964  68000 472400    0    0     0    13 1001  926  1  1 98  0  0
                             0  0 106648  93964  68016 472400    0    0     0    18  998  936  1  2 97  0  0
                             0  0 106648  93964  68032 472400    0    0     0    17 1001  936  1  2 98  0  0
                             0  0 106648  90616  68044 472408    0    0     1    45 1001  941  2  4 95  0  0
                             0  0 106648  89996  68052 472408    0    0     0    32  998  942  1  2 97  0  0
                             0  0 106648  89988  68060 472408    0    0     0    13 1000  944  1  1 97  0  0
                             0  0 106648  89996  68076 472408    0    0     0    26 1001  944  1  2 97  0  0
                            [oracle@localhost ~]$ 
                            when (si+so) is less than (bi+bo), then RAM is NOT a system bottleneck.
                            • 11. Re: Database Memory Usage
                              vlethakula
                              check for bad performing SQL's
                              • 12. Re: Database Memory Usage
                                yxes2013
                                Thanks all,
                                when (si+so) is less than (bi+bo), then RAM is NOT a system bottleneck.
                                Yes... I like this kind of direct to the point formula analysis. Can you give me some more tips? like in statspack report?
                                In your own experience, what top 5 key areas you are checking there?

                                For example, I am back to square one. There is this issue repeating again. The users complaing that all their process where hanging and
                                I am called to fix it. Supposing you are in my place.

                                What action will you take?

                                1. Print the statspack report ( I already set it up takeing snap every 30 mins).
                                2. Print vmstat 5 10
                                3. What else do you do?
                                4.
                                • 13. Re: Database Memory Usage
                                  JohnWatson
                                  yxes2013 wrote:

                                  I am really confused how to attack my issue. Since statspack has nothing to show, in my case.
                                  What is worse is that the users are telling me that even if there is more process running the server still seems hanging, or
                                  they are just getting paranoid.
                                  Your statspack report DOES have something to show. I've checked an old 9.2.0.7 statspack report, and the sections headed Buffer Pool Advisory, PGA Memory Advisory, and Shared Pool Advisory will tell you exactly what you need to know regarding tuning memory.
                                  • 14. Re: Database Memory Usage
                                    sb92075
                                    is RAM, CPU or I/O the primary bottleneck?

                                    use COPY & PASTE so we can see which system resource is primary bottleneck.
                                    1 2 3 Previous Next