Chatr Oracle on hp-ux 11i v3

[thiagesh]

Hi All,

Below are the output from chatr oracle cmd. I just want to know what is that 5 text, 6 data under Segments header,

        segments:
            index type     address      flags size
                5 text 4000000000000000 z-r-c  64M
                6 data 8000000100000000 ---m-    L (largest possible)

Thanks in Advance.

[Steven E. Protter]

Shalom,

They both look like data segments to me. The first one is limited to 64 MB. The second is as its states as big as possible.

SEP

[Venkatesh BL]

It means that the TEXT pages of this program would use 64M pagesize and DATA pages would use the largest possible pagesize. Note that these both serve as HINTS to the kernel while allocating pages during pagein.

[thiagesh]

Thanks Steven and Venkatesh.

But, on prod I'm seeing 8 text and 9 data. So was curious to know about that nos.

Actually I'm in the process of reducing oracle processes (mostly user processes), which are consuming more than 130 MB for each connection making virtual memory too low and as a result the system hauls.

My vmstat free memory shows sometimes even as low as 100.

I have oracle 9i R2 on hp-ux 11i v3. Both are 64 bit. My uat db also has the same oracle version but the unix box version is HP-UX 9000. In this box, all the oracle process are as low as less than 50 MB.

So I would like to know what are the kernel parameters that I can tune to bring down to 50 MB of oracle processes.

If I changee vps_chatr_ceiling to 64 M, will it solve my problem? Currently the value is 1GB.

Thanks in advance.

[Dennis Handly]

>on prod I'm seeing 8 text and 9 data. So was curious to know about that nos.

The numbers aren't so important as the names. I think "elfdump -S -o exec" will list the segments.

>making virtual memory too low and as a result the system hauls.

You should make more. What does "swapinfo -tam" show?

>If I change vps_chatr_ceiling to 64 M, will it solve my problem? Currently the value is 1GB.

It depends. You obviously want large page for the shared global area. But you don't want large pages for the process data area if you don't have multiple Gbs.

You may be trading off performance for something that doesn't work at all (current).

[thiagesh]

Hi Dennis,

Thanks. Here are my swapinfo result, but it seems to be ok.

Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 8192 0 8192 0% 0 - 1 /dev/vg00/lvol2
reserve - 1727 -1727
memory 3877 1163 2714 30%
total 12069 2890 9179 24% - 0 -

There are no pagination/swapping problems, but still the vmstat sometimes shows 100 KB and the system performance is very slow at that time. So we stop certain batch jobs at that time, then will be ok.

our total RAM is 12 GB. 2 oracle isntances are running currently. I suggested to move one instance to a new box.

Is there any other suggestions and is it possible that the oracle memory processes can be controlled in unix like how we do in windows using orastack to reduce the stack memory.

Appreciate ur quick repsonses.

Thanks.

[Hein van den Heuvel]

>> Actually I'm in the process of reducing oracle processes (mostly user processes),

So why don't you start with mentioning that?

>> If I changee vps_chatr_ceiling to 64 M, will it solve my problem?

NO. The program attributes only define HOW memory will be mapped, not how much. This is of course minor effect on memory ( rounding ) but not enough to make a significant impact.


>> So I would like to know what are the kernel parameters that I can tune to bring down to 50 MB of oracle processes.

So tell Oracle to use less, using Oracle knobs. Work with your DBA, this is NOT an SA exercise it is a DBA/performance issue.

With Oracle 9i it introduced a new Oracle parameter called pga_aggregate_target. When the pga_aggregate_target parameter is set and you are using dedicated Oracle connections, Oracle9i will ignore all of the 'good old' PGA parameters in the Oracle file, including sort_area_size, hash_area_size and sort_area_retained_size.

For desperate cases you may also look into shared connections (MTS)


>> in windows using orastack to reduce the stack memory.

Not applicable. In Windows there is this one-process for each oracle instance and each thread needs a pre-allocated stack. On Unix each (shared) connection is a process and the stack will grow as needed. No more, no less.

So far you did not mention the oracle SGA size. Conceivably that is over sized. You want to work with the DBA and STATSPACK (or AWR) to see whether trimming down the SGA has an acceptable performance impact.

>> I suggested to move one instance to a new box.

Sure, that'll help. Or get more memory on the current box. But really either step should only be done after fully understanding the resources needs, which, as I'll mentio for a final time, only the DBA and other Oracle folks can heklp you determine. There is very little value a system administrator can add, other then 'getting out of the way' (shmmax, dbc_min/max, mount direct, ...).

Hope this helps some,
Regards,

Hein van den Heuvel ( at gmail dot com )
HvdH Performance Consulting

[thiagesh]

Thanks Hein.

Yes, Rgd pga_aggregate_target, I do know that this accounts for user process memory. The current value for the above param is optimal.

Yes, the database will gain memory usage by using shared server architecture instead of dedicated.

>>in windows using orastack to reduce the stack memory.

>>Not applicable. In Windows there is this one-process for each oracle instance and each thread needs a pre-allocated stack.

Why not applicable? Oracle supplies the ORASTACK utility to allow customers to modify the default
stack size of a thread/session when created in the Oracle executable. When
ORASTACK is run against specific executables it alters the part of the binary
header that defines the default stack size used by the create thread API. It
is not necessary to change the default number of pages committed by the thread
because these will be allocated as required from the stack. By reducing the
stack of every session created in the Oracle executable, it is possible to
achieve a larger user population. In a system with a 1000 users reducing the
stack from 1Mb to 500K would release 500Mb of the address space for other
allocations or more users.

Fine ... I just referred orastack utility, just to know any thing similar to that in unix, b'z I so far worked as a dba in windows platform and not in unix.

Also took statspack report and to me sga is properly configured. My only issue is, b'z of two instances running in the same box, the memory is more concerned.

Any how, finally my mgmt aggreed to move one of the instance to a different box.

Thanks hein.

[thiagesh]

Got some info in this thread which really helped me in concluding the issue.

[Dennis Handly]

>Here are my swapinfo result, but it seems to be ok.
>total 12069 2890 9179 24%
>the system performance is very slow at that time.

Did you do this when virtual memory was too low? It shouldn't be that slow with memory only 30% used.