Re: Production performance issues
Date: Thu, 30 Oct 2014 11:16:49 -0600
Message-ID: <CAJzM94CnQB5=GGb+Nr_sp7U_pQ1jLwsAeRgfDn8bO2=r2DZ8DA_at_mail.gmail.com>
Thanks for the information. Support still hasn't responded. Seems we have a function based index on lower(supplier_eid), but it's a single column index and when it is used, the cost skyrockets to more than 10 times what it is currently. Still looking into why that would be the case. We do have the option of creating a new index if we it will improve performance. Prefer not to do that just yet.
Sandy
On Thu, Oct 30, 2014 at 11:02 AM, Job Miller <jobmiller_at_yahoo.com> wrote:
> support is likely going to ask that you use SQLT XTRACT to collect the
> details.
>
> it includes the plan with row source, all the object stats, and your
> various init.ora, the SQL Monitor report, etc..
>
> everything a tuner would need in one easy/quick place.
>
> The lower(this_.SUPPLIER_EID)=:1 is not a good practice, because it
> eliminates the possibility of an index unless you have a function based
> index on that expression or you have expression statistics.
>
> Job
>
> ------------------------------
> *From:* Sandra Becker <sbecker6925_at_gmail.com>
> *To:* oracle-l <oracle-l_at_freelists.org>
> *Sent:* Thursday, October 30, 2014 12:12 PM
> *Subject:* Production performance issues
>
> 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
>
>
>
-- Sandy GHX -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 30 2014 - 18:16:49 CET