oracle10g ， 导入到新装的服务器后有个查询性能急剧下降，在原库查询大概为2秒，导入到新库后5分钟都不出来
explain plan 后的结果为
Plan
SELECT STATEMENT ALL_ROWSCost: 7,131 Bytes: 44 Cardinality: 1
13 SORT GROUP BY Cost: 7,131 Bytes: 44 Cardinality: 1
12 VIEW SMC. Cost: 7,130 Bytes: 44 Cardinality: 1
11 HASH GROUP BY Cost: 7,130 Bytes: 44 Cardinality: 1
10 VIEW SMC. Cost: 7,129 Bytes: 44 Cardinality: 1
9 WINDOW SORT Cost: 7,129 Bytes: 151 Cardinality: 1
8 HASH JOIN Cost: 7,128 Bytes: 151 Cardinality: 1
6 MERGE JOIN CARTESIAN Cost: 3,040 Bytes: 298,468 Cardinality: 3,596
3 NESTED LOOPS Cost: 2,828 Bytes: 67 Cardinality: 1
1 TABLE ACCESS FULL TABLE SMC.POS_SOUT Cost: 2,828 Bytes: 53 Cardinality: 1
2 INDEX UNIQUE SCAN INDEX (UNIQUE) SMC.SYS_C005320 Cost: 0 Bytes: 14 Cardinality: 1
5 BUFFER SORT Cost: 3,040 Bytes: 425,840 Cardinality: 26,615
4 TABLE ACCESS FULL TABLE SMC.PT_PART Cost: 212 Bytes: 425,840 Cardinality: 26,615
7 TABLE ACCESS FULL TABLE SMC.POS_SOUT_DETL Cost: 4,077 Bytes: 61,046,796 Cardinality: 897,747
------------------------------------------------------------------------------------------------------------
在原库里的结果是
Plan
SELECT STATEMENT ALL_ROWSCost: 8,365 Bytes: 1,200 Cardinality: 25
12 SORT GROUP BY Cost: 8,365 Bytes: 1,200 Cardinality: 25
11 VIEW SMC. Cost: 8,361 Bytes: 1,454,784 Cardinality: 30,308
10 HASH GROUP BY Cost: 8,361 Bytes: 1,515,400 Cardinality: 30,308
9 VIEW SMC. Cost: 7,982 Bytes: 1,515,400 Cardinality: 30,308
8 WINDOW SORT Cost: 7,982 Bytes: 4,243,120 Cardinality: 30,308
7 HASH JOIN Cost: 7,035 Bytes: 4,243,120 Cardinality: 30,308
1 TABLE ACCESS FULL TABLE SMC.PT_PART Cost: 213 Bytes: 603,420 Cardinality: 30,171
6 HASH JOIN Cost: 6,820 Bytes: 3,636,960 Cardinality: 30,308
2 INDEX RANGE SCAN INDEX (UNIQUE) SMC.SYS_C006102 Cost: 1 Bytes: 1,170 Cardinality: 90
5 HASH JOIN Cost: 6,818 Bytes: 3,261,039 Cardinality: 30,477
3 TABLE ACCESS FULL TABLE SMC.POS_SOUT Cost: 2,776 Bytes: 915,663 Cardinality: 18,687
4 TABLE ACCESS FULL TABLE SMC.POS_SOUT_DETL Cost: 4,031 Bytes: 45,075,628 Cardinality: 777,166
这里为什么在新库里会变成 MERGE JOIN , 索引什么的都在的。（注：原库是32位，新的是64位。应该跟这个没关系吧。。只是安装oracle的时候安装包竟然要32位的.so 包，所以 LD_DIRECOTRY 指向的是 /url/lib 不是 lib64）
系统是 CentOS65. 64位的
------解决思路----------------------
导入后新的执行计划比旧的执行计划cost要小,很可能是统计信息的问题.当然也有可能是系统参数,服务器性能,表存储参数
参数等有关.
先执行一下:
begin
dbms_stats.gather_table_stats('SMC','POS_SOUT',estimate_percent => 30,cascade => true);
dbms_stats.gather_table_stats('SMC','POS_SOUT_DETL',estimate_percent => 30,cascade => true);
dbms_stats.gather_table_stats('SMC','PT_PART',estimate_percent => 30,cascade => true);
end;
看看情况.
如果不行,试试在语句select后加/*+leading(PT_PART,POS_SOUT,POS_SOUT_DETL)*/看会不会走原来的计划再说,
如果不行,试试在语句select后加/*+use_hash(POS_SOUT,POS_SOUT_DETL)*/试试
------解决思路----------------------
新库没有采集统计信息，手动重新采一下
特别是SMC.POS_SOUT表，严重偏差