This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Nov 12, 2012 8:24 PM by sb92075 RSS

Performance problem again 9i db

846231 Newbie
Currently Being Moderated
Hi all,

I am confused how to answer the users for their common question, What cause their batch job query to slow down?
The always claim they run it on the same manner . But sometimes it gets too slow and sometimes it is fast.

I am correct that they can get the answer to their questions if the run statspack report?


Thanks a lot,
Kins
  • 1. Re: Performance problem again 9i db
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Hi all,

    I am confused how to answer the users for their common question, What cause their batch job query to slow down?
    The always claim they run it on the same manner . But sometimes it gets too slow and sometimes it is fast.

    I am correct that they can get the answer to their questions if the run statspack report?
    if yes, post an example.
  • 2. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Here the example query :)
    CREATE OR REPLACE FORCE VIEW "JSCUS"."VW_USR_AR_CUST_OFFC_SLS_INVC" ("TRANSACTION_BRANCH", "BRANCH_ADDRESS_1", "BRANCH_ADDRESS_2", "BRANCH_PHONES", "BRANCH_FAX", "BRANCH_TIN", "TRX_NUMBER", "TRX_NUMBER_DISP", "TRX_DATE", "CREATION_DATE", "TRX_DATE_DISP", "ORDER_NUMBER", "CUSTOMER_ORDER_NUMBER", "PURCHASE_ORDER", "SALES_ORDER_DATE", "SALES_ORDER_DATE_DISP", "ORDER_REQUEST_DATE", "ORDER_REQUEST_DATE_DISP", "ORDER_DELIVERY_DATE", "ORDER_DELIVERY_DATE_DISP", "ORDER_TYPE", "SHIP_DATE_ACTUAL_DISP", "TERMS_NAME", "TERMS_NAME_DISP", "SALESPERSON_NAME", "SALESPERSON_PHONE", "LINE_TAX_CODE", "DOCUMENT_TYPE", "DOCUMENT_NAME", "BILL_TO_CUSTOMER_ID", "BILL_TO_CUSTOMER_NAME", "BILL_TO_TAX_REFERENCE", "SHIP_TO_LOCATION_ID", "SHIP_TO_NAME", "SHIP_TO_ADDRESS1", "SHIP_TO_ADDRESS2", "SHIP_TO_ADDRESS3", "SHIP_TO_ADDRESS4", "SHIP_TO_CITY", "TIN_LOCATION", "SHIP_TO_PROVINCE", "SHIP_TO_CITY_PROVINCE", "SHOW_DELIVERY_ADDRESS", "DELIVERY_TO_ADDRESS1", "DELIVERY_TO_ADDRESS2", "DELIVERY_TO_CITY_PROV",
      "LINE_SALES_ORDER", "LINE_NUMBER", "LINE_TYPE", "INVENTORY_ITEM_ID", "INVENTORY_ITEM", "LINE_DESCRIPTION", "ITEM_DESCRIPTION", "ITEM_ALT_DESCRIPTION", "ITEM_CUST_NUMBER", "UOM_CODE", "QUANTITY_INVOICED", "QUANTITY_ORDERED", "CASE_QUANTITY_INVOICED", "LN", "WD", "HT", "UNIT_CASE_VOLUME", "EXTENDED_CASE_VOLUME", "CUBIC_METER", "UNIT_STANDARD_PRICE", "UNIT_SELLING_PRICE", "LINE_AMOUNT", "GROSS_LINE_AMOUNT", "NET_TAX_LINE_AMOUNT", "REVENUE_AMOUNT", "GROSS_UNIT_SELLING_PRICE", "TAX_RATE", "UNIT_PRICE", "GROSS_UNIT_PRICE", "NET_TAX_UNIT_PRICE", "LINE_TAX_AMOUNT", "SALES_TAX_ID", "DISCOUNT", "DISCOUNT_STRING_DISP", "SEGMENT1", "ATTRIBUTE1", "ATTRIBUTE2", "ATTRIBUTE3", "A", "B", "SPEC_INSTR")
    AS
      SELECT
        -- CREATED BY: abc
        ----- header information -----
        DECODE(cust_trx.cust_trx_type_id,1,'Main Office','Cebu Branch') TRANSACTION_BRANCH ,
        DECODE(cust_trx.cust_trx_type_id,1,'abcr','Mandaue North Central, M.L. Quezon Avenue') BRANCH_ADDRESS_1 ,
        DECODE(cust_trx.cust_trx_type_id,1,' Pasig City 1605','Cabangcalan, Mandaue City, Cebu') BRANCH_ADDRESS_2 ,
        DECODE(cust_trx.cust_trx_type_id,1,'Tel.: 916-1111','yxz') BRANCH_PHONES ,
        DECODE(cust_trx.cust_trx_type_id,1,'yxz','xyz') BRANCH_FAX ,
        DECODE(cust_trx.cust_trx_type_id,1,'VAT Reg. TIN',' ') BRANCH_TIN ,
        cust_trx.trx_number TRX_NUMBER ,
        TO_CHAR(cust_trx.trx_number,'0000000') TRX_NUMBER_DISP ,
        cust_trx.trx_date TRX_DATE ,
        cust_trx.creation_date creation_date,
        TO_CHAR(cust_trx.trx_date,'mm/dd/yyyy') TRX_DATE_DISP ,
        trx_line.interface_line_attribute1 ORDER_NUMBER ,
        cust_trx.purchase_order CUSTOMER_ORDER_NUMBER ,
        cust_trx.purchase_order PURCHASE_ORDER ,
        trx_line.sales_order_date SALES_ORDER_DATE ,
        TO_CHAR(trx_line.sales_order_date,'MM/DD/YYYY') SALES_ORDER_DATE_DISP
        --,     TO_CHAR(oe_line.request_date,'MM/DD/YYYY') ORDER_REQUEST_DATE
        --,     TO_CHAR(oe_line.request_date,'MM/DD/YYYY') ORDER_REQUEST_DATE_DISP
        --,     oe_line.request_date ORDER_DELIVERY_DATE
        --,     TO_CHAR(oe_line.request_date,'MM/DD/YYYY') ORDER_DELIVERY_DATE_DISP
        --- NOTE : REQUEST DATE OR DELIVERY DATE OF HEADER AND LINE MAY BE DIFFERENT ----
        ,
        TO_CHAR(oe_header.request_date,'MM/DD/YYYY') ORDER_REQUEST_DATE ,
        TO_CHAR(oe_header.request_date,'MM/DD/YYYY') ORDER_REQUEST_DATE_DISP ,
        oe_header.request_date ORDER_DELIVERY_DATE ,
        TO_CHAR(oe_header.request_date,'MM/DD/YYYY') ORDER_DELIVERY_DATE_DISP ,
        trx_line.interface_line_attribute2 ORDER_TYPE ,
        TO_CHAR(cust_trx.ship_date_actual,'MM/DD/YYYY') SHIP_DATE_ACTUAL_DISP ,
        terms.name TERMS_NAME ,
        DECODE(terms.name, NULL, ' ', trim(terms.name)
        || ' day(s)') TERMS_NAME_DISP ,
        sales_rep.name SALESPERSON_NAME ,
        TO_CHAR(COALESCE(resources.source_phone,' ')) SALESPERSON_PHONE ,
        vat_tax.tax_code LINE_TAX_CODE ,
        'INV' DOCUMENT_TYPE ,
        'SALES INVOICE' DOCUMENT_NAME
        ----- customer information -----
        ,
        cust_trx.bill_to_customer_id BILL_TO_CUSTOMER_ID ,
        bill_cust.customer_name BILL_TO_CUSTOMER_NAME ,
        bill_cust.tax_reference BILL_TO_TAX_REFERENCE
        ----- customer address information -----
        ,
        ship_addr.location_id SHIP_TO_LOCATION_ID ,
        ship_addr.address_lines_phonetic SHIP_TO_NAME ,
        ship_addr.address1 SHIP_TO_ADDRESS1 ,
        ship_addr.address2 SHIP_TO_ADDRESS2 ,
        ship_addr.address3 SHIP_TO_ADDRESS3 ,
        ship_addr.address4 SHIP_TO_ADDRESS4 ,
        ship_addr.city ship_to_city ,
        ship_addr.attribute5 tin_location, -- for tin location of SVI
        ship_addr.province SHIP_TO_PROVINCE ,
        COALESCE(ship_addr.city, ship_addr.province) SHIP_TO_CITY_PROVINCE ,
        COALESCE(ship_addr.attribute3, 'N') SHOW_DELIVERY_ADDRESS ,
        jscus.FUNC_USR_GET_DELIVERY_ADDR1(trx_line.interface_line_attribute1) DELIVERY_TO_ADDRESS1 ,
        jscus.FUNC_USR_GET_DELIVERY_ADDR2(trx_line.interface_line_attribute1) DELIVERY_TO_ADDRESS2 ,
        jscus.FUNC_USR_GET_DELV_CITY_PROV(trx_line.interface_line_attribute1) DELIVERY_TO_CITY_PROV
        ----- line item information -----
        ,
        trx_line.sales_order LINE_SALES_ORDER ,
        trx_line.line_number LINE_NUMBER ,
        trx_line.line_type LINE_TYPE ,
        sys_item.inventory_item_id INVENTORY_ITEM_ID ,
        sys_item.segment1 INVENTORY_ITEM ,
        trx_line.description LINE_DESCRIPTION ,
        trx_line.description ITEM_DESCRIPTION ,
        jscus.func_usr_item_alt_desc(cust_trx.bill_to_customer_id, trx_line.inventory_item_id) ITEM_ALT_DESCRIPTION ,
        jscus.func_usr_item_cust_number(cust_trx.bill_to_customer_id, trx_line.inventory_item_id) ITEM_CUST_NUMBER
        ----- ordered item quantity information -----
        ,
        trx_line.uom_code UOM_CODE ,
        trx_line.quantity_invoiced QUANTITY_INVOICED ,
        trx_line.quantity_invoiced QUANTITY_ORDERED ,
        DECODE(trx_line.uom_code, 'CS', trx_line.quantity_invoiced, jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',to_date(oe_header.ordered_date)) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',to_date(oe_header.ordered_date))) case_quantity_invoiced,
        /*CASE
        WHEN trx_line.uom_code = 'CS'
        THEN trx_line.quantity_invoiced
        ELSE (jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',to_date(oe_header.ordered_date)) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',to_date(oe_header.ordered_date)))
        END CASE_QUANTITY_INVOICED , */
        sys_item.attribute1 LN ,
        sys_item.attribute2 WD ,
        sys_item.attribute3 HT ,
        COALESCE(((sys_item.attribute1                                 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0) UNIT_CASE_VOLUME ,
        DECODE(trx_line.uom_code, 'CS', COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0) * trx_line.quantity_invoiced, (jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',to_date(oe_header.ordered_date)) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',to_date(oe_header.ordered_date))) * COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0)) extended_case_volume,
        /*CASE
        WHEN trx_line.uom_code = 'CS'
        THEN COALESCE(((sys_item.attribute1                                                                                     * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0) * trx_line.quantity_invoiced
        ELSE (jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',to_date(oe_header.ordered_date)) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',to_date(oe_header.ordered_date))) * COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0)
        END EXTENDED_CASE_VOLUME ,*/
        to_number(TO_CHAR(DECODE(trx_line.uom_code, 'CS', COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0) * trx_line.quantity_invoiced, (jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',oe_header.ordered_date) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',oe_header.ordered_date)) * COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0))
        /*CASE
        WHEN trx_line.uom_code = 'CS'
        THEN COALESCE(((sys_item.attribute1                                                                                     * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0) * trx_line.quantity_invoiced
        ELSE (jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,trx_line.uom_code,'PC',to_date(oe_header.ordered_date)) * trx_line.quantity_invoiced / jscus.FUNC_USR_CONVERSION_RATE(sys_item.inventory_item_id,'CS','PC',to_date(oe_header.ordered_date))) * COALESCE(((sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000), 0)
        END */
        ,'999999.9999')) CUBIC_METER
        ----- ordered item amount information -----
        ,
        trx_line.unit_standard_price UNIT_STANDARD_PRICE ,
        trx_line.unit_selling_price UNIT_SELLING_PRICE ,
        COALESCE(trx_line.gross_extended_amount,trx_line.extended_amount) LINE_AMOUNT ,
        COALESCE(trx_line.gross_extended_amount,trx_line.extended_amount) GROSS_LINE_AMOUNT ,
        trx_line.extended_amount NET_TAX_LINE_AMOUNT ,
        trx_line.revenue_amount REVENUE_AMOUNT ,
        trx_line.gross_unit_selling_price GROSS_UNIT_SELLING_PRICE ,
        tax_line.tax_rate TAX_RATE ,
        to_number(REPLACE(trim(list_line.attribute1),',')) UNIT_PRICE ,
        TO_NUMBER(REPLACE(TRIM(LIST_LINE.ATTRIBUTE1),',')) GROSS_UNIT_PRICE ,
        trx_line.unit_selling_price / ( NVL(1-LIST_LINE.ATTRIBUTE2 ,1) * NVL(1-LIST_LINE.ATTRIBUTE3 ,1) * NVL(1-LIST_LINE.ATTRIBUTE4 ,1) * NVL(1-LIST_LINE.ATTRIBUTE5 ,1) * NVL(1-LIST_LINE.ATTRIBUTE6 ,1)) NET_TAX_UNIT_PRICE ,
        --( to_number(REPLACE(trim(list_line.attribute1),',')) / (1 + (COALESCE(tax_line.tax_rate, 0) / 100)) ) NET_TAX_UNIT_PRICE ,
        tax_line.extended_amount LINE_TAX_AMOUNT ,
        trx_line.sales_tax_id SALES_TAX_ID ,
        ( NVL(1                                    -LIST_LINE.ATTRIBUTE2 ,1) * NVL(1-LIST_LINE.ATTRIBUTE3 ,1) * NVL(1-LIST_LINE.ATTRIBUTE4 ,1) * NVL(1-LIST_LINE.ATTRIBUTE5 ,1) * NVL(1-LIST_LINE.ATTRIBUTE6 ,1) ) DISCOUNT ,
        COALESCE(trim(TO_CHAR(list_line.attribute2 * 100,'990.0')),' ')
        || nvl2(list_line.attribute2,'%','')
        || nvl2(list_line.attribute3, ',', '')
        || COALESCE(trim(TO_CHAR(list_line.attribute3 * 100,'990.0')),' ')
        || nvl2(list_line.attribute3,'%','')
        || nvl2(list_line.attribute4, ',', '')
        || COALESCE(trim(TO_CHAR(list_line.attribute4 * 100,'990.0')),' ')
        || nvl2(list_line.attribute4,'%','')
        || nvl2(list_line.attribute4, ',', '')
        || COALESCE(trim(TO_CHAR(list_line.attribute5 * 100,'990.0')),' ')
        || nvl2(list_line.attribute5,'%','')
        || nvl2(list_line.attribute5, ',', '')
        || COALESCE(trim(TO_CHAR(list_line.attribute6 * 100,'990.0')),' ')
        || nvl2(list_line.attribute6,'%','') DISCOUNT_STRING_DISP,
        sys_item.segment1,
        sys_item.attribute1,
        sys_item.attribute2,
        sys_item.attribute3,
        sys_item.attribute1  * sys_item.attribute2 * sys_item.attribute3 a,
        (sys_item.attribute1 * sys_item.attribute2 * sys_item.attribute3) / 1000000 b,
        oe_header.attribute16 spec_instr
      FROM apps.ra_customer_trx_all cust_trx ,
        apps.ra_customer_trx_lines_all tax_line ,
        apps.ra_customer_trx_lines_all trx_line ,
        apps.hr_all_organization_units org_unit ,
        apps.ra_customers bill_cust ,
        apps.oe_order_headers_all oe_header ,
        apps.oe_order_lines_all oe_line ,
        apps.ra_site_uses_all ship_site ,
        apps.ra_addresses_all ship_addr ,
        apps.ra_terms terms ,
        apps.ra_salesreps_all sales_rep ,
        apps.jtf_rs_defresources_vl resources ,
        apps.mtl_system_items_b sys_item ,
        apps.ar_vat_tax_all vat_tax ,
        apps.qp_list_lines_v list_line
      WHERE cust_trx.customer_trx_id         = trx_line.customer_trx_id
      AND trx_line.line_type                 = 'LINE'
      AND trx_line.customer_trx_line_id      = tax_line.link_to_cust_trx_line_id
      AND trx_line.customer_trx_id           = tax_line.customer_trx_id
      AND cust_trx.org_id                    = org_unit.organization_id
      AND cust_trx.bill_to_customer_id       = bill_cust.customer_id
      AND trx_line.interface_line_attribute6 = oe_line.line_id
      AND oe_header.header_id                = oe_line.header_id
      AND oe_header.order_number             = trx_line.interface_line_attribute1
      AND cust_trx.ship_to_site_use_id       = ship_site.site_use_id
      AND ship_site.address_id               = ship_addr.address_id
      AND cust_trx.term_id                   = terms.term_id(+)
      AND cust_trx.primary_salesrep_id       = sales_rep.salesrep_id(+)
      AND cust_trx.org_id                    = sales_rep.org_id(+)
      AND sales_rep.resource_id              = resources.resource_id(+)
      AND trx_line.inventory_item_id         = sys_item.inventory_item_id
      AND sys_item.organization_id           = 105
      AND trx_line.vat_tax_id                = vat_tax.vat_tax_id(+)
      AND (oe_line.inventory_item_id         = list_line.product_attr_value(+)
      AND oe_line.price_list_id              = list_line.list_header_id(+)
      AND oe_line.pricing_quantity_uom       = list_line.product_uom_code(+)
      AND OE_LINE.PRICING_DATE BETWEEN COALESCE(LIST_LINE.START_DATE_ACTIVE, OE_LINE.PRICING_DATE) AND COALESCE(LIST_LINE.END_DATE_ACTIVE, OE_LINE.PRICING_DATE) )
      AND trim(cust_trx.bill_to_customer_id) NOT IN
        -- Customers excluded in Sales Invoice
        ( '1042' 
        )
        --and oe_header.order_number = '10115049'
        --and oe_header.order_number = '10115409'
        --AND cust_trx.ct_reference = '10016712';;;;;;;;;;;;
    select      TRANSACTION_BRANCH
    ,     BRANCH_ADDRESS_1
    ,     BRANCH_ADDRESS_2
    ,     BRANCH_PHONES
    ,     BRANCH_FAX
    ,     BRANCH_TIN
    ,     TRX_NUMBER
    ,     TRX_NUMBER_DISP
    ,     TRX_DATE
    ,     TRX_DATE_DISP
    ,     BILL_TO_CUSTOMER_ID
    ,     BILL_TO_CUSTOMER_NAME
    ,     SHIP_DATE_ACTUAL_DISP
    ,     SHIP_TO_NAME
    ,     SHIP_TO_ADDRESS1
    ,     SHIP_TO_ADDRESS2
    ,     SHIP_TO_ADDRESS3
    ,     SHIP_TO_ADDRESS4
    ,     SHIP_TO_CITY_PROVINCE
    ,     ITEM_ALT_DESCRIPTION
    ,     ITEM_CUST_NUMBER
    ,     CUBIC_METER
    ,     INVENTORY_ITEM
    ,     ITEM_DESCRIPTION
    ,     LINE_NUMBER
    ,     LINE_DESCRIPTION
    ,     UOM_CODE
    ,     QUANTITY_ORDERED
    ,     QUANTITY_INVOICED
    ,     UNIT_STANDARD_PRICE
    ,     UNIT_SELLING_PRICE
    ,     LINE_SALES_ORDER
    ,     LINE_TYPE
    ,     NET_TAX_LINE_AMOUNT
    ,     REVENUE_AMOUNT
    ,     TAX_RATE
    ,     GROSS_UNIT_SELLING_PRICE
    ,     GROSS_LINE_AMOUNT
    ,     LINE_AMOUNT
    ,     LINE_TAX_AMOUNT
    ,     PURCHASE_ORDER
    ,     TERMS_NAME
    ,     SALES_TAX_ID
    ,     SALESPERSON_NAME
    ,     SALESPERSON_PHONE
    ,     CUSTOMER_ORDER_NUMBER
    ,     DISCOUNT_STRING_DISP
    ,     GROSS_UNIT_PRICE
    ,     UNIT_PRICE
    ,     NET_TAX_UNIT_PRICE
    ,     DOCUMENT_TYPE
    ,     DOCUMENT_NAME
    ,     SALES_ORDER_DATE
    ,     SALES_ORDER_DATE_DISP
    ,     LINE_TAX_CODE
    ,     ORDER_REQUEST_DATE
    ,       ORDER_NUMBER
    ,     BILL_TO_TAX_REFERENCE
    ,     SHOW_DELIVERY_ADDRESS
    ,     DELIVERY_TO_ADDRESS1
    ,     DELIVERY_TO_ADDRESS2
    ,     DELIVERY_TO_CITY_PROV
    ,     decode(tin_location,null,' ','-'||tin_location) tin_location
    ,     spec_instr
    from jscus.VW_USR_AR_CUST_OFFC_SLS_INVC
    where quantity_invoiced > 0
    and order_type = coalesce($P{OE_TRAN_TYPE},order_type)
    and trim(order_number) between coalesce($P{OE_START_ORDNBR},trim(order_number))
    and coalesce($P{OE_END_ORDNBR},$P{OE_START_ORDNBR},trim(order_number))
    
    and to_date(sales_order_date) between coalesce($P{OE_START_ORDDATE},to_date(sales_order_date))
    and coalesce($P{OE_END_ORDDATE},$P{OE_START_ORDDATE},to_date(sales_order_date))
    
    and to_date(order_delivery_date) between coalesce($P{OE_START_REQDATE},to_date(order_delivery_date))
    and coalesce($P{OE_END_REQDATE},$P{OE_START_REQDATE},to_date(order_delivery_date))
    
    order by trx_number
    ,     document_type
    ,     inventory_item
  • 3. Re: Performance problem again 9i db
    sb92075 Guru
    Currently Being Moderated
    nice SQL

    how to tell if it ran fast or slow & why it did so?
  • 4. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Thanks :)

    I told you by using statspack ;)

    I just want to validate my answer.

    I can not understand why it run slow and why it runs fast.

    They said they monitored CPU and MEMORY and there are still lots free.

    I just guess and told them that there are i/o contention on the disk. And lots of users accessing the same disk. Even if I did not see it
  • 5. Re: Performance problem again 9i db
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Thanks :)

    I told you by using statspack ;)
    STATPACK knows NOTHING about posted SQL.
  • 6. Re: Performance problem again 9i db
    rp0428 Guru
    Currently Being Moderated
    >
    I can not understand why it run slow and why it runs fast
    >
    Post the execution plan for the query. The plan usually has some useful information.
  • 7. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Thanks :)

    I been using 11.2.0.1 lately and I already forgot 9i :( .....Does it l have explain plan tools? same with 11.2?
  • 8. Re: Performance problem again 9i db
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Thanks :)

    I been using 11.2.0.1 lately and I already forgot 9i :( .....Does it l have explain plan tools? same with 11.2?
    why do you ask here?
    too lazy to even try it yourself?
    unwilling or incapable to Read The Fine Manuals?
  • 9. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Yes, I read it already (before)

    But if you are not using it for a long time you tend to forget :(
  • 10. Re: Performance problem again 9i db
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Yes, I read it already (before)

    But if you are not using it for a long time you tend to forget :(
    are you bragging or complaining?

    It sounds like a personal problem!
  • 11. Re: Performance problem again 9i db
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    statspack & AWR are not for troubleshooting individual queries. You troubleshoot poorly performing queries with other tools, like SQL real-time monitoring, dbms_xplan, extended SQL trace, ASH etc. It also helps to implement some sort of a service-level monitoring system on the application side to capture elapsed time of slow queries (possibly with other diagnostic information) and generate alerts.

    Best regards,
    Nikolay
  • 12. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Thanks,
    Statspack & AWR are not for troubleshooting individual queries. You troubleshoot poorly performing queries with other tools, like SQL real-time monitoring, dbms_xplan, extended SQL trace, ASH etc. It also helps to implement >some sort of a service-level monitoring system on the application side to capture elapsed time of slow queries (possibly with other diagnostic information) and generate alerts.
    But it is not poorly performing query because its has been running good for some times. It just that occasionally it gets slow. Maybe it has been affected by many "factors". And I just want to validate if this "factors" can be traced by Statspack. :)
  • 13. Re: Performance problem again 9i db
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    in principle it should be possible to obtain some additional information as to the root cause of such performance variations using statspack. You should start by scheduling a job to take statspack snapshots every hour or so. Then you can query statspack views to see if the observed change in performance correlates with a change in plan_hash_value and I/O stats.

    Best regards,
    Nikolay
  • 14. Re: Performance problem again 9i db
    846231 Newbie
    Currently Being Moderated
    Thanks Nik,

    Is there other effective/effecient way aside from Statspack? Based in your "actual" experience in 9i, How do you handle a situation when the users complained that the apps is performing slow?
    I usually tell them to bounced the database and the apps. and worst case restart the server machine to remove all zombie processes.... and sometimes it helped :)

    Edited by: KinsaKaUy? on 12-Nov-2012 05:21
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points