mercredi 24 décembre 2014

Oracle - Slow queries without prepending schema name or providing hint


I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table.



select max(col) from table; - takes 300 seconds (index full scan)

select max(col) from schema.table; - .03 seconds (index full scan min/max)

select /*+ index(tab_col) */ max(col) from table; - .03 seconds (index full scan min/max)


We have similar queries behaving slowly as well until prepending the schema name or using a hint, but this one is the best to demonstrate. This schema was created with impdp remap_schema. The source schema still works well.


What I've tried:



  1. Gathered statistics on the schema, then the DB using dbms_stats.

  2. We did have a public synonym for table, i removed it and gathered statistics again.

  3. expdp/impdp to another server. Queries run fast on the 2nd server. Unable to recreate issue.

  4. Dropped Database links then gathered statistics again. No joy.


Any clues? Thank you.





Aucun commentaire:

Enregistrer un commentaire