javasqloracle-databasewildflydatabase-view

What is reason of slowness and boost of output of a DB view which was taking 6 sec suddenly taking 1.5 sec?

I have a Oracle view with following condition:

CREATE OR REPLACE FORCE EDITIONABLE VIEW .....
.
.
select *  from 
(select t1.*,(select t.enddate from (select enddate,empid,
  ROW_NUMBER() OVER (PARTITION BY empid ORDER BY enddate desc) as seqnum
  from employee t2
  where t2.empid=t1.empid) t
   where seqnum=1)  As enddate_1 from (select * from
(select WED.*,ROW_NUMBER() OVER (PARTITION BY empid ORDER BY startdate desc) as seqnum from 
(select t1.*,COUNT(*) OVER (PARTITION BY empid) WDECOUNT from employee t1) WED
where WDECOUNT=1 or WED.startdate <= sysdate)) t1 WHERE seqnum=1);

This view is used by 20 or 30 Java integrations in Wildfly Server to fetch data of employee.

Since last 7 days of creation it was taking 6 sec to returns 50 records and now suddenly it is taking 1.5 for fetching same records (No changes are made in view sql).

Checked oracle history and haven't found any Clear Cache Command or any kind of locks.

What are the probabilities according to you for sudden increase in performance?

(This was under testing on various laptops and we found same behavior in all (slow and sudden speed). A doubt comes for wildfly connection pooling; but other tables are working fine.

I am not aware of oracle at all if some indexing is done (not found in history) or resources are provided or so. If someone can help from oracle angle too)

Oracle query i used to check history:

SELECT v.FIRST_LOAD_TIME, CPU_TIME, ELAPSED_TIME, PARSING_SCHEMA_NAME, MODULE, V.* FROM V$SQL V 
where PARSING_SCHEMA_NAME = 'MAHTERJEEDATA' order by v.FIRST_LOAD_TIME desc;

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

Solution

  • That is like looking for needles in haystacks. There are a lot of options here, but if you ask me I would try to see when the automatic gather statistics process in your database was executed last time.

    Probably, the tables involved in the view have new statistics and the CBO now has a better execution plan, perhaps a better cardinality estimation led to a new plan.

    Check the execution plan of the view for the past 7 days using AWR

    SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('your_sql_id'));

    Perhaps you get now two different plans stored on AWR for the same SQL.

    It is worth a try. But like I said, there are a lot of options. The one I am telling you is one of the most commons for a boost in performance.