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:
- Gathered statistics on the schema, then the DB using
dbms_stats. - We did have a public synonym for table, i removed it and gathered statistics again.
expdp/impdpto another server. Queries run fast on the 2nd server. Unable to recreate issue.- Dropped Database links then gathered statistics again. No joy.
Any clues? Thank you.
Aucun commentaire:
Enregistrer un commentaire