What is the difference between bytes_cached and bytes_used in v$temp_extent_pool?
Why 'ORA-1652: unable to extend temp segment by xxxx in tablespace TEMP' comes in alert log but free space is there in temp files?
How segment is managed in temporary tablespace, ORACLE?
-------------------------------------------------------------------------------------------------------------------------------------
Normally sorting will happen in PGA. If PGA is not large enough to accommodate the entire sorting, sorting will be done as chunks and sorted data will be stored in the temporary tablespace temporary. Later the results will be merged together and will produce the desired output.
The temporary space will not be reserved for any users on sessions (in RAC) at the beginning. Space will be allocated as requirement comes for the temporary space.
You can monitor the space utilization of the temporary tablespace from DBA_TEMP_FREE_SPACE or V$TEMP_EXTENT_POOL. If you want to monitor per user you can use v$tempseg_usage view for it.
When an activity related to the temporary tablespce is completed (for example, Sorting has been completed and the result is given to the user process), The space allocated for the activity will be marked free but will not be completely released from the segment unless some other users or instance ask for the space ( it is called steeling extents in some cases). This makes a soft reservation on that space by a user or a session. The bytes reserved like this is called cached bytes.
You can monitor this cached bytes from V$TEMP_EXTENT_POOL. Cached bytes and used bytes will be equal at the beginning of a session but latter there may be huge difference in between. See the difference in one of the production databases where I am working.
SQL> select TABLESPACE_NAME,sum(BYTES_CACHED)/(1024*1024) MB_CACHED,sum (BYTES_USED)/(1024*1024) MB_USED from v$temp_extent_pool group by TABLESPACE_NAME;
TABLESPACE_NAME MB_CACHED MB_USED
---------------- ----------- ----------
TEMP1 6218 9.375
TEMP 0 0
TEMP2 8190 102
Sometimes it may take time to release the cached bytes by a user or a session, then you may get ORA-1652 even though you have enough free space in the temporary tablespace.
Thanks,
alimajaf
Why 'ORA-1652: unable to extend temp segment by xxxx in tablespace TEMP' comes in alert log but free space is there in temp files?
How segment is managed in temporary tablespace, ORACLE?
-------------------------------------------------------------------------------------------------------------------------------------
Normally sorting will happen in PGA. If PGA is not large enough to accommodate the entire sorting, sorting will be done as chunks and sorted data will be stored in the temporary tablespace temporary. Later the results will be merged together and will produce the desired output.
The temporary space will not be reserved for any users on sessions (in RAC) at the beginning. Space will be allocated as requirement comes for the temporary space.
You can monitor the space utilization of the temporary tablespace from DBA_TEMP_FREE_SPACE or V$TEMP_EXTENT_POOL. If you want to monitor per user you can use v$tempseg_usage view for it.
When an activity related to the temporary tablespce is completed (for example, Sorting has been completed and the result is given to the user process), The space allocated for the activity will be marked free but will not be completely released from the segment unless some other users or instance ask for the space ( it is called steeling extents in some cases). This makes a soft reservation on that space by a user or a session. The bytes reserved like this is called cached bytes.
You can monitor this cached bytes from V$TEMP_EXTENT_POOL. Cached bytes and used bytes will be equal at the beginning of a session but latter there may be huge difference in between. See the difference in one of the production databases where I am working.
SQL> select TABLESPACE_NAME,sum(BYTES_CACHED)/(1024*1024) MB_CACHED,sum (BYTES_USED)/(1024*1024) MB_USED from v$temp_extent_pool group by TABLESPACE_NAME;
TABLESPACE_NAME MB_CACHED MB_USED
---------------- ----------- ----------
TEMP1 6218 9.375
TEMP 0 0
TEMP2 8190 102
Sometimes it may take time to release the cached bytes by a user or a session, then you may get ORA-1652 even though you have enough free space in the temporary tablespace.
Thanks,
alimajaf
No comments:
Post a Comment