MarkView Performance Issues Related to Queries Against MV_SESSION_LOG

Issue

Our DBA has found that there is a MarkView performance issue related to queries against mv_session_log table in the MarkView schema.

Cause

• This can occur over time as old no-longer-required session data builds up in the mv_session_log table in the MarkView schema.
• Please review the Num_rows field for the MV_SESSION_LOG table in the Performance Information output of the MarkView Support Tools.
  • If there is over 500k rows, please take the following steps: 

Solution

1. Backup existing records (temporarily):

Create table mv_object_lock_bk as select * from mv_object_lock;

Create table mv_object_access_log_bk as select * from mv_object_access_log;

Create table mv_session_log_bk as select * from mv_session_log;

 

2. Delete the old records:

delete from mv_object_lock where markview_session_id in (select markview_session_id from mv_session_log where last_activity_timestamp < sysdate - 30);

delete from mv_object_access_log where markview_session_id in (select markview_session_id from mv_session_log where last_activity_timestamp < sysdate - 30);

delete from mv_session_log where last_activity_timestamp < sysdate - 30;

Commit;

 

3. Create the following index (if one does not already exist).:

Create index MV_SES_LOGON_TS_NDX on MV_SESSION_LOG(LOGON_TIMESTAMP);

 

4. Rebuild tables and indexes

alter table MV_SESSION_LOG move;
alter index MV_SES_MARKVIEW_SESSION_PK rebuild;
alter index MV_SES_USER_HOST_LOGON_NDX rebuild;
alter index MV_SES_WORKSTATION_ID_FK rebuild; 
alter index MV_SES_LOGON_TS_NDX rebuild;
exec dbms_stats.gather_table_stats('MARKVIEW','MV_SESSION_LOG');

alter table MV_OBJECT_LOCK move;
alter index MV_LCK_MARKVIEW_SESSION_ID_FK rebuild;
alter index MV_LCK_OBJECT_LOCK_NDX rebuild;
alter index MV_LCK_OBJECT_LOCK_PK rebuild;
exec dbms_stats.gather_table_stats('MARKVIEW','MV_OBJECT_LOCK');

alter table MV_OBJECT_ACCESS_LOG move;
alter index MV_OAL_OBJECT_TYPE_ID_FK rebuild;
alter index MV_OAL_PK rebuild;
alter index MV_OAL_TYPE_UNQ rebuild;
exec dbms_stats.gather_table_stats('MARKVIEW','MV_OBJECT_ACCESS_LOG');

Level of Complexity 

High (Consult with your DBA before proceeding)

If you have any questions, please log a MarkView support case , ensuring you upload your current Performance Information output from the MarkView Support Tools when you do.

 

Applies to  

Product	Version	Build	Environment	Hardware
MarkView	All