ssm_threshold tuning

[lawrenzo_1]

Hello,

There are two systems running oracle - both are the same architecture except one has more processing power across the 2 cpu's.

The user is running an oracle query on one system call samosa and comparing the run times agains a system called cumin - cumin being the system with greater processing power.

The query runs for approx 6 secs on samosa and when rerun it then takes around 1.2 secs ( apparently to do with oracle caching )

when the query is run on cumin the query runs for 4 secs however when rerun it takes around 4 secs every time.

The cumin system has recently been built to company standards and should be performing the same if not better than samosa.

After some digging and reading I checked the sysconfig parametres and found the only difference was the ssm_threshold param is set to 0 on samosa (the older system) and to 8388608.

can someone advise me on these settings and if I change this setting is there any other setting that needs to be changed?

many thanks

Chris.

[Hein van den Heuvel · Accepted solution]

Chris, for this and similar questions please read:

http://h71028.www7.hp.com/ERC/downloads/4AA0-9949ENW.pdf

Check out the section:
"Shared Memory Allocation and Granularity Hints"

Here is a quote about ssm_threshold:
"This tunable parameter controls which type of shared memory implementation is used. The default in Tru64 UNIX V5.x is to use Segmented Shared Memory (SSM) type shared memory. The only two reasons to change this parameter are the use of statically allocated granularity hints memory or the use of an application on the same system that prevents the use of SSM type shared memory (for example, Sybase); in either case ssm_threshold should be set to 0. Note that not using SSM type shared memory while not using granularity hints memory (which includes those cases where shared memory allocations exceed the amount of memory reserved for granularity hints) can have a severe
negative performance impact, especially when many processes attach and detach from large shared memory segments.
HP recommends leaving the value of ssm_threshold at its default value of 8388608, unless statically allocated granularity hints memory (rad_gh_regions or gh_chunks) is used; in which case ssm_threshold should be set to 0."

I like GH, but it is tedious to setup
Bigpages is the way to go.

Back to your observations...

IF this problem has something to do with ssm_threshold, which I doubt, then that would strictly show as CPU time, and would otherwise be totaly invisible to oracle.
So is the system cpu bound during those queries? system or user?

You must compare the Oracle stats to help you guide where to look. Either global, or better still for the query alone.
Just statspack around the query should be enough, qurie trace + execution plan is better. Are the number of consistent gets the same? parse stats? io stats?

Is the SGA (buffers and pool) set up similar?

If according to oracle as many IOs are done, then look back at Tru64. Is the io rate similar? ubc settings? Is there a similar amount of memory availabel on both systems?

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Kapil Jha]

Hi Lawrenzo,
I do not think it is smthing related to ssm.
Can you please post output for the query in both machines using.
sql>set autot on
sql> your query

Please send output of both the machine.
BR,
Kapil

[lawrenzo_1]

ok thanks guys.

I will take a closer look into the GH settings and ensure both systems are the same