Hi Folks
Using a DSN of dbi:ODBC:xyz, the DBI -> connect(...) call takes 16 (sic)=
seconds
with both the Perl script and Oracle running on the same PC under Windows.
You have been warned :-(.
--
Cheers
Ron Savage, ron@savage.net.au on 13/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
------=_Part_4218_15620186.1150178644391
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
On 6/12/06, Ron Savage
> Using a DSN of dbi:ODBC:xyz, the DBI -> connect(...) call takes 16 (sic)
> seconds
> with both the Perl script and Oracle running on the same PC under Windows.
>
> You have been warned :-(.
>
I've occasionally seen similar outrageously long connection times with other
software (not necessarily Perl - but Windows is always part of the mix), and
it usually comes down to how the host names are resolved - and there turns
out to be some problems (severe problems) with ... well, I'm probably about
to use the jargon all wrong, but ... DNS and MS domain controllers and the
like. Sometimes, DHCP gets in the way too. So, I'd be more inclined to
label it a "network or PC client setup" problem than a "Perl + DBI _
DBD::Anything" problem.
--
Jonathan Leffler
Guardian of DBD::Informix - v2005.02 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
------=_Part_4218_15620186.1150178644391--
On Mon, 12 Jun 2006 23:04:04 -0700, Jonathan Leffler wrote:
Hi Jonathan
Wellllllllllllllll, I know Oracle has been criticized for taking up to 7=
secs to
connect in a non-Win evironment, so I assume this is the same, just much=
worse.
Of course, it could indicate the Oracle developers has a (twisted) sense of=
humour, and that this is their way of pressuring users to move to non-Win
platforms :-)).
Either way, it's appalling.
--
Cheers
Ron Savage, ron@savage.net.au on 13/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
Ron Savage wrote:
>On Mon, 12 Jun 2006 23:04:04 -0700, Jonathan Leffler wrote:
>
>Hi Jonathan
>
>Wellllllllllllllll, I know Oracle has been criticized for taking up to 7 secs to
>connect in a non-Win evironment, so I assume this is the same, just much worse.
>
>Of course, it could indicate the Oracle developers has a (twisted) sense of
>humour, and that this is their way of pressuring users to move to non-Win
>platforms :-)).
>
>Either way, it's appalling.
>
>
I have to say that it doesn't sound right to me. I agree that
connecting to Oracle through ODBC can be slower than straight connects
because there's more information being exchanged between the two sides.
But 14 seconds sounds too long. I would have a word with support and see
what they come up with.
Martin
On Tue, 13 Jun 2006 09:54:41 +0100, Martin Hall wrote:
Hi Martin
> I have to say that it doesn't sound right to me. I agree that
Perhaps you mean unbelievable? I agree.
--
Cheers
Ron Savage, ron@savage.net.au on 13/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
I would like to voice some frustration at the advice to ask
'support' for assistance in this situation. It has been a problem for years
now. Too many requests for help dealing with the PERL/ODBC/Windows problem
with slow connections are dead-ended.
I believe a major hurdle is that a very large proportion of the
PERL/ODBC/Windows requests involve attempts to utilize open source software
from within typically non-open source environments. The network support
often won't consider trying to solve the problem on first principles - it is
not their job to address issues involving non-standard software. In other
cases support staff couldn't help even if willing, they just don't know
enough about either networking in general or open source variants in
particular. Hence when a request for help to the DBI list elicits a response
along the lines of 'You'll have to ask your network admin to investigate at
their end', the problem is unlikely to be resolved.
The specific issue of slow connectivity using PERL/ODBC/Windows from
within a non-open source network has been recurring very frequently for
several years now. I ran this gauntlet in 2004, and it was clear then that
it was an old and essentially unresolved problem. We know it can be fixed
with co-operation at the network admin end, but this only matters if they
both wish and know what to do. A great many users (and would-be users) of
DBI would benefit from a comprehensive treatment of the subject. For
instance, what specifically are the environment parameters that are likely
at fault? Are there ways for the client to circumvent any of these? How can
the client prove that the problem is with the network setup, and not their
own implementation of PERL or DBI? In cases where network support might be
co-operative but not sufficiently knowledgable, can they be guided?
> Mark Fowler
Population Ecology Division
> Bedford Inst of Oceanography
> Dept Fisheries & Oceans
> Dartmouth NS Canada
B2Y 4A2
Tel. (902) 426-3529
Fax (902) 426-9710
Email fowlerm@mar.dfo-mpo.gc.ca
Home Tel. (902) 461-0708
Home Email mark.fowler@ns.sympatico.ca
-----Original Message-----
From: Martin Hall [mailto:martin.hall@oracle.com]
Sent: June 13, 2006 5:55 AM
To: ron@savage.net.au
Cc: List - DBI users
Subject: Re: Speed test for connecting to Oracle for Windows via ODBC
Ron Savage wrote:
>On Mon, 12 Jun 2006 23:04:04 -0700, Jonathan Leffler wrote:
>
>Hi Jonathan
>
>Wellllllllllllllll, I know Oracle has been criticized for taking up to
>7 secs to connect in a non-Win evironment, so I assume this is the same,
just much worse.
>
>Of course, it could indicate the Oracle developers has a (twisted)
>sense of humour, and that this is their way of pressuring users to move
>to non-Win platforms :-)).
>
>Either way, it's appalling.
>
>
I have to say that it doesn't sound right to me. I agree that connecting to
Oracle through ODBC can be slower than straight connects because there's
more information being exchanged between the two sides.
But 14 seconds sounds too long. I would have a word with support and see
what they come up with.
Martin
I can understand frustration but your request is too nebulous for those who
do have the skills to help you troubleshoot. I connect to Oracle from many
different Windows installs daily and have never seen the problems you have
described without other identifiable machine configuration issues (as in the
machine couldn't resolve ANYTHING quickly or had additional software that
contributed - like connecting from a vmware host). The problems were never
specifically related to DBI and were resolvable without the use of this list
so you fix the machine to fix your problem.
Your "blame the network guy statements" miss the point that for a lot of us
we ARE the networking guys and have both programming and infrastructure
skills. Not everyone works in environments where there are lines drawn
between that beginning and ending of our responsibilities - some of us even
deal with users :-)
You mention name resolution but don't go into detail. Are you connecting to
the server IP, the FQDN, or are you using the Netbios name? What OS are you
using and what patch level are you at. How many NICs are in the machine and
what is the binding order on those nics?
Saying it is "slow" is about as useful as saying it is "broken". For people
who want to help it is equally frustrating to hear about problems when they
are half described - I'd help if there was more there and I'm sure other
would as well. I think the reason people don't "solve" this problem is
because they can't replicate it with the information provided and move on to
other fire missions.
-M
-----Original Message-----
From: Fowler, Mark [mailto:FowlerM@mar.dfo-mpo.gc.ca]
Sent: Tuesday, June 13, 2006 7:03 AM
To: Martin Hall; ron@savage.net.au
Cc: List - DBI users
Subject: RE: Speed test for connecting to Oracle for Windows via ODBC
I would like to voice some frustration at the advice to ask
'support' for assistance in this situation. It has been a problem for years
now. Too many requests for help dealing with the PERL/ODBC/Windows problem
with slow connections are dead-ended.
I believe a major hurdle is that a very large proportion of the
PERL/ODBC/Windows requests involve attempts to utilize open source software
from within typically non-open source environments. The network support
often won't consider trying to solve the problem on first principles - it is
not their job to address issues involving non-standard software. In other
cases support staff couldn't help even if willing, they just don't know
enough about either networking in general or open source variants in
particular. Hence when a request for help to the DBI list elicits a response
along the lines of 'You'll have to ask your network admin to investigate at
their end', the problem is unlikely to be resolved.
The specific issue of slow connectivity using PERL/ODBC/Windows from
within a non-open source network has been recurring very frequently for
several years now. I ran this gauntlet in 2004, and it was clear then that
it was an old and essentially unresolved problem. We know it can be fixed
with co-operation at the network admin end, but this only matters if they
both wish and know what to do. A great many users (and would-be users) of
DBI would benefit from a comprehensive treatment of the subject. For
instance, what specifically are the environment parameters that are likely
at fault? Are there ways for the client to circumvent any of these? How can
the client prove that the problem is with the network setup, and not their
own implementation of PERL or DBI? In cases where network support might be
co-operative but not sufficiently knowledgable, can they be guided?
> Mark Fowler
Population Ecology Division
> Bedford Inst of Oceanography
> Dept Fisheries & Oceans
> Dartmouth NS Canada
B2Y 4A2
Tel. (902) 426-3529
Fax (902) 426-9710
Email fowlerm@mar.dfo-mpo.gc.ca
Home Tel. (902) 461-0708
Home Email mark.fowler@ns.sympatico.ca
-----Original Message-----
From: Martin Hall [mailto:martin.hall@oracle.com]
Sent: June 13, 2006 5:55 AM
To: ron@savage.net.au
Cc: List - DBI users
Subject: Re: Speed test for connecting to Oracle for Windows via ODBC
Ron Savage wrote:
>On Mon, 12 Jun 2006 23:04:04 -0700, Jonathan Leffler wrote:
>
>Hi Jonathan
>
>Wellllllllllllllll, I know Oracle has been criticized for taking up to
>7 secs to connect in a non-Win evironment, so I assume this is the same,
just much worse.
>
>Of course, it could indicate the Oracle developers has a (twisted)
>sense of humour, and that this is their way of pressuring users to move
>to non-Win platforms :-)).
>
>Either way, it's appalling.
>
>
I have to say that it doesn't sound right to me. I agree that connecting to
Oracle through ODBC can be slower than straight connects because there's
more information being exchanged between the two sides.
But 14 seconds sounds too long. I would have a word with support and see
what they come up with.
Martin
-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Monday, June 12, 2006 9:22 PM
To: List - DBI users
Subject: Speed test for connecting to Oracle for Windows via ODBC
>=20
> Hi Folks
>=20
> Using a DSN of dbi:ODBC:xyz, the DBI -> connect(...) call takes 16
> (sic) seconds with both the Perl script and Oracle running on the same
> PC under Windows.
Control Panel
-> Administrative Tools
-> Data Sources (ODBC)
-> (Your DSN)
-> Configure
-> Test Connection
How long does that take to acknowledge success?
Philip
Apologies, I was making a general observation. I was more interested in
making the exercise easier for others, as my own situation is probably an
obsolete issue. All those technical aspects necessary to obtain a solution
were provided during 2004, and some would no longer be valid (e.g.
esoftmatic ppd's, versions of Apache, PERL, Oracle, etc). You are quite
right to assume that DBI was not the problem. It was determined that the LAN
configuration was the problem (various degrees of bottlenecking among
network nodes), but without being able to specify the exact details, largely
due to disinterest on the part of LAN support with respect to non-standard
software. My point is that it would have been helpful if I could have
demonstrated without ambiguity what aspects of the configuration were
responsible for the bottlenecking, and what changes would have rectified the
problem. Perhaps this is not considered to have any bearing on DBI, but this
list is where I most often see the question raised.
I received a post from Job Miller pointing me to 'How to Perform Client-Side
Tracing of Programmatic Interfaces on Windows Platforms'. I haven't been
able to obtain this yet, but hopefully this sort of thing makes the task
easier for folks wrestling with DBI connectivity issues. I feel it important
for users within a Microsoft-centric environment to have a better handle on
networking when attempting to use open source communications software. An
'ask support' response may not be helpful in many of these cases.
> Mark Fowler
Population Ecology Division
> Bedford Inst of Oceanography
> Dept Fisheries & Oceans
> Dartmouth NS Canada
B2Y 4A2
Tel. (902) 426-3529
Fax (902) 426-9710
Email fowlerm@mar.dfo-mpo.gc.ca
Home Tel. (902) 461-0708
Home Email mark.fowler@ns.sympatico.ca
-----Original Message-----
From: Matthew Ramadanovic [mailto:matthew.ramadanovic@yale.edu]
Sent: June 13, 2006 10:05 AM
To: 'Fowler, Mark'; Martin Hall; ron@savage.net.au
Cc: List - DBI users
Subject: RE: Speed test for connecting to Oracle for Windows via ODBC
I can understand frustration but your request is too nebulous for those who
do have the skills to help you troubleshoot. I connect to Oracle from many
different Windows installs daily and have never seen the problems you have
described without other identifiable machine configuration issues (as in the
machine couldn't resolve ANYTHING quickly or had additional software that
contributed - like connecting from a vmware host). The problems were never
specifically related to DBI and were resolvable without the use of this list
so you fix the machine to fix your problem.
Your "blame the network guy statements" miss the point that for a lot of us
we ARE the networking guys and have both programming and infrastructure
skills. Not everyone works in environments where there are lines drawn
between that beginning and ending of our responsibilities - some of us even
deal with users :-)
You mention name resolution but don't go into detail. Are you connecting to
the server IP, the FQDN, or are you using the Netbios name? What OS are you
using and what patch level are you at. How many NICs are in the machine and
what is the binding order on those nics?
Saying it is "slow" is about as useful as saying it is "broken". For people
who want to help it is equally frustrating to hear about problems when they
are half described - I'd help if there was more there and I'm sure other
would as well. I think the reason people don't "solve" this problem is
because they can't replicate it with the information provided and move on to
other fire missions.
-M
-----Original Message-----
From: Fowler, Mark [mailto:FowlerM@mar.dfo-mpo.gc.ca]
Sent: Tuesday, June 13, 2006 7:03 AM
To: Martin Hall; ron@savage.net.au
Cc: List - DBI users
Subject: RE: Speed test for connecting to Oracle for Windows via ODBC
I would like to voice some frustration at the advice to ask
'support' for assistance in this situation. It has been a problem for years
now. Too many requests for help dealing with the PERL/ODBC/Windows problem
with slow connections are dead-ended.
I believe a major hurdle is that a very large proportion of the
PERL/ODBC/Windows requests involve attempts to utilize open source software
from within typically non-open source environments. The network support
often won't consider trying to solve the problem on first principles - it is
not their job to address issues involving non-standard software. In other
cases support staff couldn't help even if willing, they just don't know
enough about either networking in general or open source variants in
particular. Hence when a request for help to the DBI list elicits a response
along the lines of 'You'll have to ask your network admin to investigate at
their end', the problem is unlikely to be resolved.
The specific issue of slow connectivity using PERL/ODBC/Windows from
within a non-open source network has been recurring very frequently for
several years now. I ran this gauntlet in 2004, and it was clear then that
it was an old and essentially unresolved problem. We know it can be fixed
with co-operation at the network admin end, but this only matters if they
both wish and know what to do. A great many users (and would-be users) of
DBI would benefit from a comprehensive treatment of the subject. For
instance, what specifically are the environment parameters that are likely
at fault? Are there ways for the client to circumvent any of these? How can
the client prove that the problem is with the network setup, and not their
own implementation of PERL or DBI? In cases where network support might be
co-operative but not sufficiently knowledgable, can they be guided?
> Mark Fowler
Population Ecology Division
> Bedford Inst of Oceanography
> Dept Fisheries & Oceans
> Dartmouth NS Canada
B2Y 4A2
Tel. (902) 426-3529
Fax (902) 426-9710
Email fowlerm@mar.dfo-mpo.gc.ca
Home Tel. (902) 461-0708
Home Email mark.fowler@ns.sympatico.ca
-----Original Message-----
From: Martin Hall [mailto:martin.hall@oracle.com]
Sent: June 13, 2006 5:55 AM
To: ron@savage.net.au
Cc: List - DBI users
Subject: Re: Speed test for connecting to Oracle for Windows via ODBC
Ron Savage wrote:
>On Mon, 12 Jun 2006 23:04:04 -0700, Jonathan Leffler wrote:
>
>Hi Jonathan
>
>Wellllllllllllllll, I know Oracle has been criticized for taking up to
>7 secs to connect in a non-Win evironment, so I assume this is the
>same,
just much worse.
>
>Of course, it could indicate the Oracle developers has a (twisted)
>sense of humour, and that this is their way of pressuring users to move
>to non-Win platforms :-)).
>
>Either way, it's appalling.
>
>
I have to say that it doesn't sound right to me. I agree that connecting to
Oracle through ODBC can be slower than straight connects because there's
more information being exchanged between the two sides.
But 14 seconds sounds too long. I would have a word with support and see
what they come up with.
Martin
On Tue, 13 Jun 2006 09:57:38 -0400, Garrett, Philip \(MAN-Corporate\)=
wrote:
Hi Philip
> Control Panel
> -> Administrative Tools
> -> Data Sources (ODBC)
> -> (Your DSN)
> -> Configure
> -> Test Connection
>
> How long does that take to acknowledge success?
It's 16 seconds after clicking Test before the dialog box pops up asking for=
the
password. That may well be a Window-inspired delay of course.
Then, after entering the pw and clicking OK, it's usually < 1 sec.
However on the first test after booting, after clicking OK it was around 7=
.. 8
seconds (sound familiar?)
This is all on a home PC with a broadband connexion to the internet, and no=
other PCs in the house.
--
Cheers
Ron Savage, ron@savage.net.au on 14/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Tuesday, June 13, 2006 6:11 PM
To: List - DBI users
Subject: RE: Speed test for connecting to Oracle for Windows via ODBC
>=20
> On Tue, 13 Jun 2006 09:57:38 -0400, Garrett, Philip \(MAN-Corporate\)
wrote:
>=20
> > -> Test Connection
> >
> > How long does that take to acknowledge success?
>=20
> It's 16 seconds after clicking Test before the dialog box pops up
> asking for the password. That may well be a Window-inspired delay
> of course.
>
> Then, after entering the pw and clicking OK, it's usually < 1 sec.
>
> However on the first test after booting, after clicking OK it was
> around 7 .. 8 seconds (sound familiar?)
>
> This is all on a home PC with a broadband connexion to the internet,
> and no other PCs in the house.
My first guess is that the server is attempting to do reverse lookup on
you. My (general, not Oracle) experience has been that when there's a
several second delay when connecting, but everything is fine thereafter,
it's usually reverse DNS.
I use DBD::ADO to get at Oracle over ODBC without any problems. But of
course changing to ADO won't help you, because you have a network/Oracle
setup problem.
How long does it take to connect via sqlplus? tnsping?
Philip
On Tue, 13 Jun 2006 18:37:49 -0400, Garrett, Philip \(MAN-Corporate\) wrote:
Hi Philip
> How long does it take to connect via sqlplus? tnsping?
sqlplus user/pass responds in < 0.5 sec.
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html
On Tue, 13 Jun 2006 18:37:49 -0400, Garrett, Philip \(MAN-Corporate\) wrote:
Hi Philip
> How long does it take to connect via sqlplus? tnsping?
I should mention the MySQL ODBC connector V 3.5 when tested responds
immediately.
The Postgres connector does not have a test button.
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html
On Tue, 13 Jun 2006 18:37:49 -0400, Garrett, Philip \(MAN-Corporate\) wrote:
Hi Philip
> How long does it take to connect via sqlplus? tnsping?
Took me a while to work out what the command line option for tnsping was. Its
XE.
So: tnsping XE responds immediately.
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html
On Tue, 13 Jun 2006 18:37:49 -0400, Garrett, Philip \(MAN-Corporate\) wrote:
Hi Philip
>> It's 16 seconds after clicking Test before the dialog box pops up
That testing was under Win2K at home.
Now at work (Monash University) with WinXP and a network, response is immediate
(referring to the Test Connexion button in the ODBC setup).
--
Ron Savage
ron@savage.net.au
http://savage.net.au/index.html
On Wed, 14 Jun 2006 11:13:52 +1000, Ron Savage wrote:
Hi Ron
After some kindly prompting by Job Miller (thanx to all who offered help)=
and a
long tour of Oracle's site and of http://asktom.oracle.com/ I did this:
o Changed sqlnet.ora from:
SQLNET.AUTHENTICATION_SERVICES =3D (NTS)
to:
SQLNET.AUTHENTICATION_SERVICES =3D (NONE)
and connexions are now immediate.
Along the way I did this:
o Changed tnsnames.ora from:
(ADDRESS =3D (PROTOCOL =3D TCP)(HOST =3D ron)(PORT =3D 1521))
to:
(ADDRESS =3D (PROTOCOL =3D TCP)(HOST =3D 127.0.0.1)(PORT =3D 1521))
but that doesn't make any difference in speed.
The first version ('ron') triggers some trace msgs beginning with:
(1960) [23-JUN-2006 11:52:33:073] snlinGetAddrInfo: Invalid IP address=
string
ron
(1960) [23-JUN-2006 11:52:33:073] nttbnd2addr: looking up IP addr for host:=
ron
as you'd expect.
For the record, if anyone wishes to enable tracing, patch sqlnet.ora by=
adding:
TRACE_LEVEL_CLIENT =3D 4
TRACE_LEVEL_SERVER =3D 4
TRACE_DIRECTORY_CLIENT =3D c:\temp
TRACE_DIRECTORY_SERVER =3D c:\temp
TRACE_FILE_CLIENT =3D client
TRACE_FILE_SERVER =3D server
--
Cheers
Ron Savage, ron@savage.net.au on 23/06/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company