我还是DBA方面的菜鸟呢,但以下这个问题也让我们的DBA头疼.
DB从WIN2000(ORACLE 8.17) 移植(IMPORT)到UNIX(ORACLE 8.17)后,整体性能变得很差.
具体两个DB的差异有:
IN WIN2000:
字符集 SIMPLIFIED
ORA使用的内存 300
Database Buffers 52334592NAME VALUE DESCRIPTION
------------------------------------- ----------------------------------------------------------
shared_pool_size 45932032 size in bytes of shared pool
db_block_size 16384 Size of database block in bytes
sort_area_size 65536 size of in-memory sort work area
optimizer_mode CHOOSE optimizer mode
optimizer_max_permutations 80000 optimizer maximum join permutations per query block
hash_area_size 131072 size of in-memory hash work area
object_cache_optimal_size 102400 optimal size of the user session's object cache in bytes IN UNIX:
字符集 UTF8
ORA使用的内存 150
Database Buffers 104857600NAME VALUE DESCRIPTION
--------------------------------------------------------------------------------------------
shared_pool_size 52428800 size in bytes of shared pool
db_block_size 8192 Size of database block in bytes
sort_area_size 524288 size of in-memory sort work area
optimizer_mode RULE optimizer mode
optimizer_max_permutations 80000 optimizer maximum join permutations per query block
hash_area_size 524288 size of in-memory hash work area
object_cache_optimal_size 102400 其中差别大的有'ORA使用的内存,Database Buffers,db_block_size,optimizer_mode'.具体的例子有:
VIEW:
create view V1 as
SELECT * FROM
T1 WH,
T2 GS,
T3 GYS,
T4 ZD1,
T4 ZD2,
T4 ZD3,
T5 A1,
T5 A2
WHERE WH.C11 = GS.C21 (+)
AND WH.C12 = GYS.C31 (+)
AND WH.C13 = A1.C51 (+)
AND WH.C14 = A2.C51 (+)
AND WH.C15 = ZD1.C41 (+)
AND WH.C16 = ZD3.C41 (+)
AND GS.C22 = ZD2.C41 (+);
其中T1,T2有近50万数据,T3,T4有3000笔,T5有10笔数据.
T1.C11 有唯一索引 I11,T1.C12 有不唯一索引 I12
T2.C21,T2.C22 有非唯一索引 I21,I22
T3.C31 有非唯一索引 I31
T4.C41 有唯一索引 I41
T5.C51 有唯一索引 I51
在移植到UNIX之后,性能变得很差,系统几乎慢得不能用,当时optimizer_mode还是使用CHOOSE,
即CBO为先的模式,于是针对这个VIEW用/*+ RULE */强制执行,效果好了很多,又用NOLOGGING
针对SQL语句进行修改,效果不明显.最后干脆将optimizer_mode改为RULE,即RBO模式,整个系统
开始快起来,大部分功能可以如常使用,但是这个VIEW始终存在速度问题,具体情况
1.用HINT rule时consistent gets很大,但physical reads不大,T1 FULL SCAN
T2 用了I21的RANGE SCAN.
2.用HINT ordered时consistent gets不大,但physical reads较大,T1,T2 FULL SCAN.
现在都是在RBO的模式下.
但是两次都没有使用T1的唯一INDEX I11,不知道是否正常?
而用ordered HINT时连T2的INDEX I21都没有用了,但是用rule比ordered耗时.
目前情况是如果用有索引的COLUMN查询,速度好可以,但用没有索引的COLUMN查询
的时候就太耗时了.(又不能给所有需要用做查询条件的COLUMN加INDEX)
本来VIEW不算复杂,如果可以改善请高手指出来.
关于设置方面真的想请高手好好指点一下,怎么才可以更有效的使用INDEX呢?
DB从WIN2000(ORACLE 8.17) 移植(IMPORT)到UNIX(ORACLE 8.17)后,整体性能变得很差.
具体两个DB的差异有:
IN WIN2000:
字符集 SIMPLIFIED
ORA使用的内存 300
Database Buffers 52334592NAME VALUE DESCRIPTION
------------------------------------- ----------------------------------------------------------
shared_pool_size 45932032 size in bytes of shared pool
db_block_size 16384 Size of database block in bytes
sort_area_size 65536 size of in-memory sort work area
optimizer_mode CHOOSE optimizer mode
optimizer_max_permutations 80000 optimizer maximum join permutations per query block
hash_area_size 131072 size of in-memory hash work area
object_cache_optimal_size 102400 optimal size of the user session's object cache in bytes IN UNIX:
字符集 UTF8
ORA使用的内存 150
Database Buffers 104857600NAME VALUE DESCRIPTION
--------------------------------------------------------------------------------------------
shared_pool_size 52428800 size in bytes of shared pool
db_block_size 8192 Size of database block in bytes
sort_area_size 524288 size of in-memory sort work area
optimizer_mode RULE optimizer mode
optimizer_max_permutations 80000 optimizer maximum join permutations per query block
hash_area_size 524288 size of in-memory hash work area
object_cache_optimal_size 102400 其中差别大的有'ORA使用的内存,Database Buffers,db_block_size,optimizer_mode'.具体的例子有:
VIEW:
create view V1 as
SELECT * FROM
T1 WH,
T2 GS,
T3 GYS,
T4 ZD1,
T4 ZD2,
T4 ZD3,
T5 A1,
T5 A2
WHERE WH.C11 = GS.C21 (+)
AND WH.C12 = GYS.C31 (+)
AND WH.C13 = A1.C51 (+)
AND WH.C14 = A2.C51 (+)
AND WH.C15 = ZD1.C41 (+)
AND WH.C16 = ZD3.C41 (+)
AND GS.C22 = ZD2.C41 (+);
其中T1,T2有近50万数据,T3,T4有3000笔,T5有10笔数据.
T1.C11 有唯一索引 I11,T1.C12 有不唯一索引 I12
T2.C21,T2.C22 有非唯一索引 I21,I22
T3.C31 有非唯一索引 I31
T4.C41 有唯一索引 I41
T5.C51 有唯一索引 I51
在移植到UNIX之后,性能变得很差,系统几乎慢得不能用,当时optimizer_mode还是使用CHOOSE,
即CBO为先的模式,于是针对这个VIEW用/*+ RULE */强制执行,效果好了很多,又用NOLOGGING
针对SQL语句进行修改,效果不明显.最后干脆将optimizer_mode改为RULE,即RBO模式,整个系统
开始快起来,大部分功能可以如常使用,但是这个VIEW始终存在速度问题,具体情况
1.用HINT rule时consistent gets很大,但physical reads不大,T1 FULL SCAN
T2 用了I21的RANGE SCAN.
2.用HINT ordered时consistent gets不大,但physical reads较大,T1,T2 FULL SCAN.
现在都是在RBO的模式下.
但是两次都没有使用T1的唯一INDEX I11,不知道是否正常?
而用ordered HINT时连T2的INDEX I21都没有用了,但是用rule比ordered耗时.
目前情况是如果用有索引的COLUMN查询,速度好可以,但用没有索引的COLUMN查询
的时候就太耗时了.(又不能给所有需要用做查询条件的COLUMN加INDEX)
本来VIEW不算复杂,如果可以改善请高手指出来.
关于设置方面真的想请高手好好指点一下,怎么才可以更有效的使用INDEX呢?
解决方案 »
- 求推Oracle经典书籍!!!!!!!
- 存储过程 没有年 月日比较
- oracle 中字符串分隔查询
- 【求助】高手能否提供DBATools For PL/SQL Developer的无限期试用版？？？
- 为什么执行一条很长的SELECT语句，ORACLE服务就停止了呢？
- 安装问题 急求
- 怎样在存储过程中根据分区名（part20050621）删除过期的分区？
- 怎么写这个sql语句！
- 我有正版oracle815，谁买？
- 有请各位大侠！VBA如何读取ORACLE表的序列号？
- 有out參數的oracle如何調用,不用封裝在事務對象中!
- ??棘手问题:从SqlServer2000中往Oracle中倒入数据表,可是在Oracle上查询表的时候说这个对象不存在??
C19是T1的一个没有索引的COLUMN.
这时VIEW用HINT RULE /*+ RULE */
SELECT count(*) FROM V1 WHERE C19='DC13483' AND ROWNUM<10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (OUTER)
4 3 FILTER
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS (OUTER)
8 7 NESTED LOOPS (OUTER)
9 8 NESTED LOOPS (OUTER)
10 9 NESTED LOOPS (OUTER)
11 10 TABLE ACCESS (FULL) OF 'T1'
12 10 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)
13 9 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)
14 8 INDEX (UNIQUE SCAN) OF 'I51' (UNIQUE)
15 7 INDEX (UNIQUE SCAN) OF 'I51' (UNIQUE)
16 6 INDEX (RANGE SCAN) OF 'I31' (NON-UNIQUE)
17 5 TABLE ACCESS (BY INDEX ROWID) OF 'T2'
18 17 INDEX (RANGE SCAN) OF 'I21' (NON-UNIQUE)
19 3 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)Statistics
----------------------------------------------------------
0 recursive calls
7 db block gets
1051482 consistent gets
4306 physical reads
0 redo size
152 bytes sent via SQL*Net to client
251 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed这时VIEW用HINT ORDERED /*+ ORDERED */
SELECT count(*) FROM V1 WHERE C19='DC13483' AND ROWNUM<10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=2777395 Card=1 Bytes=164)
1 0 SORT (AGGREGATE)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (OUTER) (Cost=2777395 Card=25600000000 Bytes=4198400000000)
4 3 HASH JOIN (Cost=2777395 Card=1280000000 Bytes=194560000000)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=1145 Card=20 Bytes=740)
6 4 NESTED LOOPS (OUTER) (Cost=2000 Card=6400000000 Bytes=736000000000)
7 6 NESTED LOOPS (OUTER) (Cost=2000 Card=320000000 Bytes=32960000000)
8 7 NESTED LOOPS (OUTER) (Cost=2000 Card=16000000Bytes=1456000000)
9 8 NESTED LOOPS (OUTER) (Cost=2000 Card=800000Bytes=62400000)
10 9 HASH JOIN (OUTER) (Cost=2000 Card=40000 Bytes=2600000)
11 10 TABLE ACCESS (FULL) OF 'T1' (Cost=1987 Card=2000
Bytes=118000)
12 10 INDEX (FAST FULL SCAN) OF 'I31' (
NON-UNIQUE) (Cost=4 Card=2000 Bytes=12000)
13 9 INDEX (UNIQUE SCAN) OF 'I51' (UNIQUE)
14 8 INDEX (UNIQUE SCAN) OF 'I51' (UNIQUE)
15 7 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)
16 6 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)
17 3 INDEX (UNIQUE SCAN) OF 'I41' (UNIQUE)Statistics
----------------------------------------------------------
14 recursive calls
25 db block gets
20795 consistent gets
11878 physical reads
216 redo size
153 bytes sent via SQL*Net to client
251 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
/*+ parallel (t1 18) full(t1)*/