help：10g rac大表全表扫描比单机慢
本帖最后由 Vincent2013 于 2014-1-28 13:50 编辑环境：
aix6.1 pwoerha6.1+oracle10g rac使用裸设备作存储
测试环境：aix6.1+oracle10g文件系统
数据库版本：10.2.0.4
问题：
一个1000M左右的表，全表扫描在rac上执行时间是单机上的近20倍
附件是收集的awr及10046事件，请各位大师协助分析，解决，谢谢！ select id,globeid
from
ATT_PD_VIPO where globeid in ('06M00000100397052')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.47 45.27 6249 127425 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.47 45.28 6249 127425 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL ATT_PD_VIPO (cr=127425 pr=6249 pw=0 time=63 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 12.69 12.69
gc buffer busy 32625 0.04 35.29
gc cr multi block request 5082 0.00 3.15
db file scattered read 997 0.01 0.70
db file sequential read 1365 0.01 0.77
gc current block 2-way 453 0.00 0.61
gc cr grant 2-way 503 0.00 0.67
latch: cache buffers chains 1014 0.00 0.03
db file parallel read 443 0.02 0.53
read by other session 556 0.01 0.21
select id,globeid
from
ATT_PD_VIPO where globeid in ('06M00000100397052')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.99 3.29 61042 127425 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.99 3.30 61042 127425 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL ATT_PD_VIPO (cr=127425 pr=61042 pw=0 time=67 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 14.01 14.01
db file sequential read 17770 0.00 0.15
db file scattered read 14752 0.00 0.62
latch: cache buffers chains 1 0.00 0.00
关键词 RAC+ GC 争用 Liu Maclean(刘相兵 发表于 2014-1-23 15:08 static/image/common/back.gif
select id,globeid
from
ATT_PD_VIPO where globeid in ('06M00000100397052')
刘大，我这个有什么方法能调整下吗？ 去google或MOS找下关键词吧
页:
[1]