Re: Expand SGA but performance slow down?

On 21 Aug 1998 12:38:48 GMT, jared_at_pandora.planet.net (Jared Hecker) wrote:

>Expanding the SGA only speeds things up if you are memory-bound. In this
>case it sounds like you were not, and are taking a few extra seconds to
>allocate the extra buffers the first time you run a query.
>
>Try running a large query several times under your original setup and the
>new setup, and you will see the difference.
>
>hth -
>
>Regards,
>jh
>
>Violin (violin.hsiao_at_mail.pouchen.com.tw) wrote:
>: Hello,
>: 2 days ago I changed the init.ora and startup again.
>: In init.ora,I changed the:
>: db_block_buffers = 200 --> 6400
>: shared_pool_size = 3500000 --> 10000000
>: log_buffer = 8192 --> 245760
>: processes = 50 -->100
>
>: After startup database again,the SGA allocated successfully.
>: Database Buffers = 400K --> 12800KBytes
>: Fixed Size=35K --> 31K
>: Redo Buffers=8K --> 240K
>: Variable Size=6887K --> 12526K
>
>: When I do the same SQL for a large amount of rows(about 37,000) for
>: select and insert statement,
>: Before changed SGA,the statement takes 5 seconds to complete.
>: After changed SGA,it takes 17 seconds to complete.
>: Why???
>: Isn't the SGA more large more better?
>: If you know why,please tell me.
>: Please Cc to : violin.hsiao_at_mail.pouchen.com.tw,Thank you :)

Knowing the hardware might help as well. ( My news server missed the first post, so sorry if it was included). Where you really gain on perceived performance from a large SGA is if this then allows you to get a significant amount of your tables into memory, and do not need to retrieve data from disc.

This will only really become aparrent when you've run enough queries to load up the data, so the performance benefits will appear if you run the query more than once ( as Jared suggested), or tie relevant tables into memory.

However, if you are running on an Unix system ( I'm most conversant with HPUX at the moment, but it holds for all that I've used so far), and your tablespaces are on formatted discs, then you could be harming your performance by robbing memory from the IO buffers.

Also, has your table grown while you're doing all of this? If there's a significant amount of deletions on this table as well, it may need reorganising. Try dropping and recreating any indexes that are on it and seeing if that makes any difference.

HTH, and good luck!

Steve Received on Fri Aug 21 1998 - 11:14:14 CDT