orcle 11g select count(*) from v$lock 引起的思索
来源：互联网 发布：fluke网络测试仪 编辑：程序博客网 时间：2024/05/21 17:31
<strong><span style="color:#ff6666;">最近发现orcle 11g select count(*) from v$lock 查询很慢，觉得有必要进行详细的分析</span></strong>
select count(*) from v$lock;--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 50 | 0 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 50 | | ||* 2 | HASH JOIN | | 1 | 50 | 0 (0)| 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 |
生产库10046：
********************************************************************************SQL ID: ct78468spkzrt Plan Hash: 2384831130select count(*) from v$lockcall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.01 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 13.09 21.12 0 0 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 13.09 21.13 0 0 0 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSNumber of plan statistics captured: 1select count(*) from X$KSQRSRows (1st) Rows (avg) Rows (max) Row Source Operation---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=21123213 us) 355 355 355 HASH JOIN (cr=0 pr=0 pw=0 time=21083516 us cost=1 size=50 card=1)<span style="color:#ff0000;"> 10715136 10715136 10715136 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=10072178 us cost=0 size=3800 card=100) 1536 1536 1536 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=6834 us cost=0 size=19 card=1) 10715136 10715136 10715136 BUFFER SORT (cr=0 pr=0 pw=0 time=3222054 us cost=0 size=1900 card=100)</span> 6976 6976 6976 FIXED TABLE FULL X$KSQRS (cr=0 pr=0 pw=0 time=1911 us cost=0 size=1900 card=100) 356 356 356 VIEW GV$_LOCK (cr=0 pr=0 pw=0 time=11637 us cost=0 size=120 card=10) 356 356 356 UNION-ALL (cr=0 pr=0 pw=0 time=11281 us) 352 352 352 FILTER (cr=0 pr=0 pw=0 time=10570 us) 352 352 352 VIEW GV$_LOCK1 (cr=0 pr=0 pw=0 time=10330 us cost=0 size=24 card=2) 352 352 352 UNION-ALL (cr=0 pr=0 pw=0 time=9978 us) 0 0 0 FIXED TABLE FULL X$KDNSSF (cr=0 pr=0 pw=0 time=594 us cost=0 size=64 card=1) 352 352 352 FIXED TABLE FULL X$KSQEQ (cr=0 pr=0 pw=0 time=8792 us cost=0 size=64 card=1) 4 4 4 FIXED TABLE FULL X$KTADM (cr=0 pr=0 pw=0 time=12004 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTATRFIL (cr=0 pr=0 pw=0 time=17 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTATRFSL (cr=0 pr=0 pw=0 time=6 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTATL (cr=0 pr=0 pw=0 time=9 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTSTUSC (cr=0 pr=0 pw=0 time=17 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTSTUSS (cr=0 pr=0 pw=0 time=11 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTSTUSG (cr=0 pr=0 pw=0 time=12 us cost=0 size=64 card=1) 0 0 0 FIXED TABLE FULL X$KTCXB (cr=0 pr=0 pw=0 time=6216 us cost=0 size=64 card=1)Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 asynch descriptor resize 61 0.00 0.00 SQL*Net message from client 2 30.66 30.66GV￥lock性能正常：
15:21:44 sys@gshx1(newgsdb01)> select count(*) from gv$lock; COUNT(*)---------- 706Elapsed: 00:00:00.07Execution Plan----------------------------------------------------------Plan hash value: 483924080-----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |-----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 00:00:01 | | | || 1 | SORT AGGREGATE | | 1 | | | | | | || 2 | PX COORDINATOR | | 10 | | 1 (100)| 00:00:01 | | | || 3 | PX SEND QC (RANDOM) | :TQ10000 | 10 | 370 | 1 (100)| 00:00:01 | Q1,00 | P->S | QC (RAND) || 4 | VIEW | GV$LOCK | | | | | Q1,00 | PCWP | ||* 5 | HASH JOIN | | 10 | 370 | 1 (100)| 00:00:01 | Q1,00 | PCWP | ||* 6 | HASH JOIN | | 10 | 180 | 1 (100)| 00:00:01 | Q1,00 | PCWP | || 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 | Q1,00 | PCWP | || 8 | UNION-ALL | | | | | | Q1,00 | PCWP | ||* 9 | FILTER | | | | | | Q1,00 | PCWP | || 10 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0)| 00:00:01 | Q1,00 | PCWP | || 11 | UNION-ALL | | | | | | Q1,00 | PCWP | ||* 12 | FIXED TABLE FULL| X$KDNSSF | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 13 | FIXED TABLE FULL| X$KSQEQ | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 14 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 15 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 16 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 17 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 18 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 19 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 20 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | ||* 21 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0)| 00:00:01 | Q1,00 | PCWP | || <span style="color:#ff6666;"> 22 | FIXED TABLE FULL | X$KSUSE | 100 | 600 | 0 (0)| 00:00:01 | Q1,00 | PCWP | || 23 | FIXED TABLE FULL | X$KSQRS | 100 | 1900 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |</span>
<strong>并且查询select * from v$lock是正常的</strong>对这两个表的信息应该是正确的，再看我测试环境：
--------------------------------------------------------------------------------Plan hash value: 2329815124--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 40 | 1 (100| 1 | SORT AGGREGATE | | 1 | 40 || 2 | NESTED LOOPS | | 1 | 40 | 1 (100|* 3 | HASH JOIN | | 1 | 31 | 1 (100|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 19 | 0 (0| 5 | VIEW | GV$_LOCK | 10 | 120 | 0 (0| 6 | UNION-ALL | | | ||* 7 | FILTER | | | || 8 | VIEW | GV$_LOCK1 | 2 | 24 | 0 (0| 9 | UNION-ALL | | | ||* 10 | FIXED TABLE FULL | X$KDNSSF | 1 | 64 | 0 (0|* 11 | FIXED TABLE FULL | X$KSQEQ | 1 | 64 | 0 (0|* 12 | FIXED TABLE FULL | X$KTADM | 1 | 64 | 0 (0|* 13 | FIXED TABLE FULL | X$KTATRFIL | 1 | 64 | 0 (0|* 14 | FIXED TABLE FULL | X$KTATRFSL | 1 | 64 | 0 (0|* 15 | FIXED TABLE FULL | X$KTATL | 1 | 64 | 0 (0|* 16 | FIXED TABLE FULL | X$KTSTUSC | 1 | 64 | 0 (0|* 17 | FIXED TABLE FULL | X$KTSTUSS | 1 | 64 | 0 (0|* 18 | FIXED TABLE FULL | X$KTSTUSG | 1 | 64 | 0 (0|* 19 | FIXED TABLE FULL | X$KTCXB | 1 | 64 | 0 (0|* 20 | FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) | 1 | 9 | 0 (0--------------------------------------------------------------------------------生产库和测试库的执行计划是不一样的，寻找生产库为啥有笛卡尔计：
根据经验处理办法有两种：
一：收集内存表所有的统计信息
execute dbms_stats.gather_fixed_objects_stats(）
二：添加提示
select /*+ rule */count(*) from v$lock
遗憾的是当时我只考虑统计信息没有考虑添加提示：
生产库经过验证，速度是很快的。
思考：
对内存表信息的收集
SQL> begin 2 dbms_stats.gather_fixed_objects_stats(stattab => 'X$KSQRS'); 3 end; 4 / begindbms_stats.gather_fixed_objects_stats(stattab => 'X$KSQRS');end; ORA-02030: 只能从固定的表/视图查询ORA-06512: 在 "SYS.DBMS_STATS", line 20508ORA-06512: 在 "SYS.DBMS_STATS", line 20945ORA-06512: 在 "SYS.DBMS_STATS", line 21498ORA-06512: 在 line 3 SQL> SQL> select table_name, num_rows, last_analyzed 2 from dba_tab_statistics 3 where last_analyzed is not null 4 and table_name = 'X$KSQRS' 5 / TABLE_NAME NUM_ROWS LAST_ANALYZED------------------------------ ---------- -------------X$KSQRS 1312 2014/12/30 22 SQL> SQL> begin 2 dbms_stats.delete_table_stats('SYS','X$KSQRS'); 3 end; 4 / PL/SQL procedure successfully completed SQL> SQL> select table_name, num_rows, last_analyzed 2 from dba_tab_statistics 3 where last_analyzed is not null 4 and table_name = 'X$KSQRS' 5 / TABLE_NAME NUM_ROWS LAST_ANALYZED------------------------------ ---------- ------------- SQL> SQL> begin 2 dbms_stats.gather_table_stats('SYS','X$KSQRS'); 3 end; 4 / PL/SQL procedure successfully completed SQL> SQL> select table_name, num_rows, last_analyzed 2 from dba_tab_statistics 3 where last_analyzed is not null 4 and table_name = 'X$KSQRS' 5 / TABLE_NAME NUM_ROWS LAST_ANALYZED------------------------------ ---------- -------------X$KSQRS 1312 2014/12/30 22 注：gather_dictionary_stats--> 针对table$这样的表，存在于物理数据库中~
gather_fixed_objects_stats--> 针对x$table这样的内存表，不存在物理数据库中，只在内存中存在，动态试图的基表
gather_system_stats-->针cpu/io
固定对象统计信息
自动统计信息收集job不会收集固定对象的统计统计信息.当优化统计信息丢失时不象其它的数据库表对于sql语句中调用X$表是不能自动使用动态抽样的.如果它们的统计信息丢失优化器会使用预先定义的缺省统计信息.这些缺省的统计信息可能没有代表性且可能导致选择次优的执行计划,在系统中可能会导致严重的性能问题.如果是这个原因造成性能问题强烈建议你手动收集固定对象的统计信息.可以使用dbms_stats.gather_fixed_objects_stats过程来收集固定对象的统计信息.因为在系统如果存在一个有代表性的工作负载收集x$这些固定对象的统计信息是很重要的.在大型系统中由于收集固定对象统计信息需要额外的资源所以对固定对象收集统计信息不总是可行.如果不能在负载高峰期间收集固定对象的统计信息那么应该在系统负载降低之后对三种关键类型的固定对象表收集统计信息:
structural data--比如controlfile contents
Session based data - 比如 v$session, v$access
Workload data -比如 v$sql, v$sql_plan
建议当主数据库或应用程序升级后,实现新的模块或者改变数据库的配置后重新收集固定对象统计信息.例如,如果增加SGA的大小包含缓冲区缓存和共享池信息的x$表会显著的发生改变,比如v$buffer_pool或v$shared_pool_advice视图使用的x$表.系统统计信息系统统计信息能让优化器通过使用执行这个语句相关的实际系统硬件信息,比如,cpu速度和IO性能,来在执行计划中对每一个步骤获得更精确的成本值.系统统计信息缺省情况下是启用的,它使用缺省值自动初始化,这些值对于大多数系统来说是有代表性的.
0 0
- orcle 11g select count(*) from v$lock 引起的思索
- select * from 与select count(*) from 的区别
- Select count(*) from与Select * from的区别
- 5G 商业模式的思索？
- g.hibernate.hql.ast.QuerySyntaxException: tbl_base_count is not mapped [SELECT COUNT(*) FROM tbl_bas
- Oracle 11g 数据恢复 数据误删除后的恢复 0、执行 select log_mode from v$database;查看是否为归档模式 1、确定删除时间和被删除的表 04-23，GR
- select count(column_name) form table_name or select count(*) from table_Name
- orcle 11g创建表空间
- GAP LOCK引起的死锁
- select count(id) from table 返回值
- 使用JDBC获取select count(*) from table_name
- select count的差异
- Select count(*) 的优化
- 11g v$active_session_history的列说明
- Count (*),Select Count(1),Select Count(column)的区别
- select count(*) from temp 与select count(1) from temp有什么区别
- SELECT count(ID) FROM LM_Photos WHERE (userid = @userid) 报错的原因
- [VB.NET]VB2005+ACCESS中,如何获得select count(1) from tb的返回值?
- 1005 继续(3n+1)猜想
- git发布代码到github过程和常见错误
- Java中使用Jedis操作Redis
- 我的2014，人生的又一个转折
- gcc 中-g对应的源代码和gdb有关的flags和变量 write_symbols
- orcle 11g select count(*) from v$lock 引起的思索
- 使用 Sphinx 更好地进行 MySQL 搜索
- 如何学习JAVA
- [ Ubuntu Chinese Method ] uninstall DBus, install fcitx
- 兼职分享
- 第一个Servlet
- Dubbo之旅--架构路线
- 回顾2014，展望2015
- 利用CSS和<div>手绘静态多级菜单
