Load Balance SMTP with F5 BIG-IP

The F5 BIG-IP has a template for Exchange 2010 which assists administrators with configuring load balancing for Outlook Anywhere, Active Sync and Outlook Web App. This template does not configure SMTP load balancing. There are many circumstances where you may want an SMTP endpoint IP address to be highly available and load balanced between multiple hub transport servers.

In this post I will go through and show you how to configure the BIG-IP LTM for load balancing the SMTP protocol and the challenges associated with this. This article was written using the F5 BIG-IP LTM VE version 10.2.3.

Create a Health Monitor

Create a health monitor which monitors the Exchange 2010 SMTP service on our Exchange 2010 servers. The heath monitor will send SMTP HELO requests on a regular basis to ensure the SMTP servers are healthy.

Expand Local Traffic and click Add next to Monitors.

I called the monitor SMTP_Monitor. Set the Type to SMTP. Provided an interval of 120 seconds meaning the monitor will send an SMTP HELO every 2 minutes to the Ex2010 servers to see if they are still online. Configured the Alias service port to 25.

Create a Pool for the SMTP Servers

A load balancing pool is a logical set of devices, in our case SMTP servers, that you group together to receive and process traffic. To create a new pool under Pool List click Add.

I called the pool smtp_pool. Select the SMTP_Monitor we created earlier. Select the load balancing method as Round Robin. Add the SMTP servers to our pool in which we wish to distribute inbound SMTP connections to.

Create an SMTP Virtual Server

Create an SMTP Virtual Server on the F5 BIG-IP which will allow the BIG-IP system to listen on TCP25 to load balance incoming SMTP sessions. To do this under Virtual Servers –> Virtual Server List click add.

I called the SMTP Virtual Server SMTP_VS. Under Destination I specified 172.16.51.174. This is the Virtual IP the BIG-IP will listen on for incoming SMTP traffic. Select a service port of 25 and place the device in an enabled state.

Under configuration set SNAT Pool to Auto Map (I have explained what Auto Map is below).

Scroll down further and under resources set the Default Pool to smtp_pool along with the default persistence profile to source_addr.

Test our BIG-IP SMTP Virtual Server

The F5 BIG-IP device should now be configured to load balance SMTP requests between the two Exchange 2010 servers. In your Virtual Server List the SMTP_VS should come up green.

Apply my new iRule to the SMTP Virtual Server

Next we need to attach the iRule to the SMTP virtual server in the F5 configuration screen. To do this go to your Virtual Servers –> Click Virtual Server List then select our SMTP_VS created earlier.

Select Resources then under iRules click Manage.

Select our smtp_irule out of the list available then click finish.

Testing our Configuration

So time to test the configuration to ensure it works as configured. Lets telnet my BIG-IP SMTP Virtual Server from the host we allowed 172.16.51.23 by running the following command:

telnet 172.16.51.174 25

I then wrote some random comments in the telnet session so we can identify our server 172.16.51.23 in our SMTP logs.

I then repeated the procedure from another server which is not in our Data Group List created above.

In our SMTP logs on our Exchange 2010 server as expected the 172.16.51.23 server came from 172.16.51.174 and the non trusted IP came from 172.16.51.175.

From a command prompt verify you can telnet our SMTP virtual server on 172.16.51.174 on port 25.

We can see that it successfully connected to one of the SMTP servers in our load balancing pool “smtp_pool”

At this point your F5 BIG-IP is successfully load balancing SMTP.

The Problem…

When building an email solution it is absolutely critical to avoid becoming an open SMTP relay. Most organisations implement relay restrictions by locking anonymous relay down to certain source IP addresses on their internal network such as applications and printers. Source IP is generally the preferred method as Administrators do not have to deal with SMTP authentication methods. The list of IP addresses who are allowed relay anonymously are usually configured on the Exchange SMTP receive connectors. However when dealing with load balancers such as a F5 BIG-IP Local Traffic Manager this becomes a difficult task.

Why?

Whilst load balancing connections the F5 BIGIP uses SNAT to re-write the source IP address on the SMTP packets to one of its “Self IP” addresses or “Virtual IP” addresses. This means the Exchange servers will see all requests coming from the same IP address making it impossible to determine which request belongs to what client. This is illustrated in the following diagram:

However I have a workaround for you. If we setup two SNAT addresses on the F5 BIG-IP for example 172.16.51.174 and 172.16.51.175 we can configure our BIG-IP to say any source IP addresses that need to be an anonymous open relay hit our Exchange 2010 servers from 172.16.51.174 ELSE hit our Exchange 2010 servers from 172.16.51.175. This solution means we need to configure our list of allowed IP addresses for SMTP relay on our F5 BIG-IP instead of our Exchange SMTP Receive Connectors.

Create a Data Group List

First we must create a list of IP addresses we want to allow anonymous relay for on our F5 BIG-IP. These are the IP addresses we would normally configure on our Exchange receive connectors. To do this we need to create a new data group list.

Add a new data group list by expanding iRules –> Data Group List and clicking the add button.

I called my Data Group List smtp_relay_allowed and specified the IP address 172.16.51.21. You can add as many IP addresses as you want for anonymous relay.

Create a new iRule

An iRule is a powerful and flexible feature of BIG-IP devices which provide you with unprecedented control to directly manipulate and manage any IP application traffic. By creating an iRule we can instruct the BIG-IP to return a different SNAT address based on on the condition. We want to instruct our BIG-IP to perform the following:

IF a clients source IP is on our Data Group List THEN use an SNAT address of 172.16.51.174 ELSE use the SNAT address of 172.16.51.175.

To create the iRule under Local Traffic Select iRule –> iRule List and click the add button.

I called my iRule smtp_irule and created the following code to perform my required conditions as mentioned above.

A copy of the code:

when CLIENT_ACCEPTED {
set accepted_snat “172.16.51.174”

if { [ class exists smtp_relay_allowed ] }
{
if { [class match [IP::client_addr] equals $::smtp_relay_allowed] }
{
snat $accepted_snat
} else {
snat automap
}
} else {
snat automap
}
}

Automap is a feature of the BIGIP where it automatically selects a Self IP at random to use for the SNAT translation. A Self IP is an IP you have assigned to the BIGIP manually under your network configuration. This is different to a Virtual IP address which is created when you setup a virtual server. I only have one Self IP on my BIG IP set to 172.16.51.175 and one virtual IP set to 172.16.51.174 used by all my F5 virtual servers on different TCP ports. As a result automap will ONLY select 172.16.51.175.

Why would you want multiple source SNAT IP addresses?

For each connection made from the BIG-IP to your load balanced servers a TCP source port needs to be opened for the communication. TCP only has 65535 ports for source and destination traffic so if the number of connections exceeded the number of available ports, the BIG-IP would not be able to take new connections. In this event you could add an additional Self IP and rely on the Automap feature or create an SNAT pool which is a predefined list of IP addresses the BIG-IP is allowed to use for SNAT.

Advertisements

Siebel SWSE Stats to identify performance issue

Yesterday came across this Oracle support Article Doc ID 478027.1 while trying to debug performance issue. This article provide details aboutStats.swe page that can help you provide valuable information to troubleshoot some hang issues with the application.

To visit the stats page you need to open following URL

http://<webserver>/<application>/_stats.swe?verbose=high

It shows all kind of stats and the most important section is Current Operations Processing. The “Current Operations Processing” section contains a table which shows all current requests that are in progress. The table in this section shows the Operation running in the left column and the Duration in right column. Requests that are highlighted in bold have been running for more than 10 seconds. A request that is highlighted in bold with a large duration value is another indication that this request may be hanging. If the request never completes then it has effectively hung.

Both application and database server hangs can exhibit the above behavior in the stats page. Typically if the stats page cannot be accessed, then this is an indication that the web server itself may have hung.

Below is the screenshot of how the page looks like:

image

On Oracle Database 12c, Part 2

Row Pattern Matching

In the beginning, SQL provided the ability to look left and right in a result set. That is, you could look at a row of data, possibly the result of a join of many tables, and apply predicates to that row. You could compare any column in that row with any other column in that row. However, you could not look up and down in a result set. The ability to look up and down was included in Oracle8i Database in the form of analytic windowing functions. Using these analytic functions, you could look not only left and right in a result set but also up and down, using windowing functions such as LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE. These windowing functions are extremely powerful, and they opened up a whole new way of analyzing data with SQL. As powerful as they are, however, they had some limitations.

Often when processing data, you want to recognize a pattern in an ordered stream of data. For example, you might have an audit trail of data, with columns representing the username, an event that took place, and a time stamp representing when that event took place. You might be interested in finding all the people in that audit trail who used “application X” then “application Y” and then “application Z” and, finally, went back to a specific part of “application X.” Preceding, following, and in between those events might be countless other events. For some users, there might be no events between their interesting actions, but for other users, there might be hundreds or thousands of events. Trying to find such a pattern with analytic windowing functions falls short. You don’t know how many rows backward or forward in the result set you would have to look—LAG and LEAD analysis won’t really help. At the very least, you would have to make multiple passes on the data, use multiple self-joins, or resort to scalar subqueries. The query you’d need to write would be extremely complex, and the cost of executing it would be extremely high.

