13 June, 2014

Gather Statistics Enhancements in 12c

Here are 5 posts that I did on Gather Statistics Enhancements in 12c :

1.  During a CTAS

2.  In a Direct Path INSERT

3.   Reports on Statistics

4.  Does not COMMIT a GTT

5.  Report on COL_USAGE
.
.
.


Getting your Transaction ID

You can get the Transaction ID for a session by joining V$SESSION.TADDR to V$TRANSACTION.ADDR.

A Transaction ID consists of the Undo Segment #, the Slot # and the SEQ #.

For example :
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
6.3.9463

SQL>

Thus, my current transaction is in Undo Segment 6, Slot 3, Sequence 9463.
SQL>    select count(*) from v$transaction;

  COUNT(*)
----------
         1

SQL> col username format a12
SQL> l
  1  select s.username, s.sid, s.serial#,
  2  t.xidusn, t.xidslot, t.xidsqn
  3  from v$session s, v$transaction t
  4* where s.taddr=t.addr
SQL> /

USERNAME            SID    SERIAL#     XIDUSN    XIDSLOT     XIDSQN
------------ ---------- ---------- ---------- ---------- ----------
HEMANT               38         23          6          3       9463

SQL> 

As soon as I commit or rollback or issue a DDL or a CONNECT, the transaction ends.
SQL> rollback;

Rollback complete.

SQL> select count(*) from v$transaction;

  COUNT(*)
----------
         0

SQL> 

Remember that an Undo segment can hold multiple transactions. That is why the Undo Segment Header has multiple "slots", one for each active transaction.  Once a transaction completes (and likely after the undo_retention period), a slot can be reused with an incremented seq#.
.
.
.

02 June, 2014

Guenadi Jilevski's posts on building RAC Clusters on VM Virtual Box

Guenadi Jilevski has a few posts on building Oracle RAC Clusters on VM Virtual Box

1.  11gR2 RAC co-existing with 10gR2

2.  11gR2 RAC using GNS

3.  12c RAC

Note : Unfortunately, I haven't had the time and resources to build and test clusters using these instructions.

.
.
.