Re: statspack comment is needed

hopehope_123 wrote:
> Hi ,
>
> Thank you very much for your help.
>
> Based on the article ,
>
> the system has 8 cpus .
> 5551 secs ( 92) min cpu time in 60 minutes : if i have 2 cpus utilized
> 100 % then , i can get 120 min. cpu time , here only 92 min. cpu time
> can be achieved by using only 2 cpus, i have 8 , so only %20 of total
> cpu resources is used.
>
> Is it correct?
>
> The application is an oltp which has strict transaction limitations
> such as every business procedure has 4 secs. time out value.
>
> This is another part of the report , which shows high numbers of av
> rd(ms)
>
> Tablespace Filename
> ------------------------
> ----------------------------------------------------
> Av Av Av Av Buffer
> Av Buf
> Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
> Wt(ms)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
>
> UNDO_TBS_FIRST /dev/vx/rdsk/datadg/UNDOFIRST
> 1 0 110.0 1.0 1,021 0 3
> 0.0
> /dev/vx/rdsk/datadg/verde_raw_undo12_8g
> 1 0 90.0 1.0 2,555 1 17
> 0.0
> /dev/vx/rdsk/datadg/verde_raw_undo1_1g
> 1 0 90.0 1.0 850 0 6
> 1.7
>
> UNDO_TBS_SECOND /dev/vx/rdsk/datadg/UNDOSECOND
> 1 0 90.0 1.0 1 0 0
> /dev/vx/rdsk/datadg/verde_raw_undo22_1g
> 1 0 120.0 1.0 1 0 2
> 0.0
> /dev/vx/rdsk/datadg/verde_raw_undo2_1g
> 1 0 90.0 1.0 1 0 0
>
>
> Kind Regards,
> hope

Tuning by looking at statspack output for anything is tricky but especially just things like CPU or IO statistics is not exactly a recommended approach any longer. ( If you use it to identify SQL with bad LIO that's a somewhat better approach ).

Try looking at Tom Kyte's site http://asktom.oracle.com and do a search on statspack ... you will probably start down a better path.

Cary Millsap's book "Optimizing Oracle Performance" is recommended. Received on Fri Dec 08 2006 - 06:13:58 CST