Oracle GoldenGate Extract Internals, Part III

This is the third post in the Oracle GoldenGate Extract Internals series (links to part I and part II). In this post, we’re going to take a closer look at various queries that the Extract process uses against the database.

As before, we will start by examining the strace output:

nanosleep({1, 0}, NULL)                 = 0 ... read(20, "\1\"\0\0\255\1\0\0\217\0\0\0H\200\366\256\5\24\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1024000) = 1024000 ... write(16, "\0$\0\0\6\0\0\0\0\0\3N'\7\0\0\0\2\0\0\0`\0\0\0\0\0\0\0\7\0011"..., 36) = 36 read(17, "\0\351\0\0\6\0\0\0\0\0\6\1\"\375\2\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0"..., 8208) = 233 write(16, "\0 \0\0\6\0\0\0\0\0\3N(\10\0\0\0\2\0\0\0`\0\0\0\0\0\0\0\7\0011", 32) = 32 read(17, "\0\343\0\0\6\0\0\0\0\0\6\1\"\7\1\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0"..., 8208) = 227 write(16, "\0K\0\0\6\0\0\0\0\0\3N)\t\0\0\0\2\0\0\0`\0\0\0\0\0\0\0\7,/"..., 75) = 75 read(17, "\0\341\0\0\6\0\0\0\0\0\6\1\"\375\1\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0"..., 8208) = 225 write(16, "\0Q\0\0\6\0\0\0\0\0\3N*\n\0\0\0\2\0\0\0`\0\0\0\0\0\0\0\7,/"..., 81) = 81 read(17, "\0\254\0\0\6\0\0\0\0\0\4\1\0\0\0)\0\1\0\0\0\0{\5\0\0\0\0\n\0\0\0"..., 8208) = 172 lseek(20, 227328, SEEK_SET)             = 227328 nanosleep({1, 0}, NULL)                 = 0 

1. Monitoring Redo Log Activity

File descriptors 16 and 17 are the pipes for one of the bequeath connections we have with the database. There are four queries being submitted each cycle (following the same order as those being sent to a database):

• SELECT DECODE(archived, 'YES', 1, 0), status FROM v$log WHERE thread# = :ora_thread AND sequence# = :ora_seq_no
• SELECT MAX(sequence#) FROM v$log WHERE thread# = :ora_thread AND status in ('INVALIDATED', 'CURRENT', 'ACTIVE')
• SELECT DECODE(status, 'STALE', 1, 0) FROM v$logfile WHERE member = :log_name
• SELECT 1 FROM V$LOGFILE WHERE(STATUS NOT IN ('STALE', 'INVALID') OR STATUS IS NULL) AND MEMBER <> :log_name AND EXISTS ( SELECT 1 FROM V$LOG WHERE GROUP# = V$LOGFILE.GROUP# AND THREAD# = :ora_thread AND SEQUENCE# = :ora_seq_no ) AND ROWNUM = 1

Impact on Control File I/O

The purpose of these statements is to constantly keep an eye on what’s happening inside the database by regularly polling the contents of the above views. What’s worth mentioning about the above queries is that all of them will cause extra I/O to the controlfile. On my test database, that equaled 640KB each cycle (40 I/O requests, 16KB each). In most cases, this is nothing to worry about–just keep the additional I/O in mind in case your controlfile is already a hot spot.

2. Resolving Metadata and Data Dictionary Queries

The redo log stores object identifiers (a number), which means that when the Extract process encounters a supported operation, it needs a way to find out more details. This is achieved by a couple of statements against the data dictionary.

Initial Object Identification

The following statement will be issued first: SELECT u.name, o.name, o.dataobj#, o.type#, (SELECT bitand(t.property, 1) FROM sys.tab$ t WHERE t.obj# = :ora_object_id) FROM sys.obj$ o, sys.user$ u WHERE o.obj# = :ora_object_id AND decode(bitand(o.flags, 128), 128, 'YES', 'NO') = 'NO' AND o.owner# = u.user# AND decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N') = 'N' AND (o.type# in (1, 19, 20, 34) OR EXISTS (SELECT 'x' FROM sys.tab$ t WHERE t.obj# = :ora_object_id))

Index-Organized Table (IOT) Verification

In case the object turns out to be a table, it will be checked whether it is an overflow segment for an IOT:

SELECT nvl(iot_name, 'NULL') FROM all_tables WHERE owner = :owner AND table_name = :object_name

In case the object in question turns out to be an index, a corresponding check will be made to see whether it’s an underlying index for an IOT: SELECT table_owner, table_name FROM all_indexes WHERE index_name = :object_name AND owner = :owner AND index_type = 'IOT - TOP'

3. Advanced Attribute Gathering and Potential Pitfalls

The above queries will be executed regardless of whether or not you’re interested in capturing changes from the particular object, because the queries are required before you can make that decision. In case this is something we’re interested in, additional information will be requested:

select object_type, object_name, subobject_name from dba_objects where object_id = :ora_object_id

Partitioning and Tablespace Mapping

Depending on the result, one of the following two statements will be executed to check tablespace types:

• select ts.bigfile from dba_tablespaces ts, all_tables t where t.table_name = :ora_object_name and t.tablespace_name = ts.tablespace_name and rownum = 1
• select t.bigfile from dba_tablespaces t, all_tab_partitions p where p.partition_name = :ora_subobject_name and p.tablespace_name = t.tablespace_name and rownum=1

The ROWNUM = 1 Limitation

There is obviously an issue with the above two statements. Neither of them specify the object owner and, in case you have two (or more) objects with the same name but in different schemas, the above statements may return incorrect information. It’s interesting how the issue is shoved away using the rownum = 1 condition.

Since ROWID organization differs between small- and big-file tablespaces (relative data file number vs. block number usage), functionality relying on this mapping could potentially be affected.

4. Column Resolution and Replicat Dependencies

What else is interesting? The big thing is that none of the information on columns is being resolved during the Extract phase. Column information will be fetched by the Replicat process using the destination system’s data dictionary.

Combine this with the fact that an online data dictionary does not store historical information about an object’s metadata, and you have a perfect recipe for nasty situations—which is exactly why Oracle Streams relies on MVDD instead of the online data dictionary. But more on that when we get to the Replicat process internals series.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?