Are long running queries affecting database performance?
Hello,
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. Would you consider, for a valid reason, not to perform these queries during the normal operation of the database/application and to perform them on non-peak hours? And if yes, what would be that reason?
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"?
To be specific, the queries are not SELECT .. FOR UPDATE and do not run in Parallel. Also, some of them might be CTAS.
Also, unrelated to the performance related issues, generally speaking, do you allow ad-hoc queries on your production database? Even simple queries such as 'select value from table_a where row_id=1'? Do you have any business or security policies of forbidding ad-hoc queries on a database just because it is a production database?
Thank you for your feedback.
Apostolis
Edited by: Apostolis on Jul 27, 2012 9:25 PM