Home » RDBMS Server » Performance Tuning » Wrong plan chosen by optimizer (Oracle 11.1.0.6 , OEL 6)
Wrong plan chosen by optimizer [message #669885] |
Tue, 22 May 2018 12:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I'm seeing the following plan when I do this query
delete from MY_VIEW t where t.IDENTIFIER2 = 3693094;
--
I get a full scan on one of the tables of the view.
Execution Plan
----------------------------------------------------------
Plan hash value: 3618360267
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 10 | 950 | | 52399 (1)| 00:10:29 |
| 1 | DELETE | MY_VIEW | | | | | |
| 2 | VIEW | MY_VIEW | 10 | 950 | | 52399 (1)| 00:10:29 |
| 3 | MERGE JOIN | | 10 | 780 | | 52399 (1)| 00:10:29 |
| 4 | SORT JOIN | | 4216K| 156M| | 52396 (1)| 00:10:29 |
| 5 | VIEW | | 4216K| 156M| | 52396 (1)| 00:10:29 |
| 6 | SORT GROUP BY | | 4216K| 68M| 273M| 52396 (1)| 00:10:29 |
| 7 | TABLE ACCESS FULL | TAB2 | 10M| 165M| | 17479 (1)| 00:03:30 |
|* 8 | SORT JOIN | | 10 | 390 | | 3 (34)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| TAB1 | 10 | 390 | | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | TAB1_IND1 | 11 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("BBB_S"."IDENTIFIER2"="AAA"."TAB1_ISN")
filter("BBB_S"."IDENTIFIER2"="AAA"."TAB1_ISN")
9 - filter("AAA"."AAA_DELETED"=0)
10 - access("AAA"."IDENTIFIER2"=3693094)
Statistics
----------------------------------------------------------
210 recursive calls
39 db block gets
64183 consistent gets
51113 physical reads
0 redo size
572 bytes sent via SQL*Net to client
538 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
0 rows processed
here is the code of this view:
create or replace view MY_VIEW as
--
select BBB.IDENTIFIER2
,sum(BBB.BBB_AMOUNT) as AAA_AMOUNT
,sum(BBB.BBB_CURRENCY_AMOUNT) as AAA_CURRENCY_AMOUNT,
AAA.TAB1_ISN
,AAA.AAA_TYPE
,AAA.IDENTIFIER2
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,0 as TDIL_ATTACH_ADJUSTMENT_TO
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY
from TAB1 AAA
join TAB2 BBB
on BBB.IDENTIFIER2 = AAA.TAB1_ISN and AAA.AAA_DELETED = 0
GROUP BY AAA.TAB1_ISN,
AAA.AAA_TYPE
,AAA.IDENTIFIER2
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY,
BBB.IDENTIFIER2
what actually happens is that a trigger fires when trying to delete from this view... here is the trigger's code:
create or replace trigger "TRG_DELETE"
instead of delete on MY_VIEW
for each row
declare
seqno number;
begin
select decode(:old.AAA_object_type,
'TYPE1 ITEM',
nvl((select TAB3.TAB3_deleted
from TAB4 MYTAB4, TAB3
where MYTAB4.MYTAB4_HOUSE_ISN = :old.IDENTIFIER2
and TAB3.TAB3_fwbm_serial_no = MYTAB4.MTAB_master_isn),
(select TAB3.TAB3_deleted
from TAB3
where TAB3.TAB3_some_serial_no = :old.IDENTIFIER2)),
'TYPE2 ITEM',
(select TAB5.TAB5_deleted
from TAB5
where TAB5.TAB5_serial_no1 = :old.IDENTIFIER2),
'SOME TYPE',
(select non_deleted
from non_non_SOME_data
where non_object_isn = :old.IDENTIFIER2),
0)
into seqno
from dual;
if seqno = 0 then
update TAB1
set AAA_deleted = 1
where TAB1_isn = :old.TAB1_isn;
select seq_generic.nextval into seqno from dual;
if (:old.AAA_amount <> 0 or :old.AAA_currency_amount <> 0) then
insert into TAB2
(TAB2_isn,
IDENTIFIER2,
BBB_transaction_datetime,
BBB_amount,
BBB_currency,
BBB_currency_rate,
BBB_currency_amount)
values
(seqno,
:old.TAB1_isn,
SOMEPKG.GETSOMEDATE,
:old.AAA_amount * -1,
:old.AAA_currency_code,
:old.AAA_currency_rate,
:old.AAA_currency_amount * -1);
end if;
end if;
end TRG_Delete;
When I re-write the code of the view( that is not being accessed, as I have an instead of trigger! )
Like this, for some reason I get access to the BBB table using an index and not FULL SCAN.
create or replace view MY_VIEW as
--
select BBB.BBB_TRANSACTION_HEADER_ISN
,sum(BBB.BBB_AMOUNT) as AAA_AMOUNT
,sum(BBB.BBB_CURRENCY_AMOUNT) as AAA_CURRENCY_AMOUNT,
AAA.AAA_TRANSACTION_HEADER_ISN
,AAA.AAA_TYPE
,AAA.AAA_OBJECT_ISN
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,0 as TDIL_ATTACH_ADJUSTMENT_TO
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY
from AAA_TRANSACTION_HEADER AAA
join BBB_TRANSACTION_DETAIL BBB
on BBB.BBB_TRANSACTION_HEADER_ISN = AAA.AAA_TRANSACTION_HEADER_ISN and AAA.AAA_DELETED = 0
GROUP BY AAA.AAA_TRANSACTION_HEADER_ISN,
AAA.AAA_TYPE
,AAA.AAA_OBJECT_ISN
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY,
BBB.BBB_TRANSACTION_HEADER_ISN
And I finally get a good execution plan!
I just don't understand why it doesn't work in the original form, and how to make it work in the original form...
Because this is one of the cases when it's hard to change such logic without good explanation, and also, I am not sure it'll work well everywhere, Because I simply don't understand why it works ( or doesn't)
SQL> delete from my_view t where t.IDENTIFIER1 = 3693094;
12 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 475626509
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 24 | 2280 | 9 (12)| 00:00:01 |
| 1 | DELETE | MY_VIEW | | | | |
| 2 | VIEW | MY_VIEW | 24 | 2280 | 9 (12)| 00:00:01 |
| 3 | HASH GROUP BY | | 24 | 4248 | 9 (12)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 24 | 4248 | 8 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAB1 | 10 | 1600 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | TAB1_IND1 | 11 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | TAB2_IND1 | 2 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | TAB2 | 2 | 34 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("AAA"."AAA_DELETED"=0)
7 - access("AAA"."AAA_IDENTIFIER1"=3693094)
8 - access("BBB"."BBB_IDENTIFIER2"="AAA"."IDENTIFIER1")
Statistics
----------------------------------------------------------
88 recursive calls
95 db block gets
309 consistent gets
0 physical reads
12160 redo size
573 bytes sent via SQL*Net to client
538 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
12 rows processed
I tried to re-collect statistics on the 2 tables but it didn't work - still doing FTS when deleting from the view in the 1st version...
How can I understand why this is happening, and make the original query behave appropriately ( perform index range scan rather than FTS ) ??
BOTH TABLES CONTAIN SOME 8M records, and I tried to collect stats for them a couple of times, but result is the same - it only works
Thanks in advance.
Andrey
[Updated on: Tue, 22 May 2018 12:41] Report message to a moderator
|
|
|
|
Re: Wrong plan chosen by optimizer [message #669889 is a reply to message #669885] |
Wed, 23 May 2018 01:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You first view is constructed from these tables,from TAB1 AAA
join TAB2 BBB and your second view from these,from AAA_TRANSACTION_HEADER AAA
join BBB_TRANSACTION_DETAIL BBB
Different tables, different plans?
|
|
|
|
Re: Wrong plan chosen by optimizer [message #669893 is a reply to message #669892] |
Wed, 23 May 2018 03:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
John Watson wrote on Wed, 23 May 2018 09:29You first view is constructed from these tables,from TAB1 AAA
join TAB2 BBB and your second view from these,from AAA_TRANSACTION_HEADER AAA
join BBB_TRANSACTION_DETAIL BBB
Different tables, different plans?
Thanks John, I apologize but while masking the code I have made some mistakes...
I would have fixed the table names, but I can't edit anymore
These tables are the same...
Only thing that changes is that when I re-write the view with both tables joined and then grouped and not BBB grouped and then joined to AAA.
But I don't understand what or how to Investigate and find out why optimizer behaves wrongly, in spite of the fact that I try to re-gather statistics, create histograms... it must have a "reason".. where can I see the reason ?
The application uses bind variables, this is my test case with literal value , but the behavior is the same.
And also, if I fix it with an outline or so - it'll hit me up in another situation with a similar but differently parsed SQL..
Thanks
|
|
|
Re: Wrong plan chosen by optimizer [message #669894 is a reply to message #669885] |
Wed, 23 May 2018 04:24 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Andrey_R wrote on Tue, 22 May 2018 18:29
When I re-write the code of the view( that is not being accessed, as I have an instead of trigger! )
It is being accessed. Oracle will go and find all the rows in the view that satisfy the where clause and then run the trigger for each one.
|
|
|
|
Re: Wrong plan chosen by optimizer [message #669897 is a reply to message #669895] |
Wed, 23 May 2018 06:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Wed, 23 May 2018 12:27It's not clear what you did to the view since the obvious differences appear to be failures of name masking rather than real changes.
You are right, I've made a mistake and now hard to fix as I can't edit
This is the view BEFORE ( bad plan , FTS, bad performance)
create or replace view MY_VIEW as
with BBB_S as
(select BBB.BBB_TRANSACTION_HEADER_ISN
,sum(BBB.BBB_AMOUNT) as AAA_AMOUNT
,sum(BBB.BBB_CURRENCY_AMOUNT) as AAA_CURRENCY_AMOUNT
from BBB_TRANSACTION_DETAIL BBB
group by BBB.BBB_TRANSACTION_HEADER_ISN) select AAA.AAA_TRANSACTION_HEADER_ISN
,AAA.AAA_TYPE
,AAA.AAA_OBJECT_ISN
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,0 as TDIL_ATTACH_ADJUSTMENT_TO
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY
,BBB_S.AAA_AMOUNT
,BBB_S.AAA_CURRENCY_AMOUNT
,AAA.AAA_IDENTIFIER
from TAB1 AAA
join TAB2 BBB_S
on BBB_S.BBB_TRANSACTION_HEADER_ISN = AAA.AAA_TRANSACTION_HEADER_ISN and AAA.AAA_DELETED = 0;
--
This is the view AFTER ( good plan, Index range scan, good performance )
create or replace view MY_VIEW as
--
select BBB.IDENTIFIER2
,sum(BBB.BBB_AMOUNT) as AAA_AMOUNT
,sum(BBB.BBB_CURRENCY_AMOUNT) as AAA_CURRENCY_AMOUNT,
AAA.TAB1_ISN
,AAA.AAA_TYPE
,AAA.IDENTIFIER2
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,0 as TDIL_ATTACH_ADJUSTMENT_TO
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY
from TAB1 AAA
join TAB2 BBB
on BBB.IDENTIFIER2 = AAA.TAB1_ISN and AAA.AAA_DELETED = 0
GROUP BY AAA.TAB1_ISN,
AAA.AAA_TYPE
,AAA.IDENTIFIER2
,AAA.AAA_OBJECT_TYPE
,AAA.AAA_CUSTOMER
,AAA.AAA_DESCRIPTION
,AAA.AAA_UNIT_TYPE
,AAA.AAA_UNIT_AMOUNT
,AAA.AAA_TARIFF_GRP_ISN
,AAA.AAA_TARIFF_CODE
,AAA.AAA_CURRENCY_CODE
,AAA.AAA_CURRENCY_RATE
,AAA.AAA_CHARGE_IS_FIXED
,AAA.AAA_NOTCHARGEABLE
,AAA.AAA_VAT_CODE
,AAA.AAA_RELATED_ISN
,AAA.AAA_GROUP_TYPE
,AAA.AAA_TARIFF_HEADER_ISN
,AAA.AAA_CLEARED
,AAA.AAA_REMARKS
,AAA.AAA_EXTRA_DESC_CODE
,AAA.AAA_EXTRA_DESC_PARAM
,AAA.AAA_DISCOUNT_ORIGINAL
,AAA.AAA_DISCOUNT_PRECENT
,AAA.AAA_DISCOUNT_AMOUNT
,AAA.AAA_CURR_DISCOUNT_ORIGINAL
,AAA.AAA_CURR_DISCOUNT_AMOUNT
,AAA.AAA_DATE_OF_SERVICE
,AAA.AAA_ADDED_MANUALLY,
BBB.IDENTIFIER2
|
|
|
Re: Wrong plan chosen by optimizer [message #669898 is a reply to message #669897] |
Wed, 23 May 2018 06:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I would assume that the bad plan is doing the sums because the WITH clause forces it, but the delete has no need to calculate the sums - all it needs to do is identify the ids, so if you give it view that can skip the sums it will.
|
|
|
|
Re: Wrong plan chosen by optimizer [message #669900 is a reply to message #669899] |
Wed, 23 May 2018 06:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
John Watson wrote on Wed, 23 May 2018 14:33In your poorly performing query, the aggregation forces Oracle to materialize the factored subquery, and the only sensible way to do that is with a scan.
Thx John.
How can I make it understand there is a better way, instead of re-writing the view ?
Or How can I see How it calculates what is the "best thing to do from here" ?
|
|
|
|
Re: Wrong plan chosen by optimizer [message #669902 is a reply to message #669898] |
Wed, 23 May 2018 06:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
cookiemonster wrote on Wed, 23 May 2018 14:23I would assume that the bad plan is doing the sums because the WITH clause forces it, but the delete has no need to calculate the sums - all it needs to do is identify the ids, so if you give it view that can skip the sums it will.
Thanks Cookiemonester. I am just not yet understanding why Oracle can't examine the re-write by the same logic I figured out that the query can benefit from ?
Or how can I make it try to ?
|
|
|
Re: Wrong plan chosen by optimizer [message #669903 is a reply to message #669902] |
Wed, 23 May 2018 07:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The whole point of that WITH clause is that it forces oracle to sum before joining to the other table.
Without the WITH clause oracle can choose when (or if) to do the sum.
You've tied oracles hands and are now looking for a way for oracle to act as though it's hands aren't tied.
You need to either rewrite the view
Or change the app to not use the view at all when doing this delete.
|
|
|
|
Goto Forum:
Current Time: Sat Aug 31 18:52:17 CDT 2024
|