Re: 10.2.0.4 Database Performance Issues ( cursor: pin S wait on X ) on AIX 5.3
- From: Balakrishna Y <krishna000@xxxxxxxxx>
- To: "Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 24 Nov 2009 18:24:29 +0530
Thanks!! for you reply but this does'nt solve my purpose. But my requirement
is why lgpg_regions is getting reduced automatically. It should not be like
that. Any idea ??
Regards
Bala
On Tue, Nov 24, 2009 at 6:18 PM, Johnson, William L (TEIS) <
WLJohnson@xxxxxxxxxxxxxxxxxxx> wrote:
> I don’t have all of my notes with me right now – but try searching
> metalink for this…
>
> _first_spare_parameter integer 1
>
>
> ------------------------------
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Balakrishna Y
> *Sent:* Tuesday, November 24, 2009 7:43 AM
> *To:* Surachart Opun
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: 10.2.0.4 Database Performance Issues ( cursor: pin S wait
> on X ) on AIX 5.3
>
>
>
> Hi ,
>
> By setting LOCK_SGA=TRUE and also increasing large pages at OS level
> according to this note *372157.1 *our performance got increased.
>
> We were not getting any latch issues there after but all of a sudden
> lgpg_regions getting reduced to 366 from 933. Again we started getting
> performance problems severely .
>
> I don't really understand how come lgpg_regions got reduced automatically
> to 366.Does any one know is there anything can be done to set it permanently
> .
>
> Regards
>
> Bala
>
> On Tue, Nov 24, 2009 at 5:59 PM, Surachart Opun <surachart@xxxxxxxxx>
> wrote:
>
> Check
>
> - frequency resizing operations for the Shared Pool.
>
>
>
> ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';
>
> SET PAGESIZE 900
>
> SET LINESIZE 255
>
> COL COMPONENT FORMAT A25
>
> COL INITIAL_SIZE FORMAT A10
>
> COL TARGET_SIZE FORMAT A10
>
> COL FINAL_SIZE FORMAT A10
>
> COL OPER_TYPE FORMAT A10
>
> select START_TIME, component, oper_type, oper_mode,status,
> initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET",
> FINAL_SIZE/1024/1024 "FINAL", END_TIME
>
> from v$sga_resize_ops
>
> order by start_time, component;
>
>
>
> if frequency... check on metalink to help
>
> If you have many session (OLTP)
>
> - avoid to use dynamic SQL
>
> http://surachartopun.com/2009/07/dynamic-sql-cursor-pin-s-wait-on-x.html
>
>
>
> - make sure you use bind variable in SQL statement.
>
> - check sql, pl/sql and solve it
>
>
>
> by the way, check AWR report + Spatspack Report in that time.
>
>
>
> - check bug on metalink
>
>
>
> Surachart Opun
> http://surachartopun.com
>
>
>
> On Tue, Nov 24, 2009 at 12:16 PM, Balakrishna Y <krishna000@xxxxxxxxx>
> wrote:
>
> Hi All,
>
> We have recently upgraded our Production Database to 10.2.0.4 from 10.2.0.2
> . It is observed that our performance is getting hampered with* latch:
> library cache, latch: library cache Lock , **cursor: pin S wait on X .
>
> *Did any one come accross these problems after upgrading to 10.2.0.4 .
> Your comments is higly appreciated please.
>
> Regards
>
> Bala
>
>
>
>
>
Other related posts:
