访问V$LOCK视图出现性能问题和temp报错

最新推荐文章于 2021-04-09 04:09:49 发布
最新推荐文章于 2021-04-09 04:09:49 发布
阅读量276 收藏
点赞数
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ycwyong/article/details/80228879
版权

开发人员反映查询v$lock特别慢；

手工执行报错

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 7 16:53:19 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> select count(*) from v$lock ;
select count(*) from v$lock
                     *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

 

 

 

查询temp使用情况发现

TABLESPACE_NAME           FREE(G)   TOTAL(G)    USED(%)    FREE(%)
------------------------------ ---------- ---------- ---------- ----------
TEMP                    0       1        100      0

SQL> SQL>  
TOTAL(GB)   FREE(GB) TEMP_FILE                USED(GB)  TOTAL(GB)   FREE(GB)    USED(GB)
---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
     1        1 /mnt/oradata/xnjc/temp01.dbf          0      1        1           0

temp全部被使用，temp表空间大小只要1G

SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15 
       SID USERNAME       TABLESPACE HASH_VALUE                   SEGTYPE     CONTENTS      BLOCKS
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
     8 SEC           TEMP       0/2866845384                   LOB_DATA  TEMPORARY      128

sec用户正在使用temp

temp表空间分配太小，先解决这个分配问题

SQL> alter tablespace temp add tempfile '/mnt/oradata/xnjc/temp02.dbf' size 128m autoextend on next 100m
   /

Tablespace altered.

 

下面验证v$lock访问慢问题

SQL> select count(*) from v$lock ;

  COUNT(*)
----------
       111

访问要30多秒时间

 

 

查看下执行计划
SQL> SELECT * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |       1 |      50 |       1 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE     |         |       1 |      50 |          |     |
|*  2 |   HASH JOIN        |         |       1 |      50 |       1 (100)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN |         |     100 |    3800 |       0   (0)| 00:00:01 |
|*  4 |     FIXED TABLE FULL    | X$KSUSE    |       1 |      19 |       0   (0)| 00:00:01 |
|   5 |     BUFFER SORT     |         |     100 |    1900 |       0   (0)| 00:00:01 |
|   6 |      FIXED TABLE FULL    | X$KSQRS    |     100 |    1900 |       0   (0)| 00:00:01 |
|   7 |    VIEW         | GV$_LOCK   |      10 |     120 |       0   (0)| 00:00:01 |
|   8 |     UNION-ALL        |         |         |         |          |     |
|*  9 |      FILTER        |         |         |         |          |     |
|  10 |       VIEW        | GV$_LOCK1  |       2 |      24 |       0   (0)| 00:00:01 |
|  11 |        UNION-ALL    |         |         |         |          |     |
|* 12 |     FIXED TABLE FULL| X$KDNSSF   |       1 |      64 |       0   (0)| 00:00:01 |

|* 13 |     FIXED TABLE FULL| X$KSQEQ    |       1 |      64 |       0   (0)| 00:00:01 |

|* 14 |      FIXED TABLE FULL    | X$KTADM    |       1 |      64 |       0   (0)| 00:00:01 |
|* 15 |      FIXED TABLE FULL    | X$KTATRFIL |       1 |      64 |       0   (0)| 00:00:01 |

|* 16 |      FIXED TABLE FULL    | X$KTATRFSL |       1 |      64 |       0   (0)| 00:00:01 |

|* 17 |      FIXED TABLE FULL    | X$KTATL    |       1 |      64 |       0   (0)| 00:00:01 |

|* 18 |      FIXED TABLE FULL    | X$KTSTUSC  |       1 |      64 |       0   (0)| 00:00:01 |

|* 19 |      FIXED TABLE FULL    | X$KTSTUSS  |       1 |      64 |       0   (0)| 00:00:01 |

|* 20 |      FIXED TABLE FULL    | X$KTSTUSG  |       1 |      64 |       0   (0)| 00:00:01 |

|* 21 |      FIXED TABLE FULL    | X$KTCXB    |       1 |      64 |       0   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("R
          ADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR"))
   4 - filter("S"."INST_ID"=USERENV('INSTANCE'))
   9 - filter(USERENV('INSTANCE') IS NOT NULL)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  12 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  13 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  14 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  15 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  16 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  17 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  18 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  20 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)
  21 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND
          "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0)

56 rows selected.

id=3走了笛卡尔执行计划 估算出问题，X$KSUSE 不会只有一条数据，实际查询

SQL> select count(*) from  X$KSUSE ;

  COUNT(*)
----------
      2272

SQL> exec dbms_stats.gather_fixed_objects_stats  ; 收集统计信息

重新执行

select count(*) from v$lock; 秒出问题解决

 

 

查看调整后的执行计划


