Re: soft parses of recursive (system) statements

Hi!

Are you using/have installed OLAP option or use other DW enhancements in your DB?
Association$ table is related to OLAP Metadata API, afaik.

If you are experiencing excessive soft parsing on recursive (data dictionary) operations, particularily during long running complex PL/SQL blocks, you should look whether your _row_cache_cursors matches the need.

Before you do anything, read
http://www.ixora.com.au/tips/tuning/row_cache_cursors.htm , it's very helpful.

Tanel.

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3f6b55aa.14357665_at_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 Sat Sep 20 2003 - 07:39:43 CDT