performance issue

This situation can be explained by following scenario where 2 differents sessions insert the same primary key in the same table:

-- session 1:
insert into t values(1);
-- session 1 does something else without running COMMIT or ROLLBACK

-- session 2
insert into t values(1);
-- session 2 waits until session 1 runs COMMIT or ROLLBACK.

You can try to use DBMS_MONITOR to trace SQL statements (see examples in http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php) to check wait events.

does application utilize connection pooling?

You can try to create a logon trigger to enable trace provided that the Oracle user account executing the trigger has been granted EXECUTE privilege on DBMS_MONITOR.

Something like:

create or replace trigger tt
after logon
on database
begin
if (user = '<connecting user>') then
 dbms_session.session_trace_enable(waits => true, binds => true);
end if;
end tt;
/
show errors
exit

WARNING: if there are errors in this trigger this can prevent almost any new connection to database !!!

Note also that your application seems to suffer from n°1 mistake according to Top Ten Mistakes Found in Oracle Systems http://docs.oracle.com/cd/E11882_01/server.112/e16638/technique.htm#i11221 :

>
1. Bad connection management

The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.
>

Edited by: P. Forstmann on 21 févr. 2012 19:04

You can try to disable the trigger after 1 or 2 minutes or try to filter sessions to be traced by using V$SESSION columns if you know which sessions are more likely to get the issue.

hi,

You can use:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   topnsql   => 'MAXIMUM'
);
END;
/

references: http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_workload_repos.htm#BACEHJJI
topnsql
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 50,000. Specifying NULL will keep the current setting.
If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.