Re: 10.2.0.4 Database Performance Issues ( cursor: pin S wait on X ) on AIX 5.3
- From: Surachart Opun <surachart@xxxxxxxxx>
- To: Balakrishna Y <krishna000@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 20:24:39 +0700
Thank you for your explain. I don't know much about AIX.
And you use LOCK_SGA=TRUE , that mean you don't have use ASMM ;)
http://surachartopun.com/2009/11/locksga-can-not-use-with-amm-or-asmm.html
By the way You use LOCK_SGA=TRUE (on AIX-Based Systems), then need Large
Page Feature, so enable Large Page Feature on AIX-Based Systems [ID
372157.1]
And I think metalink 372157.1, that can help you explain for lgpg_regions
value (LARGE PAGES).
lgpg_regions=num_of_large_pages+num_of_additional_pages
num_of_large_pages = INT((total_SGA_size-1)/16MB)+1
num_of_additional_pages = INT((.text_pages+4095)/4096) +
N*(INT(.data_pages+4095)/4096)
http://www.ibm.com/developerworks/aix/library/au-aixoracle/index.html
Thank You & good Luck
Surachart Opun
http://surachartopun.com
On Tue, Nov 24, 2009 at 7:42 PM, Balakrishna Y <krishna000@xxxxxxxxx> wrote:
> 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:
