This content has been marked as final. Show 9 replies
This situation can be explained by following scenario where 2 differents sessions insert the same primary key in the same table:
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.
-- 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 create a logon trigger to enable trace provided that the Oracle user account executing the trigger has been granted EXECUTE privilege on DBMS_MONITOR.
WARNING: if there are errors in this trigger this can prevent almost any new connection to database !!!
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
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
I did the folowing;
1- Took an AWR snapshot.
2- Asked the developers to run the queries from the application
3- Took another AWR snapshot.
After that I have generated an AWR report but I don't see application queries listed in the report. I suppose the AWR report only shows the top 30 sql listed.
How can I change that so I have all the queries listed or at least appliaction queries, I think I need to modify some paramaters in the database right? My idea is to have all application listed and then see the queries response time (=elapsed time).
Another question, wondering if there is response time for each query capture somewhere in AWR dba_hist tables; which table and which column should I look for?
You can use:
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( topnsql => 'MAXIMUM' ); END; /
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.