shared pool latch相关描述

The shared pool latch is used to protect critical operations when allocating
and freeing memory in the shared pool.
If an application makes use of literal (unshared) SQL then this can severely
limit scalability and throughput. The cost of parsing a new SQL statement is
expensive both in terms of CPU requirements and the number of times the library
cache and shared pool latches may need to be acquired and released. Before Oracle9,
there was just one such latch for the entire database to protect the allocation of
memory in the library cache. In Oracle9, multiple children were introduced to relieve
contention on this resource.

减少shared pool latch方法

Avoid hard parses when possible, parse once, execute many.
Eliminate  literal SQL so that same sql is shared by many sessions.
Size the shared_pool adequately to avoid reloads
Use of MTS (shared server option) also greatly influences the shared pool latch.

查询未绑定sql

--9i
SELECT substr(sql_text,1,40) "SQL",
         count(*) ,
         sum(executions) "TotExecs"
    FROM v$sqlarea
   WHERE executions < 5
   GROUP BY substr(sql_text,1,40)
  HAVING count(*) > 30
   ORDER BY 2
  ;
--10g及其以后版本
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
     (SELECT  FORCE_MATCHING_SIGNATURE,
              COUNT(*) cnt
     FROM     v$sqlarea
     WHERE    FORCE_MATCHING_SIGNATURE!=0
     GROUP BY FORCE_MATCHING_SIGNATURE
     HAVING   COUNT(*) > 20
     )
     ,
     sq AS
     (SELECT  sql_text                ,
              FORCE_MATCHING_SIGNATURE,
              row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
     FROM     v$sqlarea s
     WHERE    FORCE_MATCHING_SIGNATURE IN
              (SELECT FORCE_MATCHING_SIGNATURE
              FROM    c
              )
     )
SELECT   sq.sql_text                ,
         sq.FORCE_MATCHING_SIGNATURE,
         c.cnt "unshared count"
FROM     c,
         sq
WHERE    sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND      sq.p                       =1
ORDER BY c.cnt DESC

查询数据库整体解析情况

select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from ( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' );

参考:Note 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch

cache buffer lru chain latch官方解释

The cache buffer lru chain latch is acquired in order to introduce a new block into the buffer cache
and when writing a buffer back to disk, specifically when trying to scan the LRU (least recently used) chain
containing all the dirty blocks in the buffer cache.

cache buffer lru chain latch可能原因

想查看或者修改LRU+LRUW的进程，始终要持有cache buffers lru chain latch。
若在此过程中发生争用，则要等待latch:cache buffers lru chain 事件。
总结出来如下两种情况会导致cache buffers lru chain latch:
1.进程欲读取还没有装载到内存上的块时，通过查询LRU 列分配到所需空闲缓冲区，在此过程中需要cache buffers lru chain latch。
2.DBWR 为了将脏缓冲区记录到文件上，查询LRUW 列，将相应缓冲区移动到LRU 列的过程中也要获得cache buffers lru chain latch。
2.1)DBWR在如下情况下将脏缓冲区记录到文件里。
2.2)Oracle 进程为了获得空闲缓冲区，向DBWR 请求记录脏缓冲区时；
2.3)Oracle进程为执行Parallel Query 或Tablespace Backup，Truncate/Drop 等工作，请求记录相关对象的脏缓冲区时；
2.4)周期性或管理上的原因检查点（checkpointing）被执行时。
2.5)Oracle 为了保障将通过FAST_START_MTTR_TARGET（或LOG_CHECKPOINT_TIMEOUT）指定的时间的恢复，周期性执行检查点。
2.6)管理员执行检查点命令或根据日志文件切换，也会发生检查点。

cache buffers lru chain latch争用的最重要的原因是过多请求空闲缓冲区。低效的SQL语句是过多请求空闲缓冲区的最典型情况，若多个会话同时执行低效的SQL语句，则在查询空闲缓冲区过程中和记录脏缓冲区的过程中，为了获取buffers lru chain latch发生争用。多个会话同时扫描不同表或索引时，发生cache buffers lru chain latch争用的概率高。多个会话将各不相同的块载入到内存过程中，确保空闲缓冲区的请求会增多，因此发生对工作组争用的概率将提高。特别是因为数据修改频繁，以至于脏缓冲区数量多，正因此DBWR 因为检查点而查询LRUW 列的次数频繁，所以cache buffers lru chain latch争用将更加严重。cache buffers lru chain latch争用的另一个重要特点就是伴随着物理I/O。若是低效的索引扫描引起的问题，则同时发生db file sequential read 等待和lru chain latch争用；若是不必要的全表扫描引起的问题，则同时发生db file scattered read 等待和lru chain latch争用。事实上，cache buffers chains latch争用和cache buffers lru chain latch争用同时发生的情况较多，因为复杂的应用程序将复合地应用上述模式。data buffer过小或检查点周期过短时，也会增加cache buffers lru chain latch争用；但是现在的数据库的data buffer都不会太小,而检查点周期一般使用缺省值，所以通常定位cache buffers lru chain latch的原因还是在低效的SQL语句上