Enter row pattern matching, implemented via the MATCH_RECOGNIZE clause in Oracle Database 12c. This clause enables you to take a set of data (a result set—your audit trail, for example); partition it into nonoverlapping sets of data (by username in your audit trail); sort these sets (by time stamp in your audit trail); and then look for a pattern that spans many rows in that partitioned, ordered set. With the audit trail example, you would query all records for applications X, Y, and Z. You would partition the records by username and sort within each username by time stamp. Then you would look for one or more Xs, followed by one or more Ys, followed by one or more Zs, and finally followed by the specific part of application X you were interested in. You can do all this processing in a single pass through the data—no self-joins, no Cartesian joins, and no scalar subqueries are required. The query would be relatively easy to code—the MATCH_RECOGNIZE clause is very compact—and the performance of this query would be much better than that of one written without the MATCH_RECOGNIZE clause.

To demonstrate this new feature, I am going to analyze a set of stock data. Stock analysts are often interested in seeing a V- or W-shaped pattern in stock data. That is, they would like to know at which points in time a stock hits a high value, followed by a series of drops in price and then followed by a series of rises in price. They’d like to know when the pattern that defines the V shape started, when it hit the bottom, and when it hit the top again. I’ll start this example by defining a simple stock table:

SQL> create table stocks
  2  ( symbol   varchar2(10),
  3    tstamp   date,
  4    price    number,
  5    primary key (symbol,tstamp)
  6  )
  7  organization index
  8  /
Table created.

Now I’ll create a bit of data to analyze:

SQL> declare
  2    l_data sys.odciNumberList :=
  3           sys.odciNumberList
  4           ( 35, 34, 33, 34, 35,
  5           36, 37, 36, 35, 34, 35, 
                              36, 37 );
  6    l_cnt  number := l_data.count;
  7  begin
  8    for i in 1 .. l_cnt
  9    loop
 10        insert into stocks
 11        ( symbol, tstamp, price )
 12        values
 13        ('XYZ', sysdate-l_cnt+i, 
                           l_data(i) );
 14    end loop;
 15    commit;
 16  end;
 17  /
PL/SQL procedure successfully completed.

Because this set of data is so small, I can analyze it with ASCII art in SQL*Plus easily, as shown in Listing 1.

Code Listing 1: Displaying ASCII art patterns

SQL> select symbol, tstamp, price,
  2         rpad('*',price,'*') hist
  3    from stocks
  4   order by symbol, tstamp;

SYMBOL     TSTAMP         PRICE HIST
—————————— —————————————— ————— ——————————————————————————————————————
XYZ        01-SEP-12         35 ***********************************
XYZ        02-SEP-12         34 **********************************
XYZ        03-SEP-12         33 *********************************
XYZ        04-SEP-12         34 **********************************
XYZ        05-SEP-12         35 ***********************************
XYZ        06-SEP-12         36 ************************************
XYZ        07-SEP-12         37 *************************************
XYZ        08-SEP-12         36 ************************************
XYZ        09-SEP-12         35 ***********************************
XYZ        10-SEP-12         34 **********************************
XYZ        11-SEP-12         35 ***********************************
XYZ        12-SEP-12         36 ************************************
XYZ        13-SEP-12         37 *************************************

This set of data is only 13 rows, so using a brute force method to analyze it works and I can readily see the pattern I am looking for. I can see clearly two V shapes in this data. I can see that on September 1, I have the beginning of a V, which bottoms out on September 3 and peaks again on September 7. I can see the second V easily, too; it begins on the 7th (the beginning of the second pattern is the end of the first—think about how you’d report on that with basic SQL), bottoms out on the 10th, and peaks again on the 13th. I am interested, therefore, in getting two rows of data—each with the three relevant dates. I would like you to think about how you might write a SQL statement in Oracle Database 11g Release 2 and how, before that, you might output two rows (repeating September 7, which is tricky) with the information I am looking for. It can be done, but it is rather complex and the performance would be questionable at best.

With the MATCH_RECOGNIZE clause, this is rather easy to query. For example:

SQL> SELECT *
  2  FROM stocks MATCH_RECOGNIZE
  3  ( PARTITION BY symbol
  4    ORDER BY tstamp
  5    MEASURES
  6       STRT.tstamp AS start_tstamp,
  7       LAST(DOWN.tstamp) AS 
          bottom_tstamp,
  8       LAST(UP.tstamp) AS end_tstamp
  9    ONE ROW PER MATCH
 10    AFTER MATCH SKIP TO LAST UP
 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)
 15  ) MR
 16   ORDER BY MR.symbol, 
               MR.start_tstamp;

