首页 » ORACLE 9i-23ai » Tuning PGA Memory

Tuning PGA Memory

You can get the correct size of PGA using V$PGA_TARGET_ADVICE, dynamic performance view.

SQL> SELECT ROUND(pga_target_for_estimate/1024/1024) target_mb,
  2  estd_pga_cache_hit_percentage cache_hit_perc,estd_overalloc_count
  3   FROM V$PGA_TARGET_ADVICE;

 TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
       250             66                13703
       500             87                   86
      1000             95                    4
      1500             95                    0
      2000             98                    0
      2400             99                    0
      2800             99                    0
      3200             99                    0
      3600             99                    0
      4000             99                    0
      6000             99                    0
      8000             99                    0
     12000             99                    0
     16000             99                    0

Checking PGA for each sessions

You can check session level PGA using V$SESSTAT and V$SESSION view and also you can check the username, who is using that memory.

sys@OEM12C>SELECT round(s.value/1024/1024 ,2) MB ,s.sid,a.username
  2  FROM V$SESSTAT S, V$STATNAME N, V$SESSION A
  3  WHERE n.STATISTIC# = s.STATISTIC# and
  4  name = 'session pga memory'
  5  AND s.sid=a.sid
  6  ORDER BY s.value;

                 MB                  SID USERNAME
-------------------- -------------------- ------------------------------
                 .41                 1104
                 .41                 1103
                 .41                 1105
                 .47                 1089
                 .47                 1095
                 .53                 1074 ICME
                 .53                 1078 ICME
                 .53                 1063 ICME
                 .53                 1087
                 .66                 1098
                 .78                 1064 ICME
                 .78                 1084 ICME
                  .8                 1100
                 .89                 1099
                 .97                 1069 SYS
                1.03                 1097
                 1.1                 1088
                1.47                 1061 ZABBIX
                1.78                 1096
                2.39                 1067 GGSMGR
                4.65                 1102
               11.21                 1101

To check the total PGA in use and hit ratio for PGA

sys@GGS>SELECT * FROM V$PGASTAT;

NAME                                                                            VALUE UNIT
---------------------------------------------------------------- -------------------- ------------
aggregate PGA target parameter                                             3879731200 bytes
aggregate PGA auto target                                                  3467400192 bytes
global memory bound                                                         387973120 bytes
total PGA inuse                                                              27149312 bytes
total PGA allocated                                                          57661440 bytes
maximum PGA allocated                                                      4563434496 bytes
total freeable PGA memory                                                    25755648 bytes
process count                                                                      25
max processes count                                                                63
PGA memory freed back to OS                                            15763969212416 bytes
total PGA used for auto workareas                                                   0 bytes
maximum PGA used for auto workareas                                        2923335680 bytes
total PGA used for manual workareas                                                 0 bytes
maximum PGA used for manual workareas                                         1899520 bytes
over allocation count                                                               0
bytes processed                                                        36117468017664 bytes
extra bytes read/written                                                3105058237440 bytes
cache hit percentage                                                            92.08 percent
recompute count (total)                                                       4175071

The ideal way to perform sorts is by doing the entire job in memory. A sort job that Oracle performs entirely in memory is said to be an optimal sort. If you set the PGA_AGGREGATE_TARGET too low, some of the sort data is written out directly to disk (temporary tablespace) because the sorts are too large to fit in memory. If only part of a sort job spills over to disk, it’s called a 1-pass sort. If the instance performs most of the sort on disk instead of in memory, the response time will be high. This is called multi pass sort.

Another method of checking the efficiency of PGA memory is to check V$SQL_WORKAREA_HISTOGRAM.

V$SQL_WORKAREA_HISTOGRAM displays the cumulative work area execution statistics (cumulated since instance startup) for different work area groups. The work areas are split into 33 groups based on their optimal memory requirements with the requirements increasing in powers of two. That is, work areas whose optimal requirement varies from 0 KB to 1 KB, 1 KB to 2 KB, 2 KB to 4 KB, … and 2 TB to 4 TB.

For each work area group, the V$SQL_WORKAREA_HISTOGRAM view shows how many work areas in that group were able to run in optimal mode, how many were able to run in one-pass mode, and finally how many ran in multi-pass mode. The DBA can take a snapshot at the beginning and the end of a desired time interval to derive the same statistics for that interval.

sys@GGS>SELECT
  2  low_optimal_size/1024 "Low (K)",
  3  (high_optimal_size + 1)/1024 "High (K)",
  4  optimal_executions "Optimal",
  5  onepass_executions "1-Pass",
  6  multipasses_executions ">1 Pass"
  7  FROM v$sql_workarea_histogram
  8  WHERE total_executions <> 0;

             Low (K)             High (K)              Optimal               1-Pass              >1 Pass
-------------------- -------------------- -------------------- -------------------- --------------------
                   2                    4             22167255                    0                    0
                  64                  128                31068                    0                    0
                 128                  256                13624                    0                    0
                 256                  512                52058                    0                    0
                 512                 1024              1821196                    0                    0
                1024                 2048               528162                    0                    0
                2048                 4096               285108                    6                    0
                4096                 8192               366635                  300                    0
                8192                16384               555839                   14                    0
               16384                32768                13526                  205                    0
               32768                65536               236743                  276                    0
               65536               131072                 5570                   52                    0
              131072               262144                24924                12789                    0
              262144               524288                  105                  608                    0
              524288              1048576                    0                  290                    0
             1048576              2097152                    0                   59                    0
             2097152              4194304                    0                    8                    0

You can check the proportion of work areas since you started the Oracle instance, using optimal, 1-pass, and multipass PGA memory sizes.

sys@GGS>SELECT name PROFILE, cnt COUNT,
  2  DECODE(total, 0, 0, ROUND(cnt*100/total)) PERCENTAGE
  3  FROM (SELECT name, value cnt, (sum(value) over ()) total
  4  FROM V$SYSSTAT
  5  WHERE name like 'workarea exec%');

PROFILE                                                                         COUNT           PERCENTAGE
---------------------------------------------------------------- -------------------- --------------------
workarea executions - optimal                                                26154868                  100
workarea executions - onepass                                                   14607                    0
workarea executions - multipass                                                     0                    0

Since almost all the sorting and temporary operation are carried out inder optimal catagory we can conclude that out PGA is sized correctly.

references http://avdeo.com/2007/06/17/tuning-pga-memory-oracle-database-10g/

打赏

对不起,这篇文章暂时关闭评论。