Re: Performace Degrade after upgrading to 8.1.7.4

hardikarm_at_yahoo.com (Mahesh Hardikar) wrote in message news:<4a1c57c2.0306120319.1fa67fb3_at_posting.google.com>...
> Hello ,
>
> Thanks for the inputs & URL. Its really nice. Based on that I am now
> trying to find out segments where really contention occurs. But till
> now I have not come across 'Buffer Busy' in v$session_wait. Mostly I
> get 'db file scattered/sequential read'
>
> Same case I have logged with Oracle Support. Interestingly they claim
> no issues with FreeList. I also saw 'Buffer Bust Waits' in utlestat
> but they are negligible as per Oracle Support.
>
> Following is the extract of Utlestat ...
>
> Event Name Count Total Time Avg Time
> -------------------------------- ------------- -------------
> -------------
> SQL*Net message from client 97882 11053115
> 112.92
> rdbms ipc message 160 983200
> 6145
> db file scattered read 7113206 369610
> .05
> virtual circuit status 80 245841
> 3073.01
> dispatcher timer 40 245800
> 6145
> latch free 59262 204172
> 3.45
> buffer busy waits 63907 67942
> 1.06
> db file sequential read 1917309 38415
> .02
> enqueue 75 21028
> 280.37
> SQL*Net more data from client 1553 842
> .54
> SQL*Net more data to client 13577 505
> .04
> log file sync 1538 395
> .26
> direct path read 20995 230
> .01
> direct path write 14485 118
> .01
> SQL*Net message to client 97881 99
> 0
> log file switch completion 9 94
> 10.44
> refresh controlfile command 603 30
> .05
> control file sequential read 5493 29
> .01
> file open 972 11
> .01
> db file parallel read 3 3
> 1
> SQL*Net break/reset to client 15 1
> .07
>
> CLASS COUNT TIME
> ------------------ ---------------- ----------------
> data block 63905 68245
> segment header 1 0
> undo header 1 0
>
> Now management has added CPU on the machine. But I would also like to
> get root cause of it. Will it be worth to change Freelist for some
> tables where contention is occuring via alter table ?
>

There is no contention for "segment header" so you don't need to add any freelists. Since there is contention for "data block", you should start by changing PCTFREE and PCTUSED settings. Probably to default values of PCTFREE 10 and PCTUSED 40 as mentioned in other post.

Waits for "db file scattered read" and "db file sequential read" typically happen during I/O reads. You may want to increase the values of db_file_multiblock_read_count and db_block_buffers then check the waits on both events.

Regards
/Rauf Sarwar Received on Fri Jun 13 2003 - 00:30:08 CEST