Select statement causing ORA-1652

[kenny chia]

Hi
When I ran a select statement that returns alot of data (300825 rows),
There is no sorting in the select statement (eg order by)
the following oracle error is logged into the alert file

ORA-1652: unable to extend temp segment by 256000 in tablespace TAB1

Why is there a temp segment in the tablespace TAB1? Will a select statement create temp segments in a tablespace?
The tablespace TAB1 is used to store tables only
Oracle 8 is used

[Hein van den Heuvel]

You don't give us much to go on.

Why not include the select statement in question. I suspect that the oracle optimizer decided it needed a 'merge join'.
I'm sure that is described in many places, including the Oracle doc.

Google pointed me to http://www.adp-gmbh.ch/ora/explainplan.html
Which reads "MERGE JOIN
Usually, you want to select data across multiple table which is referred to as to join the tables. MERGE JOIN is just one possible method of joining the data (the others are HASH JOIN, NESTED LOOPS and CLUSTER JOIN). A Sort Merge Join basically sorts all relevant rows in the first table by the join key (ba), and also sorts the relevant rows in the second table by the join key (aa), and then merges these sorted rows."

You can verify whether this is happening through EXPLAIN PLAN.

An other possible reason is that one of the (secondary) tables used in the select is not a base table but a VIEW.

Hope this helps,
Hein.

[Thierry Poels_1]

Hi,

when appropriate indexes are not available temporary segments will also be used.

regards,
Thierry.

[Yogeeraj_1]

hi,

two things may be occuring.

1. The tablespace TAB1 has very little space left.
2. The default temporary tablespace of this user is TAB1.

Please post the output of this SQL command:
===========================================
select temporary_tablespace
from dba_users
where username=''
===========================================

You may also have to review your query by analysing your execution plan and add an index on that table accordingly.

You might also be using PERMANENT tablespace for temporary use. how was your temporary tablespace created?

I usually create all my temporary tablespaces as follows (example):
============================================
create temporary tablespace test_temp
tempfile '/tmp/test_temp.dbf' size 512k reuse
extent management local
uniform size 64k
/
============================================

best regards
Yogeeraj

[Printaporn_1]

Hi,

check again to make sure that default temp tablespace for user not TAB1.

[Nicolas Dumeige]

Hello,

You refer to the alertSID.log, but don't you have the same error in SQL*Plus ?

To get the explain plan for the SELECT statement :

SET ECHO OFF
SET LINESIZE 120
COLUMN PDEB FORMAT A4
COLUMN PFIN FORMAT A4
COLUMN QUERY_PLAN FORMAT A60
COLUMN PLAN FORMAT A80
SET ECHO ON

explain plan
for
---------------------
YOUR SELECT STATEMENT
---------------------
SET ECHO OFF
SELECT
id,
position,
lpad(' ',2*(level-1))|| operation || ' ' || options || ' ' || object_name || ' ' || object_type || ' ' ||
object_instance || ' ' || decode(id,0, 'Cost = ' || position) Query_Plan,
level,
PARTITION_START PDEB,
PARTITION_STOP PFIN
FROM
plan_table
START WITH
id = 0
CONNECT BY PRIOR
id = parent_id
;
-- Or the Oracle script
@?/rdbms/admin/utlxpls
rollback;
SET ECHO ON

Nicolas

[Brian Crabtree]

1. Check your user temporary tablespace setting. It is possible that it is set to this tablespace.

2. Check your object's PARALLEL settings. It could be using a parallel query to pull data from the table, which means that 4 queries are pulling data, and the information is stored in a temporary segment.

Even though you don't have an order by, any join is still going to need temporary space to hold data from the join so that it can select the rows needed.

Thanks,

Brian

[kenny chia]

Hi all
This is the result of the SQL Explain plan statement

0 SELECT STATEMENT Cost = 1
1 1 SORT UNIQUE 2
2 1 NESTED LOOPS 3
3 1 NESTED LOOPS 4
4 1 NESTED LOOPS 5
5 1 TABLE ACCESS BY INDEX ROWID LOT_DATA 2 6
6 1 INDEX RANGE SCAN LOT_DATA_PRD_INDEX NON-UNIQUE 7
7 2 TABLE ACCESS BY INDEX ROWID WIP_DATA 1 6
8 1 INDEX RANGE SCAN WIP_DATA_WIP_LOT NON-UNIQUE 7
9 2 TABLE ACCESS BY INDEX ROWID EQ_DATA 3 5
10 1 INDEX UNIQUE SCAN EQ_DATA UNIQUE 6
11 2 INDEX RANGE SCAN WTEST_DATA UNIQUE 4

[Hein van den Heuvel]

So there is a sort right there in the explain plan to accomplish a unique (or group by?) clause in the select.
Now we know why the temp segment may needed as well as to hold the collecttion.

Now check the temp dataspace for the user as requested. Just do a select * from dba_users where username = 'xxx';

Btw... 256000 is not too much. If it is just sort, can you not avoid the disk sort by increasing sort_area_size and sort_area_retained_size? (sqlplus> show parameter sort )

Hein.

[Yogeeraj_1]

hi,

important that you post the output of the following:
===========================================
select temporary_tablespace
from dba_users
where username=''
===========================================

