with ObjectHistory as (
select temp.OBJECT_KEY, MAX(temp.SOURCE_EVENT_DATE) as MAXSED, temp.USER_NAME, temp.EVENT_DATE FROM (
SELECT max(OBJECT_KEY) as OBJECT_KEY, USER_NAME, EVENT_DATE, SOURCE_EVENT_DATE
FROM V_AAA GROUP BY OBJECT_HISTORY_KEY, USER_NAME, EVENT_DATE, SOURCE_EVENT_DATE ) temp, BBB SKM
WHERE SKM.OBJECT_KEY = temp.OBJECT_KEY and SKM.ULT_PARENT_OBJECT_KEY = 2 GROUP BY temp.OBJECT_KEY, temp.SOURCE_EVENT_DATE, temp.USER_NAME, temp.EVENT_DATE
)
SELECT H.OBJECT_KEY, H.USER_NAME, H.EVENT_DATE FROM ObjectHistory H ;

Here

V_AAA :*It is a view and*
BBB : *It is 11 Million Records Table*

Execution of this query is taking ages and never finishes.

**Windows Resource Monitor** :

Disk I/O : Reads (1 million Bytes/sec) ,
Write (55,000 Bytes/Sec) : 100% Highest Active Time

Physical Memory (16 GB) : 82% Used

CPU (4 core, 2.24 GHz) : 12% Used

I also followed this blog : http://www.orafaq.com/node/854 None of the queries are blocked.

From `dba_objects` table has an entry with `object_name` : `BBB` and Lock Mode : `3`



View Script : V_AAA

CREATE OR REPLACE FORCE VIEW "V_AAA" ("AAA_KEY", "OBJECT_KEY", "OPERATION", "EVENT_DATE", "SOURCE_EVENT_DATE", "USER_NAME", "USER_COMMENT", "SYNC_DATE", "SOURCE_GUID", "DIAG_GUID", "OBJECT_TYPE", "SOURCE_SYSTEM") AS
SELECT
OB.Object_History_ID AAA_KEY,
SKM.OBJECT_KEY OBJECT_KEY,
RO.Name OPERATION,
(TIMESTAMP '1970-01-01 00:00:00.0' + NUMTODSINTERVAL(OB.Event_Date, 'second')) EVENT_DATE, -- now it is the GMT time
OB.Event_Date SOURCE_EVENT_DATE,
RU.Name USER_NAME,
RC.String USER_COMMENT,
OB.Event_Date*1000 SYNC_DATE,
CO.Object_GUID SOURCE_GUID,
W.Object_GUID DIAG_GUID,
OT.Name OBJECT_TYPE,
'EASTUDIO' as SOURCE_SYSTEM
FROM Object_History OB
INNER JOIN ctl_obj_Ver COV on OB.ctl_obj_Ver_ID = COV.ctl_obj_Ver_ID
INNER JOIN ctl_obj CO on COV.ctl_obj_ID = CO.ctl_obj_ID
INNER JOIN sk_map SKM on SKM.SOURCE_GUID = CO.Object_GUID
LEFT OUTER JOIN Repo_Comment RC on OB.Repo_Comment_ID = RC.Repo_Comment_ID
INNER JOIN repo_oper RO on RO.repo_oper_ID = OB.repo_oper_ID
INNER JOIN User_On_Machine UON on OB.User_On_Machine_ID = UON.User_On_Machine_ID
INNER JOIN Repo_User RU on RU.Repo_User_ID = UON.Repo_User_ID
INNER JOIN EAWorkspace W on W.EAWorkspace_ID = OB.Ctl_Diag_ID
INNER JOIN Object_Type OT on OT.Object_Type_ID = CO.Object_Type_ID
WHERE
OT.Name in ('EAWORKS','EADIAG','EAPROJ')
UNION ALL
SELECT
OB.Object_History_ID as AAA_KEY,
SKM.OBJECT_KEY OBJECT_KEY,
RO.Name as OPERATION,
(TIMESTAMP '1970-01-01 00:00:00.0' + NUMTODSINTERVAL(OB.Event_Date, 'second')) EVENT_DATE, -- now it is the GMT time
OB.Event_Date as SOURCE_EVENT_DATE,
RU.Name as USER_NAME,
RC.String as USER_COMMENT,
OB.Event_Date*1000 SYNC_DATE,
CO.Object_GUID as SOURCE_GUID,
D.Object_GUID as DIAG_GUID,
OT.Name as OBJECT_TYPE,
'ERStudio' as SOURCE_SYSTEM
FROM Object_History OB
INNER JOIN ctl_obj_Ver COV on OB.ctl_obj_Ver_ID = COV.ctl_obj_Ver_ID
INNER JOIN ctl_obj CO on COV.ctl_obj_ID = CO.ctl_obj_ID
INNER JOIN sk_map SKM on SKM.SOURCE_GUID = CO.Object_GUID
LEFT OUTER JOIN Repo_Comment RC on OB.Repo_Comment_ID = RC.Repo_Comment_ID
INNER JOIN repo_oper RO on RO.repo_oper_ID = OB.repo_oper_ID
INNER JOIN User_On_Machine UON on OB.User_On_Machine_ID = UON.User_On_Machine_ID
INNER JOIN Repo_User RU on RU.Repo_User_ID = UON.Repo_User_ID
INNER JOIN Diag D on D.Diag_ID = OB.Ctl_Diag_ID
INNER JOIN Object_Type OT on OT.Object_Type_ID = CO.Object_Type_ID
WHERE
OT.Name in ('DIAG','MODEL','ENTITY','ATTRIBUTE','ENTITY_DISPLAY',
'RELATIONSHIP','RELATIONSHIP_DISPLAY','SUBMODEL','CHECK_CONSTRAINT',
'DB_VIEW','VIEW_FIELD','VIEW_DISPLAY')
UNION ALL
SELECT
OB.Object_History_ID as AAA_KEY,
SKM.OBJECT_KEY OBJECT_KEY,
RO.Name as OPERATION,
(TIMESTAMP '1970-01-01 00:00:00.0' + NUMTODSINTERVAL(OB.Event_Date, 'second')) EVENT_DATE, -- now it is the GMT time
OB.Event_Date as SOURCE_EVENT_DATE,
RU.Name as USER_NAME,
RC.String as USER_COMMENT,
OB.Event_Date*1000 SYNC_DATE,
CO.Object_GUID as SOURCE_GUID,
D.Object_GUID as DIAG_GUID,
OT.Name as OBJECT_TYPE,
'ERStudio' as SOURCE_SYSTEM
FROM Object_History OB
INNER JOIN ctl_obj_Ver COV on OB.ctl_obj_Ver_ID = COV.ctl_obj_Ver_ID
INNER JOIN ctl_obj CO on COV.ctl_obj_ID = CO.ctl_obj_ID
INNER JOIN sk_map SKM on SKM.SOURCE_GUID = CO.Object_GUID
LEFT OUTER JOIN Repo_Comment RC on OB.Repo_Comment_ID = RC.Repo_Comment_ID
INNER JOIN repo_oper RO on RO.repo_oper_ID = OB.repo_oper_ID
INNER JOIN User_On_Machine UON on OB.User_On_Machine_ID = UON.User_On_Machine_ID
INNER JOIN Repo_User RU on RU.Repo_User_ID = UON.Repo_User_ID
INNER JOIN Diag_DD DD on DD.Enterprise_DD_ID = OB.Ctl_Diag_ID
INNER JOIN Diag D on D.Diag_ID = DD.Diag_ID
INNER JOIN Object_Type OT on OT.Object_Type_ID = CO.Object_Type_ID
WHERE
OT.Name in ('DATA_DIC','DOMAIN', 'REFE_VAL','DEFAULT',
'RULE','USER_DATATYPE','ATT_TYPE','ATTACH')
AND DD.Flags in (0,1);