| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: are fast refreshes really fast?
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------=_NextPartTM-000-f5a40469-1faf-4681-a974-f6005723b502 Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C34BB9.33FEE3A2"
------_=_NextPart_001_01C34BB9.33FEE3A2
Content-Type: text/plain;
charset="iso-8859-1"
vw_nso_1 is probably an inline view .... look for statements in the trace file ... something that has inline views ...
Thanks for this analysis ...
Raj
-----Original Message-----
From: Henry Poras [mailto:hporas_at_etal.uri.edu]
Sent: Wednesday, July 16, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L
Subject: are fast refreshes really fast?
I've been working with Materialized Views and fast refreshes lately. I
finally stopped to think about what is going on. Amazing what happens when I
stop to think. When I think of fast refreshes, I assume the materialized
view is refreshed from the MLOG$ tables associated with the base tables
(MLOG$ tables record changes, Insert/Update/Delete, made to the base
tables). I just realized that this isn't necessarily so. A fast refresh can
do a full table scan (or index scan, ...) on base tables. Pretty
straightforward, I just never stopped to think. I guess this means that some
fast refreshes can take quite a while (the pause that refreshes?).
Here is the deal. If the materialized view is based on a join there is no choice but to access the base table(s). A simple example:
CREATE TABLE test1 (id1 number);
CREATE TABLE test2 (id2 number
id1 number);
CREATE MATERIALIZED VIEW LOG on test1
WITH ROWID;
this creates mlog$_test1 there are some problems with join fast refreshes using Primary Keys
CREATE MATERIALIZED VIEW LOG on test2
WITH ROWID;
this creates mlog$_test2
CREATE MATERIALIZED VIEW test1_test2
WITH ROWID
AS
SELECT test1.rowid "t1_rowid", test2.rowid "t2_rowid", test1.id1, test2.id2
FROM test1, test2
WHERE test1.id1=test2.id1
/
INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (2,1);
COMMIT;
mlog$_test1 and mlog$_test2 both have a single entry.
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');
do a fast refresh
So far this seems OK. All that is needed to populate the materialized view are the mlogs. After the refresh completes successfully, the two mlog$ tables are empty, as expected.
Now try
INSERT INTO test2 VALUES (3,1);
COMMIT;
mlog$_test2 has one row, mlog$_test1 is empty.
When I refresh test1_test2, a row is added because of my entry into test2. The test1 data, however, is no longer in mlog$_test1, but in the base table test1. The fast refresh must access the base tables. How it does so is dependent on available indexes, statistics, table size, ...
To confirm this, I ran my test with 10046 trace on, and did three sets of inserts/fast refresh
case#1
INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (2,1);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');
The trace file had two different INSERT INTO test1_test2 statements. It was actually formed, by Oracle, with various hints and a nested subquery. The main point is that one of the inserts was driven by MLOG$_TEST2 and accessed test1 (by index unique scan in this case), while the other insert was driven by MLOG$_TEST1 and accessed base table test2 (fts/hash join).
case#2
INSERT INTO test2 values (3,1);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');
The trace file had a single INSERT INTO test1_test2. It was driven by MLOG$_TEST2 and accessed test1 with index unique scan.
case#3
INSERT INTO test1 VALUES (2);
exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');
The trace file had a single INSERT INTO test1_test2. It was driven by MLOG$_TEST1 and accessed test2 with fts/hash join.
I am still looking through my trace files when I have free time (ha!) to see if there is any other good stuff. For example, the explain plans for the INSERT INTO test1_test2 inlude a view VW_NSO_1 which I can't find. No clue what that is.
Hope this helps someone.
Henry
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: hporas_at_etal.uri.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Jul 16 2003 - 11:42:10 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C34BB9.33FEE3A2 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2654.45"> <TITLE>RE: are fast refreshes really fast?</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>vw_nso_1 is probably an inline view .... look for = statements in the trace file ... something that has inline views = ...</FONT> </P> <P><FONT SIZE=3D2>Thanks for this analysis ...</FONT> <BR><FONT SIZE=3D2>Raj</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----------------</FONT> <BR><FONT SIZE=3D2>Rajendra dot Jamadagni at nospamespn dot com</FONT> <BR><FONT SIZE=3D2>All Views expressed in this email are strictly = personal.</FONT> <BR><FONT SIZE=3D2>QOTD: Any clod can have facts, having an opinion is = an art !</FONT> </P> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Henry Poras [<A = HREF=3D"mailto:hporas_at_etal.uri.edu">mailto:hporas_at_etal.uri.edu</A>]</FON= T> <BR><FONT SIZE=3D2>Sent: Wednesday, July 16, 2003 1:29 PM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: are fast refreshes really fast?</FONT> </P> <BR> <P><FONT SIZE=3D2>I've been working with Materialized Views and fast = refreshes lately. I</FONT> <BR><FONT SIZE=3D2>finally stopped to think about what is going on. = Amazing what happens when I</FONT> <BR><FONT SIZE=3D2>stop to think. When I think of fast refreshes, I = assume the materialized</FONT> <BR><FONT SIZE=3D2>view is refreshed from the MLOG$ tables associated = with the base tables</FONT> <BR><FONT SIZE=3D2>(MLOG$ tables record changes, Insert/Update/Delete, = made to the base</FONT> <BR><FONT SIZE=3D2>tables). I just realized that this isn't necessarily = so. A fast refresh can</FONT> <BR><FONT SIZE=3D2>do a full table scan (or index scan, ...) on base = tables. Pretty</FONT> <BR><FONT SIZE=3D2>straightforward, I just never stopped to think. I = guess this means that some</FONT> <BR><FONT SIZE=3D2>fast refreshes can take quite a while (the pause = that refreshes?).</FONT> </P> <P><FONT SIZE=3D2>Here is the deal. If the materialized view is based = on a join there is no</FONT> <BR><FONT SIZE=3D2>choice but to access the base table(s). A simple = example:</FONT> </P> <P><FONT SIZE=3D2>CREATE TABLE test1 (id1 number);</FONT> <BR><FONT SIZE=3D2>CREATE TABLE test2 (id2 number</FONT> <BR><FONT = SIZE=3D2> &nb= sp; &nb= sp; = id1 number);</FONT> </P> <P><FONT SIZE=3D2>CREATE MATERIALIZED VIEW LOG on test1</FONT> <BR><FONT SIZE=3D2>WITH ROWID;</FONT> <BR> <FONT SIZE=3D2>this = creates mlog$_test1</FONT> <BR> <FONT SIZE=3D2>there are = some problems with join fast refreshes using Primary Keys</FONT> </P> <P><FONT SIZE=3D2>CREATE MATERIALIZED VIEW LOG on test2</FONT> <BR><FONT SIZE=3D2>WITH ROWID;</FONT> <BR> <FONT SIZE=3D2>this = creates mlog$_test2</FONT> </P> <P><FONT SIZE=3D2>CREATE MATERIALIZED VIEW test1_test2</FONT> <BR><FONT SIZE=3D2>WITH ROWID</FONT> <BR><FONT SIZE=3D2>AS</FONT> <BR><FONT SIZE=3D2>SELECT test1.rowid "t1_rowid", = test2.rowid "t2_rowid", test1.id1, test2.id2</FONT> <BR><FONT SIZE=3D2>FROM test1, test2</FONT> <BR><FONT SIZE=3D2>WHERE test1.id1=3Dtest2.id1</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT SIZE=3D2>INSERT INTO test1 VALUES (1);</FONT> <BR><FONT SIZE=3D2>INSERT INTO test2 VALUES (2,1);</FONT> <BR><FONT SIZE=3D2>COMMIT;</FONT> <BR> <FONT = SIZE=3D2>mlog$_test1 and mlog$_test2 both have a single entry.</FONT> </P> <P><FONT SIZE=3D2>exec DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');</FONT> <BR> <FONT SIZE=3D2>do a fast = refresh</FONT> </P> <P><FONT SIZE=3D2>So far this seems OK. All that is needed to populate = the materialized view</FONT> <BR><FONT SIZE=3D2>are the mlogs. After the refresh completes = successfully, the two mlog$</FONT> <BR><FONT SIZE=3D2>tables are empty, as expected.</FONT> </P> <P><FONT SIZE=3D2>Now try</FONT> </P> <P><FONT SIZE=3D2>INSERT INTO test2 VALUES (3,1);</FONT> <BR><FONT SIZE=3D2>COMMIT;</FONT> <BR> <FONT = SIZE=3D2>mlog$_test2 has one row, mlog$_test1 is empty.</FONT> </P> <P><FONT SIZE=3D2>When I refresh test1_test2, a row is added because of = my entry into test2.</FONT> <BR><FONT SIZE=3D2>The test1 data, however, is no longer in = mlog$_test1, but in the base table</FONT> <BR><FONT SIZE=3D2>test1. The fast refresh must access the base tables. = How it does so is</FONT> <BR><FONT SIZE=3D2>dependent on available indexes, statistics, table = size, ...</FONT> </P> <P><FONT SIZE=3D2>To confirm this, I ran my test with 10046 trace on, = and did three sets of</FONT> <BR><FONT SIZE=3D2>inserts/fast refresh</FONT> </P> <P><FONT SIZE=3D2>case#1</FONT> <BR><FONT SIZE=3D2>INSERT INTO test1 VALUES (1);</FONT> <BR><FONT SIZE=3D2>INSERT INTO test2 VALUES (2,1);</FONT> <BR><FONT SIZE=3D2>exec = DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');</FONT> </P> <P><FONT SIZE=3D2>The trace file had two different INSERT INTO = test1_test2 statements. It was</FONT> <BR><FONT SIZE=3D2>actually formed, by Oracle, with various hints and a = nested subquery. The</FONT> <BR><FONT SIZE=3D2>main point is that one of the inserts was driven by = MLOG$_TEST2 and accessed</FONT> <BR><FONT SIZE=3D2>test1 (by index unique scan in this case), while the = other insert was driven</FONT> <BR><FONT SIZE=3D2>by MLOG$_TEST1 and accessed base table test2 =
(fts/hash join).</FONT>
</P> <P><FONT SIZE=3D2>case#2</FONT> <BR><FONT SIZE=3D2>INSERT INTO test2 values (3,1);</FONT> <BR><FONT SIZE=3D2>exec = DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');</FONT> </P> <P><FONT SIZE=3D2>The trace file had a single INSERT INTO test1_test2. = It was driven by</FONT> <BR><FONT SIZE=3D2>MLOG$_TEST2 and accessed test1 with index unique = scan.</FONT> </P> <P><FONT SIZE=3D2>case#3</FONT> <BR><FONT SIZE=3D2>INSERT INTO test1 VALUES (2);</FONT> <BR><FONT SIZE=3D2>exec = DBMS_SNAPSHOT.REFRESH('TEST1_TEST2','F');</FONT> </P> <P><FONT SIZE=3D2>The trace file had a single INSERT INTO test1_test2. = It was driven by</FONT> <BR><FONT SIZE=3D2>MLOG$_TEST1 and accessed test2 with fts/hash = join.</FONT> </P> <P><FONT SIZE=3D2>I am still looking through my trace files when I have = free time (ha!) to see</FONT> <BR><FONT SIZE=3D2>if there is any other good stuff. For example, the = explain plans for the</FONT> <BR><FONT SIZE=3D2>INSERT INTO test1_test2 inlude a view VW_NSO_1 which = I can't find. No clue</FONT> <BR><FONT SIZE=3D2>what that is.</FONT> </P> <P><FONT SIZE=3D2>Hope this helps someone.</FONT> </P> <P><FONT SIZE=3D2>Henry</FONT> </P> <BR> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A = HREF=3D"http://www.orafaq.net" = TARGET=3D"_blank">http://www.orafaq.net</A></FONT> <BR><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Henry Poras</FONT> <BR><FONT SIZE=3D2> INET: hporas_at_etal.uri.edu</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- = 858-538-5051 <A HREF=3D"http://www.fatcity.com" = TARGET=3D"_blank">http://www.fatcity.com</A></FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Mailing list = and web hosting services</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= ------</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an = E-Mail message</FONT> <BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of = 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB = ORACLE-L</FONT> <BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed = from). You may</FONT> <BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P> </BODY> </HTML> ------_=_NextPart_001_01C34BB9.33FEE3A2-- ------=_NextPartTM-000-f5a40469-1faf-4681-a974-f6005723b502 Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" ********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2
 |
 |