If you're looking to do some hard cornering and power out of the corners, the only way you can do it is in manual mode.
At ZoomZoomLive, I kept it in 2nd gear the whole time. God was that a riot. I achieved such a fast track time w/the cx-7 that they told me to slow down or I'm ejected.
I use the manual mode in my p5 if I'm going to pass someone or I am winding through a subdivision. 2nd gear is all I need to fly through turns and still keep it under 25 mph (posted speed limit).
I've also noticed that automatics like to stay in a higher gear if possible to coast/conserve fuel. This is all fine and good except if you're coasting up to a traffic light that suddenly turns green. Hitting the gas gives you no power. It eventually downshifts, but I can be much faster if I manually downshift.