Last week I was in a meeting to determine how to solve a problem in one of the foundations of a client. The problem was related to two materialized view refresh (I'll call the MV1 and MV2 for privacy) and what happened was that in the last days had not been able to refresh the view because the process was canceled for lack of space UNDO. The views are refreshed in every 1 hour COMPLETE way through a job on base and kept a detailed diary. In general never exceed 100,000 records, but now had more than 100 million since it was detected that a filter error in the view WHERE MV1 (VM2 uses a sentence that refers to MV1) detail was taken over 2 years instead of the day.
team raised recreate database views, which is a valid solution and I agreed in the first instance, but has some disadvantages: 1) you have to run a drop and immediately create each view which can invalidations cause cascade and therefore must be done in a maintenance window and 2) until it is complete recreation of both views will be invalid dependent objects, and is somewhat difficult to estimate with certainty how long it will delay this process, thus risk out of the window. As suggested workaround make a drink in the following way (it is important to note that this does not require any mv Drop):
sqlplus> exec dbms_refresh (list => 'MV1', atomic_refresh => FALSE)
sqlplus> exec dbms_refresh (list => ' MV2 'atomic_refresh => FALSE) A 10g partitr atomic_refresh default parameter is TRUE and know that means I will explain briefly how the process of soda from trying:
Each time you refresh a view mode FORCE running two steps:
1) The purge or delete all existing rows materialized view
2) new rows are inserted by running the query defined in the MV. atomic_refresh parameter defines the method used to perform step 1. In 10g step 1 implies a DELETE of all rows, it is said that the process of soda 10g is atomic because the delete and insert are made in one transaction (atomic). Before 10g the default value of the parameter was FALSE implying that step 1 was done with a TRUNCATE, which is obviously faster than DELETE since it is not transactional. Just as it is not transactional UNDO does not consume space, recall that the DELETE is a DML operation that consumes far more than undo, since it must keep all the columns in each row in case you need a retreat.
As I said above, in the particular case of soda from the two MV's, both, for a errror filtering in the MV1, left with millions of rows instead of with a few tens of thousands as it should, since the base is 10g is taking the default parameter = TRUE atomic_refesh dictates perform a delete, in this case will be a delete of about 100M rows in both cases and therefore always canceled UNDO space, since it is not prepared to support and configures such a mass deletion. The suggestion to change the default parameter atomic_refresh = FALSE perform a TRUNCATE and then the cooling insert views in fast without having to recreate them.
is common once explained el nuevo funcionamiento en 10g, que alguien se pregunte porque no se sigue truncando en lugar de hacer delete. La explicación es que en el caso que al realizarse el truncate y luego fallar el insert, la MV quedará vacia lo cual podría afectar el negocio ya que quedaran vacias hasta que el refresco se pueda completar con exito. En otro caso que tiene sentido el delete es cuando no pueden quedar nunca vacias las MV's porque se consultan mucho y si se hace truncate no se retornaran filas hasta que finalice el refresco. Generalmente los errores de refresco se produce cuando los datos se obtienen accediendo las tablas fuente por un dblink desde otra base. En el caso de la base en cuestión, este problema no existe ya que las MV's se refrescan con datos de tablas que are in the same scheme.
As last clarification, it is important to note that there is no risk in carrying out the soda suggested and may at any time of day without affecting overall performance. Once this cooled you can activate the jobs that shoot soft drinks normally. I will now show an example for comparing times, generating a table T and a materialized view MV_T
rop @ DESA10G> alter table t add primary key (x);
Table amended. rop @ DESA10G> create Materialized view complete refresh mv_t
2 3 4 as select * from t;
materialized view created. rop @ DESA10G> set timing on
rop @ DESA10G> exec dbms_mview.refresh (list => 'MV_T' atomic_refresh => TRUE)
PL / SQL procedure successfully completed. After
: 00:02:39.75
rop @ DESA10G> exec dbms_mview.refresh (list => 'MV_T' atomic_refresh => FALSE)
PL / SQL procedure successfully completed. After
: 00:00:15.54
rop @ DESA10G> In sum, it is important to analyze the business requirements, whether these requirements support the short unavailability that not cause the refresh atomically (truncate) in addition to the possibility that the MV are empty, the result of an error or cancellation, until the next refresh, then it is possible to cool faster and with little consumption of Setting Up the parameter UNDO atomic_refresh FALSE.
