sqlldr and index growth problem [message #444581] |
Mon, 22 February 2010 13:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajmiller3rd
Messages: 2 Registered: April 2008
|
Junior Member |
|
|
Hi All,
Background:
I have several tables in a schema that are partitioned by month. These tables have data loaded nightly by various sqlldr crontab jobs - using direct path.
On these tables are a total of 5 local indexes - also partitioned by month. The monthly index partitions all reside in their own monthly tablespace, ie SUSEX_IDX_200906_C and SUSEX_IDX_200907_C and so on and so on.
Individually, these monthly index partitions add up to about 25gb each month.
The table spaces are pre-created with 4 data files with auto extend ON with a maxsize of 8gb.
Problem:
Starting in Jan 2010, we received a noticed that space was nearly exhausted in the SUSEX_IDX_201001_C table space. We added an additional data file with auto extend ON with maxsize of 8gb. Then it happened again. We added another data file with the same parameters.
Being the primary DBA for this database, something did not seem right and I started to investigate. I found that all six data files were only showing half used ( 4gb) but had auto extended to 8gb.
I checked the high water mark for each data file and it was right there at the max file size. I checked the sizes of the index partitions and they totaled up to only 28g. Also there are extents at the very end of each data file so I'm unable to shrink the files to recover the unused space.
So I have a 48gb table space with only 28 actually used. And this months table space SUSEX_IDX_201002_C is acting the same way.
I know that basically sqlldr with direct path set to true disables indexes, loads the data and then enables the indexes.
Question:
Has anyone heard of a sqlldr load using direct=true causing unusual index growth?
There are no other inserts, updates, deletes happening to these tables. Just the sqlldr jobs at night.
Thanks to all in advance,
Al
|
|
|
Re: sqlldr and index growth problem [message #444582 is a reply to message #444581] |
Mon, 22 February 2010 13:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Database Utilities
Chapter 11 Conventional and Direct Path Loads
Section Direct Path Load
Quote:Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.
The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).
Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.
Paragraph "Direct Path Load of a Partitioned or Subpartitioned Table"
Quote:When loading a partitioned or subpartitioned table, SQL*Loader partitions the rows and maintains indexes (which can also be partitioned). Note that a direct path load of a partitioned or subpartitioned table can be quite resource-intensive for tables with many partitions or subpartitions.
Section Using Direct Path Load
Paragraph Building Indexes
Quote:You can improve performance of direct path loads by using temporary storage. After each block is formatted, the new index keys are put in a sort (temporary) segment. The old index and the new keys are merged at load finish time to create the new index. The old index, sort (temporary) segment, and new index segment all require storage until the merge is complete. Then the old index and temporary segment are removed.
Quote:When multiple indexes are built, the temporary segments corresponding to each index exist simultaneously, in addition to the old indexes. The new keys are then merged with the old indexes, one index at a time. As each new index is created, the old index and the corresponding temporary segment are removed.
Note that temporary here does not mean temporary tablespace but temporary segment inside the index tablespace.
So yes, what you see is the expected behaviour.
Regards
Michel
[Updated on: Mon, 22 February 2010 13:24] Report message to a moderator
|
|
|