For sure, every network from time to time aborts connection. Especially this commonly could be seen in WANs.
When this happens while a session is fetching data or using some other object through db-link, PMON for some reason (from time to time) is unable to kill this session, so session stucks, leading to ‘cursor: pin S wait on X’ wait event.
It’s quite difficult to find out, which session you need to kill to release memory lock, so here is the query for this:
WHERE sid IN
(SELECT TO_NUMBER (SUBSTR (TO_CHAR (RAWTOHEX (p2raw)), 1, 8),
WHERE event = 'cursor: pin S wait on X')
The following screenshot shows reducing Latch/mutex waits:
Compatible.. What is compatible with what?
Setting COMPATIBLE=’10.0.0′ in Oracle 11g seems promising for 10g developers. Some of them could think ‘I don’t need to modify my code to migrate to 11g’.
But the purpose of this parameter is completely different. It makes Oracle to create and operate with file structures as different version. And the only thing someone can do is to upgrade or downgrade an Oracle version. I don’t think someone would downgrade, but upgrade procedure I’ll describe a little bit later.
‘COMPATIBLE’ is a static parameter. Setting COMPATIBLE=’10.0.0′ in previously created 11g DB will cause an error:
Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set compatible='10.2.0' scope=spfile;
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 810053632 bytes
Fixed Size 2180104 bytes
Variable Size 490736632 bytes
Database Buffers 310378496 bytes
Redo Buffers 6758400 bytes
ORA-00201: control file version 22.214.171.124.0 incompatible with ORACLE version 10.2.0.0.0
ORA-00202: control file: 'C:\TOOLS\ORACLE\PRODUCT\ORADATA\ORCL11G\CONTROL01.CTL'
Recreating a controlfile will cause another error — wrong version of SYSTEM01.DBF.
What Is Compatibility for Oracle Database?
To be continued…