oracle 存储过程运行速度慢

Question

这两天oracle数据库怎么了，原来一个存储过程只要十几秒钟就可以做完，现在需要一百多秒，而且有的select语句还查不出结果。
不知道这是怎么回事，请各位帮帮忙，分析是什么原因？
还有，数据库如何调优啊？

Answer 1

还是做个statspack的报告吧。具体怎么做google一下，很简单的。
把报告的结果贴出来，帮你分析。

Answer 2

看看你的 udump 的的TRC 文件用TKPROF生成一个报告看看

Answer 3

做一下执行计划，因为数据量变大以后很多情况都可能出现。Explain plan for
--你的代码
Select * from table(dbms_xplan.display);
看看是否有全表扫描

Answer 4

我做完报告了，帮我分析一下，谢谢。
STATSPACK report forDB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORACLE        1524594773 oracle              1 9.0.1.1.1   NO      CY7-XX-ZHAOY
                                                                   H            Snap Id     Snap Time      Sessions Curs/Sess Comment
            ------- ------------------ -------- --------- -------------------
Begin Snap:       1 02-9月 -08 16:26:18       8       3.9
  End Snap:       2 02-9月 -08 16:26:24       8       4.9
   Elapsed:                0.10 (mins)Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:        32M      Std Block Size:         4K
           Shared Pool Size:        48M          Log Buffer:       512KLoad Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             72,448.67            434,692.00
              Logical reads:                698.00              4,188.00
              Block changes:                206.83              1,241.00
             Physical reads:                  0.67                  4.00
            Physical writes:                  7.33                 44.00
                 User calls:                  0.33                  2.00
                     Parses:                 10.67                 64.00
                Hard parses:                  2.50                 15.00
                      Sorts:                 15.00                 90.00
                     Logons:                  0.00                  0.00
                   Executes:                 28.83                173.00
               Transactions:                  0.17  % Blocks changed per Read:   29.63    Recursive Call %:    99.81
 Rollback per transaction %:    0.00       Rows per Sort:    73.63Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.90    In-memory Sort %:  100.00
            Library Hit   %:   85.45        Soft Parse %:   76.56
         Execute to Parse %:   63.01         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   66.67     % Non-Parse CPU:   99.88 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   49.13   50.34
    % SQL with executions>1:   40.81   44.79
  % Memory for SQL w/exec>1:   47.51   61.41Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                            Wait     % Total
Event                                               Waits  Time (s)   Wt Time
-------------------------------------------- ------------ ----------- -------
control file sequential read                           43           0   55.48
db file sequential read                                 4           0   33.21
db file parallel write                                  4           0    5.75
control file parallel write                             2           0    3.10
log file parallel write                                 8           0    1.47
          -------------------------------------------------------------
Wait Events for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
control file sequential read           43          0          0      3     43.0
db file sequential read                 4          0          0     19      4.0
db file parallel write                  4          4          0      3      4.0
control file parallel write             2          0          0      4      2.0
log file parallel write                 8          7          0      0      8.0
log file sync                           2          0          0      1      2.0
SQL*Net message from client             1          0          3   3252      1.0
SQL*Net message to client               1          0          0      0      1.0
          -------------------------------------------------------------
Background Wait Events for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> ordered by wait time desc, waits desc (idle events last)                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read                 4          0          0     19      4.0
control file sequential read            3          0          0      4      3.0
db file parallel write                  4          4          0      3      4.0
control file parallel write             2          0          0      4      2.0
log file parallel write                 8          7          0      0      8.0
smon timer                              1          1        307 ######      1.0
rdbms ipc message                      19         14         18    965     19.0
pmon timer                              1          1          3   3000      1.0
          -------------------------------------------------------------

Answer 5

SQL ordered by Gets for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
             20            4            5.0    0.5     0.00      0.00 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1             12            4            3.0    0.3     0.00      0.00 1705880752
