Re: The problem is that SQL*Net is too chatty, because FTP runs fine.

Jared,

A 10046 trace is one of the first things I asked for when I got pulled in on this problem. There was some resistance, so I let it go at the time. Yesterday I started pushing again for doing a 10046 trace. I probably would have gotten too if the problem had not been resolved today. Yep, the problem has been resolved and it wasn't a network issue. Oh my! You are going to love this one, it was the change in the complete refresh method from 9i to 10g where a delete/insert is done instead of a truncate/insert /*+ append */.

Once the problem was isolated to be a 9i to 10g issue everything fell into place. Here are a couple of quotes from the last two emails I received from the primary DBA on this task:

"...I'm running a test now with atomic_refresh => false."

"What used to run in 2 hours 40 min now runs in 12 min..."

I guess it is tempting to call out the posse and round up the usual suspects, only this time the usual suspects weren't even in town. :-) The primary DBA is currently running more tests to ensure he can replicate the issue several more times, but I suspect we call this one a wrap.

Again, thanks everyone for you input.

Cheers,

David

P.S. To make it easy for anyone else following this thread, below are some links that talk about this MV refresh issue when going from 9i to 10g as well as the Oracle doc reference to the "Atomic refresh" parameter:

http://www.freelists.org/archives/oracle-l/01-2007/msg00605.html
http://www.freelists.org/archives/oracle-l/01-2007/msg00606.html
http://www.freelists.org/archives/oracle-l/08-2007/msg00669.html
http://www.freelists.org/archives/oracle-l/08-2007/msg00677.html
http://www.freelists.org/archives/oracle-l/04-2007/msg00026.html
http://www.freelists.org/archives/oracle-l/06-2007/msg00347.html
http://www.freelists.org/archives/oracle-l/08-2007/msg00655.html
http://www.freelists.org/archives/oracle-l/05-2006/msg01206.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15695764787749#75858971544055 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15695764787749#79471455776904

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/refresh.htm#sthref846 Oracle(R) Database Data Warehousing Guide 10g Release 2 (10.2)

15 Maintaining the Data Warehouse

Refresh Specific Materialized Views with REFRESH Refresh All Materialized Views with REFRESH_ALL_MVIEWS

Atomic refresh (TRUE or FALSE)
If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction. If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.

On Feb 1, 2008 2:35 PM, Jared Still <jkstill_at_gmail.com> wrote:

> On Jan 31, 2008 9:14 AM, David Taft <oradbt054_at_gmail.com> wrote:
>
> I may be asking the obvious here, but have you collected timing statistics
> on
> 'SQL*Net more data to client' from the database?
>
>

--
http://www.freelists.org/webpage/oracle-l