<p>I have table employee like,
<br>
employee
(
emp_id int primary key,
emp_name varchar(50),
mngr_id int)</p>

<p>and here mngr_id would either null or contain valid emp_id. This way it form the hierarchy of employees in the organization.</p>

<p>In order to traverse the entire hierarchy I had to write the recursive stored procedure. (in Oracle it's easy by using CONNECT BY .. START WITH)</p>

<p>So the question is that what is the performance impact of such stored procedure given that the level of hierarchy would not go beyond 10 levels !</p>

<p>Is there any other way to achieve the same ?</p>

Regarding the last question: There are a few nice options at
"What is the most efficient/elegant way to parse a flat table into a tree?"
You should also consider caching the result of the recursion in an intermediate table. If you change that only on update to your hierarchy table, the recursion performance hit will be negligible.
EDIT:
Personally I would do the recursion in the presentation layer of my app, for example on the web server. This provides greater flexibility compared to what can be achieved in SQL, and you can also use session or application level caching. (Using a pre-constructed DB table that is kept up to date with a trigger never leaves you with an outdated cache, though.)