Also, post a more complete trace statistics of the SQL command:
============================================
e.g.
SQL> set autotrace traceonly
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
299 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
============================================

These statistics will shed some more light on the problem that you are encountering.

regards
Yogeeraj

[kenny chia]

Hi all
This is the SQL

select distinct l.box_no, l.box_prd, l.box_mfno, l.box_cno, l.wtest_date, l.pts_date, w.wip_date, w.wip_route || '/' || w.wip_oper step_name, w.wip_opcode, eq.eq_name ||'/'||eq.eq_code eqp, wt.wtest_waf disk from wip_data w, box_data l, eq_data eq, wtest_data wt where l.box_prd in ('255') AND l.wtest_date <= to_date '4/6/04 4:16:04
PM', 'MM/DD/RR HH:MI:SS AM') AND l.wtest_date >= to_date('3/24/04 4:16:04 PM', 'MM/DD/RR HH:MI:SS AM') AND w.wip_box = l.box_no AND w.wip_eqcode = eq.eq_code AND l.box_no = wt.wtest_box

when I remove the distinct keyword, the SQL returned > 1 million lines.
If I put the distinct keyword, Oracle will have to sort > 1 million lines
I have already set the sort area to 750MB. The server has 1.5G RAM

The tablespace TAB1 still has 900MB free space

[Nicolas Dumeige]

Kenny,

To get the actual size of the sort operation, you can use this rq :

select
SID ,
OPERATION_TYPE ,
POLICY ,
SUM(ROUND(WORK_AREA_SIZE/1024/1024)) WA_SIZE_MO,
SUM(ROUND(ACTUAL_MEM_USED /1024/1024)) ACTUAL_MEM_USED_MO ,
SUM(ROUND(MAX_MEM_USED /1024/1024)) MAX_MEM_USED_MO ,
SUM(NUMBER_PASSES) PASSES,
SUM(ROUND(TEMPSEG_SIZE /1024/1024)) DISQUE_MO
FROM
v$sql_workarea_active
GROUP BY
SID ,
OPERATION_TYPE,
POLICY
ORDER BY
OPERATION_TYPE,
SID
/


BUT YOU DID NOT ANSWER THE FISRT QUESTION :
Have you check your user temporary tablespace setting ?
It not normal that for a select statement you get ORA-1652 on the user TS.

Cheers

Nicolas

[Hein van den Heuvel]

Ah, now we are talking. Real data to work with! You still need to check out the temporary_tablespace assignment for the user/session though!

> when I remove the distinct keyword, the SQL returned > 1 million lines.
> If I put the distinct keyword, Oracle will have to sort > 1 million lines
> I have already set the sort area to 750MB. The server has 1.5G RAM

Sounds like indeed you have to be serious about the sort setup.

And sounds like you basically did all you could for trying to stick to a memory sort.
Btw... I believe that once you are forced to go to disk you might as well trim back the sort_size dramatically to a couple of MB or whatever the rest of the application tends to be happy with.

Do you suppose you could come up with a nested query where the distinct becomes a much earlier filter, reducing the number of rows to work on earlier? You would have to poke at rowcounts to see if this makes sense.

Almost there....

Good luck,
Hein.

[kenny chia]

Thanks for all the feedback!

Anyway, the query

select temporary_tablespace
from dba_users
where username=

TEMPORARY_TABLESPACE
------------------------------
TAB1_TEMP

That is the temporary tablespace for the instance

>Do you suppose you could come up with a >nested query where the distinct becomes a >much earlier filter, reducing the number of >rows to work on earlier? You would have to >poke at rowcounts to see if this makes sense.

->nested query
You mean a select distinct statement inside a select statement? The query will then have small and multiple sorts, compared to now which is one big sort. I will look into this..

[Nicolas Dumeige]

In some case, a hint to force the Oracle optimizer to do an hash join instead of a nested loop will provide better performance.

ex :
SELECT /*+ ordered use_hash(TSLDMRST) parallel(TSLDMRST,4) */
...fields...
FROM TSLDMRST;


Beware that hints can provide immediate imporvement but DO REMEMBER that it is also dangerous to have many because when the time goes by, the condition may be very different and the improvement hints become a stupid directive. Bypass Oracle optimizer is not alway a good idea.

Another option is to look why Oracle choose this explanation plan and what you can do to correct it if don't feel that's the best way to procede.

- create index
- build and maintain statistics and historgram
- use materialized view if applicable
- ...

Check DBMS_STATS standard package :
PROCEDURE GATHER_TABLE_STATS
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT `FOR ALL COLUMNS SIZE 1',
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT `DEFAULT',
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);

PROCEDURE GATHER_INDEX_STATS(
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);


Cheers

Nicolas

[Volker Borowski]

Hi,

sort_area_size is 750 MB ????

I doubt that. Check V$PARAMETER, if it really has taken this value after the instance has started!
This is a PGA parameter in Oracle 8 !
Each server process needs to allocate it's own (private) sort area. So if you have i.e. 10 server-processes, you will have 7,5GB of sort-memory....

65K to 256K will be a reasonable sort_area_size.
If you really have 750MB to sort, go for an Index to support the sort and "hint" the statement.

Hope this helps
Volker