Today, I noticed a hang of the session that was executing a simple MERGE statement(s) with
“cursor: pin S wait on X” wait event.

Killed the session and tried to shutdown immediate. Shutdown process hanged as well (only session with the status killed was present in V$SESSION prior to shutdown), with the message in alert.log:

Sat Jan 19 12:56:05 2008
Active call for process 2100 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
Active call for process 1736 user 'SYSTEM' program 'ORACLE.EXE (SHAD)'
SHUTDOWN: waiting for active calls to complete.

A quick search on Metalink revealed that this is a bug introduced in 10.2.0.3.

References:

Note 5907779 – “Self deadlock hang on “cursor: pin S wait on X” (typically from DBMS_STATS)”
Note 401435.1 -“10.2.0.3 Patch Set – Availability and Known Issues”

The bug will be fixed in Patchset 10.2.0.4. For the time being you can use interim patch 5907779, if you’re not using Windows because patch for this platform is not available at the moment.

If the problem re-appear I’ll first check the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views, before killing the sessions.

Today I needed to setup a small test Oracle database on Windows with Oracle ASM storage. Because I didn’t have unused disk partitions that I could spare for Oracle ASM, I decided to use cooked files instead.

Note: Using cooked files instead of raw disks is unsupported by Oracle and should not be used on development and production systems! I needed a quick (and dirty) way to do some testing with ASM, I would never use cooked files in development and/or production.

You’ll need dd port for Windows for creating fake ASM disk files. I used dd version 0.5. For a complete explanation of the procedure refer to Metalink Note:602620.1 “ASM Using OS Files Instead of Real Raw Devices On Windows”.

SET ORACLE_SID=+ASM
sql> connect / as sysdba
--
-- Turn on cooked files support in addition to raw disks
--
sql> alter system set "_asm_allow_only_raw_disks"=false scope=spfile;
--
-- Let's see what I have on the system
--
sql> select path from v$asm_disk; 

no rows selected
--
-- I'll store asm cooked files in C:\ORADATA\ASMDATA so I need to
-- add appropriate disk string for ASM instance to recognize those files
-- as "disk" candidates.
--
sql> alter system set asm_diskstring='C:\ORADATA\ASMDATA\*' scope=both;
--
-- At this point restart the ASM instance for parameters to take effect...
--
--
-- Let's create a couple of disks, I chose 4 x 500MB files...
--
cmd> dd if=/dev/zero of=c:\oradata\asmdata\asmdisk01 bs=1k count=500000 
cmd> dd if=/dev/zero of=c:\oradata\asmdata\asmdisk02 bs=1k count=500000
cmd> dd if=/dev/zero of=c:\oradata\asmdata\asmdisk03 bs=1k count=500000
cmd> dd if=/dev/zero of=c:\oradata\asmdata\asmdisk04 bs=1k count=500000
--
-- Let's check if I see those "disks"...
--
SQL> select path from v$asm_disk;

PATH
-------------------------------------
C:\ORADATA\ASMDATA\ASMDISK01
C:\ORADATA\ASMDATA\ASMDISK04
C:\ORADATA\ASMDATA\ASMDISK03
C:\ORADATA\ASMDATA\ASMDISK02
---
--- Now we can create diskgroup with fake ASM disks...
---
SQL> create diskgroup DGROUP1 external redundancy disk
  2  'C:\ORADATA\ASMDATA\ASMDISK01',
  3  'C:\ORADATA\ASMDATA\ASMDISK02',
  4  'C:\ORADATA\ASMDATA\ASMDISK03',
  5  'C:\ORADATA\ASMDATA\ASMDISK04';

Diskgroup created.
--
-- And final check...
--
SQL> select group_number, name, total_mb, state, 
     database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME              TOTAL_MB STATE       DATABASE_COMPAT
------------ --------------- ---------- ----------- ---------------
           1 DGROUP1               1952 MOUNTED     10.1.0.0.0

With the arrival of “cheap” Intel/AMD based servers with 64-bit CPU’s and large amount of memory, I think the DBA managing Oracle on Linux/Windows has to be aware of kernel features that allows better handling of otherwise, vast amount of standard memory pages (4k on Linux and Windows). Lately I was doing some research of the available articles on Metalink about Linux and Windows approaches to handle larger than default (4k) memory pages.

While finding documentation about HugePages feature on Linux was easy, the lack of the same information for Windows equivalent, Large Pages mechanism, was somehow a frustrating experience – I hope Oracle will refresh some of the existing notes that covers NT architecture with the information about Large Pages. The concept behind the HugePages & Large Page are very similar, but as we know, usually the devil is hiding in the details.

Below are references that I used during my research.

Linux – HugePages

Metalink Notes:

• Note:361323.1 “HugePages on Linux: What It Is… and What It Is Not…”
• Note:361468.1 “HugePages on 64-bit Linux”
• Note:401749.1 “Shell Script to Calculate Values Recommended HugePages / HugeTLB Configuration”

Bigpages Linux kernel feature was replaced by HugePages in 2.6 backported to 2.4), for those of you who are “stuck” on older kernel version (such as RHAS 2.1), two Metalink notes:

• Note:275318.1 “The Bigpages Feature on Linux”
• Note:261889.1 “Bigpages vs. Hugetlb on RedHat Linux”

Other references:

• Kevin Closson blog: Oracle11g Automatic Memory Management – Part I. Linux Hugepages Support.

Windows Server 2003 (x86, EMT64)

---

