soft parses of recursive (system) statements

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Fri, 19 Sep 2003 19:26:55 GMT
Message-ID: <3f6b55aa.14357665@nyc.news.speakeasy.net>

We've been fighting soft parses for the past 3-4 weeks (during high load we execute up to 4000 queries per second) So far we got the 'parse to execute' ration down to ~15% from about 40%.
Whenever I look at trace files of sessions, sorted by prscnt, i see that the majority (well, lots) of internal statements are 'recursive' and always softparsed.
Is there are anything I can do to reduce soft parses? Is there are anything I can do to reduce recursive calls?

Thanks

e.g.:
select a.default_cpu_cost, a.default_io_cost from

 association$ a                                 where a.obj# = :1
                          and a.property = :2


call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 144 0.02 0.04 0 0 0 0
Execute 144 0.03 0.01 0 0 0 0
Fetch 144 0.01 0.00 0 144 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 432 0.06 0.05 0 144 0 0
select a.default_selectivity
from

 association$ a                                 where a.obj# = :1
                          and a.property = :2


call     count       cpu    elapsed       disk      query    current

rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 144 0.04 0.04 0 0 0 0
Execute 144 0.01 0.00 0 0 0 0
Fetch 144 0.00 0.00 0 144 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 432 0.05 0.04 0 144 0

0

select
name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,

nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,

scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,   rowid,col#,property, charsetid,charsetform,spare1,spare2 from
 col$ where obj#=:1 order by intcol#

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 17 0.02 0.00 0 0 0 0
Execute 29 0.00 0.00 0 0 0 0
Fetch 370 0.03 0.04 0 106 0 341
------- ------ -------- ---------- ---------- ---------- ----------

---

total 416 0.05 0.04 0 106 0 341

select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,   o.dataobj#,o.flags
from
 obj$ o where o.obj#=:1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 17 0.01 0.00 0 0 0 0
Execute 32 0.01 0.00 0 0 0 0
Fetch 32 0.00 0.00 0 96 0 32
------- ------ -------- ---------- ---------- ---------- ----------

---

total 81 0.02 0.00 0 96 0 32
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Sep 19 2003 - 14:26:55 CDT