关于CACHE BUFFERS CHAINS描述

CACHE BUFFERS CHAINS latch is acquired when searching for data blocks cached in the buffer cache.
Since the Buffer cache is implemented as a sum of chains of blocks, each of those chains is protected
by a child of this latch when needs to be scanned. Contention in this latch can be caused by very heavy
access to a single block. This can require the application to be reviewed.

产生CACHE BUFFERS CHAINS原因

The main cause of the cache buffers chains latch contention is usually a hot block issue.
This happens when multiple sessions repeatedly access one or more blocks that are protected
by the same child cache buffers chains latch.

CACHE BUFFERS CHAINS 处理方法
1) Examine the application to see if the execution of certain DML and SELECT statements can be reorganized to eliminate contention on the object.

处理方法如下:
--通过报告确定latch: cache buffers chains 等待
Top 5 Timed Events                                      Avg    %Total
~~~~~~~~~~~~~~~~~~                                      wait   Call
Event                          Waits        Time (s)    (ms)   Time   Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: cache buffers chains          74,642      35,421    475    6.1 Concurrenc
CPU time                                         11,422           2.0
log file sync                        34,890       1,748     50    0.3 Commit
latch free                            2,279         774    340    0.1 Other
db file parallel write               18,818         768     41    0.1 System I/O
-------------------------------------------------------------
--找出逻辑读高sql
SQL ordered by Gets         DB/Inst:  Snaps: 1-2
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets:   265,126,882
-> Captured SQL account for   99.8% of Total
                            Gets                CPU      Elapsed
Buffer Gets    Executions   per Exec     %Total Time (s) Time (s)  SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
   256,763,367       19,052     13,477.0   96.8 ######## ######### a9nchgksux6x2
Module: JDBC Thin Client
SELECT * FROM SALES ....
     1,974,516      987,056          2.0    0.7    80.31    110.94 ct6xwvwg3w0bv
SELECT COUNT(*) FROM ORDERS ....
--逻辑读大对象
Segments by Logical Reads
-> Total Logical Reads:     265,126,882
-> Captured Segments account for   98.5% of Total
           Tablespace                      Subobject  Obj.       Logical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
DMSUSER    USERS      SALES                           TABLE  212,206,208   80.04
DMSUSER    USERS      SALES_PK                        INDEX   44,369,264   16.74
DMSUSER    USERS      SYS_C0012345                    INDEX    1,982,592     .75
DMSUSER    USERS      ORDERS_PK                       INDEX      842,304     .32
DMSUSER    USERS      INVOICES                        TABLE      147,488     .06
          -------------------------------------------------------------
处理思路：
1.Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary.
  This may be within a single session or across multiple sessions.
2.Check for suboptimal SQL (this is the most common cause of the events)
 look at the execution plan for the SQL being run and try to reduce the
 gets per executions which will minimize the number of blocks being accessed
 and therefore reduce the chances of multiple sessions contending for the same block.

Note:1342917.1 Troubleshooting ‘latch: cache buffers chains’ Wait Contention

2) Decrease the buffer cache -although this may only help in a small amount of cases.

3) DBWR throughput may have a factor in this as well.If using multiple DBWR’s then increase the number of DBWR’s.

4) Increase the PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block.

找出热点对象
First determine which latch id(ADDR) are interesting by examining the number of
sleeps for this latch. The higher the sleep count, the more interesting the
latch id(ADDR) is:
SQL> select CHILD#  "cCHILD"
     ,      ADDR    "sADDR"
     ,      GETS    "sGETS"
     ,      MISSES  "sMISSES"
     ,      SLEEPS  "sSLEEPS"
     from v$latch_children
     where name = 'cache buffers chains'
     order by 5, 1, 2, 3;
Run the above query a few times to to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.
SQL> column segment_name format a35
     select /*+ RULE */
       e.owner ||'.'|| e.segment_name  segment_name,
       e.extent_id  extent#,
       x.dbablk - e.block_id + 1  block#,
       x.tch,
       l.child#
     from
       sys.v$latch_children  l,
       sys.x$bh  x,
       sys.dba_extents  e
     where
       x.hladdr  = '&ADDR' and
       e.file_id = x.file# and
       x.hladdr = l.addr and
       x.dbablk between e.block_id and e.block_id + e.blocks -1
     order by x.tch desc ;
