Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    2

    Unanswered: Performance of multiple selects

    The select query does not place a lock on the underlying table.
    Does a select query impact the performance of anohter selct query running on teh same base table?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does a select query impact the performance of anohter selct query running on teh same base table?
    no
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2011
    Posts
    2
    We have to create an SQL view ona tabke that has a high volume of data( ~12 million rows).An ETL job will query data from thsi veiw.Some people feel this view will impact the online users of the appicaiton and other porcesses that update the same base table on which this view is based. the db is Oracle.Is this a fair apprehension?
    Or does queryig data form the SQL view have no impact on other rocessing on the base table

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    neerens,

    whether one query impacts the performance of another depends a lot on your datafile distribution, the disk architecture (striping or not), whether you run partitioning or not and - last but not least - the version of Oracle (starting with 11.2 there is a "result cache" that can be utilized).

    If i.e. all your table data resides on one physical disk and you are doing a lot of full table scans (as ETL tools tend to do) and each of those requests data from the same table (which -assuming the above- would be on the same physical disk), you might get serious impact.

    I'd recommend to check the utilization and response characteristics of your disks.

    On Linux i.e. you might want to monitor the behavior of your disk(s) with
    Code:
    iostat -m -x <interval in seconds>
    If you see the disk utilization nearing 100% and/or the average wait time increasing when starting your additional queries, you might have a problem (which can get quite serious)

    Also make sure that DBMS_STATS.GATHER_SYSTEM_STATS has been executed (in both, the "noworkload" and the "interval" mode).
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •