• Oracle


     数据库内部对象X$统计信息过旧,导致v$lock查询慢

    前段时间用python写了个zabbix监控脚本,里面有一个检查锁的sql语句,sql语句是这样子的
    select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600;
    但是zabbix界面显示这条语句超时,zabbix超时时间默认是3s,我将其改为15s,竟然还是超时,看样子要仔细研究这个sql语句了。
    这一看不得了,这条语句执行用了18s,统计v$lock的行数竟然要7min之久,这明显无法接受。

    SQL> select count(*) retvalue from v$lock where type in('TM', 'TX') and ctime > 600;
    
      RETVALUE
    ----------
             0
    
    Elapsed: 00:00:18.82

    查看其执行计划

    SQL> select * from table(dbms_xplan.display_cursor());
    
    ---------------------------------------------------------------------------
    | Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)|
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |            |       |       |     1 (100)|
    |   1 |  SORT AGGREGATE         |            |     1 |    53 |            |
    |*  2 |   HASH JOIN             |            |     1 |    53 |     0   (0)|
    |   3 |    MERGE JOIN CARTESIAN |            |     1 |    41 |     0   (0)|
    |*  4 |     FIXED TABLE FULL    | X$KSUSE    |     1 |    19 |     0   (0)|    
    |   5 |     BUFFER SORT         |            |     1 |    22 |     0   (0)|
    |*  6 |      FIXED TABLE FULL   | X$KSQRS    |     1 |    22 |     0   (0)|    
    |   7 |    VIEW                 | GV$_LOCK   |    10 |   120 |     0   (0)|
    |   8 |     UNION-ALL           |            |       |       |            |
    |*  9 |      FILTER             |            |       |       |            |
    |  10 |       VIEW              | GV$_LOCK1  |     2 |    24 |     0   (0)|
    |  11 |        UNION-ALL        |            |       |       |            |
    |* 12 |         FIXED TABLE FULL| X$KDNSSF   |     1 |    77 |     0   (0)|    
    |* 13 |         FIXED TABLE FULL| X$KSQEQ    |     1 |    77 |     0   (0)|    
    |* 14 |      FIXED TABLE FULL   | X$KTADM    |     1 |    77 |     0   (0)|    
    |* 15 |      FIXED TABLE FULL   | X$KTATRFIL |     1 |    77 |     0   (0)|    
    |* 16 |      FIXED TABLE FULL   | X$KTATRFSL |     1 |    77 |     0   (0)|    
    |* 17 |      FIXED TABLE FULL   | X$KTATL    |     1 |    77 |     0   (0)|    
    |* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |     1 |    77 |     0   (0)|    
    |* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |     1 |    77 |     0   (0)|    
    |* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |     1 |    77 |     0   (0)|    
    |* 21 |      FIXED TABLE FULL   | X$KTCXB    |     1 |    77 |     0   (0)|    
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("SADDR"="S"."ADDR" AND
                  TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||
                  RAWTOHEX("R"."ADDR"))
       4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
       6 - filter(("R"."KSQRSIDT"='TM' OR "R"."KSQRSIDT"='TX'))
       9 - filter(USERENV('INSTANCE') IS NOT NULL)

    统计v$lock的行数

    SQL> select count(*) from v$lock;
    
      COUNT(*)
    ----------
           600
    
    Elapsed: 00:07:46.84  

    这条语句的执行计划与上面的一样,这里我就不贴出来了

    v$lock只有600行,怎么会执行时间这么久,通过v$session能看到这条语句的等待事件为"direct path read temp"
    该等待事件表示服务器进程直接读取临时表空间的数据,通常由临时表太大造成。从上面的执行计划中可以看出临时表很大的原因可能是"MERGE JOIN CARTESIAN"。
    "MERGE JOIN CARTESIAN"表示笛卡尔联接,如果两表的行数都不小的话,这的确会造成临时表过大。查看X$KSUSE,X$KSQRS的行数

    SQL> select count(*) from X$KSUSE;                                                  
    
      COUNT(*)
    ----------
          4544
    
    SQL> select count(*) from X$KSQRS;
    
      COUNT(*)
    ----------
         20224

     这几千和几万来个笛卡尔积就是几千万的临时数据了,而我的pga只有4g,pga不够所以就用到了临时表空间进行表关联,也就造成了等待事件,所以说这条语句慢的主因就是这个笛卡儿积。

    这条语句之前执行都好好的,为什么现在慢了呢,最可能的情况是统计信息过旧,因为自动统计信息收集job不会收集固定对象也就是X$表的统计信息。

    收集下固定对象的统计信息

    SQL> begin
         dbms_stats.gather_fixed_objects_stats;
         end;
         /
    
    PL/SQL procedure successfully completed.  

    再执行以下语句,可以看到执行时间0.1s都不到,而且执行计划也恢复正常,赶紧在我这边的生产库把类似问题进行处理,嘿嘿。

    SQL> select count(*) from v$lock;
    
      COUNT(*)
    ----------
           600
    
    Elapsed: 00:00:00.08
    
    SQL> select * from table(dbms_xplan.display_cursor());
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |            |       |       |    29 (100)|          |
    |   1 |  SORT AGGREGATE          |            |     1 |    36 |            |          |
    |   2 |   HASH JOIN              |            |  3034 |   106K|    29 (100)| 00:00:01 |
    |   3 |    HASH JOIN             |            |    15 |   360 |    23 (100)| 00:00:01 |
    |   4 |     VIEW                 | GV$_LOCK   |    15 |   180 |    22 (100)| 00:00:01 |
    |   5 |      UNION-ALL           |            |       |       |            |          |
    |   6 |       FILTER             |            |       |       |            |          |
    |   7 |        VIEW              | GV$_LOCK1  |     7 |    84 |    15 (100)| 00:00:01 |
    |   8 |         UNION-ALL        |            |       |       |            |          |
    |   9 |          FIXED TABLE FULL| X$KDNSSF   |     1 |    16 |     1 (100)| 00:00:01 |
    |  10 |          FIXED TABLE FULL| X$KSQEQ    |     6 |   102 |    14 (100)| 00:00:01 |
    |  11 |       FIXED TABLE FULL   | X$KTADM    |     1 |    20 |     5 (100)| 00:00:01 |
    |  12 |       FIXED TABLE FULL   | X$KTATRFIL |     1 |    14 |     0   (0)|          |
    |  13 |       FIXED TABLE FULL   | X$KTATRFSL |     1 |    14 |     0   (0)|          |
    |  14 |       FIXED TABLE FULL   | X$KTATL    |     1 |    20 |     0   (0)|          |
    |  15 |       FIXED TABLE FULL   | X$KTSTUSC  |     1 |    14 |     0   (0)|          |
    |  16 |       FIXED TABLE FULL   | X$KTSTUSS  |     1 |    16 |     0   (0)|          |
    |  17 |       FIXED TABLE FULL   | X$KTSTUSG  |     1 |    14 |     0   (0)|          |
    |  18 |       FIXED TABLE FULL   | X$KTCXB    |     1 |    18 |     1 (100)| 00:00:01 |
    |  19 |     FIXED TABLE FULL     | X$KSUSE    |  4544 | 54528 |     1 (100)| 00:00:01 |
    |  20 |    FIXED TABLE FULL      | X$KSQRS    | 20224 |   237K|     5 (100)| 00:00:01 |
    ---------------------------------------------------------------------------------------

    总结:
    1.一些动态性能视图v$查询很慢的话,可能是由于动态性能视图所查询的内部对象表x$统计信息过旧,cbo选择了错误的执行计划造成。
    2.自动统计信息收集job不会收集内部对象表的统计信息,所以需要dba定时手工收集,或者是自己创建个job定期执行。

  • 相关阅读:
    3.2 直线与方程
    3.1.2 两条直线平等与垂直的判定
    GNU Make
    linux 启动后台进程
    go 占位符
    raft 协议
    restTemplate 接收list数据
    JAVA通过实体类生成数据库查询语句(驼峰命名规则)
    flink使用命令开始、停止任务
    SPringBoot 配置类继承WebMvcConfigurationSupport和实现WebMvcConfigurer的使用
  • 原文地址:https://www.cnblogs.com/ddzj01/p/10812076.html
Copyright © 2020-2023  润新知