SQL*Loader direct

We had to setup SQL Server Replication to an Oracle database. The 2 tables in questions are huge. The small table has 63mill rows and the big one has 1.5bill rows. The big table has 50 date ranged partitions.

I used SQL Loader direct path without any indexes in place from csv files to do the initial load.

The first thing to do was to prepare the database for Direct Loading by executing the script $ORACLE_HOME/rdbms/admin/catldr.sql.

I also had to set my session’s DATE format to be the same as the format in the CSV file.

$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

To get the fastest possible load performance on the 50 partitions I followed these rules:
There were no indexes on the table
The table was set to NOLOGGING
The database was put in NOARCHIVELOG mode

And the following SQL Loader options were used:
DIRECT=TRUE to invoke Direct Loading. This will effectively bypass most of the RDBMS processing
UNRECOVERABLE to turn off database logging
PARELLEL=TRUE and APPEND in order to run multiple load jobs concurrently for the different partitions.

My Korn Shell Script looked like this:

#!/bin/ksh

export ORAENV_ASK=NO;
export ORACLE_SID=xxprd;
. oraenv;

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS".000"'

sqlldr xx/xx control=c1.ctl DIRECT=TRUE PARALLEL=TRUE log=c1.log &
sqlldr xx/xx control=c2.ctl DIRECT=TRUE PARALLEL=TRUE log=c2.log &
sqlldr xx/xx control=c3.ctl DIRECT=TRUE PARALLEL=TRUE log=c3.log &

… etc up to 10 jobs

And I created 10 control files they all looked identical except for the infile:

UNRECOVERABLE LOAD DATA
 infile '/dump/exports/XXprd/x200512.csv'
 append into table usage_profile
 fields terminated by "," TRAILING NULLCOLS
 (MONTH_ID, MSISDN, IMSI, IMEI, START_DT, END_DT, EVENT_CNT, STREAM, SUCCESS_CALLS, UNSUCCESS, DURATION, VOLUME_UPLINK, VOLUME_DOWNLINK, VOLUME, SUBSCRIBER_TYPE, SP_CODE)

The job completed in 12 minutes and 5 jobs later all 1.5 billion records were loaded!

Moving Online Redo Log Files

We found a lot of sessions waiting for Log File Sync and in turn found that LGWR was waiting for Log File Parallel Write.

We have 2 dedicated disks for Online redo logs. They are multiplexed, with members of the same group on different disks. When a Log Switch occurs 1 member of the Redo Logs being Archived gets READS from the ARCH process while both members of the CURRENT group are receiving WRITE requests from LGWR. We decided to add 2 more disks and split the even numbered groups from the uneven numbered groups. So that we can separate the READS and WRITES.

Current layout:

SQL>select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

G# STATUS TYPE MEMBER
-------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log;

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME
------------------------------------------
1 45181 2147483648 2 YES INACTIVE 03-SEP-2008 08:00:18
2 45182 2147483648 2 YES INACTIVE 03-SEP-2008 08:45:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:00:47
4 45178 2147483648 2 YES INACTIVE 03-SEP-2008 06:25:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 07:02:21
6 45180 2147483648 2 YES INACTIVE 03-SEP-2008 07:48:50

We use RAW devices, so after the Unix Admins created the new RAW volumes on the new disks I simply added the members:

Creating Redo Log Members for the even groups on the 2 new disks:

ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2c' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2d' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4c' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4d' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6c' TO GROUP 6;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6d' TO GROUP 6;

The status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.

SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by group#, member;

G STATUS TYPE MEMBER
-----------------------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2c
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2d
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4c
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4d
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6c
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6d

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log; 

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME 
------------------------------------------------
1 45181 2147483648 2 YES ACTIVE 03-SEP-2008 09:24:18
2 45182 2147483648 4 YES ACTIVE 03-SEP-2008 09:27:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:30:47
4 45178 2147483648 4 YES INACTIVE 03-SEP-2008 09:17:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 09:19:21
6 45180 2147483648 4 YES INACTIVE 03-SEP-2008 09:22:50

Before dropping Redo Log Members you should be aware of the following rules:
You can drop a redo log member only if it is not part of an active or current group.
Make sure the group to which a redo log member belongs is archived before dropping the member.
If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.
So after a few log switches (ALTER SYSTEM SWITCH LOGFILE) I was ready to drop the old members.
Note that V$LOG.STATUS show ACTIVE for group 1 and 2. This means the Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. This was the result of all the log switches I did. Once their STATUS changed to INACTIVE I continued dropping the old member.

ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6b';