SYMBOL  START_TST BOTTOM_TS END_TSTAM
——————  ————————— ————————— —————————
XYZ     01-SEP-12 03-SEP-12 07-SEP-12
XYZ     07-SEP-12 10-SEP-12 13-SEP-12

I’ll walk through this query line by line. The first two lines define the query that defines the result set I’ll partition and order and then apply my pattern to. This query can be any query—with joins, aggregation, and so on. The MATCH_RECOGNIZE clause starts on the end of line 2 and is contained in lines 3 through 15.

On lines 3 and 4, I set up my partitions and order this stream of data. I partition by the stock symbol, and within each of these stocks, I order the data by time stamp, from oldest to newest. Note that my result set is deterministic here. Because my primary key is SYMBOL, TSTAMP, the set of rows will be deterministic from run to run of this query. This fact is typically very important in pattern matching—you want to know that the rows that form your pattern are being observed in a “stable” state, a deterministic state. Otherwise, two executions of the same query might return different answers! Seebit.ly/Z6nxLL for a further explanation and examples of why this deterministic behavior is desirable, and usually necessary, for correct results.

Now I’m going to skip ahead a bit to the pattern part of the query:

 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)

I’m interested in a data pattern in which I have any record, followed by one or more records in which the price of the stock goes down, followed by one or more records in which the stock price increases. That is what my pattern on lines 11 through 14 represents. I’m looking for a starting record—any record—followed by one or more “down” records, followed by one or more “up” records. To identify “any record,” I use the correlation name STRT, short for start. (That name is rather arbitrary—I could have used any valid identifier.) Because STRT is not in the DEFINE, the definition section, it can match any record—every record in the result set could potentially match and become a STRT record. For a record to become the STRT record, however, it must be followed by a DOWN record, at least one and possibly more (that is the meaning of the + after DOWN). Now, DOWN does have a definition in this case: a record can be considered a DOWN record if and only if the price of that record is less than the price of the previous record in the result set (remember, it is a deterministically ordered stream of data). As long as I have records that match this definition, I’ll be matching DOWN records. When I ultimately get to a record that no longer meets the criterion for being a DOWN record, I’ll continue the pattern matching in the hopes that it is an UP record. An UP record is defined as any record whose price is greater than the previous record’s price.

Now I’ll walk through the pattern in lines 11 through 14 with my actual data set. I start by processing the first record, for September 1. That record meets the criterion for being a STRT record, because there is no defining criterion. The first record can be the STRT record of a pattern match only if the following record is a DOWN record. So, I advance in the result set, get the second record, and apply the definition of DOWN to it. Is its price of 34 less than the prior record’s price, 35? Because it is, I am still matching my pattern; I can advance to the third record and apply the definition again. It is another DOWN record. Then I get to the fourth record—which is not a DOWN record—so I’ve finished matching the STRT DOWN+ portion of the pattern. I must now verify that the fourth record conforms to the definition of UP in my definition. And sure enough, it does. I’ve found the pattern—I know I have some record followed by one or more DOWN records followed by at least one UP record, but the pattern won’t stop here. Much as with regular expressions, I am going to continue matching to find the largest conforming pattern. I take a look at the fifth, sixth, and seventh records and find that they are all UP records, so they become part of the pattern. Then I get to the eighth record, and it does not match the definition of UP anymore, because its price is not greater than the prior record’s price. I am done with the first pattern, and the set of rows that match this pattern are September 1 through 7. I am now ready to output this information.

Going back to the MATCH_RECOGNIZE clause, a MEASURES clause starts on line 5. This identifies the outputs of my query, and in this case, I have asked for the rows in the pattern to be tagged with STRT.tstamp, the timestamp associated with the starting record, the beginning of my pattern. I also asked for LAST(DOWN.tstamp)—the last DOWN time stamp—and LAST(UP.tstamp)—the last UP time stamp. Those three values represent the beginning, the bottom, and the end of the V-shaped pattern. Line 9 of the query—ONE ROW PER MATCH—makes the MATCH_RECOGNIZE clause work very much like an aggregate function. For this one big pattern that spans seven rows of data, I’d like one row to be output with my three measures in it. This will help me analyze the data as I turn millions of rows into tens or hundreds or thousands of rows, which is something I can get my head around.

