Hi all,
We have a oracle 9i database.......
My wuestion what should be the value of all the below parameters...
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- --------------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
SQL>
SQL> 1
1* select EVENT,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
SQL> 2 order by 2;
SQL> /
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
reliable message 1 0
wait for sync ack 1 49
wait for tmc2 to complete 1 3
refresh controlfile command 1 4
control file heartbeat 1 391
global cache cancel wait 2 0
global cache assume wait 2 0
instance state change 2 0
log buffer space 3 2
ges LMD to inherit communication ch 4 5
annels
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
ges reconfiguration to start 4 2
log switch/archive 4 818
library cache load lock 4 1
ges cgs registration 6 20
switch logfile command 7 77
sbtinit 8 0
ges global resource directory to be 8 3
frozen
ges lmd/lmses to unfreeze in rcfg - 12 3
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
mrcvr
lock deadlock retry 16 0
lock escalate retry 17 41
buffer deadlock 19 0
retry contact SCN lock master 22 9
ges lmd/lmses to freeze in rcfg - m 23 3
rcvr
global cache domain validation 26 100
ges global resource directory to be 29 2
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
unfrozen
latch activity 30 11
log file switch completion 37 10
wait for votes 38 4
buffer busy global cache 41 3
kjctssqmg: quick message send wait 44 0
log file single write 63 0
slave TJ process wait 69 2
ges inquiry response 94 0
control file single write 136 3
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
db file single write 147 0
ges enter server mode 159 188
ges LMON to get to FTDONE 175 3
inactive session 196 97
global cache busy 211 41
global cache freelist wait 468 23
ges2 LMON to wake up LMD - mrcvr 508 3
ges2 LMON to wake up lms - mrcvr 2 1002 3
wait list latch free 1043 3
name-service call wait 1379 15
PX Deq: Table Q Normal 1675 1
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
PX Deq: Msg Fragment 1861 0
log file sequential read 2954 2
local write wait 3200 1
PX Deq: Signal ACK 6163 1
BFILE closure 7340 0
BFILE open 7340 0
PX qref latch 7661 2
undo segment extension 7830 0
PX Deq: Parse Reply 9037 1
global cache quiesce wait 9170 1
cr request retry 10804 0
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
PX Deq: Join ACK 11403 1
IPC send completion sync 12233 0
LGWR wait for redo copy 12463 0
library cache pin 16747 1
global cache null to s 17238 0
global cache open s 18276 0
PX Deq Credit: need buffer 28738 2
rdbms ipc reply 30448 0
PX Deq: Execution Msg 36837 81
BFILE get length 42313 0
buffer busy global CR 48220 3
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
buffer busy waits 61937 4
BFILE read 72180 0
BFILE internal seek 72180 0
SQL*Net break/reset to client 95852 0
process startup 97296 16
smon timer 105682 3297
direct path read (lob) 110572 0
PX Idle Wait 123179 240
KJC: Wait for msg sends to complete 156664 0
PX Deq Credit: send blkd 206833 1
SQL*Net more data from client 207782 2
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
global cache open x 247251 0
log file sync 279795 1
global cache null to x 287788 0
library cache lock 310497 0
db file parallel write 389995 1
global cache s to x 413101 0
wait for master scn 1223340 0
control file parallel write 1228520 0
pmon timer 1264499 285
async disk IO 1372835 0
row cache lock 1695921 0
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
log file parallel write 2052993 0
direct path write 2294039 0
PX Deq: Execute Reply 2794431 0
jobq slave wait 2826270 291
db file parallel read 3651422 5
control file sequential read 4955734 0
ksxr poll remote instances 5892683 0
DFS lock handle 6082717 0
latch free 6445502 2
PX Deq: reap credit 7377155 0
direct path read 11164536 0
EVENT TOTAL_WAITS AVERAGE_WAIT
----------------------------------- ----------- ------------
enqueue 25477002 1
CGS wait for IPC msg 34538272 0
db file scattered read 46061111 0
rdbms ipc message 51128984 76
db file sequential read 62452600 1
ges remote message 110882900 3
global cache cr request 129116276 0
gcs remote message 183741952 4
SQL*Net more data to client 567238690 0
SQL*Net message from client 782068658 13
SQL*Net message to client 782069617 0
111 rows selected.
SQL>
I the dbazine.com , igot to know that ideally the value for the optimizer_index_cost_adj should be calulated as (df file sequential read/db file scattered read ) * 100
And i am getting the value 135.....
and what about all the other paramters.......
Pls suggest me....
Does it have a impact on the perfomance of the database or not......
I have one more question ,the question i have been asked how you will calcualate the value of db_block_size, give me a calulation...
Please throw some lights on the same....