orcle 11g select count(*) from v$lock 引起的思索，orcle11g

orcle 11g select count(*) from v$lock 引起的思索，orcle11g

<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: 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.66

GV￥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效能,來在執行計畫中對每一個步驟獲得更精確的成本值.系統統計資訊預設情況下是啟用的,它使用預設值自動初始化,這些值對於大多數系統來說是有代表性的.