Now that I’ve found the first pattern, I’m ready to start looking for the next one. The question now becomes, “Where do I start looking for the next pattern?” I could start looking for the next pattern in the second row, but that wouldn’t make sense for this pattern, because I’d find V shapes inside of V shapes over and over again, which is not very interesting. Where I would like to start the search for the next pattern is on the last row of the first pattern. I want to pick up where I left off, and line 10 of the query—AFTER MATCH SKIP TO LAST UP—accomplishes that. It permits the last row of the first pattern in this case to potentially become the first row of the next pattern. And as you can see by the output, September 7 does, in fact, become the first row of the next pattern. September 7 is effectively output twice in this result set. (Think about what you would have to do in regular SQL to get a single row output twice in a result set!)

And that’s it. I take all the pattern matches, order them by the stock symbol and the starting price, and display them. In this case, I’ve taken 13 rows and turned them into 2 rows, each of them representing three points of data. It becomes very easy now to see these patterns and start to interpret them. You can see how you might further analyze and aggregate this data to answer questions such as “What is the average period of time elapsed in a V for a given stock symbol?” “What is the max time?” “How many V shapes do you usually see in a year?” “Is there any common time over the years or months when a stock bottoms out?” and so on.

For detailed information on the MATCH_RECOGNIZE syntax, seebit.ly/15x5p0o. In particular, the “SQL for Pattern Matching” chapter in Oracle Database Data Warehousing Guide 12c Release 1 (12.1) is an excellent resource to get started with.

Temporary Undo

I’ll now take a look at a new way to process undo for global temporary tables in Oracle Database 12c.

Many DBAs and developers used to be surprised to discover that their operations on global temporary tables—such as INSERT, UPDATE, MERGE, and DELETE—generated redo: less redo than they observed on a regular, permanent table but still a sizable amount. Their first question was, “Where does this redo come from?” The answer was that it comes from the undo that Oracle Database has to generate on that global temporary table. The undo for global temporary tables must be generated—the database needs it in case an application issues a rollback and to provide for read-consistent results. For situations in which a developer inserts some information into a global temporary table and then issues a SELECT statement against it, followed by an UPDATE or a DELETE, the rules of read consistency state that the SELECT statement cannot see the effects of the UPDATE or DELETE. To make that possible, the database needs that undo. (See bit.ly/120NB0w for more information on read consistency, one of the core tenets of Oracle Database).

So a modification of a global temporary table needs to generate undo, and the undo tablespace must be protected by redo. In the event of an instance crash, the database needs the redo information in order to recover the undo tablespace so it can then roll back any transaction that was in process but not yet committed when the instance failed. And furthermore, before Oracle Database 12c, the undo tablespace did not distinguish between undo for temporary objects and undo for permanent objects.

But starting in Oracle Database 12c, temporary undo can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will no longer generate redo. If you have large batch operations that utilize global temporary tables, you may well discover that the amount of redo you generate decreases by a large amount. Furthermore, you’ll be generating less undo in your undo tablespace. And that means you’ll be able to support a longer undo_retention time with a smaller undo tablespace.

Another pleasant side effect of this new change is that global temporary tables can now be utilized in a read-only Oracle Active Data Guard database. Yes, you can now have a read-only database in which you can read and write global temporary tables. Because one of the uses of a global temporary table has historically been in reporting systems—for storing intermediate query results—this makes a read-only Oracle Active Data Guard reporting database that much more viable.

How undo is generated in Oracle Database 12c for global temporary tables is controlled by a new init.ora parameter: temp_undo_enabled. It has two settings: TRUE and FALSE. By default, this parameter is set to FALSE and undo will be generated in the same fashion it was in the past. For example:

SQL> alter session 
set temp_undo_enabled = false;

Session altered.

SQL> insert into gtt
  2  select *
  3    from all_objects;
87310 rows created.

Statistics
———————————————————————————
…
     566304  redo size
…

SQ> update gtt
  2     set object_name = 
        lower(object_name);
87310 rows updated.

Statistics
————————————————————————————
… 
    8243680  redo size
…

As you can see, the INSERT generates about half a megabyte of redo (566,304 bytes) while the UPDATE generates upwards of 8 MB of redo (8,243,680 bytes). If I enable temporary undo, however:

SQL> alter session 
set temp_undo_enabled = true;

Session altered.

SQL> insert into gtt
  2  select *
  3    from all_objects;
87310 rows created.

Statistics
———————————————————————————————
…
        280  redo size
…

SQL> update gtt
  2     set object_name = 
        lower(object_name);
87310 rows updated.
Statistics
———————————————————————————————
…
          0  redo size
…

the redo is either trivial or nonexistent.

