1 2 Previous Next 15 Replies Latest reply: Jun 7, 2014 5:39 AM by Saravanan Vijayasundaram RSS

    How to improve the performance of my query?

    4825af58-0f48-407d-8bf3-0c890ec124f6

      Can help someone , I have oracle 11g. I receive query its running slow taken 25-30 min time, i want to tune this query.Its using 4 table have index every table and primary key have Tables( ods_orders and ods_orderitems) and using inline view .we can not disable index b'coz tables using other application. please give me suggestion.......

      select .........(30 fields)..

      FROM

                   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

                  WHERE

                        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        = StrPartnerOCN_I            OR    ord.legal_customer_ocn = StrOCN_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 '%'||StrC_OrderRef_I||'%' OR osd.service_delivery_ord_ref LIKE '%'||StrC_OrderRef_I||'%')

                  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)

                  ORDER BY ord.order_signed_date desc, orderitem_number desc,

                           service_delivery_ord_ref desc, site_label desc

                )

           ) WHERE (ord_cnt <=MaxNoRecords_I OR MaxNoRecords_I IS NULL);

       

       

      Thanks,

      Dev

        • 1. Re: How to improve the performance of my query?
          Ratnesh Kumar Roy

          Hi,

           

          Can you please post the size of sga, pga and memory_target .

          • 2. Re: How to improve the performance of my query?
            Pleiadian

            Use as little like '%' || value || '%' as possible.

            It is killing for the performance in general as it can not be optimised with regular indexes, you have to use Indexing with Oracle Text

            • 3. Re: How to improve the performance of my query?
              4825af58-0f48-407d-8bf3-0c890ec124f6

              Hi,

              Size of max sga = 2605 mb

              pga  =0

              memory_target=5 gb

              • 4. Re: How to improve the performance of my query?
                Martin Preiss

                could you add the execution plan? The query contains multiple elements that make a selective access difficult (or impossible): the like conditions with a leading % and the or connected predicates (IS NULL). I would be surprised to see a selective access for the first table in the join process.

                • 5. Re: How to improve the performance of my query?
                  SomeoneElse

                  > I receive query its running slow taken 25-30 min time,

                   

                  How do you know that's slow?

                  • 6. Re: How to improve the performance of my query?
                    4825af58-0f48-407d-8bf3-0c890ec124f6

                    Hi Martin,

                     

                    ------------------------------------------------------------------------------------------------------------------------

                    | Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |

                    ------------------------------------------------------------------------------------------------------------------------

                    |   0 | SELECT STATEMENT                       |                               |   121 |   803K|   883   (1)| 00:00:11 |

                    |*  1 |  VIEW                                              |                               |   121 |   803K|   883   (1)| 00:00:11 |

                    |   2 |   WINDOW SORT                              |                               |   121 |   801K|   883   (1)| 00:00:11 |

                    |   3 |    VIEW                                             |                               |   121 |   801K|   882   (1)| 00:00:11 |

                    |   4 |     SORT ORDER BY                          |                               |   121 | 64130 |   882   (1)| 00:00:11 |

                    |   5 |      CONCATENATION                         |                               |       |       |            |          |

                    |*  6 |       FILTER                                        |                               |       |       |            |          |

                    |   7 |        NESTED LOOPS OUTER              |                               |     1 |   530 |   107   (0)| 00:00:02 |

                    |*  8 |         FILTER                                       |                               |       |       |            |          |

                    |   9 |          NESTED LOOPS OUTER            |                               |     1 |   502 |   105   (0)| 00:00:02 |

                    |* 10 |           FILTER                                    |                               |       |       |            |          |

                    |  11 |            NESTED LOOPS OUTER          |                               |     1 |   252 |   103   (0)| 00:00:02 |

                    |* 12 |             TABLE ACCESS BY INDEX ROWID| ODS_ORDERS                    |     1 |   132 |   101   (0)| 00:00:02 |

                    |* 13 |              INDEX RANGE SCAN          | IDX_ORD_STATUS                |  1249 |       |     6   (0)| 00:00:01 |

                    |  14 |             TABLE ACCESS BY INDEX ROWID| ODS_ORDERITEMS                |     1 |   120 |     2   (0)| 00:00:01 |

                    |* 15 |              INDEX RANGE SCAN          | IDX_OI_ORDER_ID               |     1 |       |     1   (0)| 00:00:01 |

                    |  16 |           TABLE ACCESS BY INDEX ROWID  | ODS_SERVICE_DELIVERY          |     1 |   250 |     2   (0)| 00:00:01 |

                    |* 17 |            INDEX RANGE SCAN            | IDX_OSD_ORDERITEM_ID          |     1 |       |     1   (0)| 00:00:01 |

                    |  18 |         TABLE ACCESS BY INDEX ROWID    | ODS_SITES                     |     2 |    56 |     2   (0)| 00:00:01 |

                    |* 19 |          INDEX RANGE SCAN              | IDX_SITE_SERVICE_DEL_ORDER_ID |     2 |       |     1   (0)| 00:00:01 |

                    |  20 |        NESTED LOOPS                        |                               |       |       |            |          |

                    |  21 |         NESTED LOOPS                      |                               |     1 |    32 |   143   (1)| 00:00:02 |

                    |* 22 |          TABLE ACCESS FULL             | CO_PARTNER_RELATIONSHIPS      |     1 |    19 |   141   (1)| 00:00:02 |

                    |* 23 |          INDEX UNIQUE SCAN             | SYS_C0025138                  |     1 |       |     1   (0)| 00:00:01 |

                    |* 24 |         TABLE ACCESS BY INDEX ROWID    | CO_LEGAL_CUSTOMERS            |     1 |    13 |     2   (0)| 00:00:01 |

                    |* 25 |       FILTER                                      |                               |       |       |            |          |

                    |* 26 |        HASH JOIN RIGHT OUTER           |                               |  2224 |  1151K|   774   (1)| 00:00:10 |

                    |  27 |         TABLE ACCESS FULL              | ODS_SITES                     |  7569 |   206K|   141   (1)| 00:00:02 |

                    |* 28 |         FILTER                                                    |                               |       |       |            |          |

                    |* 29 |          HASH JOIN OUTER               |                               |  1168 |   572K|   633   (1)| 00:00:08 |

                    |* 30 |           FILTER                            |                               |       |       |            |          |

                    |* 31 |            HASH JOIN OUTER             |                               |  1168 |   287K|   492   (1)| 00:00:06 |

                    |* 32 |             TABLE ACCESS FULL          | ODS_ORDERS                    |   984 |   126K|   281   (1)| 00:00:04 |

                    |  33 |             TABLE ACCESS FULL          | ODS_ORDERITEMS                | 23302 |  2730K|   210   (1)| 00:00:03 |

                    |  34 |           TABLE ACCESS FULL            | ODS_SERVICE_DELIVERY          |  3976 |   970K|   140   (0)| 00:00:02 |

                    |  35 |        NESTED LOOPS                    |                               |       |       |            |          |

                    |  36 |         NESTED LOOPS                   |                               |     1 |    32 |   143   (1)| 00:00:02 |

                    |* 37 |          TABLE ACCESS FULL             | CO_PARTNER_RELATIONSHIPS      |     1 |    19 |   141   (1)| 00:00:02 |

                    |* 38 |          INDEX UNIQUE SCAN             | SYS_C0025138                  |     1 |       |     1   (0)| 00:00:01 |

                    |* 39 |         TABLE ACCESS BY INDEX ROWID    | CO_LEGAL_CUSTOMERS            |     1 |    13 |     2   (0)| 00:00:01 |

                    ------------------------------------------------------------------------------------------------------------------------

                     

                    Predicate Information (identified by operation id):

                    ---------------------------------------------------

                     

                       1 - filter("ORD_CNT"<=TO_NUMBER(:MAXNORECORDS_I) OR :MAXNORECORDS_I IS NULL)

                       6 - filter( EXISTS (SELECT 0 FROM "CO_APPS"."CO_PARTNER_RELATIONSHIPS" "B","CO_APPS"."CO_LEGAL_CUSTOMERS"

                                  "A" WHERE "A"."LEGAL_CUSTOMER_PARTYID"="B"."LEGAL_CUSTOMER_PARTYID" AND

                                  SYS_OP_C2C("A"."LEGAL_CUSTOMER_OCN")=:B1 AND "B"."PARTNER_RELATIONSHIP_PARTNERID"=133867 AND "ROLE"='Super

                                  Partner') OR "ORD"."PARTNER_OCN"=SYS_OP_C2C(:STRPARTNEROCN_I))

                       8 - filter(("OSD"."INTERNAL_ORDER"=U'0' OR "OSD"."INTERNAL_ORDER" IS NULL) AND ("ORD"."ORDER_NUMBER" LIKE

                                  SYS_OP_C2C('%'||:STRC_ORDERREF_I||'%') OR "OSD"."SERVICE_DELIVERY_ORD_REF" LIKE

                                  SYS_OP_C2C('%'||:STRC_ORDERREF_I||'%') OR :STRC_ORDERREF_I IS NULL) AND ("OSD"."SERVICE_INSTANCE_ID" LIKE

                                  SYS_OP_C2C(:STRSERVICEID_I||'%') OR :STRSERVICEID_I IS NULL) AND ("OSD"."BILLING_ACCOUNT_NUMBER" LIKE

                                  SYS_OP_C2C(:STRBCNNUMBER_I||'%') OR :STRBCNNUMBER_I IS NULL) AND ("OSD"."SOLUTION_ID" LIKE

                                  '%'||:SOLUTIONID_I||'%' OR :SOLUTIONID_I IS NULL) AND ("OSD"."SERVICE_NAME" LIKE

                                  SYS_OP_C2C('%'||:SERVICENAME_I||'%') OR :SERVICENAME_I IS NULL) AND ("OSD"."SOLUTION_REFERENCE" LIKE

                                  '%'||:SOLUTION_REF_I||'%' OR :SOLUTION_REF_I IS NULL))

                      10 - filter(UPPER("OI"."PRODUCT_OFFERING") LIKE SYS_OP_C2C('%'||UPPER(:STRPRODUCTOFFERING_I)||'%') OR

                                  :STRPRODUCTOFFERING_I IS NULL)

                      12 - filter("ORD"."CLOSED_DATE">:V_ORDERSCLOSEDSINCE AND (:STRCUSTOMERORDERREF_I IS NULL OR

                                  "ORD"."CUSTOMER_REFERENCE" LIKE SYS_OP_C2C('%'||:STRCUSTOMERORDERREF_I||'%')) AND "ORD"."ORDER_NUMBER" IS NOT

                                  NULL)

                      13 - access("ORD"."STATUS"=U'CLOSED')

                      15 - access("ORD"."ORDER_ID"="OI"."ORDER_ID"(+))

                      17 - access("OI"."ORDERITEM_ID"="OSD"."ORDERITEM_ID"(+))

                      19 - access("OSD"."SERVICE_DELIVERY_ORDER_ID"="SITE"."SERVICE_DELIVERY_ORDER_ID"(+))

                      22 - filter("B"."PARTNER_RELATIONSHIP_PARTNERID"=133867 AND "ROLE"='Super Partner')

                      23 - access("A"."LEGAL_CUSTOMER_PARTYID"="B"."LEGAL_CUSTOMER_PARTYID")

                      24 - filter(SYS_OP_C2C("A"."LEGAL_CUSTOMER_OCN")=:B1)

                      25 - filter( EXISTS (SELECT 0 FROM "CO_APPS"."CO_PARTNER_RELATIONSHIPS" "B","CO_APPS"."CO_LEGAL_CUSTOMERS"

                                  "A" WHERE "A"."LEGAL_CUSTOMER_PARTYID"="B"."LEGAL_CUSTOMER_PARTYID" AND

                                  SYS_OP_C2C("A"."LEGAL_CUSTOMER_OCN")=:B1 AND "B"."PARTNER_RELATIONSHIP_PARTNERID"=133867 AND "ROLE"='Super

                                  Partner') OR "ORD"."PARTNER_OCN"=SYS_OP_C2C(:STRPARTNEROCN_I))

                      26 - access("OSD"."SERVICE_DELIVERY_ORDER_ID"="SITE"."SERVICE_DELIVERY_ORDER_ID"(+))

                      28 - filter(("OSD"."INTERNAL_ORDER"=U'0' OR "OSD"."INTERNAL_ORDER" IS NULL) AND ("ORD"."ORDER_NUMBER" LIKE

                                  SYS_OP_C2C('%'||:STRC_ORDERREF_I||'%') OR "OSD"."SERVICE_DELIVERY_ORD_REF" LIKE

                                  SYS_OP_C2C('%'||:STRC_ORDERREF_I||'%') OR :STRC_ORDERREF_I IS NULL) AND ("OSD"."SERVICE_INSTANCE_ID" LIKE

                                  SYS_OP_C2C(:STRSERVICEID_I||'%') OR :STRSERVICEID_I IS NULL) AND ("OSD"."BILLING_ACCOUNT_NUMBER" LIKE

                                  SYS_OP_C2C(:STRBCNNUMBER_I||'%') OR :STRBCNNUMBER_I IS NULL) AND ("OSD"."SOLUTION_ID" LIKE

                                  '%'||:SOLUTIONID_I||'%' OR :SOLUTIONID_I IS NULL) AND ("OSD"."SERVICE_NAME" LIKE

                                  SYS_OP_C2C('%'||:SERVICENAME_I||'%') OR :SERVICENAME_I IS NULL) AND ("OSD"."SOLUTION_REFERENCE" LIKE

                                  '%'||:SOLUTION_REF_I||'%' OR :SOLUTION_REF_I IS NULL))

                      29 - access("OI"."ORDERITEM_ID"="OSD"."ORDERITEM_ID"(+))

                      30 - filter(UPPER("OI"."PRODUCT_OFFERING") LIKE SYS_OP_C2C('%'||UPPER(:STRPRODUCTOFFERING_I)||'%') OR

                                  :STRPRODUCTOFFERING_I IS NULL)

                      31 - access("ORD"."ORDER_ID"="OI"."ORDER_ID"(+))

                      32 - filter((:STRCUSTOMERORDERREF_I IS NULL OR "ORD"."CUSTOMER_REFERENCE" LIKE

                                  SYS_OP_C2C('%'||:STRCUSTOMERORDERREF_I||'%')) AND ("ORD"."STATUS"=U'CANCELLED' OR "ORD"."STATUS"=U'Complete' OR

                                  "ORD"."STATUS"=U'INPROGRESS' OR "ORD"."STATUS"=U'Open') AND "ORD"."ORDER_NUMBER" IS NOT NULL AND

                                  (LNNVL("ORD"."STATUS"=U'CLOSED') OR LNNVL("ORD"."CLOSED_DATE">:V_ORDERSCLOSEDSINCE)))

                      37 - filter("B"."PARTNER_RELATIONSHIP_PARTNERID"=133867 AND "ROLE"='Super Partner')

                      38 - access("A"."LEGAL_CUSTOMER_PARTYID"="B"."LEGAL_CUSTOMER_PARTYID")

                      39 - filter(SYS_OP_C2C("A"."LEGAL_CUSTOMER_OCN")=:B1)

                    • 7. Re: How to improve the performance of my query?
                      Martin Preiss

                      in step 13 the optimizer expects the index access on IDX_ORD_STATUS with the predicate "ORD"."STATUS"=U'CLOSED' to return 1249 entries - and expects that only one row survives the filter in step 12:

                        12 - filter("ORD"."CLOSED_DATE">:V_ORDERSCLOSEDSINCE AND (:STRCUSTOMERORDERREF_I IS NULL OR

                                    "ORD"."CUSTOMER_REFERENCE" LIKE SYS_OP_C2C('%'||:STRCUSTOMERORDERREF_I||'%')) AND "ORD"."ORDER_NUMBER" IS NOT

                                    NULL)

                      Is this a good estimation? If it is not then the following Nested Loops joins may become expensive.

                       

                      In step 32 the optimizer calculates with 984 rows from ODS_ORDERS when filtering by:

                        32 - filter((:STRCUSTOMERORDERREF_I IS NULL OR "ORD"."CUSTOMER_REFERENCE" LIKE

                                    SYS_OP_C2C('%'||:STRCUSTOMERORDERREF_I||'%')) AND ("ORD"."STATUS"=U'CANCELLED' OR "ORD"."STATUS"=U'Complete' OR

                                    "ORD"."STATUS"=U'INPROGRESS' OR "ORD"."STATUS"=U'Open') AND "ORD"."ORDER_NUMBER" IS NOT NULL AND

                                    (LNNVL("ORD"."STATUS"=U'CLOSED') OR LNNVL("ORD"."CLOSED_DATE">:V_ORDERSCLOSEDSINCE)))

                      And again the question is if the assumption is somehow appropriate.

                       

                      With misleading cardinalities the CBO will create a suboptimal plan. And with the current information the optimizer does not expect this query to take much time.

                      • 8. Re: How to improve the performance of my query?
                        chris227

                        This seems not to be the real plan, as it shows only 11 seconds, but the expected one.

                         

                        However, one guess would be

                         

                        |* 30 |           FILTER                            |                               |       |       |            |          |

                        |* 31 |            HASH JOIN OUTER             |                               |  1168 |   287K|   492   (1)| 00:00:06 |

                        |* 32 |             TABLE ACCESS FULL          | ODS_ORDERS                    |   984 |   126K|   281   (1)| 00:00:04 |

                        |  33 |             TABLE ACCESS FULL          | ODS_ORDERITEMS                | 23302 |  2730K|   210   (1)| 00:00:03 |

                        |  34 |           TABLE ACCESS FULL            | ODS_SERVICE_DELIVERY          |  3976 |   970K|   140   (0)| 00:00:02 |

                        That might be a bottleneck.

                        We dont know anything about your indexes but the usage of upper here will refuse their usage on OI"."PRODUCT_OFFERING if there are any.

                         

                        30 - filter(UPPER("OI"."PRODUCT_OFFERING") LIKE SYS_OP_C2C('%'||UPPER(:STRPRODUCTOFFERING_I)||'%') OR

                                      :STRPRODUCTOFFERING_I IS NULL)

                        • 9. Re: How to improve the performance of my query?
                          Shatag

                          Hi,

                           

                          Try using,  NVL(Column,'X') = 'X'    instead "Column IS NULL"   where ever is applicable.

                          - This might inrease the performance..

                           

                          Shatag..

                          • 10. Re: How to improve the performance of my query?
                            Pleiadian

                            Shatag wrote:

                             

                            Hi,

                             

                            Try using,  NVL(Column,'X') = 'X'    instead "Column IS NULL"   where ever is applicable.

                            - This might inrease the performance..

                             

                            Shatag..

                             

                            And what happens when your data actually contains an 'X' and the query returns unexpected results...

                            • 11. Re: How to improve the performance of my query?
                              Saravanan Vijayasundaram

                              Hi Pleiadian,

                              As suggested by Shatag, 'X' is just an example.

                              If  you suspect that your data could have value 'X' then please replace it with any data that do not match with your actual data.

                               

                               

                              For example, NVL(Column,'1#2*') = '1#2*'

                               

                               

                              Regards,

                              Saravanan

                              • 12. Re: How to improve the performance of my query?
                                SomeoneElse

                                > Try using,  NVL(Column,'X') = 'X'    instead  "Column IS NULL"   where ever is applicable.

                                > - This might increase the performance..

                                 

                                I'd like to know how.

                                • 13. Re: How to improve the performance of my query?
                                  indra budiantho

                                  i focus on this :

                                   

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

                                                AS str2tbltype ))) ocn_tbl

                                   

                                  why is it not involved in the join condition?

                                   

                                  As my experience, if the query is called more than one time, it is better to put that  ocn_table in a General Temporary Table (GTT) and you can make primary key on the GTT also.

                                  • 14. Re: How to improve the performance of my query?
                                    Pleiadian

                                    Hi Saravanan,

                                     

                                    In my opinion its just a bug waiting to happen... I wouldn't want a developer make assumptions about the future of the data.

                                    Besides that, I don't see how nvl(column,'X') = 'X' is faster than column is null.

                                    1 2 Previous Next