Execution Plan
----------------------------------------------------------
Plan hash value: 2965011029

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

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Tim
e     |

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

|   0 | SELECT STATEMENT     |          |     1 |    36 |     9 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE      |          |     1 |    36 |        |      |
|   2 |   HASH JOIN         |          | 13905 |   488K|     9 (100)| 00:00:01 |
|   3 |    HASH JOIN         |          |   136 |  3264 |     7 (100)| 00:00:01 |
|   4 |     VIEW         | GV$_LOCK   |   136 |  1632 |     6 (100)| 00:00:01 |
|   5 |      UNION-ALL         |          |       |       |        |      |
|   6 |       FILTER         |          |       |       |        |      |
|   7 |        VIEW         | GV$_LOCK1  |   128 |  1536 |     4 (100)| 00:00:01 |
|   8 |     UNION-ALL     |          |       |       |        |      |
|   9 |      FIXED TABLE FULL| X$KDNSSF   |     1 |    16 |     0    (0)| 00:00:01 |
|  10 |      FIXED TABLE FULL| X$KSQEQ    |   127 |  2159 |     4 (100)| 00:00:01 |
|  11 |       FIXED TABLE FULL     | X$KTADM    |     1 |    16 |     1 (100)| 00:00:01 |
|  12 |       FIXED TABLE FULL     | X$KTATRFIL |     1 |    14 |     0    (0)| 00:00:01 |
|  13 |       FIXED TABLE FULL     | X$KTATRFSL |     1 |    14 |     0    (0)| 00:00:01 |
|  14 |       FIXED TABLE FULL     | X$KTATL    |     1 |    26 |     0    (0)| 00:00:01 |
|  15 |       FIXED TABLE FULL     | X$KTSTUSC  |     1 |    14 |     0    (0)| 00:00:01 |
|  16 |       FIXED TABLE FULL     | X$KTSTUSS  |     1 |    16 |     0    (0)| 00:00:01 |
|  17 |       FIXED TABLE FULL     | X$KTSTUSG  |     1 |    14 |     0    (0)| 00:00:01 |
|  18 |       FIXED TABLE FULL     | X$KTCXB    |     1 |    16 |     0    (0)| 00:00:01 |
|  19 |     FIXED TABLE FULL     | X$KSUSE    |  2272 | 27264 |     0    (0)| 00:00:01 |
|  20 |    FIXED TABLE FULL     | X$KSQRS    | 10224 |   119K|     1 (100)| 00:00:01 |

--------------------------------------------------------------------------------笛卡尔执行计划消除------

总结：

GATHER_FIXED_OBJECTS_STATS此过程收集统计所有的动态性能表

必须有SYSDBA或具有ANALYZE ANY DICTIONARY权限才能执行此过程

