It is an aftermarket replacement pipe for the catalytic converter. Frees up some HP and TQ, but not a whole lot. Makes the exhaust louder and usually raspier. It is called an off-road pipe because it is not technically legal to remove the cat, and thus they are "for off-road use only". There are also high-flow cats, called HFCs, that are less frowned upon by the boys in blue but still give many of the benefits of an ORP.
Mine: 2008 Saab 9-3 Aero SportCombi XWD: 82k, Black, MANUAL
Gone: 2002 SVT, #1654
Hers: 2010 Focus SES: 70k, Ebony, Auto, Moon and Tune