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

From: "David Taft" <oradbt054@xxxxxxxxx>
To: "Jared Still" <jkstill@xxxxxxxxx>
Date: Fri, 1 Feb 2008 15:48:48 -0500

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:

//www.freelists.org/archives/oracle-l/01-2007/msg00605.html
//www.freelists.org/archives/oracle-l/01-2007/msg00606.html
//www.freelists.org/archives/oracle-l/08-2007/msg00669.html
//www.freelists.org/archives/oracle-l/08-2007/msg00677.html
//www.freelists.org/archives/oracle-l/04-2007/msg00026.html
//www.freelists.org/archives/oracle-l/06-2007/msg00347.html
//www.freelists.org/archives/oracle-l/08-2007/msg00655.html
//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@xxxxxxxxx> wrote:

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

Follow-Ups:
- Re: The problem is that SQL*Net is too chatty, because FTP runs fine.
  - From: Jared Still

References:
- Re: The problem is that SQL*Net is too chatty, because FTP runs fine.
  - From: Jared Still

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

Other related posts: