Performance Tuning :: (Fast Refresh) MV Too Slow?
Sep 23, 2010
We have a MV which fetches data from around 27 tables containing 26 joins out of which 25 are outer joins. Some tables in the query are being referred multiple times through different alias names and hence the actual no of physical tables used is 18. This MV takes about 50 mins to refresh through complete refresh mechanism. We decided to make it fast refresh and thus made these configurations:
- Created MV logs based on rowid for each of the base tables.
- Recreated MV using FAST refresh,with primary key option enabled
- Pulled rowid for all these tables in the select column statement.
Even after making all the recommendations suggested by Oracle for fast refresh MV's we are still getting refresh time of around 65 mins(refresh time increased!!!).We already have indexes built on all the join columns of the base tables. What else do we need to do to make this a "fast" refresh MV ?
View 2 Replies
ADVERTISEMENT
May 22, 2012
My ERP Application is responding fast while running reports or saving entries, if Oracle 10g Express Edition (XE) is installed. But in Oracle 10g Enterprise Edition or Standard Editions the same application is running very slow.
View 1 Replies
View Related
Jan 16, 2012
I have a base table with ~20 mio. records with two FAST REFRESH Materialized Views based on that table using various aggregate functions in their view definition.
The problem is, when e.g. one record changes in the base table, I see two records in the MV log table MLOG$, but invoking the fast refresh mechanism by using using:
dbms_snapshot.refresh('mv1', 'f');
dbms_snapshot.refresh('mv2', 'f');
Is still running after ~20 minutes now.
View 2 Replies
View Related
Nov 1, 2012
The customer moved their database from 10g to a 11gR2 database.They created the MV in the new system and now it takes 26 hours to refresh as opposed to 15 min in the old 10g database.Just looking for a game plan for troubleshooting.
View 8 Replies
View Related
Oct 10, 2013
I've 2 tables in schema A.
1) TXN_HD_FCT (This table has 6 billion records. Has fields TXN_DT, CUST_ID, TXN_AMT, TXN_ID and the table is partitioned on TXN_DT. We load data approximately 600K+ records to this table every day
2) RELINK_CUST (This table has 2 fieds CUST_ID and NEW_CUST_ID). We load data approximatley 100-200K+ records every day into this table. This table has total 10MM+ records now.
Now i need to create a table (TXN_UPD_HDR_FCT) in Schema B by joining above 2 tables using below query every day
CREATE TABLE TXN_UPD_HDR_FCT
select a.TXN_DT,
nvl(b.new_cust_id, a.cust_id) as cust_id,
a.txn_amt,
a.txn_id,
from schema_A.TXN_HD_FCT a,
schema_A.RELINK_CUST b
where a.cust_id = b.cust_id(+);
We update CUST_ID to NEW_CUST_ID based on some rules (and keep it in the table RELINK_CUST) and need to take this new_cust_id while building the table TXN_UPD_HDR_FCT.
Building this table is taking approximately 4+ hours every day. just refresh only records from TXN_HDR_FCT whose CUST_ID is changed or any records inserted into TXN_HDR_FCT? I read about Materialized views but not sure how can i create MV logs on 2 tables? If i use materialized views do i need to create indexes every day?
View 15 Replies
View Related
Nov 12, 2010
I create a view on production server which takes almost 10 to 12 minutes when it shows data. this view contains 3 or 4 tables on which all primary and unique columns have indexes.which index will be better for fast retrieval of data .
View 5 Replies
View Related
Oct 15, 2013
I am inserting data using a procedure for 2012 and 2013 year which is using partitioned tables includes crore of data in a partition taking lot of time or taking months. Is there any other way by which I can insert data fast from our query.
View 14 Replies
View Related
Aug 14, 2010
I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
how to again make the MVs fast refreshable?
View 4 Replies
View Related
Apr 26, 2013
I am running one simple delete statement in one table with rownum<10000 but it is taking nearly 10 to 15 mins.Table doesn't have any child table rows and triggers.
View 13 Replies
View Related
Jan 12, 2011
I'm extracting/retrieving the data from the oracle database using Java application it's bit slow. However, when I retrieve from the SQL server it's faster than oracle.
View 6 Replies
View Related
Sep 4, 2011
If a table(have a primary key) is empty(after truncate),the sql of dml(insert,update) is very quickly,but if the table have many rows about 10,000,000 rows, the dml is very slowly,why?
View 6 Replies
View Related
Mar 23, 2012
we are using oracle 9i on AIX Server. When Customer were accessing the database, accidentally power was shut down. we restarted the Server,and Oracle database. all resumed successfully.
However while doing "Payments by the customer" it takes a lot of time to insert even a single payment record on database.The database is Live and our customer are very much frustrated,
View 1 Replies
View Related
Nov 14, 2012
The product I work on requires a query to tell us what tables are dependent on certain types.
SELECT dba_tab_cols.owner,
dba_tab_cols.table_name,
dba_tab_cols.data_type_owner,
dba_tab_cols.data_type
FROM dba_tab_cols
JOIN dba_types
ON dba_types.owner = dba_tab_cols.data_type_owner
AND dba_types.type_name = dba_tab_cols.data_type
WHERE (dba_types.owner IN ('SCHEMA1', 'SCHEMA2'......))
I find this query to be pretty slow. I think it is because data_type_owner in dba_tab_cols is not indexed. Adding an index is not an option because users expect our product to read-only.
View 1 Replies
View Related
Jul 4, 2011
Few days ago, My database server no access to StorageBox then I reboot it then after works fine. But, know DB import process is too slow. Before 100GB DB import process completed within 10 hours when server normal running. Now 2 day working, but not complete
How to investigate this issue? Maybe I miss increase some parameters on the Server or Oracle?
Here is my server brief info:
RAM is 16GB,
SWAP size is 16GB,
CPU 12 cores
SQL> show sga;
Total System Global Area 4294967296 bytes
Fixed Size 1984144 bytes
Variable Size 369105264 bytes
Database Buffers 3909091328 bytes
Redo Buffers 14786560 bytes
View 11 Replies
View Related
Jun 18, 2012
I have an Oracle database (9.2.0.7) installed on a HP-UX server.When trying to access this database from another HP-UX or Linux server, connection is fine. But when trying to connect from a Windows based client, connection is very slow (almost 1 minute to return the result of a 'select count(*)' like query, which is immediate from the Linux client).
Here are some facts I can add :
- Clients and servers are on the same network segment (it is not a network matter)
- No matter which client version I use, there no difference
- I tried to know what happens on the Oracle server when performing my sample query using tusc command : the result is that the server is performing exactly the same actions when sending my query from a Linux client or a Windows client
- The only relevant difference seems to be the client OS
View 13 Replies
View Related
Sep 14, 2012
I have a query which takes 5 minutes when run through the java app which uses hibernate. I've cut and pasted the SQL directly from hiberate trace file and run it in sqlplus/sqldeveloper and it runs instantly (0.01 seconds)(uses the index all ok and explain plan looks good - see below.) I don't know how to get the explain plan when it's running through the app or why it should be any different anyway as the query is identical.
My query is as follows:
SELECT /*+ INDEX (SPD SPD_SEQ_CODE) */ SPD.*
FROM SEQ_ADDR_DATA SPD, SEQ_ADDR_LEVELS SPL
WHERE SPD.SPVR_ID = '10'
AND SPL.SPLE_ID = SPD.SPLE_ID
AND SPL.SPLE_LEVEL <= '2' AND SPDA_ID NOT IN
[code]....
View 7 Replies
View Related
Jun 12, 2013
I have created a materialized view and also a normal View, which has 3 tables used in both the views, when inserted new records it reflects in a normal view but when i select the materialized view i cant see the updated data.
here is the materialized view i created;
CREATE MATERIALIZED VIEW pct_sales_materialized
BUILD IMMEDIATE REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT A.DEP_NAME,B.EMP_ID,C.EMP_NAME
FROM department_head A,department_child B,emp_detail C
WHERE A.DEP_ID = B.DEP_ID
AND B.EMP_ID = C.EMP_ID
View 2 Replies
View Related
Mar 20, 2012
I need to create a Materialized View, which should refresh every Tuesday and Friday at 5am.
CREATE MATERIALIZED VIEW DUAL_MV
TABLESPACE USERS_SMALL
REFRESH NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 05/24
AS SELECT * FROM DUAL;
I can do it for single day like above, but how to refresh on multiple days, using create materialized view syntax .
View 2 Replies
View Related
Jun 28, 2009
I created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option. These tables are the main tables of the DB and contains alot of data(about 15 millions of records). two master tables have index but mview doesn't have any indexes. I've thought the entire concept of the fast refresh is that it should be a relatively quick operation but the result showed that DB performance reduced, for example I could insert 900 records per minute thru application into master tables before but now I can just insert about 400 records per hour!.Also, It slows down the select operation, went from 0.11 seconds(with joining two tables) to 33 seconds (in mview)!.
First of all, I considered following possibilities and test them:
1) The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.
2) I checked the query of the materialized view and confirmed that it was a simple select from the master tables without any aggregation or sorting.
3) DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems.
4) A query of dba_registered_snapshots showed that no other snapshots appeared to be using these logs. So, After each refresh, the records in mview logs were deleting.
As you see, it seems everything is ok, but what was causing this fast refresh to go so slow?
I use oracle 10.2 on Suse Linux enterprise server.
View 3 Replies
View Related
Jun 20, 2011
i have created a fast refresh materialized view but it is not performing fast refresh or even complete refresh.Then i have checked my materialized view capabilities then in mv_capabilities_table i got a message that materialized view log is newer than old .
View 2 Replies
View Related
Feb 8, 2012
I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?
--build up needed schema objects
CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,
[Code]....
View 9 Replies
View Related
Jun 19, 2008
CREATE Materialized View log on t1 with rowid;
CREATE Materialized View log on t2 with rowid;
CREATE Materialized View log on t3 with rowid;
CREATE Materialized View MV_test
BUILD IMMEDIATE
FAST REFRESH ON COMMIT
[code].......
I have created a Materialized View by joining the above 3 tables. The Materialized View logs with ROWID has been created on each of the 3 base tables. One of the base tables has around 160 million rows and i expect the Materialized View to have the same number of rows.
The Materialized View has been built for FAST REFRESH on COMMIT. The SELECT list of the CREATE Materialized View statement includes all the ROWIDS from each base tables for doing the fast refresh.
The DBMS_MVIEW explain view shows that the view is capable of doing fast refresh on any DML. The Materialized View took 3 hrs to get built. I have also created indexes on all the three rowid columns (t1rowid,t2rowid,t3rowid) on the Materialized View.
Now, When i do any update on one of the base table and do a commit,
UPDATE t1 set value=500 where tx_id='1234';
The commit is taking around around 15 minutes. I have generated the trace and had a look at it, and it shows that, to refresh the Materialized View, ORACLE is first deleting that record from the Materialized View and then inserting the new record from the base table to the Materialized View.
This delete done by oracle is being done on the basis of the rowid of the updated record stored on the Materialized View log,
However its not using the index created on the t1rowid of the Materialized View and doing a full scan of the MV and hence the delete is taking a lot of time. The delete statement is using a hint NO_MERGE and HASH_SJ which is causing it to do the full table scan of the MV. why is ORACLE not using the index on the t1rowid column of the MV to do this delete?
Also to do the insert in the MV, its selecting the new changed value from the base tables t1,t2 and t2. Here again its doing a full table scan of the base table t1, rather then using the rowid directly or the index coulmn.which is taking a lot of time.
View 4 Replies
View Related
Dec 8, 2011
I have the source tables having 10 columns. I want to track the delta changes of only 6 columns. In case of insertion/updation, that new record should be inserted into another DELTA table of different schema.While in case of deletion, A new row will be inserted into that DELTA table , with the value of the primary key and other columns populated with NULL values.
I have applied the concept of 'after trigger' specifying the columns names but it logic is not working properly. Its tracking the changes for other 4 columns also.
View 4 Replies
View Related
Jun 17, 2010
We are planning to create a materialized view on 5 tables and 2 views. After first complete refresh we want to do Fast refresh going forward and planning to create view log for base tables. Since the SQL uses two views, we can create view-log for its underlying tables. But will that work ? In case of fast refresh ? or it will still go for complete refresh ?
View 9 Replies
View Related
Mar 6, 2013
I have a view that includes sub queries in its select.
I am creating a MV over that view. and i want to fast refresh it when ever a commit is placed on the table (that is used in the from clause for the simple view)
View 9 Replies
View Related
Sep 26, 2013
create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd , xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details) columns s1 VARCHAR2 (255) path 'string[1]' ,s2 VARCHAR2(255) path 'string[2]' ,b VARCHAR2(255) path 'boolean' ,d VARCHAR2(255) path 'date' ) i WHERE pd.mandator_key = 'VSO01' AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and
I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
I have found this URL....
View 2 Replies
View Related
Sep 4, 2008
We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.
ERROR at line 1:
ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after
cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
let me know when will this error occur during Fast Refresh.
View 4 Replies
View Related
Nov 19, 2010
I am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?
You can re-generate the scenario at your end with the following code-
--Login to System-
create user manu identified by manu;
grant connect, resource to manu;
create user arpit identified by arpit;
grant connect, resource to arpit;
grant create materialized view to manu;
[code]....
but when I am doing it with on demand options, materialized view is being created-
--Login to Manu-
create materialized view arpit_view
build immediate
refresh on demand
as
select * from arpit.arpit
/
View 5 Replies
View Related
Aug 20, 2009
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.
DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh
View 6 Replies
View Related
Apr 27, 2011
I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal
WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TBTABS
WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS)
INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J
WHERE j.tabstatus NOT BETWEEN -6 AND -4
AND j.linetype = 1
AND T.tabid = J.tabid
AND T.companyid = J.companyid
AND T.storelocid = J.storelocid)
from the where clause
View 10 Replies
View Related
