direct path write temp

[Hameed, Amir]

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

[Dominic Brooks]

Minimising writes to temp is the best optimisation!

Do that by getting accurate estimates in your execution plans wherever possible and reducing poorly sized work areas -
Trying to avoid multi pass executions - optimise where possible to be one pass and similarly one pass to be in memory.

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/V-SQL_WORKAREA.html#GUID-72AE8C0F-DC23-45BD-9589-25EBF490BF0E

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/V-SQL_WORKAREA_ACTIVE.html#GUID-AA69DDA7-9A2A-4FF8-918A-387FCF684ABA

Cheers
Dominic
Sent from my iPhone

On 27 Sep 2018, at 18:47, Hameed, Amir <***@xerox.com<mailto:***@xerox.com>> 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]

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

Post by Hameed, Amir
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

[Arian Stijf]

This post might be inappropriate. Click to display it.

[Kellyn Pot'Vin-Gorman]

Actually - there are underscore hints that were used to change the amount
of temp you could allocate to hash and sorts and setting the work area
policy to manusl, but they have the very adverse effect post 10g: PGA stops
being allocated at all.
https://dbakevlar.com/2011/08/a-tale-of-session-parameter-settings/
This was only at the session level, but with enhancements to PGA, don't
even consider these old settings.

Kellyn

Post by Arian Stijf
Is that a tongue in your cheek?
As far as I know there's indeed no parameter to control writes to temp
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

Post by Mladen Gogala
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

Post by Hameed, Amir
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: [phone]

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

[image: Kellyn Pot'Vin on about.me]

*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
President Denver SQL Server User Group <http://denversql.org/>
about.me/dbakevlar

[Arian Stijf]

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

How do I optimize my writes to GTT or a table with NOLOGGING for large
inserts?
-----Original Message-----
Behalf Of Arian Stijf
Sent: Thursday, September 27, 2018 4:50 PM
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
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

Post by Mladen Gogala
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

Post by Hameed, Amir
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: [phone]

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

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

[Mladen Gogala]

Well, for one, have several temporary tablespaces for these operations
use the least used one, located on the fast storage. Second, do not use
nologging in production. Ever.

How do I optimize my writes to GTT or a table with NOLOGGING for large inserts?
-----Original Message-----
Sent: Thursday, September 27, 2018 4:50 PM
Subject: Re: direct path write temp
Is that a tongue in your cheek?
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

Post by Mladen Gogala
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

Post by Hameed, Amir
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: [phone]

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

["Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)]

I guess you don't use dataguard. Some of us don't get a choice there.

Elizabeth Reen
CPB Database Group Manager


-----Original Message-----
From: [External] oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of [External] Mladen Gogala
Sent: Friday, September 28, 2018 2:06 AM
To: Hameed, Amir; ***@stijf.com; oracle-***@freelists.org
Cc: oracle-l-***@freelists.org
Subject: Re: direct path write temp

Well, for one, have several temporary tablespaces for these operations
use the least used one, located on the fast storage. Second, do not use
nologging in production. Ever.

How do I optimize my writes to GTT or a table with NOLOGGING for large inserts?
-----Original Message-----
Sent: Thursday, September 27, 2018 4:50 PM
Subject: Re: direct path write temp
Is that a tongue in your cheek?
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

Post by Mladen Gogala
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

Post by Hameed, Amir
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: [phone]

--
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwICaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=8CHtisWJn2FeCm_J7zD_iJtAqJF7A6SCtgpLV9ayQV0&s=CHK21UHpfFsI-R0iezzXii-w9hRprjlv-UR2yahwf2w&e=

--
Mladen Gogala
Database Consultant
Tel: [phone]

[Mladen Gogala]

Hi!

When I do tongue in cheek replies, you will know it as there is rarely
any doubt. No, in-memory option is a real option to speed up queries
because it eliminates lots of hashing.

Regards

Post by Arian Stijf
Is that a tongue in your cheek?