why My database always use wrong/bad execution plan
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.