Re: Login Slow on One Database using ArcGIS

On 01/03/2018 12:16 PM, Sallie Cottingham wrote:
>
> I have a server that has 4 databases on it and the user is reporting
> that logging in to one specific database is extremely slow when using
> VPN, but that all of the others are fine.
>
> What I have found out (and confirmed with the user) is that logging
> into the DB thru SQL*Plus or TOAD has no issues.  The problem occurs
> when they are logging into the DB thru their application which happens
> to be ArcGIS. This is a standalone DB  running 11.2.0.4.
>
> Our Infrastructure Architect is telling me that the server itself is
> almost maxed out on CPU and so he has added RAM to it.  (It is a VM
> machine)   He is telling me that I am going to need to increase the
> SGA on the DB to solve the problem.
>
> Before I go there I wanted to check with this group to provide any
> ideas on what I can look for to help pinpoint the problem.   My
> tendency is to think that it is a network problem or some problem with
> the ArcGIS setup since the problem only occurs when using the
> application, not when access is thru SQL*Plus or TOAD.  Also, the
> other 3 databases on this server are also running ArcGIS with no
> issues.   I’m just not sure where to begin looking and would
> appreciate any help!
>
> *Sallie Cottingham*
>
> Database Administrator
>
> Division of Technology Solutions
>
> Comptroller of the Treasury
>
> 425 Fifth Avenue North
>
> Nashville, TN 37243-34001
>
> _Sallie.Cottingham_at_cot.tn.gov <mailto:Sallie.Cottingham_at_cot.tn.gov>_
> 615-401-7962
>
> */The Comptroller’s Nashville office is moving to a new location. Our
> office’s telephone numbers, fax/**/numbers, and e-mail addresses will
> not change. However, effective December 18, 2017, all mail should be
> sent to the following address:/*
>
> *//*
>
> */Tennessee Comptroller of the Treasury/*
>
> */Technology Solutions/*
>
> */Cordell Hull Building/*
>
> */425 Fifth Avenue North/*
>
> */Nashville, TN 37243-3400/*
>
Well, I am not at all sure what ArcGIS does, but I would turn on SQL trace for the user using DBMS_MONITOR. My guess is that ArcGIS is trying to perform some SQL while connecting, maybe from a log-in trigger. Did you check the existence of login triggers? If there aren't any triggers, what do you mean by login? Are you looking to Oracle to confirm that ArcGIS has logged in or are you waiting for ArcGIS to tell you that it is ready. If, as I suspect, the latter is the case then ArcGIS maybe executing SQL commands as a part of the login process. What you need to do is to find out what commands are that ArcGIS is executing and see whether there is anything you can do to speed them up. From what I've seen with some other products, ArcGIS is probably querying V$VERSION, V$DATABASE and V$PARAMETERS and stuff like that. Maybe gathering dictionary statistics would help.

-- 
Mladen Gogala
Database Consultant
http://mgogala.freehostia.com


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