Forum Stats

  • 3,768,999 Users
  • 2,252,895 Discussions
  • 7,874,835 Comments

Discussions

Optimizer doesn't use indexes after upgrade from 11.2 to 19

aksenov mike
aksenov mike Member Posts: 8 Green Ribbon
edited Aug 25, 2020 3:49PM in General Database Discussions

After DB upgrade to 19c from 11.2 we have significant poor performance on the same query executions.

Execution plans show that 19c doesn't use indexes.Though fresh statistics were gathered.

When we set optimizer to 11.2  and import 11g plans - it runs OK.

What can be considered to resolve such issue?

Answers

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Aug 25, 2020 3:49PM

    aksenov, how was the upgrade performed (in place vs import/export, transportable tablespace etc...)  How were the statistics generated?  Were the default for 19c dbms_stats collection parameters used? 

    - -

    If the default collection parameters were not used recollect statistics using the defaults.

    - -

    Post the SQL and actual query plan for a problem query.  Post the index information for the tables in the index along with associated column statistics.

    - -

    HTH -- Mark D Powell --