待高手解答，latch: cache buffers chains事件如何解决

2013-01-01

在线等待高手解答，latch: cache buffers chains事件怎么解决！1SQL_ID6s7jtnzrmmuw1, child number 02-----

在线等待高手解答，latch: cache buffers chains事件怎么解决！

1SQL_ID  6s7jtnzrmmuw1, child number 0
2-------------------------------------
3insert  /*+ append */  into qianyi.WEB_APP_BASE select * from V_WEB_APP_BASE
4 
5Plan hash value: 843235207
6 
7------------------------------------------------------------------------
8| Id  | Operation                       | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
9------------------------------------------------------------------------
10|   0 | INSERT STATEMENT                |                  |       |       |       | 16714 (100)|          |
11|   1 |  LOAD AS SELECT                 |                  |       |       |       |            |          |
12|   2 |   VIEW                          | V_WEB_APP_BASE   | 50001 |  1035M|       | 16714   (2)| 00:01:54 |
13|   3 |    UNION-ALL                    |                  |       |       |       |            |          |
14|   4 |     NESTED LOOPS                |                  |     1 |   707 |       |     4   (0)| 00:00:01 |
15|   5 |      TABLE ACCESS FULL          | T_PLY_BASE_TRANS |     1 |    38 |       |     2   (0)| 00:00:01 |
16|   6 |      TABLE ACCESS BY INDEX ROWID| T_PLY_BASE       |     1 |   669 |       |     2   (0)| 00:00:01 | 
 
17|*  7 |       INDEX UNIQUE SCAN         | PK_PLY_BASE      |     1 |       |       |     1   (0)| 00:00:01 |
18|*  8 |     HASH JOIN                   |                  | 50000 |    56M|  2056K| 16710   (2)| 00:01:54 |
19|   9 |      TABLE ACCESS FULL          | T_EDR_BASE_TRANS | 50000 |  1464K|       |    61   (4)| 00:00:01 |
20|  10 |      TABLE ACCESS FULL          | T_EDR_BASE       |   509K|   562M|       |  8213   (3)| 00:00:56 |
21------------------------------------------------------------------------
22 
23Predicate Information (identified by operation id):
24---------------------------------------------------
25 
26   7 - access("A"."C_PLY_APP_NO"="B"."C_PLY_APP_NO")
27   8 - access("C"."C_EDR_APP_NO"="D"."C_EDR_APP_NO")
28

T_PLY_BASE_TRANS   --0 rows
T_PLY_BASE              --3000000 rows
T_EDR_BASE_TRANS   --50000 rows
T_EDR_BASE              --500000 rows

从抓出来的这个执行计划看，这个sql的执行计划没有问题吧，应该很快就能执行完结果的，但是真正在数据库中却要跑40分钟，一直等待：latch: cache buffers chains

SQL> select * from (select latch#,gets,misses from v$latch_children where name = 'cache buffers chains' order by misses desc) where rownum <= 10;

    LATCH#       GETS     MISSES
---------- ---------- ----------
       122  796749524   33238090
       122  474008309    4544130
       122   26100007    3874355
       122  393011341    3605687
       122  393779469    3022598
       122  394561570    2386402
       122  261811589    2217600
       122    6536775    1750723
       122    6531901    1734751
       122    6536392    1703470

10 rows selected

------解决方案--------------------

1.优化逻辑读大的sql
2.检查io
3.上awr或者sp报告
[解决办法]
重新对该表最统计分析，

热点排行

oracle

待高手解答，latch: cache buffers chains事件如何解决