Example of the output :
SEGMENT_NAME                     EXTENT#      BLOCK#       TCH    CHILD#
-------------------------------- ------------ ------------ ------ ----------
SCOTT.EMP_PK                       5            474          17     7,668
SCOTT.EMP                          1            449           2     7,668
Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

5) Consider implementing reverse key indexes (if range scans aren’t commonly used against the segment)

相关参数

SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter optimizer_mode;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ----------------
optimizer_mode                       string                 ALL_ROWS
SQL> show parameter cursor_sharing;
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ----------------
cursor_sharing                       string                 EXACT
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _optim_peek_user_binds
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optim_peek_user_binds%')
NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ ----------------------------------
_optim_peek_user_binds           TRUE                     enable peeking of user binds

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(30));
Table created.
SQL>  begin
  2     for i in 1..100000 loop
  3          insert into t_xifenfei values(i,'xifenfei');
  4      end loop;
  5      commit;
  6    end;
  7    /
PL/SQL procedure successfully completed.
SQL> update t_xifenfei SET name='www.xifenfei.com' where mod(id,20000)=0;
5 row updated.
SQL> commit;
Commit complete.
SQL> create index i_xifenfei on t_xifenfei(name);
Index created.

默认收集统计信息,查看执行计划

SQL> exec  DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autot trace exp
SQL> select id from t_xifenfei where name='xifenfei';
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='xifenfei')
SQL> select id from t_xifenfei where name='www.xifenfei.com';
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='www.xifenfei.com')
--这里可以发现,对于这样少量的列的情况，没有选择一个合适的执行计划

准确收集统计信息

SQL> exec  DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE,
    2 method_opt => 'FOR ALL COLUMNS SIZE 254',estimate_percent => 100);
PL/SQL procedure successfully completed.

再次查看执行计划

SQL> select id from t_xifenfei where name='www.xifenfei.com';
Execution Plan
----------------------------------------------------------
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME"='www.xifenfei.com')
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        320  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
SQL> select id from t_xifenfei where name='xifenfei';
99995 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 99999 |  1367K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 99999 |  1367K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"='xifenfei')
Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       6970  consistent gets
          0  physical reads
          0  redo size
    1455968  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99995  rows processed
--通过这里可以看出在完整的收集表和index包括直方图信息后,数据库执行计划正常
--也说明一点:在数据列分布不均匀的时候,依靠数据库自动收集直方图还是不怎么拷贝.

使用AUTOTRACE测试

SQL> set autot trace exp
SQL> var a varchar2(30);
SQL> exec :a := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select id from t_xifenfei where name=:a;
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME"=:A)
--这里可以发现11g的Bind Variable Peeking 没有使用正确的执行计划,其实这个是AUTOTRACE本身的bug导致

收集下面sql执行计划(peeking测试需要)get_plan.sql脚本

SQL> select * from t_xifenfei where name='wwww.xifenfei.com' and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 2708637417
select * from t_xifenfei where name='wwww.xifenfei.com' and id=100
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=100)
   2 - access("NAME"='wwww.xifenfei.com')
SQL>  select * from t_xifenfei where name='xifenfei' and id=100;
        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 1355242984
 select * from t_xifenfei where name='xifenfei' and id=100
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"=100 AND "NAME"='xifenfei'))
--这里可以看到,两个执行计划都我们希望的

测试peeking功能

SQL> alter system flush shared_pool;
System altered.
SQL> select * from t_xifenfei where name='xifenfei' and id=100;
        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 2860562673
select * from t_xifenfei where name='xifenfei' and id=100
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"=100 AND "NAME"='xifenfei'))
SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ID"=100 AND "NAME"=:B))
--重新硬解析
SQL> alter system flush shared_pool;
System altered.
SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
no rows selected
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=100)
   2 - access("NAME"=:B)
SQL> var b varchar2(30);
SQL> exec :b := 'xifenfei';
PL/SQL procedure successfully completed.
SQL> select * from t_xifenfei where name=:b and id=100;
        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei
1 row selected.
SQL> @get_plan.sql
Rollback complete.
Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=100)
   2 - access("NAME"=:B)
--虽然oracle 11g宣称在在Bind Variable Peeking上增强了很多,
--但是这里的实验,依然证明他存在问题,导致执行计划不正确

