trace文件如下,请帮忙分析问题,谢谢!SQL ID: 61ztaytyqghay
Plan Hash: 2562382205
SELECT col1
FROM
table1 WHERE ROWNUM < 2000000--disk(硬盘读 query及文件系统CACHE读次数)
--query:orcl cache读次数call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 4.58 7.23 3540 164834 0 3999998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 4.58 7.24 3540 164834 0 3999998 Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
1999999 COUNT STOPKEY (cr=82415读缓存的块 pr=2902读硬盘块 pw=0读硬盘等待时间 time=2092190 us时间)
1999999 PARTITION LIST ALL PARTITION: 1 12 (cr=82415 pr=2902 pw=0 time=1517878 us cost=23387 size=3999998 card=1999999)
1999999 TABLE ACCESS FULL table1 PARTITION: 1 12 (cr=82415 pr=2902 pw=0 time=992228 us cost=23387 size=3999998 card=1999999)全表扫描
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 7 0.00 0.00
library cache pin 7 0.00 0.00
enq: KO - fast object checkpoint 7 0.00 0.00
reliable message 3 0.00 0.00
Disk file operations I/O 13 0.00 0.00
direct path read 物理读 3 0.00 0.00
gc cr multi block request 13 0.00 0.00
db file scattered read 分散读 24 0.00 0.00
gc cr grant 2-way 1 0.00 0.00
db file sequential read连续读 1 0.00 0.00
********************************************************************************
Plan Hash: 2562382205
SELECT col1
FROM
table1 WHERE ROWNUM < 2000000--disk(硬盘读 query及文件系统CACHE读次数)
--query:orcl cache读次数call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 4.58 7.23 3540 164834 0 3999998
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 4.58 7.24 3540 164834 0 3999998 Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
1999999 COUNT STOPKEY (cr=82415读缓存的块 pr=2902读硬盘块 pw=0读硬盘等待时间 time=2092190 us时间)
1999999 PARTITION LIST ALL PARTITION: 1 12 (cr=82415 pr=2902 pw=0 time=1517878 us cost=23387 size=3999998 card=1999999)
1999999 TABLE ACCESS FULL table1 PARTITION: 1 12 (cr=82415 pr=2902 pw=0 time=992228 us cost=23387 size=3999998 card=1999999)全表扫描
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 7 0.00 0.00
library cache pin 7 0.00 0.00
enq: KO - fast object checkpoint 7 0.00 0.00
reliable message 3 0.00 0.00
Disk file operations I/O 13 0.00 0.00
direct path read 物理读 3 0.00 0.00
gc cr multi block request 13 0.00 0.00
db file scattered read 分散读 24 0.00 0.00
gc cr grant 2-way 1 0.00 0.00
db file sequential read连续读 1 0.00 0.00
********************************************************************************
如果有这么多数据。那你可以SELECT count(1)
FROM
table1 WHERE ROWNUM < 2000000 看一下有多少条数据。