Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Apache Impala Internals Deep Dive with Tanel Poder + Gluent New World Training Month

We are running a “Gluent New World training month” in this July and have scheduled 3 webinars on following Wednesdays for this!

The first webinar with Michael Rainey is going to cover modern alternatives to the traditional old-school “ETL on a RDBMS” approach for data integration and sharing. Then on the next Wednesday I will demonstrate some Apache Impala SQL engine’s internals, with commentary from an Oracle database geek’s angle (I plan to get pretty deep & technical). And in the end of the month, a Gluent customer Vistra Energy will talk about their journey towards a modern analytics platforms.

All together this should give a good overview of architectural opportunities that modern enterprise data platforms provide, with some technical Apache Impala hacking thrill too!

Offload, Transform & Present – The New World of Data Integration

Apache Impala Internals with Tanel Poder

  • Speaker: Tanel Poder, Gluent
  • Wednesday, July 19 @ 12 PM CDT

Building an Analytics Platform with Oracle & Hadoop

  • Speakers: Gerry Moore & Suresh Irukulapati, Vistra Energy
  • Wednesday, July 26 @ 9 AM CDT

You can see the abstracts and register for the webinars here.

We plan to run more technical sessions about different modern platform components and more customer case studies in the future too. See you soon!

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

12.2 New Feature: the FLEX ASM disk group part 2

In the first part of this series I explained the basics and some potential motivation behind the use of ASM Flex disk groups. In this part I would like to complete the description of new concepts.

New Concepts related to FLEX ASM Disk Groups

With the Flex disk group mounted, the next steps are to create a few new entities. First, I want to create a Quota Group. The Quota Group – as the name implies – will enforce quotas for entities residing within it. It is optional to add one yourself, Oracle creates a default Quota Group for you that does not enforce storage limits. As you will see later, the default Quota Group will be assigned to all new databases in the Flex ASM disk group.

The entity to be stored inside the Quota Group is named a File Group, and serves to logically group files such as those belonging to a database. According to the SQL Language Reference version 12.2, the File Group can be created for a

  • Database (non-CDB, CDB, and PDB)
  • Cluster or
  • ASM Volume

Remember that the compatible.rdbms and compatible.asm parameters are to be set to 12.2.0.1(+) on the Flex ASM disk group, ruling out pre 12.2 databases. For this post I am intending to store database-related files in the File Group. And since I like CDBs, I’m using this database type.

Database Creation

With the FLEX disk group in place I fired up dbca in silent mode to create a database for me. Before submitting the command however I created a Quota Group, connected to the ASM instance, like this:

SQL> alter diskgroup flex add quotagroup QG_CDB set quota = 20g;

Diskgroup altered.

SQL> select QUOTAGROUP_NUMBER,NAME,USED_QUOTA_MB,QUOTA_LIMIT_MB from v$asm_quotagroup;

QUOTAGROUP_NUMBER NAME                           USED_QUOTA_MB QUOTA_LIMIT_MB
----------------- ------------------------------ ------------- --------------
                1 GENERIC                                    0              0
                3 QG_CDB                                     0          20480
SQL> 

Oracle provides a GENERIC Quota Group without storage limitations when the Flex ASM disk group is created. QG_CDB is the Quota Group I just created. In hindsight I don’t think that creating the Quota Group at this stage was necessary because it wasn’t used straight away… but I’m getting ahead of myself.

Here is the command to create the two-node RAC database on my FLEX diskgroup, which I ran next:

[oracle@rac122pri1 ~]$ dbca -silent -createDatabase -templateName martin_cdb12cr2_001.dbc \
> -gdbName CDB -sysPassword secretpwd1 -systemPassword secretpwd2 -storageType ASM \
> -diskGroupName FLEX -recoveryGroupName FLEX -sampleSchema true \
> -totalMemory 2048 -dbsnmpPassword secretpwd3 -nodeinfo rac122pri1,rac122pri2 \
> -createAsContainerDatabase true -databaseConfigType RAC

I should probably create a disk group +FLEXRECO for my Fast Recovery Area, but since this is a lab system I don’t quite care enough to justify the extra space usage. Your mileage will most definitely vary, this isn’t supposed to be a blue print, instead it’s just me dabbling around with new technology :)

It takes a little time for the dbca command to return. It appears as if the database had automatically created File Groups for the CDB’s components, mapping to the GENERIC Quota Group (quotagroup_number 1 from the listing above). Querying the ASM instance, I can find these:

SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME, USED_QUOTA_MB, QUOTAGROUP_NUMBER from v$asm_filegroup
  2  /

FILEGROUP_NUMBER NAME                 CLIENT_NAME          USED_QUOTA_MB QUOTAGROUP_NUMBER
---------------- -------------------- -------------------- ------------- -----------------
               0 DEFAULT_FILEGROUP                                     0                 1
               1 CDB_CDB$ROOT         CDB_CDB$ROOT                  6704                 1
               2 CDB_PDB$SEED         CDB_PDB$SEED                  1656                 1

Just like with Quota Groups, there is a default entity, named DEFAULT_FILEGROUP. The CDB_CDB$ROOT and CDB_PDB$SEED seem to map to the newly created database. As you can see a little later in this post, the first “CDB” in CDB_CDB$ROOT maps to the database name. CDB$ROOT and PDB$SEED should sound familiar if you have previously worked with Container Databases.

Oracle creating File Groups for databases on its own is not too bad, because it takes half the work away from me. To test whether this holds true for newly created PDBs, I added a PDB named PDB1 to my CDB. Sure enough, after the create pluggable database command returned, there was a new File Group:

SQL> select FILEGROUP_NUMBER, NAME, CLIENT_NAME from v$asm_filegroup;

FILEGROUP_NUMBER NAME                 CLIENT_NAME
---------------- -------------------- --------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         CDB_CDB$ROOT
               2 CDB_PDB$SEED         CDB_PDB$SEED
               3 PDB1                 PDB1

The output made me think – somehow name and client_name no longer look that useful for establishing a relation between CDB and PDB1. This might actually not be necessary from a technical point of view, but somehow I need to understand which PDB1 is meant in the view in case there is more than 1. I can think of a PDB1 in CDB as super important, whereas PDB1 in some other CDB is less important. One way would be to name the PDB differently to allow a human to map the PDB name to a CDB. Having said that I certainly don’t want to have references to the CDB in my PDB name, that defeats the purpose of pluggable databases and wouldn’t hold true anyway when I unplug/plug the PDB to a different CDB.

Interlude
If you wonder what happens when you create another PDB1 in a different CDB, I did so, too. After a quick change to my dbca command and after the creation of another CDB I named ORCL, I create a new PDB1 within it. This is the result:
SQL> select filegroup_number, name, client_name, guid from v$asm_filegroup;

FILEGROUP_NUMBER NAME                 CLIENT_NAME          GUID
---------------- -------------------- -------------------- --------------------------------
               0 DEFAULT_FILEGROUP
               1 CDB_CDB$ROOT         CDB_CDB$ROOT         4700A987085A3DFAE05387E5E50A8C7B
               2 CDB_PDB$SEED         CDB_PDB$SEED         536DF51E8E28221BE0534764A8C0FD81
               3 PDB1                 PDB1                 537B677EF8DA0F1AE0534764A8C05729
               4 ORCL_CDB$ROOT        ORCL_CDB$ROOT        4700A987085A3DFAE05387E5E50A8C7B
               5 ORCL_PDB$SEED        ORCL_PDB$SEED        537E63B952183748E0534764A8C09A7F
               6 PDB1_0001            PDB1                 537EB5B87E62586EE0534764A8C05530

7 rows selected.

The good news is there is no clash, or even a failure of the “create pluggable database” command. The new database’s CDB$ROOT and PDB$SEED namespaces don’t collide because of the database-name prefix. CDB.PDB1 and ORCL.PDB1 don’t clash because Oracle appends a number to the File Group’s name.

The not-so-good news is that the (File Group) name and client_name became ambiguous. But there is a solution: playing around a bit I found that the GUID column in v$asm_filegroup maps to the GUID in v$pdbs/v$container.

SQL> select sys_context('USERENV','CDB_NAME') cdb_name, guid 
  2  from v$pdbs where guid = '537EB5B87E62586EE0534764A8C05530';

CDB_NAME                       GUID
------------------------------ --------------------------------
ORCL                           537EB5B87E62586EE0534764A8C05530

Looks like it’s time to understand those GUIDs better, hopefully there will be more to come soon.

Quotas

As you can see from the previous example, is not necessary to enforce a quota, however it is possible. I’ll do this for the sake of completeness (and in preparation for part 3).

NOTE: This section of the blog post was actually written before I had created the second CDB (“ORCL”), which is why you don’t see it in the command output.

Many of the administrative commands related to File Group and Quota Groups that I entered in SQL can also be issued via asmcmd, as shown here:

ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
5          1               GENERIC          1            10016          0               
5          3               QG_CDB           1            0              20480           

ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name   Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      0                                         
CDB_CDB$ROOT       FLEX        GENERIC      6704           CDB_CDB$ROOT  DATABASE     
CDB_PDB$SEED       FLEX        GENERIC      1656           CDB_PDB$SEED  DATABASE     
PDB1               FLEX        GENERIC      1656           PDB1          DATABASE     

ASMCMD> help mvfg
mvfg
        Moves a file group in a disk group to the specified Quota Group.

Synopsis
        mvfg -G  --filegroup  

Description
        The options for the mvfg command are described below.

        -G diskgroup     - Disk group name.
        --filegroup      - File group name.

Examples
        The following is an example of the mvfg command. The file group
        FG1 in the DATA disk group is moved to the Quota Group QG1.

        ASMCMD [+] > mvfg -G DATA --filegroup FG1 QG1

See Also
       mkqg rmqg chqg lsqg

ASMCMD> 

The first two should be self-explanatory: lsqg is short for list Quota Group, and lsfg is the equivalent for File Groups. the mvfg takes a couple of parameters but should be straight forward. With the commands introduced it’s time to perform the action. I need to move File Groups using mvfg:

ASMCMD> mvfg -G flex --filegroup CDB_CDB$ROOT QG_CDB
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup CDB_PDB$SEED QG_CDB
Diskgroup altered.
ASMCMD> mvfg -G flex --filegroup PDB1 QG_CDB
Diskgroup altered.
ASMCMD> lsfg
File Group         Disk Group  Quota Group  Used Quota MB  Client Name   Client Type  
DEFAULT_FILEGROUP  FLEX        GENERIC      0                                         
CDB_CDB$ROOT       FLEX        QG_CDB       6704           CDB_CDB$ROOT  DATABASE     
CDB_PDB$SEED       FLEX        QG_CDB       1656           CDB_PDB$SEED  DATABASE     
PDB1               FLEX        QG_CDB       1656           PDB1          DATABASE     

ASMCMD> lsqg
Group_Num  Quotagroup_Num  Quotagroup_Name  Incarnation  Used_Quota_MB  Quota_Limit_MB  
5          1               GENERIC          1            0              0               
5          3               QG_CDB           1            10016          20480           
ASMCMD> 

The command completes pretty much instantaneously, so it’s not actually “moving” data, it appears that all there is done is an update on meta-data. Moving the File Group is translated to the following SQL commands, found in the ASM instance’s alert.log:

2017-07-04 11:01:53.492000 +01:00
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_CDB$ROOT TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_CDB$ROOT TO QG_CDB
2017-07-04 11:02:08.645000 +01:00
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_PDB$SEED TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP CDB_PDB$SEED TO QG_CDB
SQL> /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP PDB1 TO QG_CDB
SUCCESS: /* ASMCMD */ALTER DISKGROUP FLEX MOVE FILEGROUP PDB1 TO QG_CDB

Summary Part 2

In part 2 of the article series I played around with Quota Groups and File Groups. Getting to grips with these concepts is necessary for a better understanding of how a Flex ASM disk group works. In part 3 I’ll try and investigate the effect of changing properties to file groups, and whether quotas are enforced.

ODTUG’s KSCOPE 2nd Annual Geekathon

After returning from KSCOPE two weeks ago, I was again approached to be a judge this year on the Geekathon 2017.

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/07/Screen-Shot-2... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/07/Screen-Shot-2... 768w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/07/Screen-Shot-2... 1382w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/07/Screen-Shot-2... 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

I was thrilled when the option was left open to me to compete this year. instead of be a judge.  As much as I love to offer my insight and expertise in a judging capacity, I really do love a great maker opportunity, which I haven’t had much time to allocate to with all the work I’m doing at Delphix.

Needless to say, I’m looking forward to this year’s competition, so look out maker’s, there’s a new…err, old gun….old set of Mickey ears in town!  (For those of you who aren’t getting that, next year’s KSCOPE 2018 conference is in Orlando, Fl…. :))

I’ve been part of the maker’s space for a number of years, finding it one of the most diverse and supportive groups in the nation.  I’ve presented at local events and been advertised in the NY Times to represent makers for the Barnes and Noble Book store events.

