Forum Stats

  • 3,722,879 Users
  • 2,244,429 Discussions
  • 7,850,126 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Need to tune performance issue of a sql query

Yogendra Singh
Yogendra Singh Member Posts: 4 Red Ribbon

Hi,

I have a sql query which is causing performance issue for a specific customer. I am using standard oracle tables only to fetch the data. Can anybody please help in tuning attached query or else let me know if this could be something related to data issues for a specific customer.

For example: When i am providing cust_account_id as 328627 in attached query, it is retrieving 9987 rows in approx 16 sec while for cust_account_id as 1462371, it is retriving 8853 rows in approx 160 sec.


(Note: I am using SQL Developer to run attached query.)

Answers

  • evgenyg
    evgenyg Member Posts: 332 Bronze Badge

    I am not sure why you not starting from standard tools:

    1. Explain plan
    2. OWI

    It would be good idea to start from there.


    P.S. Why would someone download any file from an internet untrusted source?

  • Yogendra Singh
    Yogendra Singh Member Posts: 4 Red Ribbon

    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.

  • evgenyg
    evgenyg Member Posts: 332 Bronze Badge

    If the exe. plan "...looks fine .." then you most probably have no issue isn't it?

    Your query has very little use for me (format the query may be a good idea as well) as I don't have your tables set, your database, your machine. And I have no idea about DDL of tables that you use in the query.

    Explain plan is something that you get using dbms_xplan: https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS378

    And this is something very basic to start with. As well you need to provide a full 4 digit version of oracle you working with and OS.

  • jeffb1
    jeffb1 Member Posts: 172 Bronze Badge

    Is there a difference in the plan between the good and bad executions?

  • Yogendra Singh
    Yogendra Singh Member Posts: 4 Red Ribbon

    @jeffb1 , Not really. We couldn't see any differences in the plan between good and bad executions.

  • evgenyg
    evgenyg Member Posts: 332 Bronze Badge

    Between "good" and "bad" executions there is definitely a difference, but to find it you need to use tools provided by oracle.

    Check out awr/awrsqrpt/events. There is no place for guessing games. Most probably the execution time depends on the amount of data for specific input (you saying exe. plan's identical). so check out logical reads/physical reads between "good" and "bad" executions.

    As well, for example, possible that "good" execution running on the warm cache and "bad" on cold and therefore doing much more physical I/O (whatever it means today).

    The point that you have tools to find out so use them.

Sign In or Register to comment.