oracle query - ORA-01652: unable to extend temp segment but only in some versions of sql*plus

Question

Hi all,

This one has me rather confused. I've written a query which runs fine from my development client but fails on the production client with error "ORA-01652: unable to extend temp segment by....". In both instances, the database and user is the same. On my development machine (MS Windows) I've got SQL*PLUS (Release 9.0.1.4.0) and Toad 9.0 (both using version 9.0.4.0.1 of the oci.dll). Both run the code without errors.

However when I run the same file, against the same database, using the same username/password from a different machine, this time version 10.2.0.4.0 (from the 10.2.0.4-1 Oracle instant client) I get the error.

It does occur reproducibly.

Unfortunately I've only got limited access to the dictionary views on the database which is set up as read-only (can't even get an explain plan!).

I've tried working around the problem by tuning the query (I suspect that there is a large interim result set which is subsequently trimmed down) but have not managed to change the behaviour at either client.

It may be possible to deploy a different version of the client on the machine causing the problems - but currently that looks like downgrading to a previous version.

Any ideas?

TIA

Update

Based on Gary's answer below, I had a look at the glogin.sql scripts - the only difference was that 'SET SQLPLUSCOMPATIBILITY 8.1.7' was present on the working client but absent on failing client - but adding it in did not resolve the problem.

I also tried

alter session set workarea_size_policy=manual;
alter session set hash_area_size=1048576000;

and

alter session set sort_area_size=1048576000;

to no avail :(

Answer 1

Years ago I worked on a DR database that was fully READONLY, and even the TEMP tablespace wasn't writable. Any query that tried to spill to temp would fail (even if the temp space to be used was pretty trivial).

If this is the same situation, I wouldn't be surprised if there was a login.sql (or glogin.sql or a logon trigger) that does an ALTER SESSION to set larger PGA memory value for the session, and/or changes the optimizer goal to FIRST_ROWS.

If you can, compare the results of the following from both clients:

select * from v$parameter where ismodified != 'FALSE';

Also from each client for the problem SQL, try EXPLAIN PLAN FOR SELECT... and SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

See if it is coming up with different query plans.

Answer 2

Not really an answer - but a bit more information....

Our local DBAs were able to confirm that the 16Gb (!) TEMP tablespace was indeed being used and had filled up, but only from the Linux clients (I was able to recreate the error making an oci8 call from PHP). In the case of the sqlplus client I was actually using exactly the same file to run the query on both clients (copied via scp without text conversion - so line endings were CRLF - i.e. byte for byte the same as was running on the Windows client).

So the only rational solution was that the 2 client stacks were resulting in different execution plans!

Running the query from both clients approx simultaeneously on a DBMS with very little load gave the same result - meaning that the two clients also generated different sqlids for the query.

(and also Oracle was ignoring my hints - I hate when it does that).

There is no way Oracle should be doing this - even if it were doing some internal munging of the query before presenting it to the DBMS (which would give rise to the different sqlids) the client stack used should be totally transparent regarding the choice of an execution plan - this should only ever change based on the content of the query and the state of the DBMS.

The problem was complicated by not being to see any explain plans - but for the query to use up so much temporary tablespace, it had to be doing a very ugly join (at least partially cartesian) before filtering the resultset. Adding hints to override this had no effect. So I resolved the problem by splitting the query into 2 cursors and doing a nested lookup using PL/SQL. A very ugly solution, but it solved my immediate problem. Fortunately I just need to generate a text file.

For the benefit of anyone finding themselves in a similar pickle:

BEGIN

DECLARE
CURSOR query_outer IS
    SELECT some_primary_key, some_other_stuff
    FROM atable
    WHERE....

CURSOR query_details (p_some_pk) IS
    SELECT COUNT(*), SUM(avalue)
    FROM btable
    WHERE fk=p_some_pk
    AND....

FOR m IN query_outer
LOOP
    FOR n IN query_details(m.some_primary_key)
    LOOP
        dbms_out.put_line(....);
    END LOOP;
END LOOP;

END;

The more I use Oracle, the more I hate it!