Friday, 6 September 2013

Get information regarding the execution time of Oracle 11g snapshot replication job?

Get information regarding the execution time of Oracle 11g snapshot
replication job?

In Oracle 11g, I have a remote database link and I am replicating a few
tables from one database to another using an Oracle materialized view
snapshot. I created my snapshot, which copies the data from the master
database to the replicated database using the below command...
CREATE SNAPSHOT MY_TABLE
PCTFREE 15
STORAGE
(INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE MY_TABLESPACE
USING INDEX
PCTFREE 0
STORAGE (
INITIAL 200K
NEXT 200K
PCTINCREASE 0)
TABLESPACE MY_TABLESPACE
REFRESH FORCE AS
SELECT * FROM MY_TABLE@MASTER_DB;
I refresh the snapshot every 10 minutes with the below job...
dbms_refresh.make(
name => 'MY_GRP',
list => 'my_table1,my_table2,my_table3',
next_date => SYSDATE,
interval => 'SYSDATE + (10/(60 * 24))',
implicit_destroy => TRUE,
lax => TRUE,
rollback_seg => 'RB06'
);
Now, I would like to be able to get some metrics on the performance of the
replications. I can query the user_jobs or dba_jobs tables and get the
total_time, next_date, etc, but I am looking for a more detailed
explanation about the run history such as the time that each individual
refresh took to complete, the amount of cpu used, etc.
I know oracle provides this information for scheduler jobs in the
user_scheduler_jobs, user_scheduler_run_details, etc.. tables, but my
snapshot refreshes do not appear in these views. Does anyone know of any
similar Oracle tables where I can find the specific information on the
amount of time each individual snapshot refresh took and/or the cpu
resources used to complete it?

No comments:

Post a Comment