Question

Hi,

We had failed over our primary Oracle DB to our standby. Both servers are identical. Now i have noticed that one update statement was executing very slow on the new server. When comparing the execution plans for the same statement I've noticed that one index was not used on the new server and it was dong fts. I did gather stats for the table and the index that was not used, but to no avail. Then I hint the index and got execution plan that did not use fts but the cost difference was high when using the same index. Bellow are the plans and the statement:

on the old server:

    UPDATE STATEMENT  ALL_ROWS
    Cost: 50,402  Bytes: 10,090,430  Cardinality: 107,345
    Partition #: 0
            8 UPDATE PMTPROD01.PAYMP_LIABILITY_INFO
            Cost: 0  Bytes: 0  Cardinality: 0
            Partition #: 0
                    7 MERGE JOIN ANTI NA
                    Cost: 50,402  Bytes: 10,090,430  Cardinality: 107,345
                    Partition #: 0
                            3 SORT JOIN
                            Cost: 50,201  Bytes: 5,581,940  Cardinality: 107,345
                            Partition #: 0
                                    2 TABLE ACCESS BY INDEX ROWID PMTPROD01.PAYMP_LIABILITY_INFO [Analyzed]
                                    Cost: 48,815  Bytes: 5,581,940  Cardinality: 107,345
                                    Partition #: 0
                                            1 INDEX RANGE SCAN PMTPROD01.PAYMP_LBI_CLIENT_FK_I [Analyzed]
                                            Cost: 1,549  Bytes: 0  Cardinality: 214,690
                                            Partition #: 0
                            6 SORT UNIQUE
                            Cost: 201  Bytes: 84  Cardinality: 2
                            Partition #: 0
                                    5 TABLE ACCESS BY INDEX ROWID PMTPROD01.PAYMP_LIABILITY_INFO [Analyzed]
                                    Cost: 200  Bytes: 84  Cardinality: 2
                                    Partition #: 0
                                            4 INDEX RANGE SCAN PMTPROD01.PAYMP_BATCHID_FK_I [Analyzed]
                                            Cost: 15  Bytes: 0  Cardinality: 699
                                            Partition #: 0

new server:

Plan

    9 UPDATE STATEMENT  ALL_ROWS
    Cost: 2,047,856  Bytes: 1,187,129,806  Cardinality: 6,862,022
    Partition #: 0
            8 UPDATE PMTPROD01.PAYMP_LIABILITY_INFO
            Cost: 0  Bytes: 0  Cardinality: 0
            Partition #: 0
                    7 MERGE JOIN ANTI NA
                    Cost: 2,047,856  Bytes: 1,187,129,806  Cardinality: 6,862,022
                    Partition #: 0
                            3 SORT JOIN
                            Cost: 2,047,708  Bytes: 631,306,024  Cardinality: 6,862,022
                            Partition #: 0
                                    2 TABLE ACCESS BY INDEX ROWID PMTPROD01.PAYMP_LIABILITY_INFO [Analyzed]
                                    Cost: 1,902,202  Bytes: 631,306,024  Cardinality: 6,862,022
                                    Partition #: 0
                                            1 INDEX RANGE SCAN PMTPROD01.PAYMP_LBI_CLIENT_FK_I [Analyzed]
                                            Cost: 60,160  Bytes: 0  Cardinality: 8,037,047
                                            Partition #: 0
                            6 SORT UNIQUE
                            Cost: 148  Bytes: 7,452  Cardinality: 92
                            Partition #: 0
                                    5 TABLE ACCESS BY INDEX ROWID PMTPROD01.PAYMP_LIABILITY_INFO [Analyzed]
                                    Cost: 147  Bytes: 7,452  Cardinality: 92
                                    Partition #: 0
                                            4 INDEX RANGE SCAN PMTPROD01.PAYMP_BATCHID_FK_I [Analyzed]
                                            Cost: 12  Bytes: 0  Cardinality: 506
                                            Partition #: 0

statement:

       update /*+ index (li PAYMP_LBI_CLIENT_FK_I) */
             pmtprod01.paymp_liability_info li
          set li.active_flag = 'N',
              li.modified_by = 'a',
              li.modified_on = sysdate
          where li.client_code = '11111111111'
            and li.active_flag = 'Y'
            and (li.account_number,
                 nvl (li.invoice_number, 'Y'),
                 li.cx_segment_type_code,
                 (case when 'N' = 'N' then 'Y' else nvl(li.auth_token1, 'Y') end),
                 (case when 'N' = 'N' then 'Y' else nvl(li.auth_token2, 'Y') end),
                 (case when 'N' = 'N' then 'Y' else nvl(li.auth_token3, 'Y') end)
                ) not in (
                select li.account_number,
                       nvl (li.invoice_number, 'Y'),
                       li.cx_segment_type_code,
                       (case when 'N' = 'N' then 'Y' else nvl(li.auth_token1, 'Y') end),
                       (case when 'N' = 'N' then 'Y' else nvl(li.auth_token2, 'Y') end),
                       (case when 'N' = 'N' then 'Y' else nvl(li.auth_token3, 'Y') end)
                from pmtprod01.paymp_liability_info li
                where li.client_code = '11111111111'
                  and li.batch_id = 4631161
                  and li.active_flag = 'Y'
                  );

Not sure why the difference? Any help is greatly appreciated.

Thanks, Zare

Answer 1

Well, in the first plan the optimizer expects to fetch 214,690 rows from the index PAYMP_LBI_CLIENT_FK_I, in the second case it believes it will fetch about 8 mln rows. The reason for this difference must be in the statistics, so I'd compare them in more detail.

However, the best approach is usually to run the query with /*+ GATHER_PLAN_STATS */ and catch the execution plan with DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS'). There you can see how the actual row counts diverge from the estimates (A-Rows vs. E-Row). That is usually the first step to track mis-estimates down.

answered Jul 07 '14 at 13:02

Markus Winand ♦♦
936●5●11●20

@Markus, Do I get it correct, that EXPLAIN PLAN FOR for a query hinted with /*+ GATHER_PLAN_STATS */ is equivalent of PostgreSQL's EXPLAIN (analyze) construct?

(Sep 15 '14 at 15:48) Victor

1

@Victor No exactly. To get a similar result to PostgreSQL's explain analyze you need to execute the query with the /*+ GATHER_PLAN_STATS */ hint (note: execute the query! don't prefix with explain!) then you can get the plan + stats via select * from table(dbms_xplan.display_cursor());

(Sep 15 '14 at 19:57) Markus Winand ♦♦

@Markus, what about cases when query produces too many rows? Is it possible somehow to avoid rows being displayed. I've tried set autotrace traceonly, but it affects display_cursor(), producing wrong output.

(Sep 17 '14 at 10:44) Victor

@victor Yes, thats annoying. In SQL*Plus you can SET TERMOUT OFF for that purpose.

(Sep 17 '14 at 11:46) Markus Winand ♦♦

Answer 2

Hi Marcus,

Thanks for the update. I had to quickly change to update to for loop and with batch commits every 10k just to make think moving. I will run the statements with suggested hints and check the actual plans.

answered Jul 09 '14 at 15:45

zare
1●1●1●1