Your Onsite DBA has called you up and told you that one of the tables PS_ABC_TAO has grown very big in size. Based on the standard naming convention, you have determined that the record is a temporary record since it ends with _TAO You look into the database and decide that that the data is not required any more. You ask the DBA to delete the data in the table.Next day you get a call from an irritated user who says that a daily process that took only 1 minute to run is taking about 2 hours today. You look into his process and find that it uses PS_ABC_TAO as a temporary table.What would you suggest the DBA to do ?

wyangw

May 6th, 2005

How can the update statistics method solve the size problem? If it is used as temp table the data should be refreshed each time the job runs. This question doesnot make sense.

AL

Jan 23rd, 2006

THere are 2 problems described here. 1) Table growth of the temp table. THat is temporarily solved by deleting the rows of the temp table. However the jobs that use this table should be looked into to determine why they do not delete what should be temporay data. **table data should also be backed up before deleting as a precaution.2) the second problem is the impact to performance likely caused from deleting data from temp. Updating the statistics MAY help this problem as long as you are not updating stats on an empty table, those stats would be meaningless. If you are not able to simulate meaningful data for compiling stats it is likely better to delete the statistics.

Soujanya

Feb 25th, 2006

Problem here is that the table has to be truncated not deleted. This makes sure that Oracle High water mark gets reset. Otherwise, system thinks that the temp table is of the same huge size as before and tries to do table scans for that entire length. So the answer is, call up the DBA and have the table truncated.

amgill
Profile Answers by amgill

Sep 29th, 2009

One way to handle such temp tables is to delete stats and then lock stats so that the scheduled update stats job does not update stats on them. One example I have is the PS HRMS (Oracle DB), "Pay confirm" process. The following statements fixed the perfromance issue.

EXEC DBMS_STATS.DELETE_TABLE_STATS('SYSADM',PS_WRK_SEQ_CHECK');
EXEC DBMS_STATS.LOCK_TABLE_STATS('SYSADM',PS_WRK_SEQ_CHECK');

This will force the oracle to take at look at teh table data before coming up with a plan.

MBKUL

Aug 10th, 2012

Before asking the DBA to truncate the table, we can check the number of rows in that table, followed by table truncation, and for stats we can populate fake stats using the row count number before table truncation.