wah...kalau informasi nya terbatas....even nebak2 pun gak berani... karena related dgn aplikasi dan query2 nya spt apa...kalau query2 nya panjang 5 meteran nambah dikit aja bisa impact pada shared pool.
kalau melihat shared pool hit ratio nya masih 99% .... jadi masih ada space untuk naikin. mungkin hitung2an kasarnya begini: alokasi memory by session_cached_cursors = average length of cursor * session_cached_cursors misal kalau query nya panjang2 tsb dgn length 100k, dan session cached cursor 1000..ya sediakan saja kira2 memory 100MB untuk shared pool. 100m = 100k * 1000 -- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com On 3/11/09, Andes Febrian <[email protected]> wrote: > pak ujang, apakah dengan menaikkan parameter session_cached_cursors akan > memberikan impact yg significant ? > bila harus menaikkan session_cached_cursors, berapakah nilai yg pas ? > current parameter session_cached_cursors = 20 (masih default) > dan current parameter open_cursors = 1024 (sudah pernah di rubah) > > adakan saran berapa besar nilai untuk menaikkan kedua parameter ini ? mohon > pencerahannya. > > untuk apply patch, saya akan cari2 dulu di metalink yg mengenai cursor:pin S > wait on X" > > many thanks. > > On Wed, Mar 11, 2009 at 9:55 AM, Ujang Jaenudin > <[email protected]>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>> >> 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> >> >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 ^_^
