1 2 Previous Next 21 Replies Latest reply: Nov 12, 2012 10:24 PM by sb92075 RSS

    Performance problem again 9i db

    846231
      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
          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
            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
              nice SQL

              how to tell if it ran fast or slow & why it did so?
              • 4. Re: Performance problem again 9i db
                846231
                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
                  KinsaKaUy? wrote:
                  Thanks :)

                  I told you by using statspack ;)
                  STATPACK knows NOTHING about posted SQL.
                  • 6. Re: Performance problem again 9i db
                    rp0428
                    >
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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