Re: tunning help needed...

Stefan.,

I would guess that your comment is based on an enthusiasm for looking at wait events to determine performance. Whilst it is the 'new' exciting thing, I think it is too easy to get hung up on it to the extent that you forgot that there are two reasons for performance problems.

    being block from working     v$system_event et. al.
    working too hard                v$sysstat et. al.

Look at this evidence - which does NOT require timed_statistics: I've omitted the "per tx" figures as they simply half the "per sec" figures.

> sorts (rows) 615,835 1,234.1
> table fetch by rowid 76,726 153.8
> table scan blocks gotten 137,870 276.3
> table scan rows gotten 27,222,785 54,554.7

> physical reads direct 12,529 25.1
> physical writes direct 12,529 25.1

Doesn't it strike you as odd the system does 300 rows fetched by tablescan for every row fetched by rowid ? An apparent average of 27,00 per transaction !

Notice also that the numbers for direct reads and writes are rather high - either the system is busy (re)building tables and/or indexes, or it is running parallel query to do this.

Whilst it would be quite nice to see the wait time and compare it with the CPU usage time to "prove" that the most significant problem was CPU wastage due to SQL execution, you can't say that there is no "real" evidence to make judgements on.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Stefan wrote in message ...
Hi Stan,

this thread is quite interesting. These guys are all talking about problems
without any real evidence. Running statspack without having turned on
timed_statistics is pretty useless.

Please turn on timed_statistics and rerun your statspack report

Regards
Stefan