• [bbk2192] 第33集 Chapter 09Optimizing Sore Perations(02)


    cache hit percentage这个参数,可以作为DBA衡量PGA相关参数指标是否合理的一个重要标准.

    SQL> SELECT low_optimal_size "Low",High_optimal_size "Hight",optimal_executions "Opt",onepass_executions "One",multipasses_executions "Multi",total_executions "Total"
      2  FROM v$sql_workarea_histogram;
    
           Low      Hight        Opt        One      Multi      Total
    ---------- ---------- ---------- ---------- ---------- ----------
             0       1023          0          0          0          0
          1024       2047          0          0          0          0
          2048       4095     170714          0          0     170714
          4096       8191          0          0          0          0
          8192      16383          0          0          0          0
         16384      32767          0          0          0          0
         32768      65535          0          0          0          0
         65536     131071        638          0          0        638
        131072     262143        392          0          0        392
        262144     524287        368          0          0        368
        524288    1048575       2924          0          0       2924
    
           Low      Hight        Opt        One      Multi      Total
    ---------- ---------- ---------- ---------- ---------- ----------
       1048576    2097151       1105          0          0       1105
       2097152    4194303        225          0          0        225
       4194304    8388607        203          0          0        203
       8388608   16777215          2          4          0          6
      16777216   33554431         12          0          0         12
      33554432   67108863          2          0          0          2
      67108864  134217727          0          0          0          0
     134217728  268435455          0          0          0          0
     268435456  536870911          0          0          0          0
     536870912 1073741823          0          0          0          0
    1073741824 2147483647          0          0          0          0
    
           Low      Hight        Opt        One      Multi      Total
    ---------- ---------- ---------- ---------- ---------- ----------
    2147483648 4294967295          0          0          0          0
    4294967296 8589934591          0          0          0          0
    8589934592 1.7180E+10          0          0          0          0
    1.7180E+10 3.4360E+10          0          0          0          0
    3.4360E+10 6.8719E+10          0          0          0          0
    6.8719E+10 1.3744E+11          0          0          0          0
    1.3744E+11 2.7488E+11          0          0          0          0
    2.7488E+11 5.4976E+11          0          0          0          0
    5.4976E+11 1.0995E+12          0          0          0          0
    1.0995E+12 2.1990E+12          0          0          0          0
    2.1990E+12 4.3980E+12          0          0          0          0
    
    33 rows selected.

    PGA Cache Hit Percentages

     

    SQL> SELECT low_optimal_size/1024 AS low_kb
      2          ,(high_optimal_size+1)/1024 AS high_kb
      3          ,ROUND(100*optimal_executions/total_executions) AS optimal
      4          ,ROUND(100*onepass_executions/total_executions) AS onepass
      5          ,ROUND(100*multipasses_executions/total_executions) AS multipass
      6  FROM v$sql_workarea_histogram
      7  WHERE total_executions !=0
      8  ORDER BY low_kb;
    
        LOW_KB    HIGH_KB    OPTIMAL    ONEPASS  MULTIPASS
    ---------- ---------- ---------- ---------- ----------
             2          4        100          0          0
            64        128        100          0          0
           128        256        100          0          0
           256        512        100          0          0
           512       1024        100          0          0
          1024       2048        100          0          0
          2048       4096        100          0          0
          4096       8192        100          0          0
          8192      16384         33         67          0
         16384      32768        100          0          0
         32768      65536        100          0          0
    
    11 rows selected.

    Determine PGA Workload

    SQL> SELECT optimal_count,round(optimal_count*100/total,2) optimal_perc,
      2          onepass_count,round(onepass_count*100/total,2) onepass_perc,
      3          multipass_count,round(multipass_count*100/total,2) multipass_perc
      4  FROM
      5          (SELECT decode(sum(total_executions),0,1,sum(total_executions)) total,
      6                  sum(OPTIMAL_EXECUTIONS) optimal_count,
      7                  sum(ONEPASS_EXECUTIONS) onepass_count,
      8                  sum(MULTIPASSES_EXECUTIONS) multipass_count
      9           FROM v$sql_workarea_histogram
     10           WHERE low_optimal_size > 64*1024
     11          );
    
    OPTIMAL_COUNT OPTIMAL_PERC ONEPASS_COUNT ONEPASS_PERC MULTIPASS_COUNT MULTIPASS_PERC
    ------------- ------------ ------------- ------------ --------------- --------------
             5233        99.92             4          .08               0              0

    解读: 

    OPTIMAL_COUNT OPTIMAL_PERC--->这对数据值较大,
    ONEPASS_COUNT ONEPASS_PERC--->这对数据值较小或者接近于0,
    MULTIPASS_COUNT MULTIPASS_PERC--->这对值很小或者接近于0,

    综合上述三个统计指标,说明比较合理的状态.否则应当考虑调整PGA相关参数信息.

    SQL> SELECT to_number(decode(sid,65535,NULL,sid)) sid,
      2          operation_type Oper,
      3          trunc(expected_size/1024) esize,
      4          trunc(actual_mem_used/1024) mem,
      5          trunc(max_mem_used/1024) "Max Mem",
      6          number_passes pass
      7  FROM v$sql_workarea_active
      8  ORDER BY 1,2;
    
           SID OPER                                                                                  ESIZE        MEM    Max Mem       PASS
    ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
            73 SORT (v2)                                                                              7228       7228       7228          0
    上述动态性能视图,还需要在商榷. 
    SQL> SELECT to_number(decode(sid,65535,NULL,sid)) sid,
      2          operation_type Oper,
      3          trunc(expected_size/1024) esize,
      4          trunc(actual_mem_used/1024) mem,
      5          trunc(max_mem_used/1024) "Max Mem",
      6          number_passes pass,
      7          trunc(TEMPSEG_SIZE/1024) TSIZE
      8  FROM v$sql_workarea_active
      9  ORDER BY 1,2;
    
    no rows selected
    
    SQL> /
    
           SID OPER                                                                                  ESIZE        MEM    Max Mem       PASS      TSIZE
    ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
            73 SORT (v2)                                                                              7228       7228       7228          0

    Monitoring SQL Memory 

    --历史的执行记录
    SELECT
    sql_text, sum(onepass_executions) onepass_cnt, sum(multipasses_executions) mpass_cnbt FROM V$SQL s,V$SQL_WORKAREA wa WHERE s.address = wa.address GROUP BY sql_text HAVING sum(onepass_executions+multipasses_executions) > 0;
    --当前正在执行的SQL,监控
    SELECT TO_NUMBER(DECODE(sid,65535,NULL,sid)) sid, operation_type OPERATION, TRUNC(expected_size/1024) ESIZE, TRUNC(actual_mem_used/1024) MEM, TRUNC(max_mem_used/1024) MAXMEM, number_passes PASS, TRUNC(tempseg_size/1024) TSIZE FROM V$SQL_WORKAREA_ACTIVE ORDER BY 1,2;
    usefull sqlcode for monitoring sql memory
    col "0/1/m" format a10
    col name format a20
    SELECT operation,options,object_name name,
            trunc(bytes/1024/1024) "input(MB)",
            trunc(last_memory_used/1024) last_mem,
            trunc(estimated_optimal_size/1024) optimal_mem,
            trunc(estimated_onepass_size/1024) onepass_mem,
            decode(optimal_executions,null,null,optimal_executions||'/'||onepass_executions||'/'||multipasses_executions) "0/1/M"
    FROM V$SQL_PLAN p,V$SQL_WORKAREA w
    WHERE p.address=w.address(+)
            AND p.hash_value=w.hash_value(+)
            AND p.id=w.operation_id(+);
  • 相关阅读:
    Ubuntu 安装Nginx的APT源
    Slots的数值设计与人为干涉
    棋牌游戏的经济系统分析
    kubernetes学习笔记之阿里云游戏业务实战
    Certbot-免费的https证书
    let’s Encrypt 证书之安装故障 Could not bind to IPv4 or IPv6.
    ssh:Permissions 0644 for ‘/root/.ssh/id_rsa’ are too open
    详解nginx websocket配置
    nginx upstream的五种分配方式
    nrm安装与配置
  • 原文地址:https://www.cnblogs.com/arcer/p/3063267.html
Copyright © 2020-2023  润新知