Re: Production performance issues
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: sbecker6925@xxxxxxxxx
- Date: Thu, 30 Oct 2014 18:01:31 +0000
Sandy
I know, Hibernate generated.
Given that INVOICE_ID is apparently the PK in both tables, why do we need
to join these tables to themselves?
You appear to have (aliases changed for clarity)
select ...
from invoice inv
LEFT OUTER join invoice_header invhdr2 on invhdr2.invoice_id =
inv.invoice_id
where (lower(inv.supplier_eid) = : 1
and inv.invoice_id in (
select inv1.invoice_id
from invoice inv1
INNER join invoice_header invhdr1 on invhdr1.invoice_id =
inv1.invoice_id
where invhdr1.invoice_date > :2)
)
and ((inv.invoice_id in
(
select invoice_id
from invoice
)))
The last subquery appears to add no value.
The second subquery could be combined with the first. If there is an
invoice which joins to an invoice header for header date > :2, then the
same header MUST the one you are finding in the main query. And the left
outer join in the main query is therefore useless, because you know from
the subquery that only rows that match on a regular INNER join on
invoice_id are included.
So I think your query could be transformed to this much simpler logically
equivalent form without changing its results:
select ...
from invoice inv
INNER join invoice_header invhdr2 on invhdr2.invoice_id = inv.invoice_id
where lower(inv.supplier_eid) = : 1
and invhdr2.invoice_date > :2
Options are therefore:
1) simplify the Hibernate query, however that is defined
2) see if you can get Oracle to substitute the simple query for the complex
one (steps back, waves hands hopefully - I've read about this but never
actually tried it)
Of course CBO may already have worked all this out for itself... but if so
I don't see why it would need 3 index scans for just two tables - and it
wouldn't need the HASH UNIQUE. It could just go for
INVOICE_HEADER_INVID_INVDT, go to PK_INVOICE and then INVOICE to check
supplier_eid and finally get select columns from the INVOICE_HEADER table.
NB - if PK_INVOICE_HEADER is unique, by definition
INVOICE_HEADER_INVID_INVDT is also unique. Maybe declaring it so would give
the CBO another useful clue.
HTH
Nigel
On 30 October 2014 17:16, Sandra Becker <sbecker6925@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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@xxxxxxxxx>
>> *To:* oracle-l <oracle-l@xxxxxxxxxxxxx>
>> *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
>
Other related posts:
