This discussion is archived
7 Replies Latest reply: Jul 27, 2012 12:12 PM by Apostolis RSS

Are long running queries affecting database performance?

Apostolis Newbie
Currently Being Moderated
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
  • 1. Re: Are long running queries affecting database performance?
    sb92075 Guru
    Currently Being Moderated
    Apostolos Giannakidis wrote:
    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.

    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
  • 2. Re: Are long running queries affecting database performance?
    Apostolis Newbie
    Currently Being Moderated
    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?

    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 indexed_table where row_id=1'? Do you have any business or security policies for forbidding ad-hoc queries on a database just because it is a production database?

    Thank you,
    Apostolis
  • 3. Re: Are long running queries affecting database performance?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    >
    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.
    >

    It is not a good practice to have developers playing in production DB.

    >
    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 there is possible performance "penalty" of the overall database performance and operation.
    Long running queries will cause Oracle to keep Undo longer and it will get bigger, and may require large temp space for sorts. It may cause CRM sessions to get exceptions if there is not enough these resources for them.

    If development on prod DB is unavoidable you can set limits on dev account using resource profiles.
  • 4. Re: Are long running queries affecting database performance?
    Apostolis Newbie
    Currently Being Moderated
    user11181920 wrote:
    It is not a good practice to have developers playing in production DB.
    The queries are unavoidable and have to be performed.
    They are not part of an experiment or a game of the developers.

    Thank you
  • 5. Re: Are long running queries affecting database performance?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Generally speaking, unless you have a real-time or near real-time reporting replica where queries can be run during the day, it's pretty hard to justify a blanket ban on running ad-hoc SQL during the day. Telling users that in order to do the data analysis they want to do that they have to wait until after their regular shift has ended to start running queries is generally not acceptable.

    On the other hand, it is entirely reasonable on a case-by-case basis to determine whether queries really need to be ad-hoc SQL or whether they can be incorporated into some reporting tool that will run the report automatically at night (of course, the answer to that depends on whether the users actually need current data in the report or whether they're happy with a report from last night). It is entirely reasonable if ad-hoc queries are impacting the performance of the OLTP application to look into creating a reporting database that runs on a different server from the OLTP application. It is entirely reasonable to look into things like Resource Manager to prioritize OLTP workload over reporting workload. And it is potentially reasonable to, on a case-by-case basis ask users to stop running ad-hoc queries (either generally or specific queries) during particular times. If the OLTP application is under heavy load because it is doing some month-end processing, you may want to temporarily stop ad-hoc queries or ask people not to run queries that are known to be particularly resource intensive.

    Justin
  • 6. Re: Are long running queries affecting database performance?
    jgarry Guru
    Currently Being Moderated
    One bad query can ruin your online users whole day.

    I echo the sentiment about not letting developers into production.

    What I see on a mixed system that has a code generator that can create some ad hoc queries is, if only one is a hog, it doesn't affect the normal users a lot, but two or more can make a serious noticeable difference. YMMV. But if you are doing billion row full table scans, on purpose or otherwise, I'd say prima facie, put those on another machine or off hours. Watch for core-hogging and I/O-hogging, depending, though I would expect the latter on an 8-core with the attendant crappy I/O interrupts. But then again, Oracle can be surprisingly persistent about doing things in memory, since after all, everyone knows that memory is faster. Right? (Answer: Wrong! It Depends!)

    That said, I do ad hoc anyways. But I watch what happens and have to deal with complaints. Eventually I'll get another machine, and a lot of the DSS stuff is already offloaded to other machines, with the occasional odbc or dblink to update, or whatever. Some users have been trained to do things in batch, they're happy to have their report ready for them in the morning rather than staring at an hourglass for hours.
  • 7. Re: Are long running queries affecting database performance?
    Apostolis Newbie
    Currently Being Moderated
    You've all been very helpful.

    Thank you all for your answers!

    Regards,
    Apostolis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points