Speed-up loadercli data import run?

[Former Member]

Hi all,

I do not know if a similar one was posted before (I did not find..)..

We are just migrating schema data from one MaxDB to another (both v7.6) using the loadercli with loader command files (pls. see command extracts below) that export the schema data table-wise, and then import the table data into the new DB again (in order to cover look-up relations via FKs). The export takes ~12min while the import takes _{3h. - There is a very long table (}6.4mio rows) included. But this import duration lasts too long!

Is there any possibility to speed-up this loadercli import (like e.g. Oracle SQL*Loader's direct load without parsing..)?

-Thanks in advance!

kind regards

Frank

Export loader command:

EXPORT TABLE <schema>.<table>

DATA OUTSTREAM FILE '<path>\<export_data_file>'

RECORDS

NULL '?'

DATE ISO

TIME ISO

TIMESTAMP ISO

DECIMAL '/./,/'

BOOLEAN 'TRUE/FALSE'

PACKAGE OUTSTREAM FILE '<path>\<package_file>'

CSV

NULL '?'

DATE ISO

TIME ISO

TIMESTAMP ISO

DECIMAL '/./,/'

BOOLEAN 'TRUE/FALSE'

//

Import loader command:

IMPORT TABLE <schema>.<table>

REJECT DUPLICATES

DATA INSTREAM FILE '<path>\<export_data_file>'

RECORDS

NULL '?'

DATE ISO

TIME ISO

TIMESTAMP ISO

DECIMAL '/./,/'

BOOLEAN 'TRUE/FALSE'

PACKAGE OUTSTREAM FILE '<path>\<package_file>'

CSV

NULL '?'

DATE ISO

TIME ISO

TIMESTAMP ISO

DECIMAL '/./,/'

BOOLEAN 'TRUE/FALSE'

//

[lbreddemann · Accepted solution]

Hi Frank,

what is the database doing all the time?

Have you checked x_cons <sid> show active?

Is the DB Analyzer running? If so, any warnings during the import?

In general there is no 'go_faster' switch. We need to know what is talking the time here, than we can (hopefully) come up with a way to improve this.

BTW: send us your parameters, the exact MaxDB version etc. - ah - just copy/paste the startup part from the knldiag file!

And before I forget it: be so kind and tell us about the database you're building there.

What is it for? How large is the database going to be in total? How many users? How many schemas/tables etc.?

This has nothing to do with your problem, but I'm still interested in NON-SAP MaxDB implementations. So let us know a bit more about your db

regards

Lars

Edited by: Lars Breddemann on Aug 13, 2008 11:11 PM

[Former Member · Accepted solution]

Hi Lars,

+ What is the database doing all the time? Have you checked x_cons <sid> show active?

Unfortunately not during that import..sorry for that. But when we re-run the import I will check with the x_cons tool.

+ Is the DB Analyzer running? If so, any warnings during the import?

Similar to the question above: not yet. sorry

+ Send us your parameters, the exact MaxDB version etc. - ah - just copy/paste the startup part from the knldiag file!

pls. see below

+ What is database for? How large is the database going to be in total? How many users? How many schemas/tables etc.?

Background: We are a semiconductors test center which is testing ICs for their electr. functionality. For certain products additional things are done at testtime like patching firmware etc., or unique serial numbers. That is what this database is dedicated for; it is providing unique serial numbers for destined products (e.g. ICs in FIFA world cup 2006's tickets). The provided serial numbers are checked out (in order guarantee uniqueness), and the check-out records are stored in the DB for later tracking purposes.

The DB for itself is not that large (~1,5GB) and has just the schema with 15 tables for this unique serial number application, but the table storing the check-out transactions has ~6.4mio rows. There are up-to 100 users (test machines auto-connecting via XUser) running against this DB.

kind regards

Frank

-

---

Date Time TID(hex) Typ MsgID Label Message-Text

-

---

2008-08-07 15:18:29 0x9DC 20233 RTE Dump of all kernel parameters start

2008-08-07 15:18:29 0x9DC 20206 RTE Using mode NORMAL for data volume 1

2008-08-07 15:18:29 0x9DC 20206 RTE Using mode NORMAL for data volume 2

2008-08-07 15:18:29 0x9DC 20206 RTE Using mode NORMAL for data volume 3

2008-08-07 15:18:29 0x9DC 20234 RTE ADMIN=1

2008-08-07 15:18:29 0x9DC 20234 RTE AKDUMP_ALLOWED=YES

2008-08-07 15:18:29 0x9DC 20234 RTE ALLOW_MULTIPLE_SERVERTASK_UKTS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE AUTHENTICATION_ALLOW=

2008-08-07 15:18:29 0x9DC 20234 RTE AUTHENTICATION_DENY=

2008-08-07 15:18:29 0x9DC 20234 RTE AUTOSAVE=1

2008-08-07 15:18:29 0x9DC 20234 RTE AUTO_RECREATE_BAD_INDEXES=NO

2008-08-07 15:18:29 0x9DC 20234 RTE BACKUPRESULT=1

2008-08-07 15:18:29 0x9DC 20234 RTE BACKUP_BLOCK_CNT=64

2008-08-07 15:18:29 0x9DC 20234 RTE BACKUPHISTFILE=dbm.knl

2008-08-07 15:18:29 0x9DC 20234 RTE BACKUPMED_DEF=dbm.mdf

2008-08-07 15:18:29 0x9DC 20234 RTE CACHE_SIZE=2560

2008-08-07 15:18:29 0x9DC 20234 RTE CALLSTACKLEVEL=0

2008-08-07 15:18:29 0x9DC 20234 RTE CATCACHE_MINSIZE=262144

2008-08-07 15:18:29 0x9DC 20234 RTE CAT_CACHE_SUPPLY=4544

2008-08-07 15:18:29 0x9DC 20234 RTE CHECKDATA=1

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_BACKUP=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_COMMON=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_CONVERTER=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_DATACACHE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_DATAINDEX=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_DATAPAGELOG=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_FBM=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_HASHED_RESULTSET=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_IOMAN=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_KB_REGIONS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOCK_SUPPLY=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOCK=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOGHISTORY=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOGPAGE=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOGTRANS=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_LOGVOLUME=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_QUERYREWRITE=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_REGIONS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_SRVTASKS=0

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_TABLE_WIDTH=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_TASK_SPECIFIC_CATALOGCACHE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_TRANSLIST=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_TREE_LOCKS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CHECK_TREE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CLUSTERED_LOBS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE CLUSTER_WRITE_THRESHOLD=80

2008-08-07 15:18:29 0x9DC 20234 RTE COLUMNCOMPRESSION=YES

2008-08-07 15:18:29 0x9DC 20234 RTE _COMMENT=

2008-08-07 15:18:29 0x9DC 20234 RTE CONTROLPASSWORD=***

2008-08-07 15:18:29 0x9DC 20234 RTE CONTROLUSERID=DBM

2008-08-07 15:18:29 0x9DC 20234 RTE CONVERTER_REGIONS=8

2008-08-07 15:18:29 0x9DC 20234 RTE DATABASEFULL=1

2008-08-07 15:18:29 0x9DC 20234 RTE DATACACHE_RGNS=8

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_IO_BLOCK_COUNT=64

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_GROUPS=1

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_MODE_0001=NORMAL

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_MODE_0002=NORMAL

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_MODE_0003=NORMAL

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_NAME_0001=<path>\<sid>_data01.dbf

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_NAME_0002=<path>\<sid>_data02.dbf

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_NAME_0003=<path>\<sid>_data03.dbf

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_SIZE_0001=262144

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_SIZE_0002=262144

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_SIZE_0003=262144

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_TYPE_0001=F

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_TYPE_0002=F

2008-08-07 15:18:29 0x9DC 20234 RTE DATA_VOLUME_TYPE_0003=F

2008-08-07 15:18:29 0x9DC 20234 RTE DATE_TIME_FORMAT=INTERNAL

2008-08-07 15:18:29 0x9DC 20234 RTE DBFILLINGABOVELIMIT=70L80M85M90H95H96H97H98H99H

2008-08-07 15:18:29 0x9DC 20234 RTE DBFILLINGBELOWLIMIT=70L80L85L90L95L

2008-08-07 15:18:29 0x9DC 20234 RTE DDLTRIGGER=YES

2008-08-07 15:18:29 0x9DC 20234 RTE DEADLOCK_DETECTION=4

2008-08-07 15:18:29 0x9DC 20234 RTE DEFAULT_CODE=ASCII

2008-08-07 15:18:29 0x9DC 20234 RTE DELAYCOMMIT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE DELAYLOGWRITER=0

2008-08-07 15:18:29 0x9DC 20234 RTE DIAG_HISTORY_NUM=2

2008-08-07 15:18:29 0x9DC 20234 RTE DIAG_HISTORY_PATH=<path>\indep_data\wrk\<sid>\DIAGHISTORY

2008-08-07 15:18:29 0x9DC 20234 RTE DIAGSEM=0

2008-08-07 15:18:29 0x9DC 20234 RTE DWIO_AREA_FLUSH=50

2008-08-07 15:18:29 0x9DC 20234 RTE DWIO_AREA_SIZE=50

2008-08-07 15:18:29 0x9DC 20234 RTE DWLRU_TAIL_FLUSH=25

2008-08-07 15:18:29 0x9DC 20234 RTE ENABLE_CHECK_INSTANCE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE ENABLE_SYSTEM_TRIGGERS=YES

2008-08-07 15:18:29 0x9DC 20234 RTE ERROR=3

2008-08-07 15:18:29 0x9DC 20234 RTE _EVENTFILE=knldiag.evt

2008-08-07 15:18:29 0x9DC 20234 RTE _EVENTSIZE=0

2008-08-07 15:18:29 0x9DC 20234 RTE EVENT=1

2008-08-07 15:18:29 0x9DC 20234 RTE EXPAND_COM_TRACE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE EXTERNAL_DUMP_REQUEST=NO

2008-08-07 15:18:29 0x9DC 20234 RTE FBMLOW_IO_RATE=10

2008-08-07 15:18:29 0x9DC 20234 RTE FBM_VOLUME_BALANCE=10

2008-08-07 15:18:29 0x9DC 20234 RTE FBM_VOLUME_COMPRESSION=50

2008-08-07 15:18:29 0x9DC 20234 RTE FILEDIR_SPINLOCKPOOL_SIZE=10

2008-08-07 15:18:29 0x9DC 20234 RTE FORBID_LOAD_BALANCING=NO

2008-08-07 15:18:29 0x9DC 20234 RTE FORMATTING_MODE=PARALLEL

2008-08-07 15:18:29 0x9DC 20234 RTE FORMAT_DATAVOLUME=YES

2008-08-07 15:18:29 0x9DC 20234 RTE HASHED_RESULTSET_CACHESIZE=262144

2008-08-07 15:18:29 0x9DC 20234 RTE HASHED_RESULTSET=YES

2008-08-07 15:18:29 0x9DC 20234 RTE HEAP_CHECK_LEVEL=0

2008-08-07 15:18:29 0x9DC 20234 RTE HIRES_TIMER_TYPE=CPU

2008-08-07 15:18:29 0x9DC 20234 RTE HS_STORAGE_DLL=libhsscopy

2008-08-07 15:18:29 0x9DC 20234 RTE HS_SYNC_INTERVAL=50

2008-08-07 15:18:29 0x9DC 20234 RTE IDXFILELIST_SIZE=0

2008-08-07 15:18:29 0x9DC 20234 RTE INDEX_LEAF_CACHING=2

2008-08-07 15:18:29 0x9DC 20234 RTE INIT_ALLOCATORSIZE=262144

2008-08-07 15:18:29 0x9DC 20234 RTE INSTANCE_TYPE=OLTP

2008-08-07 15:18:29 0x9DC 20234 RTE IOPROCSFOR_PRIO=0

2008-08-07 15:18:29 0x9DC 20234 RTE IOPROCSFOR_READER=0

2008-08-07 15:18:29 0x9DC 20234 RTE IOPROCSPER_DEV=1

2008-08-07 15:18:29 0x9DC 20234 RTE IOPROCSSWITCH=2

2008-08-07 15:18:29 0x9DC 20234 RTE JOIN_MAXTAB_LEVEL4=16

2008-08-07 15:18:29 0x9DC 20234 RTE JOIN_MAXTAB_LEVEL9=5

2008-08-07 15:18:29 0x9DC 20234 RTE JOIN_SEARCH_LEVEL=0

2008-08-07 15:18:29 0x9DC 20234 RTE JOIN_TABLEBUFFER=128

2008-08-07 15:18:29 0x9DC 20234 RTE _KERNELDIAGFILE=knldiag

2008-08-07 15:18:29 0x9DC 20234 RTE KERNELDIAGSIZE=800

2008-08-07 15:18:29 0x9DC 20234 RTE _KERNELDUMPFILE=knldump

2008-08-07 15:18:29 0x9DC 20234 RTE _KERNELTRACEFILE=knltrace

2008-08-07 15:18:29 0x9DC 20234 RTE KERNELTRACESIZE=897

2008-08-07 15:18:29 0x9DC 20234 RTE KERNELVERSION=KERNEL 7.6.03 BUILD 015-123-173-107

2008-08-07 15:18:29 0x9DC 20234 RTE LOAD_BALANCING_CHK=0

2008-08-07 15:18:29 0x9DC 20234 RTE LOAD_BALANCING_DIF=10

2008-08-07 15:18:29 0x9DC 20234 RTE LOAD_BALANCING_EQ=5

2008-08-07 15:18:29 0x9DC 20234 RTE LOCAL_REDO_LOG_BUFFER_SIZE=0

2008-08-07 15:18:29 0x9DC 20234 RTE LOCKSUPPLY_BLOCK=100

2008-08-07 15:18:29 0x9DC 20234 RTE LOGABOVELIMIT=50L75L90M95M96H97H98H99H

2008-08-07 15:18:29 0x9DC 20234 RTE LOGFULL=1

2008-08-07 15:18:29 0x9DC 20234 RTE LOGSEGMENTFULL=1

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_BACKUP_TO_PIPE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_IO_BLOCK_COUNT=4

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_IO_QUEUE=50

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_MIRRORED=NO

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_QUEUE_COUNT=1

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_SEGMENT_SIZE=87381

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_NAME_001=<path>\<sid>_log01.dbf

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_NAME_002=<path>\<sid>_log02.dbf

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_SIZE_001=262144

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_SIZE_002=262144

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_TYPE_001=F

2008-08-07 15:18:29 0x9DC 20234 RTE LOG_VOLUME_TYPE_002=F

2008-08-07 15:18:29 0x9DC 20234 RTE LRU_FOR_SCAN=NO

2008-08-07 15:18:29 0x9DC 20234 RTE MAXBACKUPDEVS=2

2008-08-07 15:18:29 0x9DC 20234 RTE MAXCPU=1

2008-08-07 15:18:29 0x9DC 20234 RTE MAXDATAVOLUMES=10

2008-08-07 15:18:29 0x9DC 20234 RTE _MAXEVENTS=100

2008-08-07 15:18:29 0x9DC 20234 RTE _MAXEVENTTASKS=2

2008-08-07 15:18:29 0x9DC 20234 RTE MAXGARBAGECOLL=1

2008-08-07 15:18:29 0x9DC 20234 RTE MAXLOCKS=5680

2008-08-07 15:18:29 0x9DC 20234 RTE MAXLOGVOLUMES=10

2008-08-07 15:18:29 0x9DC 20234 RTE MAXPAGER=10

2008-08-07 15:18:29 0x9DC 20234 RTE MAXRGN_REQUEST=-1

2008-08-07 15:18:29 0x9DC 20234 RTE MAXSERVERTASKS=26

2008-08-07 15:18:29 0x9DC 20234 RTE MAXTASKSTACK=1024

2008-08-07 15:18:29 0x9DC 20234 RTE _MAXTRANS=568

2008-08-07 15:18:29 0x9DC 20234 RTE MAXUSERTASKS=70

2008-08-07 15:18:29 0x9DC 20234 RTE MAXVOLUMES=21

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_HASHTABLE_MEMORY=5120

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_LOG_QUEUE_COUNT=0

2008-08-07 15:18:29 0x9DC 20234 RTE MAXMESSAGE_FILES=0

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_MESSAGE_LIST_LENGTH=100

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_RETENTION_TIME=480

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_SERVERTASK_STACK=500

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_SINGLE_HASHTABLE_SIZE=512

2008-08-07 15:18:29 0x9DC 20234 RTE MAX_SPECIALTASK_STACK=500

2008-08-07 15:18:29 0x9DC 20234 RTE MBLOCKDATA_SIZE=32768

2008-08-07 15:18:29 0x9DC 20234 RTE MBLOCKQUAL_SIZE=32768

2008-08-07 15:18:29 0x9DC 20234 RTE MBLOCKSTACK_SIZE=32768

2008-08-07 15:18:29 0x9DC 20234 RTE MBLOCKSTRAT_SIZE=16384

2008-08-07 15:18:29 0x9DC 20234 RTE MCOD=NO

2008-08-07 15:18:29 0x9DC 20234 RTE MEMORY_ALLOCATION_LIMIT=0

2008-08-07 15:18:29 0x9DC 20234 RTE MINI_DUMP=NORMAL

2008-08-07 15:18:29 0x9DC 20234 RTE MINREPLYSIZE=4096

2008-08-07 15:18:29 0x9DC 20234 RTE MINSERVERTASKS=26

2008-08-07 15:18:29 0x9DC 20234 RTE MIN_RETENTION_TIME=60

2008-08-07 15:18:29 0x9DC 20234 RTE MONITOR_READ=2147483647

2008-08-07 15:18:29 0x9DC 20234 RTE MONITOR_ROWNO=0

2008-08-07 15:18:29 0x9DC 20234 RTE MONITOR_SELECTIVITY=0

2008-08-07 15:18:29 0x9DC 20234 RTE MONITOR_TIME=2147483647

2008-08-07 15:18:29 0x9DC 20234 RTE MPDISP_LOOPS=1

2008-08-07 15:18:29 0x9DC 20234 RTE MPDISP_PRIO=DEFAULT

2008-08-07 15:18:29 0x9DC 20234 RTE MPRGN_BUSY_WAIT=DEFAULT

2008-08-07 15:18:29 0x9DC 20234 RTE MPRGN_DIRTY_READ=DEFAULT

2008-08-07 15:18:29 0x9DC 20234 RTE MP_RGN_LOOP=-1

2008-08-07 15:18:29 0x9DC 20234 RTE MPRGN_PRIO=DEFAULT

2008-08-07 15:18:29 0x9DC 20234 RTE MPRGN_QUEUE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OFFICIAL_NODE=

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_HEAP_BLOCKSIZE=10000

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_HEAP_COUNT=1

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_HEAP_LIMIT=0

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_HEAP_THRESHOLD=100

2008-08-07 15:18:29 0x9DC 20234 RTE OMSREGIONS=0

2008-08-07 15:18:29 0x9DC 20234 RTE OMSRGNS=7

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_RUN_IN_UDE_SERVER=NO

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_STREAM_TIMEOUT=30

2008-08-07 15:18:29 0x9DC 20234 RTE OMS_VERS_THRESHOLD=2097152

2008-08-07 15:18:29 0x9DC 20234 RTE ONLINE=1

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_AGGREGATION=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_FETCH_REVERSE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_FIRST_ROWS=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_HASHTABLE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_HASH_MINIMAL_RATIO=1

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_ONEPHASE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_OPERATOR_SORT=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_OUTER=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_PARALLEL_MINSIZE=1000000

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_JOIN_PARALLEL_SERVERS=0

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_MIN_MAX=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_OPERATOR_JOIN_COSTFUNC=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_OPERATOR_JOIN=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_QUAL_ON_INDEX=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_QUERYREWRITE=OPERATOR

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIMIZE_STAR_JOIN=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIM_CACHE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIM_INV_ONLY=YES

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIM_JOIN_FETCH=0

2008-08-07 15:18:29 0x9DC 20234 RTE OPTIM_MAX_MERGE=500

2008-08-07 15:18:29 0x9DC 20234 RTE OUTOFSESSIONS=3

2008-08-07 15:18:29 0x9DC 20234 RTE PACKETSIZE=131072

2008-08-07 15:18:29 0x9DC 20234 RTE PAGESIZE=8192

2008-08-07 15:18:29 0x9DC 20234 RTE PREALLOCATE_IOWORKER=NO

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOBASE_COM=10

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOBASE_IOC=80

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOBASE_RAV=80

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOBASE_REX=40

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOBASE_U2U=100

2008-08-07 15:18:29 0x9DC 20234 RTE PRIOFACTOR=80

2008-08-07 15:18:29 0x9DC 20234 RTE PROTECT_DATACACHE_MEMORY=NO

2008-08-07 15:18:29 0x9DC 20234 RTE READAHEADBLOBS=32

2008-08-07 15:18:29 0x9DC 20234 RTE REQUEST_TIMEOUT=5000

2008-08-07 15:18:29 0x9DC 20234 RTE RESERVEDSERVERTASKS=15

2008-08-07 15:18:29 0x9DC 20234 RTE RESTARTTIME=600

2008-08-07 15:18:29 0x9DC 20234 RTE ROW_LOCKS_PER_TRANSACTION=50

2008-08-07 15:18:29 0x9DC 20234 RTE ROWRGNS=8

2008-08-07 15:18:29 0x9DC 20234 RTE _RTEDUMPFILE=rtedump

2008-08-07 15:18:29 0x9DC 20234 RTE RTE_TEST_REGIONS=0

2008-08-07 15:18:29 0x9DC 20234 RTE RUNDIRECTORY=<path>\indep_data\wrk\<sid>

2008-08-07 15:18:29 0x9DC 20234 RTE SEQUENCE_CACHE=1

2008-08-07 15:18:29 0x9DC 20234 RTE SERVERDBFOR_SAP=YES

2008-08-07 15:18:29 0x9DC 20234 RTE SESSION_TIMEOUT=900

2008-08-07 15:18:29 0x9DC 20234 RTE SET_VOLUME_LOCK=YES

2008-08-07 15:18:29 0x9DC 20234 RTE SHAREDSQL_CLEANUPTHRESHOLD=25

2008-08-07 15:18:29 0x9DC 20234 RTE SHAREDSQL_COMMANDCACHESIZE=262144

2008-08-07 15:18:29 0x9DC 20234 RTE SHAREDSQL=YES

2008-08-07 15:18:29 0x9DC 20234 RTE SHOW_MAX_KB_STACK_USE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE SHOW_MAX_STACK_USE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE SPINLOCK_BACKOFF_BASE=1

2008-08-07 15:18:29 0x9DC 20234 RTE SPINLOCK_BACKOFF_FACTOR=2

2008-08-07 15:18:29 0x9DC 20234 RTE SPINLOCK_BACKOFF_MAXIMUM=64

2008-08-07 15:18:29 0x9DC 20234 RTE SPINLOCK_LOOP_COUNT=30000

2008-08-07 15:18:29 0x9DC 20234 RTE STANDBY=1

2008-08-07 15:18:29 0x9DC 20234 RTE SUBTREE_LOCKS=NO

2008-08-07 15:18:29 0x9DC 20234 RTE SYMBOL_DEMANGLING=NO

2008-08-07 15:18:29 0x9DC 20234 RTE SYMBOL_RESOLUTION=YES

2008-08-07 15:18:29 0x9DC 20234 RTE SYSTEMERROR=3

2008-08-07 15:18:29 0x9DC 20234 RTE TABRGNS=8

2008-08-07 15:18:29 0x9DC 20234 RTE TASKCLUSTER01=tw;al;ut;2000sv,100bup;10ev,10gc;

2008-08-07 15:18:29 0x9DC 20234 RTE TASKCLUSTER02=ti,100dw;30000us;

2008-08-07 15:18:29 0x9DC 20234 RTE TASKCLUSTER03=compress

2008-08-07 15:18:29 0x9DC 20234 RTE TIME_MEASUREMENT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_AK=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_ALLOCATOR=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_CATALOG=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_CLIENTKERNELCOM=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_COMMON=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_COMMUNICATION=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_CONVERTER=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DATACACHE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DATACHAIN=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DATAINDEX=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DATAPAM=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DATATREE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DBPROC=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DEFAULT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_DELETE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_FBM=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_FILEDIR=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_FRAMECTRL=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_INDEX=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_INSERT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_IOMAN=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_IPC=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_JOIN=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_KSQL=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOCK=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOGACTION=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOGHISTORY=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOGPAGE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOGTRANS=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LOGVOLUME=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_LONG=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_MEMORY=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_MESSAGES=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECTCONTAINER=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECT_ADD=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECT_ALTER=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECT_FREE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECT_GET=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OBJECT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_CONTAINERDIR=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_CONTEXT=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_ERROR=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_FLUSHCACHE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_INTERFACE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_KEYRANGE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_KEY=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_LOCK=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_MEMORY=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_NEWOBJ=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_SESSION=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_STREAM=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_VAROBJECT=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OMS_VERSION=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_OPTIMIZE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_ORDER_STANDARD=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_ORDER=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGER=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_BUP=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_EV=2

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_GC=20

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_LW=5

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_PG=3

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_SV=5

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_TI=2

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_US=10

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES_UT=5

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PAGES=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_PRIMARY_TREE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_QUERYREWRITE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_RUNTIME=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SELECT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SHAREDSQL=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SQLMANAGER=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SRVTASKS=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_STOP_ERRORCODE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SYNCHRONISATION=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_SYSVIEW=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_TABLE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_TIME=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_UPDATE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE TRACE_VOLUME=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRANS_HISTORY_SIZE=0

2008-08-07 15:18:29 0x9DC 20234 RTE TRANSRGNS=8

2008-08-07 15:18:29 0x9DC 20234 RTE TRANS_THRESHOLD_VALUE=60

2008-08-07 15:18:29 0x9DC 20234 RTE UKT_CPU_RELATIONSHIP=NONE

2008-08-07 15:18:29 0x9DC 20234 RTE _UNICODE=NO

2008-08-07 15:18:29 0x9DC 20234 RTE UPDATESTAT_PARALLEL_SERVERS=0

2008-08-07 15:18:29 0x9DC 20234 RTE UPDATESTAT_SAMPLE_ALGO=1

2008-08-07 15:18:29 0x9DC 20234 RTE UPDSTATWANTED=1

2008-08-07 15:18:29 0x9DC 20234 RTE USED_MAX_LOG_QUEUE_COUNT=1

2008-08-07 15:18:29 0x9DC 20234 RTE USESELECTFETCH=YES

2008-08-07 15:18:29 0x9DC 20234 RTE USEUNICODECOLUMNCOMPRESSION=NO

2008-08-07 15:18:29 0x9DC 20234 RTE USEVARIABLEINPUT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE USEASYNC_IO=YES

2008-08-07 15:18:29 0x9DC 20234 RTE USE_BACKUP_SECURITY_DESCRIPTOR=YES

2008-08-07 15:18:29 0x9DC 20234 RTE USE_COROUTINES=YES

2008-08-07 15:18:29 0x9DC 20234 RTE USE_FIBERS=YES

2008-08-07 15:18:29 0x9DC 20234 RTE USEIOPROCS_ONLY=NO

2008-08-07 15:18:29 0x9DC 20234 RTE USE_OPEN_DIRECT_FOR_BACKUP=NO

2008-08-07 15:18:29 0x9DC 20234 RTE USE_OPEN_DIRECT=NO

2008-08-07 15:18:29 0x9DC 20234 RTE USE_SYSTEM_PAGE_CACHE=YES

2008-08-07 15:18:29 0x9DC 20234 RTE UTILITYPROTFILE=dbm.utl

2008-08-07 15:18:29 0x9DC 20234 RTE UTILITY_PROTSIZE=100

2008-08-07 15:18:29 0x9DC 20234 RTE VOLUMENO_BIT_COUNT=8

2008-08-07 15:18:29 0x9DC 20234 RTE WORKDATASIZE=8192

2008-08-07 15:18:29 0x9DC 20234 RTE WORKSTACKSIZE=8192

2008-08-07 15:18:29 0x9DC 20234 RTE XP_CONVERTER_REGIONS=0

2008-08-07 15:18:29 0x9DC 20234 RTE XP_DATA_CACHE_RGNS=0

2008-08-07 15:18:29 0x9DC 20234 RTE XP_MAXPAGER=0

2008-08-07 15:18:29 0x9DC 20235 RTE Dump of all kernel parameters done

[lbreddemann · Accepted solution]

Hi Frank,

thanks for describing your application!

Concerning your issue - well we would need the performance data, sorry about that.

The only thing from the parameters I would change is the size of the LOG QUEUE so that there are not too many waits for the log IO during the huge load.

Another thing: are the FK constraints all in place and active during the load?

If so, I would try to deactivate them for the load and activate them afterwards.

Regards

Lars

[Former Member · Accepted solution]

Hi Lars,

regarding the performance data: We just ran another import test with the DB analyzer sniffing (=> dbanalyzer -n <node> -d <sid> -u <user>,<pwd> -c 2 -o <path>\<out file>) and a cyclic x_cons job (every 10min, ) in place. Is there any upload facility for files in this forum (I did not find one up-to-now..)? Or how should I post the output?

The FK constraints were all in place and active during the load. But perhaps this is an option to install the constraints (maybe the PKs + UKs, too?) after the import..

kind regards

Frank

[lbreddemann · Accepted solution]

Hi Frank,

just send me the files as an email attachment.

There is no file storage facility in the forum.

best regard,

Lars

[lbreddemann · Accepted solution]

Ok, Frank send me the analysis data.

>The suggestion of yours to switch off the FKs is very interesting. We will do another run without all FKs.. >Are PKs, UKs, or after insert triggers impacting on the import performance?

All kinds of constraints will cost performance - of course they do, since every record needs to be checked against every single constraint.

Anyhow - disabling them may lead to inconsistencies that have to be resolved manually afterwards. So this way should be taken only with great care.

Besides, from the short glimpse I had on the data I don't suspect the constraints to be the issue.

The DBAnalyzer brought up some W3 Warnings like:

• W3 Lock list escalations: 3204 (sic !)

and

• W3 Log queue overflows: 10

Lock list escalations are a very bad thing that happens when a task is locking so many rows on a table that the lock-management resource in the system is allocated mostly by these locks.

In that case MaxDB converts many row locks to one big table lock.

So the first advice is:

Raise your MAXLOCKS parameter from currently 5680 to - say 150000!

Advice #2:

Increase the LOG_IO_QUEUE from currently 50 to - say 1000

Try your import again after a restart of the database.

How is the performance now?

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

after following your advices to modify the DB params to MAXLOCKS=150000 and LOG_IO_QUEUE=1000 (and additionally MAXCPU=2) we made another import run without switching off the FKs (and the other constrains..) with the results that the import lasts 1,5h. This is acceptable!

During this test import we ran the DB Analyzer and the X_Cons tools again. Maybe it is interessting to know if there still occur any warnings (esp. W3s..)?!

-Thanks so far!

kind regards

Frank

[steffen_schildberg · Accepted solution]

Hi Frank,

you could additionally lower the COMMIT frequency of the Loader during IMPORT using the command

SET TRANSACTION SIZE <value>. I think an acceptable value would be 100000 in your case where the long lasting table has 6.4 Mio rows. As default the Loader uses the size of 10 communication packets which might result in a high COMMIT frequency during IMPORT. On the other hand in case of failure the last <= 100000 rows are lost and need to be imported again.

And yes the Loader offers the possibility to load the data very fast like Oracle does with Direct Path Load - use the data format PAGES for this. This has, however, some constraints:

- Readable text files (like your CSV files) can be FASTLOADed only if the table does not have LONG columns.After a successful FASTLOAD a backup is mandatory otherwise the imported tables stay read only.

- It is even possible to EXPORT and IMPORT in format PAGES (this would speed up the EXPORT, too). After successful IMPORT a backup is mandatory, too. In this case the code type may not change (ASCII <--> UNICODE) between source and target database, the tables exported and imported must be identical in definition, and the target table must be empty.

May be its worth to try this format out.

Best regards,

Steffen

[lbreddemann · Accepted solution]

Hi Frank,

I checked the DBAN.prt file you send me and there are far less warnings in it now.

You may very well examine the DB Analyzer protocols yourself - the MaxDB DBA Studio (which is available as a free download here in SDN) has a neat feature to handle DB Analyzer and analyze the logs it creates.

As the detailed logs are all CSV files you can also import them into e.g. Excel for trend analysis etc. But beware of Excel's auto-dataformat-guessing functionality!

Anyhow I can only agree with the hints Steffen gave you - the loadercli provides many options to speed up processing even if the DB is correctly setup. Nevertheless, if it would be my task to import the data I would check wether deactivating the constrains can improve the performance even more.

Ok - if your problem is solved so far, don't forget to mark the question as answered.

best regards,

Lars

[Former Member · Accepted solution]

Hi Steffen & Lars,

first of all sorry for my late response! (Got stuck somewhere else..)

Your tuning tips were a full success!! - Increasing the DB init parameters (MAXCPU, MAXLOCKS and LOG_IO_QUEUE) reduced the import duration by the half (~3h -> ~1,5h). Additionally introducing SET TRANSACTION SIZE statements into the loader command file for this import saved another ~20min, so that we ended up with 1:12min(!). That is totally sufficient, not to say great! Because of pressing other tasks we did not test a switch to the PAGES format (with a mandatory backup after the import)..

Thanks a lot you both!!

kind regards

Frank