The Borg Assimilated Console is a must. No two ways about that - very nice bonuses in itself and makes part of a set.
Romulan Zero-point Energy console
Rule 62 Console
Point Defense System is pretty handy
Of the consoles which fits on any ships, which are worth using?
These three consoles immediately come to mind:
1) Borg Assimilated Universal Module (crits, +weapon power)
2) Zero Point Energy Conduit (crits, +subsystem power)
3) Tachyokinetic Converter (crits, +turn rate)
These are must-have items if you want the best possible ship combat performance and damage output.