AIX

hi,
We have two LPARs, both have same capacity and believe same configuration. ulimit settings for oracle user is unlimited for both LPARs. Installed oracle databases with same configurations on both LPARs, both databases sync every second so volume is same. Both LPARs/databases have identical jobs running at exact time.
We run net-backup process at same time on both LPARs every night. LPAR1 finishes in 30 min but LPAR2 takes 2 hours to complete.
When I observed, i noticed backup processes running on LPAR1 consuming 35 to 40% CPU for every PID. And i noticed backup processes running on LPAR2 consuming only 5 to 10% CPU for every PID.
I'm understanding that backup process in LPAR2 delays because it's not utilizing available CPU, please correct me if 'm wrong? Secondly how we can make or instruct the AIX to utilize more CPUs for the backup processes running on LPAR2?

-Thanks in advance
-Bala

If you are certain that you are using the same disk infrastructure and there is no lower class of IO rate given by the SAN (or any other infrastructure, e.g. different number of paths, fabric port settings etc.) then my first, perhaps wildly inaccurate, guess would be a missing index.

This could lead to more IO and therefore less CPU because there is less CPU work to do, you are doing more waiting for IO.

If the LPAR is otherwise idle, have a look at the output from vmstat 10 10 and see what the Wait column (towards the very end) tells you.

Other things to consider might be lower memory which might be causing paging. From the same output, look at the pages in & out (somewhere in the middle) and that might illustrate that. You can also have a look at lsps -a and if it's a bit heavy that could be an indication of low memory.

Beyond that, you would be looking into the oracle parameter file for variations.


I hope that this helps,
Robin

As Robin says especially have a look for IO.

In general it might be helpful to have nmon running for monitoring. So you can compare lot's of different system parameters from both hosts while holding them side by side.

IO is high, IO could see from database side. I thought LPAR2 IO is high because it does not get sufficient CPU, am I making right statement?

It depends what you name as IO. When you use for example the OS tool iostat you can see how bytes have been read/written but also the number of IOs and the time in percentage, how much has been spent for IO.
I am no Oracle DBA and have no access so I don't know what you see.

Way back we had some SAN storage, that was far from being reached it's max bandwith MB/s wise, but it had so many small IOs, it wasn't able to handle more.

So with just one or two sentences by you describing it, it is hard to guess around what could be the reason.
Since the task is running for a longer time, it is really recommended to monitor also for this time.
Please do youself a favour, download nmon (I think it is actually installed already with the OS these days) and have it run in the background and get this performance data on both boxes.
It is really easy to setup and when you have the data, use lpar2rrd and paint some pictures so you can compare those graphs to get a clue.

Just google for nmon ibm and you will find also the documentation with examples.

I would still say to check that all the indices you expect exist and are valid on the server with the problem, something like:-

Code:

SELECT OWNER,INDEX_NAME,STATUS
FROM DBA_INDEXES
WHERE STATUS NOT LIKE 'VALID'
ORDER BY OWNER,INDEX_NAME ;

If it's not there, you can use DBA_OBJECTS instead and give it OBJECT_TYPE of INDEX

You could also recalculate the statistics that Oracle uses to pick how it uses the data/indices, with something like a REBUILD INDEX or COMPUTE STATISTICS etc.
Have you got an explain plan from the database to show you what it's doing when it is running a long transaction.

My favoured explanation is that it is either missing an index or not using one, consequently you end up reading the whole table (lots of IO) and performing the equivalent of a massive if in a loop to get the data you want. The explain plan is key. If you see something about a full table scan on a huge table, that is a very good bet.

Try to get a comparable one on the good side and see if there is a difference.

It's been ages since I had this sort of thing so I can't remember how you generate it. I think we might even have used a client tool (Toad or Oracle Discoverer perhaps?) to give us the information. There will be a DBA command line way too I'm sure. From memory (no pun intended) it might be to do with the view V$SQL_PLAN, but I no longer have access to systems like that any more.


I hope that this helps,
Robin

This is a RMAN incremental backup. Why does it uses indexes for copying the file? I would agree if this is a long running SQL or any DML statements.