Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
-- Query disk temporary tablespace usage when executing a query
iSQL> set vertical on;
iSQL> set colsize 100
iSQL> SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') TIME,
       'TEMP_T_STATS',
       CREATE_TIME, DROP_TIME,
       SS.ID SESSION_ID,
       TRANSACTION_ID TX_ID,
       SS.AUTOCOMMIT_FLAG ,
       STMT.EXECUTE_FLAG ,
       SQL_TEXT,
       STATE,
       ESTIMATED_OPTIMAL_SORT_SIZE,
       ESTIMATED_OPTIMAL_HASH_SIZE,
       ALLOC_WAIT_COUNT,
       WORK_AREA_SIZE, 
       NORMAL_AREA_SIZE
  FROM X$TEMPTABLE_STATS TEMP,
       V$STATEMENT STMT,
       V$SESSION SS
 WHERE TRANSACTION_ID = STMT.TX_ID
   AND SS.ID <> SESSION_ID()
   AND STMT.SESSION_ID = SS.ID
 ORDER BY SESSION_ID;
TIME                        : 15:39:03                   
'TEMP_T_STATS'              : TEMP_T_STATS  
CREATE_TIME                 : 20161129_153221                   
DROP_TIME                   : 20161129_153311                   
SESSION_ID                  : 1           
TX_ID                       : 51842       
AUTOCOMMIT_FLAG             : 1           
EXECUTE_FLAG                : 0           
SQL_TEXT                    : select count(*) from (select count(*) from t, t2, t3 group by t.c2, t2.c2, t3.c2)     
STATE                       : SORT_INSERTNSORT                  
ESTIMATED_OPTIMAL_SORT_SIZE : 562205696            
ESTIMATED_OPTIMAL_HASH_SIZE : 0                    
ALLOC_WAIT_COUNT            : 0                    
WORK_AREA_SIZE              : 1048576              
NORMAL_AREA_SIZE            : 267911168              -- Disk temporary tablespace usage. Similar to the size of TEMP_MAX_PAGE_COUNT converted to bytes.

Solution

...

Change the value of the TEMP_MAX_PAGE_COUNT property according to the maximum value of the disk temporary tablespace.