数据库是 10g rac 两个实例，
用sqlldr 导入数据之后，sqlldr采用的是 skip_idex_maitenance parallel模式导入, rebuild index, 数据量比较大，所以只gather 部分表的及索引的 statistics，
执行以下sql，
INSERT /*+parallel(t_temp_billing,8) */
INTO T_TEMP_BILLING NOLOGGING
SELECT *
from A,B,C，D
where A.ID = B.IDAND B.ID = C.ID AND C.ID= D.ID
执行计划，四个表全部全表扫描，其中一个表数据1000多万。
TKPROF分析完之后,发现Execute 的 disk 为 0，效率极其低下。
call
count cpu elapsed disk
query
currentrows
------- ------ -------- -------------------- ----------
--------------------
Parse 1
0.03 0.03
0
0
0
0
Execute 1
11489.56 11230.700
606018980 122388 1141730
Fetch 0
0.00
0.00
0
0
0
0
---------------------
-------------------- ----------
---------- ----------
total 2 11489.59 11230.730
606018980 122388 1141730
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 222 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on
Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
enq: HW - contention
3430.00
0.26
enq: FB - contention
11170.01
0.76
gc cr block 2-way
20.00
0.00
gc current grant busy
80.00
0.00
row cache lock
1680.00
0.05
gc current multi block request
12250.08
1.33
gc current grant 2-way
70.00
0.00
latch: cache buffers chains
50.00
0.00
log file switch completion
50.12
0.22
log file switch (checkpoint incomplete) 80.98
5.57
KJC: Wait for msg sends to complete
60.01
0.01
gc current block 2-way
40.00
0.00
********************************************************************************
从新建立一个用户，然后导入数据，执行同样的步骤及sql，分析之后得到以下信息
call count cpuelapsed diskquerycurrentrows
------- -------------- ---------- ---------- ---------- --------------------
Parse10.25 0.24
0 92
0 0
Execute1187.62 193.33 214577 389410 122384 1144335
Fetch00.00 0.00
0
0
0 0
------- -------------- ---------- ---------- ---------- --------------------
total2187.87 193.57 214577 389502 122384 1144335
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 223 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on
Times Max. WaitTotal Waited
---------------------------------------- Waited----------------------
db file scattered read
140520.02 12.29
db file sequential read
11210.01
1.23
gc current grant 2-way
60.00
0.00
enq: HW - contention
3310.01
0.26
enq: FB - contention
8140.01
0.38
row cache lock
1680.00
0.05
gc current multi block request
12740.02
1.03
log file switch completion
30.09
0.10
KJC: Wait for msg sends to complete
30.00
0.00
********************************************************************************
两个查询都是在实例1上面做的，但是第一个查询既然花了接近3个小时的时间。比较让人心烦的事，在实例1中把这个花费需要3个小时的查询运行一次之后，后面的运行恢复正常，都只需要花费3,4分钟。
我猜想第一次运行查询既然没有io，是不是所有的数据都是通过实例2来获取，然后传给实例1的？实例2的awr信息目前还没生产，明天去公司把这些信息补齐。
这个问题偶尔能重现，但是就是不知道引起的具体原因是什么，让人头痛，希望各位大师能帮帮忙分析一下。
补充一下执行计划，对应的sql，一共找到了2个执行计划，一个全部全表扫描，一个是nested loops
全部全表扫描：
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pst
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT |
| | | | 90259 (100)|
| |
| 1 |HASH JOIN
|
| 51522 |6742K|6344K| 90259 (5)| 00:18:04 | | |
| 2 | HASH JOIN
|
| 51522 |5735K| | 85154 (5)| 00:17:02 | | |
| 3 |TABLE ACCESS FULL | T_TEMP_TARIFF| 174 |2958 | | 3 (0)| 00:00:01 |
| 4 |HASH JOIN |
| 429K|39M|13M| 85142 (5)| 00:17:02 | | |
| 5 | TABLE ACCESS FULL| T_SUBSCRIBERINFOS| 244K|10M| | 10428 (3)| 00:02:06
| 6 | PARTITION RANGE ALL|
|1996K| 100M| | 67832 (5)| 00:13:34 | 1 |1
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 7 |TABLE ACCESS FULL | T_SUBSCRIBERELATIONS |1996K| 100M| | 67832 (5)| 00:13:34
| 8 | TABLE ACCESS FULL| T_CUSTOMERINFO | 697K|13M| |3710 (5)| 00:00:45
----------------------------------------------------------------------------------------------------
nested loops:
----------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT
|
| | | 8 (100)|
| | |
| 1 |NESTED LOOPS
|
| 1 | 140 | 8 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS
|
| 1 | 120 | 6 (0)| 00:00:01 | | |
| 3 |NESTED LOOPS
|
| 1 |76 | 4 (0)| 00:00:01 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL
| T_TEMP_TARIFF
| 1 |23 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_SUBSCRIBERELATIONS
| 1 |53 | 2 (
| 6 |INDEX RANGE SCAN
| IDX_T_SUBREATIONS_PRODCUTCODE | 121K| | 2 (0)| 00
| 7 |TABLE ACCESS BY INDEX ROWID| T_SUBSCRIBERINFOS
| 1 |44 | 2 (0)|
| 8 | INDEX UNIQUE SCAN
| PK_T_SUBSCRIBERINFOS
| 1 | | 1 (0)| 00:00
| 9 | TABLE ACCESS BY INDEX ROWID | T_CUSTOMERINFO
| 1 |20 | 2 (0)| 00:
|10 |INDEX UNIQUE SCAN
| PK_T_CUSTOMERINFO
| 1 | | 1 (0)| 00:00:01
----------------------------------------------------------------------------------------------------
[ 本帖最后由 zy8643954 于 2010-10-17 11:08 编辑 ]
|
