tuning problem

From: stefan keller <s.keller_at_impetris.ch>
Date: Wed, 4 Sep 2002 10:24:13 +0200
Message-ID: <3d75c32e_1@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

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 Received on Wed Sep 04 2002 - 03:24:13 CDT