Select statement causing ORA-1652

[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