索引监控慢的问题

SQL> select count(*)  from (select 1 from dba_indexes where partitioned = 'NO' and status <> 'VALID' union all select 1 from dba_ind_partitions where status = 'UNUSABLE');

  COUNT(*)
----------
         0

Elapsed: 00:00:00.02  很快

 

Execution Plan
----------------------------------------------------------
执行计划略

Note
-----
   - 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1903  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

--以上语句没有发现问题

 

 

--半个小时后发现问题
----------------------------------------------------------
       1751  recursive calls  --解析时间
          0  db block gets
       2360  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         66  sorts (memory)
          0  sorts (disk)
          1  rows processe

********************************************************************************

 

--节点2
select count(*) 
from
 (select 1 from dba_indexes where partitioned = 'NO' and status <> 'VALID'
  union all select 1 from dba_ind_partitions where status = 'UNUSABLE')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.28       0.28          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.03          0       1903          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.31       0.31          0       1903          0           1

 

 

--节点一
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.48       0.48          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.05       0.04          0       3806          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.53       0.53          0       3806          0   

  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              6        0.00          0.00
  row cache lock                                 20        0.00          0.00
  library cache pin                               2        0.00          0.00
  rdbms ipc reply                                 2        0.00          0.00
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     4        8.17         11.11
 
--执行时间长时由于发生了解析！ 

语句发现，没有没有该语句游标（很少）
select sql_id from dba_hist_sqltext where  sql_text like '%select count(*)  from (select 1 from dba_indexes where partitioned = ''NO''%';
select to_char(sample_time, 'yyyymmdd hh24miss')
  from dba_hist_active_sess_history a

 where sql_id in ('277uysvsc9dsf', '9vyz1jb3p7spg')
 and sample_time > to_date('20140711','yyyymmdd')
20140711 102045
20140711 105508

 

3 session_cached_cursors 200
解析偏多，该参数可能偏小！
SQL> Select 'session_cached_cursors' Parameter,
  2      Lpad(Value, 5) Value,
  3      Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
  4  From (Select Max(s.Value) Used
  5        From V$statname n, V$sesstat s

  6       Where n.Name = 'session cursor cache count'
  7        And s.Statistic# = n.Statistic#),
  8      (Select Value From V$parameter Where Name = 'session_cached_cursors')
  9  Union All
 10  Select 'open_cursors',
 11      Lpad(Value, 5),
 12      To_Char(100 * Used / Value, '990') || '%'

 13  From (Select Max(Sum(s.Value)) Used
 14        From V$statname n, V$sesstat s
 15       Where n.Name In
 16          ('opened cursors current', 'session cursor cache count')
 17        And s.Statistic# = n.Statistic#
 18       Group By s.Sid),
 19      (Select Value From V$parameter Where Name = 'open_cursors');

PARAMETER              VALUE      USAGE
---------------------- ---------- -----
session_cached_cursors   200       100%     --需要加大
open_cursors            3000        13%     --适当减少