opinions on statspack

[hannah00]

thanks Dapi,

1) Are my critical business activities (queries & changes) executed with a appropriate speed? If not, why not?
YES, AFTER THE CHANGE I MADE BY ADDING "Optimizer_index_cost_adj = 10", THE MAP IS NOW RUNNING MUCH FASTER THAN BEFORE.

2) How is my server standing up to this?
SERVER IS FINE, I DID TRACE THE SERVER CPU AND MEMORY, IT NEVER EXCEED 50% OF THE TOTAL PHYSICAL RESOURCES THAT WERE AVAILABLE.

As I mention it before, one of my query did a FTS and I guess Oracle pick whatever plan with lower cost and ignore the index, now after making the change, the cost to execute the same query is higher but it did pick up INDEX and the app perform much better, but I run STATPACK again and CPU was still at 80% and DB SEQUENTIAL READ still somewhere at 16% and I don't undertand this.

statspack was run in about 20 min timeframe, with a few users in there.

[pando]

To start with forget compare the costs, lower does not mean better

Get the TOP sql in your second statspack report and see if both reports (before modifying the parameter and after) share same TOP sql statements

[hrishy]

Originally posted by hannah00
thanks Dapi,

1) Are my critical business activities (queries & changes) executed with a appropriate speed? If not, why not?
YES, AFTER THE CHANGE I MADE BY ADDING "Optimizer_index_cost_adj = 10", THE MAP IS NOW RUNNING MUCH FASTER THAN BEFORE.

Hi Hannah

when you changed your optimizer_index_cost_adj to 10 you said that the map was running faster.If i were you i would have done this

1)Get the plan of the query as it is
2)alter session set Optimizer_index_cost_adj to 10 and see
whats the plan again
3)try and see how plan in 2 is more efficient dont rely on the cost alone check for response time Logical IO's
4)see why the optimizer choose plan B .

If you just Optimizer_index_cost_adj=10 i am not sure how your other queries might perform.If you think that other users are not screaming then yes go ahead and make Optimizer_index_cost_adj =10

regards
Hrishy

[hannah00]

thanks all for the valueable advises.

I will definitely test it out and and let you know what I find.

[hannah00]

thanks Hirshy.

here is the latest test I have.

I trace two different execution plans one with Optimizer_index_cost_adj =10 and one without Optimizer_index_cost_adj =10
and below is what I find:

on the total CPU time on the trace file on Optimizer_index_cost_adj =10 is much lowever than the one without Optimizer_index_cost_adj =10. but I what I don;t understand is the top 5 events of CPU is higher than before:

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                           21    92.13
db file sequential read                            28,842           1     5.83
control file parallel write                           497           0     1.81
SQL*Net more data to client                           325           0      .09
log file sync                                           1           0      .06

could you please explain to me why???

[hrishy]

Hi Hannah

when you change that parameter Optimizer_index_cost_adj it effected the queries as that parameter tells the optimizer that indexes are cheaper to use and please go ahead and use them.That explains why your query plan changed.

But i doubt whether the Optimizer_index_cost_adj has anything to do with the CPU.(thats what dapi The cranky old philospoher said

I n t e r e s t i n g . . . . 

I wouldn't have expected excessive FTS to produce this

code:--------------------------------------------------------------------------------Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                           97    75.94
db file sequential read                         1,532,454          28    22.23
db file scattered read                             23,868           2     1.61--------------------------------------------------------------------------------

what he meant was you were trying to cure the disease of high cpu usage by trying to medicate with Optimizer_index_cost_adj .


Optimizer_index_cost_adj setting this will not solve your problem.

for advise on setting this parameter refer to this formula courtesy by Tim Gorman

OPTIMIZER_INDEX_COST_ADJ = 
select round((select average_wait
                from v$system_event
               where event='db file sequential read')
            /
             (select average_wait
                from v$system_event
               where event='db file scattered read')
            * 100)
  from dual;

in 9i

col a1 head "avg. wait time|(db file sequential read)"
col a2 head "avg. wait time|(db file scattered read)"
col a3 head "new setting for|optimizer_index_cost_adj"

logged in as sys issue
select a.average_wait a1,
       b.average_wait a2,
       round( ((a.average_wait/b.average_wait)*100) ) a3
from
      (select d.kslednam EVENT,
              s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
       from x$kslei s, x$ksled d
       where s.ksleswts != 0 and s.indx = d.indx) a,
      (select d.kslednam EVENT,
              s.kslestim / (10000 * s.ksleswts) AVERAGE_WAIT
       from x$kslei s, x$ksled d
       where s.ksleswts != 0 and s.indx = d.indx) b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';

This should give you a starting point you should up it or bring it down after testing all your crtical business scenarios as the cranky old philosopher put it ;-).

Now coming back to your problem of high cpu usage.You can

1)use os utilities to find out who is hogging the cpu
2)dig through your statspack report to find cpu intensive sql's
3)upload your statspack report to www.oraperf.com for free analysis.
4)Be happy that your cpu usage is high and you are getting all your moneys worth if nothing is a suspect ;-)

regards
Hrishy

[OracleDoc]

Good call on the Oraperf Hirshy I totally forgot about that one.

[DaPi]

Originally posted by hannah00
but I what I don;t understand is the top 5 events of CPU is higher than before:

Two things:
- you will always have a top 5, you can't get rid of them, something has to be "top"
- the % elapsed will always add up to 100% (if you take ALL the waits)

Your CPU wait time is down from 97 to 21 seconds.
Your db file sequential read wait time is down from 28 to 1 seconds.
If you're taking the statspack over the same time period, you're winning.

92% CPU wait, might be a very good thing (or it might not) - not many Oracle servers are CPU-bound.

[abhaysk]

Originally posted by pando
Get the TOP sql in your second statspack report and see if both reports (before modifying the parameter and after) share same TOP sql statements

To add.. check the plans before and after modifying the param..

I suspect u may see in the plan/s like 'Index Full Scan'..


Abhay.

[hannah00]

thank you so much for help!!!

those are great advises.