How can I measure Oracle CPU utilization and IO performance? I'm trying to establish the best connection pool size and app thread pool size for a specific spatial query in Oracle SQL 11g. What I'm looking for is to run X queries in parallel with Y connection using Z thread and track the average/highest CPU/IO during this run. Eventually I want determine which configuration is the most cost effective and understand how changing of the query will affects its cost and throughput in a given HW.
Any ideas are welcome :)
I assume that by 11g, you mean 11g release 2 (11.2.0.4). Anything earlier is no longer supported.
To monitor performance, you can just use the monitoring tools provided with Oracle: the standard Oracle Enterprise Manager console will give you a fairly comprehensive view of what happens. See https://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_realtime.htm#TDPPT033
Then you have the tools like AWR and ADDM. See the complete guide at https://docs.oracle.com/cd/E11882_01/server.112/e10822/toc.htm for details on how to use them.
To drive your test, I suggest you take a look at Swingbench: http://dominicgiles.com/swingbench.html