Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

how assign more resurce to oracle istance

SOLVED
Go to solution
Paolo Gilli
Frequent Advisor

how assign more resurce to oracle istance

I have an hp-ux 9000 N/4000 where running 5 oracle instances (A,B,C,D,E).
I need to improve priority/system resource for instance B cause big update activity.
How can I do improve priority/system resource for instance B?
If i need use -renice- command, which oracle process I need to -renice-?
Thankyou
10 REPLIES
T G Manikandan
Honored Contributor

Re: how assign more resurce to oracle istance

There is a product called Process Resource Manager(PRM).Just install the eval copy of that software and try increasing the resource percentage for the Oracle application.
This can be quite useful for your need.

http://software.hp.com/portal/swdepot/displayProductInfo.do?productNumber=B3835DATRY
Graham Cameron_1
Honored Contributor

Re: how assign more resurce to oracle istance

Paolo

There are number of things you can do to give more resource to instance B, but renice is going to be difficult, because there are many processes associated with each instance, and some of them come and go.
You would have to run "ps -ef|grep " and renice each process you find.

Other ways - shut down all other instances.

Assuming you can't do this, next choice is to allocate more shared memory to instance B, by tweaking init.ora parameters. How you do this depends on what version of oracle you have, and you don't say. I've tried to look up some links for you but MetaLink is down this morning. You can also enable parallel operations on the database, which, can, depending on the SQL and the database structure (partitioning etc), spread the load over more than one processor.

Finally, if you are running hpux11i on a partioned server, you can run instance B in a separate LPAR, and move procesors (and I think memory) in and out as required.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Jean-Luc Oudart
Honored Contributor
Solution

Re: how assign more resurce to oracle istance

Paolo,

Presumably you want to improve priority on B because of performance problem on this specific instance.
Before going the PRM route investigate what is "wrong" with B performance (Giving more resources to B may impact performance on A,C,D,E).
have you checked memory utilisation for each instance ?
Oracleversion ?
Statspack report ?
how much physical memory on your server ?
swapinfo -tam ?

Regards,
Jean-Luc

fiat lux
Paolo Gilli
Frequent Advisor

Re: how assign more resurce to oracle istance

Manikandan,Jean-Luc, I need to do this operation one shot, only in this particular situation where
users of instance BBB need to finish update operation as soon a possible.
so, as Graham said, using ps command I have:

ps -aelf|grep -e BBB|grep -e ora_
1 S oracle 9727 1 0 156 20 4a1da900 4642 5faf187c 07:07:00 ? 0:06 ora_qmn0_BBB
1 S oracle 9745 1 0 156 20 4e911700 15586 5faf1894 07:07:01 ? 28:12 ora_p001_BBB
1 R oracle 9779 1 255 241 20 4a13c200 15834 - 07:07:01 ? 14:48 ora_p005_BBB
1 R oracle 9755 1 103 203 20 4e8f1700 15582 - 07:07:01 ? 35:10 ora_p002_BBB
1 R oracle 9770 1 147 214 20 4893f400 15834 - 07:07:01 ? 14:18 ora_p004_BBB
1 S oracle 9687 1 51 156 20 54570f00 4550 5faf184c 07:07:00 ? 2:39 ora_lgwr_BBB
1 S oracle 9695 1 0 156 20 410d9c00 4550 5faf1854 07:07:00 ? 1:15 ora_ckpt_BBB
1 S oracle 9671 1 73 154 20 53051200 4994 8f3600 07:06:59 ? 1:45 ora_dbw0_BBB
1 S oracle 9722 1 0 156 20 49603c00 7362 5faf1874 07:07:00 ? 0:09 ora_snp1_BBB
1 S oracle 9716 1 0 156 20 515b1000 6158 5faf1864 07:07:00 ? 0:05 ora_snp0_BBB
1 S oracle 9663 1 0 156 20 4b7b6600 4762 5faf1834 07:06:59 ? 0:01 ora_pmon_BBB
1 S oracle 9793 1 0 156 20 4a2ccc00 15614 5faf18c4 07:07:01 ? 16:46 ora_p007_BBB
1 S oracle 9709 1 0 156 20 54e50900 4534 5faf186c 07:07:00 ? 0:00 ora_reco_BBB
1 S oracle 9786 1 0 156 20 51330400 15614 5faf18bc 07:07:01 ? 17:28 ora_p006_BBB
1 S oracle 9701 1 0 156 20 4f8d1900 4682 5faf185c 07:07:00 ? 0:08 ora_smon_BBB
1 S oracle 9763 1 255 156 20 52091500 15582 5faf18a4 07:07:01 ? 28:41 ora_p003_BBB
1 S oracle 9679 1 78 154 20 493f0f00 4986 8f3600 07:06:59 ? 1:45 ora_dbw1_BBB
1 S oracle 9737 1 0 156 20 4d892400 15598 5faf188c 07:07:00 ? 28:38 ora_p000_BBB

