首页 > 代码库 > orcle 11g select count(*) from v$lock 引起的思索
orcle 11g select count(*) from v$lock 引起的思索
<strong>最近发现orcle 11g select count(*) from v$lock 查询很慢，觉得有必要进行详细的分析：：</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 | |<span style="color:#ff0000;"> 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 |</span> | 7 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 | | 8 | UNION-ALL | | | | | | |* 9 | <span style="color:#3333ff;">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 |</span>
生产库10046：
********************************************************************************
SQL ID: ct78468spkzrt Plan Hash: 2384831130
select count(*)
from
v$lock
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 13.09 21.12 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 13.09 21.13 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
select count(*) from X$KSQRS
Rows (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(*)
----------
706
Elapsed: 00:00:00.07
Execution 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 /
begin
dbms_stats.gather_fixed_objects_stats(stattab => 'X$KSQRS');
end;
ORA-02030: 只能从固定的表/视图查询
ORA-06512: 在 "SYS.DBMS_STATS", line 20508
ORA-06512: 在 "SYS.DBMS_STATS", line 20945
ORA-06512: 在 "SYS.DBMS_STATS", line 21498
ORA-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性能,来在执行计划中对每一个步骤获得更精确的成本值.系统统计信息缺省情况下是启用的,它使用缺省值自动初始化,这些值对于大多数系统来说是有代表性的.
orcle 11g select count(*) from v$lock 引起的思索
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。