Hi All,
Hopefully post to the right forum, if not please do let me know. Thanks
I have one pre-production issue still don't have any clue how to move forward.
This is 2 RAC nodes in linux platform with Oracle 11.2.0.2
In the begininng this environment having a lot of performance issue, as huge "cursor: pin S wait on X", "latch: shared pool"
and "library cache:Mutex X" causing very bad performance in this environment. After oracle support suggest to disable few hidden paramter
and adjust some parameter, then it help to stablized the environment (according to oracle, the initial issue was caused by high version count).
But we still can find minimal "latch:shared pool" and "library cache:Mutex X" on top 5 wait event list on hourly AWR report.
This time oracle was saying it might caused by high reload and high invalidatiosn in sqlarea (not sure how true it is), luckily the event
did not caused the performance issue at this moment, but we're asking support how can we get rid of the "mutex/latch" event.
They give me one query to check the invalidations in v$sqlarea, and they suspect the high validation is causing by application.
select *
from v$sqlarea
order by invalidations DESC;
Weird thing is, there have one SQL tag with "SQL Analyze" always causing high invalidations. But we're not able to get more detail (base on SQL_ID)
in v$sql or v$session table. This SQL insert into v$sqlarea and remove within 1 or 2 seconds, hard to get more information.
And the statement is exactly the same, but don't know why SQL Analyze always checking on it.
This SQL is triggering by SYS user, and it is inserting into MLOG$ table (one of the application materialized log file)
insert into "test"."MLOG$_test1" select * from "test"."MLOG$_test1"
The v$sqlarea information as below, sometime the invalidations can hit more than 10,000
SQL_ID SQL_TEXT LOADS INVALIDATIONS
0m6dhq90rg82x /* SQL Analyze(632,0) */ insert into "test"."MLOG$_test" select * from "test"."MLOG$_test 7981 7981
{code}
Anyone have any idea how can i move forward for this issue? As Oracle is asking me to use SQLTXPLAIN get the detail?
Please share with me if you have any idea. Thanks in advance.
Regards,
Klng