Database Utilities (MOSC)

MOSC Banner

why My database always use wrong/bad execution plan

edited Apr 28, 2020 11:37AM in Database Utilities (MOSC) 11 commentsAnswered

We have an Oracle 12.1 database (slisblb2) using by Siebel application. Now for most of the SQLs, by default, the optimizer always chooses a wrong/bad execution plan, and causing very poor performance. Hence, almost for most of the SQLs, we have to check the execution plan, disable some indexes, then SQLs will run much much faster. After that we need to create a SQL PLAN BASELINE without using the disabled indexes, applied the baseline for SQLs, and then re-enable the indexes.

In order to investigate the issue, we build a new testing server/DB (slisblb5), and then use TTS(transportable tablespace) to migrate database to new server(system, sysaux and undo have to be created on new DB, as they can't be transported, all other tablespaces are transported). now on new server/DB, data size, statistics and indexes are exactly the same, but without apply any SQL PLAN BASELINE, then optimizer always can choose good/correct execution plan, and all SQLs are running very quick.  As this server is just for testing, we can't use it to replace existing server.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center