In a read-only Oracle Active Data Guard database, the redo would be entirely nonexistent. This means you’ll potentially be shipping less redo, applying less redo, and enjoying a longer undo retention period in your primary database.

On Oracle Database 12c, Part 1

The first three Oracle Database 12c features ;

  • Improved defaults
  • Bigger datatypes
  • Top-n queries

Improved Defaults

The ability to create a default column value has existed in SQL for a while. The functionality has been somewhat limited, however, with various restrictions. For example, you were restricted from using a SEQUENCE object to supply a default value. Additionally, if a default value was to be inserted into or updated in a table, you had to either use the DEFAULT keyword in the SQL statement or leave the column out of the INSERT statement entirely. Furthermore, adding a new column that permits NULL values with a default value was an offline operation. In Oracle Database 12c, however, these restrictions and functionality limitations have been removed.

Removed Restriction: Generating a Default Value from a SEQUENCE. In Oracle Database 12c, you can now use the .NEXTVAL attribute of a sequence to create a default column value. For example, this code

SQL> create sequence s;
Sequence created.
SQL> create table t
  2  ( x int
  3      default s.nextval
  4          primary key,
  5    y varchar2(30)
  6  );
Table created.

SQL> insert into t (x,y)
  2  values ( default, 'hello' );
1 row created.

SQL> insert into t (y)
  2  values ( 'world' );
1 row created.

SQL> select * from t;

         X  Y
  ————————  ————————
         1  hello
         2  world

demonstrates that you can create a default column value for the primary key from the sequence value—without using a trigger, as you would have in the past. So in Oracle Database 12c, DEFAULT S.NEXTVAL in the CREATE TABLE statement will replace the following procedural code:

SQL> create trigger t
  2  before insert on t
  3  for each row
  4  begin
  5    if (:new.x is null)
  6    then
  7       :new.x := s.nextval;
  8    end if;
  9  end;
 10  /
Trigger created.

In addition to using a reference to a sequence to create a default column value, you can alternatively use an IDENTITY type, which will generate a sequence and associate that sequence with the table. For example, this CREATE TABLE statement

SQL> create table t
  2  ( x int
  3      generated as identity
  4          primary key,
  5    y varchar2(30)
  6  )
  7  /
Table created.

will result in the same data’s being loaded into table T without your having to explicitly create a sequence (as you did in the CREATE TABLE statement that explicitly called DEFAULT S.NEXTVAL). You can see this sequence if you look at the schema:

SQL> select object_name, object_type
  2    from user_objects
  3  /

OBJECT_NAME              OBJECT_TYPE
——————————————————       —————————————
T                        TABLE
ISEQ$$_90241             SEQUENCE
SYS_C0010233             INDEX

But note that if you drop the table and purge it from the recycle bin, the sequence will be removed as well:

SQL> drop table t purge;
Table dropped.

SQL> select object_name, object_type
  2    from user_objects
  3  /
no rows selected

Because identity is using a sequence under the covers, you can also control all the settings of the underlying sequence. For example, this CREATE TABLE statement

SQL> create table t
  2  ( x int
  3      generated by default
  4          as identity
  5          ( start with 42
  6            increment by 1000 )
  7          primary key,
  8    y varchar2(30)
  9  )
 10  /
Table created.

shows that you can control the START WITH and INCREMENT BY values. Additionally, by using the GENERATED BY DEFAULT statement instead of just GENERATED, you can override the default identity value. Here as demonstrated this by inserting the value 1 and then two more rows, enabling identity to generate the default values:

SQL> insert into t (x,y)
  2  values ( 1, 'override' );
1 row created.

SQL> insert into t (x,y)
  2  values ( default, 'hello' );
1 row created.

SQL> insert into t (y)
  2  values ( 'world' );
1 row created.

SQL> select * from t;

         X  Y
——————————  ———————————
         1  override
        42  hello
      1042  world


Improved Functionality: Create a Default Value for a NULL Column.
 In Oracle Database 12c, you can now create a default column value not only when you use the DEFAULT keyword or leave the column entirely out of the INSERT statement but also when you set the column value explicitly to NULL.

In the past, if a column used a default value, you either had to use the DEFAULT keyword in the INSERT/UPDATE statement or leave the column entirely out of the INSERT/UPDATE statement. That meant that in order to use a default value at certain times and not others, you needed at least two INSERT/UPDATE statements with complicated if/then/else constructs. For example, if column X had a default value and you sometimes wanted to insert an overriding value and sometimes not, you would need code resembling the following:

if (x is_to_be_defaulted)
then
   insert into t (x, … ) 
   values ( DEFAULT, … );
