SQL Memory Usage

Find the cursors with one or more work areas that have been executed in one or multiple passes


        set pages 55 

        select s.sql_id, 

               sum(onepass_executions) onepass_cnt, 

               sum(multipasses_executions) mpass_cnt 

        from v$sql s, 

             v$sql_workarea wa 

       where s.address = wa.address 

      group by s.sql_id 

      having sum(onepass_executions + multipasses_executions) > 0 

     order by sum(onepass_executions) + sum(multipasses_executions) desc;

Active Workareas


     select sql_id, 

            to_number(decode(sid, 65535, null, sid)) sid, 

            operation_type                           operation, 

            trunc(expected_size/1024)                esize_mb, 

            trunc(actual_mem_used/1024)              mem_mb, 

            trunc(max_mem_used/1024)                 maxmem_mb, 

            number_passes                            pass, 

            nvl(trunc(tempseg_size/1024),0)          tsize_mb 

      from v$sql_workarea_active 

    order by tsize_mb desc;

SQL Statement Generating Sort


      select session_num, sql_id, username, segtype, blocks, tablespace 

      from v$tempseg_usage 

      order by blocks desc;

PGA Target Size Advisor


         col PGA_TARGET_FOR_ESTIMATE form 999,999,999,999,999 

         col BYTES_PROCESSED form     999,999,999,999,999 

         col ESTD_TIME form           999,999,999,999,999 

         col ESTD_EXTRA_BYTES_RW form 999,999,999,999,999 

         select * from v$pga_target_advice;

PGA Statistics


      select * from v$pgastat;


SQL> select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice; 



 SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME 

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

     3744            .375      3261359 

     4992              .5      2119057 

     6240            .625      2083947 

     7488             .75      2064759 

     8736            .875      2049857 

     9984               1      2041284 

    11232           1.125      2033324 

    12480            1.25      2029241 

    13728           1.375      2023934 

    14976             1.5      2020464 

    16224           1.625      2020260 

    17472            1.75      2020260 

    18720           1.875      2020261 

    19968               2      2020264

DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL.

Minimizing DB_TIME is the overall objective of all tuning.

Look at the 6th row i.e value 9984 MB , this is the value of parameter target being set.

So,It can be seen that if the SGA is raised from its current value of 9984MB to 11232MB then DB_TIME would reduce.

Pin Packages

declare own varchar2(100); nam varchar2(100); cursor pkgs is select owner, object_name from dba_objects where object_type = 'PACKAGE'; begin open pkgs; loop fetch pkgs into own, nam; exit when pkgs%notfound; dbms_shared_pool.unkeep(own || '.' || nam, 'P'); end loop; end; /
HOME