You Asked

1)In 100 lines of plsql program I have raised performance issue how to find which line occur the performance issues.

2)Is there any way in which i can find out when a stored procedure was last executed in oracle.

NOT only a procedure am looking for all those obj like trigger , package, cursor.(was last executed in
oralce).

and Connor said...

1) If it is a SQL statement, you can see that via the PROGRAM_ID and PROGRAM_LINE# columns in V$SQL. Alternatively, you can use the plsql profiler to track usage down to the line level.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_profiler.htm

2) You can get a rough idea using v$sql

SQL> select last_active_time from v$sql
  2  where sql_text = 'BEGIN sum_calculation; END;';

LAST_ACTIVE_TIME
-------------------
01/07/2016 11:24:41

SQL> exec sum_calculation

PL/SQL procedure successfully completed.

SQL> select last_active_time from v$sql
  2  where sql_text = 'BEGIN sum_calculation; END;';

LAST_ACTIVE_TIME
-------------------
01/07/2016 14:05:21

Some other options here:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2102688400346667213

https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:11397563179480

Is this answer out of date? If it is, please let us know via a Comment