RE: 10.2.0.4 Database Performance Issues ( cursor: pin S wait on X ) on AIX 5.3
- From: "Johnson, William L (TEIS)" <WLJohnson@xxxxxxxxxxxxxxxxxxx>
- To: "krishna000@xxxxxxxxx" <krishna000@xxxxxxxxx>, Surachart Opun <surachart@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 07:48:59 -0500
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<mailto: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<mailto: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:
