Production performance issues

From: Sandra Becker <sbecker6925_at_gmail.com>
Date: Thu, 30 Oct 2014 10:12:22 -0600
Message-ID: <CAJzM94B95Mqno=1uYOg9gu6RN=zG-+ji9-OXr4UQmsL0v8NFyA_at_mail.gmail.com>

Solaris 10
Oracle EE 11.2.0.2
Code in question written in Hibernate

Apparently we have been having performance issues--from a customer perspective--in a production database for the past 3 or 4 months. The DBA team wasn't informed until Friday last week. There will be a new product going live tomorrow on the same database and the "powers that be" want the performance issues fixed yesterday. We have identified one piece of code in particular that they are complaining about. Running it through SQL tuning through EM did not produce any recommendations. We were hoping someone smarter than use could see what might be done with the following query to improve performance.

We will continue working this and other queries, reviewing indexes, etc. Have already opened a ticket with Oracle support, but no response yet. Thanks in advance.

*select* this_.INVOICE_ID *as* INVOICE1_52_1_, this_.DIVISION_NAME *as*

DIVISION2_52_1_, this_.DOC_ID *as* DOC3_52_1_, this_.INSERT_TS *as*
INSERT4_52_1_, this_.INVOICE_STATE *as* INVOICE5_52_1_, this_.NOTES *as*
NOTES52_1_, this_.ODAP_OWNER *as* ODAP7_52_1_, this_.ODAP_STATE *as*
ODAP8_52_1_, this_.PARTITION_KEY *as* PARTITION9_52_1_, this_.PAYER_IDENT

*as* PAYER10_52_1_, this_.PROVIDER_EID *as* PROVIDER11_52_1_, this_.STATUS
*as* STATUS52_1_, this_.SUPPLIER_DIVISION_IDS *as* SUPPLIER13_52_1_,
this_.SUPPLIER_EID *as* SUPPLIER14_52_1_, this_.SUPPLIER_ROOT_EID *as* SUPPLIER15_52_1_, this_.TJVM_CALLBACK_URL *as* TJVM16_52_1_, this_.TJVM_VERSION_CREATION_TS *as* TJVM17_52_1_, this_.*TYPE* *as* TYPE52_1_, this_.UPDATE_TS *as* UPDATE19_52_1_, this_.VENDOR_NAME *as* VENDOR20_52_1_, invoicehea2_.INVOICE_ID *as* INVOICE1_59_0_, invoicehea2_.ALT_INVOICE_NUM *as* ALT2_59_0_, invoicehea2_.ASN_NUMBER *as* ASN3_59_0_, invoicehea2_.CURRENCY *as* CURRENCY59_0_, invoicehea2_.DISC_AMT
*as* DISC5_59_0_, invoicehea2_.DISC_PAYMENT_DUE_DATE *as* DISC6_59_0_,
invoicehea2_.DISC_PCT *as* DISC7_59_0_, invoicehea2_.FILE_GENERATED_DATE
*as* FILE8_59_0_, invoicehea2_.GHX_ORDER_NUM *as* GHX9_59_0_,
invoicehea2_.INSERT_TS *as* INSERT10_59_0_, invoicehea2_.INVOICE_AMT *as* INVOICE11_59_0_, invoicehea2_.INVOICE_DATE *as* INVOICE12_59_0_, invoicehea2_.INVOICE_NUM *as* INVOICE13_59_0_, invoicehea2_.INVOICE_TYPE
*as* INVOICE14_59_0_, invoicehea2_.LATEST_MESSAGE *as* LATEST15_59_0_,
invoicehea2_.LINE_COUNT *as* LINE16_59_0_, invoicehea2_.NET_AMT_DUE *as* NET17_59_0_, invoicehea2_.ORDER_NUM *as* ORDER18_59_0_,

invoicehea2_.PAYMENT_DUE_DATE *as* PAYMENT19_59_0_,
invoicehea2_.PAYMENT_TERMS_NOTE *as* PAYMENT20_59_0_,
invoicehea2_.SPECIAL_CHARGES *as* SPECIAL21_59_0_, invoicehea2_.TAX_TOTAL

*as* TAX22_59_0_, invoicehea2_.TERMS *as* TERMS59_0_,
invoicehea2_.UPDATE_TS *as* UPDATE24_59_0_
*from* INVOICE this_ left outer *join* INVOICE_HEADER invoicehea2_ *on*
this_.INVOICE_ID=invoicehea2_.INVOICE_ID
*where* (lower(this_.SUPPLIER_EID)=:1 *and* this_.INVOICE_ID *in*
(*select* this_.INVOICE_ID *as* y0_
*from* INVOICE this_ inner *join* INVOICE_HEADER invoicehea1_ *on*
this_.INVOICE_ID=invoicehea1_.INVOICE_ID
*where* invoicehea1_.INVOICE_DATE>:2 )) *and* ((this_.INVOICE_ID *in*
(*select* this_.INVOICE_ID *as* y0_
*from* INVOICE this_)))

*Execution plan*

   P_ID ID OPERATION
OBJECT_NAME COST card KBYTES TEMP_SPC

• ---- ---------------------------------------- -------------------- --------- ------- --------- ---------

<Null>     0 SELECT STATEMENT
<Null>                  122016 <Null>  <Null>    <Null>

      0    1   VIEW
VM_NWVW_2               122016   21865    133240 <Null>

      1    2     HASH UNIQUE
<Null>                  122016   21865      6747   8356000

      2    3       NESTED LOOPS OUTER
<Null>                  121086   21865      6747 <Null>

      3    4         NESTED LOOPS
    <Null>                   77322   21860      4334 <Null>

      4    5           INDEX FAST FULL SCAN
INVOICE_HEADER_INVID     55318   21860       320 <Null>

                                                      _INVDT



      4    6           TABLE ACCESS BY GLOBAL INDEX R
INVOICE                      2       1         0 <Null>

      6    7             INDEX UNIQUE SCAN
PK_INVOICE                   1       1 <Null>    <Null>

      3    8         TABLE ACCESS BY GLOBAL INDEX ROW
INVOICE_HEADER               2       1         0 <Null>

      8    9           INDEX UNIQUE SCAN
PK_INVOICE_HEADER            1       1 <Null>    <Null>





*Indexes*UNIQUENES INDEX_NAME                           POS

COLUMN_NAME                           CLF   TBL_ROWS  dist_keys
--------- ----------------------------------- ----
------------------------------ ---------- ---------- ----------
UNIQUE    PK_INVOICE                             1
INVOICE_ID                        8001900   26444703   26646447

UNIQUE    PK_INVOICE_HEADER                      1
INVOICE_ID                        7530930   26450480   25824300

NONUNIQUE INVOICE_HEADER_INVID_INVDT             1
INVOICE_ID                        7865133   26450480   26491680
NONUNIQUE                                        2
INVOICE_DATE                      7865133   26450480   26491680

Sandy
GHX

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 30 2014 - 17:12:22 CET