Hi,
While you are using your program which is connected to Oracle database, you can feel slowness on application related with Oracle database. This problem is probably related with blocking session and database lock.
You can find momentarily blocking session and lock with below scripts.
select s1.username || '@' || s1.machine || ' ( THIS SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
You can generate Kill script of Blocking sessions like below.
SELECT 'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''';',sid,username,serial#,process,NVL (sql_id, 0), sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait FROM gv$session s WHERE blocking_session_status = 'VALID' OR sid IN (SELECT blocking_session FROM gv$session WHERE blocking_session_status = 'VALID');
query result will be like below if you have blocking session in your database.
alter system kill session '1,39390,@1'; alter system kill session '2,2536,@1'; alter system kill session '3,26324,@1';
then run above script in new session and kill these blocking sessions to solve problem.