Recently, I was involved in a discussion about import performance in this OTN forum in which the original poster raised the issue of whether or not import will resort to single-row inserts for tables with date columns.

For array inserts, the buffer parameter essentially specifies the size of this array. We know, however, that if a table has lob columns, the import parameter buffer is not honored, and the import utility will use single-row inserts for those tables. But tables with date columns, so the claim goes, also must suffer single-row inserts. In this blog, I will probe this further and validate that claim.

Let’s create a table and populate it with 300K rows.

 create table t1 (n1 number, v1 varchar2 (512), d1 date);

 insert into t1
 select n1, lpad(n1, 500, 'x'), sysdate
 from (select level n1 from dual connect by level <=300003);
 commit;

 REM Creating an  export file..
 host exp userid=cbqt/cbqt file=exp_t1.dmp log=exp_t1.log tables=t1

The above code fragment created a table, inserted 300,000 rows, and exported that table to an export dump file. This dump file is ready to be imported. But, we need to trace the import to measure the effect of the buffer parameter. The problem, though, is how to trace the import session alone without generating every session in the database. This can be achieved by creating a logon trigger as below. Only sessions from a test user will have trace enabled from this trigger (the username is CBQT).

REM I could use "on schema clause too, but this is part of generic code that I use.
REM Riyaj Shamsudeen - To trace a session through logon trigger
create or replace trigger
set_system_event
after logon  on database
declare
v_user dba_users.username%TYPE:=user;
sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
  if (v_user = 'CBQT') THEN
      execute immediate sql_stmt1;
  end if;
end;
/

Let’s drop the table, then import with a default buffer size of 64K. Through the logon trigger, a new SQL trace file will be generated. That trace file will be analyzed with the tkprof utility as shown in the code fragment below:

(more…)