Oracle Sql优化

Question

   各位，下面是SQL的执行计划，该用的索引也都用了，而对于我这个刚接触SQL优化的菜鸟来说，我也认为下面得到的执行计划已是最优的了，但所花费的时间却不尽人意，查询一年的数据需要10秒左右。但在实际的业务开发过程中需要对9种不同的业务进行查询（SQL一样，表名不同，各表所建索引也都一样），而为了减少对数据库的访问所以9种业务SQL之间采用了union all进行连接。  现在的问题是：
    1、在程序中每天的第一次执行中花费的时间大约是200秒左右，之后就会快很多，而我采用的是硬编码SQL，按理来说在SGA共享池中是没有相同的SQL的，但我改变查询条件，执行时间是在30秒左右。这是我不能理解的，也没有找到答案。
    2、在上面的执行计划中对于表FEP是进行的全表扫描（表中有索引），而在其他业务查询时却用了索引，SQL语句一样表名相同，各表所建索引也是一样的，经过反复调整也没有答案，不知道是不是走索引比全表扫描代价更大的原因，有待验证。
    3、截图中的计划不知在各位看来是否是最优的，是否还有优化的余地，如果有该从什么地方下手？
    4、上面也说了9种不同的业务之间使用union all进行接接查询，如果每一条SQL执行时间是10秒的话，也是需要90秒的，不知各位对于我这种写法有没有好的建议或更好的写法。程序中是用for循环对每个业务品种的SQl进行拼装然后进行union all连接查询。
    5、如果需要使用hints提示的话，应该要基于什么原则。    另外，问个问题，有些十几万或者百万、千万的数据只要花费几秒钟就可以查询出来和是如何实现的呢？
    还请各位不啬赐教，拜谢了！SQLOracle性能优化索引

Answer 1

首先、你的执行计划里有个问题 ,unique index扫描的成本没被统计,show parameter opt参数怎么设置的
1）这里可能有物理读的影响、清掉buffer cache或者时间就差不多
2）fep走的nested loops join、而且是驱动表、但他却是全表扫、有点不太对劲、你强制让trn_inr做驱动表试试
3）嵌套循环这么多、肯定可以优化的、而且、视图未被合并？
4）sql语句的能搞上来看看不？、如果太长伪代码也行、贴出在哪些列上搞了索引、表的记录数、另外、表有分析过没？db版本多少？总之、你提供的信息太少了、有必要看下提问智慧
5）、、、、、、

Answer 2

抱歉，下面是opt的参数设置：1、Oracle的版本是10g的，使用alter system flush buffer_cache清除后时间相差还是挺大的，刚试下了开始没清buffer cache执行时间是16秒左右，清后10秒左右，再清再执行平均是2.5秒左右。下面是统计信息：清除buffer cache执行：
再次执行：
2、如何让TRN表作为驱动表。
3、视图需要人工合并吗？
4、SQL语句：
select b1.bchname,b.bchname,' ',y.nam1,trn.inifrm,trn.ownref,to_char(obj.opndat,'YYYY-MM-DD'),
c.cur,c.amt,c.amt*r.rat,f.famt,f2.famt,stb.txt,'进口' from 
trn trn,BCD obj,pts s,pty y,bch b,bch b1,cbb c,rat r,
(SELECT STB.COD COD,STB.TXT TXT FROM STB WHERE STB.TBL='CTYTXT' AND STB.UIL='EN') stb,
(select Trn.INR as inr,sum(p.xrfamt) as famt from BCD obj,fep p,trn trn where  
trn.objtyp = 'BCD'  and to_char(TRN.cpldattim,'YYYY-MM-DD')>='2012-05-30' and to_char(TRN.cpldattim,'YYYY-MM-DD')<'2013-07-01' and 
trn.objinr = obj.inr and trn.relflg = 'R' and trn.ownref = obj.ownref and substr(p.feecod,1,3) <> 'SWI' and 
trn.inr = p.srctrninr and p.srcdat is not null and p.rpldat is null and p.dondat is not null and trn.objinr=p.objinr(+) and 
trn.objtyp=p.objtyp(+) group by Trn.INR) f,
(select Trn.INR as inr,sum(p.xrfamt) as famt from BCD obj,fep p,trn trn where  
trn.objtyp = 'BCD'  and to_char(TRN.cpldattim,'YYYY-MM-DD')>='2012-05-30' and to_char(TRN.cpldattim,'YYYY-MM-DD')<'2013-07-01' and 
trn.objinr = obj.inr and trn.relflg = 'R' and trn.ownref = obj.ownref and substr(p.feecod,1,3) = 'SWI' and 
trn.inr = p.srctrninr and p.srcdat is not null and p.rpldat is null and p.dondat is not null and trn.objinr=p.objinr(+) and 
trn.objtyp=p.objtyp(+) group by Trn.INR) f2 
where obj.stacty = stb.cod(+) and  trn.objtyp = 'BCD' and trn.objinr = obj.inr and trn.relflg = 'R' and s.objtyp = 'BCD' and 
s.objinr = obj.inr and s.rol = 'DRE' and s.ptyinr = y.inr and trn.branchinr = b.inr and b.upbranch = b1.branch and 
c.objtyp = trn.objtyp and c.objinr = trn.objinr and c.cbc = 'MAXSUM' and c.extid = 'AMT1' and to_char(c.enddat,'YYYY')='2299' and 
c.cur = r.cur and to_char(r.dat,'YYYY-MM') = to_char(obj.opndat,'YYYY-MM') and f.inr(+) = trn.inr and f2.inr(+) = trn.inr  and 
to_char(TRN.cpldattim,'YYYY-MM-DD') >= '2012-05-30' and to_char(TRN.cpldattim,'YYYY-MM-DD') < '2013-07-01'
有关各表的索引：
如果还需要什么信息请告诉我，谢谢了！

Answer 3

看了一下，表应该是自动分析的，下面是各表的总数据量及上面Sql中业务的数据量：
TRN:176649，644
BCD(业务数据表):224
PTS:207606，672
PTY:164051
BCH:805
CBB:301296，1428
RAT:433
STB:39791
FEP:17401，662

Answer 4

关于opt中的 filesystemio_options测试机上的是value是asynch,昨晚贴的是我本机的，其他都一样。