Spotlight provides a variety of insights into our SQL server. Used primarily in the IT department, it notifies us of stress on the server and allows users to help target specific processes contributing to that stress. It also allows us to rewind to the time a problem occurs and see a snapshot of what was happening, which is useful when trying to identify the cause of off-hours issues.
Spotlight is great when you need a tool to give a quick look at a SQL server as a whole. It gives you the ability to see a lot of things, which can be vital in diagnosing problems or identifying stressors. Less great is the open-end of some of the diagnoses it makes, and things it identifies as "problems" (unless you love users coming to you and saying something like "Spotlight says there's a problem with our page latch!!!").
All the DBAs in our team use Spotlight, which currently is at 6 people. We bring up in the morning and continue to monitor all day. This gives us a great view of active production databases. We are able to view top sessions, top SQL and see CPU consumption. It is actually one of the best tools we have used for monitoring Oracle databases.
The DBA team uses it for real-time monitoring of a couple dozen of the production instances as well as spot monitoring of development instances when troubleshooting issues. We also make it available in a read-only mode to our clients so they can monitor their production instances as well. It's not the company supported/supplied software, but since that doesn't work well enough, we've supplemented it with Spotlight for our most important instances.
It had a positive impact in two main areas. First, it was easy for the DBA team to configure it to watch and alert on the most important instances. And second, it was very easy to provide clients read-only access to the dashboard so they could watch their servers as well, i.e. they weren't always pestering us for current status.