Coba cek apakah sql ini masih bisa dioptimize? SELECT HubMessage.* FROM HubMessage WHERE ((((HubMessage.SubscriberID IS NOT NULL AND HubMessage.GivenUp = :P1) AND HubMessage.Invalid = :P2) AND HubMessage.Delivered = :P3) AND HubMessage.Cancelled = :P4)
Wait event 'db file scattered read' berhubungan dengan 'full table scans' atau 'fast full index scans'. Coba cek apakah ada penambahan index yg bisa memperkecil jumlah block yg diakses.. Cara paling mudah adalah dengan membandingkan antara jumlah hasil query dengan jumlah block yg diakses. Kalau misalkan hasil query cuma 10 baris, tetapi mengakses 10000 block, maka sepertinya sql kurang efisien, dan penambahan index biasanya sangat membantu. Utk menentukan kolom mana yg diindex, coba cari tahu distribusi data dari kolom2 yg diakses query tsb, misalkan dengan query ini: select GivenUp,Invalid,Delivered,Cancelled,count(*) from HubMessage group by GivenUp,Invalid,Delivered,Cancelled Menaikkan parameter db_file_multiblock_read_count bisa mengurangi jumlah wait event 'db file scattered read', tetapi mesti hati2 kalau di set secara global (alter system), karena bisa mengakibatkan perubahan execution plan secara global (cost dari full table scans menjadi lebih kecil soalnya). regards, tomi --- praneko <[EMAIL PROTECTED]> wrote: > > Many thanks.. Pak Tomi.:) > > Berdasarkan Statspack level 5 the most wait time > karena : > 1. db file scattered read = 73.09% > 2. db file sequential read = 17.27% > 3. > > Setelah breakdown db_file_scaterred_read ternyata > karena top SQL > statements : > > SELECT HubMessage.* FROM HubMessage WHERE > ((((HubMessage.SubscriberID IS NOT NULL AND > HubMessage.GivenUp > = :P1) AND HubMessage.Invalid = :P2) AND > HubMessage.Delivered = :P3) > AND HubMessage.Cancelled = :P4) > > Execute 262 kali. > Tabel tersebut ada kolom yg NCLOB type nya. > > apakah saya harus ganti > db_file_multiblock_read_count parameter ? > Saat ini db_file_multiblock_read_count= 16 > DB_BLOCK_Size = 8kb > > Many thanks > Amang N > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.blogspot.com Mirror: http://indooracle.wordpress.com ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.blogspot.com Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
