I agree with the other poster, your system would use it just fine although not as well as it could.
I would very much suggest more ram and a lesser video card unless DX10 is an absolute must for you right away.
And, given the ram prices it's hard to recommend DDR1 when DDR2 is a similar price... though that involves upgrading everything else.
Presuming you can't afford that...
The thing that I would do is hold out with this system for as long as possible and save up for a more complete upgrade. Whether it's C2D or AM2 doesn't matter a tremendous amount in my opinion since price is more of a factor to me, I can't afford the fastest CPU so it doesn't matter to me which company makes it I just want the fastest for the money I can afford, but I can't make that choice for you.
I would only get the 8800 now if you plan on upgrading the rest of the system fairly soon, otherwise by the time you get around to upgrading it you might very well want something better than the 8800, and you'll have the same problem.
Whether you need an upgrade aside, part of DX10 is it's design to make the Vid Card more independent of the processor. So while a CPU boost may make some difference in gaming performance, it will be more negligable than before.
I would get a core2 6300 duo OCed to the max or an even faster processor stock, even with my amd clocked to 2.8ghz, my amd x2 3800 is the bottleneck for everything. I did however achieve a 3dmark06 of 9500 with the vid card.
Regardless, hold off on 8800 till Nvidia can release better drivers. There is a massive issues list. I really regret buying one as it doesn't really work with my 32inch lcd properly. DVI output is fudged unless you use a specific driver version. Also, fog effects are screwd for one of the games I play most. CSS does wierd things.
You are going to look like such a fool when nVidia releases the DX10 drivers.
Do you think it will make a big difference in the current games we have when the card can finally take advantage of DX10 in vista? Obviously it'll make a big difference in the DX10 games but how will the DX10 support affect the DX9 games? (if this has been spoken about, my bad, haven't really read up about the new cards all that much)