Re: Use of TEMP tablespace other than sorting

DA Morgan wrote:
>
> Rick Denoire wrote:
>
> > Using an SQL query with a join over six tables, the query breaks with
> > the message "ORA-1652: unable to extend temp segment by 512
> > in tablespace TEMP". This query does not do any sort operation, at
> > least not explicitly.
> >
> > After having expanded TEMP four times consecutively up to 48 GB, I am
> > still getting the same error. So I wonder what TEMP is used for beside
> > sort operations. The tablespace is locally managed, uses uniform
> > extents of 4 MB each. The sort area size is set to this size as well.
> >
> > Is there a way to monitor the use of space in TEMP by a particular
> > session?
> >
> > Oracle 8.1.7 / Solaris 2.7
> >
> > Any hint would be appreciated.
> >
> > Rick Denoire
>
> I'd suggest a couple of things. The first is the you run explain plan to
> see what is happening. The second is that you post the query and give us
> some idea of how many rows are in the tables. At first blush it appears
> that something is wrong. But without more background I wouldn't want to
> speculate as to what.
>
> BTW: CBO or RBO and are statistics with DBMS_STATS current?
>
> Daniel Morgan

The following query will show you who is using temp and how much they are using.

--
SET heading ON
SET linesize 140
SET pagesize 40

COLUMN user       FORMAT a20               HEADING 'User'
COLUMN username   FORMAT a10               HEADING 'Username'
COLUMN osuser     FORMAT a12               HEADING 'OS User'
COLUMN sid_ser    FORMAT a10               HEADING 'SID,SER'
COLUMN blk_mb     FORMAT 99,999            HEADING 'Temp MB'
COLUMN sorts      FORMAT 9,999             HEADING 'Sorts'
COLUMN sql_text   FORMAT a40 word_wrapped  HEADING 'SQL Text'
COLUMN process    FORMAT a35 word_wrapped  HEADING 'Module:Action'
COLUMN tablespace NOPRINT                new_value ts_name

BREAK ON sid_ser SKIP 1 DUPLICATES
BREAK ON tablespace -
      ON REPORT
COMPUTE SUM OF blk_mb on tablespace report

TTITLE Center 'Current Sort Usage'   -
  skip Center '~~~~~~~~~~~~~~~~~~' -
  skip 1 Left   'Temp Tablespace: ' ts_name -
  skip 2 -

SELECT /*+ ORDERED */
       u.tablespace,
       s.sid || ',' || s.serial# as sid_ser,
       s.username,
       s.osuser,
       (SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb,
       x.sql_text,
       s.module || ':' || chr(10) ||'.  ' || s.action as process
  FROM v$sort_usage u
       ,v$session S
       ,v$sql X
       ,v$parameter a
 WHERE s.saddr                = u.session_addr
   AND s.sql_address          = x.address
   AND s.sql_hash_value       = x.hash_value
   AND a.name                 = 'db_block_size'
 GROUP
    BY u.tablespace
     , s.sid
     , s.serial#
     , s.username
     , s.osuser
     , a.value
     , x.sql_text
     , s.module
     , s.action
 ORDER
     BY u.tablespace
      , s.sid
/

TTITLE off
clear computes
clear breaks
-- 
Andrew Allen