Apostolos Giannakidis wrote:
A general question regarding the performance of the Oracle database.
We have a production Oracle 11gR2 OTLP database that holds customer related data for a CRM application. The database has several billion records in total. The hardware that hosts the database instance is a 8-core Xeon CPU with 16GB RAM and is dedicated to this instance.
On normal operation, this database has 3-4 concurrent sessions -at most- that perform only queries and DML statements generated from the CRM application. Each statement lasts some milliseconds (no long running operations).
On a database like this, would you (as a developer) run ad-hoc queries for reporting purposes at the same time of the normal operation of the database/application (meaning that there would be normal CRM users connected at that moment)? These ad-hoc queries would often be long running, lasting from several minutes to a couple of hours.
My question is about the possible performance "penalty" of the overall database operation and if that ad-hoc, long running, queries could affect the performance of the CRM application in any way. Also, could a long running query be considered as "resource intensive"?
YES, every query impacts performance; some more than others