Indexing strategies with Jonathan Lewis

Today I was part of a NYOUG seminar about “Indexing Strategies” held by Mr. Jonathan Lewis himself. If somebody doesn’t know Jon Lewis, he is one of the top Oracle gurus on the entire planet with more than 25 years of Oracle experience. He probably used Oracle before I was even born (1985)! The very interesting fact however is that he actually never ever worked for Oracle but still has all the deep inside knowledge. Usually those gurus came out from Oracle or are still working for them. I guess the great amount of years dealing with Oracle helped him there.

However, Jonathan Lewis is also one of my top favorite because he shares his knowledge on his personal blog: http://jonathanlewis.wordpress.com In fact, Jons blog is the only one which I never can catch up with because he posts literally quicker than I got time to read his fabulous posts… reminds me that I should update my Blogroll.

The seminar itself was simply great. It was not free but those 230 bucks where totally worth it. Not only is Jon Lewis a great speaker – was the first time that I actually saw him – he also gave great detailed insights into indexes how they work, what is all possible with them and why Oracle is actually taking an index and much more important when it is not! . Furthermore he combined his sessions with some real world examples that he himself dealt with in the past which makes the whole thing much less theoretical.

Of course I won’t cover the whole seminar here, but I’ll end with some interesting things that I took out of it – some of which I got reminded again, some which were new for me:

• Indexing means: Getting to data quickly
• Indexes focus/compact data
• There is always a Trade-Off between loading and querying performance
• Index maintenance is expensive – it introduces costs
• It’s all about precision
• Non-unique indexes include the rowid automatically – for Oracle there are no non-unique indexes
• Index compression can save a lots of space on repetitive columns
• Index compression means elimination of duplicates
• Primary key constraints don’t necessarily need unique key indexes but only indexes with the columns in the right order
• Reverse indexes scatter entries accros the line, clustering factor can end up terrible
• Don’t duplicate indexes – e.g. FK constraints can share the first columns of another index
• Difference between tables and indexes: Index blocks have to be at the right place
• Don’t trust Oracle – test everything!

 

Exadata, Exadata, Exadata

I’m currently in Menlo Park, California at the old Sun lab from Oracle and do some Exadata testing for work. Everything TOP SECRET of course so I cannot tell anything about it. But what I can do is to share some nice pics from the facilities and the boxes!

Log file sync to death…

What happens if you forget to move our redo logs to a fast disk? Well you will see something like this:

Oracle LogMiner

Oracle provides a powerful out of the box tool called LogMiner within the Oracle database. LogMiner enables you to query online and archived redo log files through simple SQL. The log files of the database contain all changes made to user data or to the database dictionary due to recovery purposes. Based on that LogMiner can be a powerful data auditing as well as a sophisticated data analysis tool. So here is a small example how to have an inside look into the redo data of your database (for a detailed cover of Oracle LogMiner have a look at Chapter 19 of the Oracle Database Utilities guide).

First of all you need to tell LogMiner which log files you want to examine. For this example I just going to use the 3 default redo log files that come with the default installation:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 26 09:30:09 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc v$logfile;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP#                                             NUMBER
 STATUS                                             VARCHAR2(7)
 TYPE                                               VARCHAR2(7)
 MEMBER                                             VARCHAR2(513)
 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> SELECT group#, status, member FROM v$LOGFILE;

 GROUP# STATUS
---------- -------
MEMBER
--------------------------------------------------------------------------------
 3
/home/oracle/app/oracle/oradata/orcl/redo03.log

 2
/home/oracle/app/oracle/oradata/orcl/redo02.log

 1
/home/oracle/app/oracle/oradata/orcl/redo01.log

Next I tell LogMiner to use those three redo log files:

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/app/oracle/oradata/orcl/redo03.log');

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/app/oracle/oradata/orcl/redo02.log');

PL/SQL procedure successfully completed.

SQL> exec DBMS_LOGMNR.ADD_LOGFILE('/home/oracle/app/oracle/oradata/orcl/redo01.log');

PL/SQL procedure successfully completed.

To verify that it worked I’ll query the V$LOGMNR_LOGS view which contains all log files that LogMiner will examine:

SQL> SELECT * FROM V$LOGMNR_LOGS;

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 32
/home/oracle/app/oracle/oradata/orcl/redo02.log
07-DEC-10 07-DEC-10 1229390655 ORCL         754488 30-OCT-09 11.2.0.0.0

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 1         32    2056780    2231224 NO  NO  ONLINE         512
 41424384                                           0

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 33
/home/oracle/app/oracle/oradata/orcl/redo03.log
07-DEC-10 26-JAN-11 1229390655 ORCL         754488 30-OCT-09 11.2.0.0.0

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 1         33    2231224    2252561 NO  NO  ONLINE         512
 7395840                                           0

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 34
/home/oracle/app/oracle/oradata/orcl/redo01.log
26-JAN-11 01-JAN-88 1229390655 ORCL         754488 30-OCT-09 11.2.0.0.0

 LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------
LOW_TIME  HIGH_TIME      DB_ID DB_NAME   RESET_SCN RESET_SCN COMPATIBLE
--------- --------- ---------- -------- ---------- --------- -----------------
 THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE     BLOCKSIZE
---------- ---------- ---------- ---------- --- --- ------- ----------
 FILESIZE INFO                                 STATUS
---------- -------------------------------- ----------
 1         34    2252561 2.8147E+14 NO  NO  ONLINE         512
 0                                           0

Now, after telling LogMiner which log files I want to examine I’m all set to start the mining process.

