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
Andrey_R
Messages: 320
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) Smile


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 #669886 is a reply to message #669885] Tue, 22 May 2018 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26700
Registered: January 2009
Location: SoCal
Senior Member
Does Production code contain Bind Variable?

WHY MY INDEX IS not BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html

Re: Wrong plan chosen by optimizer [message #669889 is a reply to message #669885] Wed, 23 May 2018 01:29 Go to previous messageGo to next message
John Watson
Messages: 8265
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 #669892 is a reply to message #669886] Wed, 23 May 2018 03:35 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

BlackSwan wrote on Tue, 22 May 2018 20:55
Does Production code contain Bind Variable?

WHY MY INDEX IS not BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html

Thanks BlackSwan. I hope that next time you'll be in need of an answer on a specific case and will invest in a message for a forum,
You will get a similar reply. I hope you will be sent to www.google.com as a reply to your motivation and resourcefulness.
Then maybe you'll understand how unhelpful and sarcastic such a reply can be.





Re: Wrong plan chosen by optimizer [message #669893 is a reply to message #669892] Wed, 23 May 2018 03:47 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Wed, 23 May 2018 09:29
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?
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 Sad

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 messageGo to next message
cookiemonster
Messages: 13894
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 #669895 is a reply to message #669894] Wed, 23 May 2018 04:27 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not clear what you did to the view since the obvious differences appear to be failures of name masking rather than real changes.
Re: Wrong plan chosen by optimizer [message #669897 is a reply to message #669895] Wed, 23 May 2018 06:02 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 23 May 2018 12:27
It'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 Smile


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 messageGo to next message
cookiemonster
Messages: 13894
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 #669899 is a reply to message #669897] Wed, 23 May 2018 06:33 Go to previous messageGo to next message
John Watson
Messages: 8265
Registered: January 2010
Location: Global Village
Senior Member
In 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.
Re: Wrong plan chosen by optimizer [message #669900 is a reply to message #669899] Wed, 23 May 2018 06:47 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Wed, 23 May 2018 14:33
In 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 #669901 is a reply to message #669900] Wed, 23 May 2018 06:48 Go to previous messageGo to next message
John Watson
Messages: 8265
Registered: January 2010
Location: Global Village
Senior Member
Quote:
How can I make it understand there is a better way, instead of re-writing the view ?
You can't. The way you have written the view, there is no better way.
Re: Wrong plan chosen by optimizer [message #669902 is a reply to message #669898] Wed, 23 May 2018 06:48 Go to previous messageGo to next message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 23 May 2018 14:23
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.
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 messageGo to next message
cookiemonster
Messages: 13894
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.
Re: Wrong plan chosen by optimizer [message #669905 is a reply to message #669903] Wed, 23 May 2018 08:03 Go to previous message
Andrey_R
Messages: 320
Registered: January 2012
Location: Israel
Senior Member

Thank you Cookiemonster and John Watson!

I'll stick to the re-write for this case and beware of these subqueried group by's with a WITH clause.

Andrey
Previous Topic: Distinct query taking long time to run
Next Topic: Wrong plan chosen by optimizer
Goto Forum:
  


Current Time: Sun May 24 18:48:03 CDT 2020