I gave it 36 hours, it never showed so here it is again. My apologies if
it's a duplicate.
Can someone give me an explanation for this?
I'm trying to tune a sql statement. optimizer_mode is set to choose in the
init.ora. The select pulls from 6 tables. As is it does full table scans
on all 6 tables. There are appropriate indexes that can be used. Row
counts vary from 40 rows to approx. 3000. This is my first 8 database. My
7.3.4 databases always seemed to work as advertised with CBO, but this one
isn't doing what I expect so I'm trying various things. (many just to see
what they do and I know there is a lot more to look at.)
One thing I'm trying is 'alter session set optimizer_mode = first_rows'.
The first time I ran 'select * from table1,table2,.... where ....;' after
altering the session the sql statement ran fine and I got my explain plan
(set autotrace on). The next time, though I got 'ORA-01652: unable to
extend temp segment by 256 in tablespace TEMP'. I know my temp tablespace
is small, 10m, so I up it to 50m. Same error. Alter the table, pctincrease
= 0, same error. Up temp to 100m. Same error. Now I'm baffled. I'm the
only user (this is my play database, no one else even knows it exists!), and
it ran fine before the alter session. So for giggles I set 'alter session
set optimizer_mode = all_rows'. Works fine, no errors. Change it back to
first_rows, ORA-1652. If I add first_rows as a hint I get the same error.
There is no order by in the statement. Is there something in first_rows
that forces a sort? If so, *why*????
The db is 8.1.6.0.0 running on Solaris 2.7.
I've got other things I want to try. Right now I'm leaning toward a rule
hint for this statement. But I thought I'd ask the list gurus for insight.
TIA!
Linda
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seley, Linda
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
