Hi,
if it may be of any help, I'm using mod_perl with full satisfaction.
If you're basically only using CGI.pm, you don't need to change anything in your programs or in Apache directives to refer to the Apache.pm module (at least for latest versions of Apache and mod_perl) and you can pre-load CGI.pm as other modules at Apache startup. All is explained in CGI.pm documentation.
The best basic installation of mod_perl is the one done at the same time you install Apache: you can make a compilation of mod_perl as explained here, where you also set the directives to enable Apache for mod_perl.

I just had problems with @INC under Apache, but the installation of mod_perl enlighted me and I discovered things that were not evident to me :)
In fact, the day you will upgrade your perl from 5.005_03 to - say - 5.6.0, @INC will change but it won't for Apache, and you will have to deal with this, for example giving the PerlSetEnv Perl5LIB directive in httpd.conf to locate the updated @INC directories. All is explained here, anyway.
After I installed 5.6.0 I didn't know I was using the old perl libraries under Apache, So I was using CGI.pm version 2.73 from prompt and version 2.53 through the browser. :) I should have printed the version of libraries I was using through Apache after the installation of 5.6.0
I just mention this because I spent more than one hour in the CB with very helpful guys (as usual) before I could find it, and I want to share this info.
ciao,
roberto