else
   insert into t (x, … ) 
   values ( :x, … );
end if;

Now, that might be OK if you sometimes had to create a default value for one column, but what if you have two or three or more columns? Think of how many combinations of INSERTs or UPDATEs you would need with complex if/then/else blocks to support that. In Oracle Database 12c, you can now create a default column value when you explicitly put a NULL value into that column. Here’s an example:

SQL> create table t
  2  ( x number
  3      generated as identity
  4          primary key,
  5    y varchar2(30),
  6    z number default ON NULL 42
  7  )
  8  /
Table created.

By using z number default ON NULL 42, I’ve specified that column Z will receive the default value not only when I explicitly set it to DEFAULT or leave it out of the INSERT statement but also when I explicitly insert NULL into it, as in

SQL> insert into t (y)
  2  values ( 'just y' );
1 row created.

SQL> insert into t (y,z)
  2  values ( 'y with z set to null', 
null );
1 row created.

SQL> insert into t (y,z)
  2  values ( 'y and z', 100 );
1 row created.

SQL> select * from t;

   X  Y                            Z
————  ——————————————————————     ————
   1  just y                      42
   2  y with z set to null        42
   3  y and z                    100

As you can see, the Z column value is created with the default value 42 in both cases now. Also, the declaration for Z had the effect of defining it as NOT NULL, even though I did not explicitly state that:

SQL> select column_name, nullable
  2    from user_tab_columns
  3   where table_name = 'T'
  4   order by column_id
  5  /

COLUMN_NAME  N
———————————  —
X            N
Y            Y
Z            N

More Online Operations: Better Column Addition. In Oracle Database 11g you were able to perform a fast add of a column to a table if it had a default value and was defined as NOT NULL. (Arup Nanda has written about this at bit.ly/16tQNCh.) However, if you attempted to add a column with a default value and that column permitted null values, the ADD COLUMN operation could take a significant amount of time, generate a large amount of undo and redo, and lock the entire table for the duration of the operation. In Oracle Database 12c, that time, volume, and locking are no longer part of the process.

To demonstrate this, I copy ALL_OBJECTS into a table and measure its space—in blocks and bytes—using the show_space utility, posted on asktom.oracle.com:

SQL> create table t
  2  as
  3  select *
  4    from all_objects;
Table created.

SQL> exec show_space('T')
…
Full Blocks        ....        1,437
Total Blocks...........        1,536
Total Bytes............   12,582,912
Total MBytes...........           12
…

PL/SQL procedure successfully completed.

Now I add a column to table T, and this column will have a large default value. Because the column I’m adding is a CHAR(2000), it will always consume the full 2,000 bytes, given that the CHAR type is always blank-padded and fixed-width. Table T has more than 87,000 records, so adding a column would typically take a significant amount of time, but as you can see, the addition is practically instantaneous in Oracle Database 12c:

SQL> set timing on
SQL> alter table t 
add (data char(2000) default 'x');
Table altered.
Elapsed: 00:00:00.07

I perform the identical operation in Oracle Database 11g and observe the following timing:

SQL> set timing on
SQL> alter table t 
add (data char(2000) default 'x');
Table altered.
Elapsed: 00:00:28.59

Clearly, that’s a significant difference in runtimes. Plus, when I look at the size of the table with the additional column in Oracle Database 12c

SQL> exec show_space('T')
…
Full Blocks        ....        1,437
Total Blocks...........        1,536
Total Bytes............   12,582,912
Total MBytes...........           12
…

PL/SQL procedure successfully completed.

I see that the table did not grow at all. However, running the same test in Oracle Database 11g shows that the table will grow from about 9 MB to 192 MB. Additionally, in Oracle Database 11g, almost every row in the table was a migrated row, because the row grew by orders of magnitude. That table probably would be due for a reorganization in the prior releases but not in Oracle Database 12c.

Bigger Datatypes

Oracle8 Database provided a big increase in the size of VARCHAR types—from 255 bytes (in Oracle7) to 4,000 bytes. Now the Oracle Database 12c release increases the size from 4,000 bytes to 32 K, bringing the SQL VARCHAR2, NVARCHAR2, and RAW datatypes in line with their PL/SQL counterparts.

By default, this new capability is not enabled and would have to be enabled by the DBA’s setting the new MAX_STRING_SIZE init.ora parameter to EXTENDED. Once that’s done, you’ll be able to issue statements such as

SQL> create table t ( x varchar(32767) );
Table created.

and then use string functions such as RPAD, LPAD, and TRIM :

SQL> insert into 
t values ( rpad('*',32000,'*') );
1 row created.

