Oracle related google search

This here is a link to an Oracle specified Google search: http://www.miracleoy.fi/search.html

It was created by the Finland Miracle group and searches especially for Oracle related sites and block all the untrusted sites from them. Good if you want to find your solution quick!

NYOUG Meeting – 25th anniversary

It’s a long time since I have posted something here, I know. As you might remember from my last post I moved over to New York and well, there where a lot of things to do the last months. Anyway, I’m not writing here to make excuses. Fact is that I have been very busy lately. In my (new) role as “Performance Engineer” in the company I got new tasks beside my Oracle tuning stuff. Actually I spent most of the time in the last few months with tuning our Java application and not Oracle anymore. But I’m happy with that as this gave and still gives me the opportunity to learn more about the art of tuning! But this just as a short clarification why my Oracle related posts are so rare lately.

Anyway, today I had the chance to attend the New York Oracle User Group meeting together with my working colleagues. This was my first user group meeting at all so I was a little bit excited about what will be waiting for me there! As I took this serious I printed off the agenda already a few days back and marked the sessions which seemed to be interesting for me. One great thing I discovered was a session with Tanel Poder who I already know because of his blog (which I have also in my Blogroll of course!). Tanel is one of the few guys who shares his scripts with you. Most of the DBAs hide their secret, magic scripts for themselves to be the number 1 DBA. But Tanel provides his scripts to the world. You can download the whole catalog as a simple zip file from his website and well believe me he has a lot of scripts! Anyway, from the four sessions I have chosen three of them where performance related. Actually all of the sessions that I wanted to attend were performance related but unfortunately the first session about performance on SSD was canceled so I attended Arup Nandas session about upgrading to 11g and how database replay/standby database can help you minimize the downtime. Actually I had such a session already back in February in Vienna at Oracle so there weren’t really new things for me at the presentation. The second session was then the one from Tanel. It was about (his) scripts and tools that (can) make your life easier. Third session was from Dave Anderson about 11g Result-Set Cache. He did some life demos on it what was really cool. It gave you the chance to see result set cache really working and improving your performance at the response time. Fourth and last session was from Dean Richards  – “Advanced SQL tuning”. This was a very, very interesting session as he had some real world issues in the presentation. He also gave an overview on which steps are important for performance tuning (knowing the background, triggering down the issue, etc.) After the sessions there were some vendor raffles. I hoped to win the 32″ TV but of course I did not! Actually I didn’t win anything! ;(

If I have to rate the sessions I attended then I would say that the best presenter was Tanel Poder. He was agile and brought some nice “real world” jokes into the session. There was not a single second in his session where you got bored or did pay more attention to your Blackberry than to him! The best session rated by the content would have been Dean Richards “Advanced SQL tuning”. He showed up important things about how to tune and how important it is to understand the whole story around it!

Well time for pictures:














Oracle 10g enqueue waits

Just found a useful list of Oracle 10g enqueue waits in the web which I don’t want to deprive from you:

Enqueue Type Description
enq: AD – allocate AU Synchronizes accesses to a specific OSM (Oracle Software Manager) disk AU
enq: AD – deallocate AU Synchronizes accesses to a specific OSM disk AU
enq: AF – task serialization Serializes access to an advisor task
enq: AG – contention Synchronizes generation use of a particular workspace
enq: AO – contention Synchronizes access to objects and scalar variables
enq: AS – contention Synchronizes new service activation
enq: AT – contention Serializes alter tablespace operations
enq: AW – AW$ table lock Allows global access synchronization to the AW$ table (analytical workplace tables used in OLAP option)
enq: AW – AW generation lock Gives in-use generation state for a particular workspace
enq: AW – user access for AW Synchronizes user accesses to a particular workspace
enq: AW – AW state lock Row lock synchronization for the AW$ table
enq: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: CF – contention Synchronizes accesses to the controlfile
enq: CI – contention Coordinates cross-instance function invocations
enq: CL – drop label Synchronizes accesses to label cache when dropping a label
enq: CL – compare labels Synchronizes accesses to label cache for label comparison
enq: CM – gate Serializes access to instance enqueue
enq: CM – instance Indicates OSM disk group is mounted
enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
enq: CT – state Lock held while enabling or disabling change tracking to ensure that it is enabled or disabled by only one user at a time
enq: CT – state change gate 2 Lock held while enabling or disabling change tracking in RAC
enq: CT – reading Lock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT – CTWR process start/stop Lock held to ensure that only one CTWR (Change Tracking Writer, which tracks block changes and is initiated by the alter database enable block change tracking command) process is started in a single instance
enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
enq: CT – change stream ownership Lock held by one instance while change tracking is enabled to guarantee access to thread-specific resources
enq: CT – local space management Lock held during change tracking space management operations that affect just the data for one thread
enq: CU – contention Recovers cursors in case of death while compiling
enq: DB – contention Synchronizes modification of database wide supplemental logging attributes
enq: DD – contention Synchronizes local accesses to ASM (Automatic Storage Management) disk groups
enq: DF – contention Enqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG – contention Synchronizes accesses to ASM disk groups
enq: DL – contention Lock to prevent index DDL during direct load
enq: DM – contention Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
enq: DN – contention Serializes group number generations
enq: DP – contention Synchronizes access to LDAP parameters
enq: DR – contention Serializes the active distributed recovery operation
enq: DS – contention Prevents a database suspend during LMON reconfiguration
enq: DT – contention Serializes changing the default temporary table space and user creation
enq: DV – contention Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DX – contention Serializes tightly coupled distributed transaction branches
enq: FA – access file Synchronizes accesses to open ASM files
enq: FB – contention Ensures that only one process can format data blocks in auto segment space managed tablespaces
enq: FC – open an ACD thread LGWR opens an ACD thread
enq: FC – recover an ACD thread SMON recovers an ACD thread
enq: FD – Marker generation Synchronization
enq: FD – Flashback coordinator Synchronization
enq: FD – Tablespace flashback on/off Synchronization
enq: FD – Flashback on/off Synchronization
enq: FG – serialize ACD relocate Only 1 process in the cluster may do ACD relocation in a disk group
enq: FG – LGWR redo generation enq race Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FG – FG redo generation enq race Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FL – Flashback database log Synchronizes access to Flashback database log
enq: FL – Flashback db command Synchronizes Flashback Database and deletion of flashback logs
enq: FM – contention Synchronizes access to global file mapping state
enq: FR – contention Begins recovery of disk group
enq: FS – contention Synchronizes recovery and file operations or synchronizes dictionary check
enq: FT – allow LGWR writes Allows LGWR to generate redo in this thread
enq: FT – disable LGWR writes Prevents LGWR from generating redo in this thread
enq: FU – contention Serializes the capture of the DB feature, usage, and high watermark statistics
enq: HD – contention Serializes accesses to ASM SGA data structures
enq: HP – contention Synchronizes accesses to queue pages
enq: HQ – contention Synchronizes the creation of new queue IDs
enq: HV – contention Lock used to broker the high watermark during parallel inserts
enq: HW – contention Lock used to broker the high watermark during parallel inserts
enq: IA – contention Information not available
enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL – contention Synchronizes accesses to internal label data structures
enq: IM – contention for blr Serializes block recovery for IMU txn
enq: IR – contention Synchronizes instance recovery
enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
enq: IS – contention Synchronizes instance state changes
enq: IT – contention Synchronizes accesses to a temp object’s metadata
enq: JD – contention Synchronizes dates between job queue coordinator and slave processes
enq: JI – contention Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ – contention Lock to prevent multiple instances from running a single job
enq: JS – contention Synchronizes accesses to the job cache
enq: JS – coord post lock Lock for coordinator posting
enq: JS – global wdw lock Lock acquired when doing wdw ddl
enq: JS – job chain evaluate lock Lock when job chain evaluated for steps to create
enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
enq: JS – slave enq get lock2 Gets run info locks before slv objget
enq: JS – slave enq get lock1 Slave locks exec pre to sess strt
enq: JS – running job cnt lock3 Lock to set running job count epost
enq: JS – running job cnt lock2 Lock to set running job count epre
enq: JS – running job cnt lock Lock to get running job count
enq: JS – coord rcv lock Lock when coord receives msg
enq: JS – queue lock Lock on internal scheduler queue
enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
enq: KK – context Lock held by open redo thread, used by other instances to force a log switch
enq: KM – contention Synchronizes various Resource Manager operations
enq: KP – contention Synchronizes kupp process startup
enq: KT – contention Synchronizes accesses to the current Resource Manager plan
enq: MD – contention Lock held during materialized view log DDL statements
enq: MH – contention Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq: ML – contention Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN – contention Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MR – contention Lock used to coordinate media recovery with other uses of datafiles
enq: MS – contention Lock held during materialized view refresh to set up MV log
enq: MW – contention Serializes the calibration of the manageability schedules with the Maintenance Window
enq: OC – contention Synchronizes write accesses to the outline cache
enq: OL – contention Synchronizes accesses to a particular outline name
enq: OQ – xsoqhiAlloc Synchronizes access to olapi history allocation
enq: OQ – xsoqhiClose Synchronizes access to olapi history closing
enq: OQ – xsoqhistrecb Synchronizes access to olapi history globals
enq: OQ – xsoqhiFlush Synchronizes access to olapi history flushing
enq: OQ – xsoq*histrecb Synchronizes access to olapi history parameter CB
enq: PD – contention Prevents others from updating the same property
enq: PE – contention Synchronizes system parameter updates
enq: PF – contention Synchronizes accesses to the password file
enq: PG – contention Synchronizes global system parameter updates
enq: PH – contention Lock used for recovery when setting proxy for AQ HTTP notifications
enq: PI – contention Communicates remote Parallel Execution Server Process creation status
enq: PL – contention Coordinates plug-in operation of transportable tablespaces
enq: PR – contention Synchronizes process startup
enq: PS – contention Parallel Execution Server Process reservation and synchronization
enq: PT – contention Synchronizes access to ASM PST metadata
enq: PV – syncstart Synchronizes slave start_shutdown
enq: PV – syncshut Synchronizes instance shutdown_slvstart
enq: PW – prewarm status in dbw0 DBWR0 holds this enqueue indicating pre-warmed buffers present in cache
enq: PW – flush prewarm buffers Direct Load needs to flush prewarmed buffers if DBWR0 holds this enqueue
enq: RB – contention Serializes OSM rollback recovery operations
enq: RF – synch: per-SGA Broker metadata Ensures r/w atomicity of DG configuration metadata per unique SGA
enq: RF – synchronization: critical ai Synchronizes critical apply instance among primary instances
enq: RF – new AI Synchronizes selection of the new apply instance
enq: RF – synchronization: chief Anoints 1 instance’s DMON (Data Guard Broker Monitor) as chief to other instance’s DMONs
enq: RF – synchronization: HC master Anoints 1 instance’s DMON as health check master
enq: RF – synchronization: aifo master Synchronizes critical apply instance failure detection and failover operation
enq: RF – atomicity Ensures atomicity of log transport setup
enq: RN – contention Coordinates nab computations of online logs during recovery
enq: RO – contention Coordinates flushing of multiple objects
enq: RO – fast object reuse Coordinates fast object reuse
enq: RP – contention Enqueue held when resilvering is needed or when data block is repaired from mirror
enq: RS – file delete Lock held to prevent file from accessing during space reclamation
enq: RS – persist alert level Lock held to make alert level persistent
enq: RS – write alert level Lock held to write alert level
enq: RS – read alert level Lock held to read alert level
enq: RS – prevent aging list update Lock held to prevent aging list update
enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR (Recovery Writer, used in Flashback Database operations) to indicate mounted or open status
enq: SB – contention Synchronizes logical standby metadata operations
enq: SF – contention Lock held for recovery when setting sender for AQ e-mail notifications
enq: SH – contention Enqueue always acquired in no-wait mode – should seldom see this contention
enq: SI – contention Prevents multiple streams table instantiations
enq: SK – contention Serialize shrink of a segment
enq: SQ – contention Lock to ensure that only one process can replenish the sequence cache
enq: SR – contention Coordinates replication / streams operations
enq: SS – contention Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
enq: ST – contention Synchronizes space management activities in dictionary-managed tablespaces
enq: SU – contention Serializes access to SaveUndo Segment
enq: SW – contention Coordinates the ‘alter system suspend’ operation
enq: TA – contention Serializes operations on undo segments and undo tablespaces
enq: TB – SQL Tuning Base Cache Update Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB – SQL Tuning Base Cache Load Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TC – contention Lock held to guarantee uniqueness of a tablespace checkpoint
enq: TC – contention2 Lock during setup of a unique tablespace checkpoint in null mode
enq: TD – KTF dump entries KTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE – KTF broadcast KTF broadcasting
enq: TF – contention Serializes dropping of a temporary file
enq: TL – contention Serializes threshold log table read and update
enq: TM – contention Synchronizes accesses to an object
enq: TO – contention Synchronizes DDL and DML operations on a temp object
enq: TQ – TM contention TM access to the queue table
enq: TQ – DDL contention DDL access to the queue table
enq: TQ – INI contention TM access to the queue table
enq: TS – contention Serializes accesses to temp segments
enq: TT – contention Serializes DDL operations on tablespaces
enq: TW – contention Lock held by one instance to wait for transactions on all instances to finish
enq: TX – contention Lock held by a transaction to allow other transactions to wait for it
enq: TX – row lock contention Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq: TX – allocate ITL entry Allocating an ITL entry in order to begin a transaction
enq: TX – index contention Lock held on an index during a split to prevent other operations on it
enq: UL – contention Lock held used by user applications
enq: US – contention Lock held to perform DDL on the undo segment
enq: WA – contention Lock used for recovery when setting watermark for memory usage in AQ notifications
enq: WF – contention Enqueue used to serialize the flushing of snapshots
enq: WL – contention Coordinates access to redo log files and archive logs
enq: WP – contention Enqueue to handle concurrency between purging and baselines
enq: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XR – quiesce database Lock held during database quiesce
enq: XR – database force logging Lock held during database force logging mode
enq: XY – contention Lock used by Oracle Corporation for internal testing

Of course I don’t want to steal anyones content here:

Found at: http://oracle-dox.net/

Direct link: http://oracle-dox.net/McGraw.Hill-Oracle.Wait.Interf/8174final/LiB0063.html

Many thanks to Oracle-Dox for providing this table!

Finally a good tool for Data Modeling: Oracle SqlDeveloper Data Modeling

How long did you search for a good tool to do Data Modeling. Well not long I guess, I always used Microsoft Visio for it. But how long did you search for a tool to create a Data Model and export the DDL statements or vice versa and also for one which is free? I did it now for around 4 year and then I found this blog entry: Finally – free tool for Oracle Data Modeling

It’s called Oracle SqlDeveloper Data Modeling

I just downloaded it and tried it out: It’s absolutely nice. You can create logical models like you know it from Visio but you can also create relational models where you can already define column data types, constraints and so.

But not even that, you can create relational models out from logical and vice versa!

Oracle SqlDeveloper Data Modeling can also export it to DDL (a feature I searched long for!). And even better: You can choose which DDL (Oracle 9i, 10g, 11g; SQL Server 2000, 2005; DB2 7, 7.1, 8, 8.1). The only bad thing: MySql isn’t supported right now 😦 (Already gave feedback on that! 😉 )

Of course you’re also able to play it the other way round and import DDL and create a datamodel out of it – really cool stuff! And you can again import it for any databases listed above!

And now the best feature: If you’ve access to the DB catalog, you can even create a data model out of an existing schema!

Sorry for the grey shadows but a little bit of data protection must be! 😉

All in all Oracle SqlDeveloper Data Modeling is really a good tool. It has lots of more features which I didn’t try out so far (Design Rules, Multidimensional Models, Process Models, …) and the best thing: It’s free – at the moment. It seems that there were some rumors about a license for this tool. We’ll see!

Private strand flush not complete

Today I got a message in the alert log which is new to me:

Thread 1 cannot allocate new log, sequence 607
Private strand flush not complete

Private strand flush not complete… sounds similar to checkpoint not complete. So the next step brought me to Metalink and there I found note 372557.1.

What you can read in there is that Oracle hasn’t completed writing all the redo information to the log at the logfile switch. It is also written that this is similar to a “checkpoint not complete” except that this only involves the redo being written to the log and that the log switch can not occur until all of the redo has been written.

The next thing: What’s a private strand?

Oracle says:

A “strand” is new terminology for 10g and it deals with latches for redo.

Strands are a mechanism to allow multiple allocation latches for processes to write redo more efficiently in the redo buffer and is related to the log_parallelism parameter present in 9i.

The concept of a strand is to ensure that the redo generation rate for an instance is optimal and that when there is some kind of redo contention then the number of strands is dynamically adjusted to compensate.

The initial allocation for the number of strands depends on the number of CPU’s and is started with 2 strands with one strand for active redo generation.

For large scale enterprise systems the amount of redo generation is large and hence these strands are *made active* as and when the foregrounds encounter this redo contention (allocated latch related contention) when this concept of dynamic strands comes into play.

There is always shared strands and a number of private strands .

Oracle 10g has some major changes in the mechanisms for redo (and undo), which seem to be aimed at reducing contention.

Instead of redo being recorded in real time, it can be recorded ‘privately’ and pumped into the redo log buffer on commit.

Similary the undo can be generated as ‘in memory undo’ and applied in bulk.

This affect the memory used for redo management and the possibility to flush it in pieces.

The message you get is related to internal Cache Redo File management.

You can disregard these messages as normal messages.

When you switch logs all private strands have to be flushed to the current log before the switch is allowed to proceed.

So all in all this isn’t a bug or an error. As said above it’s similar to the “checkpoint not complete” message and can be also handled that way!