First of all, I did my homework and found those similar questions, but they seem to cover particular firefox addons. My scenario is different: I don't run a ton of addons, but still periodically CPU usage skyrockets to 100% (I have an old single core CPU). I wonder if it is possible to see which tab is the offending one. Generally I don't run a gazillion of tabs, I try to stick to the 7+/-2 common sense rule, but closing tabs one by one and watching the CPU usage is still not very convenient.
Identifying troublesome sites can be hard. Visually, look for something that moves — most CPU hoggers are there to animate something (automatically scrolling text, rotating ads, movies, ...). (Animated gifs don't use much CPU.)
If you're lucky, the CPU hogger also makes network accesses. These are a lot easier to pin down to a site. Run tcpdump, wireshark, or whatever your favorite network traffic observer is. Web traffic is TCP and usually to remote port 80. This might point to a site you're visiting, or it might point to a site that embeds into a site you're visiting, typically an ad.
Chrome runs one process per tab, so there finding a CPU hogger is trivial. It's also faster than Firefox (but even more of a memory hog if you have many tabs open). You might want to give it a try, though it definitely has fewer bells and whistles, so it's not for everybody.
Chrome offers a task manager (Shift-Esc).
If you want the same for Firefox, vote for this bug: Need a way for users to tell where time is being spent when the UI goes unresponsive