| How to estimate/configure the size of a Temporary Tablespace? [message #60233] |
Mon, 26 January 2004 22:31  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
I have several tips about it, but most of them must be executed or monitored when a peak/heavy transaction is occurring. How can I know that at a time T, a peak workload is occuring? I can't stay and wait see/monitor the unix and the oracle processes all the time (how about it happens at night time..)? How to catch and monitor the peak work load time and corresponding SQL batch/transaction? Is there not a way to log all this in one day and check easily when does the peak work load occurs and which kinf of SQL batch or transaction...??
Here are the tips I have gathered, but I can't wait until a peak work is occuring:
.Check V$SORT_SEGMENT/V$SORT_USAGE views in very peek times of your environment:
.V$SORT_SEGMENT will show the size of total temp segment allocated in the temp TBS.
.V$SORT_USAGE will show the total amount of temp space used by each active transaction.
select username, tablespace, contents, blocks
from v$sort_usage;
Through this valuable information u can decide how much temp space get used in very peek hours in ur env. and according
to that statistics u can set ur temp TSs accordingly.
Moreover by the use of V$SORT_USAGE view u can even know which transaction is using much temp space and u can take
appropriate steps against it.
Many thanks for your answers!
Regards,
Patrick Tahiri.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
