RE: direct path write temp

From: Arian Stijf <arian_at_stijf.com>
Date: Thu, 27 Sep 2018 23:18:41 +0200
Message-ID: <5f543c0cc00a11af152be4d30448c8dd_at_stijf.com>


NOLOGGING won't change the explain plan and thus the need to use TEMP space.

GTT's are no different from regular inserts, other than that the GTT is located in the TEMP tablespace.

So I'm still in doubt about the root cause of your issue. For GTT's it's hard to avoid a temporary direct write, since the GTT is located in TEMP tablespace. So a temporary direct write is one of the most efficient ways to insert data.
For a large insert using TEMP tablespace, the first thing that comes to mind is to skip the order by clause.

But again: What statement do you want to optimize, and what is the explain plan of the statement?

Regards,

Arian

Hameed, Amir schreef op 27.09.2018 23:11:
> How do I optimize my writes to GTT or a table with NOLOGGING for large
> inserts?
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce@freelists.org> On
> Behalf Of Arian Stijf
> Sent: Thursday, September 27, 2018 4:50 PM
> To: oracle-l_at_freelists.org; gogala.mladen_at_gmail.com
> Cc: oracle-l-bounce_at_freelists.org
> Subject: Re: direct path write temp
>
> Is that a tongue in your cheek?
>
> As far as I know there's indeed no parameter to control writes to temp
> space. So:
> Avoiding the writes in the first place is the best option.
> Second best is making sure enough memory is available to accommodate
> for the operation, so no temp space is needed.
> Third and most likely option is a combination of the above.
>
> Regards,
>
> Arian
>
> Mladen Gogala schreef op 27.09.2018 22:39:

>> No parameter controls temporary direct writes. If the hash or sort
>> doesn't fit in available PGA memory, the surplus  will be written to
>> the temporary tablespace. And that's about it. So, don't use sort or
>> hash joins. Another method of avoiding writing to the temp is using
>> in-memory option, but that will cost you a pretty penny.
>> 
>> Regards
>> 
>> On 09/27/2018 01:13 PM, Hameed, Amir wrote:
>> 
>>> Hi,
>>> 
>>> I am trying to find out what database parameter controls the size of
>>> DIRECT PATH WRITE TEMP and is there way to optimize it. You feedback
>>> will be appreciated.
>>> 
>>> Thank you,
>>> 
>>> Amir
>> 
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217

>
> --
> http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 27 2018 - 23:18:41 CEST

Original text of this message