SQL> exec DBMS_LOGMNR.START_LOGMNR(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

The option “DICT_FROM_ONLINE_CATALOG” tells the LogMiner process to get the object information from the database dictionary directly. If I wouldn’t specify this option then Oracle wouldn’t translate the object ids into the given names. So instead of INSERT INTO TEST (id, text) VALUES… I would see something like INSERT INTO “UNKNOWN”.”OBJ# 96768″(“COL 1″,” COL 2″) VALUES… That of course is not very useful if you want to see what actually happened and so I’m going to use the dictionary to translate the ids into the given names.

So now let’s see how it works:

SQL> CREATE TABLE TEST (id NUMBER, text VARCHAR2(25))
TABLESPACE USERS
LOGGING;

Table created.

SQL> INSERT INTO TEST VALUES (1,'Test1');
INSERT INTO TEST VALUES (2,'Test2');
COMMIT;

1 row created.

SQL>
1 row created.

SQL>
Commit complete.

Note that I defined the LOGGING clause to make sure that LOGGING is enabled for that table. Redo log files are usually used for instance and/or media recovery. The data needed for such operations is automatically recorded in the redo log files. However, the recorded data might not be sufficient to make sense for the user. Specifying the LOGGING clause logs a little bit more information that should be enough for this scenario. For other scenarios however it might be also required to turn on supplemental logging.

All information that LogMiner has collected will be visible in the V$LOGMNR_CONTENTS view: The memory structure for V$LOGMNR_CONTENTS is held in the sessions PGA memory and not in the SGA. Therefore just the session which started LogMiner can also retrieve data from V$LOGMNR_CONTENTS:

SQL> SELECT timestamp, scn, start_scn, operation, seg_owner, seg_name, sql_redo
 FROM V$LOGMNR_CONTENTS
 ORDER BY timestamp, scn;

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419448            DDL
SYS
TEST
CREATE TABLE TEST (id NUMBER, text VARCHAR2(25))
TABLESPACE USERS
LOGGING ;

27-JAN-2011 09:56:50    125419451            UNSUPPORTED
SYS
SEG$
Unsupported

27-JAN-2011 09:56:50    125419451            UNSUPPORTED
SYS
SEG$
Unsupported

27-JAN-2011 09:56:50    125419452  125419441 COMMIT

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
commit;

27-JAN-2011 09:56:50    125419453            START

set transaction read write;

27-JAN-2011 09:56:50    125419453            INTERNAL

27-JAN-2011 09:56:50    125419454  125419453 COMMIT

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
commit;

27-JAN-2011 09:56:50    125419456            START

set transaction read write;

27-JAN-2011 09:56:50    125419456            INSERT
SYS
TEST
insert into "SYS"."TEST"("ID","TEXT") values ('1','Test1');

27-JAN-2011 09:56:50    125419456            INSERT
SYS
TEST

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
insert into "SYS"."TEST"("ID","TEXT") values ('2','Test2');

27-JAN-2011 09:56:50    125419457  125419456 COMMIT

commit;

As you can see the creation of the table as well as the INSERT statements can be retrieved from the redo log files. You can also see that the CREATE TABLE DDL issues 2 COMMITs internally. After that you see the two INSERT statements I executed. And that’s not everything. If I look a little bit further up I can also see what the database executed internally in order to create the table:

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419441            START

set transaction read write;

27-JAN-2011 09:56:50    125419441            INSERT
SYS
OBJ$
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"
,"TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$
","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('171359','17135
9','0','TEST','1',NULL,'2',TO_DATE('27-JAN-2011 09:56:50', 'DD-MON-YYYY HH24:MI:
SS'),TO_DATE('27-JAN-2011 09:56:50', 'DD-MON-YYYY HH24:MI:SS'),TO_DATE('27-JAN-2
011 09:56:50', 'DD-MON-YYYY HH24:MI:SS'),'1',NULL,NULL,'0',NULL,'6','1','0',NULL
,NULL,NULL);

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419441            INTERNAL

27-JAN-2011 09:56:50    125419441            START

set transaction read write;

27-JAN-2011 09:56:50    125419441            INTERNAL

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419444            START

set transaction read write;

27-JAN-2011 09:56:50    125419444            UNSUPPORTED
SYS
SEG$
Unsupported

27-JAN-2011 09:56:50    125419445  125419444 COMMIT

commit;

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419445            UNSUPPORTED
SYS
SEG$
Unsupported

27-JAN-2011 09:56:50    125419446            START

set transaction read write;

27-JAN-2011 09:56:50    125419446  125419441 COMMIT

commit;

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419446            UNSUPPORTED
SYS
TAB$
Unsupported

27-JAN-2011 09:56:50    125419447            UNSUPPORTED
SYS
TAB$
Unsupported

27-JAN-2011 09:56:50    125419447  125419446 COMMIT

commit;

TIMESTAMP                     SCN  START_SCN OPERATION
---------------------- ---------- ---------- --------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
SQL_REDO
--------------------------------------------------------------------------------
27-JAN-2011 09:56:50    125419447            UNSUPPORTED
SYS
COL$
Unsupported

27-JAN-2011 09:56:50    125419447            UNSUPPORTED
SYS
COL$
Unsupported

27-JAN-2011 09:56:50    125419448            DDL
SYS
TEST
CREATE TABLE TEST (id NUMBER, text VARCHAR2(25))
TABLESPACE USERS

As you can see even the INSERT into the internal OBJ$ table got recorded as well as some unsupported operations into SEG$, TAB$ and COL$.

Important to note again is that LogMiner runs just in the current session. The memory structure required runs within the sessions PGA. That means that no other session can retrieve the information from V$LOGMNR_CONTENTS. If you try it from another session you will get: ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

Also important is that you should always call DBMS_LOGMNR.END_LOGMNR(); before you log off. Otherwise you will get a silent ORA-600 [723]… as the bulletin article “The LogMiner utility” (Doc ID: 62508.1) on MOS says:

If you do not call end_logmnr, you will silently get ORA-00600 [723] …
on logoff. This OERI is triggered because the PGA is bigger at logoff than it was at logon, which is considered a space leak. The main problem from a support perspective is that it is silent, i.e. not signalled back to the user screen, because by then they have logged off.

The way to spot LogMiner leaks is that the trace file produced by the OERI 723 will have A PGA heap dumped with many Chunks of type ‘Freeable’ with a description of “KRVD:alh”

2010 in review

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

The Blog-Health-o-Meter™ reads Fresher than ever.

Crunchy numbers

The average container ship can carry about 4,500 containers. This blog was viewed about 18,000 times in 2010. If each view were a shipping container, your blog would have filled about 4 fully loaded ships.

 

In 2010, there were 12 new posts, growing the total archive of this blog to 89 posts. There were 11 pictures uploaded, taking up a total of 164kb. That’s about a picture per month.

The busiest day of the year was December 10th with 127 views. The most popular post that day was BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP.

Where did they come from?

The top referring sites in 2010 were google.co.in, google.com, forums.oracle.com, oracleportal.it, and yandex.ru.

Some visitors came searching, mostly for bulk collect, private strand flush not complete, bulk collect forall, bulk collect in cursor, and enq: dx – contention.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

BULK COLLECT & FORALL vs. CURSOR & FOR-LOOP September 2007
27 comments

2

Oracle 10g enqueue waits May 2009
1 comment

3

Private strand flush not complete October 2008

4

Finally a good tool for Data Modeling: Oracle SqlDeveloper Data Modeling November 2008
6 comments

5

Prepared statement in batch mode vs. FORALL in PL/SQL November 2007

Oracle lock types

Did you know that there are 202 different lock types in Oracle 11.2 but only 4 of them which are a direct result of a SQL statement? Here is the list:

TYPE	NAME	IS_USER	DESCRIPTION
TM	DML	YES	Synchronizes accesses to an object
TX	Transaction	YES	Lock held by a transaction to allow other transactions to wait for it
UL	User-defined	YES	Lock used by user applications
ZH	Compression Analyzer	YES	Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
AB	Auto BMR	NO	A general class of locks used by auto BMR for various purposes
AD	ASM Disk AU Lock	NO	Synchronizes accesses to a specific ASM disk AU
AE	Edition Lock	NO	Prevent Dropping an edition in use
AF	Advisor Framework	NO	This enqueue is used to serialize access to an advisor task
AG	Analytic Workspace Generation	NO	Synchronizes generation use of a particular workspace
AK	GES Deadlock Test	NO	Lock used for internal testing
AM	ASM Enqueue	NO	ASM instance general-purpose enqueue
AO	MultiWriter Object Access	NO	Synchronizes access to objects and scalar variables
AR	ASM Relocation Lock	NO	Protects locked extent pointers during ASM file relocation
AS	Service Operations	NO	Synchronizes new service activation
AT	Alter Tablespace	NO	Serializes ‘alter tablespace’ operations
AU	Audit index file	NO	Lock held to synchronize access XML to audit index file
AV	ASM volume locks	NO	id1 is for persistent DG number locking. id2 is for other volume serialization
AW	Analytic Workspace	NO	Synchronizes access to Analytic Workspace resources
AY	KSXA Test Affinity Dictionary	NO	Affinity Dictionary test affinity synchronization
BB	Global Transaction Branch	NO	2PC distributed transaction branch across RAC instances
BF	BLOOM FILTER	NO	Synchronize access to a bloom filter in a parallel statement
BR	Backup/Restore	NO	Lock held by a backup/restore operation to allow other operations to wait for it
CA	Calibration	NO	Lock used by IO Calibration
CF	Controlfile Transaction	NO	Synchronizes accesses to the controlfile
CI	Cross-Instance Call Invocation	NO	Coordinates cross-instance function invocations
CL	Label Security cache	NO	Synchronizes accesses to label cache and label tables
CM	ASM Instance Enqueue	NO	ASM instance and gate enqueue
CN	KTCN REG enq	NO	Enqueue held for registrations for change notifications
CO	KTUCLO Master Slave enq	NO	Enqueue held for determining Master Slaves
CQ	Cleanup querycache registrations	NO	Serializes access to cleanup client query cache registrations
CR	Reuse Block Range	NO	Coordinates checkpointing of block range reuse
CT	Block Change Tracking	NO	A general class of locks used by change tracking for various purposes
CU	Cursor	NO	Recovers cursors in case of death while compiling
DB	DbsDriver	NO	Synchronizes modification of database wide supplementallogging attributes
DD	ASM Local Disk Group	NO	Synchronizes local accesses to ASM disk groups
DF	Datafile Online in RAC	NO	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG	ASM Disk Group Modification	NO	Synchronizes accesses to ASM disk groups
DI	GES Internal	NO	Coordinates Global Enqueue Service deadlock detection
DL	Direct Loader Index Creation	NO	Lock to prevent index DDL during direct load
DM	Database Mount/Open	NO	Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
DN	Diskgroup number generator	NO	Serializes Group number generations
DO	ASM Disk Online Lock	NO	Synchronizes disk onlines and their recovery
DP	LDAP Parameter	NO	Synchronizes access to LDAP parameters
DQ	ASM RBAL doorbell	NO	Inter-RBAL process metadata invalidation notification
DR	Distributed Recovery	NO	Serializes the active distributed recovery operation
DS	Database Suspend	NO	Prevents a database suspend during LMON reconfiguration
DT	Default Temporary Tablespace	NO	Serializes changing the default temporary table space and user creation
DV	Diana Versioning	NO	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW	In memory Dispenser	NO	Serialize in memory dispenser operations
DX	Distributed Transaction	NO	Serializes tightly coupled distributed transaction branches
E	Library Cache Lock 2	NO	Synchronizes accesses to library cache objects
FA	ASM File Access Lock	NO	Synchronizes accesses to open ASM files
FB	Format Block	NO	Ensures that only one process can format data blocks in auto segment space managed tablespaces
FC	Disk Group Chunk Mount	NO	controls access to an ACD chunk
FD	Flashback Database	NO	Coordinate flashback database
FE	KTFA Recovery	NO	Flashback archive Enqueue to serialize recovery
FG	ACD Relocation Gate Enqueue	NO	ACD relocation serialization
FL	Flashback database log	NO	Synchronize access to flashback database log
FM	File Mapping	NO	Synchronizes access to global file mapping state
FP	File Object	NO	Synchronizes various File Object(FOB) operations
FR	Disk Group Recovery	NO	enqueue to control ASM recovery
FS	File Set / Dictionary Check	NO	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT	Disk Group Redo Generation	NO	controls the privilege to generate redo in a thread
FU	DBFUS	NO	This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
FW	Flashback Writer	NO	Coordinate RVWR on multiple instances
FX	ACD Xtnt Info CIC	NO	serialize ACD relocation CIC
FZ	ASM Freezing Cache Lock	NO	Freezes ASM Cache for a diskgroup
G	Library Cache Pin 2	NO	Synchronizes accesses to the contents of library cache objects
HD	ASM Disk Header	NO	Serializes accesses to ASM SGA data structures
HP	Queue Page	NO	Synchronizes accesses to queue pages
HQ	Hash Queue	NO	Synchronizes the creation of new queue IDs
HV	Direct Loader High Water Mark	NO	Lock used to broker the high water mark during parallel inserts
HW	Segment High Water Mark	NO	Lock used to broker the high water mark during parallel inserts
IA	Internal	NO
ID	NID	NO	Lock held by a NID operation to allow other operations to wait for it
IL	Label Security	NO	Synchronizes accesses to internal label data structures
IM	Kti blr lock	NO	Serializes block recovery for an IMU txn
IR	Instance Recovery	NO	Synchronizes instance recovery
IS	Instance State	NO	Enqueue used to synchronize instance state changes
IT	In-Mem Temp Table Meta Creation	NO	Synchronizes accesses to a temp object’s metadata
IV	Library Cache Invalidation	NO	Synchronizes library cache object invalidations across instances
IZ	INSTANCE LOCK	NO	Proctects the lock name space used by the instance
JD	Job Queue Date	NO	Synchronizes dates between job queue coordinator and slave processes
JI	Materialized View	NO	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ	Job Queue	NO	Lock to prevent multiple instances from running a single job
JS	Job Scheduler	NO	Synchronizes accesses to the job cache
JX	SQL STATEMENT QUEUE	NO	synchronize SQL statement queue operations
KD	Scheduler Master DBRM	NO	Determine DBRM master
KE	ASM Cached Attributes	NO	Synchronization of ASM cached attributes
KK	Kick Instance to Switch Logs	NO	Lock held by open redo thread, used by other instances to force a log switch
KL	LOB KSI LOCK	NO	KSI lock for buffer cache and wgc concurrency
KM	Scheduler	NO	Synchronizes various Resource Manager operations
KO	Multiple Object Checkpoint	NO	Coordinates checkpointing of multiple objects
KP	Kupp Process Startup	NO	Synchronizes kupp process startup
KQ	ASM Attributes Enque	NO	Single Inst Sync of ASM attributes
KT	Scheduler Plan	NO	Synchronizes accesses to the current Resource Manager plan
L	Library Cache Lock 1	NO	Synchronizes accesses to library cache objects
MD	Materialized View Log DDL	NO	Lock held during materialized view log DDL statements
MH	AQ Notification Mail Host	NO	Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK	Master Key	NO	Serializes enc$ access
ML	AQ Notification Mail Port	NO	Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN	LogMiner	NO	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO	MMON restricted session	NO	Serialize MMON operations for restricted sessions
MR	Media Recovery	NO	Lock used to coordinate media recovery with other uses of datafiles
MS	Materialized View Refresh Log	NO	Lock held during materialized view refresh to setup MV log
MV	Online Datafile Move	NO	Synchronizes online datafile move operation or cleanup
MW	MWIN Schedule	NO	This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
MX	ksz synch	NO	Used to synchronize storage server info across all nodes
N	Library Cache Pin 1	NO	Synchronizes accesses to the contents of library cache objects
OC	Outline Cache	NO	Synchronizes write accesses to the outline cache
OD	Online DDLs	NO	Lock to prevent concurrent online DDLs
OL	Outline Name	NO	Synchronizes accesses to a particular outline name
OQ	OLAPI Histories	NO	Synchronizes access to olapi history resources
OW	Encryption Wallet	NO	Serializes wallet initialization and access
PD	Property Lock	NO	Serializes property update
PE	Parameter	NO	Synchronizes system parameter updates
PF	Password File	NO	Synchronizes accesses to the password file
PG	Global Parameter	NO	Synchronizes global system parameter updates
PH	AQ Notification Proxy	NO	Lock used for recovery when setting Proxy for AQ HTTP notifications
PI	Remote PX Process Spawn Status	NO	Communicates remote Parallel Execution Server Process creation status
PL	Transportable Tablespace	NO	Coordinates plug-in operation of transportable tablespaces
PM	ASM PST Signalling	NO	Signals inter-instance access to ASM PST metadata
PR	Process Startup	NO	Synchronizes process startup
PS	PX Process Reservation	NO	Parallel Execution Server Process reservation and synchronization
PT	ASM Partnership and Status Table	NO	Gates inter-node synchronization of ASM PST metadata
PV	KSV slave startup	NO	Synchronizes slave start_shut
PW	Buffer Cache PreWarm	NO	Coordinates Direct Loads with Prewarmed cache buffers
Q	Row Cache	NO	Coordinates updates and accesses to row cache objects
RB	ASM Rollback Recovery	NO	Serializes ASM rollback recovery operations
RC	Result Cache: Enqueue	NO	Accessing a result in the result-set cache
RD	RAC Load	NO	synchronize update of RAC load info
RE	Block Repair/Resilvering	NO	Synchronizes block repair/mirror resilvering operations
RF	Data Guard Broker	NO	Synchronizes broker lock operation involving lock value
RK	wallet_set_mkey	NO	Serializes wallet master key rekey
RL	RAC Encryption Wallet Lock	NO	RAC encryption wallet lock
RM	GES Resource Remastering	NO	Coordinates Global Enqueue Service resource remastering
RN	Redo Log Nab Computation	NO	Coordinates nab computations of online logs during recovery
RO	Multiple Object Reuse	NO	Coordinates flushing of multiple objects
RP	Resilver / Repair	NO	Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR	Workload Capture and Replay	NO	Prevents concurrent invokers of DBMS_WORKLOAD_*
RS	Reclaimable Space	NO	Lock held by a space reclaimable operation to allow other operations to wait for it
RT	Redo Thread	NO	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU	Rolling Migration	NO	Serilized rolling migration operations
RW	Materialized View Flags	NO	Lock used when creating or readingmaterialized view flags in detail tables
RX	ASM Extent Relocation Lock	NO	Synchronizes relocating ASM extents
SB	LogicalStandby	NO	Synchronizes Logical Standby metadata operations
SC	System Change Number	NO	Coordinates system change number generation on multiple instances
SE	Session Migration	NO	Lock used by transparent session migration
SF	AQ Notification Sender	NO	Lock used for recovery when setting Sender for AQ e-mail notifications
SH	Active Session History Flushing	NO	To prevent multiple concurrent flushers
SI	Streams Table Instantiation	NO	Prevents muiltiple streams table instantiations
SJ	KTSJ Slave Task Cancel	NO	Serializes cancelling task executed by slave process
SK	Shrink Segment	NO	Serialize shrink of a segment
SL	Serialize Lock request	NO	Request serialization to LCK0
SM	SMON Serialization	NO	Lock to check SMON global work in RAC
SO	Shared Object	NO	Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
SP	Spare Enqueue	NO	Spare enqueue for one-off backports
SQ	Sequence Cache	NO	Lock to ensure that only one process can replenish the sequence cache
SR	Synchronized Replication	NO	Coordinates replication / streams operations
SS	Sort Segment	NO	Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST	Space Transaction	NO	Synchronizes space management activities in dictionary-managed tablespaces
SU	SaveUndo Segment	NO	Serializes access to SaveUndo Segment
SV	Sequence Ordering	NO	Lock to ensure ordered sequence allocation in RAC mode
SW	Suspend Writes	NO	Coordinates the ‘alter system suspend’ operation
TA	Instance Undo	NO	Serializes operations on undo segments and undo tablespaces
TB	SQL Tuning Base Existence Cache	NO	Synchronizes writes to the SQL Tuning Base Existence Cache
TC	Tablespace Checkpoint	NO	Lock held to guarantee uniqueness of a tablespace checkpoint
TD	KTF map table enqueue	NO	Serializes updates and inserts to the SMON_SCN_TIME mapping table
TE	KTF broadcast	NO	Serializes broadcasts for flushes to SMON_SCN_TIME
TF	Temporary File	NO	Serializes dropping of a temporary file
TH	Threshold Chain	NO	Serializes threshold in-memory chain access
TK	Auto Task Serialization	NO	Prevents more than one AutoTask Background Slave from being spawned
TL	Log Lock	NO	Serializes threshold log table read and update
TO	Temp Object	NO	Synchronizes DDL and DML operations on a temp object
TP	Runtime Fixed Table Purge	NO	Lock used for fixed runtime table purge and definition
TQ	Queue table enqueue	NO	Synchronizes access to queue table
TS	Temporary Segment	NO	Serializes accesses to temp segments
TT	Tablespace	NO	Serializes DDL operations on tablespaces
TW	Cross-Instance Transaction	NO	Lock held by one instance to wait for transactions on all instances to finish
US	Undo Segment	NO	Lock held to perform DDL on the undo segment
V	Library Cache Lock 3	NO	Synchronizes accesses to library cache objects
WA	AQ Notification Watermark	NO	Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF	AWR Flush	NO	This enqueue is used to serialize the flushing of snapshots
WG	Write gather local enqueue	NO	Long term lock on wgc file state
WL	Being Written Redo Log	NO	Coordinates access to redo log files and archive logs
WM	WLM Plan Operations	NO	Synchronizes new WLM Plan activation
WP	AWR Purge	NO	This enqueue handles concurrency between purging and baselines
WR	LNS archiving log	NO	Coordinates access to logs by Async LNS and ARCH/FG
WS	LogWriter Standby	NO	Used by LogWriter to coordinate communication to standby databases
XB	ASM Group Block lock	NO	Prevents client diskgroup use during storage reconfiguration
XC	XDB Configuration	NO	Synchronization access to XDB configuration
XD	Auto Online Exadata disks	NO	Serialize Auto Management of Exadata disks
XH	AQ Notification No-Proxy	NO	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL	ASM Extent Fault Lock	NO	Keep multiple processes from faulting in the same extent chunk
XQ	ASM Extent Relocation Enqueue	NO	ASM extent relocation
XR	Quiesce / Force Logging	NO	Lock held during database quiesce or for database force logging
XY	Internal Test	NO	Lock used for internal testing
Y	Library Cache Pin 3	NO	Synchronizes accesses to the contents of library cache objects
ZA	Audit Partition	NO	Lock held for adding partition to Aud table
ZF	FGA Partition	NO	Lock held for adding partition to Fga table
ZG	File Group	NO	Coordinates file group operations
ZZ	Global Context Action	NO	Lock held for updating Global context hash tables

The SQL to retrieve them:

SELECT type, name, is_user, description FROM V$LOCK_TYPE ORDER BY is_user DESC, type;

Why my job is safe

I spent the last couple of days with coding a new registration module for a private website that I administrate. The requirements were the very basic ones:

• Make an HTML form
• Validate the inputs
• Send a confirmation email with a link
• On link activation: Retrieve the data and write it into a table in the database

As those requirements are common in so many registration forms all over the web, I thought: Before coding something again, just have a look at Google and see if  you can find something useful to embed. And I found stuff, lots of stuff but surprisingly every example I found did the following:

• HTML form
• Write data into the table with an “active” column set to false
• Send a confirmation email with an link with an unique identifier for the inserted row (most times a md5 hash value)
• On link activation: Retrieve the unique identifier
• Update the row in the database with active flag set to true

And, at least some examples, took also care about the house keeping and cleaned the inactive rows from time to time.

Well, this does the job of course but the way how it does it is simply dirty – very dirty!

From the functional aspect there are following issues:

• Every SQL statement for retrieving the active data has to make sure to include ” AND active = TRUE” in the WHERE clause
• If an user is unlucky, the cleanup process removes his data before he can click the activation link if the process doesn’t implement a time range

From the performance side however:

All I want to do is:

• Validate the data from the form
• Save it temporarily until the link is clicked or the data gets expired
• Write it into the database

With those solutions found on the internet however I would have done following:

• Validate the data
• Write it into the database (with an column extension for an active flag)
• Retrieve the data again and update it to set it active
• Create a cleanup job to purge inactive data
• Extend all my SQLs against that table with a “AND active = TRUE” WHERE clause

So I have more steps, more unnecessary hops to the database, probably maintain another index over time for the cleanup job. Sometimes I wonder why programmers don’t think first before the code. Of course many roads lead to Rome but that doesn’t mean that you should just take a random one. But as long as there are programmers out there who don’t think or don’t think hard enough there will be always performance problems and people like me needed to solve them. So I guess I shouldn’t complain but my dream of the perfect IT world is still alive…

Well, the webpage in my case is made in PHP which has one nice feature called: Sessions
That does the job perfectly fine and most important: Clean! It is enabled by default, allows you to store data within a session and includes also an expire functionality which is also enabled by default. It supports two ways of storing the session id: Either within a cookie in the browser (enabled by default) or you can also pass the session id over POST and GET requests. As I use a confirmation email to activate the registration I just had to make sure to deactivate the cookie storage feature first as I pass the session id over a link/GET request. I wouldn’t have to deactivate the cookie storage from a functional point of view as the cookie would be just never read again and would expire some time but from security and the “do it right” aspect this has to be deactivated. So all I did was following:

// Disable cookie storage
ini_set("session.use_cookies",0);
// Start the session
session_start();

// Save the data within the session
$_SESSION["Title"] = $Title;
$_SESSION["FirstName"] = $FistName;
$_SESSION["LastName"] = $LastName;

//Send the email with the activation link
if (mail($Email, "Please activate your registration", "http://www.mywebsite.com/activate.php?sid=".htmlspecialchars (session_id()))
{
 // If sending the mail was successful, store all session data and close the session
 session_write_close();
}
else
{
 // If sending the mail failed, report an error, delete the session data and destroy the session!
 // Error reporting....
 session_unset();
 session_destroy();
}

And all the activation.php file does, is:

// Disable cookie storage
ini_set("session.use_cookies",0);
// Set the session id from GET request
session_id($_GET["sid"]);
// Start session
session_start();
// Get variables
$Title = $_SESSION["Title"];
$FistName = $_SESSION["FirstName"];
$LastName = $_SESSION["LastName"];

// Connect to database
$hDB = @mysql_connect("localhost", "user","password");
mysql_select_db("myDB", $hDB);

// Insert data into database
if (mysql_query("INSERT INTO table (Title, FirstName, LastName) VALUES ('".mysql_real_escape_string($Title)."','".mysql_real_escape_string($FirstName)."','".mysql_real_escape_string($LastName)."'")
{
 // Destroy session
 session_unset();
 session_destroy();
}

Oracle 11gR2 enqueue waits

Over 1 and a half year ago I posted the enqueue waits for Oracle 10g. Well, a lot has changed in 11g including the enqueue waits. This is the new list of enqueue waits in 11gR2:

Group	Enqueue Type	Description
Auto BMR	enq: AB – ABMR process initialized	Lock held to ensure that ABMR process is initialized
Auto BMR	enq: AB – ABMR process start/stop	Lock held to ensure that only one ABMR is started in the cluster
ASM Disk AU Lock	enq: AD – allocate AU	Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock	enq: AD – deallocate AU	Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock	enq: AD – relocate AU	Synchronizes accesses to a specific ASM disk AU
Edition Lock	enq: AE – lock	Prevent Dropping an edition in use
Advisor Framework	enq: AF – task serialization	This enqueue is used to serialize access to an advisor task
Analytic Workspace Generation	enq: AG – contention	Synchronizes generation use of a particular workspace
ASM Enqueue	enq: AM – ASM ACD Relocation	Block ASM cache freeze
ASM Enqueue	enq: AM – ASM Amdu Dump	Allow only one AMDU dump when block read failure
ASM Enqueue	enq: AM – ASM File Destroy	Prevent same file deletion race
ASM Enqueue	enq: AM – ASM Password File Update	Allow one ASM password file update per cluster at a time
ASM Enqueue	enq: AM – ASM User	Prevents a user from being dropped if it owns any open files
ASM Enqueue	enq: AM – ASM cache freeze	Start ASM cache freeze
ASM Enqueue	enq: AM – PST split check	Synchronizes check for Storage (PST) split in disk groups
ASM Enqueue	enq: AM – background COD reservation	Reserve a background COD entry
ASM Enqueue	enq: AM – client registration	Registers DB instance to ASM client state object hash
ASM Enqueue	enq: AM – disk offline	Synchronizes disk offlines
ASM Enqueue	enq: AM – group block	ASM group block
ASM Enqueue	enq: AM – group use	Client group use
ASM Enqueue	enq: AM – rollback COD reservation	Reserve a rollback COD entry
ASM Enqueue	enq: AM – shutdown	Prevent DB instance registration during ASM instance shutdown
MultiWriter Object Access	enq: AO – contention	Synchornizes access to objects and scalar variables
Service Operations	enq: AS – service activation	Synchronizes new service activation
Alter Tablespace	enq: AT – contention	Serializes ‘alter tablespace’ operations
Audit index file	enq: AU – audit index file	lock held to operate on the audit index file
ASM volume locks	enq: AV – AVD client registration	Serialize inst reg and first DG use
ASM volume locks	enq: AV – add/enable first volume in DG	Serialize taking the AVD DG enqueue
ASM volume locks	enq: AV – persistent DG number	prevent DG number collisions
ASM volume locks	enq: AV – volume relocate	Serialize relocating volume extents
Analytic Workspace	enq: AW – AW generation lock	In-use generation state for a particular workspace
Analytic Workspace	enq: AW – AW state lock	Row lock synchronization for the AW$ table
Analytic Workspace	enq: AW – AW$ table lock	Global access synchronization to the AW$ table
Analytic Workspace	enq: AW – user access for AW	Synchronizes user accesses to a particular workspace
KSXA Test Affinity Dictionary	enq: AY – contention	Affinity Dictionary test affinity synchronization
Global Transaction Branch	enq: BB – 2PC across RAC instances	2PC distributed transaction branch across RAC instances
BLOOM FILTER	enq: BF – PMON Join Filter cleanup	PMON bloom filter recovery
BLOOM FILTER	enq: BF – allocation contention	Allocate a bloom filter in a parallel statement
Backup/Restore	enq: BR – file shrink	Lock held to prevent file from decreasing in physical size during RMAN backup
Backup/Restore	enq: BR – multi-section restore header	Lock held to serialize file header access during multi-section restore
Backup/Restore	enq: BR – multi-section restore section	Lock held to serialize section access during multi-section restore
Backup/Restore	enq: BR – perform autobackup	Lock held to perform a new controlfile autobackup
Backup/Restore	enq: BR – proxy-copy	Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
Backup/Restore	enq: BR – request autobackup	Lock held to request controlfile autobackups
Backup/Restore	enq: BR – space info datafile hdr update	Lock held to prevent multiple process to update the headers at the same time
Calibration	enq: CA – contention	Synchronizes various IO calibration runs
Controlfile Transaction	enq: CF – contention	Synchronizes accesses to the controlfile
Cross-Instance Call Invocation	enq: CI – contention	Coordinates cross-instance function invocations
Label Security cache	enq: CL – compare labels	Synchronizes accesses to label cache for label comparison
Label Security cache	enq: CL – drop label	Synchronizes accesses to label cache when dropping a label
ASM Instance Enqueue	enq: CM – gate	serialize access to instance enqueue
ASM Instance Enqueue	enq: CM – instance	indicate ASM diskgroup is mounted
KTCN REG enq	enq: CN – race with init	during descriptor initialization
KTCN REG enq	enq: CN – race with reg	during transaction commit to see concurrent registrations
KTCN REG enq	enq: CN – race with txn	during registration
KTUCLO Master Slave enq	enq: CO – master slave det	enqueue held be Master in Cleanout Optim
Cleanup querycache registrations	enq: CQ – contention	Serializes access to cleanup client query cache registrations
Reuse Block Range	enq: CR – block range reuse ckpt	Coordinates fast block range reuse ckpt
Block Change Tracking	enq: CT – CTWR process start/stop	Lock held to ensure that only one CTWR process is started in a single instance
Block Change Tracking	enq: CT – change stream ownership	Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
Block Change Tracking	enq: CT – global space management	Lock held during change tracking space management operations that affect the entire change tracking file
Block Change Tracking	enq: CT – local space management	Lock held during change tracking space management operations that affect just the data for one thread
Block Change Tracking	enq: CT – reading	Lock held to ensure that change tracking data remains in existence until a reader is done with it
Block Change Tracking	enq: CT – state	Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
Block Change Tracking	enq: CT – state change gate 1	Lock held while enabling or disabling change tracking in RAC
Block Change Tracking	enq: CT – state change gate 2	Lock held while enabling or disabling change tracking in RAC
Cursor	enq: CU – contention	Recovers cursors in case of death while compiling
DbsDriver	enq: DB – contention	Synchronizes modification of database wide supplementallogging attributes
ASM Local Disk Group	enq: DD – contention	Synchronizes local accesses to ASM disk groups
Datafile Online in RAC	enq: DF – contention	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
ASM Disk Group Modification	enq: DG – contention	Synchronizes accesses to ASM disk groups
Direct Loader Index Creation	enq: DL – contention	Lock to prevent index DDL during direct load
Database Mount/Open	enq: DM – contention	Enqueue held by foreground or DBWR to syncrhonize database mount/open with other operations
Diskgroup number generator	enq: DN – contention	Serializes group number generations
ASM Disk Online Lock	enq: DO – Staleness Registry create	Synchronizes Staleness Registry creation
ASM Disk Online Lock	enq: DO – disk online	Synchronizes disk onlines and their recovery
ASM Disk Online Lock	enq: DO – disk online operation	Represents an active disk online operation
ASM Disk Online Lock	enq: DO – disk online recovery	Synchronizes disk onlines and their recovery
ASM Disk Online Lock	enq: DO – startup of MARK process	Synchronizes startup of MARK process
LDAP Parameter	enq: DP – contention	Synchronizes access to LDAP parameters
Distributed Recovery	enq: DR – contention	Serializes the active distributed recovery operation
Database Suspend	enq: DS – contention	Prevents a database suspend during LMON reconfiguration
Default Temporary Tablespace	enq: DT – contention	Serializes changing the default temporary table spaceand user creation
Diana Versioning	enq: DV – contention	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
In memory Dispenser	enq: DW – contention	Serialize in memory dispenser operations
Distributed Transaction	enq: DX – contention	Serializes tightly coupled distributed transaction branches
ASM File Access Lock	enq: FA – access file	Synchronizes accesses to open ASM files
Format Block	enq: FB – contention	Ensures that only one process can format data blcoks in auto segment space managed tablespaces
Disk Group Chunk Mount	enq: FC – open an ACD thread	LGWR opens an ACD thread
Disk Group Chunk Mount	enq: FC – recover an ACD thread	SMON recovers an ACD thread
Flashback Database	enq: FD – Flashback coordinator	Synchronization
Flashback Database	enq: FD – Flashback logical operations	Synchronization
Flashback Database	enq: FD – Flashback on/off	Synchronization
Flashback Database	enq: FD – Marker generation	Synchronization
Flashback Database	enq: FD – Restore point create/drop	Synchronization
Flashback Database	enq: FD – Tablespace flashback on/off	Synchronization
KTFA Recovery	enq: FE – contention	Serializes flashback archive recovery
ACD Relocation Gate Enqueue	enq: FG – FG redo generation enq race	resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue	enq: FG – LGWR redo generation enq race	resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue	enq: FG – serialize ACD relocate	only 1 process in the cluster may do ACD relocation in a disk group
Flashback database log	enq: FL – Flashback database log	Synchronization
Flashback database log	enq: FL – Flashback db command	Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
File Mapping	enq: FM – contention	Synchronizes access to global file mapping state
File Object	enq: FP – global fob contention	Synchronizes various File Object(FOB) operations
Disk Group Recovery	enq: FR – contention	begin recovery of disk group
Disk Group Recovery	enq: FR – recover the thread	wait for lock domain detach
Disk Group Recovery	enq: FR – use the thread	indicate this ACD thread is alive
File Set / Dictionary Check	enq: FS – contention	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
Disk Group Redo Generation	enq: FT – allow LGWR writes	allow LGWR to generate redo in this thread
Disk Group Redo Generation	enq: FT – disable LGWR writes	prevent LGWR from generating redo in this thread
DBFUS	enq: FU – contention	This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
ACD Xtnt Info CIC	enq: FX – issue ACD Xtnt Relocation CIC	ARB relocates ACD extent
ASM Disk Header	enq: HD – contention	Serializes accesses to ASM SGA data structures
Queue Page	enq: HP – contention	Synchronizes accesses to queue pages
Hash Queue	enq: HQ – contention	Synchronizes the creation of new queue IDs
Direct Loader High Water Mark	enq: HV – contention	Lock used to broker the high water mark during parallel inserts
Segment High Water Mark	enq: HW – contention	Lock used to broker the high water mark during parallel inserts
Internal	enq: IA – contention
NID	enq: ID – contention	Lock held to prevent other processes from performing controlfile transaction while NID is running
Label Security	enq: IL – contention	Synchronizes accesses to internal label data structures
Kti blr lock	enq: IM – contention for blr	Serializes block recovery for IMU txn
Instance Recovery	enq: IR – contention	Synchronizes instance recovery
Instance Recovery	enq: IR – contention2	Synchronizes parallel instance recovery and shutdown immediate
Instance State	enq: IS – contention	Enqueue used to synchronize instance state changes
In-Mem Temp Table Meta Creation	enq: IT – contention	Synchronizes accesses to a temp object’s metadata
Job Queue Date	enq: JD – contention	Synchronizes dates between job queue coordinator and slave processes
Materialized View	enq: JI – contention	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Job Queue	enq: JQ – contention	Lock to prevent multiple instances from running a single job
Job Scheduler	enq: JS – aq sync	Scheduler evt code and AQ sync
Job Scheduler	enq: JS – contention	Synchronizes accesses to the job cache
Job Scheduler	enq: JS – evt notify	Lock got during event notification
Job Scheduler	enq: JS – evtsub add	Lock got when adding subscriber to event q
Job Scheduler	enq: JS – evtsub drop	Lock got when dropping subscriber to event q
Job Scheduler	enq: JS – job recov lock	Lock to recover jobs running on crashed RAC inst
Job Scheduler	enq: JS – job run lock – synchronize	Lock to prevent job from running elsewhere
Job Scheduler	enq: JS – q mem clnup lck	Lock obtained when cleaning up q memory
Job Scheduler	enq: JS – queue lock	Lock on internal scheduler queue
Job Scheduler	enq: JS – sch locl enqs	Scheduler non-global enqueues
Job Scheduler	enq: JS – wdw op	Lock got when doing window open/close
SQL STATEMENT QUEUE	enq: JX – SQL statement queue	statement
SQL STATEMENT QUEUE	enq: JX – cleanup of queue	release SQL statement resources
Scheduler Master DBRM	enq: KD – determine DBRM master	Determine DBRM master
Scheduler	enq: KM – contention	Synchronizes various Resource Manager operations
Multiple Object Checkpoint	enq: KO – fast object checkpoint	Coordinates fast object checkpoint
Kupp Process Startup	enq: KP – contention	Synchronizes kupp process startup
ASM Attributes Enque	enq: KQ – access ASM attribute	Synchronization of ASM cached attributes
Scheduler Plan	enq: KT – contention	Synchronizes accesses to the current Resource Manager plan
Materialized View Log DDL	enq: MD – contention	Lock held during materialized view log DDL statements
AQ Notification Mail Host	enq: MH – contention	Lock used for recovery when setting Mail Host for AQ e-mail notifications
Master Key	enq: MK – contention	changing values in enc$
AQ Notification Mail Port	enq: ML – contention	Lock used for recovery when setting Mail Port for AQ e-mail notifications
LogMiner	enq: MN – contention	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MMON restricted session	enq: MO – contention	Serialize MMON operations for restricted sessions
Media Recovery	enq: MR – contention	Lock used to coordinate media recovery with other uses of datafiles
Media Recovery	enq: MR – standby role transition	Lock used to disallow concurrent standby role transition attempt
Materialized View Refresh Log	enq: MS – contention	Lock held during materialized view refresh to setup MV log
Online Datafile Move	enq: MV – datafile move	Held during online datafile move operation or cleanup
MWIN Schedule	enq: MW – contention	This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
ksz synch	enq: MX – sync storage server info	Lock held to generate a response to the storage server information request when an instance is starting up
Outline Cache	enq: OC – contention	Synchronizes write accesses to the outline cache
Online DDLs	enq: OD – Serializing DDLs	Lock to prevent concurrent online DDLs
Outline Name	enq: OL – contention	Synchronizes accesses to a particular outline name
OLAPI Histories	enq: OQ – xsoq*histrecb	Synchronizes access to olapi history parameter CB
OLAPI Histories	enq: OQ – xsoqhiAlloc	Synchronizes access to olapi history allocation
OLAPI Histories	enq: OQ – xsoqhiClose	Synchronizes access to olapi history closing
OLAPI Histories	enq: OQ – xsoqhiFlush	Synchronizes access to olapi history flushing
OLAPI Histories	enq: OQ – xsoqhistrecb	Synchronizes access to olapi history globals
Encryption Wallet	enq: OW – initialization	initializing the wallet context
Encryption Wallet	enq: OW – termination	terminate the wallet context
Property Lock	enq: PD – contention	Prevents others from updating the same property
Parameter	enq: PE – contention	Synchronizes system parameter updates
Password File	enq: PF – contention	Synchronizes accesses to the password file
Global Parameter	enq: PG – contention	Synchronizes global system parameter updates
AQ Notification Proxy	enq: PH – contention	Lock used for recovery when setting Proxy for AQ HTTP notifications
Remote PX Process Spawn Status	enq: PI – contention	Communicates remote Parallel Execution Server Process creation status
Transportable Tablespace	enq: PL – contention	Coordinates plug-in operation of transportable tablespaces
Process Startup	enq: PR – contention	Synchronizes process startup
PX Process Reservation	enq: PS – contention	Parallel Execution Server Process reservation and synchronization
ASM Partnership and Status Table	enq: PT – contention	Synchronizes access to ASM PST metadata
KSV slave startup	enq: PV – syncshut	Synchronizes instance shutdown_slvstart
KSV slave startup	enq: PV – syncstart	Synchronizes slave start_shutdown
Buffer Cache PreWarm	enq: PW – flush prewarm buffers	Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
Buffer Cache PreWarm	enq: PW – perwarm status in dbw0	DBWR 0 holds enqueue indicating prewarmed buffers present in cache
ASM Rollback Recovery	enq: RB – contention	Serializes ASM rollback recovery operations
Result Cache: Enqueue	enq: RC – Result Cache: Contention	Coordinates access to a result-set
RAC Load	enq: RD – RAC load	update RAC load info
Block Repair/Resilvering	enq: RE – block repair contention	Synchronize block repair/resilvering operations
Data Guard Broker	enq: RF – DG Broker Current File ID	Identifies which configuration metadata file is current
Data Guard Broker	enq: RF – FSFO Observer Heartbeat	Captures recent Fast-Start Failover Observer heartbeat information
Data Guard Broker	enq: RF – RF – Database Automatic Disable	Means for detecting when database is being automatically disabled
Data Guard Broker	enq: RF – atomicity	Ensures atomicity of log transport setup
Data Guard Broker	enq: RF – new AI	Synchronizes selection of the new apply instance
Data Guard Broker	enq: RF – synch: DG Broker metadata	Ensures r/w atomicity of DG configuration metadata
Data Guard Broker	enq: RF – synchronization: aifo master	Synchronizes apply instance failure detection and failover operation
Data Guard Broker	enq: RF – synchronization: critical ai	Synchronizes critical apply instance among primary instances
wallet_set_mkey	enq: RK – set key	wallet master key rekey
RAC Encryption Wallet Lock	enq: RL – RAC wallet lock	RAC wallet lock
Redo Log Nab Computation	enq: RN – contention	Coordinates nab computations of online logs during recovery
Multiple Object Reuse	enq: RO – contention	Coordinates flushing of multiple objects
Multiple Object Reuse	enq: RO – fast object reuse	Coordinates fast object reuse
Resilver / Repair	enq: RP – contention	Enqueue held when resilvering is needed or when datablock is repaired from mirror
Workload Capture and Replay	enq: RR – contention	Concurrent invocation of DBMS_WORKLOAD_* package API
Reclaimable Space	enq: RS – file delete	Lock held to prevent file from accessing during space reclaimation
Reclaimable Space	enq: RS – persist alert level	Lock held to make alert level persistent
Reclaimable Space	enq: RS – prevent aging list update	Lock held to prevent aging list update
Reclaimable Space	enq: RS – prevent file delete	Lock held to prevent deleting file to reclaim space
Reclaimable Space	enq: RS – read alert level	Lock held to read alert level
Reclaimable Space	enq: RS – record reuse	Lock held to prevent file from accessing while reusing circular record
Reclaimable Space	enq: RS – write alert level	Lock held to write alert level
Redo Thread	enq: RT – contention	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
Redo Thread	enq: RT – thread internal enable/disable	Thread locks held by CKPT to synchronize thread enable and disable
Rolling Migration	enq: RU – contention	Serializes rolling migration operations
Rolling Migration	enq: RU – waiting	Results of rolling migration CIC
Materialized View Flags	enq: RW – MV metadata contention	Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
ASM Extent Relocation Lock	enq: RX – relocate extent	Synchronizes relocating ASM extents
LogicalStandby	enq: SB – contention	Synchronizes Logical Standby metadata operations
Session Migration	enq: SE – contention	Synchronizes transparent session migration operations
AQ Notification Sender	enq: SF – contention	Lock used for recovery when setting Sender for AQ e-mail notifications
Active Session History Flushing	enq: SH – contention	Should seldom see this contention as this Enqueue is always acquired in no-wait mode
Streams Table Instantiation	enq: SI – contention	Prevents multiple streams tabel instantiations
KTSJ Slave Task Cancel	enq: SJ – Slave Task Cancel	Serializes cancelling task executed by slave process
Shrink Segment	enq: SK – contention	Serialize shrink of a segment
Serialize Lock request	enq: SL – escalate lock	sending lock escalate to LCK0
Serialize Lock request	enq: SL – get lock	sending lock req to LCK0
Serialize Lock request	enq: SL – get lock for undo	sending lock req for undo to LCK0
Shared Object	enq: SO – contention	Synchronizes access to Shared Object (PL/SQL Shared Object Manager)
Spare Enqueue	enq: SP – contention	(1) due to one-off patch
Spare Enqueue	enq: SP – contention 2	(2) due to one-off patch
Spare Enqueue	enq: SP – contention 3	(3) due to one-off patch
Spare Enqueue	enq: SP – contention 4	(4) due to one-off patch
Sequence Cache	enq: SQ – contention	Lock to ensure that only one process can replenish the sequence cache
Synchronized Replication	enq: SR – contention	Coordinates replication / streams operations
Sort Segment	enq: SS – contention	Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
Space Transaction	enq: ST – contention	Synchronizes space management activities in dictionary-managed tablespaces
SaveUndo Segment	enq: SU – contention	Serializes access to SaveUndo Segment
Suspend Writes	enq: SW – contention	Coordinates the ‘alter system suspend’ operation
Instance Undo	enq: TA – contention	Serializes operations on undo segments and undo tablespaces
SQL Tuning Base Existence Cache	enq: TB – SQL Tuning Base Cache Load	Synchronizes writes to the SQL Tuning Base Existence Cache
SQL Tuning Base Existence Cache	enq: TB – SQL Tuning Base Cache Update	Synchronizes writes to the SQL Tuning Base Existence Cache
Tablespace Checkpoint	enq: TC – contention	Lock held to guarantee uniqueness of a tablespace checkpoint
Tablespace Checkpoint	enq: TC – contention2	Lock of setup of a unqiue tablespace checkpoint in null mode
KTF map table enqueue	enq: TD – KTF dump entries	KTF dumping time/scn mappings in SMON_SCN_TIME table
KTF broadcast	enq: TE – KTF broadcast	KTF broadcasting
Temporary File	enq: TF – contention	Serializes dropping of a temporary file
Threshold Chain	enq: TH – metric threshold evaluation	Serializes threshold in-memory chain access
Auto Task Serialization	enq: TK – Auto Task Serialization	Lock held by MMON to prevent other MMON spawning of Autotask Slave
Auto Task Serialization	enq: TK – Auto Task Slave Lockout	Serializes spawned Autotask Slaves
Log Lock	enq: TL – contention	Serializes threshold log table read and update
DML	enq: TM – contention	Synchronizes accesses to an object
Temp Object	enq: TO – contention	Synchronizes DDL and DML operations on a temp object
Runtime Fixed Table Purge	enq: TP – contention	Lock held during purge and dynamic reconfiguration of fixed tables.
Queue table enqueue	enq: TQ – DDL contention	TM access to the queue table
Queue table enqueue	enq: TQ – DDL-INI contention	Streams DDL on queue table
Queue table enqueue	enq: TQ – INI contention	TM access to the queue table
Queue table enqueue	enq: TQ – TM contention	TM access to the queue table
Temporary Segment	enq: TS – contention	Serializes accesses to temp segments
Tablespace	enq: TT – contention	Serializes DDL operations on tablespaces
Cross-Instance Transaction	enq: TW – contention	Lock held by one instance to wait for transactions on all instances to finish
Transaction	enq: TX – allocate ITL entry	Allocating an ITL entry in order to begin a transaction
Transaction	enq: TX – contention	Lock held by a transaction to allow other transactions to wait for it
Transaction	enq: TX – index contention	Lock held on an index during a split to prevent other operations on it
Transaction	enq: TX – row lock contention	Lock held on a particular row by a transaction to prevent other transactions from modifying it
User-defined	enq: UL – contention	Lock used by user applications
Undo Segment	enq: US – contention	Lock held to perform DDL on the undo segment
AQ Notification Watermark	enq: WA – contention	Lock used for recovery when setting Watermark for memory usage in AQ notifications
AWR Flush	enq: WF – contention	This enqueue is used to serialize the flushing of snapshots
Write gather local enqueue	enq: WG – delete fso	acquire lobid local enqueue when deleting fso
Write gather local enqueue	enq: WG – lock fso	acquire lobid local enqueue when locking fso
Being Written Redo Log	enq: WL – RAC-wide SGA contention	Serialize access to RAC-wide SGA
Being Written Redo Log	enq: WL – RFS global state contention	Serialize access to RFS global state
Being Written Redo Log	enq: WL – Test access/locking	Testing redo transport access/locking
Being Written Redo Log	enq: WL – contention	Coordinates access to redo log files and archive logs
WLM Plan Operations	enq: WM – WLM Plan activation	Synchronizes new WLM Plan activation
AWR Purge	enq: WP – contention	This enqueue handles concurrency between purging and baselines
LNS archiving log	enq: WR – contention	Coordinates access to logs by Async LNS and ARCH/FG
XDB Configuration	enq: XC – XDB Configuration	Lock obtained when incrementing XDB configuration version number
Auto Online Exadata disks	enq: XD – ASM disk drop/add	Serialize Auto Drop/Add Exadata disk operations
AQ Notification No-Proxy	enq: XH – contention	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
ASM Extent Fault Lock	enq: XL – fault extent map	Keep multiple processes from faulting in the same extent chunk
ASM Extent Relocation Enqueue	enq: XQ – purification	wait for relocation before doing block purification
ASM Extent Relocation Enqueue	enq: XQ – recovery	prevent relocation during _recovery_asserts checking
ASM Extent Relocation Enqueue	enq: XQ – relocation	wait for recovery before doing relocation
Quiesce / Force Logging	enq: XR – database force logging	Lock held during database force logging mode
Quiesce / Force Logging	enq: XR – quiesce database	Lock held during database quiesce
Internal Test	enq: XY – contention	Lock used for internal testing
Audit Partition	enq: ZA – add std audit table partition	lock held to add partition to std audit table
FGA Partition	enq: ZF – add fga audit table partition	lock held to add partition to fga audit table
File Group	enq: ZG – contention	Coordinates file group operations
Compression Analyzer	enq: ZH – compression analysis	Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
Global Context Action	enq: ZZ – update hash tables	lock held for updating global context hash tables

For those who made it all to the bottom here a little extra:

While I just found the enqueue waits for 10g on the internet I managed to find out how to retrieve that information. And believe it or not – it’s available just over a simple query:

SELECT eq_name "Group", ev.name "Enqueue Type", eq.req_description "Description"
 FROM v$enqueue_statistics eq, v$event_name ev
 WHERE eq.event#=ev.event#
 ORDER BY ev.name;

Ask Tom search engine

Today my boss asked me if I know how to fix the search engine for FireFox for AskTom.oracle.com since Tom Kyte has moved it to APEX. Actually I didn’t have the search engine even installed anymore (hope that means that I’m already so good with Oracle myself ) but thought that it would be nice to have it again.

Therefore I quickly build the search engine for the new Ask Tom site.

To install it, download this file by right-clicking on it and choosing “Save Link As”:

SearchEngineInstaller.png

Unfortunately – or better fortunately – WordPress doesn’t allow Javascript within posts nor xml files as media. Therefore I have to work with faked png files here.
After you download the file rename it to “SearchEngineInstaller.html” and open the file in your browser.
Then click on “Install Ask Tom search engine”, install the engine and “Mission accomplished”!

If you are interested in the “code” you can download the search engine file here: AskTomSearchEngine.png
Again with right click and “Save Link As”. Then rename it to “AskTomSearchEngine.xml”

A few facts about TimesTen

• TimesTen gets usually deployed in the Middle Tier – same location where the application runs
• TimesTen is persistent and can run as the only database on a system
• TimesTen persists to the disk over a background process
• TimesTen supports High Availability
• TimesTen can be used as cache between the application and the Oracle database
• TimesTen can be linked into applications directly – the calls to TimesTen happens over normal subroutine calls in the code which require no context switches on the system
• You can cache either a group of related tables or even a subset of specific rows
• TimesTen supports read-only and update able caches
• TimesTen supports “DynamicLoad” which loads the data from Oracle when you need it
• You can have as much read-only subscribers as you want
• TimesTen supports SQL and PL/SQL
• TimesTen is certified on all main application servers and OR-Mapping tools like Hibernate