Useful scripts: Catch a session, causing ‘cursor: pin S wait on X’…

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:

SELECT *
 FROM v$session
 WHERE sid IN
 (SELECT TO_NUMBER (SUBSTR (TO_CHAR (RAWTOHEX (p2raw)), 1, 8),
 'XXXXXXXX') sid
 FROM v$session
 WHERE event = 'cursor: pin S wait on X')

The following screenshot shows reducing Latch/mutex waits:

‘COMPATIBLE’… Confusing minds parameter.

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:

Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set compatible='10.2.0' scope=spfile;
System altered.
SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 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 11.2.0.0.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…