Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: SQL concurrency and parallelism

    I am using Oracle 9i on Windows 2000 Server, previously I have 2 instances, for reporting, which run a number of very complex SQLs, and the response time is acceptable.

    1 week before, I created 3-4 more instances (i.e. totally 5-6 instance) and they are also doing reporting, when all these 6 instance are running reporting, the response time is very slow, at least 10-20 times slower than before, but the CPU utilization is only 20-30%.

    Since the CPU is not fully utilized, do you guy know what init. parameter is governing the CPU concurrency and parallelism? and how to monitor these parameter so that I can have an idea the appropriate value I should set?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Since the CPU is not fully utilized, do you guy know what init. parameter is governing the CPU concurrency and parallelism?
    Ready, Fire, AIM!
    My initial reaction is that I suspect the system is memory starved & swapping like crazy.
    What is the sum total of all the SGA sizes & how does that compare to total RAM?
    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
    Dec 2001
    Posts
    80
    Quote Originally Posted by anacedent
    >Since the CPU is not fully utilized, do you guy know what init. parameter is governing the CPU concurrency and parallelism?
    Ready, Fire, AIM!
    My initial reaction is that I suspect the system is memory starved & swapping like crazy.
    What is the sum total of all the SGA sizes & how does that compare to total RAM?
    anacedent,
    thx for you reply, the sga_max_size is 139533192, around 130M, the memory size of this server is 2G, I saw from the task manager that the memory usage is around 520M only.
    Last edited by matthewlau; 11-22-05 at 23:35.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could be IO bound instead of CPU bound. With 6 instances all presumably fighting over the same IO bandwidth it will get slow. I would say unless youve got a lot of CPU and IO bandwidth 6 instances is too many.

    Alan

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >the sga_max_size is 139533192, around 130M
    130*SIX is about 2GB (in round numbers)
    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.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Is that round even numbers

    Anyway anacedents point could be correct as dont forget PGA is outside the sga. Check the VM Size under the processes tab of task manager (add the VM size column through View | select columns).

    Alan

  7. #7
    Join Date
    Dec 2001
    Posts
    80
    Thx all,
    The memory usage is around 520M only. As I have the information of max. SGA size allowed, I don't have the current size, but I guess all instance doesn't allocate 130M SGA as the VM usage is around 520M only, since the server has 2G memory, it seems memory is not the bottleneck, am I right?

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Are you IO bound as I mentioned earlier? six instances all fighting over your disks can lead to low CPU usage as the processes are simply waiting for data to return. If this is the case you might want to increase your data caches as then they wont need to go the disks so much.

    Alan

Posting Permissions

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