Re: Slow access to the data base.
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 21 Jan 2009 09:32:38 -0800 (PST)
Message-ID: <53dbfa89-797e-4335-903e-c89bef28ed7f_at_f20g2000yqg.googlegroups.com>
On Jan 19, 2:24 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 19, 6:53 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 16, 2:30 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > On Jan 16, 12:33 am, atch..._at_gmail.com wrote:
>
> > > > Good day,
>
> > > > I have an oracle installation on an IBM machine with AIX.
> > > > I have three users created on the Oracle DB: The user "oracle", "mo",
> > > > and "fo"
> > > > The installation was working well so far.
> > > > But for almost a week, the connexion of the users "mo" and "fo" have
> > > > been very slow. But the connexion to the user "oracle" has no
> > > > probleme.
> > > > When I stop the listener, I the users "mo" and "fo" can not access the
> > > > DB anymore wich mean that even on the same server, the users "mo" and
> > > > "fo" access the DB via a listenner.
>
> > > > This is my question:
>
> > > > 1- How can I do to make sure that on the server the users "mo" and
> > > > "fo" will access the DB without passing through a listener?
> > > > 2- What may have happened for the DB access to be sos slow?
> > > > 3- How can I globally solve the problem.
> > > > 4- Can I delete the users "mo" and "fo" and recreate them so that they
> > > > will connect to the DB without passing through a listener? How can I
> > > > make it?
>
> > > There are lots of things that can be happening here. metalink Note:
> > > 620256.995 has a person resolving identical symptoms by chmod 6751 on
> > > the oracle executable - it is a good example of what to check. If you
> > > don't have that exact protection on the oracle executable, it probably
> > > means you missed some installation steps, such as running root.sh. By
> > > the way, you don't want to set everything in the OH bin directory to
> > > that, just certain things.
>
> > > Off the top of my head (so season with a lot of salt), these are
> > > things you might check:
>
> > > Be sure you have all kernel patches and setting correct. It helps to
> > > say which exact version of Oracle and which exact version of the OS
> > > you are using whenever you post here (or anywhere, probably). Seehttp://dbaoracle.net/readme-cdos.htm#subj12 I have a vague memory
> > > that ibm has some docs online about certain AIX/oracle
> > > configurations. Your network hardware may even be relevant here.
>
> > > Check your tcp settings and also see what netstat has to say about who
> > > is connected. This is highly platform-dependent. Mark's assertion
> > > about using local listener connections and not having performance
> > > problems - well, to avoid the risk of spreading myths, I'm asking if
> > > anyone has any evidence one way or another. It's an assertion one of
> > > my vendors has made, and I don't think it is true over the universe of
> > > likely configurations and loads, but have no resources to prove it.
> > > And that vendor doesn't handle some tcp stuff correctly, I'm killing
> > > processes every day.
>
> > > Check your sqlnet.ora settings, some configurations may waste time.
> > > Also watch for platform-dependent bugs.
>
> > > AIX is somewhat idiosyncratic in my opinion, so take observations and
> > > advice about other platforms, even other unix and other versions of
> > > AIX, with caution. I don't have access to one so I can't be more
> > > specific, but when I did multi-platform support, hoo-boy.
>
> > > I'm sure I've seen more info out there on metalink and google for this
> > > sort of problem.
>
> > > jg
> > > --
> > > _at_home.com is bogus.
> > > If at first you don't succeed, throw good money after bad.http://www3.signonsandiego.com/stories/2009/jan/16/1m16ship001056-hop...quoted text -
>
> > > - Show quoted text -
>
> > Evidence is available via the sqlnet trace and listener.log file. If
> > the connects are truely slow then it becomes an issue of finding the
> > mis-configuration that will solve the problem. Your note is a good
> > starting point.
>
> > HTH -- Mark D Powell --
>
> Well, yes, if there is an actual problem like the OP, tracing is the
> way to go. I'm more concerned with the assertion that there are no
> performance hits with local tcp versus bequeath. That would require
> lots of testing and may vary by configuration. If nothing else, it's
> an additional piece of technology in the stack. It may not make any
> difference.
>
> jg
> --
> _at_home.com is bogus.http://singularityhub.com/2009/01/12/a-review-of-the-best-robots-of-2...- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 21 Jan 2009 09:32:38 -0800 (PST)
Message-ID: <53dbfa89-797e-4335-903e-c89bef28ed7f_at_f20g2000yqg.googlegroups.com>
On Jan 19, 2:24 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 19, 6:53 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 16, 2:30 pm, joel garry <joel-ga..._at_home.com> wrote:
>
> > > On Jan 16, 12:33 am, atch..._at_gmail.com wrote:
>
> > > > Good day,
>
> > > > I have an oracle installation on an IBM machine with AIX.
> > > > I have three users created on the Oracle DB: The user "oracle", "mo",
> > > > and "fo"
> > > > The installation was working well so far.
> > > > But for almost a week, the connexion of the users "mo" and "fo" have
> > > > been very slow. But the connexion to the user "oracle" has no
> > > > probleme.
> > > > When I stop the listener, I the users "mo" and "fo" can not access the
> > > > DB anymore wich mean that even on the same server, the users "mo" and
> > > > "fo" access the DB via a listenner.
>
> > > > This is my question:
>
> > > > 1- How can I do to make sure that on the server the users "mo" and
> > > > "fo" will access the DB without passing through a listener?
> > > > 2- What may have happened for the DB access to be sos slow?
> > > > 3- How can I globally solve the problem.
> > > > 4- Can I delete the users "mo" and "fo" and recreate them so that they
> > > > will connect to the DB without passing through a listener? How can I
> > > > make it?
>
> > > There are lots of things that can be happening here. metalink Note:
> > > 620256.995 has a person resolving identical symptoms by chmod 6751 on
> > > the oracle executable - it is a good example of what to check. If you
> > > don't have that exact protection on the oracle executable, it probably
> > > means you missed some installation steps, such as running root.sh. By
> > > the way, you don't want to set everything in the OH bin directory to
> > > that, just certain things.
>
> > > Off the top of my head (so season with a lot of salt), these are
> > > things you might check:
>
> > > Be sure you have all kernel patches and setting correct. It helps to
> > > say which exact version of Oracle and which exact version of the OS
> > > you are using whenever you post here (or anywhere, probably). Seehttp://dbaoracle.net/readme-cdos.htm#subj12 I have a vague memory
> > > that ibm has some docs online about certain AIX/oracle
> > > configurations. Your network hardware may even be relevant here.
>
> > > Check your tcp settings and also see what netstat has to say about who
> > > is connected. This is highly platform-dependent. Mark's assertion
> > > about using local listener connections and not having performance
> > > problems - well, to avoid the risk of spreading myths, I'm asking if
> > > anyone has any evidence one way or another. It's an assertion one of
> > > my vendors has made, and I don't think it is true over the universe of
> > > likely configurations and loads, but have no resources to prove it.
> > > And that vendor doesn't handle some tcp stuff correctly, I'm killing
> > > processes every day.
>
> > > Check your sqlnet.ora settings, some configurations may waste time.
> > > Also watch for platform-dependent bugs.
>
> > > AIX is somewhat idiosyncratic in my opinion, so take observations and
> > > advice about other platforms, even other unix and other versions of
> > > AIX, with caution. I don't have access to one so I can't be more
> > > specific, but when I did multi-platform support, hoo-boy.
>
> > > I'm sure I've seen more info out there on metalink and google for this
> > > sort of problem.
>
> > > jg
> > > --
> > > _at_home.com is bogus.
> > > If at first you don't succeed, throw good money after bad.http://www3.signonsandiego.com/stories/2009/jan/16/1m16ship001056-hop...quoted text -
>
> > > - Show quoted text -
>
> > Evidence is available via the sqlnet trace and listener.log file. If
> > the connects are truely slow then it becomes an issue of finding the
> > mis-configuration that will solve the problem. Your note is a good
> > starting point.
>
> > HTH -- Mark D Powell --
>
> Well, yes, if there is an actual problem like the OP, tracing is the
> way to go. I'm more concerned with the assertion that there are no
> performance hits with local tcp versus bequeath. That would require
> lots of testing and may vary by configuration. If nothing else, it's
> an additional piece of technology in the stack. It may not make any
> difference.
>
> jg
> --
> _at_home.com is bogus.http://singularityhub.com/2009/01/12/a-review-of-the-best-robots-of-2...- Hide quoted text -
>
> - Show quoted text -
Our view is the applications should never run on the same machine as the database for security reasons hence all connections go through the listener. Only DBA's can connect locally on the db server.
There is extra overhead when you go via TCP over bequeath but it is small. You can measure the connect time via trace. Since we have an production application server and a separate production database server all connections to the database have to go through the listener. We run test the same way even though the some of the applications and databases sit on the same machine becuase we discovered it is possible to have a protocol specific error occur. So we test like production runs: TCP connects all the way.
Testing an assertion pretty much never hurts. It might even save you from making a mistake.
- Mark D Powell --