3 Replies Latest reply: Jun 4, 2014 2:58 AM by Srini Chavali-Oracle RSS

    My query is running very slow.....


      I am using 11gr2 our query performance is very poor, can i use "HINTS" if use hints then what type hints use....



             SELECT .......


                   ods_orders           ord,

                   ods_orderitems       oi,

                   ods_service_delivery osd,

                   ods_sites            site

                    (SELECT column_value AS ocn_no FROM TABLE(CAST(str2tbl(StrOCN_I)

                    AS str2tbltype ))) ocn_tbl


                        ord.order_id                  = oi.order_id(+)

                  AND   ord.order_number is not null

                  AND   oi.orderitem_id               = osd.orderitem_id(+)

                  AND   osd.service_delivery_order_id = site.service_delivery_order_id(+)

                  AND   (ord.partner_ocn        in (select a.legal_customer_Ocn from  co_apps.CO_LEGAL_CUSTOMERS a, co_apps.CO_PARTNER_RELATIONSHIPS b

                 WHERE   a.LEGAL_CUSTOMER_PARTYID = b.LEGAL_CUSTOMER_PARTYID and role='Super Partner' and b.PARTNER_RELATIONSHIP_PARTNERID=partnerPartyId) OR ord.partner_ocn = StrPartnerOCN_I)

                  AND   (osd.internal_order  = '0'  or osd.internal_order IS NULL)           

                  AND   (ord.customer_reference  LIKE  '%'||StrCustomerOrderRef_I||'%' OR StrCustomerOrderRef_I IS NULL)

                  AND   (ord.order_number LIKE '%'||StrCOLTOrderRef_I||'%' OR osd.service_delivery_ord_ref LIKE '%'||StrCOLTOrderRef_I||'%' OR StrCOLTOrderRef_I IS NULL)

                  AND   (osd.service_instance_id LIKE StrServiceId_I||'%' OR StrServiceId_I IS NULL)

                  AND   (upper(oi.product_offering) LIKE '%'||upper(StrProductOffering_I)||'%'  OR StrProductOffering_I IS NULL)

                  and   (osd.billing_account_number like :strbcnnumber_i||'%' or :strbcnnumber_i is null)

                  AND   (ord.status IN ('INPROGRESS','CANCELLED','Complete','Open') OR (ord.status ='CLOSED' AND ord.closed_date >v_OrdersClosedSince))

                  AND   (osd.solution_id LIKE '%'||SolutionId_I||'%' OR SolutionId_I IS NULL)           

                  and   (osd.service_name like '%'||servicename_i||'%' or servicename_i is null)           

              and (osd.solution_reference like '%'||solution_ref_i||'%' or solution_ref_i is null)



           WHERE (ord_cnt <=MaxNoRecords_I OR MaxNoRecords_I IS NULL)