Re: Help!!!!about Peformance Tuning

On Tue, 17 Jan 2006 19:14:33 -0800, ORACLE APPRENTICE wrote:

> 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:

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN DECLARE v_ID hitlog.id%TYPE;

v_TYPE hitlog.type%TYPE;   v_Count NUMBER;      CURSOR cur_produ
ct IS          select unique ID,TYPE from hitlog t;           BE
GIN      OPEN cur_product;      LOOP          FETCH cur_product

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.

All your base are belong to us.
(http://frogstar.com/aybabtu/index.asp)

-- 
http://www.mgogala.com