• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2839
  • Last Modified:

tuning load big data with sqlldr

Dear experts

I want to tune my process wich use sqlldr , I want to upload average 120 millions rows dailly  in database 9.0.1.0 on table IOT with partition per day
1/
CREATE TABLE DBM_MSC_CDR
(
  SUBNO             VARCHAR2(20 BYTE)           DEFAULT '-',
  B_SUBNO           VARCHAR2(20 BYTE)           DEFAULT '-',
  ROUTE_IN          VARCHAR2(10 BYTE)           DEFAULT '-',
  ROUTE_OUT         VARCHAR2(10 BYTE)           DEFAULT '-',
  CALL_DURATION     NUMBER(5)                   DEFAULT 0,
  IMSI_NUMBER       VARCHAR2(20 BYTE),
  IMEI_NUMBER       VARCHAR2(20 BYTE),
  CHARGE_INDICATOR  VARCHAR2(5 BYTE),
  CALL_TYPE         VARCHAR2(3 BYTE),
  CELL_ID           VARCHAR2(5 BYTE),
  TELE_SERVICE      VARCHAR2(3 BYTE),
  BEARER_SERVICE    VARCHAR2(3 BYTE),
  MSRN              VARCHAR2(20 BYTE),
  NADI              VARCHAR2(2 BYTE),
  TRANSDATE         DATE,
  FILE_ID           NUMBER(9),
  UPDDATE           DATE                        DEFAULT trunc(sysdate),
  CONSTRAINT DBM_MSC_CDR_PK PRIMARY KEY (SUBNO, B_SUBNO, TRANSDATE, CALL_TYPE, CALL_DURATION)
)
ORGANIZATION INDEX
  PARTITION BY RANGE (TRANSDATE)
(  
  PARTITION D29SEP2007 VALUES LESS THAN (TO_DATE(' 2007-09-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    TABLESPACE CDRST04
    PCTFREE    0
    INITRANS   10
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                FREELISTS        5
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION D30SEP2007 VALUES LESS THAN (TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    TABLESPACE CDRST04
    PCTFREE    0
    INITRANS   10
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                FREELISTS        5
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION D01OCT2007 VALUES LESS THAN (TO_DATE(' 2007-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    TABLESPACE CDRST01
    PCTFREE    0
    INITRANS   10
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                FREELISTS        5
                FREELIST GROUPS  1
                BUFFER_POOL      DEFAULT
               ) ...
 2/- command process :
sqlldr DATA=FILE_NAME LOG=FILE_NAME.log ERRORS=-1 BAD=FILE_NAME.bad CONTROL=FILE_NAME.ctl USERID=$UIDPWD

- controlfile  file :
# cat FILE_NAME.ctl:
LOAD DATA
APPEND
INTO TABLE DBM_MSC_CDR
(
SUBNO                         POSITION(385:404)       CHAR,
B_SUBNO                       POSITION(125:159)       CHAR,
ROUTE_IN                      POSITION(418:422)       CHAR,
ROUTE_OUT                     POSITION(405:411)       CHAR,
CALL_DURATION                 POSITION(192:196)       CHAR "DECODE(TRIM(:CALL_DURATION), '', 0, :CALL_DURATION)",
IMSI_NUMBER                   POSITION(10:24)         CHAR,
CHARGE_INDICATOR              POSITION(373:377)       CHAR,
CALL_TYPE                     POSITION(4:6)           CHAR,
CELL_ID                       POSITION(69:72)         CHAR,
IMEI_NUMBER                   POSITION(42:56)         CHAR,
TELE_SERVICE                  POSITION(197:199)       CHAR,
BEARER_SERVICE                POSITION(200:202)       CHAR,
MSRN                          POSITION(61:75)         CHAR,
NADI                          POSITION(57:58)         CHAR,
TRANSDATE                     POSITION(180:191)       DATE(12) "YYMMDDHH24MISS",
FILE_ID                               CONSTANT "ValueFileId"
)

3/- OS

HP-UX B.11.11 U 9000/800 with 8 cpu and 8192 MB of Ram

please help

Regards
0
OTAlgerie
Asked:
OTAlgerie
  • 3
  • 3
1 Solution
 
ishandoCommented:
Have you tried / checked the possibility of doing
- direct path load
- specifying the partition to load into
0
 
OTAlgerieAuthor Commented:
Hi,
I just change my controlfile with adding direct=y but if,   I run 2 or 3 process sqlldr in same time (parallel)

it generate :

ORA-00054: resource busy and acquire with NOWAIT specified
0
 
ishandoCommented:
did you use parallel=true also?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
OTAlgerieAuthor Commented:
ishando: wrote : did you use parallel=true also ?

it's done but i have this error :
SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table TABS.DBM_MSC_CDR has index defined upon it.

0
 
OTAlgerieAuthor Commented:
Hello,
any news ?
0
 
ishandoCommented:
There are certain restrictions on when you can use the direct load, have you check them? From Oracle docs:

"Restrictions on Using Direct Path Loads

The following conditions must be satisfied for you to use the direct path load method:

    * Tables are not clustered.
    * Tables to be loaded do not have any active transactions pending.

The following features are not available with direct path load.

    * Loading VARRAYs
    * Loading a parent table together with a child table
    * Loading BFILE columns

Restrictions on a Direct Path Load of a Single Partition

In addition to the previously listed restrictions, loading a single partition has the following restrictions:

    * The table that the partition is a member of cannot have any global indexes defined on it.
    * Enabled referential and check constraints on the table that the partition is a member of are not allowed.
    * Enabled triggers are not allowed.

Restrictions on Parallel Direct Path Loads

The following restrictions are enforced on parallel direct path loads:

    * Neither local or global indexes can be maintained by the load.
    * Referential integrity and CHECK constraints must be disabled.
    * Triggers must be disabled.
    * Rows can only be appended. REPLACE, TRUNCATE, and INSERT cannot be used (this is due to the individual loads not being coordinated). If you must truncate a table before a parallel load, you must do it manually.

If a parallel direct path load is being applied to a single partition, you should partition the data first (otherwise, the overhead of record rejection due to a partition mismatch slows down the load).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now