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