Oracle introduced support for Large Pages in Windows 2003 (SP1) in Oracle 10g R1.

I would expect to find the necessary information in the following two Metalink notes:

• Note:46001.1 “Oracle Database and the Windows NT memory architecture, Technical Bulletin”
• Note:46053.1 “Windows NT Memory Architecture Overview”

…unfortunately not.

The Chapter 7, Tuning Windows to Optimize Oracle Database in Oracle Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (x64) is much more helpful. This is all you need to setup Oracle 10g/11g to use Large Pages on Windows 2003.

Microsoft official documentation:

• MS Windows Internals, by Mark E. Russinovich and David Salomon, Fourth Edition
• MSDN- Large Page Support (targeted at developers)
• The Memory Manager in Windows Server 2003 and Windows Vista (ppt)

Other sources:

• AMD: Supersizing Java: Large Pages on the Opteron Processor, Part 1
• AMD: Supersizing Java: Large Pages on the Opteron Processor, Part 2
• VMware: Large Page Performance

My personal side notes about “Large Pages” on Windows 2003:

• Which Windows versions has support for Large Pages? I’m not 100% sure I know the answer. According to Wikipedia the Large Pages support is available on Windows 2003 SP1, but not on Windows XP/Vista. I don’t think this is entirely correct, since Windows XP is mentioned in Windows Internals book on page 383 (see reference above)
• don’t use Oracle LOCK_SGA in conjunction with Large Page, instead refer to PRE_PAGE_SGA parameter, if you wish to pre-allocate large pages in memory at instance startup
• user account under which process that wants to use Large Pages is running, needs “Lock Pages in Memory” system privilege. Oracle process by default runs under System account which already has this privilege…[this is false, by default no user account has this privilege, hence adding “System” account (or dedicated user account, if used!) is mandatory]
• not running Oracle service under dedicated user (such as local user oracle) means that service will run under account SYSTEM. If we authorize SYSTEM account to use “Lock Pages in Memory” privilege, we’re giving this privilege to several other services. This brings some security/stability concerns to the table. Shouldn’t we mandatory run Oracle service under some user account, rather than SYSTEM, if we’re going to use Large Pages?
• memory pages allocated via Large Page are pinned in memory and doesn’t swap to the pagefile, make sure you’re not allocating too much memory (for example, over allocating SGA) and thus taking away the memory from the regular VM space that is needed for client sessions (Oracle shadow “processes”)
• each Large Page must consist of contiguous memory, so for example if the size of the page is extremely large it can happen that due to the memory fragmentation, the process can not allocate all requested large pages (the potential for the problem rise with the number of different running processes and the frequency of starting/stopping the process that is using Large Pages)
• once allocated, the memory pinned as Large Page is not released until the process stops
• Windows settings that control Large Page behavior are stored under registry key:
  HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory
• according to “Windows Internals” book, the default large page sizes depends on architecture used:
  x86 = 4MB (2MB if PAE is enabled)
  x64 = 2MB
  IA64 = 16MB

Thanks to Franck Pachot post on dba-village.com, I re-discovered the Oracle tool for benchmarking I/O subsystem, called ORION (the link to this tool was posted by Tony van Esch on October 2005 on dba-village as well — I did bookmarked that link back then, but flushed from my memory cache soon after).

Orion can be downloaded from OTN:

http://www.oracle.com/technology/software/tech/orion/index.html

It’s available for AIX, Solaris, Linux and Windows. From the same URL you can also download ORION Users Guide – it’s short and to the point.

I tested ORION on Windows XP with 8GB raw partition; basically I followed Users guide.

1) Installed orion with msi package
2) I created raw partition and assigned letter X: to it
3) created mytest.lun file within directory where orion was installed to. File mytest.lun has single line, since I tested with single "disk":

\\.\X:

4) run simple I/O test

D:\ORACLE\ORION>orion -run simple -testname mytest -num_disks 1

ORION: ORacle IO Numbers -- Version 10.2.0.1.0
Test will take approximately 9 minutes
Larger caches may take longer

5) orion writes results in txt/csv files. Follow instructions in Users Guide if you wish to graphically represent the data in your spreadsheet of choice. 

The tool has numerous options, so make sure you read ORION Users Guide!

Original question that triggered Franck reply was: is there equivalent tool for Oracle as it’s for MS SQL Server, SQLIO.EXE (an I/O meter for SQL Server). So, if you’re responsible (doomed) for SQL Servers maintenance as well and want to adequately measure I/O throughput of underlying storage, then SQLIO.EXE is the tool for you.

is not ORA-600 or ORA-7445 or anything that starts with ORA-nnnn at all (not even data corruption!). In my book the worst bug you can hit on RDBMS server (of any kind, not just Oracle!) is the one, that will cause RDBMS server to return the wrong result set – without raising any error at all! These “little greens” are extremely hard to trace/detect in the first place; it usually takes someone who knows data intimately to notice the silly results (and DBA’s nowadays are usually not that familiar with actual semantics of data they manage).

One such bug we recently hit is Bug#4604970 – Wrong results with ‘hash group by’ aggregation enabled. The bug was introduced in 10.2.0.1 and it was fixed in 10.2.0.3.

Unfortunately, we’re stuck with 10.2.0.2 on production server, until we’re ready to patch our 10.2.0.2 production database to the release 10.2.0.3, we’re more or less forced to use the only acceptable workaround suggested in above Metalink note and that is, to turn hash-group-by feature off, by setting “_gby_hash_aggregation_enabled” to FALSE.