FAQ
It takes a long time to get connected to our Oracle 8i database.
Once connection got established the performance is OK. However it may take
few minutes to get connected.
We are in dedicated mode. Any points how to investigate the problem?

Please help.
Thanks,
Inessa.

Search Discussions

  • Norman Dunbar at Feb 3, 2006 at 4:24 pm
    Afternoon,

    initially I would check out the DNS by changing the hostname in my
    tnsnames.ora file for a direct IP address - just in case you are getting
    a delay in the DNS lookup.

    Cheers,
    Norman.

    Norman Dunbar.
    Contract Oracle DBA.
    Rivers House, Leeds.

    Internal : 7 28 2051
    External : 0113 231 2051
    Inessa Weiner 02/03/06 04:16pm >>>
    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take
    few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?

    Please help.
    Thanks,
    Inessa.

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

    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
    --
    http://www.freelists.org/webpage/oracle-l
  • Radoulov, Dimitre at Feb 3, 2006 at 4:27 pm

    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?
    I would check for DNS issues first: try to configure the tnsnames.ora/jdbc
    url etc with IP address instead of machine/logical host name.

    If the problem is not there, check for lack of resources on the DB
    server(especially for available memory) and try to activate the OracleNet
    tracing.

    Regards,
    Dimitre
  • Tanel Põder at Feb 3, 2006 at 7:19 pm

    I would check for DNS issues first: try to configure the tnsnames.ora/jdbc
    url etc with IP address instead of machine/logical host name.
    Same here.

    You could verify a DNS issue by truss-ing or strace-ing the tnslistener
    process during a new connection...

    $ strace -T -p 3057
    Process 3057 attached - interrupt to quit
    poll([{fd=8, events=POLLIN|POLLRDNORM, revents=POLLIN|POLLRDNORM}, {fd=8,
    events=0}, {fd=10, events=POLLIN|POLLRDNORM}, {fd=10, events=0}, {fd=11,
    events=POLLIN|POLLRDNORM}, {fd=11, events=0}, {fd=-1}], 7, -1) = 1
    <1.809157>
    gettimeofday({1138994188, 114434}, NULL) = 0 <0.000661>
    getsockname(8, {sa_family=AF_INET, sin_port=htons(1521),
    sin_addr=inet_addr("0.0.0.0")}, [16]) = 0 <0.000064>
    getpeername(8, 0xbfff94c4, [16]) = -1 ENOTCONN (Transport endpoint is
    not connected) <0.000049>
    ^^^
    maybe the getpeername() takes a lot of time in your case, the listener could
    be doing reverse DNS lookup

    Tanel.
  • Kline.Michael at Feb 3, 2006 at 4:57 pm
    I remember a long time ago some stories if your listener.log was
    excessively large you could have a problem like this.

    Another maybe.

    Michael Kline
    Database Administration
    Outside 804.261.9446
    Cell 804.744.1545
    3-9446


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Radoulov, Dimitre
    Sent: Friday, February 03, 2006 11:28 AM
    To: ikar_at_med.cornell.edu
    Cc: oracle-l_at_freelists.org
    Subject: Re: connection to the database is slow
    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?
    I would check for DNS issues first: try to configure the
    tnsnames.ora/jdbc
    url etc with IP address instead of machine/logical host name.

    If the problem is not there, check for lack of resources on the DB
    server(especially for available memory) and try to activate the
    OracleNet
    tracing.

    Regards,
    Dimitre

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

    LEGAL DISCLAIMER
    The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]

    --
    http://www.freelists.org/webpage/oracle-l
  • Joseph Amalraj at Feb 3, 2006 at 5:06 pm
    Try connecting on the same box as the database thru' tnsnames.ora (tcp) connection. If it is still slow it is a listener issue.


    Check if listener is one of the top processes. The listener may be having a lot of workload, in which case listener process can be assigned a higher priority or a second listener can be created (but on a different port - and this may not be preferred).


    Load on the listener can be reduced by turning the local database connections to "bequeath" instead of ipc or tcp.


    Regards


    Joseph Amalraj




    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Radoulov, Dimitre
    Sent: Friday, February 03, 2006 11:28 AM
    To: ikar_at_med.cornell.edu
    Cc: oracle-l_at_freelists.org
    Subject: Re: connection to the database is slow
    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?
    I would check for DNS issues first: try to configure the
    tnsnames.ora/jdbc
    url etc with IP address instead of machine/logical host name.

    If the problem is not there, check for lack of resources on the DB
    server(especially for available memory) and try to activate the
    OracleNet
    tracing.

    Regards,
    Dimitre

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

    LEGAL DISCLAIMER
    The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Ranko Mosic at Feb 3, 2006 at 5:24 pm
    On v7( don't know which release) SQL*Net logging ( or trace, can't remember
    ) was enabled by default and would make connection process very slow. (
    Problem similar to one with slow export on this list ).
    Check is trace/logging is enabled.
    On 2/3/06, Joseph Amalraj wrote:


    Try connecting on the same box as the database thru' tnsnames.ora (tcp)
    connection. If it is still slow it is a listener issue.

    Check if listener is one of the top processes. The listener may be having
    a lot of workload, in which case listener process can be assigned a higher
    priority or a second listener can be created (but on a different port - and
    this may not be preferred).

    Load on the listener can be reduced by turning the local database
    connections to "bequeath" instead of ipc or tcp.

    Regards

    Joseph Amalraj




    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    [mailto: oracle-l-bounce@freelists.org] On Behalf Of Radoulov, Dimitre
    Sent: Friday, February 03, 2006 11:28 AM
    To: ikar_at_med.cornell.edu
    Cc: oracle-l_at_freelists.org
    Subject: Re: connection to the database is slow
    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?
    I would check for DNS issues first: try to configure the
    tnsnames.ora/jdbc
    url etc with IP address instead of machine/logical host name.

    If the problem is not there, check for lack of resources on the DB
    server(especially for available memory) and try to activate the
    OracleNet
    tracing.



    Regards,
    Dimitre

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



    LEGAL DISCLAIMER
    The information transmitted is intended solely for the individual or
    entity to which it is addressed and may contain confidential and/or
    privileged mat erial. Any review, retransmission, dissemination or other use
    of or taking action in reliance upon this information by persons or entities
    other than the intended recipient is prohibited. If you have received this
    email in error please contact the sender and delete the material from any
    computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]



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



    --
    Regards,
    Ranko Mosic
    Contract Senior Oracle DBA
    Oracle 10g, 9i Certified Database Professional
    Phone: 416-450-2785
    email: mosicr_at_rogers.com
    http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html

    --
    http://www.freelists.org/webpage/oracle-l
  • Dimitar Radoulov at Feb 3, 2006 at 7:20 pm

    On 2/3/06, Thomas A. La Porte wrote:

    There isn't a mention of platform (client or server), but it
    would be useful to truss or strace the client process if you are
    on a Unix box.

    That should give you an indication of where the time is being
    spent, much like a 10043 trace. No sense guessing if you have
    tools available to tell you what's wrong.
    I agree ..., but you have to consider the time you have to spend to learn
    to interpret the trace/strace/truss output :)

    Regards,
    Dimitre
  • Tanel Põder at Feb 3, 2006 at 8:00 pm
    I think on learning you never spend time - you invest time.

    In long term, learning new things always pays off. You learn truss once and are able to accurately determine where the problem lies in the future. The other way is do some rule of thumb quick checks every time you hit a problem, some times this approach pays off fast, sometimes you get completely distracted from the root cause of your problem.

    In long term - learning wins.

    Tanel.

    I agree ..., but you have to consider the time you have to spend to learn to interpret the trace/strace/truss output :)

    Regards,
    Dimitre
  • Radoulov, Dimitre at Feb 3, 2006 at 8:59 pm

    I think on learning you never spend time - you invest time.
    You're right, the correct word was "elapsed".
    In long term, learning new things always pays off.
    You learn truss once and are able to accurately determine where the
    problem lies in the future.
    The other way is do some rule of thumb quick checks every time you hit a
    problem,
    some times this approach pays off fast, sometimes you get completely
    distracted from the root cause of your problem.
    I agree, but you have to be able to distinguish between the SLA time and the
    time you can dedicate to your studies.

    Regards,
    Dimitre
  • Tanel Põder at Feb 3, 2006 at 10:03 pm

    I agree, but you have to be able to distinguish between the SLA time and
    the time you can dedicate to your studies.
    Dimitre, I see your point, but still - once you invest this additional time
    (when the SLA has achieved again by old methods) to learn the new stuff,
    then next time you already have additional methods in your arsenal.

    Again, we probably are thinking the same way, just speaking about slightly
    different things.

    Tanel.
  • Juan Carlos Reyes Pacheco at Feb 3, 2006 at 7:44 pm
    in tnsnames or whatever, use the ip, not the server name, once i have
    the problem of windows server misconfiguration, and this solved the
    problem.
  • Sol beach at Feb 3, 2006 at 7:45 pm
    Using snoop/tcpdump might provide some insight at the raw packet layer
    On 2/3/06, Inessa Weiner wrote:

    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take
    few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Feb 3, 2006 at 7:46 pm
    Check your $ORACLE_HOME/otrace/admin directory.

    Do you have some rather large *.dat files there?

    If so, Oracle Trace is turned on.

    Google for EPC_DISABLED for more info.

    This was definitely a problem on 7.3 as Oracle Trace was on by
    default.

    I can't recall if was still a problem with 8i, but it's worth a look.

    HTH

    Jared
    On 2/3/06, Inessa Weiner wrote:

    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take
    few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?

    Please help.
    Thanks,
    Inessa.


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

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Ranko Mosic at Feb 3, 2006 at 7:58 pm
    That's what I said.
    On 2/3/06, Jared Still wrote:

    Check your $ORACLE_HOME/otrace/admin directory.

    Do you have some rather large *.dat files there?

    If so, Oracle Trace is turned on.

    Google for EPC_DISABLED for more info.

    This was definitely a problem on 7.3 as Oracle Trace was on by
    default.

    I can't recall if was still a problem with 8i, but it's worth a look.

    HTH
    Jared
    On 2/3/06, Inessa Weiner wrote:

    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take
    few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?

    Please help.
    Thanks,
    Inessa.


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


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
    --
    Regards,
    Ranko Mosic
    Contract Senior Oracle DBA
    Oracle 10g, 9i Certified Database Professional
    Phone: 416-450-2785
    email: mosicr_at_rogers.com
    http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html

    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Feb 3, 2006 at 8:12 pm
    Well, yes, except you left out the location and name of trace
    files, and the EPC_DISABLED environment variable.

    :)

    Jared
    On 2/3/06, Ranko Mosic wrote:

    That's what I said.
    On 2/3/06, Jared Still wrote:

    Check your $ORACLE_HOME/otrace/admin directory.

    Do you have some rather large *.dat files there?

    If so, Oracle Trace is turned on.

    Google for EPC_DISABLED for more info.

    This was definitely a problem on 7.3 as Oracle Trace was on by
    default.

    I can't recall if was still a problem with 8i, but it's worth a look.

    HTH
    Jared
    On 2/3/06, Inessa Weiner wrote:

    It takes a long time to get connected to our Oracle 8i database.
    Once connection got established the performance is OK. However it may
    take
    few minutes to get connected.
    We are in dedicated mode. Any points how to investigate the problem?

    Please help.
    Thanks,
    Inessa.


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


    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist


    --
    Regards,
    Ranko Mosic
    Contract Senior Oracle DBA
    Oracle 10g, 9i Certified Database Professional
    Phone: 416-450-2785
    email: mosicr_at_rogers.com

    http://ca.geocities.com/mosicr@rogers.com/ContractSeniorOracleDBARankoMosicMain.html
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Feb 3, 2006 at 8:21 pm
    True enough Dimitre.....but, you learn it once, and it will serve you
    well. I think it's a worthwhile investment to learn how to read truss
    output....;-)


    -Mark


    --
    Mark J. Bobak
    Senior Oracle Architect
    ProQuest Information & Learning

    "There are 10 types of people in the world: Those who understand
    binary, and those who don't."



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dimitar Radoulov
    Sent: Friday, February 03, 2006 2:20 PM
    To: Thomas A. La Porte
    Cc: oracle-l_at_freelists.org
    Subject: Re: connection to the database is slow

    On 2/3/06, Thomas A. La Porte wrote:

    There isn't a mention of platform (client or server), but it
    would be useful to truss or strace the client process if you are

    on a Unix box.

    That should give you an indication of where the time is being
    spent, much like a 10043 trace. No sense guessing if you have
    tools available to tell you what's wrong.

    I agree ..., but you have to consider the time you have to spend to
    learn to interpret the trace/strace/truss output :)



    Regards,
    Dimitre





    --
    http://www.freelists.org/webpage/oracle-l
  • Radoulov, Dimitre at Feb 3, 2006 at 8:40 pm

    True enough Dimitre.....but, you learn it once, and it will serve you
    well. I think it's a worthwhile investment to learn how to read truss
    output....;-)
    I think on learning you never spend time - you invest time.

    In long term, learning new things always pays off.
    You learn truss once and are able to accurately determine where the
    problem lies in the future.
    The other way is do some rule of thumb quick checks every time you hit a
    problem,
    some times this approach pays off fast, sometimes you get completely
    distracted from the root cause of your problem.

    In long term - learning wins.
    Mark, Tanel,
    I think it's a question of methodology and business requirements:
    in this case I would try to exclude the obvious first (guessing) and it's
    likely that I can gain time and fix the problem faster.
    In other words, I would avoid tracing with 10046 a poorly performing sql
    statement without checking the table structure and the indexes beforehand.

    Regards,
    Dimitre
  • Tanel Põder at Feb 3, 2006 at 8:55 pm

    Mark, Tanel,
    I think it's a question of methodology and business requirements:
    in this case I would try to exclude the obvious first (guessing) and it's
    likely that I can gain time and fix the problem faster.
    In other words, I would avoid tracing with 10046 a poorly performing sql
    statement without checking the table structure and the indexes beforehand.
    Agreed - we were talking about somewhat different things here.
    Learning and consistent methodology always pays off, but if you are
    acquintant with the system, it is worth to check out the issues what have
    "usually been a problem". But normally when you have exhausted the "usual
    suspects" from your experience only, it's worth to switch to a from top-down
    methodology.

    Tanel.
  • Tanel Põder at Feb 3, 2006 at 8:57 pm

    "usually been a problem". But normally when you have exhausted the "usual
    suspects" from your experience only, it's worth to switch to a from
    top-down methodology.
    I mean here that I personally don't start scanning google and trying fixes
    what others have used if I've exhausted my own experience. Then it's time to
    learn something new :)

    Tanel.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 3, '06 at 4:16p
activeFeb 3, '06 at 10:03p
posts20
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase