Need to tune performance issue of a sql query

Thanks evgenyg!

I have already checked the explain plan of the query and everything looks fine and this query is taking more time to fetch data only for a specific customer, for rest of the customers its working fine., it is fetching data within 10 sec.

It was a long query so i thought of attaching it. But for everyone's convenience i can paste the same query over in this editor as well.

SELECT COUNT(*)

FROM

 (SELECT msi.segment1 Item,

  DECODE ( 'CONFIGURED ASSY', mc.segment2,

  (SELECT cii1.serial_number

  FROM csi_ii_relationships cis,

   csi_item_instances cii1,

   mtl_system_items_b msi1,

   mtl_item_categories mic1,

   mtl_categories mc1

  WHERE object_id          = cii.instance_id

  AND cis.subject_id        = cii1.instance_id

  AND relationship_type_code    = 'COMPONENT-OF'

  AND cii1.inventory_item_id    = msi1.inventory_item_id

  AND cii1.last_vld_organization_id = msi1.organization_id

  AND mic1.inventory_item_id    = msi1.inventory_item_id

  AND mic1.organization_id     = msi1.organization_id

  AND mc1.category_id        = mic1.category_id

  AND mc1.segment2         = 'BASE UNIT'

  AND mic1.category_set_id     = 1

  AND cii1.serial_number      IS NOT NULL

  AND UPPER (msi1.description) NOT LIKE '%'

   || :2

   || '%'

  AND (SYSDATE) BETWEEN (NVL (cis.active_start_date,SYSDATE)) AND (NVL (cis.active_end_date,SYSDATE))

  AND ROWNUM = 1

  ),

  (SELECT 'CONFIGURED ASSY'

  FROM mtl_categories mc2,

   mtl_item_categories mic2,

   mtl_category_sets mcs

  WHERE mic2.inventory_item_id = msi.inventory_item_id

  AND mic2.organization_id   = msi.organization_id

  AND mc2.category_id     = mic2.category_id

  AND mic2.category_set_id   = mcs.category_set_id

  AND mcs.category_set_name  = 'RQUOTE'

  AND mc2.segment2      IN ('CERTIFIED')

  ),

  (SELECT cii1.serial_number

  FROM csi_ii_relationships cis,

   csi_item_instances cii1,

   mtl_system_items_b msi1,

   mtl_item_categories mic1,

   mtl_categories mc1

  WHERE object_id          = cii.instance_id

  AND cis.subject_id        = cii1.instance_id

  AND relationship_type_code    = 'COMPONENT-OF'

  AND cii1.inventory_item_id    = msi1.inventory_item_id

  AND cii1.last_vld_organization_id = msi1.organization_id

  AND mic1.inventory_item_id    = msi1.inventory_item_id

  AND mic1.organization_id     = msi1.organization_id

  AND mc1.category_id        = mic1.category_id

  AND mc1.segment2         = 'BASE UNIT'

  AND mic1.category_set_id     = 1

  AND cii1.serial_number      IS NOT NULL

  AND UPPER ( msi1.description) NOT LIKE '%'

   || :2

   || '%'

  AND (SYSDATE) BETWEEN (NVL (cis.active_start_date,SYSDATE)) AND (NVL (cis.active_end_date,SYSDATE))

  AND ROWNUM = 1

  ), cii.serial_number) mfr_Serial_Number,

  DECODE ( 'CONFIGURED ASSY', mc.segment2, cii.serial_number,

  (SELECT 'CONFIGURED ASSY'

  FROM mtl_categories mc2,

   mtl_item_categories mic2,

   mtl_category_sets mcs

  WHERE mic2.inventory_item_id = msi.inventory_item_id

  AND mic2.organization_id   = msi.organization_id

  AND mc2.category_id     = mic2.category_id

  AND mic2.category_set_id   = mcs.category_set_id

  AND mcs.category_set_name  = 'RQUOTE'

  AND mc2.segment2      IN ('CERTIFIED')

  ), cii.serial_number, NULL) Config_Serial_Number,

  cii.external_reference Equipment_Id,

  hz_format_pub.format_address_lov (hl.address1, NULL, NULL, NULL, hl.city, hl.postal_code, hl.state, NULL, hl.county, NULL, NULL) Install_Location,

  hps.party_site_id,

  hps.party_site_number,

  hps.party_id,

  cii.instance_id,

  hca.cust_account_id,

  mc.segment7 Model,

  cis.name Status,

  hl.location_id

 FROM csi_item_instances cii,

  csi_instance_statuses cis,

  mtl_system_items_b msi,

  mtl_item_categories mic,

  mtl_categories mc,

  hz_party_sites hps,

  hz_cust_accounts hca,

  hz_party_site_uses hpsu,

  hz_locations hl

 WHERE NVL (cii.active_end_date, '31-DEC-4712') > TRUNC (SYSDATE)

 AND cii.accounting_class_code         = 'CUST_PROD'

 AND cii.owner_party_id             = hps.party_id

 AND hps.party_id                = hca.party_id(+)

 AND hps.party_site_id             = hpsu.party_site_id

 AND hpsu.site_use_type             = 'SHIP_TO'

 AND hpsu.status                = 'A'

 AND hca.status(+)               = 'A'

 AND hps.status                 = 'A'

 AND cii.location_type_code           = 'HZ_PARTY_SITES'

 AND cii.location_id              = hps.party_site_id(+)

 AND hps.location_id              = hl.location_id(+)

 AND cii.instance_status_id           = cis.instance_status_id(+)

 AND cii.inventory_item_id           = msi.inventory_item_id

 AND cii.last_vld_organization_id        = msi.organization_id

 AND mic.inventory_item_id           = msi.inventory_item_id

 AND mic.organization_id            = msi.organization_id

 AND mc.category_id               = mic.category_id

 AND mc.segment2 NOT LIKE '%ACCESSORY%'

 AND mic.category_set_id = 1

 AND cii.serial_number IS NOT NULL

 AND NOT EXISTS

  (SELECT 1

  FROM csi_ii_relationships

  WHERE subject_id      = cii.instance_id

  AND relationship_type_code = 'COMPONENT-OF'

  )

 ) QRSLT

WHERE (cust_account_id = 328627);

Note: Please pass bind variable as NULL.