Re: tuning problem

"stefan keller" <s.keller_at_impetris.ch> wrote in message news:3d75c32e_1_at_news.bluewin.ch...
> hello folowing question, please anwer also to: stefan.keller_at_rieter.com
>
> i have a table (PPOM_STUB) containing 300000 recs
>
> there is following sql from a third company (i cant change the source
 code)
> SELECT DISTINCT t_01.rsecondary_objectu, t_01.rsecondary_objectc
> FROM
> PIMANRELATION t_01 , PIMANTYPE t_02 WHERE ( ( ( ( t_01.rprimary_objectu =
> :1
> ) AND ( t_01.rrelation_typeu = t_02.PUID ) ) AND ( t_02.ptype_name =
> 'IMAN_master_form' ) ) AND NOT EXISTS ( SELECT t_03.PUID FROM PPOM_STUB
> t_03 WHERE ( t_01.rsecondary_objectu = SUBSTR ( t_03.pobject_uid , 1 ,
> 14 ) ) ) )
>
> this "NOT EXISTS ( SELECT t_03.PUID FROM PPOM_STUB
> t_03 WHERE ( t_01.rsecondary_objectu = SUBSTR ( t_03.pobject_uid , 1 ,
> 14 )" is a killer subselect:
> tkprof says:
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> Parse 291 0.64 0.67 0 0 0
> 0
> Execute 291 0.02 0.02 0 0 0
> 0
> Fetch 291 319.27 320.28 80 473827 0
> 163
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
 --
> ----
> total 873 319.93 320.97 80 473827 0
> 163

Others have pointed you at function based indexes (which looks like a good suggestion).

Something you can play with (very very very carefully in test) is the init.ora parameter CURSOR_SHARING. Changing this parameter will mean that Oracle substitutes bind variables for your literals which will eliminate a lot of your parsing. It is however also quite prone to change execution plans (especially if you have histograms). It may give you large advantages or it may ruin performance.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************