I’ve officially built out my proposal and submitted it as a team of one, but if you’re new to the makers space, unsure of what to do with a beacon or would just like to have a team to collaborate with, that’s an option!  Just find your maker-mates, come up with an awesome idea and if you need a beacon, just ask-  the ODTUGr’s will be glad to send you one so you can compete!  Just go to the ODTUG Geekathon site and submit a team and an idea!



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [ODTUG's KSCOPE 2nd Annual Geekathon], All Right Reserved. 2017.

The post ODTUG’s KSCOPE 2nd Annual Geekathon appeared first on DBA Kevlar.

Enough with AFIEDT.BUF

You are in SQL*Plus. You entered a command and urgh, there was a typo. No worries, you bring up the command in an editor by typing:

SQL> ed

This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.

History of Afiedt.Buf

First a little bit of background information on the odd name. SQL*Plus as a tool evolved from another tool Oracle provided a long, long time ago, called--rather uncreatively--User Friendly Interface, or UFI. When the editor wanted to bring up a file for editing, the file had to be given a name unique enough so as not to conflict with anything else. Therefore the file was named ufiedt.buf, which roughly indicated UFI Editor Buffer.

Later as UFI was added new features, it was called Advanced UFI (well, "advanced" is relative). To keep pace, the generated file was called Advanced UFI Editor Buffer, or aufiedt.buf. However an operating system that Oracle supported couldn't handle a 7 character file name before the period before suffix. Therefore the name was shortened to afiedt.buf to keep it distinct from ufiedt.buf (which presumably was not as "advanced"). As the advancements in technology came, Oracle didn't bother to change it and the name stuck. Long live afiedt.buf!

Why Change It?

If ain't broken, don't fix it. Shouldn't that be the case? The name doesn't break anything; so why change it? There are three reasons to at least consider.

  1. The name may difficult to type.
  2. he extension of ".buf" is not automatically recognized by SQL*Plus as a SQL script.
  3. The file is created in the present directory. Suppose you are in a directory you don't have permission on, e.g. "/etc" in Unix or \Windows in Windows, you can't just bring up the editor as you won't have the permissions to create the file there. You will get this error:
SP2-0110: Cannot create save file "afiedt.buf"
  • What do you do? Exit from SQL*Plus, change to a proper directory and re-execute this command? Then you would lose all the settings you may have done in that session. So you would want to create the file in a location of your choosing.
So, while you mean no disrespect to Oracle's legacy, you would want to relegate afiedt.buf to where it belongs--to the footnotes of history.

How can I Change It?

It's ridiculously simple to change the file. A setting in SQL*Plus controls it.

SQL> set editfile arup.sql

After this when you type the editor command:

SQL> ed
Wrote file arup.sql

Look how the file created was named "arup.sql"; not afiedt.buf. Of course you can use any name as you find appropriate.

Changing Directory

Let's handle the second problem. You are in a directory where you don't have write permission and you want to edit the command. Well, you can use a full path to the file as well.

SQL> set editfile c:\temp\arup.sql
SQL> ed
Wrote file c:\temp\arup.sql

The file is created in the C:\temp directory instead of the default option of the current directory.

Changing the Editor

While on the subject, do you know that you can change the default editor as well? For instance, if you are on Windows, you don't have to have Notepad as the editor for SQL scripts. It can be notepad++, or, as I prefer, "vim", which is enhanced vi. In Unix, the default is not vi; it's "ed" and you may want to change it. How to do that?

Very simple; another setting _EDITOR allows us to do that. It's a variable; so you have to use DEFINE command. Use the following:

SQL> define _editor = vi

That's it; now when you enter "ed" command, it will bring up vi editor. Change to whatever you want to set.

Tips for Practical Uses

If you want to change the default editor and the default file for editing, put the commands in the glogin.sql file, which ensures their automatic execution when someone invokes SQL*Plus.

Afiedt.Buf is dead. Long live Afedit.Buf. Or, was it Afiedt.Buf? Oh, who cares?

12.2 Introduction to Real-Time Materialized Views (The View)

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which […]

Postgresql block internals, part 3

This is the third part in a series of blogposts about how postgresql manages data in its blocks (called ‘pages’ in postgres speak). If you found this post and did not read the previous ones, it might be beneficial to read block internals (part 1) and block internals, part 2 first. In these blogposts I’ve shown how heap and index pages look like, and how these can be investigated, including looking at the raw block information.

This blogpost is intended to show the effects on pages when DML happens. This is inherently different from my personal reference of database implementation, which is the oracle database.

If you followed the previous two articles, you have a table mytable with one thousand rows. In order to simplify and be able to grasp the essence of the implications of data changes, drop mytable and recreate the simple situation of mytable with four rows again:

test=# drop table if exists mytable;
DROP TABLE
test=# create table mytable ( id int not null, f1 varchar(30) );
CREATE TABLE
test=# alter table mytable add constraint pk_mytable primary key ( id );
ALTER TABLE
test=# insert into mytable ( id, f1 ) values (1, 'aaaaaaaaaa'), (2, 'bbbbbbbbbb'), (3, 'cccccccccc'), (4, 'dddddddddd');
INSERT 0 4

This should result in the table having four rows in the first page:

test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |   8152 |        1 |     39 |   1790 |      0 |        0 | (0,1)  |           2 |       2050 |     24 |        |       | \x010000001761616161616161616161
  2 |   8112 |        1 |     39 |   1790 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x020000001762626262626262626262
  3 |   8072 |        1 |     39 |   1790 |      0 |        0 | (0,3)  |           2 |       2050 |     24 |        |       | \x030000001763636363636363636363
  4 |   8032 |        1 |     39 |   1790 |      0 |        0 | (0,4)  |           2 |       2050 |     24 |        |       | \x040000001764646464646464646464

And the second index page having the index tuples of these four rows:

test=# select * from bt_page_items('pk_mytable',1);
 itemoffset | ctid  | itemlen | nulls | vars |          data
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          4 | (0,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00

The next thing to do, is change the configuration of postgres to disable auto-vacuum. This should absolutely not be done on any postgres database serving something useful, but in order to see the effects of data changes, and the changes as a result of a vacuum, vacuum should not kick in automatically, because then we can’t predict what we will be looking at. This is governed by the parameter ‘autovacuum’, which is turned on by default:

test=# show autovacuum;
 autovacuum
------------
 on

Turn autovacuum off and reload the configuration:

test=# alter system set autovacuum=off;
ALTER SYSTEM
test=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
test=# show autovacuum;
 autovacuum
------------
 off

Now let’s update id 1 and set f1 to ‘zzzzzzzzzz’:

test=# update mytable set f1 = 'zzzzzzzzzz' where id = 1;
UPDATE 1

Now let’s look at the page contents of the table mytable:

test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |   8152 |        1 |     39 |   1790 |   1791 |        0 | (0,5)  |       16386 |        258 |     24 |        |       | \x010000001761616161616161616161
  2 |   8112 |        1 |     39 |   1790 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x020000001762626262626262626262
  3 |   8072 |        1 |     39 |   1790 |      0 |        0 | (0,3)  |           2 |       2050 |     24 |        |       | \x030000001763636363636363636363
  4 |   8032 |        1 |     39 |   1790 |      0 |        0 | (0,4)  |           2 |       2050 |     24 |        |       | \x040000001764646464646464646464
  5 |   7992 |        1 |     39 |   1791 |      0 |        0 | (0,5)  |       32770 |      10242 |     24 |        |       | \x01000000177a7a7a7a7a7a7a7a7a7a

This is a good example how read consistency and row versioning works in postgres. The update inserted a new tuple at offset 7992 (lp_off) in the page, and added a pointer to it in the line pointer array (lp 5). Mind the ‘the update inserted a new tuple’ which is fundamental! In the metadata of tuple ID 1, xmax is set to indicate the maximum transaction ID which should be able to see the this version of the tuple. The postgres documentation says ‘delete XID stamp’ for xmax. Tuple ID 5 is created with exactly the same XID for xmin as tuple 1 has for xmax, which is the transaction ID starting from which this tuple should be visible. Obviously, for this tuple xmax is set to 0, indicating no newer version exists. Also, the t_ctid (current tuple ID) for tuple 1 is set to 0,5 (block 0, tuple 5), indicating the next version of that tuple is tuple ID 5. To indicate what happened, infomask2 and infomask have been set.
infomask2 16386 means: heap has two attributes, heap HOT updated.
infomask 258 means: has variable attributes, xmin committed.

The new tuple has the following values for infomask2 and infomask:
infomask2 32770 means: heap has two attributes, is heap only tuple.
infomask 10242 means: has variable attributes, xmax invalid, this is an updated version of the row.
(see the blogpost postgresql block internals, part 2 for a description of the infomask2 and infomask bitfields)

It should be clear now the update caused the active version of the tuple with id = 1 to be tuple ID 5 instead tuple ID 1. Please mind tuple ID 1 is still entirely present (in the line pointer array and in the data part of the page).

How does this looks like in the index?

test=# select * from bt_page_items('pk_mytable',1);
 itemoffset | ctid  | itemlen | nulls | vars |          data
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          4 | (0,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00

Nothing changed!

Actually, this is exactly what ‘HOT’ (heap only tuple) means to describe in the above descriptions (infomask2, heap HOT updated). A new version of a tuple is created, but it is not covered by any index. This means when table/heap tuple with id=1 is accessed using the index, the index points to the original tuple ID (1) in the heap/table, which points to the new version of the tuple (tuple ID 5). This is an optimisation to lessen the amount of work done when updating a field that is not indexed, at the cost of following an additional pointer.

What happens if the tuple with id = 1 gets updated again?

test=# update mytable set f1 = 'yyyyyyyyyy' where id = 1;
UPDATE 1
test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |   8152 |        1 |     39 |   1790 |   1791 |        0 | (0,5)  |       16386 |       1282 |     24 |        |       | \x010000001761616161616161616161
  2 |   8112 |        1 |     39 |   1790 |      0 |        0 | (0,2)  |           2 |       2050 |     24 |        |       | \x020000001762626262626262626262
  3 |   8072 |        1 |     39 |   1790 |      0 |        0 | (0,3)  |           2 |       2050 |     24 |        |       | \x030000001763636363636363636363
  4 |   8032 |        1 |     39 |   1790 |      0 |        0 | (0,4)  |           2 |       2050 |     24 |        |       | \x040000001764646464646464646464
  5 |   7992 |        1 |     39 |   1791 |   1792 |        0 | (0,6)  |       49154 |       8450 |     24 |        |       | \x01000000177a7a7a7a7a7a7a7a7a7a
  6 |   7952 |        1 |     39 |   1792 |      0 |        0 | (0,6)  |       32770 |      10242 |     24 |        |       | \x010000001779797979797979797979

infomask 2 for tuple ID 5 now says: heap has two attributes, is heap only tuple and additionally: heap was HOT updated.
infomask for tuple ID 5 now says: heap has variable attributes, xmin committed and additionally: this is an updated version of the tuple.

Another update essentially performs exactly the same transaction on tuple ID 5 as previously done on tuple ID 1: a new tuple is created containing the result of the update (linepointer array entry and data at the end of the page), xmax is set for tuple ID 5 and xmin is set for tuple ID 6 with the same transaction ID, and the current tuple id for tuple ID 5 is set to the new version for that tuple, which is tuple ID 6.

As can be seen by looking at the value for infomask2, tuple ID 6 now has the same value as tuple ID 5 had previously. Just like the tuple ID 5 previously, the new tuple with ID 6 is a ‘hot’ updated tuple too, which means the index was once again left untouched, so the index still points to heap tuple ID 1. This means that a session that uses the index to find the tuple, will be pointed to heap tuple ID 1, which points to tuple ID 5, which points to tuple ID 6 in this case.

It’s obvious that this tuple pointer chasing can easily lead to more work to be done to find a row/tuple, meaning lesser performance. It also is obvious that this construction exists to provide a way to produce an older version of a tuple for read consistency. However, this old version is not useful anymore after some time, which is when no query exists with a transaction ID lower than the value for xmax for the old tuples, and needs to be purged. This is where vacuum comes in. Let’s vacuum mytable, and read the heap/table page again:

test=# vacuum mytable;
VACUUM
test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |      6 |        2 |      0 |        |        |          |        |             |            |        |        |       |
  2 |   8152 |        1 |     39 |   1790 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000001762626262626262626262
  3 |   8112 |        1 |     39 |   1790 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000001763636363636363636363
  4 |   8072 |        1 |     39 |   1790 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x040000001764646464646464646464
  5 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |
  6 |   8032 |        1 |     39 |   1792 |      0 |        0 | (0,6)  |       32770 |      10498 |     24 |        |       | \x010000001779797979797979797979

There are a few things vacuum did, and some things it didn’t:
– The line pointer array entries all stayed.
– The first entry points to heap tuple ID 6. lp_flags=2 together with lp_len=0 means the lp_off field points to the active tuple ID. The reason the first row still exists, is because the index entry still points to it.
– The fifth line pointer entry is all zero, lp_flags=0 means ‘unused’.

If you look closely at the line pointer array entries and the tuple data offsets before and after vacuum, you see that the past versions of the tuple data do not exist anymore. In fact, if you look closely to the offset (lp_off) values of the tuples that are stored in the page, you see that ID 2 took the offset value from ID 1 from before vacuuming, indicating it moved all the way to the back of the page. In fact, vacuum compacted the data tuples, maximising free space in the page.

A logical next question is: okay, this is way better than how it was left for read consistency after the two transactions, but it is still not optimal. Can it be fully cleaned? The answer is yes, vacuum can be told to fully clean up, including the removing any non-functional line pointer array entries, and as a logical consequence have all index entries point directly to the heap tuple it ought to point to. This is called ‘vacuum full’.

Why not use vacuum full all the time? The reason is vacuum full requires a table lock for the entire duration of running vacuum. This is not desirable and acceptable in a lot of databases. If normal vacuum is run sufficiently, running vacuum full will not yield much additional benefit in most cases, because when normal vacuum is run sufficiently, the old row versions are cleaned up in time, preventing excessive amounts of old versions in a page. This is important because normal vacuum cleans up within a page, but it does not move tuples across pages, freeing up pages and thus operating system disk space, which is what vacuum full does. This is why autovacuum should be turned on, to clean up old versions in time.

When an update performs an update on a field that is covered by the index, obviously the update can not be ‘HOT’, because the index must cover the fields it is created on. Let’s update the id of id=1 to 5:

test=# update mytable set id=5 where id=1;
UPDATE 1
test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |      6 |        2 |      0 |        |        |          |        |             |            |        |        |       |
  2 |   8152 |        1 |     39 |   1814 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000001762626262626262626262
  3 |   8112 |        1 |     39 |   1814 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000001763636363636363636363
  4 |   8072 |        1 |     39 |   1814 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x040000001764646464646464646464
  5 |   7992 |        1 |     39 |   1817 |      0 |        0 | (0,5)  |           2 |      10242 |     24 |        |       | \x050000001779797979797979797979
  6 |   8032 |        1 |     39 |   1816 |   1817 |        0 | (0,5)  |       40962 |       8450 |     24 |        |       | \x010000001779797979797979797979

As you can see, tuple ID/ line pointer number 5, which was emptied by vacuum, now is reused. The chain of tuple ID’s now is: ID 1 points to ID 6. ID 6 has xmax set, and t_ctid points to ID 5, with xmin set to the previous version’s xmax value. The value of 2 for t_infomask2 for ID 5 shows no bits indicating a HOT update. How does this look like in the index?

test=# select * from bt_page_items('pk_mytable',1);
 itemoffset | ctid  | itemlen | nulls | vars |          data
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          4 | (0,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00
          5 | (0,5) |      16 | f     | f    | 05 00 00 00 00 00 00 00

Because this is not a HOT update, there is a change to the index, an index tuple is added for the new value: ‘5’, which points to heap page 0 tuple ID 5, which is the result of the update. It is interesting to see value ‘1’ still exists in the index, while value ‘5’ is the actual value and ‘1’ the old value of the same heap tuple. It is logical however if you realise there is no versioning data in the index, so in order to understand if a tuple should be visible requires the heap/table tuple to be read. Once again, if you vacuum the table, things get sorted:

test=# vacuum mytable;
VACUUM
test=# select * from heap_page_items(get_raw_page('mytable',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |
  2 |   8152 |        1 |     39 |   1814 |      0 |        0 | (0,2)  |           2 |       2306 |     24 |        |       | \x020000001762626262626262626262
  3 |   8112 |        1 |     39 |   1814 |      0 |        0 | (0,3)  |           2 |       2306 |     24 |        |       | \x030000001763636363636363636363
  4 |   8072 |        1 |     39 |   1814 |      0 |        0 | (0,4)  |           2 |       2306 |     24 |        |       | \x040000001764646464646464646464
  5 |   8032 |        1 |     39 |   1817 |      0 |        0 | (0,5)  |           2 |      10498 |     24 |        |       | \x050000001779797979797979797979
  6 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |

Tuple ID 1 now is empty, because it is relieved from its function of pointing to tuple ID 6, because tuple ID 6 gotten a new version, which means tuple ID 6 could be and is freed by vacuum. If you look in the index, you will see vacuum now freed up the tuple of the updated value:

test=# select * from bt_page_items('pk_mytable',1);
 itemoffset | ctid  | itemlen | nulls | vars |          data
------------+-------+---------+-------+------+-------------------------
          1 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          2 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
          3 | (0,4) |      16 | f     | f    | 04 00 00 00 00 00 00 00
          4 | (0,5) |      16 | f     | f    | 05 00 00 00 00 00 00 00

The bt_page_items function does not reveal too much information about line pointer and data offsets, in other words, what vacuum did, let’s look at the raw block contents:

$ psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A d -t x1
0000000 00 00 00 00 78 60 b5 09 00 00 00 00 28 00 b0 1f
0000016 f0 1f 04 20 00 00 00 00 e0 9f 20 00 d0 9f 20 00
0000032 c0 9f 20 00 b0 9f 20 00 a0 9f 20 00 00 00 00 00
0000048 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
*
0008096 00 00 00 00 05 00 10 00 05 00 00 00 00 00 00 00
*
0008128 00 00 00 00 04 00 10 00 04 00 00 00 00 00 00 00
0008144 00 00 00 00 03 00 10 00 03 00 00 00 00 00 00 00
0008160 00 00 00 00 02 00 10 00 02 00 00 00 00 00 00 00
0008176 00 00 00 00 00 00 00 00 00 00 00 00 03 00 00 00
0008192

The interesting bit is the second star indicating a duplicate row, which is the index tuple pointing to the value ‘5’. In order to understand what this means, let’s read the line pointer array for the index tuples. Here’s a little script to do that:

for TUPLE_ID in 1 2 3 4 5 6; do
 LP_OFFSET=$( echo "24+(4 * ($TUPLE_ID - 1) )" | bc )
 DATAPTR=$( psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A n -t x2 -j $LP_OFFSET -N 2 | awk '{ print $1 }' )
 LENGTH_OFFSET=$( echo "$LP_OFFSET+2" | bc )
 LENGTH=$( psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A n -t x2 -j $LENGTH_OFFSET -N 2 | awk '{ print $1 }' )
 TRUE_DATAPTR=$( echo $(( 0x$DATAPTR & ~$((1<<15)))) )
 TRUE_LENGTH=$( echo $((0x$LENGTH >> 1)) )
 LP_BITS_2=$( printf "%016d" $( echo "ibase=16;obase=2;${DATAPTR^^}" | bc ) )
 LP_BITS_1=$( printf "%016d" $( echo "ibase=16;obase=2;${LENGTH^^}" | bc ) )
 echo "TID: $TUPLE_ID data offset: $TRUE_DATAPTR length: $TRUE_LENGTH binary: $LP_BITS_1 $LP_BITS_2"
done

And this is the output:

TID: 1 data offset: 8160 length: 16 binary: 0000000000100000 1001111111100000
TID: 2 data offset: 8144 length: 16 binary: 0000000000100000 1001111111010000
TID: 3 data offset: 8128 length: 16 binary: 0000000000100000 1001111111000000
TID: 4 data offset: 8112 length: 16 binary: 0000000000100000 1001111110110000
TID: 5 data offset: 8096 length: 16 binary: 0000000000100000 1001111110100000
TID: 6 data offset: 0 length: 0 binary: 0000000000000000 0000000000000000

That’s weird! It looks like there is an additional line pointer entry with tuple that points to the value ‘5’, which are at offset 8112 (which is the row that second asterisk was put in for), and offset 8096. If you look at the line pointer status bits (the last bit of the first bitfield ‘0000000000100000’) and the first bit of the next field, it’s all ’01’ which means LP_NORMAL (normal, live line pointer entry).

After some reading up in the source code, it turns out the upper value in the page header is used to determine which line pointer array entries are valid. So the above code should be:

PAGE_HEADER_UPPER=$( psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A n -t d2 -j 14 -N 2 | awk '{ print $1 }' )
for TUPLE_ID in $( seq 1 1000 ); do
 LP_OFFSET=$( echo "24+(4 * ($TUPLE_ID - 1) )" | bc )
 DATAPTR=$( psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A n -t x2 -j $LP_OFFSET -N 2 | awk '{ print $1 }' )
 LENGTH_OFFSET=$( echo "$LP_OFFSET+2" | bc )
 LENGTH=$( psql -d test -tA -c "select encode(get_raw_page::bytea, 'hex') from get_raw_page('pk_mytable',1)" | xxd -p -r | od -A n -t x2 -j $LENGTH_OFFSET -N 2 | awk '{ print $1 }' )
 TRUE_DATAPTR=$( echo $(( 0x$DATAPTR & ~$((1<<15)))) )
 [ $PAGE_HEADER_UPPER -gt $TRUE_DATAPTR ] && break
 TRUE_LENGTH=$( echo $((0x$LENGTH >> 1)) )
 LP_BITS_2=$( printf "%016d" $( echo "ibase=16;obase=2;${DATAPTR^^}" | bc ) )
 LP_BITS_1=$( printf "%016d" $( echo "ibase=16;obase=2;${LENGTH^^}" | bc ) )
 echo "TID: $TUPLE_ID data offset: $TRUE_DATAPTR length: $TRUE_LENGTH binary: $LP_BITS_1 $LP_BITS_2"
done

Which will only show the actual entries based on the upper value in the page header. This shows:

TID: 1 data offset: 8160 length: 16 binary: 0000000000100000 1001111111100000
TID: 2 data offset: 8144 length: 16 binary: 0000000000100000 1001111111010000
TID: 3 data offset: 8128 length: 16 binary: 0000000000100000 1001111111000000
TID: 4 data offset: 8112 length: 16 binary: 0000000000100000 1001111110110000

If you look back to the raw page, it should be clear that vacuum did cleaning up in the index. The tuple for value ‘1’ was deleted, meaning that the first entry in the line pointer array now is the value ‘2’. Also the data for the index tuples at the end of the block has been cleaned up, the data for the new first tuple is copied over the old data, the tuple data moved to the end. As a result, there was a copy left of the data for the tuple with the value ‘5’ in the tuple data area and in the line pointer array, which is not used, because the ‘upper’ value in the page header will tell if a line pointer entry is active or not.

Another scenario is important to understand. What if you insert a lot of rows in a table, and then start updating rows? As we have seen, by default postgres fills up a table page to 100%. In other words: this is not an unlikely scenario. Let’s investigate how that works! In order to begin with a clean slate, drop mytable and create it again and insert a thousand rows. My second blogpost contains an anonymous PL/SQL block that inserts rows in a loop.

If we look at the first page of mytable, we see that it is entirely filled:

test=# select * from page_header(get_raw_page('mytable',0));
    lsn    | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
 0/9BC4120 |        0 |     0 |   764 |   792 |    8192 |     8192 |       4 |         0

There is upper-lower=28 bytes free actually, but the heap tuple length is:

test=# select lp_len from heap_page_items(get_raw_page('mytable',0)) where lp=1;
 lp_len
--------
     39

So the row length is 39 bytes. So indeed page 0 of mytable is entirely filled.

New let’s update f1 of the first row (id=1) to something. As we know, this will create a new version of the row, which can’t fit in the first block, where the row with id=1 is located:

test=# update mytable set f1='ZZZZZZZZZZ' where id=1;
UPDATE 1

Let’s look at the first row how postgres handled that:

test=# select * from heap_page_items(get_raw_page('mytable',0)) where lp=1;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
  1 |   8152 |        1 |     39 |   1835 |   1836 |        0 | (5,76) |           2 |        258 |     24 |        |       | \x010000001758585858585858585858

I think the update is entirely as expected, which means xmax has a transaction ID set indicating a new version exists. However t_ctid is set to 5,76, in other words: the new version is created in page 5 tuple ID 76, which means outside of the page where to original tuple version exist. If you look at the metadata of the new tuple:

test=# select * from heap_page_items(get_raw_page('mytable',5)) where lp=76;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
 76 |   5152 |        1 |     39 |   1836 |      0 |        0 | (5,76) |           2 |      10242 |     24 |        |       | \x01000000175a5a5a5a5a5a5a5a5a5a

You see a normal new version, and no bits are set in t_infomask2 indicating a heap only tuple (hot). This means this update did change the index:

test=# select * from bt_page_items('pk_mytable',1) where itemoffset <5;
 itemoffset |  ctid   | itemlen | nulls | vars |          data
------------+---------+---------+-------+------+-------------------------
          1 | (1,182) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (5,76)  |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,1)   |      16 | f     | f    | 01 00 00 00 00 00 00 00
          4 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00

It seems weird that two index entries exist for id=1 (item offset 2 and 3) in a primary key index. If you think a little longer, it makes perfect sense: there is no version consistency information in the index. Currently two versions of the heap tuple with id=1 exist in the heap/table so it’s logical the index points to them both. Let’s see what happens when the table is vacuumed:

test=# vacuum mytable;
VACUUM
test=# select * from heap_page_items(get_raw_page('mytable',0)) where lp=1;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------
  1 |      0 |        0 |      0 |        |        |          |        |             |            |        |        |       |
test=# select * from heap_page_items(get_raw_page('mytable',5)) where lp=76;
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |              t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------
 76 |   5152 |        1 |     39 |   1836 |      0 |        0 | (5,76) |           2 |      10498 |     24 |        |       | \x01000000175a5a5a5a5a5a5a5a5a5a
test=# select * from bt_page_items('pk_mytable',1) where itemoffset <5;
 itemoffset |  ctid   | itemlen | nulls | vars |          data
------------+---------+---------+-------+------+-------------------------
          1 | (1,182) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (5,76)  |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)   |      16 | f     | f    | 03 00 00 00 00 00 00 00

After vacuuming, the old version of the row is emptied, and the index entry for the old version is deleted.

There is an additional thing inherent to how postgres handles DML to any tuple (version), which needs to be taken into account. These is what is commonly called tuple ‘hint bits’. The hint bits are bits in t_infomask in the tuple header (let me emphasise: the header with every single tuple!). During DML on a tuple, the transaction obviously is ongoing, and as such the xmax bits for a deleted or old version of a tuple are reset, and the xmin bits of the new version or inserted tuple are reset. This is how a DML operation leaves the tuples it worked on.

The first process that visits a tuple after a DML operation reads t_xmin and t_xmax to understand which version of the tuple should be active. However, during reading the metadata, if a tuple does not have the transaction status set for t_xmax (HEAP_XMAX_COMMITTED or HEAP_XMAX_INVALID) or for t_xmin (HEAP_XMIN_COMMITTED, HEAP_XMIN_INVALID), it must investigate whether the transaction is still ongoing, rolled back or committed, and set the t_infomask bits accordingly.

In order to do this, the process needs to read pg_clog and potentially pg_subtrans. Reading pg_clog/pg_subtrans involves extra reading, and setting the bits means changes to the page, which need to be recorded in the WAL file. This means a select (read only) query can lead to writing to the WAL file! It should be noted that after the just described more expensive first read, which sets the hint bits for a tuple, the next readers can take advantage of that work and do not need to read up on the transaction state of a tuple again, unless a transaction performs DML again. Running vacuum on a table (whether by autovacuum or explicitly) will set the hint bits too.

Conclusion
This blogpost looks at updates to tuples. In postgres, an update means the previous version gets xmax set, and a new version of a tuple is created with xmin set to the xmax of the old version. If the update happened to a non-indexed field and the new tuple can be allocated in the same page, the new tuple can be ‘HOT’, alias a heap only tuple. This way no modification needs to be made to the index, which is an optimisation. However, when the changed tuple is requested via the index, it points to the tuple ID of the old version, which contains the tuple ID of the updated version of that tuple. Of course the updated version can also be updated and point to a newer version.

When a HOT updated table is vacuumed, the index entry of a HOT updated tuple remains untouched, and the tuple ID in the table to which the index points to will be a dedicated pointer to the current version (no matter how many HOT row versions existed prior to vacuuming), without space allocated in the data area. All allocated space of a line pointer entry that is cleaned by vacuum is compacted, meaning that all active data entries are defragmented and allocated from the bottom.

If an indexed entry is updated, the new version is created and an index entry is created for the update. Because the old version still exists for read consistency, the index entry is not deleted, it needs to be available for read consistency. If the table is vacuumed, the old version is emptied from the table, and the index entry is deleted.

Very much alike an indexed field update, if an update causes a new version to be created in another page than the original version of the tuple, the update is not HOT, and causes the index to be modified and the new version from the update is added.

Any DML to a row resets what is called the ‘hint bits’. Of course only the hint bits of either xmin or xmax which are relevant to the transaction are reset. The hint bits describe the state of xmin and xmax, and need to be set by the next process that touches the heap/page tuple. The setting of the hint bits require the process to read pg_clog and potentially pg_subtrans to find out the state of the transaction, and set the hint bits, which is a change to the page, which requires WAL logging.

Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.


select module
from v$session
where username = 'SYS'

MODULE
-----------------------------
sqlplus.exe
sqlplus.exe

In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:


SQL> exec dbms_application_info.set_module('MyModule','MyAction')

PL/SQL procedure successfully completed.

Now if you check the module:


MODULE
---------------
MyModule
sqlplus.exe

As you can see the session where executed the packaged procedure has the descriptive module name. If you want to enable tracing, check the sessions stats, debug what's going on, etc., the ability to identify the session uniquely and accurately could be lifesaver.

But we had to execute the package dbms_application_info. The principles of least privileges says that we don't want to grant more privileges than absolutely necessary. While no one will argue against this being convenient, it will probably be hard to justify as "absolutely" necessary. So, what could you do to identify the session via the module? The generic module name "sqlplus.exe" is pretty much useless.

There is a much simpler solution. The SQL*Plus parameter appinfo comes to rescue. By default the setting is off. You can confirm that by issuing the following:


SQL> show appinfo
appinfo is OFF and set to "SQL*Plus"

To set to a value you want, use the follwoing:


SQL> set appinfo MyApp

Now if you check the sessions from another sessions:


SQL> select module
2 from v$session
3 where username = 'SYS';

MODULE
-------------------------------
MyApp
sqlplus.exe

See how the module is set to MyApp, which allows you to locate the session from many from the same user. And you could do that without calling the package.

What are the practical implications? The best usecase, I think is using this in the automatic login scripts such as glogin.sql. Put the following line in gloin.sql in $ORACLE_HOME/sqlplus/admin directory.


set appinfo "AcmeModule"

Now any session using that Oracle Home will have the Module column set to AcmeModule.

However you can also create local files called login.sql in individual directories and set appinfo apprpriately. For instance, suppose you have three major directories where you generally run the SQL scripts from. Create login.sql files on each directory. Directory "C:\App1" has a login.sql file with the following content:


set appinfo "App1"

Create login.sql files in the other directories with the appropriate contents. Now when someone connects to the database from SQL*Plus from those directories, the module will be set appropriately.
You will be able to know what directory the user has been in calling the scripts.

A caveat on the above, though. In Oracle 12.2, login.sql file is not executed, if a glogin.sql file is present. It is silently ignored. To make sure the local login.sql file is read, you have to explcitly set the variable ORACLE_PATH or SQLPATH to that directory.

Hope you find use of this little known setting in SQL*Plus.

12.2 ACFS compression, part I

One of the new 12.2 features is the ability to transparently compress ACFS filesystems.

Compression is enabled using acfsutil utility:

[root@raca1 ~]# acfsutil -h compress on
Usage: acfsutil [-h] compress on [-a ]
- Set default compression algorithm
Currently only 'lzo' available
- Enable compression on volume

Clearly there is support for more compression algorithms to be added in the future but right now only lzo is supported.

Let's go ahead and enable compression on the ACFS filesystem I have:

[root@raca1 ~]# acfsutil compress on /u02/oradata

Compression status can be checked using acfsutil info fs:

[root@raca1 ~]# acfsutil info fs /u02/oradata
/u02/oradata
ACFS Version: 12.2.0.1.0
on-disk version: 46.0
compatible.advm: 12.2.0.0.0
ACFS compatibility: 12.2.0.0.0
flags: MountPoint,Available
mount time: Fri Jul 7 12:53:39 2017
mount sequence number: 0
allocation unit: 4096
metadata block size: 4096
volumes: 1
total size: 8589934592 ( 8.00 GB )
total free: 6935588864 ( 6.46 GB )
file entry table allocation: 393216
primary volume: /dev/asm/data-95
label:
state: Available
major, minor: 250, 48641
logical sector size: 4096
size: 8589934592 ( 8.00 GB )
free: 6935588864 ( 6.46 GB )
metadata read I/O count: 23833
metadata write I/O count: 39103
total metadata bytes read: 257896448 ( 245.95 MB )
total metadata bytes written: 421969920 ( 402.42 MB )
ADVM diskgroup: DATA
ADVM resize increment: 67108864
ADVM redundancy: unprotected
ADVM stripe columns: 8
ADVM stripe width: 1048576
number of snapshots: 0
snapshot space usage: 0 ( 0.00 )
replication status: DISABLED
compression status: ENABLED

As a quick test I've created a new empty database inside ACFS filesystem. We can now check compressed file sizes:

[root@raca1 ora12cr2]# acfsutil compress info /u02/oradata/ora12cr2/sysaux01.dbf
Compression Unit size: 8192
Disk storage used: ( 88.70 MB )
Disk storage saved: ( 461.31 MB )
Storage used is 16% of what the uncompressed file would use.
File is not scheduled for asynchronous compression.
[root@raca1 ora12cr2]# ls -l /u02/oradata/ora12cr2/sysaux01.dbf
-rw-r----- 1 oracle dba 576724992 Jul 7 13:45 /u02/oradata/ora12cr2/sysaux01.dbf

It is worth noting that not all file types are compressed. For example redo logs are left uncompressed:

[root@raca1 ora12cr2]# acfsutil compress info /u02/oradata/ora12cr2/redo01.log
The file /u02/oradata/ora12cr2/redo01.log is not compressed.

So far so good -- the feature indeed allows you to save some space by compressing the files. I will continue exploring this capability in the next part.

Oracle Security Audit and Open Ports on a Database Server

As part of a detailed security audit of an Oracle database performed by our company we look at most areas that are related to two things; the security of the Oracle platform itself, i.e. the Oracle database and its software....[Read More]

Posted by Pete On 07/07/17 At 04:31 PM

OFE

The title is a well-known shorthand for parameter optimizer_features_enable and it has been the topic of a recent blog post by Mike Dietrich in which he decries the practice of switching the parameter back to an older version on an upgrade (even though, as he points out, Oracle support has been known to recommend it and the manuals describe – though not with 100% accuracy – why you might do so).

I am one of the people who will suggest that on the upgrade a client should consider setting the optimizer_features_enable to the version just left behind as a strategy for getting to a newer version of the base code while minimising the threat of plan instability, so I’m going to play devil’s advocate in this case even though, as we shall see, I am nearly 100% in favour of Mike’s complaint.

The first point, of course, is full disclosure to the client.  Eventually they will have to set the parameter to the current database version, all they’re doing to trying to spread out the workload of addressing a perceived threat. Moreover, they are only minimising the threat, not eliminating it. Setting the parameter has the effect of changing the state of a long list of parameters and “fix controls” – but there’s no guarantee that it will reverse out all the code changes between the two versions. One hopes it won’t reverse out a bug-fix (though Mike quotes a MoS note where exactly that problem appears); more significantly it might not reverse out a clever code optimisation that (in a few unlucky cases) happens to make the SQL run more slowly even when a new transformation is not involved. What you’re hoping for when you set this parameter is that the number of places in your application where you get an unlucky change in performance is much smaller than it would be if you didn’t set the parameter.

The second point is that you really want to have the minimum impact possible while doing expending as little human effort as possible. To this end it’s better to think in terms of setting the parameter for specific users (via a logon trigger), or specific sessions (e.g. batch runs), or specific statements (through a hint or SQL Patch). It may take a couple of test runs to spot the critical classes of statements that point you at the right granularity of implementation, but the more of your SQL that runs at the newer optimizer level the better.

If you’re going to aim for minimum impact, though, and if you’ve got the time to do some broad-brush testing it’s worth going back to my comment that this parameter is a big switch for a number of parameters and fix controls. Perhaps you will be able to spot which new feature, or which fix control is the one thing that needs to be changed – in the short-term – for your system.  Again, statement level is preferable to session level, which is preferable to user level, which is preferable to system level.

The thought of adding a controlling parameter as hint to a statement will probably have some people thinking about creating SQL baselines rather than adding hints to code – and if it’s 3rd party code then an SQL Baseline may be the necessary strategy. Bear in mind that a common advisory for upgrades is “create SQL Baselines for all your SQL first” – it wouldn’t have been me that said it, though!  So here’s something to consider in the light of the whole yes/no argument about optimizer_features_enable, what does a baseline look like ? Here, taken from an 11g database is the critical content of a baseline for “select user from dual”:


IGNORE_OPTIM_EMBEDDED_HINTS
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
OUTLINE_LEAF(@"SEL$1")

It’s a set of hints – including the optimizer_features_enable() hint.Using SQL Baselines to stabilise your code on the upgrade leaves you exposed (in principle) to exactly the problem in the MoS notes that Mike cited as his example of the parameter undoing a bug-fix and producing wrong results. That, by the way, is why I’m not worried by Mike’s example: if the parameter re-introduces a bug then you would have been running with the bug – or probably a workaround to the bug – anyway (Unless, say, you upgraded from 11.2.0.1 to 11.2.0.4 and decided to set the parameter to 11.2.0.3.1 – but that’s not a strategy compatible with the idea of using the parameter for stability with minimum short-term change.)

The second MoS note that Mike cites is really the one that states – emphasis is mine – a realistic view of the parameter (though I’d view the restriction to Oracle Global Support is a legal cop-out rather than a pure technology requirement):

Modifying the OPTIMIZER_FEATURES_ENABLE parameter generally is strongly discouraged and should only be used as a short term measure at the suggestion of Oracle Global Support.

The follow-up comment is, to my mind, a bit hand-wavy:

By reducing the OPTIMIZER_FEATURES_ENABLE level, new optimizer features are disabled. This has serious potential for negatively affecting performance generally by eliminating the possibility of choosing better plans that are only available with features enabled within the higher revision levels.

Arguing the case against setting the parameter because of the potential for affecting performance negatively – when you’re doing it so that nothing changes – is about as valid as the argument for setting it because of the potential for affecting performance negatively in a tiny percentage of plans that use new features when it’s a very bad idea.

Bottom line: whether or not you set the parameter you’re likely to hit a few edge cases where performance suffers; the less time you have for proper testing in advance the more likely you are to feel the need to set the parameter – but if you start heading in that direction think about using the time you do have available to minimise the scope, or even getting down to the detail of which ACTUAL feature is the problem feature that needs to be disabled for your system.

Footnote

If you want to check which parameters and fix controls change as you set the optimizer_features_enable you could mess around with the dynamic performance views. Alternatively you could take advantage of the optimizer trace – it’s one of the easy things that the 10053 offers.  Enable the trace, optimize a simple statement, then check the trace file for the bit about optimizer parameters – the section you need from 12c trace will be as follows:


***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************

... Some 1,700 lines

***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************

In my case I connected to SQL*Plus, enabled the trace and executed “select 1 from dual”; then I reconnected, set the trace again, set the optimizer_features_enable back to 11.2.0.4 (I was on 12.1.0.2 at the time) and executed “select 2 from dual”. Then I deleted everything but the relevant section from the two trace files. One of the joys of Unix is that you can then run commands like the following:

sdiff  -s  or32_ora_2098.trc  or32_ora_2110.trc  |  expand  >ofe_diff.txt

That’s “side by side comparison, showing only the differences, expand tab marks out to spaces”. Here’s the result (with one blank line inserted between the parameters and the fix controls):

                                                              > optimizer_features_enable           = 11.2.0.4
                                                              > _fix_control_key                    = -1750344682
optimizer_features_enable           = 12.1.0.2                <
_optimizer_undo_cost_change         = 12.1.0.2                | _optimizer_undo_cost_change         = 11.2.0.4
_fix_control_key                    = 0                       <
_optimizer_cube_join_enabled        = true                    | _optimizer_cube_join_enabled        = false
_optimizer_hybrid_fpwj_enabled      = true                    | _optimizer_hybrid_fpwj_enabled      = false
_px_replication_enabled             = true                    | _px_replication_enabled             = false
_optimizer_partial_join_eval        = true                    | _optimizer_partial_join_eval        = false
_px_concurrent                      = true                    | _px_concurrent                      = false
_px_object_sampling_enabled         = true                    | _px_object_sampling_enabled         = false
_optimizer_unnest_scalar_sq         = true                    | _optimizer_unnest_scalar_sq         = false
_px_filter_parallelized             = true                    | _px_filter_parallelized             = false
_px_filter_skew_handling            = true                    | _px_filter_skew_handling            = false
_optimizer_multi_table_outerjoin    = true                    | _optimizer_multi_table_outerjoin    = false
_px_groupby_pushdown                = force                   | _px_groupby_pushdown                = choose
_optimizer_ansi_join_lateral_enhance = true                   | _optimizer_ansi_join_lateral_enhance = false
_px_parallelize_expression          = true                    | _px_parallelize_expression          = false
_optimizer_ansi_rearchitecture      = true                    | _optimizer_ansi_rearchitecture      = false
_optimizer_gather_stats_on_load     = true                    | _optimizer_gather_stats_on_load     = false
_px_adaptive_dist_method            = choose                  | _px_adaptive_dist_method            = off
_optimizer_batch_table_access_by_rowid = true                 | _optimizer_batch_table_access_by_rowid = false
_px_wif_dfo_declumping              = choose                  | _px_wif_dfo_declumping              = off
_px_wif_extend_distribution_keys    = true                    | _px_wif_extend_distribution_keys    = false
_px_join_skew_handling              = true                    | _px_join_skew_handling              = false
_px_partial_rollup_pushdown         = adaptive                | _px_partial_rollup_pushdown         = off
_px_single_server_enabled           = true                    | _px_single_server_enabled           = false
_optimizer_dsdir_usage_control      = 126                     | _optimizer_dsdir_usage_control      = 0
_px_cpu_autodop_enabled             = true                    | _px_cpu_autodop_enabled             = false
_optimizer_use_gtt_session_stats    = true                    | _optimizer_use_gtt_session_stats    = false
_optimizer_adaptive_plans           = true                    | _optimizer_adaptive_plans           = false
_optimizer_proc_rate_level          = basic                   | _optimizer_proc_rate_level          = off
_adaptive_window_consolidator_enabled = true                  | _adaptive_window_consolidator_enabled = false
_optimizer_strans_adaptive_pruning  = true                    | _optimizer_strans_adaptive_pruning  = false
_optimizer_null_accepting_semijoin  = true                    | _optimizer_null_accepting_semijoin  = false
_optimizer_cluster_by_rowid         = true                    | _optimizer_cluster_by_rowid         = false
_optimizer_cluster_by_rowid_control = 129                     | _optimizer_cluster_by_rowid_control = 3
_distinct_agg_optimization_gsets    = choose                  | _distinct_agg_optimization_gsets    = off
_gby_vector_aggregation_enabled     = true                    | _gby_vector_aggregation_enabled     = false
_optimizer_vector_transformation    = true                    | _optimizer_vector_transformation    = false
_optimizer_aggr_groupby_elim        = true                    | _optimizer_aggr_groupby_elim        = false
_optimizer_reduce_groupby_key       = true                    | _optimizer_reduce_groupby_key       = false
_optimizer_cluster_by_rowid_batched = true                    | _optimizer_cluster_by_rowid_batched = false
_optimizer_inmemory_table_expansion = true                    | _optimizer_inmemory_table_expansion = false
_optimizer_inmemory_gen_pushable_preds = true                 | _optimizer_inmemory_gen_pushable_preds = false
_optimizer_inmemory_autodop         = true                    | _optimizer_inmemory_autodop         = false
_optimizer_inmemory_access_path     = true                    | _optimizer_inmemory_access_path     = false
_optimizer_inmemory_bloom_filter    = true                    | _optimizer_inmemory_bloom_filter    = false
_optimizer_nlj_hj_adaptive_join     = true                    | _optimizer_nlj_hj_adaptive_join     = false
_px_external_table_default_stats    = true                    | _px_external_table_default_stats    = false
_optimizer_inmemory_minmax_pruning  = true                    | _optimizer_inmemory_minmax_pruning  = false
_optimizer_inmemory_cluster_aware_dop = true                  | _optimizer_inmemory_cluster_aware_dop = false

    fix  9898249 = enabled                                    |     fix  9898249 = disabled
    fix 10004943 = enabled                                    |     fix 10004943 = disabled
    fix  9554026 = enabled                                    |     fix  9554026 = disabled
    fix  9593547 = enabled                                    |     fix  9593547 = disabled
    fix  9833381 = enabled                                    |     fix  9833381 = disabled
    fix 10106423 = enabled                                    |     fix 10106423 = disabled
    fix 10175079 = enabled                                    |     fix 10175079 = disabled
    fix 10236566 = enabled                                    |     fix 10236566 = disabled
    fix  9721228 = enabled                                    |     fix  9721228 = disabled
    fix  9929609 = enabled                                    |     fix  9929609 = disabled
    fix 10182672 = enabled                                    |     fix 10182672 = disabled
    fix  9832338 = enabled                                    |     fix  9832338 = disabled
    fix 11668189 = enabled                                    |     fix 11668189 = disabled
    fix 11940126 = enabled                                    |     fix 11940126 = disabled
    fix 12390139 = enabled                                    |     fix 12390139 = disabled
    fix 11744016 = enabled                                    |     fix 11744016 = disabled
    fix 10216738 = enabled                                    |     fix 10216738 = disabled
    fix 12563419 = enabled                                    |     fix 12563419 = disabled
    fix 12535474 = enabled                                    |     fix 12535474 = disabled
    fix 12561635 = enabled                                    |     fix 12561635 = disabled
    fix 12569245 = enabled                                    |     fix 12569245 = disabled
    fix 12569300 = enabled                                    |     fix 12569300 = disabled
    fix 12569316 = enabled                                    |     fix 12569316 = disabled
    fix 12569321 = enabled                                    |     fix 12569321 = disabled
    fix  9002958 = enabled                                    |     fix  9002958 = disabled
    fix 12810427 = enabled                                    |     fix 12810427 = disabled
    fix 12914055 = enabled                                    |     fix 12914055 = disabled
    fix 12978495 = enabled                                    |     fix 12978495 = disabled
    fix 13110511 = enabled                                    |     fix 13110511 = disabled
    fix 13345888 = enabled                                    |     fix 13345888 = disabled
    fix 13396096 = enabled                                    |     fix 13396096 = disabled
    fix 12999577 = enabled                                    |     fix 12999577 = disabled
    fix 12954320 = enabled                                    |     fix 12954320 = disabled
    fix 13036910 = enabled                                    |     fix 13036910 = disabled
    fix 12648629 = enabled                                    |     fix 12648629 = disabled
    fix 13704977 = enabled                                    |     fix 13704977 = disabled
    fix 11843466 = enabled                                    |     fix 11843466 = disabled
    fix 13909909 = enabled                                    |     fix 13909909 = disabled
    fix 12856200 = enabled                                    |     fix 12856200 = disabled
    fix  9852856 = enabled                                    |     fix  9852856 = disabled
    fix 14033181 = enabled                                    |     fix 14033181 = disabled
    fix 13836796 = enabled                                    |     fix 13836796 = disabled
    fix 13699643 = enabled                                    |     fix 13699643 = disabled
    fix 13735304 = enabled                                    |     fix 13735304 = disabled
    fix 14464068 = enabled                                    |     fix 14464068 = disabled
    fix 13448445 = enabled                                    |     fix 13448445 = disabled
    fix  9114915 = enabled                                    |     fix  9114915 = disabled
    fix 13109345 = enabled                                    |     fix 13109345 = disabled
    fix 14605040 = enabled                                    |     fix 14605040 = disabled
    fix 14633570 = enabled                                    |     fix 14633570 = disabled
    fix 13573073 = enabled                                    |     fix 13573073 = disabled
    fix 16237969 = enabled                                    |     fix 16237969 = disabled
    fix 13994546 = enabled                                    |     fix 13994546 = disabled
    fix 14750443 = enabled                                    |     fix 14750443 = disabled
    fix 14552075 = enabled                                    |     fix 14552075 = disabled
    fix 16324844 = enabled                                    |     fix 16324844 = disabled
    fix 13583529 = enabled                                    |     fix 13583529 = disabled
    fix 14565911 = enabled                                    |     fix 14565911 = disabled
    fix 16368002 = enabled                                    |     fix 16368002 = disabled
    fix 16077770 = enabled                                    |     fix 16077770 = disabled
    fix 11814337 = enabled                                    |     fix 11814337 = disabled
    fix 14764840 = enabled                                    |     fix 14764840 = disabled
    fix 16555865 = enabled                                    |     fix 16555865 = disabled
    fix 16625151 = enabled                                    |     fix 16625151 = disabled
    fix 16609749 = enabled                                    |     fix 16609749 = disabled
    fix 16751246 = enabled                                    |     fix 16751246 = disabled
    fix 16749025 = enabled                                    |     fix 16749025 = disabled
    fix 16750067 = enabled                                    |     fix 16750067 = disabled
    fix 15899648 = enabled                                    |     fix 15899648 = disabled
    fix 16690013 = enabled                                    |     fix 16690013 = disabled
    fix 16544878 = enabled                                    |     fix 16544878 = disabled
    fix 16725982 = enabled                                    |     fix 16725982 = disabled
    fix 14648222 = enabled                                    |     fix 14648222 = disabled
    fix 16507317 = enabled                                    |     fix 16507317 = disabled
    fix 16837274 = enabled                                    |     fix 16837274 = disabled
    fix 14085520 = enabled                                    |     fix 14085520 = disabled
    fix 16713081 = enabled                                    |     fix 16713081 = disabled
    fix 14703295 = enabled                                    |     fix 14703295 = disabled
    fix 16908409 = enabled                                    |     fix 16908409 = disabled
    fix 16212250 = enabled                                    |     fix 16212250 = disabled
    fix 17087729 = enabled                                    |     fix 17087729 = disabled
    fix 17088819 = enabled                                    |     fix 17088819 = disabled
    fix 13848786 = enabled                                    |     fix 13848786 = disabled
    fix 13522189 = enabled                                    |     fix 13522189 = disabled
    fix 16796185 = enabled                                    |     fix 16796185 = disabled
    fix 15950252 = enabled                                    |     fix 15950252 = disabled
    fix 16976121 = enabled                                    |     fix 16976121 = disabled
    fix 16582322 = enabled                                    |     fix 16582322 = disabled
    fix 16712213 = enabled                                    |     fix 16712213 = disabled
    fix 17382690 = enabled                                    |     fix 17382690 = disabled
    fix 14846352 = enabled                                    |     fix 14846352 = disabled
    fix 16516751 = enabled                                    |     fix 16516751 = disabled
    fix  8611462 = enabled                                    |     fix  8611462 = disabled
    fix 14062749 = enabled                                    |     fix 14062749 = disabled
    fix 16346018 = enabled                                    |     fix 16346018 = disabled
    fix 12977599 = enabled                                    |     fix 12977599 = disabled
    fix 14191778 = enabled                                    |     fix 14191778 = disabled
    fix 15939321 = enabled                                    |     fix 15939321 = disabled
    fix 17543180 = enabled                                    |     fix 17543180 = disabled
    fix 17301564 = enabled                                    |     fix 17301564 = disabled
    fix 12373708 = enabled                                    |     fix 12373708 = disabled
    fix 17397506 = enabled                                    |     fix 17397506 = disabled
    fix 14558315 = enabled                                    |     fix 14558315 = disabled
    fix 16615686 = enabled                                    |     fix 16615686 = disabled
    fix 16622801 = enabled                                    |     fix 16622801 = disabled
    fix 16954950 = enabled                                    |     fix 16954950 = disabled
    fix 17728161 = enabled                                    |     fix 17728161 = disabled
    fix 17760375 = enabled                                    |     fix 17760375 = disabled
    fix 17640863 = enabled                                    |     fix 17640863 = disabled
    fix 17716301 = enabled                                    |     fix 17716301 = disabled
    fix 17597748 = enabled                                    |     fix 17597748 = disabled
    fix 17303359 = enabled                                    |     fix 17303359 = disabled
    fix 16673868 = enabled                                    |     fix 16673868 = disabled
    fix 17800514 = enabled                                    |     fix 17800514 = disabled
    fix 14826303 = enabled                                    |     fix 14826303 = disabled
    fix 17663076 = enabled                                    |     fix 17663076 = disabled
    fix 17760755 = enabled                                    |     fix 17760755 = disabled
    fix 17997159 = enabled                                    |     fix 17997159 = disabled
    fix 14733442 = enabled                                    |     fix 14733442 = disabled
    fix 17781659 = enabled                                    |     fix 17781659 = disabled
    fix 17526569 = enabled                                    |     fix 17526569 = disabled
    fix 17760686 = enabled                                    |     fix 17760686 = disabled
    fix 17696414 = enabled                                    |     fix 17696414 = disabled
    fix 18116777 = enabled                                    |     fix 18116777 = disabled
    fix 16052625 = enabled                                    |     fix 16052625 = disabled
    fix 18091750 = enabled                                    |     fix 18091750 = disabled
    fix 17572606 = enabled                                    |     fix 17572606 = disabled
    fix 18196576 = enabled                                    |     fix 18196576 = disabled
    fix 17736165 = enabled                                    |     fix 17736165 = disabled
    fix 16434021 = enabled                                    |     fix 16434021 = disabled
    fix 18035463 = enabled                                    |     fix 18035463 = disabled
    fix 18011820 = enabled                                    |     fix 18011820 = disabled
    fix 16405740 = enabled                                    |     fix 16405740 = disabled
    fix 18365267 = enabled                                    |     fix 18365267 = disabled
    fix 17863980 = enabled                                    |     fix 17863980 = disabled
    fix 18398980 = enabled                                    |     fix 18398980 = disabled
    fix 18304693 = enabled                                    |     fix 18304693 = disabled
    fix 18508675 = enabled                                    |     fix 18508675 = disabled
    fix 18456944 = enabled                                    |     fix 18456944 = disabled
    fix 17908541 = enabled                                    |     fix 17908541 = disabled
    fix 18467455 = enabled                                    |     fix 18467455 = disabled
    fix 16033838 = enabled                                    |     fix 16033838 = disabled
    fix 16809786 = enabled                                    |     fix 16809786 = disabled
    fix 18425876 = enabled                                    |     fix 18425876 = disabled
    fix 18461984 = enabled                                    |     fix 18461984 = disabled
    fix 17023040 = enabled                                    |     fix 17023040 = disabled
    fix 14776289 = enabled                                    |     fix 14776289 = disabled

That’s 50 parameter differences, and 147 fix controls. Quite a lot of fixes between the two versions.

If you’re coming to the upgrade a couple of years late then you might want to consider using the new version number and list of parameters you generate as the criteria as a search for bugs in MoS. You might even find that simply running your eye down the list of parameters gives you a clue about a type of execution plan that you’ve never seen in the older version.