1 SQL_ID 6s7jtnzrmmuw1, child number 0
2 -------------------------------------
3 insert /*+ append */ into qianyi.WEB_APP_BASE select * from V_WEB_APP_BASE
4
5 Plan 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
23 Predicate 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 chainsSQL> 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 170347010 rows selected
2 -------------------------------------
3 insert /*+ append */ into qianyi.WEB_APP_BASE select * from V_WEB_APP_BASE
4
5 Plan 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
23 Predicate 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 chainsSQL> 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 170347010 rows selected
2.检查io
3.上awr或者sp报告