Oracle performance problems caused by improper definition of materialized views

Recently, a large BI Project was implemented. The KPI indicator data was stored on a monthly basis, but the user requested to perform a daily calculation and replace the calculation results of the previous day until the end of the month. The Oracle database has been stable in the initial stage of the project, but the performance has suddenly declined significantly in the last two weeks.

After checking, the CPU and memory of the server are normal, but the disk IO is very high. The index of the optimized SQL-related table is invalid, and the shrinking data file is also invalid. At last, we found that there were many time-consuming Oracle (JXX) processes in the session. Execute:

Select * from dba_jobs

It is found that there are 10 tasks that refresh the materialized view through dbms_refresh and are executed every 5 minutes. After inquiry, no one manually defined these tasks. Therefore, to view the definitions of these views, the data is required to be refreshed every five minutes in the view definition SQL. After deleting these views, the system performance returns to normal.

Conclusion:

1. Data refresh in the materialized view definition is implemented through system scheduled tasks.

2. Refreshing materialized views too frequently may cause high disk IO, especially when the source table is large. In this example, the data volume of the source table is small before, but the data volume of the table has exceeded million after the KPI is calculated recently. Therefore, the database with stable performance has encountered performance problems in the early stage.

3. materialized view is applicable to scenarios where source tables are not updated frequently. In this example, you can extract the data of the historical year from the materialized view. For this year's data, you should not use the materialized view, just use the normal view.

When the debugging process finds that the rollback file exceeds 30 GB, you can easily clean it. The process is as follows:

1. Check the existing tablespace and find the tablespace to be rolled back.

SELECT * FROM DBA_TABLESPACES where contents = 'undo'

2. Create a New tablespace for rollback

Create undo tablespace UNDO_RBS1 datafile 'e: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ undotbs02.dbf' size 1000 m;

3. modify the system rollback tablespace to the tablespace created in 2.

Alter system set undo_tablespace = undo_rbs1;

4. Delete the old tablespace from the system

Drop tablespace undo_tbs1 including contents;

5. delete the files in the old tablespace. If deletion is not allowed, restart the Oracle service and then delete it.