Venzi's Weblog

6 October 2011

Indexing strategies with Jonathan Lewis

Filed under: Oracle,Performance,Work — Venzi @ 10:25 pm

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!

 

26 April 2011

Exadata, Exadata, Exadata

Filed under: Oracle,Performance,Work — Venzi @ 6:15 pm

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! :)

14 April 2011

Log file sync to death…

Filed under: Oracle,Performance — Venzi @ 1:53 pm

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

27 January 2011

Oracle LogMiner

Filed under: Oracle — Venzi @ 12:22 pm

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”

2 January 2011

2010 in review

Filed under: Uncategorized — Venzi @ 6:56 pm

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:

Healthy blog!

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

Crunchy numbers

Featured image

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

30 December 2010

Oracle lock types

Filed under: Oracle — Venzi @ 4:19 pm

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;

28 December 2010

Why my job is safe

Filed under: Performance,Programming — Venzi @ 2:43 pm

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();
}

10 December 2010

Oracle 11gR2 enqueue waits

Filed under: Oracle,Performance — Venzi @ 9:24 am

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;

29 November 2010

Ask Tom search engine

Filed under: Oracle — Venzi @ 6:09 pm

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”

12 August 2010

A few facts about TimesTen

Filed under: Oracle — Venzi @ 3:11 pm
  • 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
Next Page »

Theme: Rubric. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.