Re: Performace Degrade after upgrading to 8.1.7.4

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          

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 ?

Thanks & Regards,
Mahesh Hardikar Received on Thu Jun 12 2003 - 13:19:58 CEST