Re: optimizer_mode = first_rows and ora-1652

Not sure about the 1652 error, but have you looked into setting the optimizer_index_cost_adj parameter? Try playing around with this. Default is 100. I had good luck at one client by using 4. YMMV of course.

>>> LSeley_at_IQNavigator.com 02/09/01 12:01PM >>>
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: LSeley_at_IQNavigator.com 

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: ListGuru_at_fatcity.com (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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: sawmillert_at_state.mi.us

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: ListGuru_at_fatcity.com (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).