> Larry,
>
> Thanks for taking the time to explain this. You are really patient when it
> comes to writing!
> I see now how Oracle does the refresh. I also remember now seeing
> a post in
> this list some time ago asking about the possibility  of tuning a similar
> sql. I think it was you :)

It was me. I was asking about reverse key indexes and if anyone had seen
issues. The Oracle replication query to resolve what to process was running
for a few hours, just the query -- I would pull it out and run it, very bad.
But the execution plan was good, what we would want. The PK was a reverse
key index based on a sequence. So I understand why the folks were tempted,
based on lots of what is written out there, to use a reverse key index.
Changed to a normal b-tree, and testing the query, it finished very quickly.
Changed back to reverse, the query to resolve what to process ran for ages.
And then back to b-tree, good performance again. So I asked the list if
anyone had seen similar things with reverse key indexes. You think other
people would have seen similar things if it was a problem with the reverse
key index. Anyway, we ditched the custom code that had a couple of
"issues" -- instead of fixing that code went with a normal b-tree index,
ditching the reverse key index, and went with Oracle's standard replication.
It has worked fine, until recent changes in a feed that is causing a *lot*
of updates. Now it's not so good (but the query still resolves quickly ;-))
Still don't know that we can attribute the query performance issue strictly
to a reverse key thing.

> As I see in the sql you sent, all the sql are using bind variables. This
> indicates that changes are done row by row in a procedural method
> instead of
> a single sql (update, insert) that handles all the changes in one shot.

Yep. But it would still be possible, I think, to parellelize that even
though it is done a row at a time. Divide and conquer and retain precedence.
I've done that in the past with custom code for pushing changes.

> When we talk about parallelizing it, then there are two choices:
> The first one is using parallelism in the sql engine itself and firing one
> sql statement that handles either all the updates or the entire deletes to
> the target table.

And this is where maybe the idea of remote transactions comes in and the
comments I referred to in the prior email. Could very well be missing
something here about why they serialize when going remotely. But it's still
interesting the parallelism you can get with a insert, for example,
depending on which side you initiate it, something you touched on in an
earlier email.

And the following comment from the 8.1.7 Data Warehousing Guide is
interesting and why we (I really, pressing on others) have been pursuing
this:

"If the user has done a lot of updates to the table, ... In the warehouse,
after a bulk load, the user should enable parallel DML in the session and
perform the refresh. Oracle will use parallel DML to do the refresh, which
will enhance performance tremendously. There is more to gain if the
materialized view is partitioned."

Note that the references are to "a table", and updates (understanding the
limitations of parallel updates in 8.1.7 and across partitions, not within)
not a group of tables. But the one thing they don't say in that statement is
if the MV is local, or if it is remote. So you find the following comment in
the Distributed Concepts Manual:

"If the table referenced in the table_expression_clause of an INSERT,
UPDATE, or DELETE statement is remote, then execution is serial rather than
parallel."

So that tends to make you think the parallelism is not going to happen when
refreshing a remote object. But heck, I can't even get a test case using an
MV and parallelism with a local object. So that's why I think I'm totally
missing something. And the thousands of pages of Oracle doc's is a lot to go
through to get the answer, though I'm trying ;-)

> Parallel updates is not available in releases below 9.2.

They are across different partitions, just not in the same partition, or so
we read, and I think I tested some time back. And I did a test case in 9.2,
not believing just the plan but actually observing, and saw the parallelism
within a  single partition.

> The second choice is to continue doing it in a procedural way but spawning
> many Oracle processes, and let the master process
> read the rows from the sql and dequeue/distribute them to the parallel job
> processes.
> The parallel jobs processes will do their jobs the same way
> (procedural) the
> single process does.

And that's what we had, though a couple of issues. And once the query
resolution performance was resolved, we went with standard replication. But
now it looks like we will dig out the old code that performs in the manner
you describe, fix the couple of issues with it, and run with that. We are
still testing capabilities -- hate to go with a custom solution if standard
features can do the job. But we have been unsuccessful with the tests. Maybe
instead of my pressing to investigate the capabilities, I should have just
agreed to go with a custom solution that we know could work. Just hate
re-inventing the wheel. But you have to look at the overall picture and the
time spent looking into things -- maybe shouldn't have been so forceful on
investigating "native" solutions and just ran with the custom approach that
would work.

> I asked a friend who handles replication and here is the thread:

Thanks for taking the time to bounce it off someone else.

Larry G. Elkins
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Reply via email to