Expert secrets on oradebug

Oracle provides an internal and poorly documented utility called oradebug.

The oradebug utility provides useful functions for debugging and tracing Oracle database errors and can also be quite handy for tracing SQL statements to output to the tkprof utility for analysis by Oracle technicians. However, there are some caveats with the use of the oradebug tool and it should be used only under the careful guidance of Oracle support to avoid potential damage to production and other critical Oracle databases.


Here are a few of the many functions available for the expert Oracle DBA with oradebug.


C:\oradebug>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 19 14:46:19 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.

SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID Set OS pid of process to debug
SETORAPID ['force'] Set Oracle pid of process to debug
SHORT_STACK Dump abridged OS stack
DUMP [addr] Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT Set trace event in process
SESSION_EVENT Set trace event in session
DUMPVAR [level] Print/dump a fixed PGA/SGA/UGA
DUMPTYPE

Print/dump an address with type info
SETVAR Modify a fixed PGA/SGA/UGA PEEK [level] Print/Dump memory
POKE Modify memory
WAKEUP Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
FLUSH Flush pending writes to trace CLOSE_TRACE Close trace file
TRACEFILE_NAME Get name of trace file
LKDEBUG Invoke global enqueue service


SGATOFILE Dump SGA to file
DMPCOWSGA Dump & map SGA as COW
MAPCOWSGA Map SGA as COW
HANGANALYZE [level] [syslevel] Analyze system hang
FFBEGIN Flash Freeze the Instance
FFDEREGISTER FF deregister instance from cluster
FFTERMINST Call exit and terminate instance
FFRESUMEINST Resume the flash frozen instance
FFSTATUS Flash freeze status of instance
UNLIMIT Unlimit the size of the trace file
PROCSTAT Dump process statistics



As you can see, there are quite a few options to the oradebug utility.
The most useful features of the oradebug utility are for hang and crash analysis for Oracle database environments. If there is a core dump or bug that must be resolved with the help of Oracle support, the Oracle DBA can generate the crash dump analysis and work through the crisis with confidence and less delay by dumping the memory contents of the SGA to the trace file and handing this off to the advanced Oracle internal support engineer for quick analysis and problem resolution.

Another use for oradebug is for performance analysis and tuning. SQL statements can be traced at the kernel level with oradebug which aids in the tuning of SQL for the Oracle database environment.

We will next cover a quick example of how to use oradebug utility to trace a user SQL session.

Oradebug requires the SYSDBA level privileges account ie) SYS to run from within a new SQL*Plus session.

First we will set our oradebug to trace the current user session with the setmypid command.

SQL> oradebug setmypid
Statement processed.
SQL>

Now lets use an ALTER SESSION command to tidy up the name for the oradebug file to make things easier when we need to locate the trace file.

SQL> alter session set tracefile_identifier='mytrace';

Session altered.

Now, let’s check where our trace file is stored with the following oradebug command:

SQL> oradebug tracefile_name
c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc

By default, oradebug writes out the trace file to the USER_DUMP_DESTINATION directory.

Now lets setup our SQL trace for our current user session and generate some database activity to capture with oradebug.

Oradebug uses events to capture various database events for Oracle. For a SQL trace, the specific event is 10046. In addition to these events for capturing low level Oracle database activity, oradebug also has many levels available for setting the trace.

Some of these trace levels with oradebug include:

Level 1 – contains the basic level of trace information. For example, this trace level will display the bind variables in PL/SQL and SQL statements.

Level 8 - provides the trace details from Level 1 plus the wait events for elapsed times that are more than current CPU timings.

Level 12- adds in all the previous trace level information in addition to all wait event information.

Since we are using oradebug in a test environment, let’s enable a full trace to capture as much detail as possible for SQL activity with our user session. We also want to set the trace file to be unlimited in size so no errors occur during the tracing activity.

SQL> oradebug unlimit
Statement processed.

SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

Now that we have the trace enabled for SQL statements with oradebug, lets generate some activity.

SQL> create table t1 (a number, b varchar2(100));
Table created.

SQL> insert into t1
2 values (1,'A');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into t1
2 values (2,'B');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into t1
2 values (3,'C');

1 row created.

SQL> commit;
Commit complete.

SQL> insert into t1
2 values (3,'C');

1 row created.

Now let’s end our trace session for oradebug and review the new trace file.

SQL> oradebug event 10046 trace name context off
Statement processed.

SQL> oradebug tracefile_name
c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc

We can format our trace file with tkprof or review the raw data generated by oradebug in the trace file. First, we will look at the raw trace dump that we generated with oradebug and then we will conclude our brief visit to oradebug with a tkprof formatted trace output.

