Discussion:
direct path write temp
(too old to reply)
Hameed, Amir
2018-09-27 17:13:32 UTC
Permalink
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
2018-09-27 18:02:50 UTC
Permalink
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
2018-09-27 20:39:20 UTC
Permalink
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: (347) 321-1217
Arian Stijf
2018-09-27 20:49:51 UTC
Permalink
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
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: (347) 321-1217
--
http://www.freelists.org/webpage/oracle-l
Kellyn Pot'Vin-Gorman
2018-09-27 21:11:25 UTC
Permalink
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: (347) 321-1217
--
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
2018-09-27 21:18:41 UTC
Permalink
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: (347) 321-1217
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Mladen Gogala
2018-09-28 06:05:32 UTC
Permalink
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: (347) 321-1217
--
http://www.freelists.org/webpage/oracle-l
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l
"Reen, Elizabeth " (Redacted sender "elizabeth.reen" for DMARC)
2018-09-28 16:18:36 UTC
Permalink
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: (347) 321-1217
--
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: (347) 321-1217
--
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
2018-09-28 15:23:35 UTC
Permalink
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?
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

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