select file# from file$ where ts#=:1             11            3            3.7    0.3     0.00      0.00 1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1              6            1            6.0    0.1     0.00      0.00 1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1              5            1            5.0    0.1     0.00      0.00  199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n              5            2            2.5    0.1     0.00      0.00  931956286
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#              4            2            2.0    0.1     0.00      0.00 1453445442
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#              4            1            4.0    0.1     0.00      0.00 2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
 or subname is null and :6 is null)              3            1            3.0    0.1     0.00      0.00   79934617
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4 from ta              3            3            1.0    0.1     0.00      0.00  114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1SQL ordered by Gets for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

Answer 6

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
              3            1            3.0    0.1     0.00      0.00  189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1              3            1            3.0    0.1     0.00      0.00 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh              3            1            3.0    0.1     0.00      0.00 4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3              1            1            1.0    0.0     0.00      0.00 1491008679
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
  from obj$ o,user$ u,trigger$ t  where t.baseobject=:1 and t.ob
j#=o.obj# and o.owner#=u.user#  order by o.obj#          -------------------------------------------------------------
SQL ordered by Reads for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Disk Reads Threshold:    1000                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
              0            1            0.0    0.0     0.00      0.00   79934617
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4 from ta              0            3            0.0    0.0     0.00      0.00  114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1              0            1            0.0    0.0     0.00      0.00  189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1              0            1            0.0    0.0     0.00      0.00  199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n              0            2            0.0    0.0     0.00      0.00  931956286
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#              0            2            0.0    0.0     0.00      0.00 1453445442
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#              0            1            0.0    0.0     0.00      0.00 1491008679
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
  from obj$ o,user$ u,trigger$ t  where t.baseobject=:1 and t.ob
j#=o.obj# and o.owner#=u.user#  order by o.obj#              0            3            0.0    0.0     0.00      0.00 1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1              0            4            0.0    0.0     0.00      0.00 1705880752
select file# from file$ where ts#=:1              0            1            0.0    0.0     0.00      0.00 1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1              0            4            0.0    0.0     0.00      0.00 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1              0            1            0.0    0.0     0.00      0.00 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
SQL ordered by Reads for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Disk Reads Threshold:    1000

Answer 7

运行一次statspack.snap;
然后运行你的存储过程
再运行一次statspack.snamp选择最后两次的快照,再出报告！！你上面的报告出的不对。

Answer 8

                                                     CPU      Elapsd
 Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh              0            1            0.0    0.0     0.00      0.00 2591785020
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$,
spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa
ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a
nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6
 or subname is null and :6 is null)              0            1            0.0    0.0     0.00      0.00 4059714361
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0) from seg$ where ts#=:1 and file#=:2 and block#=:3          -------------------------------------------------------------
SQL ordered by Executions for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Executions Threshold:     100                                                CPU per    Elap per
 Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
           4               4              1.0       0.00        0.00 1705880752
select file# from file$ where ts#=:1           4               6              1.5       0.00        0.00 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1           3               0              0.0       0.00        0.00  114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where rob
j#=:1           3               4              1.3       0.00        0.00 1536916657
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,n
vl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0) from c
def$ where obj#=:1           2               1              0.5       0.00        0.00  931956286
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)
)from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(co
l#,0) order by grantee#           2               0              0.0       0.00        0.00 1453445442
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) fro
m objauth$ where obj#=:1 and col# is not null group by privilege
#, col#, grantee# order by col#, grantee#           1               1              1.0       0.00        0.00   79934617
select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu
cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,
blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols,
property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel
cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4 from ta           1               1              1.0       0.00        0.00  189272129
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.su
bname,o.dataobj#,o.flags from obj$ o where o.obj#=:1           1               1              1.0       0.00        0.00  199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i
.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa
mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt
,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n           1               0              0.0       0.00        0.00 1491008679
select u.name,o.name, t.update$, t.insert$, t.delete$, t.enabled
  from obj$ o,user$ u,trigger$ t  where t.baseobject=:1 and t.ob
j#=o.obj# and o.owner#=u.user#  order by o.obj#           1               2              2.0       0.00        0.00 1930240031
select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where obj#
=:1           1               9              9.0       0.00        0.00 2385919346
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
SQL ordered by Executions for DB: ORACLE  Instance: oracle  Snaps: 1 -2
-> End Executions Threshold:     100