6 Replies Latest reply: Jul 24, 2013 1:29 AM by JohnWatson RSS

    why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon

    862469

      Dear Experts,

       

      Please help me in checking why virtual bytes in perfmon is taking more than memory we allocated.

      OS

      Windows Server 2003 R2 Enterprise x64 Edition Service Pack 2 (build 3790)

      ORACLE

      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

      PL/SQL Release 11.1.0.6.0 - Production

      CORE    11.1.0.6.0      Production

      TNS for 64-bit Windows: Version 11.1.0.6.0 - Production

      NLSRTL Version 11.1.0.6.0 - Production

      Total RAM on box is 8G

       

      Page File

      Windows virtual memory equal on C,D,E,F drives is set to  12273M and allocated now is 11262M

       

      LPENABLED is not set

      SQL> show parameter pga_

      pga_aggregate_target   0

      SQL> show sga

       

      Total System Global Area 5344731136 bytes

      Fixed Size                  2114736 bytes

      Variable Size            4630514512 bytes

      Database Buffers          671088640 bytes

      Redo Buffers               41013248 bytes

       

      SQL> show parameter memory

       

       

      hi_shared_memory_address  0      

      memory_max_target    5G                

      memory_target      5G                  

      shared_memory_address  0             

      SQL> show parameter sga

       

      lock_sga   FALSE                         

      pre_page_sga     FALSE                   

      sga_max_size   5G                     

      sga_target      0

                           

      Question :

      Why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon

       

      Thanks in advance your time and help.

        • 2. Re: why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon
          sybrand_b

          When you are talking about Oracle.exe you seem to disregard

           

          - The executable will take memory too

          - Oracle is a multithreaded process on Windows, so the memory consumption goes also to the various client session which are connected to the database.

           

          This means, you consume about 3G of memory beyond the SGA, and this is quite reasonable.

          So if you want to make sure Oracle consumes less than 8G of memory, you have two options

          - reduce the SGA

          You are allocating more than 50 percent of memory to the SGA. Oracle has never recommended that

          You might be willing to use v$sga_target_advisor to see the impact of this change.

          You might also need to tune your SQL statements

          - the second option you have is to shutdown Oracle.

          For sure it will use less than 8G in this solution.

           

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

          Sybrand Bakker

          Senior Oracle DBA

          • 4. Re: why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon
            JohnWatson

            Are you sure this is correct? With memory_target=5G, that should be all that is used, including PGA. I would suggest a couple of quick tests:

             

            select pool,sum(bytes) from v$sgastat group by pool;

            select * from v$pgastat;

             

            and then, explain how you are determining the memory usage from the Windows side. This could well be a problem with interpreting what Windows is showing (with which I am not very good).

            • 5. Re: why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon
              862469

              John, Thanks for the reply.

               

              Please find the output of query

               

               

              SQL> select pool,sum(bytes) from v$sgastat group by pool;
              
              
              POOL         SUM(BYTES)
              ------------ ----------
                            915543216
              java pool      67108864
              streams pool  268503944
              shared pool  2147494008
              large pool     67108864
              
              
              SQL>
              SQL> select * from v$pgastat;
              
              
              NAME                                                                  VALUE UNIT
              ---------------------------------------------------------------- ---------- ------------
              aggregate PGA target parameter                                    671088640 bytes
              aggregate PGA auto target                                         147564544 bytes
              global memory bound                                               104857600 bytes
              total PGA inuse                                                   533962752 bytes
              total PGA allocated                                               810819584 bytes
              maximum PGA allocated                                            1244466176 bytes
              total freeable PGA memory                                          90243072 bytes
              process count                                                           450
              max processes count                                                     588
              PGA memory freed back to OS                                      1.9883E+10 bytes
              total PGA used for auto workareas                                  28210176 bytes
              maximum PGA used for auto workareas                                39973888 bytes
              total PGA used for manual workareas                                       0 bytes
              maximum PGA used for manual workareas                                531456 bytes
              over allocation count                                                     7
              bytes processed                                                  2.6307E+10 bytes
              extra bytes read/written                                                  0 bytes
              cache hit percentage                                                    100 percent
              recompute count (total)                                               72720
              
              
              19 rows selected.
              
              
              SQL>
              SQL>  show parameter _target
              
              
              NAME                                 TYPE                             VALUE
              ------------------------------------ -------------------------------- -------------------
              archive_lag_target                   integer                          0
              db_flashback_retention_target        integer                          1440
              fast_start_io_target                 integer                          0
              fast_start_mttr_target               integer                          0
              memory_max_target                    big integer                      5G
              memory_target                        big integer                      5G
              pga_aggregate_target                 big integer                      0
              sga_target                           big integer                      0
              SQL> sho parameter sga_max
              
              
              NAME                                 TYPE                             VALUE
              ------------------------------------ -------------------------------- -------------------
              sga_max_size                         big integer                      5G
              
              
              
              
              
              
              
              
              
              • 6. Re: why oracle.exe is taking 8G virtual; bytes,  memroy target can not go beyond 5G. How to minimized the virtual byte in perfmon
                JohnWatson

                And the other question - why do you think the instance is taking 8G? Are you (mis)intepreting something Windows is telling you?