SGA versus sharedpool
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SGA versus sharedpool

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    SGA versus sharedpool

    Hi All,

    I am on 10.2.0.4 and I have a quick question to clear out my doubt. when we use SGA_TARGET, we would let Oracle to manage its own memory so we don't need to specify the size for shared_pool, is it correct?

    1. why from time to time, I have been reading and noticed that sga_target is used and shared_pool is also used and I bit confused.
    2. there is a sql statement that was executed over 5 million times over the period of the batch job was ran, it is used bind variable, I wonder if there is anything esle that we can do to make it run faster.

    Thanks all

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1- If SGA_TARGET is specified then SHARED_POOL_SIZE (among others) will be automatically sized.

    2- Have you traced the query?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2006
    Posts
    176
    thanks and regarding to number 2. I have trace the sql and the plan is very sufficient as I mentioned I used bind variable. the only thing I just want to make sure that it will do soft parse (memory) rather than hard parse. are there any parameters that I can tune?

    Thanks,

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    there is a sql statement that was executed over 5 million times over the period of the batch job was ran, it is used bind variable, I wonder if there is anything esle that we can do to make it run faster.
    If it were me, I would just run the query less often...

    If this query really runs 5 million times, then it needs to be really efficient. You can also create a temporary table with the data you want in a demormalized format, then just loop through the query.

    If you can do something with a single SQL statement then do so, otherwise look at PL/SQL collections and or temporary tables.
    this space intentionally left blank

  5. #5
    Join Date
    Mar 2006
    Posts
    176
    PVAB,

    so SGA_TARGET is specified, then those number that we set for SHARED_POOL_SIZE (AMONG OTHERS) are irrelevant even it was set. In other word, Oracle will ignore those number and manage by itself?

    thanks,

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Yes. If SGA_TARGET is set to anything other than Zero then Oracle will automatically manage SGA memory allocation.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by matthew00 View Post
    so SGA_TARGET is specified, then those number that we set for SHARED_POOL_SIZE (AMONG OTHERS) are irrelevant even it was set. In other word, Oracle will ignore those number and manage by itself?
    If you have any of the automatically tuned SGA components set to a non-zero value, then this will set a minimum value that Oracle will use for that particular component.

Posting Permissions

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



Click Here to Expand Forum to Full Width