Query performance issue

 Hi,

I'm facing a performance issue on production database. We've just migrate this database from 9.2.0.8 to 10.2.0.3  and the query take more than 20 hours to give us the result.

Here is the explain and the query, can someone give me a help ?

	SELECT ALL lp_dca_ds_dec.r_object_id, lp_dca_ds_dec.object_name, 
       lp_dca_ds_dec.r_object_type, lp_dca_ds_dec.r_lock_owner, 
       lp_dca_ds_dec.owner_name, lp_dca_ds_dec.r_link_cnt, 
       lp_dca_ds_dec.r_is_virtual_doc, lp_dca_ds_dec.r_content_size, 
       lp_dca_ds_dec.a_content_type, lp_dca_ds_dec.i_is_reference, 
       lp_dca_ds_dec.r_assembled_from_id, lp_dca_ds_dec.r_has_frzn_assembly, 
       lp_dca_ds_dec.a_compound_architecture, lp_dca_ds_dec.i_is_replica, 
       lp_dca_ds_dec.r_policy_id, dm_repeating.serial_nb, dm_repeating.page_nb, 
       dm_repeating.run_date
    FROM lp_dca_ds_dec_sp lp_dca_ds_dec, lp_dca_ds_dec_rp dm_repeating
    WHERE lp_dca_ds_dec.application_group = 'dca300r1'
      AND EXISTS (SELECT r_object_id