Re: Help!!!!about Peformance Tuning

> I have problems about CPU loading and Oracle performance.> The loading average is always higher than 1(which is abnormal).> I have looked up v$seesion_wait, jobq slave waits obviously hold> resources, that cause CPU loading is always high.> Plz help to tune my system.> Here is my machine load average.

What exactly needs tuning? Do you have any problems with the application
response time? Are users complaining? What part of an application
are they complaining about? Remind them that it's all their fault.
Database without users or data never needs tuning.

On the "top" screen I see that the most CPU intensive process is
Xsun, the X11 server for your box. In the statspack report I see that
the CPU time is several orders of magnitude larger then any other
wait. So, you have a problem with SQL that is burning CPU like crazy.
You can try and catch the SQL while the process is in progress. Top
Sessions OEM monitor from the diagnostics package can do it, as well as
TOAD, Tora, Karma and many other tools. Even SQL*Plus can do it. Get
SID from V$PROCESS and get SQL address and hash value from V$SESSION
and voila, you have one of the SQL statements that are burning CPU.
Another approach would be to go to V$SQL and sort it by CPU_TIME. Take
a look at 5 most expensive SQL statements and see what are they doing.
Then go to V$SQL_PLAN and see how are they executing. Then fix it.

Statspack also provides a clue about what's going on. Here is the snippet:

According to statspack, this thing is responsible for 99% of your buffer
gets. First thing I can say is that whoever wrote that particular piece of
code deserves to be executed by large doses of Kenny Rogers songs.

This is an outline how it should have been done:

TYPE TBL_ID IS TABLE OF HITLOG.UNIQUE_ID%TYPE INDEX BY BINARY_INTEGER;
TYPE TBL_TYPE IS TABLE OF HITLOG.TYPE%TYPE INDEX BY BINARY_INTEGER;
IDS TBL_ID := TBL_ID();
TYPES TBL_TYPE := TBL_TYPE();
SELECT UNIQUE_ID,TYPE BULK COLLECT
INTO IDS,TYPES FROM HITLOG
If you are not a fan of bulk collecting, you can always do the
cursor for loop like this:

FOR C IN CSR
LOOP
<do something>
END LOOP;
That is called "cursor for loop" and is described in the PL/SQL manual.
If I can conclude that from the first few lines of the script, I
can imagine the rest. Remember, you got to know when to hold 'em,
know when to fold 'em, know when to walk away and know when to run.

Alternatively, add more memory to the buffer cache. It will improve
your BCHR, statspack reports it as only 100% and when you add the memory,
forcefully declare that the machine behaves visibly better now. Also, run
x11perf during the peak time to asses how will users react. If users think
that the machine is still slow, all you need them to do is to accept that
the machine is faster now on a subconscious level. Another way to fight
this is to suggest your boss to buy an HP Superdome. It's a wonderful
piece of HW and your boss will be extremely thankful for such a suggestion.