Home » Developer & Programmer » Data Integration » Materialized view - time out?
| Materialized view - time out? [message #93336] |
Sun, 25 July 2004 22:44  |
Venkat
Messages: 110
Registered: February 2001
|
Senior Member |
|
|
Hi,
Am using materialized view to pull data from remote site...
I would like to know if there are any options available to specify the time out for the refresh... Please guide me..
Regards
Venkat
|
|
|
|
| Re: Materialized view - time out? [message #93344 is a reply to message #93336] |
Fri, 06 August 2004 11:34  |
Venkata Phani Kumar.Velur
Messages: 6
Registered: August 2004
|
Junior Member |
|
|
Hi Venkat,
Just go through this may be it might help u...
When creating a materialized view, you have the option of specifying whether the refresh occurs ON DEMAND or ON COMMIT. To use the fast warehouse refresh facility, the ON DEMAND mode must be specified, then the materialized view can be refreshed by calling one of the procedures in DBMS_MVIEW.
The DBMS_MVIEW package provides three different types of refresh operations.
* DBMS_MVIEW.REFRESH
Refresh one or more materialized views.
* DBMS_MVIEW.REFRESH_ALL_MVIEWS
Refresh all materialized views.
* DBMS_MVIEW.REFRESH_DEPENDENT
Manual Refresh Using the DBMS_MVIEW Package
Three different refresh procedures are available in the DBMS_MVIEW package for performing ON DEMAND refresh and they each have their own unique set of parameters. To use this package, Oracle8 queues must be available, which means that the following parameters must be set in the initialization parameter file. If queues are unavailable, refresh will fail with an appropriate message.
Required Initialization Parameters for Warehouse Refresh
* JOB_QUEUE_PROCESSES
The number of background processes. Determines how many materialized views can be refreshed concurrently.
* JOB_QUEUE_INTERVAL
In seconds, the interval between which the job queue scheduler checks to see if a new job has been submitted to the job queue.
* UTL_FILE_DIR
Determines the directory where the refresh log is written. If unspecified, no refresh log will be created.
These packages also create a log which, by default, is called refresh.log and is useful in helping to diagnose problems during the refresh process. This log file can be renamed by calling the procedure DBMS_OLAP.SET_LOGFILE_NAME ('log filename').
Refresh Specific Materialized Views
The DBMS_MVIEW.REFRESH procedure is used to refresh one or more materialized views that are explicitly defined in the FROM list. This refresh procedure can also be used to refresh materialized views used by replication, so not all of the parameters are required. The required parameters to use this procedure are:
* The list of materialized views to refresh, delimited by a comma
* The refresh method: A-Always, F-Fast, ?-Force, C-Complete
* Rollback segment to use
* Continue after errors
When refreshing multiple materialized views, if one of them has an error while being refreshed, the entire job will continue if set to TRUE.
* The following four parameters should be set to FALSE, 0,0,0
These are the values required by warehouse refresh, since these parameters are used by the replication process.
* Atomic refresh
If set to TRUE, then warehouse refresh is not used. It uses the snapshot/replication refresh instead. If set to FALSE, the warehouse refresh method is used and each refresh operation is performed within its own transaction.
Therefore, to perform a fast refresh on the materialized view store_mv, the package would be called as follows:
DBMS_MVIEW.REFRESH('STORE_MV', 'A', '', TRUE, FALSE, 0,0,0, FALSE);
Multiple materialized views can be refreshed at the same time and they don't all have to use the same refresh method. To give them different refresh methods, specify multiple method codes in the same order as the list of materialized views (without commas). For example, the following specifies that store_mv will be completely refreshed and product_mv will receive a fast refresh.
DBMS_MVIEW.REFRESH('STORE_MV,PRODUCT_MV', 'AF', '', TRUE, FALSE, 0,0,0, FALSE);
Refresh All Materialized Views
An alternative to specifying the materialized views to refresh is to use the procedure DBMS_MVIEW.REFRESH_ALL_MVIEWS. This will result in all materialized views being refreshed. If any of the materialized views fails to refresh, then the number of failures is reported.
The parameters for this procedure are:
* The number of failures
* The datatype number
* The refresh method: A-Always, F-Fast, ?-Force, C-Complete
* Rollback segment to use
* Continue after errors
An example of refreshing all materialized views is:
DBMS_MVIEW.REFRESH_ALL_MVIEWS ( failures,'A','',FALSE,FALSE);
Refresh Dependent
The third option is the ability to refresh only those materialized views that depend on a specific table using the procedure DBMS_MVIEW. REFRESH_DEPENDENT. For example, suppose the changes have been received for the orders table but not customer payments. The refresh dependent procedure can be called to refresh only those materialized views that reference the ORDER table.
The parameters for this procedure are:
* The number of failures
* The dependent table
* The refresh method: A-Always, F-Fast, ?-Force, C-Complete
* Rollback segment to use
* Continue after errors
A Boolean parameter. If set to TRUE, the number_of_failures output parameter will be set to the number of refreshes that failed, and a generic error message will indicate that failures occurred. The refresh log will give details of each of the errors, as will the alert log for the instance. If set to FALSE, the default, then refresh, will stop after it encounters the first error, and any remaining materialized views in the list will not be refreshed.
* Atomic refresh
A Boolean parameter.
In order to perform a full refresh on all materialized views that reference the ORDERS table, use:
DBMS_mview.refresh_dependent (failures, 'ORDERS', 'A', '', FALSE, FALSE );
Tips for Refreshing Using Warehouse Refresh
If the process that is executing DBMS_MVIEW.REFRESH is interrupted or the instance is shut down, any refresh jobs that were executing in job queue processes will be requeued and will continue running. To remove these jobs, use the DBMS_JOB.REMOVE procedure.
Materialized Views with Joins and Aggregates
Here are some guidelines for using the refresh mechanism for materialized views with joins and aggregates.
1. The warehouse refresh facility only operates on materialized views containing aggregates.
2. Always load new data using the direct-path option if possible. Avoid deletes and updates because a complete refresh will be necessary. However, you can drop a partition on a materialized view and do a fast refresh.
3. Place fixed key constraints on the fact table, and primary key constraints from the fact table to the dimension table. Doing this enables refresh to identify the fact table, which helps fast refresh.
4. During loading, disable all constraints and re-enable when finished loading.
5. Index the materialized view on the foreign key columns using a concatenated index.
6. To speed up fast refresh, make the number of job queue processes greater than the number of processors.
7. If there are many materialized views to refresh, it is faster to refresh all in a single command than to call them individually.
8. Make use of the "?" refresh method to ensure getting a refreshed materialized view that can be used to query rewrite. If a fast refresh cannot be done, a complete refresh will be performed. Whereas, if a fast refresh had been requested and there was nothing to do, the materialized view would not be refreshed at all.
9. Try to create materialized views that are fast refreshable because it is quick.
10. If a summary contains data that is based on data which is no longer in the fact table, maintain the materialized view using fast refresh. If no job queues are started, two job queue processes will be started by the refresh. This can be modified by:
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = value
11. In general, the more processors there are, the more job queue processes should be created. Also, if you are doing mostly complete refreshes, reduce the number of job queue processes, since each refresh consumes more system resources than a fast refresh. The number of job queue processes limits the number of materialized views that can be refreshed concurrently. In contrast, if you perform mostly fast refreshes, increase the number of job queue processes.
Recommended Initialization Parameters for Parallelism
The following parameters
* PARALLEL_MAX_SERVERS should be set high enough to take care of parallelism.
* SORT_AREA_SIZE should be less than HASH_AREA_SIZE.
* OPTIMIZER_MODE should equal CHOOSE (cost based optimization).
* OPTIMIZER_PERCENT_PARALLEL should equal 100.
Monitoring a Refresh
While a job is running, a SELECT * FROM V$SESSION_LONGOPS statement will tell you the progress of each materialized view being refreshed.
To look at the progress of which jobs are on which queue, use a SELECT * FROM DBA_JOBS_RUNNING statement.
The table ALL_MVIEW_ANALYSIS contains the values, as a moving average, for the time most recently refreshed and the average time to refresh using both full and incremental methods.
Refresh will schedule the long running jobs first. Use the refresh log to check what each refresh did.
Tips after Refreshing Materialized Views
After you have performed a load or incremental load and rebuilt the detail table indexes, you need to re-enable integrity constraints (if any) and refresh the materialized views and materialized view indexes that are derived from that detail data. In a data warehouse environment, referential integrity constraints are normally enabled with the NOVALIDATE or RELY options. An important decision to make before performing a refresh operation is whether the refresh needs to be recoverable. Because materialized view data is redundant and can always be reconstructed from the detail tables, it may be preferable to disable logging on the materialized view. To disable logging and run incremental refresh non-recoverably, use the ALTER MATERIALIZED VIEW...NOLOGGING statement prior to REFRESH.
If the materialized view is being refreshed using the ON COMMIT method, then, following refresh operations, the alert log (alert_ <SID>.log) and the trace file (ora_<SID>_number.trc) should be consulted to check that no errors have occurred.
Well its broing to read , Yet it gives all the details. feel free to mail me if u need any further details.
Cheers
Ven (Venkat)
|
|
|
|
| Deadlock while creating intermedia indexes [message #93402 is a reply to message #93344] |
Tue, 23 November 2004 01:56 |
sankaram
Messages: 6
Registered: November 2004
|
Junior Member |
|
|
Hi venkata phini kumar,
I created the context INDEX on the snapshot tables as follows;
BEGIN
ctx_ddl.create_preference('MYSTORAGE','BASIC_STORAGE');
ctx_ddl.set_attribute('MYSTORAGE', 'I_TABLE_CLAUSE','tablespace INDEX_TBS INITRANS 3
MAXTRANS 255 STORAGE (INITIAL 512K NEXT 512K PCTINCREASE 0)');
END;
My TABLE have two COLUMNS
Description VARCHAR2(256)
Long_Description VARCHAR2(2000);
I am creating the INDEX ON the above TABLE:
CREATE INDEX index_name ON TABLE(Long_Description)
INDEXTYPE IS CTXSYS.CONTEXT
parameters('storage MYSTORAGE');
Some times not always, While creating the INDEX I found the following error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drekmap (lob write)
ORA-00060: deadlock detected while waiting for resource
I am using Complete Refresh, and job_queue_processes = 60.
Sort_Ara_Size is less than Hash_Area_Size.
Optimizer_Mode = Choose.
Processes = 150
Can u suggest what is possible reason.
Thanking you for ur early reply,
Sankaram
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 20:48:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
