Exhaust Systems
Stock The exhaust configuration of a stock SHO is commonly referred to as a "2-1-2" layout: two exhaust manifolds feed through two separate catalytic converters; the exhaust then combines into a single pipe, goes through a single resonator, and around the gas tank; the exhaust then splits back out into two tailpipes with separate mufflers. The stock system is light-grade stainless steel; the pipes will show some surface rust but will last quite a long time in most climates.

The SHO's stock exhaust system is very quiet, but it unfortunately creates a lot of backpressure. This reduces the overall flow capabilities of the engine, which reduces the ultimate available power. The way to remedy this situation without breaking emissions law is with either free-flow mufflers or a complete "cat back" (everything downstream of the catalytic converter) exhaust system.

Alternative exhausts can be found in several configurations: true dual (custom-made or Tracking Technology), high-flow 2-1-2 (SHO Shop), cat-back (SHO Shop, Borla, Walker), and high-flow Y pipe only (SHO Shop).
Custom Duals By far the most effective exhaust for a radically modified SHO is a true dual custom-fabricated exhaust with high-flow catalytic converters, redesigned down pipes from the exhaust manifolds to the converters, and a cross-over pipe. This is the only kind of exhaust that will allow you to effectively get over 300 HP from your SHO. 

Fabrication of one of these exhausts is very difficult because of the tight passage around the gas tank. Placement of a crossover pipe is also difficult because of the SHO's transverse engine layout. An improperly placed crossover can do more harm than good.

A true dual system does not leave sufficient space for a pair of resonators, so sound abatement is achieved solely through mufflers. This will in all likelihood leave the exhaust with a pronounced "burr" or rasp. This is not a pleasant sound to many ears, so if you are concerned about this kind of thing, find a true-dual SHO to listen to before having such an exhaust installed.
SHO Shop The SHO Shop offers a complete exhaust replacement for around $900-$1100. It is available for 5-speed and automatic models in aluminized or stainless steel. The exhaust includes redesigned downpipes, high-flow catalytic converters, a redesigned Y-pipe, and stainless steel Edelbrock mufflers. The SHO Shop exhausts utilize stock hangers, and so are true bolt-ons.

The automatic SHO has a different stock Y-pipe configuration, so only the cat-back is available for cars so equipped. 

Estimated power gain from this exhaust is 30HP. It is possible to purchase only the Y-pipe assembly or only the cat-back system; power gains will not be as great. 
Borla [Borla Exhaust]The best known (and most expensive) cat-back exhaust is the stainless steel systems from Borla; several netters claim that these systems sound the nicest, and they come with a million mile warranty, but they are not cheap (expect to pay roughly $700-1000 depending on the model year).

Expected performance gain: 6 to 10 hp. Do not believe the early published reports of 30 hp gains with this exhaust! Such gains are only possible with a change-out of the catalytic converter assembly to a high-flow type.

Borla disputes this information, and insists that the dyno tests were accurate. They still reference the dyno test in their advertisements. 
Walker Dynomax Walker Dynomax makes a system for the 89-91 cars for about $200 (specify cable or rod shift linkage) and mufflers and pipe for the 92-95 models (with integrated exhaust tips). Both  aluminized steel and than stainless models are available. They have a lower tone than the stock system, but is not significantly louder.

The weld at the point where the tailpipes split off is sometimes a little sloppy on the earlier Walker Dynomax system. Flow can be improved a small amount by cleaning this weld up a bit before installation.

A SHO specific list of part numbers for the Dynomax exhaust can be found here.
Mufflers If you don't want to replace the stock pipes, switching to any low restriction muffler will help (Dynomax and Flowmaster are two popular brands, but do not necessarily offer the best performance or sound). Ed. note: I have heard the Flowmaster Turbos on Don Mallinson's SHO, and think they sound very nice outside the car. Low tone, not loud at all. It is a little raspy at full hoot because of the lack of a resonator. The Edelbrock RPM series comes highly recommended, but are reputedly somewhat loud. Most effective when combined with a MAF upgrade (see below).
Headers? From the ever-experimenting Ted Breaux:

As far as headers, there are only two sets of SHO headers in the country, and I have one. They were made by JBA, and both sets are far from perfect. Vadim's are equal length with great collectors, but are much too big for realistic power levels. Mine are smaller, but are still on the large side, are not equal length, and have ok collectors. I am going to be cutting on my headers to get them perfect, as a set of headers can cost you power if they are not correct, and there is NOT a good header set for the SHO which has been made yet. I am going to change that however when I alter my set.
Other Tips [Flex Pipe]In order to get the best results on an automatic with a cat-back exhaust, the braided flexible section of exhaust pipe should be replaced with a similar pipe but with an inner diameter greater than the stock 1 7/8 inches; 2 1/4 or 2 1/2 inches are good choices.