0

So, my dba informed me yesterday that ArcGIS Server made 40+ million requests to Oracle in a 20 minute period. That would be approx. 35k requests per second. I started laughing, and said we have the greatest performing ArcGIS Server in ArcGIS Server history - especially since its a single machine configuration.

I have no idea how he determined ArcGIS server to be making this volume of requests. Before I go do battle with him, is there some type of quick & easy monitoring tool I can run on the ArcGIS to gauge how many requests its throwing at Oracle.

  • "Quick and easy" doesn't often apply to database monitoring tools. I really don't see any reason to "battle" about anything. It's not best practice to place ArcGIS Server on the same Windows host as the database, but if the system performance is adequate, it's unclear why you would need to have any discussion at all. – Vince Jun 10 '15 at 13:09
  • 1
    Wireshark will log all traffic - expect large log files... port 66 wiki.wireshark.org/PortReference/Ports0to99 – Mapperz Jun 10 '15 at 14:01
4

The DBA is probably using Oracle's standard administration tools and console (Enterprise Manager / Grid Control). That console will highlight the high consumers in terms of number of SQL statements executed, rows fetched, CPU or IO consumption, etc. It also highlights the top SQL statements in the same way.

To find out the details of what statements get executed by a process, the DBA can use the SQL trace mechanism. That will log each and ever statement with all execution stages, resource consumption, waits, bind variables etc. That can lead to large files, that can then be analyzed and summarized using command line or gui tools.

Can you ask your DBA to show you the information that substantiates his/her claim ?

40 million requests in 20 minutes by a single process seems like a lot, but is not surprising. It all depends on what the DBA means by a "request". Most likely it is a SQL statement execution, or possible a SQL statement execution stage (parse/execute/fetch).

For you, a request means something different. From an end-user perspective, a pan operation on a web map is a request. However, that map has multiple layers (say 10 layers). That then translates to at least one SQL statement per layer. If you are dealing with ST_GEOMETRY types, then that SQL statement will trigger the execution of many internal SQL statements ("recursive SQL" in Oracle parlance). The traces separate those and the summary report totals them separately. Then each SQL statement execution has three phases: a parse phase, an execute phase and one or more fetch phases, each of them meaning a network round-trip between the "client" (= the ArcGIS server) and the "server" (the database). A well optimized flow may only need an execute step: the parse step is reused from a previous execution of the same SQL statement, and the execute step fetches all the data returned by the statement, so no additional fetch step is needed. Altogether, a single pan operation on a web map by a user could translate to 100s of individual requests (parse/execute/fetch) reaching the database.

I have three questions:

  1. Is this something that just happened once ? Or is it happening all the time, and the DBA just noticed ?
  2. Are there any performance slowdowns observed by the end-users, i.e. are end-users complaining ?
  3. Is there any system overload that triggered the DBA's investigations, i.e. is the system admin complaining ?
1

you can use Oracle Real-Time SQL Monitoring to look for queries which are going to yours db server, or use ArcGIS System Monitor but you have to have at least AGS 10.1 or greater

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.