OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Performance Issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance Issue [message #175112]

Wed, 31 May 2006 15:45

michijj
Messages: 1
Registered: May 2006

Junior Member

I am having some problem on performance tuning. In a function, I declared a cursor, then loop through this cursor to validate each criteria. If all criterias pass, insert into another table.

The reason we have to use cursor instead of direct insert statement is, some criteria is very time consuming, and we want to get a smaller size of data before checking those huge criteria.

Now the performance is bad. I was originally think it is because of the cursor loop. But after changed it to a direct insert statement, it is still bad.

There is one exception though. If I add one dummy filter in the cursor definition, the performance improved about 5 times.

The dummy filter is like this:

        and (sysdate - first_item_date) =
         (sysdate - first_item_date)

         and (sysdate - last_item_date) =
        (sysdate - last_item_date)

The SELECT statement in the cursor definition is something like this:

cursor c1  is
	  select  pv.id pallet_id,
			 	pallet_pkg.primary_collection_facility_id(pv.id, i_product_id) primary_collection_facility_id,
				pisv.total_amount total_amount,
			    pv.first_item_date first_item_date,
                pv.last_item_date last_item_date
		from pallet_view pv, pallet_inventory_summary_view pisv
		where pv.status_ind in ('INV', 'INT')
		and pv.zone_id = nvl(i_zone_id, pv.zone_id)
		and pv.storloc_id = nvl(i_storloc_id, pv.storloc_id)
		and pisv.pallet_id = pv.id
		and pisv.product_id = i_product_id
		and (pv.clusdef_id = i_clusdef_id or i_clusdef_id is null)
		and pisv.facility_id = i_facility_id
 		
        and pisv.avail_amount >0
        and (pisv.avail_amount between
        	nvl(i_avail_amount_from, pisv.avail_amount)
                             and
            nvl(i_avail_amount_to, pisv.avail_amount))
            
        and (i_target_ship_date - first_item_date) =
         (i_target_ship_date - first_item_date)

         and (i_target_ship_date - last_item_date) =
        (i_target_ship_date - last_item_date)

        and decode(pallet_pkg.num_distinct_uom(pv.id), 1,
                    round(((avail_amount/pallet_pkg.get_amount(pallet_id))*100),1), -1)
                     >= nvl(i_min_percent_avail, -1);

Will this be an idex issue or something?

Thanks.
-Jessie