Re: Insert into partitioned tables and specifying the partition name
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: amonte <ax.mount@xxxxxxxxx>
- Date: Fri, 9 Apr 2010 20:03:03 -0700
The coordinator should not be doing writes, assuming you are using
PDML? Are you?
alter session enable parallel dml;
If you are seeing waits on block 2 then the issue is space allocation
- your initial/next extents are too small (maybe the default of 64k
for ASSM tablespaces?). If you have multiple parallel loads going on
at the same time then the extent allocation rate could be quite high.
I'd recommend using 8MB for initial/next in these cases (assuming the
partitions end up with more than 8MB). This should drastically reduce
the number of extents that get allocated and thus reduce the locking
on block 2. Are all these partitioned tables in a single bigfile
tablespace?
On Fri, Apr 9, 2010 at 5:23 PM, amonte <ax.mount@xxxxxxxxx> wrote:
> Hello
>
> It is 10.2.0.4 RAC so no SQL Monitor :-(
>
> I just noticed that when slaves are waiting on "PX Deq Credit: send blkd" it
> is because the coordinator is blocked by some events such as
>
> db file parallel write
> controlfile parallel write
> library cache lock
>
> Not sure why when the first two events kicks in the whole system freezes
> until dbwr or ckpt finishes their writing. I say freeze because I run
> against v$sess_io every second and when these two events appears no increase
> on consistent gets or physical reads in v$sess_io
>
> I did some tests just now with the 25 million rows table. If I create the
> table in a tablespace where other fact tables resides and run the load in
> node 1, if there are some fact tables (residing in same tablespace) being
> loaded too in other node my load is slow, constantly I see gc waits such as
> gc current block requests on the datafile id and block 2 of file id (isnt
> that the bitmap block?). In this case the load into partitioned table
> without partition clause can take as long as 40 minutes (also because of the
> waits mentioned previously).
>
> If I create a new tablespace for my table only the load is pretty fast, it
> finishes in around 8 minutes constantly
>
> TIA
>
>
>
> 2010/4/10 Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
>>
>> First off, what is the performance data telling you when you compare
>> the 40 minute to the 15 minute runs? SQL Monitor would be my first
>> recommendation (if on 11g) else ASH.
>> What version are you on?
>>
>> Inserting into an unpartitioned table may be slightly faster than a
>> partitioned table, but it should not be that significant. It also
>> depends on a few things but ultimately it comes down to the parallel
>> execution plan you are getting. This can obviously change with
>> depending on if the source table is partitioned or not and if the
>> partitioning schemes match (equipartitioned).
>>
>> PX Deq Credit: send blkd means that the "writer/consumer" PX servers
>> are not writing fast enough and the "reader/producer" PX servers are
>> having to wait to sent them rows. That's not enough to say
>> partitioning is the problem.
>>
>> You can use partition extended syntax to insert into a specific
>> partition but in your case you would have to have 3 sessions (or
>> iterations) because there can only be one target partition (can not
>> use partition extended syntax for 3 parts at the same time).
>>
>>
>> On Fri, Apr 9, 2010 at 4:03 PM, amonte <ax.mount@xxxxxxxxx> wrote:
>> > I wonder if is a general practice in a DWH when loading data into
>> > partitioned tables using INSERT.. SELECT the partition name should be
>> > used
>> > in the INSERT?
>> >
>> > We have seen performance inestabilities when we do INSERT .. SELECT
>> > without
>> > specifying the partition name. It is normal because there is an extra
>> > step
>> > to calculate which partition to go but sometimes it is just too slow.
>> > For
>> > example when inserting 25 million rows to a heap table we get constant 4
>> > minutes. When inserting same number of rows into a 500 partitions table
>> > (the
>> > data only goes to3 partitions) it sometimes runs in 15 minutes and
>> > sometimes
>> > 40. In the case of 40 minutes we observe the parallel slaves's physical
>> > reads increase much slower (event PX Deq Credit: send blkd).
>> >
>> > When specifying the partition name we get stability as well, not as fast
>> > as
>> > compared to heap table but at least the time is predictable. So I wonder
>> > if
>> > others specifies the partition name when doing mass loading?
>>
>> --
>> Regards,
>> Greg Rahn
>> http://structureddata.org
>
>
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
