Dynamic registration in Oracle

Service registration allows processes, such as an Oracle database, to identify their available services to the listener, which then acts as a port mapper for those services. The listener uses the dynamic service information about the database and instance received through service registration.

Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. However, listener configuration must be set to listen on the ports named in the database initialization file, and must not have parameters set that prevent automatic registration, such as COST parameters.

By default, the LREG process registers service information with its local listener on the default local address of TCP/IP, port 1521. If the listener configuration is synchronized with the database configuration, then LREG can register service information with a nondefault local listener or a remote listener on another node. Synchronization occurs when the protocol address of the listener is specified in the listener.ora file and the location of the listener is specified in the initialization parameter file.

To have the LREG process register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

Because the LOCAL_LISTENER parameter and the LISTENER attribute enable LREG to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.

LOCAL_LISTENER is a comma-delimited list parameter. If a comma appears in the string, then the entire string must be enclosed in double quotation marks. Set the LOCAL_LISTENER parameter as follows:

ALTER SYSTEM SET LOCAL LISTENER=["listener_address"][,...];

If you set the parameter to null using the following statement, then the default local address of TCP/IP, port 1521 is assumed:

ALTER SYSTEM SET LOCAL_LISTENER=''

For example, if the listener address “ab,cd” is entered, then it resolves to one listener address. If the address is entered as ab,cd, then it resolves to two listener addresses, ab and cd.

The listener should be resolvable by tnsping

Suppose you have a listener named listener2 listening on port 1530

• Ensure that you create an entry in tnsnames.ora which we will use in the parameter LOCAL_LISTENER

listener2=

(DESCRIPTION =

(ADDRESS = (PROTOCOL=tcp)(HOST=localhost)(PORT=1530)))
<ul>

On the host where the local listener resides, configure the listener.ora file with the protocol address of the listener.

• On the database, set the LOCAL_LISTENER parameter in the database initialization parameter file to the alias of the local listener. For example:

ALTER SYSTEM SET LOCAL_LISTENER=listener2;

Dynamic registration is done by the database by the background process LREG.

Listener Registration Process (LREG)

The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net Listener (see “The Oracle Net Listener”). When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.

If the listener is not running when an instance starts, then the LREG process cannot register the service information. LREG attempts to connect to the listener periodically, but it may take up to 60 seconds before LREG registers with the listener after it has been started. To initiate service registration immediately after the listener is started, use the SQL statement ALTER SYSTEM REGISTER. This statement is especially useful in high availability configurations.

Sourced from Oracle Documentation.

Log file sync wait event

By Dean Richards,

The Oracle “log file sync” wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session. The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation of its completion back to the user session. The wait time includes the writing of the log buffer and the post, and is sometimes called “commit latency”.

The P1 parameter in <View:V$SESSION_WAIT> is defined as follows for the log file sync wait event:

1 2	P1 = buffer# All changes up to this buffer number (in the log buffer) must be flushed to disk and the writes confirmed to ensure that the transaction is committed and will be kept on an instance crash. The wait is for LGWR to flush up to this <strong>buffer#</strong>.

Reducing Oracle waits / wait times

If a SQL statement is encountering a significant amount of total time for this event, the average wait time should be examined. If the average wait time is low, but the number of waits is high, then the application might be committing after every row, rather than batching COMMITs. Oracle applications can reduce this wait by committing after “n” rows so there are fewer distinct COMMIT operations. Each commit has to be confirmed to make sure the relevant REDO is on disk. Although commits can be “piggybacked” by Oracle, reducing the overall number of commits by batching transactions can be very beneficial.

If the SQL statement is a SELECT statement, review the Oracle Auditing settings. If Auditing is enabled for SELECT statements, Oracle could be spending time writing and commit data to the AUDIT$ table.

If the average wait time is high, then examine the other log related waits for the session, to see where the session is spending most of its time. If a session continues to wait on the same buffer# then the SEQ# column of V$SESSION_WAIT should increment every second. If not then the local session has a problem with wait event timeouts. If the SEQ# column is incrementing then the blocking process is the LGWR process. Check to see what LGWR is waiting on as it may be stuck.

If the waits are because of slow I/O, then try the following:

• Reduce other I/O activity on the disks containing the redo logs, or use dedicated disks.
  • Try to reduce resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.
• Alternate redo logs on different disks to minimize the effect of the archiver on the log writer.
• Move the redo logs to faster disks or a faster I/O subsystem (for example, switch from RAID 5 to RAID 1).
• Consider using raw devices (or simulated raw devices provided by disk vendors) to speed up the writes.
• See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options in order to reduce the amount of redo being written.
• See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
• Check the size of the log buffer as it may be so large that LGWR is writing too many blocks at one time.

Log file sync wait event: other considerations

There may be a problem with LGWR’s ability to flush redo out quickly enough if Oracle “log file sync” waits are significant for the entire system. The overall wait time for “log file sync” can be broken down into several components. If the system still shows high “log file sync” wait times after completing the general tuning tips above, break down the total Oracle wait time into the individual components. Then, tune those components that take up the largest amount of time.

The “log file sync” wait event may be broken down into the following components:

1. Wakeup LGWR if idle
2. LGWR gathers the redo to be written and issues the I/O
3. Wait time for the log write I/O to complete
4. LGWR I/O post processing
5. LGWR posting the foreground/user session that the write has completed
6. Foreground/user session wakeup

Tune the system based on the “log file sync” component with the most wait time. Steps 2 and 3 are accumulated in the “redo write time” statistic. (i.e. as found under STATISICS section of Statspack) Step 3 is the “log file parallel write” wait event. (See Metalink Note 34583.1:”log file parallel write”) Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take some time for the OS to schedule it to run.

Data Guard note

If Data Guard with synchronous transport and commit WAIT defaults is used, the above tuning steps will still apply. However step 3 will also include the network write time and the redo write to the standby redo logs. The “log file sync” wait event and how it applies to Data Guard is explained in detail in the MAA OTN white paper – Note 387174.1:MAA – Data Guard Redo Transport and Network Best Practices.

Final thoughts

When a user session waits on the “log file sync” event, it is actually waiting for the LGWR process to write the log buffer to the redo log file and return confirmation/control back to it. If the total wait time is significant, review the average wait time. If the average wait time is low but the number of waits is high, reduce the number of commits by batching (or committing after “n”) rows.

If slow I/O, investigate the following:

• Reduce contention on existing disks.
• Put log files on faster disks.
• Put alternate redo logs on different disks to minimize the effect archive processes (log files switches).
• Review application design, use NOLOGGING operations where appropriate, and avoid changing more data than required.

If wait times are still significant, review each component of the “log file sync” and tune separately.

Buffer busy waits

Buffer busy wait happens when a session wants to access a database block in the buffer cache but it cannot because the buffer is busy. Another session is modifying the block and the contents of the block are in flux during the modification. To guarantee that the reader has a coherent image of the block with either all of the changes or none of the changes, the session modifying the block marks the block header with a flag letting other users know a change is taking place and to wait until the complete change is applied.

The two main cases where this wait can occur are:

• Another session is reading the block into the buffer
• Another session holds the buffer in an incompatible mode to our request

While the block is being changed, the block is marked as unreadable by others. The changes that are being made should last under a few hundredths of a second. A disk read should be under 20 milliseconds and a block modification should be under one millisecond. Therefore it will take a lot of buffer busy waits to cause a problem.

However, in a problem situation, there is usually a hot block, such as the first block on the free list of a table, with high concurrent inserts. All users will insert into that block at the same time, until it fills up, then users start inserting into the next free block on the list, and so on.

Another example of a problem is of multiple users running full table scans on the same large table at the same time. One user will actually read the block physically off disk, and the other users will wait on Buffer Busy Wait for the physical I/O to complete.

The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads (i.e., by adding indexes). Even if we have a huge db_cache_size, we may still see buffer busy waits, and increasing the buffer size won’t help.

The resolution of a “buffer busy wait”  events is one of the most confounding problems with Oracle.  In an I/O-bound Oracle system, buffer busy waits are common, as evidenced by any system with read (sequential/scattered) waits in the top-five waits.

Reducing buffer busy waits reduces the total I/O on the system. This can be accomplished by tuning the SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes.  Even if there is a huge db_cache_size , the DBA may still see buffer busy waits and, in this case, increasing the buffer size will not help.

Increasing the db_cache_size parameter would not necessarily fix this problem.

Point of action is give below,

Look at v$waitstat (or the buffer busy drill down page) and determine the block type with the highest waits.

Block Type and Action:

• Undo Header – Use Automatic Undo Management (AUM) or add more RBS segments)
• Undo Block – Use AUM (or increase RBS sizes)
• Data Block – First determine if it is an I/O problem. The Buffer Busy Waits drill-down page should provide this information. Otherwise, sample from v$session_wait

  SELECT p3, count(*) 
    FROM v$session_wait 
    WHERE event='buffer busy wait'
  ;
  

  If p3 is less than 200 then it is an I/O problem. Either improve I/O performance or change application. Applications running concurrent batch jobs that do full table scans on the same large tables run into this problem.

• Free List – Use ASSM (or freelists groups)

Source : http://logicalread.solarwinds.com/oracle-buffer-busy-wait-event/