he..he... begitulah oracle :) masih ingat misleading di ADDM mengenai parameter.
"pada EM selalu muncul di ADDM findings, bahwa parameter open cursor dan open_cache_cursor kurang, kemudian oracle suggest untuk menaikkan open cursor dan open_cache_cursors. saya sudah menaikkan open cursor dari parameter default menjadi 1024 tp untuk open_cache_cursor tidak saya tambahkan." seharusnya bukan parameter..yg dia reference adalah nama statistic (opened cursors*). sekarang nambah misleading satu lagi :-) grouping instance efisiensi target 100% ..... kalau asumsi kita sebagai user, angka2nya harus mendekati 100% .... tapi ada item lain yg targetnya bukan 100% (at least based on my knowledge). so, kesimpulannya...welcome to the oracle world :-) -- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com On Wed, Mar 18, 2009 at 6:51 PM, Andes Febrian <[email protected]> wrote: > tp kenapa di AWR, execute to parse masuk ke dalam bagian instance efficiency > percentages yg mana target nya adalah 100%, jadi asumsi nya semakin value > nya mendekati 100% maka semakin bagus. mohon pencerahannya. > > Instance Efficiency Percentages (Target 100%) > > Buffer Nowait %: 100.00Redo NoWait %: 100.00 Buffer Hit %: 99.91In-memory > Sort %: 99.99 Library Hit %: 99.92Soft Parse %: 99.71 Execute to Parse > %:48.12Latch > Hit %: 98.86 Parse CPU to Parse Elapsd %: 10.59% Non-Parse CPU: 96.62 > > Many Thanks > > 2009/3/18 Ujang Jaenudin <[email protected]> > >> karena itu merupakan rasio parse terhadap execution, idealnya sekali >> parse seterusnya execution. karena parse disini soft + hard >> parse....maka asumsinya sekali hard parse seterusnya no parse + >> execution. >> >> so angka execute to parse harusnya mendekati 0% lebih bagus. >> >> >> -- >> thanks and regards >> ujang | oracle dba | mysql dba >> http://ora62.wordpress.com >> >> On Wed, Mar 18, 2009 at 2:41 PM, Andes Febrian >> <[email protected]<pejantan4u%40gmail.com>> >> wrote: >> > hallo pak ujang, >> > >> > ada yg mau di tanyakan, >> > execute to parse = 48%, bagus nya nilai tersebut mencapai 100% kan ? klo >> > kurang dari 10% bisa di bilang parah tidak ? >> > >> > Many Thanks >> > >> > On Wed, Mar 11, 2009 at 9:55 AM, Ujang Jaenudin < >> [email protected] <ujang.jaenudin%40gmail.com>>wrote: >> > >> >> mencermati "execute to parse", dimana kalkulasinya diambil dari >> >> perbandingan parses/executions, parses disini adalah soft parse maupun >> >> hard parse: >> >> >> >> 100*(1-(1,076.18/2,074.36)) = 48.12 >> >> >> >> dari situ diperkiraan terjadi "tidak efisien" pada shared pool, apatah >> >> karena: >> >> >> >> - tidak menggunakan bind variable, namun ini hanya sedikit impact, >> >> buktinya angka hard parse tidak begitu tinggi. >> >> - parameter session_cached_cursor kurang memadai. >> >> sehingga terlalu banyak soft parses. >> >> - namun demikian jika melihat top 5 wait event, silahkan cek di >> >> metalink, dulu di 10.2.0.3 pernah menemukan bugs yg berelasi dgn >> >> "cursor: pin S wait on X". >> >> >> >> so, apply patch dulu baru cek lagi apakah shared pool masih bermasalah >> >> ataukah ndak.... >> >> >> >> >> >> -- >> >> thanks and regards >> >> ujang | oracle dba | mysql dba >> >> http://ora62.wordpress.com >> >> >> >> On Tue, Mar 10, 2009 at 2:49 PM, Andes Febrian >> >> <[email protected]<pejantan4u%40gmail.com> >> <pejantan4u%40gmail.com>> >> >> >> wrote: >> >> > Halo pak ujang, maaf baru bisa bales, berikut saya paste awr report >> nya, >> >> > mohon di liat2, Thanks. btw, mau tanya jg, klo istilah2 yg ada di top >> 5 >> >> > event, apa siy artinya ? Many thanks. >> >> > >> >> > DB NameDB IdInstanceInst numReleaseRACHost >> >> ITTDB1135347846ITTDB110.2.0.3.0NO >> >> > ITTDB >> >> > >> >> > >> >> > Snap IdSnap TimeSessionsCursors/Session Begin Snap:1874310-Mar-09 >> >> 09:00:05 >> >> > 258 19.8 End Snap:1874410-Mar-09 10:00:11516 14.7 Elapsed: 60.10 >> (mins) >> >> DB >> >> > Time: 647.52 (mins) >> >> > >> >> > Report Summary >> >> > >> >> > Cache Sizes >> >> > >> >> > >> >> > BeginEnd >> >> > >> >> > Buffer Cache: 2,860M 2,860MStd Block Size: 8K Shared Pool Size: >> >> 5,252M5,252MLog >> >> > Buffer: 6,104K >> >> > >> >> > Load Profile >> >> > >> >> > >> >> > Per SecondPer Transaction Redo size: 143,421.18 3,968.22 Logical >> >> > reads:160,750.904,447.70 Block >> >> > changes: 405.14 11.21 Physical reads: 208.17 5.76 Physical writes: >> >> > 154.924.29 User >> >> > calls: 1,688.73 46.72 Parses: 1,076.18 29.78 Hard parses: 3.17 0.09 >> >> > Sorts:34.440.95 >> >> > Logons: 13.23 0.37 Executes: 2,074.36 57.39 Transactions: 36.14 >> >> > >> >> > % Blocks changed per Read: 0.25Recursive Call %: 55.94 Rollback per >> >> > transaction %: 41.85Rows per Sort: 914.74 >> >> > >> >> > Instance Efficiency Percentages (Target 100%) >> >> > >> >> > Buffer Nowait %: 100.00Redo NoWait %: 100.00 Buffer Hit %: >> >> 99.91In-memory >> >> > Sort %: 99.99 Library Hit %: 99.92Soft Parse %: 99.71 Execute to Parse >> >> > %:48.12Latch >> >> > Hit %: 98.86 Parse CPU to Parse Elapsd %: 10.59% Non-Parse CPU: 96.62 >> >> > >> >> > Shared Pool Statistics >> >> > >> >> > >> >> > BeginEnd Memory Usage %: 74.11 78.28 % SQL with executions>1: 85.29 >> 81.36 >> >> % >> >> > Memory for SQL w/exec>1: 98.95 98.60 >> >> > >> >> > Top 5 Timed Events >> >> > >> >> > EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class CPU time >> >> 16,394 42.2 >> >> > cursor: pin S wait on X 634,165 6,950 11 17.9Concurrency PX Deq >> Credit: >> >> > send blkd 230,874 6,862 30 17.7Other enq: TX - row lock contention 621 >> >> > 1,6882,7184.3 >> >> > Application PX qref latch 430,860 1,664 4 4.3Other >> >> > >> >> > >> >> > On Thu, Mar 5, 2009 at 4:37 PM, Ujang Jaenudin < >> [email protected] <ujang.jaenudin%40gmail.com><ujang.jaenudin% >> 40gmail.com> >> >> >wrote: >> >> > >> >> >> bisa dipaste disini awr report sampe bagian top 5 wait event...? >> >> >> >> >> >> atau bisa cari statistik session cursor cache count, session cursor >> >> >> cache hits, cursor cache hits, parse count (total). >> >> >> >> >> >> kalau ini cara extrim, musti hati2 di environment >> >> >> production...analisanya pun butuh waktu :) >> >> >> >> >> >> alter session set events '10270 trace name context forever, level >> 10'; >> >> >> >> >> >> >> >> >> > > > > -- > Cheers, > ^_^ Andes ^_^
