RE: sqlloader direct path causes library cache lock?

(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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 25 2013 - 15:45:46 CEST