I have a 10gb database, that the queries were running very fast on Oracle XE.

I exported this db with expdp and imported on a new server Oracle 12c (hardware better than original, much more RAM and CPU) with impdp and now the queries are being slow.

I think Oracle created some internal optimisation based on queries I frequently do, and now that the Database is imported, he lost all this optimisations. This is just my theory.

Anyone knows if Oracle optimize database while you use? If yes, how do I accelerate this process on a recently imported schema.

Edit: I monitored v$sql_monitor and there was just one problem I fixed with a new index. But queries still are slow.

  • Have you gathered statistics?
    – Philᵀᴹ
    Nov 11 '16 at 11:11
  • Yes, looked in v$sql_monitor and no problem there. Nov 11 '16 at 11:12
  • Do you have an example query with the old and new explain?
    – Tom V
    Nov 14 '16 at 19:16

I would guess that you have indexes that are unusable. Check and see if you have unusable indexes. If you do, then you need to rebuild them.

SELECT owner, status, COUNT(*) FROM dba_indexes GROUP BY owner, status ORDER BY 1,2;

Not the answer you're looking for? Browse other questions tagged or ask your own question.