|
Oracle 10g
|
Last week, I analyzed Data Pump and proved that the loading/unloading of the
data with Data Pump was two times faster than that with Export/Import.
However, the degree of the improvement was not so remarkable as I had expected.
The following are result from the last time.
=============================================================================
Process Time (min) File size
----------------------------------------------- ---------- ------------------
1. Use Export to unload the data 47 19.86GB
2. Use Export to directly unload the data 32 18.69GB
3. Use Data Pump to unload the data 29 19.87GB
4. Use Import to load the data 63
5. Use Data Pump to load the data 26
=============================================================================
|
Regarding the loading, Read/Write contention on the same disk caused performance
slowdown.
This time, I use two physical disks, one for datafile and the other for dump file,
to analyze the loading.
Key point of Data Pump architecture is direct path and parallel process.
Parallel direct path allows multiple direct path load sessions, which enables
bringing out the best disk performance.
Previous disk structure:
Data file: Four RAID disks
Dump file: same as the above
Current disk structure:
Data file: Four RAID disks
Dump file: stand-alone disk
=============================================================================
Process Time (min) File size
----------------------------------------------- ---------- ------------------
1. Use Export to directly unload the data 16 18.69GB
2. Use Data Pump to unload the data 12 19.87GB
3. Use Import to load the data 56
4. Use Data Pump to load the data 10
=============================================================================
|
Loading with Import takes 56 minutes. Loading with Data Pump, on the other hand,
takes only 10 minutes, which is about five times faster than that with Import.
BTW, direct path load reminds me of direct mode of SQL*Loader. I wonder if these two processes
operate in the same manner.
SQL*Loader inserts the data beyond high-water mark (HWM) directly, and then raises
HWM. By doing this, unnecessary overhead can be prevented (such as coalesce if there is no
free space.)
Now, I use Data Pump to check HWM behavior.
1. Create sample data
SQL> create table hign_water_mark (id number, text varchar2(128))
2 > tablespace test;
Create sample data
SQL> begin
2 > for i in 10000 loop
3 > insert into high_water_mark values (i,'test text : '||i');
4 > if (mod(i,1000)=0) then
5 > commit;
6 > end if;
7 > end loop;
8 > commit;
9 > end;
10> /
|
2. Check the current HWM
SQL> var total_blocks number
SQL> var total_bytes number
SQL> var unused_blocks number
SQL> var unused_bytes number
SQL> var last_used_extent_file_id number
SQL> var last_used_extent_block_id number
SQL> var last_used_block number
SQL>
SQL> exec dbms_space.unused_space('TPC','HIGH_WATER_MARK','TABLE'
2 > ,:total_blocks
3 > ,:total_bytes
4 > ,:unused_blocks
5 > ,:unused_bytes
6 > ,:last_used_extent_file_id
7 > ,:last_used_extent_block_id
8 > ,:last_used_block);
TOTAL_BLOCK of HWM is 896.
|
3. Delete
Delete the data without lowering HWM.
4. Conventional load
5. Check the current HWM.
Execute the same SQL statement as step 2 and find out that the number of blocks is 896.
6. Delete
7. Load with Data Pump
8. Check the current HWM.
Execute the same SQL statement as step 2 and find out that the number of blocks is 1536,
which means HWM has been raised.
This proves that the loading with Data Pump raises HWM. As HWM rises, it may cause
performance degradation in full scan.
When you set TABLE_EXISTS_ACTION to APPEND and add the data to the existing data,
be aware of this rising of HWM.
When data is loaded with SQL*Loader direct path, pre-loaded trigger is once disabled
and is enabled again after the data is loaded properly.
I wonder if the trigger functions properly when data is loaded with Data Pump.
First, I create a trigger that inserts the data to WAREHOUSE table and then updates WARE_TMP
table.
SQL> create table ware_tmp (id number default 0);
Table created.
SQL> create trigger ware_test_trg after insert
2 on warehouse for each row
3 begin
4 update ware_tmp set id=id+1;
5 end;
6 /
Trigger created.
SQL> insert into ware_tmp values (0);
One row created.
SQL> commit;
Comitted.
|
I load the data with Data Pump.
-------------------------------------------------------------------------------
Import: Release 10.1.0.2.0 - Production on Sunday, 13 June, 2004 20:11
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TPC"."SYS_IMPORT_TABLE_01" is loaded/unloaded successfully.
Starting "TPC"."SYS_IMPORT_TABLE_01":
tpc/******** directory=pump_dir2 dumpfile=exp_pump_obj.dmp tables=warehouse
content=data_only logfile=imp_pump_obj.log parallel=2 table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA...
. . "TPC"."WAREHOUSE" 7.898 KB 5 rows imported
Job "TPC"."SYS_IMPORT_TABLE_01" is completed at 20:11
-------------------------------------------------------------------------------
|
I check if the trigger functions properly.
SQL> select * from ware_tmp;
ID
----------
5
|
Contrary to SQL*Loader, the trigger does function properly even in direct mode.
When SQL*Loader direct load inserts duplicate primary key data to a table with
Primary Key constraint, Index becomes unusable.
I will analyze how the data is loaded with Data Pump.
First, I define primary key constraint to WAREHOUSE table.
SQL> alter table warehouse add constraint ware_pk primary key(w_id);
Table altered.
|
Now, I use Data Pump to directly load the data.
Import: Release 10.1.0.2.0 - Production on Sunday, 13 June, 2004 20:04
Copyright (c) 2003, Oracle. All rights reserved.
-------------------------------------------------------------------------------
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TPC"."SYS_IMPORT_TABLE_01" is loaded/unloaded successfully.
Starting "TPC"."SYS_IMPORT_TABLE_01":
tpc/******** directory=pump_dir2 dumpfile=exp_pump_obj.dmp tables=warehouse
content=data_only logfile=imp_pump_obj.log parallel=2 table_exists_action=append
Prosessing object-type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA...
ORA-31693: Table data object "TPC"."WAREHOUSE" failed to load/unload and is being
skipped due to error:
ORA-00001: Unique constraint (TPC.WARE_PK) violated
Job "TPC"."SYS_IMPORT_TABLE_01" is completed but error occurs at 20:04.
-------------------------------------------------------------------------------
|
I see that Primary Key constraint is surely enabled.
That's it for today.
Koji Shinkubo
|
|
