Temp Full Updating Statistics

This past night one scheduler chain failed with the error below;

 

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
ORA-12012: error on auto execute of job 1718726
ORA-06512: at SYS.DBMS_STATS line 13591
ORA-06512: at SYS.DBMS_STATS line 13947
ORA-06512: at SYS.DBMS_STATS line 14081
ORA-06512: at SYS.DBMS_STATS line 14045
ORA-06512: at line 2

 

This job update statistics, apparently has filled the temporal. The DBMS_STATS might do intensive use of temp. In this case have been created a new table, which has about 100 gigabytes of physical size, even if this table is analyzed using a low sampling, like 5%, can make a considerable expenditure of temp. The solution is to add more space to temporary tablespace, but in this case the table is a historical and not haven’t indexes with which to analyze it will not gain anything and always be accessed using FTS (maybe set a degree of 8 or 10 to the table can help), which is chosen lock statistics for this table.

 

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s