通过整体实验过程,证明几个问题：
1.默认的的DBMS_STATS收集统计信息不一定使得所有执行计划均正确,特别在数据很不均匀分布时.
2.AUTOTRACE不能跟踪Bind Variable Peeking
3.Bind Variable Peeking是在硬解析时候生效,虽然11g进行了改善,但是有些时候效果还是不明显,如果数据很不均匀,在发现sql语句很多不合适的时候,建议先删除该sql的执行计划,让其再次硬解析,碰碰运气,如果一直效果不好,建议不适用绑定参数形式(正确的执行计划,更多的硬解析)
4._optim_peek_user_binds参数可以关闭Bind Variable Peeking功能,很不推荐.

本实验室为了说明sql profile的使用方法,不去研讨sql的执行效率.通过sql profile的方法使得一条本该使用index的sql该走全表扫描.
创建模拟表

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei as select * from dba_objects;
表已创建。
SQL> create index i_xifenfei on t_xifenfei(object_id);
索引已创建。
SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);
PL/SQL 过程已成功完成。

默认使用INDEX

SQL> SET AUTOT TRACE EXP
SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;
执行计划
----------------------------------------------------------
Plan hash value: 1926396081
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    30 |     2   (0)|00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=100)

使用hint实现全表扫描

SQL> SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=10
0;
执行计划
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=100)

查找hint对应sql的sql_id

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
0bbt69m5yhf3p
SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100
68r1cnxmn8fjk
SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'
ddmhrzhatfdyh
EXPLAIN PLAN SET STATEMENT_ID='PLUS570193' FOR SELECT /*+ FULL(T_XIFENFEI)*/OBJE
CT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
bybs0sds8yu9c
SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'

获得对应Outline

SQL> SET PAGESIZE 10000
SQL> select * from table(dbms_xplan.display_cursor('0bbt69m5yhf3p',null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0bbt69m5yhf3p, child number 0
-------------------------------------
SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   300 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |
--------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=100)
已选择33行。

创建sql profile

declare
 v_hints sys.sqlprof_attr;
 begin
 v_hints:=sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
      'DB_VERSION(''11.2.0.3'')',
      'ALL_ROWS',
      'OUTLINE_LEAF(@"SEL$1")',
      'FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")',   --这个是由于hint产生,其实我们需要的就是这个
      'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100',
v_hints,'SQLPROFILE_XIFENFEI',                 --sql profile 名称
force_match=>true,replace=>true);
end;
/

验证sql profile

SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;
执行计划
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=100)
Note
-----
   - SQL profile "SQLPROFILE_XIFENFEI" used for this statement

很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     DB Name      Host
------------ ------------ ------------
* 1393262699 XIFENFEI     XIFENFEI-PC
  3753332923 FDJDB        ora1
  3753332923 FDJDB        ora2
The default database id is the local one: '1393262699'.  To use this
database id, press <return> to continue, otherwise enter an alternative.
输入 dbid 的值:  3753332923    <--需要输入
Using 3753332923 for Database ID
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值:  1    <--需要输入
Listing the last day's Completed Snapshots
DB Name        Snap Id    Snap Started
------------ --------- ------------------
FDJDB              906 23 4月  2012 00:00
                   907 23 4月  2012 01:00
                   908 23 4月  2012 02:00
                   909 23 4月  2012 03:00
                   910 23 4月  2012 04:00
                   911 23 4月  2012 05:00
                   912 23 4月  2012 06:00
                   913 23 4月  2012 07:00
                   914 23 4月  2012 08:00
                   915 23 4月  2012 09:00
                   916 23 4月  2012 10:00
                   917 23 4月  2012 11:00
                   918 23 4月  2012 12:00
                   919 23 4月  2012 13:00
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  906  <--需要输入
Begin Snapshot Id specified: 906
输入 end_snap 的值:  907    <--需要输入
End   Snapshot Id specified: 907
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml
Choose a Directory Name from the above list (case-sensitive).
输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_906_907.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 file_name 的值:  xifenfei_awr  <--需要输入
Using the dump file prefix: xifenfei_awr
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.log
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
|  监控导出awr数据进度
End of AWR Extract

导入awr数据

SQL> @E:\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml
Choose a Directory Name from the list above (case-sensitive).
输入 directory_name 的值:  DATA_PUMP_DIR  <--需要输入(注意大小写)
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
输入 file_name 的值:  awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp)
Loading from the file name: awrdat_751_919.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
输入 schema_name 的值:  XFF_AWR  <--需要输入(临时创建用户)
Using the staging schema name: XFF_AWR
Choose the Default tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR users's default tablespace.  This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
输入 default_tablespace 的值:  EXAMPLE  <--需要输入
Using tablespace EXAMPLE as the default tablespace for the XFF_AWR
Choose the Temporary tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR user's temporary tablespace.
TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP                           TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
输入 temporary_tablespace 的值:  TEMP  <--需要输入
Using tablespace TEMP as the temporary tablespace for XFF_AWR
... Creating XFF_AWR user  (临时用户创建)
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.log
|
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log
|  监控导出awr数据进度
... Dropping XFF_AWR user  (临时用户被删除)
End of AWR Load

查看awr报告

SQL> @?/RDBMS/admin/awrrpti.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:  html   <--需要输入
Type Specified:  html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  3753332923        2 FDJDB        fdjdb2       ora2
  3753332923        1 FDJDB        fdjdb1       ora1
* 1393262699        1 XIFENFEI     xff          XIFENFEI-PC
输入 dbid 的值:   3753332923  <--需要输入
Using  3753332923 for database Id
输入 inst_num 的值:  1        <--需要输入
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
输入 num_days 的值:  1  <--需要输入
Listing the last day's Completed Snapshots
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
fdjdb1       FDJDB              906 23 4月  2012 00:00     1
                                907 23 4月  2012 01:00     1
                                908 23 4月  2012 02:00     1
                                909 23 4月  2012 03:00     1
                                910 23 4月  2012 04:00     1
                                911 23 4月  2012 05:00     1
                                912 23 4月  2012 06:00     1
                                913 23 4月  2012 07:00     1
                                914 23 4月  2012 08:00     1
                                915 23 4月  2012 09:00     1
                                916 23 4月  2012 10:00     1
                                917 23 4月  2012 11:00     1
                                918 23 4月  2012 12:00     1
                                919 23 4月  2012 13:00     1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  917  <--需要输入
Begin Snapshot Id specified: 917
输入 end_snap 的值:  918    <--需要输入
End   Snapshot Id specified: 918
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_917_918.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
输入 report_name 的值:xifenfei_awr.html  <--需要输入

早上上班，检查数据库，发现监控日志中在晚上1点到4点钟服务器异常负载现象，查看awr日志发现AUTO_SPACE_ADVISOR_JOB运行异常
0.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

1.资源使用情况
cpu情况

逻辑读情况

物理读情况

通过这些可以看出，在晚上的时间点，AUTO_SPACE_ADVISOR_JOB占用了系统的资源的大部分，导致系统负载过高，也许是出现告警的主要原因。

2.查看运行情况

SQL> col status FOR A10
SQL> COL RUN_DURATION FOR A20
SQL> COL start_date FOR A20
SQL> COL log_date FOR A20
SQL> SELECT status,TO_CHAR(ACTUAL_START_DATE,'YYYY-MM-DD HH24:MI:SS') start_date,
  2  TO_CHAR (log_date, 'YYYY-MM-DD HH24:MI:SS') log_date,RUN_DURATION
  3  FROM dba_scheduler_job_run_details
  4  WHERE job_name = 'AUTO_SPACE_ADVISOR_JOB' order by 3;
STATUS     START_DATE           LOG_DATE             RUN_DURATION
---------- -------------------- -------------------- --------------------
SUCCEEDED  2011-12-31 00:00:02  2011-12-31 00:03:05  +000 00:03:03
SUCCEEDED  2012-01-02 00:00:03  2012-01-02 00:03:05  +000 00:03:03
SUCCEEDED  2012-01-03 00:00:02  2012-01-03 00:02:17  +000 00:02:15
SUCCEEDED  2012-01-04 00:00:02  2012-01-04 00:01:41  +000 00:01:39
SUCCEEDED  2012-01-05 00:01:14  2012-01-05 04:02:05  +000 04:00:51

从这里看出，平时AUTO_SPACE_ADVISOR_JOB运行时间只有3分钟左右的时间就可以结束，昨天异常的运行了4个小时。

3.了解AUTO_SPACE_ADVISOR_JOB作用

SQL> select COMMENTS from dba_scheduler_jobs
  2  where job_name='AUTO_SPACE_ADVISOR_JOB';
COMMENTS
-------------------------------------------
auto space advisor maintenance job

从这里看出，该job的主要作用是是用于segment advisor，如果不使用该功能，可以暂时使用下面语句关闭该job

execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

4.对于AUTO_SPACE_ADVISOR_JOB总结
因为该job的在10.2的某些版本中出现类此bug情况，查看mos发现在10.2.0.4中已经修复，但是我这里因为只是出现了一次，暂时不能定位是bug还是数据库偶尔异常，继续观察，如果再出现类此现象，可以采取临时关闭该job的方式处理。