Our raw dump in the trace file reviews many low level details for Oracle as shown below.

*** TRACE DUMP CONTINUED FROM FILE ***

Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc
Mon May 19 15:12:22 2008
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:856M/2037M, Ph+PgF:2240M/3934M, VA:979M/2047M
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 26

Windows thread id: 1348, image: ORACLE.EXE (SHAD)

*** 2008-05-19 15:12:22.218
*** SERVICE NAME:(SYS$USERS) 2008-05-19 15:12:22.187
*** SESSION ID:(135.279) 2008-05-19 15:12:22.187
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=18507445018
*** 2008-05-19 15:13:20.187
WAIT #0: nam='SQL*Net message from client' ela= 57993544 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=18565462754
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #4 len=42 dep=0 uid=0 oct=1 lid=0 tim=18565469559 hv=4239432121 ad='40adba98'
create table t1(a number, b varchar2(100))
END OF STMT
PARSE #4:c=0,e=3292,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18565469553
BINDS #4:
=====================
PARSING IN CURSOR #3 len=51 dep=1 uid=0 oct=3 lid=0 tim=18565479916 hv=1523794037 ad='46295e4c'
select audit$,options from procedure$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565479911
BINDS #3:
kkscoacd

Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0a2dbffc bln=22 avl=03 flg=05
value=10200
EXEC #3:c=15625,e=11817,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565499761
FETCH #3:c=0,e=59,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=18565501551
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=0 pw=0 time=64 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=109 op='INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=32 us)'
=====================
PARSING IN CURSOR #1 len=179 dep=1 uid=0 oct=3 lid=0 tim=18565507100 hv=2812844157 ad='462ee788'
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #1:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565507094
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0a2dc1d8 bln=22 avl=03 flg=05
value=10200
EXEC #1:c=15625,e=13251,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565529339
FETCH #1:c=0,e=545,p=0,cr=20,cu=0,mis=0,r=1,dep=1,og=4,tim=18565531768
FETCH #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565533741
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565535668
FETCH #1:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565537698
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565539607
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565541509
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565543419
FETCH #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565545385
STAT #1 id=1 cnt=7 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=20 pr=0 pw=0 time=654 us)'
STAT #1 id=2 cnt=7 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=20 pr=0 pw=0 time=495 us)'
STAT #1 id=3 cnt=7 pid=2 pos=1 obj=92 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=136 us)'
STAT #1 id=4 cnt=7 pid=3 pos=1 obj=122 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=0 pw=0 time=62 us)'
STAT #1 id=5 cnt=7 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=16 pr=0 pw=0 time=257 us)'
STAT #1 id=6 cnt=7 pid=5 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=9 pr=0 pw=0 time=116 us)'

In the header section of the trace file, oradebug has provided us with useful configuration information on the system and database environment. Next, we see the dump output for wait events and SQL statements for our Oracle 10g database instance.

Now we will conclude our brief visit to the world of oradebug with a brief example of using tkprof with the trace file for database tuning analysis.

To format our trace file generated from our recent oradebug session we need to execute the tkprof command from the trace file default directory as follows.

C:\oradebug>cd c:\oracle\product\10.2.0\admin\orcl\udump
C:\oracle\product\10.2.0\admin\orcl\udump>

tkprof orcl_ora_1348_mytrace.trc trace_sql.txt explain=system/oracle
sys=no waits=yes

TKPROF: Release 10.2.0.1.0 - Production on Mon May 19 15:24:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Now, let’s review the contents of our newly formatted trace file:

TKPROF: Release 10.2.0.1.0 - Production on Mon May 19 15:24:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: orcl_ora_1348_mytrace.trc
Sort options: default

***********************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
***********************************************************************

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (XDB) (recursive depth: 1)
**********************************************************************

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (XDB) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'USER$'
(CLUSTER)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'I_USER1' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (CLUSTER) OF 'TS$' (CLUSTER)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'I_TS#' (INDEX

SELECT /*+ ALL_ROWS */ COUNT(*)
FROM
ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND
(POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION =
:B1

call count cpu elapsed disk query current rows
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=3 pr=0 pw=0 time=148 us)
1 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=43 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 FILTER
1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DUAL' (TABLE)

***********************************************************************

Misses in library cache during parse: 6

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- --------SQL*Net message to client 28 0.00 0.00
SQL*Net message from client 28 178.60 331.48
SQL*Net break/reset to client 2 0.00 0.00
db file sequential read 2 0.00 0.00
reliable message 1 0.00 0.00
enq: RO - fast object reuse 1 0.00 0.00
rdbms ipc reply 1 0.00 0.00
log file sync 6 0.00 0.00

As you can see the oradebug utility provides a wealthy of information and serves as a goldmine of resources for the Oracle expert practitioner who must resolve the toughest Oracle problems. It will allow you to develop insight into the Oracle database engine on how the nuts and bolts of the Oracle database really works.

Maximizing Export Performance

Many DBAs are faced with the challenge of speeding up utility functions such as export. Typically, an organization has only a small window for maintenance, and utility jobs must complete within that timeframe. Fortunately, there are a few things a DBA can do to expedite exports. These include:

• Use Direct Path – Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

• Use Subsets – By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

• Use a Larger Buffer – For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance. The following formula can be used to determine a proper buffer size:

buffer size = rows in array * max row size

• Separate Tables – Separate those tables that require consistent=y from those that don’t, in order to expedite the export. This way, the performance penalty will only be incurred for those tables that actually require it.

For the table with one million rows, the following benchmark tests were performed using the different export options.


Table 4.1- Benchmark tests performed using the different export options.

The table above reveals a small improvement in performance was obtained by increasing the buffer size on a conventional export. Using direct=y offered no performance boost over conventional, until it was accompanied by recordlength, which reduced the elapsed time by 25 percent.

Once data has been successfully copied to an export file, it can then be used by the import utility, as described in the next section.

SOURCE : http://www.remote-dba.net/teas_rem_util11.htm

Hypercharge Oracle data load speed

Techniques for improving the speed of Oracle import (imp or impdp) and Oracle SQL*Loader (sqlldr) jobs.

For professionals only

Of all of the performance techniques for database loading, the most important is the nologging operation, and the undocumented parameter _disable_logging=TRUE.

Note this discussion where Oracle expert Mark Bobak notes that you cannot re-start a failed instance when using _disable_logging=true, and Don Burleson notes that _disable_logging should only be used after taking a full, recoverable backup:

"The main things to consider about NOLOGGING:

- Supported feature of Oracle
- Works only with direct load insert
- Still maintains minimal logging, i.e., space transactions, extent invalidation records for loaded extents, etc.

Contrast with _disable_logging=TRUE:

- Undocumented, unsupported parameter
- all redo writes to log buffer still happen
- writes of redo buffer down to disk are disabled.
- Not limited to direct load insert. All operations are no longer protected by redo on disk.

The big difference is, if your instance dies, (crash or shutdown abort) there's no data in the online redo logs to even do instance recovery. This is very bad. Your database could suffer a corruption from which you can't recover."

All experienced DBA's know that You MUST get approval from Oracle Technical Support for all usage of undocumented parameters, but in the hands of a well-trained DBA, the hidden parameters are often a Godsend, especially for speeding-up rebuilds during tight maintenance windows with limited downtime.

The backup sandwich:

• Take a dump (full system backup)
  

• Load everything I can in PARALLEL NOLOGGONG mode (ONLY during a scheduled downtime window).
  

• Take a dump. Take a full system backup right before bringing the system online.

Most DBA's call this high-speed maintenance a "backup sandwich", it's quite common in my world where you have super-tight maintenance windows and every second counts.

Remember, unless you take a full backup after your maintenance, using _disable_logging=TRUE, an instance crash or a shutdown abort WILL trash your Oracle database.

Next, lets examine tips and techniques to improve the performance of all types of data loading, including data pump import (impdp), sql*Loader and SQL INSERT DML.

Maximizing Oracle Insert Performance

Regardless of which options were used when the data was exported, it has no influence on how the data is imported. For example, it is irrelevant to the import process whether it was a direct path export or not, since it is a plain export file, be it generated from direct or conventional means.

Unfortunately, there is no direct option available for imports (only for export and SQL*loader). The import process has more tuning limitations than other utilities. The DBA should consider the following when trying to optimize import performance:

Analyze once after the load - Set analyze=n and analyze with dbms_stats after the load has completed.

Use Solid-state disk - For a fully-tuned import job, only faster devices can speed-up import rates. Many large companies used partitioned tables, and keep the current partition on SSD for fast imports.

Increase recordlength - Many set recordlength to 64k, but it needs to be a multiple of your I/O chunk size and db_block_size (or your multiple block size, e.g. db_32k_block_size).

Set commit=n – For tables that can afford not to commit until the end of the load, this option provides a significant performance increase. Larger tables may not be suitable for this option due to the required rollback/undo space.

Dedicate a single, large rollback segment - Many professionals create a single large rollback segment and take all others offline during theimport.

Set indexes=n – Index creation can be postponed until after import completes, by specifying indexes=n. If indexes for the target table already exist at the time of execution, import performs index maintenance when data is inserted into the table. Setting indexes=n eliminates this maintenance overhead. You can also Use the indexfile parm to rebuild all the indexes once, after the data is loaded.

Defer CBO stats - Using impdp with the parameter exclude=statistics will greatly improve the import speed, but statistics will need to be re-analyzed or imported later.

Use the buffer parameter – By using a larger buffer setting, import can do more work before disk access is performed.

Disable logging - You can also use the hidden parameter _disable_logging = true to reduce redo, but beware that the resulting import will be unrecoverable.

Import Option	Elapsed Time (Seconds)	Time Reduction
commit=y	120	-
commit=y buffer=64000	100	17%
commit=n buffer=30720	72	40%
commit=N buffer = 64000	67	44%

Also see Metalink Note 155477.1 on improving importing speed:

You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export.

Your exact performance gain depends upon the following factors:

- DB_BLOCK_SIZE

- the types of columns in your table

- your I/O layout (the drive receiving the export file should be separate from the disk drive where the database files reside)

For example, invoking a Direct path Export with a maximum I/O buffer of 64kb can improve the performance of the Export with almost 50%.

This can be achieved by specifying the additional Export parameters DIRECT and RECORDLENGTH. e.g.:

exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

imp userid=system/manager full=y recordlength=65535 file=exp_full.dmp log=imp_full.log

Maximizing SQL*Loader Performance

Oracle SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data loads. These include:

1. Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in Oracle database block format. The fact that SQL is not being issued makes the entire process much less taxing on the database. There are certain cases, however, in which direct path loads cannot be used (clustered tables). To prepare the database for direct path loads, the script $ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.

2. Disable Indexes and Constraints. For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.

3. Use a Larger Bind Array. For conventional data loads only, larger bind arrays limit the number of calls to the database and increase performance. The size of the bind array is specified using the bindsize XE "bindsize" parameter. The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum length of each row.

4. Use ROWS=n to Commit Less Frequently. For conventional data loads only, rows specifies the number of rows per commit. Issuing fewer commits will enhance performance.

5. Use Parallel Loads. Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently.

$ sqlldr control=first.ctl parallel=true direct=true

$ sqlldr control=second.ctl parallel=true direct=true

6. Use Fixed Width Data. Fixed width data format saves Oracle some processing when parsing the data. The savings can be tremendous, depending on the type of data and number of rows.

7. Disable Archiving During Load. While this may not be feasible in certain environments, disabling database archiving can increase performance considerably.

8. Use unrecoverable. The unrecoverable option (unrecoverable load data) disables the writing of the data to the redo logs. This option is available for direct path loads only.

Optimizing Oracle INSERT performance

When using standard SQL statements to load Oracle data tables, there are several tuning approaches:

a - Manage segment header contention for parallel inserts - Make sure to define multiple freelist (or freelist groups) to remove contention for the table header. Multiple freelists add additional segment header blocks, removing the bottleneck. You can also use Automatic Segment Space Management (bitmap freelists) to support parallel DML, but ASSM has some limitations.

b - Parallelize the load - You can invoke parallel DML (i.e. using the PARALLEL hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists or freelist groups. Mark Bobak notes that if you submit parallel jobs to insert against the table at the same time, using the APPEND hint may cause serialization, removing the benefit of parallel jobstreams.

c - APPEND into tables - By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint. Mark Bobak notes "Also, if you're going w/ APPEND, consider putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging."

insert /*+ append */ into customer values ('hello',';there');

d - Use a large blocksize - By defining large (i.e. 32k) blocksizes for the target table, you reduce I/O because more rows fit onto a block before a "block full" condition (as set by PCTFREE) unlinks the block from the freelist.

e - Disable/drop indexes - It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.

Using SSD for insert tablespaces

For databases that require high-speed loads, some shops define the insert table partition on solid-state disk (later moving it to platter disk). Mike Ault notes in his book "Oracle Solid-State Disk Tuning", a respectable 30% improvement in load speed:

“In the SSD verses ATA benchmark the gains for insert and update processing as shown in the database loading and index build scenarios was a respectable 30%.

This 30% was due to the CPU overhead involved in the insert and update activities.

If the Oracle level processing for insert and update activities could be optimized for SSD, significant performance gains might be realized during these activities."

NOTE : Also see my notes on tuning and improving Oracle export (expdp) performance speed.

SOURCE : http://dba-oracle.com/oracle_tips_load_speed.htm