Operating System - HP-UX
1748186 Members
4260 Online
108759 Solutions
New Discussion юеВ

Re: Poor Oracle Performance

 
Yogeeraj_1
Honored Contributor

Re: Poor Oracle Performance

hi,

at the Oracle Database level, you SHOULD run statspack report to be able to know what is really going on.

If you want to connect as the oracle user, it will be quite easy since you said you have the root account password. do " su - "

then you can check if statspack is already installed by:
1. login to oracle
sqlplus /nolog
connect / as sysdba

2. check if the statspack tables are installed. Typically, table names start with "STAT":
select object_name from dba_objects
where object_name like 'STATS%';

3. If installed then run:
exec statspack.snap

exec statspack.snap

4. Generate the report by running:
@?/rdbms/admin/spreport
- choose the snapshot numbers (begin_snap and end)
- save the report file to disk.

5. Analyze reports for low load period v/s high load period.


Good luck.
If you need any further guidances, please let us know

regards
Yogeeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Poor Oracle Performance


>>> 4) Oracle: I'm not an Oracle DBA so I dont have access to SQLplus.. is there a way to determine SGA, statspack, version, etc from the root account?

There is your biggest problem.
You can NOT solve this in isolation.
This is NOT going to be a system problem.
Sure, optimal system config will 'ease the pain', but the pain is defined by Oracle.

Your co-operation with the DBA should be such that SQLplus access itself becomes irrelevant.

The really bad IO times are for very low access counts. Ignore those.

Your IO load is reasonably balanced.
The top 10% of the disk, handle 30% of the IO.
The bottom 30% of thes disk, handle 10% of the IO.
That's much better than the 20 - 80 we see all too often.
Still, it can be improved upon. Check out the SAME recommendations (Stripe and mirror everything). Bunch up a large number of LUNs into a single VG, then hand them out at striped LVs (extent based, 1MB? 4MB?). Or just have the storage do the striping.


You have CPU time to burn, but a touch heavy on the overall IO. Any memory to spare? Just give it to Oracle for the SGA and reduce the read IO!

When looking at write IOs (in the Oracle stats), pay close attenion to the REDO LOG, it often defines the user perceived speed. General writes in Oracle are NOT waited for, so matter much less.

I'll attach a small perl script to summarize the sar output into a single line per timestamp. It may help interpretting the data, and can server as a starting point for further formatting/counting:

C:\Temp>perl sar.pl < sar.txt | more

Time usr sys blk rw rw dsk

00:00:01 0 0 0 0 0
00:05:03 8 5 42023 629 105 c47t3d2
00:10:02 7 3 8930 262 64 c12t5d0
00:15:01 2 3 4637 176 150 c12t5d0
00:20:01 7 5 22787 518 83 c47t0d7
00:25:00 6 5 23287 605 62 c12t5d0
00:30:01 4 3 14759 447 59 c12t5d0
00:35:02 27 10 55627 1318 115 c47t0d7




Mel_12
Advisor

Re: Poor Oracle Performance

I'd say why do you want to do DBA job? Is it because of lack of one? If the production system is of any value, then get the DBA involved. How would you feel having a DBA do System Admin job?

As a DBA and ex system admin, the best place to look for the cause of the bottleneck is the Oracle wait events. Start with:
select * from v$waitstat. This gives you overall health of the database at a glance.

Later, run this one to begin to isolate them.
select sid, event, p1text, p1, p2text, p2, wait_time, state
FROM v$session_wait
ORDER BY wait_time.

Be sure to exlude such events as (rdbms,SQL*,Pipe) These are simply users having idle sessions and are not relevant. You will be able to find the sessions waiting for a particular event and then wear you DBA hat to trouble shoot it.

Hope this helps.
Good luck
Mel

While running the
Fred Ruffet
Honored Contributor

Re: Poor Oracle Performance

I imagine your problems are not only related to this point, but it may help.

Note that FS for Oracle data files may be mounted with these options :
nodatainlog,mincache=direct,convosync=direct
These options bypass the OS cache (Oracle has its own). It increases perfs and makes OS buffer cache not to grow too much.

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)