Re: 10.2.0.4 Database Performance Issues ( cursor: pin S wait on X ) on AIX 5.3
- From: Balakrishna Y <krishna000@xxxxxxxxx>
- To: Surachart Opun <surachart@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 18:12:44 +0530
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:
