7 Replies Latest reply on Apr 3, 2016 6:25 AM by Bashar.

    Hi CPU usage

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.2.4

      OL6.6

      11gR2

       

      The user is always complaining about slow performance.

      I check the top monitor and I see lots of hi cpu bound programs.

      How can I speed up these programs?

      Do I need to create more indexes on the table concerned?

       

      top - 13:48:46 up 6 days, 23:15,  2 users,  load average: 9.50, 10.22, 9.94

      Tasks: 575 total,   6 running, 569 sleeping,   0 stopped,   0 zombie

      Cpu(s): 54.7%us, 12.4%sy,  0.0%ni, 25.8%id,  5.1%wa,  0.0%hi,  0.1%si,  1.8%st

      Mem:  24658280k total, 18222004k used,  6436276k free,     2524k buffers

      Swap: 37320760k total,  1424784k used, 35895976k free, 12764308k cached

       

       

        PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

      3993 oraprod   20   0 3355m 353m 318m R 100.0 1.5   4:01.08 oracle

      2864 oraprod   20   0 3371m 506m 451m R 100.0 2.1  37:49.74 oracle

      16444 oraprod  20   0 3371m 792m 734m R 100.0 3.3  40:57.25 oracle

      23123 oraprod  20   0 3319m 1.2g 1.2g R 99.0  5.3   3071:18 oracle

      1615 oraprod   20   0 3308m  17m  16m S 56.2  0.1   6809:38 oracle

      7933 oraprod   20   0 3323m 238m 225m D 55.6  1.0   0:23.29 oracle

      1667 oraprod   20   0 3309m  33m  33m S 43.1  0.1   4589:37 oracle

      5769 oraprod   20   0 3310m  36m  32m S 39.1  0.1   6:40.46 oracle

      1581 oraprod   20   0 3360m 1.5g 1.4g S 11.2  6.2 444:45.54 oracle

      3511 oraprod   20   0 3308m  24m  23m S  8.2  0.1   1176:43 oracle

      3523 oraprod   20   0 3308m  24m  24m S  8.2  0.1   1174:50 oracle

       

       

       

      Kind regards,

      jc

        • 1. Re: Hi CPU usage
          Kirushna

          DO you have any long running Concurrent Requests/programs?

           

          Any info on these PID.

          23123 oraprod  20   0 3319m 1.2g 1.2g R 99.0  5.3   3071:18 oracle

          1615 oraprod   20   0 3308m  17m  16m S 56.2  0.1   6809:38 oracle

          1667 oraprod   20   0 3309m  33m  33m S 43.1  0.1   4589:37 oracle

          1581 oraprod   20   0 3360m 1.5g 1.4g S 11.2  6.2 444:45.54 oracle

          3511 oraprod   20   0 3308m  24m  23m S  8.2  0.1   1176:43 oracle

          3523 oraprod   20   0 3308m  24m  24m S  8.2  0.1   1174:50 oracle

          2864 oraprod   20   0 3371m 506m 451m R 100.0 2.1  37:49.74 oracle

          16444 oraprod  20   0 3371m 792m 734m R 100.0 3.3  40:57.25 oracle

          1 person found this helpful
          • 2. Re: Hi CPU usage
            Beauty_and_dBest

            Yes, but I want to identify which of them is the one in the "top" monitor

            • 3. Re: Hi CPU usage
              Kirushna

              Check this below document.


              Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running. (Doc ID 735119.1)


              Regards,

              Kirushna

              1 person found this helpful
              • 4. Re: Hi CPU usage
                Beauty_and_dBest

                Hi Kir and ALL,

                 

                I check the sql that has 100% resources and it is :

                 

                select order_number order_number,      warehouse_id warehouse_id from jscus.vw_usr_oe_sales_orders_1 where to_date(request_date) between to_date(:pstart_date) and to_date(sysdate + 20) and line_flow_status_code not in ('CLOSED','CANCELLED') and cancelled_flag <> '1' and line_booked_flag = 'Y' and (generate_flag is null or upper(generate_flag) = 'N') and received_flag is null group by order_number,warehouse_id

                 

                MERGE /*+ use_hash(xal) */ INTO XLA_AE_LINES_GT XAL USING XLA_AE_HEADERS_GT XAH ON ( XAL.EVENT_ID = XAH.EVENT_ID AND XAL.LEDGER_ID = XAH.LEDGER_ID AND XAL.BALANCE_TYPE_CODE = XAH.BALANCE_TYPE_CODE AND XAL.HEADER_NUM = NVL(XAH.HEADER_NUM,0) ) WHEN MATCHED THEN UPDATE SET XAL.AE_HEADER_ID = XAH.AE_HEADER_ID , XAL.REF_AE_HEADER_ID = DECODE( XAL.REF_EVENT_ID,NULL,XAH.AE_HEADER_ID,XAL.REF_AE_HEADER_ID ) , XAL.MERGE_INDEX = CASE XAL.ACCOUNTING_CLASS_CODE WHEN 'DUMMY_EXCHANGE_GAIN_LOSS_DUMMY' THEN -1 ELSE CASE NVL(XAL.CODE_COMBINATION_ID,0) WHEN -1 THEN XAL.TEMP_LINE_NUM ELSE CASE NVL( XAL.ALT_CODE_COMBINATION_ID,0 ) WHEN -1 THEN XAL.TEMP_LINE_NUM ELSE CASE XAL.MERGE_DUPLICATE_CODE WHEN 'A' THEN CASE XAL.SWITCH_SIDE_FLAG WHEN 'Y' THEN -1 ELSE CASE WHEN XAL.UNROUNDED_ACCOUNTED_CR IS NULL THEN -2 ELSE -3 END END WHEN 'W' THEN CASE WHEN XAL.UNROUNDED_ACCOUNTED_CR IS NULL THEN -2 ELSE -3 END END END END END

                 

                Can you help how to tune or rewrite the sql please

                Do I need to create function index in upper(generate_flag)?

                I read also that using "not in" is a big NO NO?

                Is there a tuning advisor in 11g?

                Does explain plan helps?

                Does AWR,ADDM helps?

                 

                Can you share me some of tuning tips please.

                 

                Kind regards,

                • 5. Re: Hi CPU usage
                  Bashar.

                  These are two SQLs!

                  Which one causes the high CPU usage?

                   

                  Can you generate the plan for the first query and post the output?

                  Please post the query of the view "jscus.vw_usr_oe_sales_orders_1".

                   

                  Regards,

                  Bashar

                  1 person found this helpful
                  • 6. Re: Hi CPU usage
                    Beauty_and_dBest

                    Hi Bashar,

                     

                    Both programs consume much CPU.

                     

                    SQL> select text from user_views where view_name='VW_USR_OE_SALES_ORDERS_1';

                    TEXT

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

                    SELECT oe_header.header_id ,

                        oe_header.order_number ,

                        oe_header.ordered_date ,

                        hz_cust.cust_account_id customer_id ,

                        oe_header.request_date ,

                        oe_lines.line_id ,

                        oe_lines.inventory_item_id ,

                        coalesce(to_number(mtl_items.attribute11), mtl

                    _items.inventory_item_id) old_inventory_item_id,

                        oe_lines.ordered_quantity ,

                        oe_lines.order_quantity_uom ,

                        hz_loc_ship.attribute1 solomon_shiptoid ,

                        oe_header.order_type_id transaction_type_id ,

                        oe_header.order_type_id ,

                        tran_type.name transaction_type_name ,

                        oe_header.org_id company_id ,

                        --oe_lines.ship_from_org_id warehouse_id ,

                        mtl_param.attribute6 warehouse_id,

                        oe_lines.ship_from_org_id new_warehouse_id,

                        COALESCE(hz_loc_ship.address1, ' ') ship_address_1 ,

                        COALESCE(hz_loc_ship.address2, ' ') ship_address_2 ,

                        COALESCE(hz_loc_ship.address3, ' ') ship_address_3 ,

                        COALESCE(hz_loc_ship.address4, ' ') ship_address_4 ,

                        COALESCE(hz_loc_ship.city, ' ') ship_city ,

                        mtl_items.segment1 inventory_code ,

                        mtl_items.description item_description ,

                        hz_loc_ship.location_id ship_address_id,

                        hz_loc_ship.location_id address_id,

                        oe_lines.creation_date created_date ,

                        oe_lines.last_update_date update_date ,

                        ((mtl_items.unit_length     * mtl_items.unit_h

                    eight* mtl_items.unit_width)/1000000) * oe_lines.p

                    ricing_quantity cbm ,

                        oe_lines.unit_selling_price * oe_lines.ordered

                    _quantity line_amount ,

                        oe_lines.attribute19 remarks ,

                        COALESCE(hz_party_site_ship.party_site_name, h

                    z_acct_site_ship.attribute1) shipname ,

                        --COALESCE(hz_loc_ship.address_lines_phonetic,

                    hz_loc_ship.attribute1) shipname ,

                        oe_header.cust_po_number customer_po_number ,

                        oe_header.cancelled_flag ,

                        oe_lines.booked_flag line_booked_flag ,

                        oe_lines.flow_status_code line_flow_status_code ,

                        oe_lines.attribute18 generate_flag ,

                        oe_lines.attribute17 received_flag

                      FROM apps.oe_order_headers_all oe_header ,

                        apps.oe_order_lines_all oe_lines ,

                        apps.hz_cust_accounts hz_cust,

                        apps.hz_parties hz_party,

                        apps.hz_cust_acct_sites_all hz_acct_site_ship,

                        apps.hz_cust_site_uses_all hz_site_use_ship,

                        apps.hz_party_sites hz_party_site_ship,

                        apps.hz_locations hz_loc_ship,

                        apps.oe_transaction_types_tl tran_type ,

                        apps.mtl_system_items_b mtl_items,

                        apps.mtl_parameters mtl_param

                      WHERE oe_header.header_id               = oe_lines.header_Id

                      AND oe_header.sold_to_org_id            = hz_cust.cust_account_id

                      AND hz_cust.party_id                    = hz_party.party_id

                      AND oe_header.ship_to_org_id            = hz_site_use_ship.site_use_id

                      AND hz_acct_site_ship.cust_acct_site_id = hz_site_use_ship.cust_acct_site_id

                      AND hz_acct_site_ship.party_site_id     = hz_party_site_ship.party_site_id

                      AND hz_party_site_ship.location_id      = hz_loc_ship.location_id

                      AND oe_header.order_type_id             = tran_type.transaction_type_id

                      AND oe_lines.inventory_item_id          = mtl_items.inventory_item_id

                      AND oe_lines.ship_from_org_id           = mtl_items.organization_id

                      AND mtl_items.organization_id           = mtl_param.organization_id

                        --AND ra_cust.status                = 'A'

                        --AND ra_address.status             = 'A'

                      and oe_header.order_category_code = 'MIXED'

                        --and oe_header.order_number = '10192953';;;;;

                    ;;;;

                    • 7. Re: Hi CPU usage
                      Bashar.

                      Please generate the execution plan as requested before and post it for review.

                       

                      Regards,

                      Bashar

                      1 person found this helpful