excluding parallel process (ora_pxxx_BBB), I have the process I need to renice. It is right?
Jean-Luc Oudart
Honored Contributor

Re: how assign more resurce to oracle istance

You can run s specific script (as root) to renice all these processes
ps -aelf|grep -e BBB|grep -e ora_ | awk '
{print "renice -n -10 -p ",$4;}' > doit

then as root
sh ./doit

Regards,
Jean-Luc
fiat lux
Hein van den Heuvel
Honored Contributor

Re: how assign more resurce to oracle istance


Looks like all you have to do for this one-time occasion it to renice all 'BBB' processes in sight. Something like:

ps -ef| awk '/BBB/{system("renice -n -10 -p " $2)'/

If you can restart that instance you may want to change it's initBBB.ora file to include: HPUX_SCHED_NOAGE = 178
(or remove that from A,C,D,E :-).

warning.. the processes yo show are 'just' backuground and empty shells. The worker slaves from the appliaction are not there yet. They will show up as 'oracleBBB'

For more sofisticated control you may want to ask Oracle who is connected with a script similar to the one below and parse the result, or 'report' with an executable nice command. (Select 'renice ...' || spid ...)


echo "@process" | sqlplus -s "/ as sysdba"

where process.sql is for example:

column id format 999;
column Local format 99999;
column Remote format 99999;

column Node format a9;
column Local_Program format a20;
column Remote_Program format a24;
set pages 9999;

select pid "Id", spid "Local", process "Remote",
s.machine "Node", SUBSTR(p.program,1,20) "Local Program",
SUBSTR(s.program,1,24) "Remote Program"
from v$process p, v$session s
where addr = paddr
order by machine, spid
/

have fun,

Hein.
Brian Crabtree
Honored Contributor

Re: how assign more resurce to oracle istance

Paolo,

For the most part, Oracle will only be using CPU when it is parsing a statement. The major resource for Oracle is in I/O. I would recommend checking your RAID array and see if the utilization is going up on there. If so, checking your RAID configuation, or adding additional I/O channels would help with performance.

If you are seeing a large amount of CPU processing for a query, I would tune the query first. Explain plan is normally best for this, and will outline what is being done how, and could give you options on what to change to increase performance. Past that, hints can also help out if you know what you want it to do.

I would also check your application out. If you tend to have a large number of full table scans, then adding parallel query option might be good, and would help in gathering data off of the disks, which could help. Increasing your db_block_buffers will help with index performance (depending on how the update is running).

If none of this sounds like it will help, post back your exact requirements and what your update job is doing.

Thanks,

Brian
Hein van den Heuvel
Honored Contributor

Re: how assign more resurce to oracle istance

Brian> For the most part, Oracle will only be using CPU when it is parsing a statement. The major resource for Oracle is in I/O.

I beg to differ. I'm sure that is a valid statement for many applications, but it is entirely different for others. Your milage may vary!

I spend most of my oracle time with benchmarks, which admittedly gives me a distored view of the world, but our tuning goal is 100% cpu, none of that in parsing.
We most often get there!

Specifically for UPDATES the statements tend to fixed, not free format/ad-hoc/generated sql. So as long as you use 'bind variables' or set the systems init/session params for 'cursor_sharing=similar' you should see no hard parse time. (with enough pool space, cursor_space_for_time).

On the IO side, a highly optimized application should only wait for redo log write IOs. The bulk of the reads should come from cache (large SGA) and the writes from dirty buffers happens in the background anyway.

So you end up with a bunch of buffer_gets and consistent_reads eating up your CPU!

Brian> I would recommend checking your RAID array and see if the utilization is going up on there.

Agreed. Many real life application are IO bound. But still.... if you give your favorite instance more CPU priority, then it will become the first to queue up its IOs and moreover it will be woken up right-away when a blockign read-io is satisfied. It can than turn around and ask for more.

Brian> If none of this sounds like it will help, post back your exact requirements and what your update job is doing.

Right. More detailed questions beget better answers :-).

Cheers,
Hein.
Paolo Gilli
Frequent Advisor

Re: how assign more resurce to oracle istance

Hi,
This forum is becoming a guru forum! :-)).
You give me very interesting information.
Hp and oracle support, as Brain said, told me to use 'renice' as last step after check oracle procedure and I/O distribution.
Using 'renice' only could not resolve the problem.
So I will check our patner application.

Thanks to all
Paolo
Leon Allen
Regular Advisor

Re: how assign more resurce to oracle istance

A human can not do a better job of allocating cpu resource than the OS itself. The OS rapidly and dynamically allocated CPU according to what needs it.

Why allocate cpu to a process thats doing lots of i/o?

Big update job? Definately lots of i/o going on there. Best leave the cpu alone, and concentrate on sql tuning / index checking or creation; file placement; even turn archive logging off if your that desperate.
Time's fun when your having flys (ancient frog saying)