Re: Partitioned table space stats ... Incremental question and TEMP tablespace usage...

From: Kellyn Pot'vin <kellyn.potvin_at_ymail.com>
Date: Thu, 22 Mar 2012 09:18:55 -0700 (PDT)
Message-ID: <1332433135.74782.YahooMailNeo_at_web121002.mail.ne1.yahoo.com>

Outside of Greg's very good question, I also have one-  Are there any indexes on this partitioned table?  I just took a quick scan through your email, but if you have chosen "TRUE" on CASCADE and you have global indexes on the partitioned table, that could easily explain the temp issue. I don't see anything specifying if there are any indexes or not, but thought I'd ask... :)

 
Kellyn Pot'Vin
Senior Technical Consultant
Enkitec
DBAKevlar.com

---

 From: Greg Rahn <greg_at_structureddata.org> To: dmann99_at_gmail.com
Cc: oracle-l_at_freelists.org
Sent: Thursday, March 22, 2012 9:34 AM
Subject: Re: Partitioned table space stats ... Incremental question and TEMP tablespace usage...  

What sql statement (from dbms_stats) is the one spilling to temp and failing?
Gathering table stats should not spill to temp with AUTO_SAMPLE_SIZE.  This is because there is no sort in 11g when using AUTO_SAMPLE_SIZE due to the fast NDV algorithm.  If you switch to a percent, it will require a sort and may spill to temp as well.

FYI - only the first (incremental) set_table_prefs is required -- the other two are the defaults already.

On Thu, Mar 22, 2012 at 8:12 AM, David Mann <dmann99_at_gmail.com> wrote:

> This is a generic 11.1.0.7 (straight install with no patches) on Solaris
> 64-bit
>
> Table MYSCHEMA.MYTABLE has 42 partitions, 15 gb across all partitions,
> largest partition is 1.75gb, but most are in the 300mb range.
>
> I have 64gb temp space defined for this database, and no one else is
> logged on.
>
> I have the table stats preferences configured like this:
>
> EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE', 'INCREMENTAL',
> 'TRUE');
> EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE', 'GRANULARITY',
> 'AUTO');
> EXEC DBMS_STATS.set_table_prefs('MYSCHEMA', 'MYTABLE',
> 'ESTIMATE_PERCENT', DBMS_STATS.AUTO_SAMPLE_SIZE);
>
> And then execute the following:
> EXEC DBMS_STATS.gather_table_stats('MYSCHEMA', 'MYTABLE',DEGREE=>4);
>
> The stats gathering chugs along and after about 45 minutes it fails with:
> ERROR at line 1:
> ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
> ORA-06512: at "SYS.DBMS_STATS", line 18408
> ORA-06512: at "SYS.DBMS_STATS", line 18429
> ORA-06512: at line 1
>
>

-- 
Regards,
Greg Rahn  |  blog <http://bit.ly/u9N0i8>  |  twitter <http://bit.ly/v733dJ>  |
linkedin <http://linkd.in/gregrahn>


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 22 2012 - 11:18:55 CDT