Hi Tom,
We are migrating our databases from Oracle 11.2.0.3 to Oracle 12.1.0.2.0R1 on Exadata and after we did this, we are seeing extreme slowness in loading 3 of our application screens, even though the queries are running as or more efficiently than in the 11g database.
Our particular problem is a java routine that passes in 4 queries inline (I know this is bad)....it executes query 1 which brings back all of the users and then loops thru the other 3 queries, with each of these queries taking less than .00009999 seconds. Query 1 averages .037seconds, but the screen takes over 4 minutes to load.
It seems the fetch is consuming the bulk of the 4 minutes. We can see the timings from the database....this shows the executions and elasped time combined for only less than 1 second, but the delta between the Last_Load and the Last_Active_Time being 3minutes, 34 seconds:
SQL_ID PLAN_HASH_VALUE FIRST_LOAD LAST_LOAD EXEC ELAPSED AVG LAST_ACTIVE_TIME
2zak2mt28773j 3535800478 04/19/2018 08:35:45 04/19/2018 08:35:45 5048 0.177 0 04/19/2018 08:39:19
66qxp3q46wafz 1869742972 04/19/2018 08:35:45 04/19/2018 08:35:45 1 0.046 0.046 04/19/2018 08:39:19
dvp8c0m7ztdjd 1569075061 04/19/2018 08:35:45 04/19/2018 08:35:45 5858 0.482 0 04/19/2018 08:39:19
5unyt1d4c6ykt 4096179566 04/19/2018 08:35:45 04/19/2018 08:35:45 4055 0.15 0 04/19/2018 08:39:19
If we run this same process in our 11g database, we are getting almost identical Executions & Elapsed times, but the delta between Last_load and Last_Active_time is about 16 seconds.
We have compared our Oracle parameter settings between the old (11g) and new database (12c) and they are very much consistent, with the 12c DB having SGA & PGA at 6GB & 4GB (11g db had 6gb & 2gb respectively).
Have you seen this problem?
Thanks,
Patrick
Where are you getting those times from? v$sql?
If so you can't use
last_active_time - last_load_time
To determine query duration. From the docs, last_load_time means:
Time at which the query plan was loaded into the library cacheNot the last time you started executing it!
You can see that from this simple demo:
select /* this */* from dual;
exec dbms_lock.sleep(80);
select /* this */* from dual;
select sql_id, first_load_time, last_load_time, last_active_time
from v$sql
where sql_text = 'select /* this */* from dual';
SQL_ID FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME
gqrwquv0utrb7 2018-04-20/07:39:55 2018-04-20/07:39:55 20-APR-2018 07:41:16
LAST_LOAD_TIME = FIRST_LOAD_TIME. Not the time the second query ran...
So to see what's causing the slowdown, you need to profile your code. You can get the SQL execution details by doing:
exec DBMS_monitor.session_trace_enable ( null, null, true, true );
***your SQL code here***
exec DBMS_monitor.session_trace_disable;
Then viewing the trace file. Read more about how to do this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof Once you've done this on the old and new system, you should be able to spot what's different. If you're still struggling at this point, post your findings here.