Tuning using Log Miner

I was working on a large import process. My application user provided the export file of size around 10GB.

I started the import process and it finished in approximately 2 hours ( no indexes and constraints).

imp / buffer=102400000 file=/tmp/table.p fromuser=NTUSER touser=NTUSER
indexes=N constraints=n statistics=None grants=N
recordlength=65535 ignore=Y commit=Y ANALYZE=N rows=y feedback=500000

After the above operation I had to run an update for 20 million records.The update operation generated around 10 GB of redo. All the tables were in no logging mode. I spent couple of hours to dig out the reason of large number of archive logs and overall update performance.

I decided to use logminer to read the archive logs to gather the details on the objects causing large redo.I copied the production archive log in the lab server and did the following:

The prod db was Nprod and lab db was NLAB.

Starting Logminer

BEGIN
DBMS_LOGMNR_D.build (
options => DBMS_LOGMNR_D.store_in_redo_logs);
END;
/
BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.addfile,
logfilename => '/tmp/2_2212_592338274.dbf');
END;
/
BEGIN
DBMS_LOGMNR.start_logmnr (
options => Dbms_Logmnr.DDL_Dict_Tracking);
END;
/
SELECT scn, operation, sql_redo, sql_undo FROM v$logmnr_contents;

The sql_redo and sql_undo had reference Object#.


I filtered out the output and got the object id. I executed the following query in the production database.

select object_id,object_name,owner from dba_objects where object_id in(57190,57188,64455,6379);


OBJECT_ID OBJECT_NAME OWNER
---------- -------------------- ------------------------------
47677 IAM_STATE CBADUSER
63798 T_HUMAONS NTUSER
63705 MLOG$_T_HUMAONS NTUSER
57197 STUDIO HUGUSER

The above output showed up MLOG. We were moving the data from one site to another and we never talked about materialized view logs. I discussed the Materialized view log details with application team and we decided that before export they will refresh the mview and drop all mlogs.


The initial import time was around 2 hours and update timing was 2 hour 38 minutes but after the above changes the import finished in 58 minutes and update finished in 1 hour 10 minutes.