Oracle tablespace growth exception solution: Another example
Description: The temporary tablespace of a database in the customer environment increases abnormally (with a random date). It may be caused by the scheduled jobs of the following two Oracle databases,
Time: around, January 14
AM
The known growth time of the temporary tablespace is consistent with the error in the trace file. Please analyze the cause and solutions.
If you need to log on to the server, please contact Lu yaoqi by phone and he will call to inform you of the relevant information.
The attachment is related logs and a custom tracking data file size log, with an interval of 1 hour.
Investigation: We found that the running of an SQL statement in the customer's environment caused an abnormal growth of the temporary tablespace.
This SQL statement looks like querying the DB time and DB CPU in the last week.
Check with the developer to determine whether the statement can be optimized.
Follow-up investigation:
Check that the status of CATPROC in dba_registry is invalid. At the same time, dbms_stats that collects statistics is also invalid in dba_objects.
Run @? /Rdbms/admin/catproc. SQL compile this component (execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC ;);
Then re-compile the dbms_stats package.
Recompile the three base tables of the preceding three WRH $ statements.
Cause: the problem has been identified. The SQL statement is executed by the SYS user during scheduled task execution at night.
The reason for occupying a large temporary tablespace is that the statement involves three tables:
WRH $ _ STAT_NAME
WRM $ _ SNAPSHOT
WRH $ _ SYS_TIME_MODEL
Inaccurate statistics lead to a poor execution plan.
After statistics are collected manually, the statement can be completed quickly and the occupied temporary tablespace is small.
The CATPROC components (Oracle Database Packages and Types) of these two databases are in the INVALID status, causing the dbms_stats stored procedure to be unavailable and thus the statistics cannot be collected automatically. Currently, dbms_stats is available.
Solution to abnormal growth of Oracle tablespace
Oracle Tutorial: instance fault recovery
Installing Oracle 12C in Linux-6-64
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian