FAQ
1) I have been steeped in a bunch of Incremental Stats blog posts for
the past week and I think I am just confusing myself at this point.

If I want to use Incremental stats on my Partitioned tables, I am
under the impression I need to use AUTO_SAMPLE_SIZE. Is this accurate?

Is it not possible to use a custom Sample Size in conjunction with
Incremental Stats? Or is it possible but my synopses will be less
accurate?

2) Reason I am asking is that I have a table that I can't get stats
on... keeps erroring out so I am trying to figure out how I can get a
successful stats run on it. I would like to try reducing the sample
size by changing from Auto to a small percentage of the table size.

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

I am still a newbie on stats for partitioned tables... Seems odds that
a 15gb table requires so much (64gb) of temp while gathering. Not sure
if I am hitting a bug. Metalink searches sent me on some wild goose
chases. Any suggestions before I request a swath of disk space for
more TEMP files appreciated as I have to get this worked out before I
move on to the larger tables in this schema (50gb+).

-Dave

--
Dave Mann
www.brainio.us
www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Greg Rahn at Mar 22, 2012 at 3:47 pm
    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 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
  • Kellyn Pot'vin at Mar 22, 2012 at 4:23 pm
    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@structureddata.org>
    To: dmann99@gmail.com
    Cc: oracle-l@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 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
  • David Mann at Mar 29, 2012 at 6:16 pm
    To follow up, the stats gather worked when I ran it again... I watched
    TEMP like a hawk and only saw a few blocks used here and there. Will
    have to dig deeper if it behaves funky again.

    This partitioned table is 15gb and only one index on it ... of the 29
    columns in the table the index references 13 of them. This is my first
    look at this app so I suspect a report writer probably had a query
    with 13 columns in the WHERE clause :)

    I will check into Index Monitoring and see how much it is actually used.

    -Dave

    On Thu, Mar 22, 2012 at 12:18 PM, Kellyn Pot'vin
    wrote:
    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
    --
    Dave Mann
    www.brainio.us
    www.ba6.us - Database Stuff - http://www.ba6.us/rss.xml
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Mar 22, 2012 at 3:56 pm
    I don't have a direct comment on note 2, but yes incremental stats requires
    AUTO_SAMPLE_SIZE.
    On Thu, Mar 22, 2012 at 3:12 PM, David Mann wrote:

    If I want to use Incremental stats on my Partitioned tables, I am
    under the impression I need to use AUTO_SAMPLE_SIZE. Is this accurate?
    >

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 22, '12 at 3:29p
activeMar 29, '12 at 6:16p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase