Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Question Unanswered: 11g R2, increasing SGA/PGA & job_queue_processes make Oracle R12 instance damn slow

    Hi guys

    We've a TEST instance (R12 12.0.6, IBM Server, 2 processors and 20GB memory) running Oracle linux 6 64-Bit. We regularly clone the Production instance to this TEST instance and as recommended by our DBA, change the SGA/PGA parameters manually like 4GB for SGA & 2GB for PGA. Job queue processes were always set as 10 prior the instance given to end users. Starting from the landing page to opening Oracle forms, this instance lagged like hell always (especially after few hours being idle)
    However, last week we needed an urgent clone and without manually changing the SGA/PGA/job_queue_processes we went online to address the situation. To our utter surprise, we realized the landing page and subsequent activities were instantaneous compared to our previous experience with the TEST environment.
    While checking, we have noticed that the cloning process was not creating any SPfile, and both SGA/PGA were set to start with 1G followed by job_queue_processes set as 2

    We were able to reproduce the sluggishness with instance by creating SPfile and changing the memory parameters, forcing us to rollback to the initial setups. What must be contributing towards the slowness while using more SGA/PGA in our case?

    Any thoughts?

    If it is a door, It will open.

  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    do not manually set the PGA or SGA. Let the database handle it. In version 12, it will take what it needs. While I used to have to set those values back in oracle 7, I have never tried to manually administer the memory allocation sin any of the newer oracle databases.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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