RE: sqlloader direct path causes library cache lock?
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <exriscer@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Jun 2013 08:45:46 -0500
We ran into something similar in the past:
http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#i1009887
(see the last paragraph if that addresses your problem)
Additional Considerations for Direct-Path INSERT
The following are some additional considerations when using direct-path INSERT.
Index Maintenance with Direct-Path INSERT
Oracle Database performs index maintenance at the end of direct-path INSERT
operations on tables (partitioned or non-partitioned) that have indexes. This
index maintenance is performed by the parallel execution servers for parallel
direct-path INSERT or by the single process for serial direct-path INSERT. You
can avoid the performance impact of index maintenance by dropping the index
before the INSERT operation and then rebuilding it afterward.
Space Considerations with Direct-Path INSERT
Direct-path INSERT requires more space than conventional-path INSERT.
All serial direct-path INSERT operations, as well as parallel direct-path
INSERT into partitioned tables, insert data above the high-water mark of the
affected segment. This requires some additional space.
Parallel direct-path INSERT into non-partitioned tables requires even more
space, because it creates a temporary segment for each degree of parallelism.
If the non-partitioned table is not in a locally managed tablespace in
automatic segment-space management mode, you can modify the values of the NEXT
and PCTINCREASE storage parameter and MINIMUM EXTENT tablespace parameter to
provide sufficient (but not excess) storage for the temporary segments. Choose
values for these parameters so that:
The size of each extent is not too small (no less than 1 MB). This setting
affects the total number of extents in the object.
The size of each extent is not so large that the parallel INSERT results in
wasted space on segments that are larger than necessary.
After the direct-path INSERT operation is complete, you can reset these
parameters to settings more appropriate for serial operations.
Locking Considerations with Direct-Path INSERT
During direct-path INSERT, the database obtains exclusive locks on the table
(or on all partitions of a partitioned table). As a result, users cannot
perform any concurrent insert, update, or delete operations on the table, and
concurrent index creation and build operations are not permitted. Concurrent
queries, however, are supported, but the query will return only the information
before the insert operation.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ls Cheng
Sent: Tuesday, June 25, 2013 2:59 AM
To: Oracle Mailinglist
Subject: sqlloader direct path causes library cache lock?
Hi
We have a process which load data by invoking SQL Loader and uses direct path
loads. The process loads many files to a couple of partitioned tables which
are being loaded by conventional insert as well (basically batch load and user
load runs concurrently).
I have observed that many user session suffers library cache lock when
inserting or querying the tables being touched by the SQL Loader, these tables
have no constraints at all (not even not null), no triggers but do have
indexes. Since it has no constraints I dont see why other session needs to wait
for library cache lock when SQL Loader kicks in?
I dont observe any enqueue (such as TM). This is 10.2.0.4 Single Instance.
Thanks
--
LSC
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
