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:
- Is this something that just happened once ? Or is it happening all the time, and the DBA just noticed ?
- Are there any performance slowdowns observed by the end-users, i.e. are end-users complaining ?
- Is there any system overload that triggered the DBA's investigations, i.e. is the system admin complaining ?