- 论坛徽章:
- 0
|
一个实际系统中的奇怪问题，请斑竹指点，关于不同系统的效率问题
这是在UNIX上执行的结果，要20秒
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 CONNECT BY (WITHOUT FILTERING)
3 2 COUNT
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'QRY_PHONES'
7 5 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_PHONEUSERI
NFO'
8 7 INDEX (UNIQUE SCAN) OF 'SYS_C002933' (UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_USERNAME'
10 9 INDEX (RANGE SCAN) OF 'XIE1QRY_USERNAME' (NON-UN
IQUE)
Statistics
----------------------------------------------------------
0 recursive calls
120124 db block gets
297238 consistent gets
2192 physical reads
0 redo size
21079 bytes sent via SQL*Net to client
2221 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
353 rows processed
这是在WINDOWS上的结果，只有1秒
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 CONNECT BY (WITHOUT FILTERING)
3 2 COUNT
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS (FULL) OF 'QRY_PHONES'
7 5 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_PHONEUSERI
NFO'
8 7 INDEX (UNIQUE SCAN) OF 'XPKQRY_PHONEUSERINFO'
(UNIQUE)
9 4 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_USERNAME'
10 9 INDEX (RANGE SCAN) OF 'XIE1QRY_USERNAME' (NON-UN
IQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
297326 consistent gets
0 physical reads
0 redo size
26130 bytes sent via SQL*Net to client
2978 bytes received via SQL*Net from client
25 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
360 rows processed
两个查询的执行计划是一样的，为什么第一个库中db block gets 为120124 ，physical reads 为2192 ，而第二个库却都为零呢？可是两个系统的内存都是1G，而且Data buffer和SGA,PGA设置都是差不多，为什么第一个库不去读缓存，而直接去读db block 呢，我很是郁闷，哪位高手能帮我分析一下吗？
我的SQL是这样的，用了CONNECT BY，进行了排序：
SELECT DISTINCT T.LAYER, T.PHONEUSERID ID, U.USERNAME NAME , NVL(T.PARENTID, 0) PARENTID
FROM QRY_PHONEUSERINFO T,QRY_USERNAME U,QRY_PHONES P
WHERE T.PHONEUSERID = U.PHONEUSERID AND T.PHONEUSERID = P.PHONEUSERID
START WITH (T.PARENTID = 0 OR T.PARENTID is NULL)
AND (P.Telephone LIKE '3%' )
AND U.AliasType = '0'
CONNECT BY PRIOR T.PHONEUSERID = T.PARENTID
是不是问题出在系统上，UNIX交换区为3G，系统内存是1G，感觉没有用上系统内存，一直在磁盘上进行排序，我对UNIX不太熟，哪个高手有这方面的经验?帮帮忙，多谢！
上面的语句改成：
SELECT DISTINCT T.LAYER, T.PHONEUSERID ID, U.USERNAME NAME , NVL(T.PARENTID, 0) PARENTID
FROM QRY_PHONEUSERINFO T,QRY_USERNAME U,QRY_PHONES P
WHERE T.PHONEUSERID = U.PHONEUSERID AND T.PHONEUSERID = P.PHONEUSERID
AND (T.PARENTID = 0 OR T.PARENTID is NULL)
AND (P.Telephone LIKE '3%' )
AND U.AliasType = '0'
不进行排序后，就不会有db block和屋里读取：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_USERNAME'
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_PHONES'
6 5 INDEX (RANGE SCAN) OF 'XIE2QRY_PHONES' (NON-UNIQ
UE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'QRY_PHONEUSERINF
O'
8 7 INDEX (UNIQUE SCAN) OF 'SYS_C002933' (UNIQUE)
9 3 INDEX (RANGE SCAN) OF 'XIE1QRY_USERNAME' (NON-UNIQUE
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5681 consistent gets
0 physical reads
0 redo size
15927 bytes sent via SQL*Net to client
1735 bytes received via SQL*Net from client
19 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
256 rows processed
所以问题还是出现在排序上，排序没有在内存中进行，而是在磁盘上，是不是更能证实跟UNIX的配置有关吧？ |
|
免费注册
发表于 2004-05-21 09:44
