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 ?
RE: Your Onsite DBA has called you up and told you that one of the tables PS_ABC_TAO has grown very big ...
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.
RE: Your Onsite DBA has called you up and told you tha...
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.
RE: Your Onsite DBA has called you up and told you tha...
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.
RE: 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 d
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.
