Production performance issues
- From: Sandra Becker <sbecker6925@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Oct 2014 10:12:22 -0600
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
Other related posts:
