I have two vehicles that, while they are both RWD, 2.0L four-cylinders, and both are fun to drive, are otherwise exceedingly different in terms of drivetrain. They are an S2000 (naturally aspirated 9000 rpm, 6 speed manual) and BMW F30 328i (turbo ~6500 rpm, 8 speed auto by ZF.) The auto is a planetary gearbox with a torque converter. The TC locks very, very early - it's basically just to get the car going. Once moving the car feels like it has a direct clutch connection between the engine and the wheels. The auto also can shift extremely quickly - as quick as a PDK, even quicker than an Audi S4's DCT. (Which really surprised me, but it was a friend's car and I don't know what mode it was in. Possible the S4 can shift more quickly.) This depends on the mode the BMW is in and how I'm driving, of course - when driving sedately in the default mode, the shifts are smooth and almost imperceptible. When it shifts quickly there's a nice kick to it.
I like the S2000's transmission when driving around town or in the mountains. It's direct and immediate. It can be fun at autocrosses and track days, but heel-toe downshifts don't always go perfectly. It sucks in stop-and-go traffic.
I like the BMW's transmission in traffic (it's effortless in auto mode) and driving spiritedly (it's responsive in manual mode, let's me concentrate on the steering and throttle more, and except for downshifting to prevent stalling it does exactly what you tell it to do - it'll even bounce the engine off redline if you configure it for such.) Driving around town in manual mode just doesn't work - the engine is way too quiet to give feedback on shift points. When I try it I often look at the tach only to realize I'm cruising along just below redline. This is quite different from the S2000, which of course gives constant and very noticeable feedback on engine speed. (Which I love. I wish with BMW engine was much louder in the cabin.) Also with eight closely spaced gears that's a lot of fiddling with the paddles to keep the engine at the rpm I want. Finally, while I don't have direct personal experience with it, I think the 8 speed auto BMWs get gas mileage as good as or better than the 6 speed manual ones.
I have no experience with Acura's current transmissions, and it sounds like the NSX's will be different yet again. But assuming there's a proper level of feedback from the NSX engine on where it is in the rev range, I think it'd be quite happy having one with a 9 speed DCT. It won't be as engaging as a manual on city streets (not like all that HP can be used properly there anyway) but when autocrossing and tracking it should perform superbly, and with just normal driving it should deliver very good efficiency and convenience.