Some heavy CPU applications (some database server software, for example) are coded to be "multi-threaded", and can therefore directly take advantage of dual CPU/core computers (be they two cores on one chip, or two single-core chips on the motherboard) to do extra processing in the application itself. However, most applications aren't written that way, and therefore most apps will only use one of the two cores (leaving the other one "free").
But even in that case the second CPU/core will still help (albeit not nearly as much as the multi-threaded application case), as long as your OS has multi-CPU/core support built into the OS. Because if one CPU heavy app uses all the CPU of one core, you still have the other one "free" to do other things (including such "mundane tasks" as redrawing your desktop, running your anti-virus software, etc). For example, on a single-core system the Windows desktop will slow to a crawl if/when you have a heavy CPU app trying to get work done. But with a dual-CPU/core system (assuming you have a version of Windows with multi-core support enabled), the (heavy CPU) application will just take over one of the CPUs/cores, and the desktop will continue to be very responsive using the other CPU/core. This isn't a doubling of performance by any means, but it is a useful effect. Likewise, if you have several programs you are running "at the same time", you will discover (again assuming your OS has the multi-CPU/core support enabled) that the OS will divide the apps up between the CPUs/cores (again allowing you to benefit from both CPUs/cores).
FWIW: At work, we haven't built a production database server using just a single CPU/core in years. And we are even starting to roll out dual-core machines to users that need high-end computers (although we still go with the "cheap CPUs" for the majority of people who rarely max out even one CPU). We just realized long ago, that the marginal cost of the extra CPU/core was more than "worth it" if/when you need heavy CPU processing to get your work done (i.e. you routinely max out a single CPU/core). Yes, dual CPU/core is not even close to doubling performance, because CPU load will often not split evenly between the CPUs/cores (even with database software, since not all database tasks can be run in parallel). As a result, it's common to have one CPU/core working hard, and the other one doing very little. But even so, you do have more overall CPU for the entire mix of tasks, and that marginal benefit is often more than worth the marginal increase in price over a single CPU/core system (again, assuming that you do heavy enough processing, that maxing out a single CPU/core would otherwise be occurring).