超人-归来
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
v$lock 视图访问慢解决方法
caobihole
09-11 180
V$ 视图访问慢 --解决方法 分析：可能是有数据字典统计信息过久，造成。 exec dbms_stats.gather_fixed_objects_stats; ------收集所有数据字典的fixed table 的统计信息，请勿在业务高峰期间执行。 如果是v$lock 视图访问慢，可以只收集他的统计信息 exec dbms_stats.gather_fixed_objec...
Oracle所有执行计划关键词 + 所有hint
最新发布
m0_47243617的博客
11-14 1227
执行计划里面的关键词大全和hint。
oracle 之数据字典屣履造门。
Rhys learn oracle！
06-20 1585
oracle 之数据字典屣履造门。       今天是2013-06-20，哎，写这篇笔记的时候，我发现我是一个非常懒惰的人，这篇文章本该昨天就完成的，想起了钱鹤滩的《明日歌》，真是“世人若被明日累，春去秋来老将至”。      oracle数据字典包括四部分，分别是RDBMS(X$),数据字典表、动态性能视图（v$）和数据字典视图。 1)RDBMS(X$)内部 表： 该部分内容是or
oracle中做数据字典,Oracle之数据字典屣履造门
weixin_35426913的博客
04-02 175
oracle之数据字典屣履造门oracle数据字典包括四部分，分别是RDBMS(X$),数据字典表、动态性能视图(v$)和数据字典视图。1)RDBMS(X$)内部 表：该部分内容是oracle较低层的表数据，这些表维持着oracle的整个视图，就像我们人体的血液在我们人体流动维持生命一样。因此这些表只能查看、研究不建议修改。oracle对修改内部表产生的后果不负责技术支持工作。在查看参数文件的时候...
ORACLE关于锁和V$LOCK的分析.docx
02-03
ORACLE 锁机制和 V$LOCK 视图分析 Oracle 数据库为了实现并发操作，使用了锁机制来控制对数据的访问。锁机制是 Oracle 数据库并发控制的核心机制之一。锁机制能够有效地防止多个用户同时访问同一个数据项，避免了...
MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error
09-09
【MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error】是一个常见的MySQL服务器启动时遇到的问题。这个问题通常表明MySQL的InnoDB存储引擎无法获取对`ibdata1`文件的锁，`ibdata1`是InnoDB用来存储数据和系统表...
数据字典和动态性能视图
10-14
数据字典和动态性能视图...数据字典提供静态信息，帮助理解和控制数据库结构，而动态性能视图则提供了运行时的性能数据，支持实时监控和性能优化。熟悉并熟练使用这些工具，对于任何Oracle DBA来说都是必不可少的技能。
Oracle10g DBA经常使用的动态性能视图和数据字典
04-06
v$lock：通过访问数据库会话，设置对象锁的所有信息 v$log：从控制文件中提取有关重做日志组的信息 v$logfile有关实例重置日志组文件名及其位置的信息 v$parameter：初始化参数文件中所有项的值 v$process：当前进程...
ORACLE锁----原理篇
gguxxing008的专栏
10-21 499
在现代的多用户多任务系统中，必然会出现多个用户同时访问共享的某个对象，这个对象可能是表，行，或者内存结构，为了解决多个用户并发性访问带来的数据的安全性，完整性及一致性问题，必须要有一种机制，来使对这些共享资源的并发性访问串行化，oracle中的锁就可以提供这样的功能，当事务在对某个对象进行操作前，先向系统发出请求，对其加相应的锁，加锁后该事务就对该数据对象有了一定的控制权限，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作（可以做select动作，但select 利用的是undo中的前镜像数据了）
【原创】v$lockv和$locked_object…
GAMELOFT9----纸上得来终觉浅，绝知此事要躬行
03-10 975
v$lockv和$locked_object的区别  2013-12-24 v1.0 一、引言     工作中有次修改表sj_affair中的数据，需要禁用该表上的触发器。结果无法禁用，报如下错误： ORA-00054: resource busy and acquire with NOWAIT specified 很明显，是该表被锁定了，于是打算kill掉锁住该表的会话。步骤如下： 1.查出锁住
Oracle 11g统计表空间使用率很慢
关注系统性能调优
05-10 2382
现场统计表空间很慢。 SELECT T1.TABLESPACE_NAME,        TOTAL_SPACE,        TOTAL_SPACE - FREE_SPACE USED_SPACE,        FREE_SPACE   FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 TOTAL_SPACE
Oracle 11g dba_free_space视图查询慢解决方案——hint调整执行计划
weixin_41429228的博客
02-28 1828
ora11g中查询表空间的使用情况时需要通过dba_free_space视图来查看表空间的剩余量，但有时可能由于回收站的对象较多等原因，导致该视图查询速度较慢，本文将从其执行计划的角度分析其原因并提出一种相对实用且无风险的解决方式。
深入浅出数据字典摘要
02-06 94
Eygle深入浅出的数据字典摘要：Oracle数据字典存储的是oracle内部的元数据，而数据字典一般是只读的，由4部分组成：内部rbdms（x$）表，数据字典表，动态性能(v$)视图和数据字典视图。X$表是oracle运行基础...
orcle 11g select count(*) from v$lock 引起的思索
"杰"出虚拟平台-数据运维~~
12-30 2036
orcle 11g select count(*) from v$lock 引起的思索
oracle 锁表监控脚本,查看oracle锁表相关脚本
weixin_42659194的博客
04-09 270
查看当前实例中锁的状态：--根据gv$lock查询锁的状况col user_name for a10col instance for a10col pid for a7col sid for 999999col serial# for 999999col hold_mode for a15col req_mode for a15set pagesize 20selecti.instance_nam...
oracle--v$lock 字段详解
qizhan0095
03-06 6960
V$LOCK视图结构 列名 类型 字段说明 ADDR RAW（4 | 8） Address of lock state object KADDR RAW（4|8） Address of lock SID NUMBER 会话的sid，可以和v$session 关联 TYPE
ORACLE锁的管理
付勤的专栏
03-30 982
ORACLE里锁有以下几种模式:0：none1：null 空2：Row-S 行共享(RS)：共享表锁，sub share3：Row-X 行独占(RX)：用于行的修改，sub exclusive4：Share 共享锁(S)：阻止其他DML操作，share5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive6：exclusive 独占(X)：独立访问使用，
Oracle的v$session视图在哪
06-10
`v$session`是Oracle数据库中的一个特殊视图，用于显示当前会话的信息。您可以使用以下SQL语句查询`v$session`视图： ``` SELECT * FROM v$session; ``` 或者，您可以使用以下SQL语句查询当前正在执行的SQL语句和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值