Home » RDBMS Server » Server Administration » why temporary tablespace have uniform extents (oracle 10g)
why temporary tablespace have uniform extents [message #576641] Wed, 06 February 2013 21:20 Go to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
When we are using pga_aggregate_target and sort area is automatically managed by Oracle.
Why oracle is not allowing for auto allocating extents in TEMP and still extents of uniform size of 1 MB is used.
Re: why temporary tablespace have uniform extents [message #576643 is a reply to message #576641] Wed, 06 February 2013 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
what does PGA_AGGREGATE_TARGET have to do with TEMP tablespace?

why do you think that having uniform size in TEMP is a problem?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: why temporary tablespace have uniform extents [message #576652 is a reply to message #576641] Thu, 07 February 2013 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why oracle is not allowing for auto allocating extents in TEMP and still extents of uniform size of 1 MB is used.


Because it is designed as it.

Regards
Michel
Re: why temporary tablespace have uniform extents [message #576661 is a reply to message #576641] Thu, 07 February 2013 01:33 Go to previous messageGo to next message
John Watson
Messages: 4685
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Why oracle is not allowing for auto allocating extents in TEMP and still extents of uniform size of 1 MB is used.
The 1M uniform size is only a default, you can specify any size you want. One might want significantly larger to take advantage of the larger direct multiblock reads that we can have nowadays: a multiblock read cannot go over an extent boundary. And of course if using ASM, the extent size should be a multiple of the allocation unit size, which many sites set to 4M.
Re: why temporary tablespace have uniform extents [message #576664 is a reply to message #576661] Thu, 07 February 2013 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway, it is uniform and cannot be another method.

Regards
Michel
Re: why temporary tablespace have uniform extents [message #576682 is a reply to message #576664] Thu, 07 February 2013 04:31 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
i understand that you can alter the extent size . Is there any reason why uniform extents in TEMP tablespace and not auto allocating as in other tablespace.
It can save the space as well
Re: why temporary tablespace have uniform extents [message #576684 is a reply to message #576682] Thu, 07 February 2013 04:50 Go to previous messageGo to next message
John Watson
Messages: 4685
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Is there any reason why uniform extents in TEMP tablespace and not auto allocating as in other tablespace.
I've already told you. Think about multi block reads.
Re: why temporary tablespace have uniform extents [message #576687 is a reply to message #576684] Thu, 07 February 2013 05:36 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
thanks a lot guys for your quick responses:)
Re: why temporary tablespace have uniform extents [message #576690 is a reply to message #576687] Thu, 07 February 2013 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you didn't understand the answer.

Regards
Michel
Re: why temporary tablespace have uniform extents [message #577704 is a reply to message #576690] Tue, 19 February 2013 14:35 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
If you set the pga_aggregate_target big enough, few sorts will be done on disk. Run the following scripts to see home disk sorts since startup. If many disk sorts are performed, you should increase pga_aggregate_target and if that is not enough then increase the uniform extent size.
ECSCDAP1P > select host_name,instance_name,round((sysdate-i.startup_time)*100)/100 up_days,
  2  A.Value Disk_Sorts,
  3         A.Value/to_number(sysdate-i.startup_time)/24 disk_sorts_per_hour,
  4         B.Value Memory_Sorts,
  5         ROUND(100*A.Value/
  6         DECODE((A.Value+B.Value),0,1,(A.Value+B.Value)),2)
  7             Pct_Disk_Sorts
  8    from V$SYSSTAT A, V$SYSSTAT B, v$instance i
  9   where A.Name = 'sorts (disk)'
 10     and B.Name = 'sorts (memory)';

HOST_NAME            INSTANCE_NAME       UP_DAYS DISK_SORTS DISK_SORTS_PER_HOUR MEMORY_SORTS
-------------------- ---------------- ---------- ---------- ------------------- ------------
csprdcdadb11         CSCDAP1               78.54          0                   0    113398750
Previous Topic: space manage
Next Topic: Manullay Oracle Database Creation
Goto Forum:
  


Current Time: Thu Oct 23 07:34:16 CDT 2014

Total time taken to generate the page: 0.13664 seconds