SQL> select length(x) from t;

 LENGTH(X)
——————————————
     32000

In the past, RPAD and other string built-in functions would have been able to return only 4,000 bytes, but now they can return up to 32 K for a VARCHAR2 return type.

Under the covers, Oracle Database 12c is using a large object (LOB) to store these larger strings and raw types. If the inserted string is up to 4,000 bytes, the database will store the data in the table database block just as it does with a legacy VARCHAR2 type; if the string exceeds 4,000 bytes, however, the database will transparently store it out of line in a LOB segment and index.

Top-N Queries and Pagination

Out of the many thousands of questions on Ask Tom (asktom.oracle.com), a couple of the most popular are, “How do I get rows N through M of a result set” (how to paginate through a result set) and “How do I get the first N records of a result set.” In fact, I’ve written more than one article inOracle Magazine over the years to address these questions (“On Top-n and Pagination Queries” and “On ROWNUM and Limiting Results”). These articles demonstrated how to accomplish these feats, but the methods demonstrated are cumbersome, nonintuitive, and not necessarily portable.

Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set or, alternatively, the first N records after skipping over a set of records, so you can easily paginate through a result set. The diagram in Figure 1 shows the syntax for the row limiting clause.

o53asktom-f1

Figure 1: Row limiting clause syntax

The row limiting clause is simply added to the end of any SQL SELECT statement to fetch a specific set of records—there is no need for multiple layers of inline views and WHERE clauses that have to be carefully positioned, as there was with ROWNUM and ROW_NUMBER().

For example, if I have a table T

SQL> create table t
  2  as
  3  select * from all_objects;
Table created.

SQL> create index t_idx 
on t(owner,object_name);
Index created.

and I want to retrieve the first five rows after sorting by OWNER and OBJECT_NAME, I only need to add a FETCH FIRST N ROWS to the SQL query, as shown in Listing 1.

Code Listing 1: Simple SELECT query with FETCH FIRST

SQL> select owner, object_name, object_id
  2    from t
  3   order by owner, object_name
  4   FETCH FIRST 5 ROWS ONLY;
…
——————————————————————————————————————————————————————————————————————————————
| Id |Operation                     | Name|Rows  |Bytes |Cost (%CPU)|Time    |
——————————————————————————————————————————————————————————————————————————————
|   0|SELECT STATEMENT              |     |    5 | 1450 |    7   (0)|00:00:01|
|*  1| VIEW                         |     |    5 | 1450 |    7   (0)|00:00:01|
|*  2|  WINDOW NOSORT STOPKEY       |     |    5 |  180 |    7   (0)|00:00:01|
|   3|   TABLE ACCESS BY INDEX ROWID|T    |87310 | 3069K|    7   (0)|00:00:01|
|   4|    INDEX FULL SCAN           |T_IDX|    5 |      |    3   (0)|00:00:01|
——————————————————————————————————————————————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————————————————————————————————————————————————————

1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)

As you can tell by the predicate information in Listing 1, the row limiting clause is using ROW_NUMBER() transparently under the covers, rewriting the query to use analytics. The row limiting clause, in short, is making it much easier to do something you would have done manually in the past.

To paginate through a result set—to get N rows at a time from a specific page in the result set—I add the OFFSET clause. In Listing 2, I skip the first five rows and get the next five rows from a result set.

Code Listing 2: Simple SELECT query with OFFSET FETCH

SQL> select owner, object_name, object_id
  2    from t
  3   order by owner, object_name
  4  OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
…
—————————————————————————————————————————————————————————————————————————————
| Id |Operation                     |Name |Rows |Bytes |Cost (%CPU)|Time    |
—————————————————————————————————————————————————————————————————————————————
|   0|SELECT STATEMENT              |     |    5| 1450 |    7   (0)|00:00:01|
|*  1| VIEW                         |     |    5| 1450 |    7   (0)|00:00:01|
|*  2|  WINDOW NOSORT STOPKEY       |     |    5|  180 |    7   (0)|00:00:01|
|   3|   TABLE ACCESS BY INDEX ROWID|T    |87310| 3069K|    7   (0)|00:00:01|
|   4|    INDEX FULL SCAN           |T_IDX|    5|      |    3   (0)|00:00:01|
—————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————

1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
       THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=CASE  WHEN
       (5>=0) THEN 5 ELSE 0 END +5)

As you can see in Listing 2, the database, under the covers, is rewriting the query to use inline views and analytics once again—automating something that was previously nonintuitive and complex.

Note that in real life, you would use bind variables instead of hard-coded literals, so instead of using the number 5 as I did, you would have bound in the number 5.