statspack report

Could someone please give me some advises with this report??? I think, the report reflect that the database had I/O issues but I am not sure how to attack the right problem.

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.94 Redo NoWait %: 100.00
Buffer Hit %: 84.25 In-memory Sort %: 99.98
Library Hit %: 90.59 Soft Parse %: 63.25
Execute to Parse %: 63.75 Latch Hit %: 99.83
Parse CPU to Parse Elapsd %: 91.82 % Non-Parse CPU: 99.98

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 72.26 90.46
% SQL with executions>1: 59.26 48.01
% Memory for SQL w/exec>1: 42.20 35.04

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file scattered read 2,419,171 178,492 76.70
db file sequential read 656,065 25,652 11.02
log file parallel write 70,378 16,616 7.14
db file parallel write 1,983 3,639 1.56
buffer busy waits 108,393 2,169 .93

When 76% of your wait time is on scattered reads, I would guess you have a lot of full table scans in your system. Start tuning your queries to use indexes.

thanks Jeff,

as far as I/O and memory, you think it's ok ???

If I told you no, would you by more hardware or fix the SQL?

thanks for the values advises.
one more question. the db_file_multiblock_read_count =32.

is it too high, should it be set to 16????

Quote:

---

Originally posted by mike2000
thanks for the values advises.
one more question. the db_file_multiblock_read_count =32.

is it too high, should it be set to 16????

---

Too many FTS (full table scans) are happening in your database. Tune your SQLs. Change db_file_multiblock_read_count to 16 and check for performance.

Jeff,

thanks for all of your response.

talking FTS, I just found out that it's true the problem is FTS and what I don't understand is all of the neccessary indexes are there and they are not being used. the client anlyzed objects on this database every night and I don't think it's a good thing to do. So what I did was to add a hint into one query and it's used indexes.

The main question is why Oracle sometimes picked up the indexes and sometimes it's not???
rebuilt indexes is going to help ????


could you please tell me why and how to fix this problems???

thanks

Your db_file_multiblock_read_count is set to higher value. It makes optimizer to calculate the FTS as cheaper. Try changing db_file_multiblock_read_count to 16 and see. You will see that "db file scattered read" wait reducing to a noticeable value.

Abt optimizer not choosing indexes... It depends on many factors.. The kind of join you use in your query (nested loop, hash, sort merge), cardinality of the indexed columns, and number of rows fetched by each of the sub queries.

thanks for your advises.

so you are saying that the db_file_multiblock_read_count is too high so that Oracle think FTS is cheaper so that it do full table scan instead of using index. By changing the db_file_multiblock_read_count =16, do you think Oracle will try to use indexes somehow. The thing is that before the changes I made today, sometime Oracle use index and sometime it do FTS and it's very inconsistent.

Quote:

---

Originally posted by mike2000
thanks for your advises.

so you are saying that the db_file_multiblock_read_count is too high so that Oracle think FTS is cheaper so that it do full table scan instead of using index. By changing the db_file_multiblock_read_count =16, do you think Oracle will try to use indexes somehow. The thing is that before the changes I made today, sometime Oracle use index and sometime it do FTS and it's very inconsistent.

---

Nope by db_file_multiblock_read_count would cause the optimizer to think that the cost of a full table scan is cheaper thats it..if you lower it from 32 to 16 then the cost based optimizer would now start thinking that the full table scan are costlier then before..your indexes would be used or not is another question all together..you will have to alter the paramter and take another statspack report and see..and post it here..

regards
Hrishy