DMLTransaction Info in oracle

For each DML operation, a transaction id is initiated. We can get the transaction id by below query:

select dbms_transaction.local_transaction_id from dual;

PAUL @ pauldb-uat > select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
8.10.4224

the transaction ID is a series of numbers denoting undo segment number, slot# and record# (also known as sequence#) respectively, separated by periods.

To get the details about a transaction we can use the below query:

select

    owner               object_owner,

    object_name         object_name,

    session_id          oracle_sid,

    oracle_username     db_user,

    decode(LOCKED_MODE,

        0, 'None',

        1, 'Null',

        2, 'Row Share',

        3, 'Row Exclusive',

        4, 'Share',

        5, 'Sub Share Exclusive',

        6, 'Exclusive',

        locked_mode

    )                   locked_mode

    from v$locked_object lo,

        dba_objects do

    where

        (xidusn||'.'||xidslot||'.'||xidsqn)

            = ('&transid')

    and

        do.object_id = lo.object_id;

Clearing ITL Slots in Oracle

For the DML operation, transaction keeps entry in the blocks containing the target rows. These entries are maintained by ITL(Interested Transaction List). Now our interest is in the question when this ITL entries will be cleared.

To answer that question, we can consider this scenario: a transaction updates 10000 records, on 10000 different blocks. Naturally there will be 10000 ITL slots, one on each block, all pointing to the same transaction ID. The transaction commits; and the locks are released. Should Oracle revisit each block and remove the ITL entry corresponding to the transaction as a part of the commit operation?

If that were the processing logic, the commit would have taken a very long time. Acquiring the buffers of the 10000 blocks and updating the ITL entry will not be quick; it will take a very long time, prolonging the commit processing. Target of the Oracle design  that the commit processing is actually very quick, with a flush of the log buffer to redo logs and the writing of the commit marker in the redo stream. Even a checkpoint to the datafiles is not done as a part of commit processing – all the effort going towards making the process fast, very fast. Had Oracle added the logic of altering ITL slots, the commit processing would have been potentially long, very long. Therefore Oracle does not remove the ITL entries after that transaction ends (by committing, or rolling back); the slots are just left behind as artifacts.

So, when does the ITL entry gets cleared? When block’s buffer is written to the disk, the unneeded ITL entries are checked and cleared out.

How ITL Slots are maintained in Block

When a transaction modifies rows, then the transaction locks the rows (since it did not commit) by placing a special type of data in the block header known as Interested Transaction List (ITL) entry. The ITL entry shows the transaction ID and other information.

Now we assume, there are 5 records in the block and a transaction updated (and therefore locked) all five of them, how many ITL entries will be used – one or five?

We think five ITL slots may be feasible; but what if the block has 10,000 records? Is it possible to have that many ITL slots in the block header? Let’s ponder on that for a second. There will be two big issues with that many ITL slots.

First, each ITL slot, by the way, is 24 bytes long. So, 10000 slots will take up 240,000 bytes or almost 22 KB. A typical Oracle block is 8KB (We know, it could be 2K, 4K or 16K; but suppose it is the default 8K). Of course it can’t accommodate 22KB.

Second, even if the total size of the ITL slots is less than the size of the block, where will be the room to hold data? In addition, there should be some space for the data block overhead; where will that space come from?

Obviously, these are genuine problems that make one ITL slot per row impractical. Therefore Oracle does not create an ITL entry for each locked row. Instead, it creates the ITL entry for each transaction, which may have updated a number of rows. Let me repeat that – each ITL slot in the block header actually refers to a transaction; not the individual rows. That is the reason why you will not find the rowid of the rows locked in the ITL slot. 

There is reference to a transaction ID; but not rowid. When a transaction wants to update a row in the block, it checks the ITL entries. If there is none, it means rows in that block are unlocked. However, if there are some ITL entries, does it mean that some rows in the block are locked? Not necessarily. It simply means that the rows the block were locked earlier; but that lock may or may not be active now. To check if a row is locked, the transaction checks for the lock byte stored along with the row.

The if the presence of an ITL slot does not mean a record in the block is locked, when does the ITL slot get cleared so that it can be reused, or when does that ITL slot disappear? Is there no effect of Commit and RollBack to clear the ITL slot.

ITL- Interested Transaction List

Oracle keeps note of which rows are locked by which transaction in an area at the top of each data block known as the 'interested transaction list'. The number of ITL slots in any block in an object is controlled by the INITRANS and MAXTRANS attributes. INITRANS is the number of slots initially created in a block when it is first used, while MAXTRANS places an upper bound on the number of entries allowed. Each transaction which wants to modify a block requires a slot in this 'ITL' list in the block.

If multiple transactions attempt to modify the same block, they can block each other if the following conditions are fulfilled:

- There is no free ITL ("Interested Transaction List") slot available. Oracle records the lock information right in the block and each transactions allocates an ITL entry. 

- Insufficient space in the block left to add a new ITL slot. Since each ITL entry requires a couple of bytes a new one cannot be created if the block doesn't have sufficient free space.

The INITRANS and MAXTRANS settings of a segment control the initial and maximum number of ITL slots per block. The default of INITRANS in recent Oracle releases is 1 resp. 2 for indexes and the default value for MAXTRANS is 255 since the 10g release.

The following example demonstrates the issue. A block is almost full and several transactions attempt to manipulate different rows that all reside in this block.


The ITL in the "enq: TX - allocate ITL entry" indicates error is for "Interested Transaction List", and there are several approaches to fixing this error:
1 - Increasing the value of INITRANS and/or MAXTRANS for the table and indexes.
2 - Move the table to a smaller blocksize.
3 - In some cases, you can remove the enq: TX - allocate ITL entry error for UPDATE/DELETE DML issues by reorganizing the table to increase PCTFREE for the table, thereby leaving less rows per data block.
4 - Reduce the degree of parallel DML on this table

Parallel Execution Wait Events in Oracle

Oracle Parallel Execution can help utilize the power of your hardware and yet remains under-used. 

It is an interesting technology that is particularly suited to data warehousing, in that it allows a single user (or small set of users) to effectively soak up all of the server resources to satisfy a particular query. According to AskTom article by Tom Kyte on parallel query that said that this isn't always what you want - you wouldn't for example want individual OTLP users soaking up all resource for regular transactions or queries - but parallel query is an excellent way to effectively use up all the available CPUs and disk units when you've got a particularly big warehouse query.

Oracle documentation states that these are main wait/idle events because they indicate the normal behavior of a process waiting for another process to do its work:

• PX Deq: Table Q Normal 
• PX Deq: Execute Reply 
• PX Deq Credit: send blkd 

PX Deq: Table Q Normal

Indicates that the slave wait for data to arrive on its input table queue. In a parallel execution environment we have a producer-consumer model. One slave set works on the data ( e.g. read data from disk , do a join ) called the producer slave set and the other slave set waits to get the data so can start the work. The slaves in this slave set are called consumer. The wait event "PX Deq: Table Q Normal" means that the slaves in the consumer slave have to wait for rows (data) from the other slave set so they can start their work. 

PX Deq: Execute Reply 

The QC is expecting a response (acknowledgment) to a control message from the slaves or is expecting to dequeue data from the producer slave set. This means he waits that the slaves finished to execute the SQL statement and that they send the result of the query back to the QC. 

PX Deq Credit: send blkd 

The wait events "PX Deq Credit: need buffer" and "PX Deq Credit: send blkd" are occur when data or messages are exchanged between process that are part of a px query. 

There is an another event: The PX qref latch event can often mean that the Producers are producing data quicker than the Consumers can consume it. On this particular system, very high degrees of parallelism were being used during an overnight batch run so a great deal of messaging was going on. Maybe we could increase parallel_execution_message_size to try to eliminate some of these waits or we might decrease the DOP(Degree of Parallelism).

Sending mail by PL Sql in oracle

A stored procedure, to send mail can be as:

We are using a new type in the SP for containing the CC mail list. For this purpose, we can do as:


CREATE OR REPLACE TYPE mail_ccs AS TABLE OF VARCHAR2;

SP Definition:

CREATE OR REPLACE PROCEDURE sendMail(
    username       IN VARCHAR2,
    password       IN VARCHAR2,
    smtpHost       IN VARCHAR2,
    smtpPort       IN PLS_INTEGER DEFAULT 25,
    mailFrom       IN VARCHAR2,
    rcptTo         IN VARCHAR2,
    ccs            IN mail_ccs,
    messageSubject IN VARCHAR2,
    messageBody    IN VARCHAR2)
IS
  l_conn UTL_SMTP.connection;
  l_ccs              VARCHAR2(2000);
  l_encoded_username VARCHAR2(200);
  l_encoded_password VARCHAR2(200);
BEGIN
  --open connection
  /*
  l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.helo(l_conn, smtpHost);
  */
  --In case of authentication needed
  l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
  l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
  l_conn             := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
  UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
  UTL_SMTP.command(l_conn, l_encoded_username);
  UTL_SMTP.command(l_conn, l_encoded_password);
  --prepare headers
  UTL_SMTP.mail(l_conn, mailFrom);
  UTL_SMTP.rcpt(l_conn, rcptTo);
  /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
  however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
  IF ccs IS NOT NULL THEN
    FOR i IN ccs.FIRST..ccs.LAST
    LOOP
      UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
      l_ccs:=l_ccs||ccs(i)||',';    --mark as CC
    END LOOP;
    --now remove the trailing comma at the end of l_ccs
    l_ccs:=SUBSTR(l_ccs,0,LENGTH(l_ccs)-1 );
  END IF;
  --start multi line message
  UTL_SMTP.open_data(l_conn);
  --prepare mail header
  /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
  and the mail date will appear as 01/01/1970*/
  UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);
  --include the message body
  UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);
  --send the email
  UTL_SMTP.close_data(l_conn);
  UTL_SMTP.quit(l_conn);
END; 



To use the above SP, we can execute that by calling below code:

DECLARE
  USERNAME VARCHAR2(200);
  PASSWORD VARCHAR2(200);
  SMTPHOST VARCHAR2(200);
  SMTPPORT BINARY_INTEGER;
  MAILFROM VARCHAR2(200);
  RCPTTO VARCHAR2(200);
  CCS PAUL.MAIL_CCS;
  MESSAGESUBJECT VARCHAR2(200);
  MESSAGEBODY VARCHAR2(200);
BEGIN
  USERNAME := 'paul.pronabananda';
  PASSWORD := 'paulpassword';
  SMTPHOST := '172.20.1.1';
  SMTPPORT := 50;
  MAILFROM := 'paul.pronabananda';
  RCPTTO := 'paul.pronabananda@gmail.com';
  -- Modify the code to initialize the variable
  -- CCS := NULL;
  MESSAGESUBJECT := 'Test';
  MESSAGEBODY := 'Test Body';

  SENDMAIL(
    USERNAME => USERNAME,
    PASSWORD => PASSWORD,
    SMTPHOST => SMTPHOST,
    SMTPPORT => SMTPPORT,
    MAILFROM => MAILFROM,
    RCPTTO => RCPTTO,
    CCS => CCS,
    MESSAGESUBJECT => MESSAGESUBJECT,
    MESSAGEBODY => MESSAGEBODY
  );
--rollback; 

END;

If mail server address is not already added in the acl list. in that case we will have to add that info in the acl:

If ACL not exists, at first we need to create ACL by below code:

BEGIN 

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 

acl => 'utlpkg.xml', 

description => 'created by paul', 

principal => 'PAUL', 

is_grant => TRUE, 

privilege => 'connect', 

start_date => null, 

end_date => null); 

END;

After creation, we are in need to add the host IP and port in that ACL using below:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'utlpkg.xml',

    host        => '172.20.1.1', 

    lower_port  => 1,

    upper_port  => 100);   

  COMMIT;

END;

--

Retrieving Query with its Bind Variable Value

To increase query performance, using bind variable is an important factor. In some cases we need to execute a SQL Query for multiple tomes and changes exist only in literal value, in such case experts suggest for using bind varibale; and results is significant on perspective of query performance.

For the case of retrieving query from the database,the value of the bind variable is not present in the query text. But we may need those values when we are working on that SQL query specially when we try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID:

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING 

FROM v$sql_bind_capture WHERE sql_id='';

2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING

FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';

Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values 

which are given in HWERE clause and they do not store timstamp,lob,clob type value

Disk Consumption with Average Incremental Value

SELECT b.tsname tablespace_name ,
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2)avg_increas_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM DBA_HIST_TBSPC_SPACE_USAGE tsu ,
      DBA_HIST_TABLESPACE_STAT ts ,
      DBA_HIST_SNAPSHOT sp,
      DBA_TABLESPACES dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
GROUP BY b.tsname
ORDER BY b.tsname;

SQL Plan Management (SPM) in Oracle

Many times we experience performance regressions because of many things i.e a execution plan has been changed.For this type of problem there is an elegant solution  called SQL Plan Management (SPM). 

Execution plan changes occur due to various system changes. For example, you might have (manually or automatically) updated statistics for some objects, or changed a few optimizer-related parameters. A more dramatic change is a database upgrade (say from 10gR2 to 11g). All of these changes have the potential to cause new execution plans to be generated for many of your SQL statements. Most new plans are obviously improvements because they are tailored to the new system environment, but some might be worse leading to performance regressions. 

DBAs have several options for addressing these regressions. However, what most DBAs want is simple: plans should only change when they will result in performance gains. In other words, the optimizer should not pick bad plans, period. 

SQL plan management provides a mechanism for maintaining consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

How SPM Works:

When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer compares the plan it just produced with the plans in the SQL plan baseline. If a matching plan is found that is flagged as accepted the plan is used. If the SQL plan baseline doesn't contain an accepted plan matching the one it just created, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, so they are considered non-reproducible, the optimizer will use the plan with the lowest cost.

Oracle call this a "conservative plan selection strategy", as the optimizer preferentially uses a tried an tested execution plan, even if a new plan looks like it might perform better. Only when the newer plan is proved to perform well will it be accepted for use.

SQL PLAN MANAGER (SPM) has three major components

SQL Plan Baseline Capture - Create SQL Plan Baseline for trusted (accepted) SQL plans.

SQL Plan Baseline Selection - Conform that only accepted SQl plans are used when an SQL statement is executed.

SQL Plan Baseline Evolution - Evaluated all SQL Plans (old & new) for each SQL statement. The new/old plan will only be excepted if it gives better or equal performance compared with existing trusted plan otherwise rejected. (If no trusted plan exist then current plan will be accepted)

To enable this cool feature we just do the followings:

ALTER SYSTEM set optimizer_capture_sql_plan_baselines= TRUE scope=both; [Default value is false]

ALTER SYSTEM set optimizer_user_sql_plan_baselines= TRUE scope=both; [Default value is true, so just check the parameter value]

Global Resource Background Process

Global Cache Service (GCS) and Global Enqueue Service (GES) are the two important services in RAC Database. These services are basically collection of background processes.  These two processes together cover and manage the total Cache Fusion process, resource transfers, and resource escalations among the instances.

These play the key role in implementing Cache Fusion. GCS ensures a single system image of the data even though the data is accessed by multiple instances. The GCS and GES are integrated components of Real Application Clusters that coordinate simultaneous access to the shared database and to shared resources within the database and database cache. GES and GCS together maintain a Global Resource Directory (GRD) to record information about resources and enqueues. GRD remains in memory and is stored on all the instances. Each instance manages a portion of the directory. This distributed nature is a key point for fault tolerance of the RAC. The following resources information is available in GRD.

• Data Block Identifiers (DBA)
• Location of most current version 
• Modes of the data blocks: (N)Null, (S)Shared, (X)Exclusive
• The Roles of the data blocks (local or global) held by each instance
• Buffer caches on multiple nodes in the cluster

The coordination of concurrent tasks within a shared cache server is called synchronization. Synchronization uses the private interconnect and heavy message transfers. The following types of resources require synchronization: data blocks and enqueues. GCS maintains the modes for blocks in the global role and is responsible for block transfers between instances. LMS processes handle the GCS messages and do the bulk of the GCS processing.

An enqueue is a shared memory structure that serializes access to database resources. It can be local or global. Oracle uses enqueues in three modes: null (N) mode, share (S) mode, and exclusive (X) mode. Blocks are the primary structures for reading and writing into and out of buffers. An enqueue is often the most requested resource.

GES maintains or handles the synchronization of the dictionary cache, library cache, transaction locks, and DDL locks. In other words, GES manages enqueues other than data blocks. To synchronize access to the data dictionary cache, latches are used in exclusive (X) mode and in single-node cluster databases. Global enqueues are used in cluster database mode.

Global Resource Directory (GRD) is the internal database that records and stores the current status of the data blocks. Whenever a block is transferred out of a local cache to another instance's cache the GRD is updated. The following resources information is available in GRD.

Atomic Control File to Memory Service (ACMS):
In a RAC environment, the ACMS per-instance process is an agent that contributes to ensuring that a distributed SGA memory update is either globally committed on success or globally aborted if a failure occurs.

Global Enqueue Service Monitor (LMON):
The LMON process monitors global enqueues and resources across the cluster and performs global enqueue recovery operations.

Global Enqueue Service Daemon (LMD): 
The LMD process manages incoming remote resource requests within each instance.

Global Cache Service Process (LMS): 
The LMS process maintains records of the data file statuses and each cached block by recording information in the GRD. The LMS process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances. This processing is part of the cache fusion feature.

Instance Enqueue Process (LCK0): 
The LCK0 process manages noncache fusion resource requests such as library and row cache requests.

Global Cache/Enqueue Service Heartbeat Monitor (LMHB):
LMHB monitors LMON, LMD, and LMSn processes to ensure that they are running normally without blocking or spinning.

Result Cache Background Process (RCBG): 
This process is used for handling invalidation and other messages generated by server processes attached to other instances in Oracle RAC.