Re: tuning problem

On Wed, 4 Sep 2002 10:24:13 +0200, "stefan keller" <s.keller_at_impetris.ch> wrote:

>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
>
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 17
>
>Rows Row Source Operation
>------- ---------------------------------------------------
> 0 SORT UNIQUE
> 0 NESTED LOOPS
> 0 TABLE ACCESS BY INDEX ROWID PIMANRELATION
> 2 INDEX RANGE SCAN (object id 2350)
> 201126 INDEX FULL SCAN (object id 2575)
> 0 TABLE ACCESS BY INDEX ROWID PIMANTYPE
> 0 INDEX UNIQUE SCAN (object id 2358)
>
>any suggestions?
>
>
>db facts
>db_block_size = 8192
>db_file_multiblock_read_count = 32
>db_block_buffers = 125000
>SHARED_POOL_SIZE = 30000000
>SHARED_POOL_RESERVED_SIZE = 15000000
>SHARED_POOL_RESERVED_MIN_ALLOC = 5000
>
>optimizer is choose
>

substr function on lefthand expression on table in subquery, which is going to force a full table scan by default If you rewrite this
 t_01.rsecondary_objectu = SUBSTR ( t_03.pobject_uid , 1 ,14 ) properly
 SUBSTR ( t_03.pobject_uid , 1 ,14 ) = t_01.rsecondary_objectu -- which means 'is constant from main query', you probably immediately see what I mean.

Several steps to be taken
If you have the Enterprise Edition, put a function based index on substr(PPOM_STUB.pobject_uid, 1,14)
However from the code it looks like (hardcoded literals are used all the time) there are much more problems in this application, so I would really recommend
- hire a lawyer
- sue the vendor

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Sep 04 2002 - 03:53:25 CDT