November 2016

Performing a large correlated update

We had a question on AskTom recently asking for the best way to update a new column on a very large table.  Four in-place update options are at our disposal:

  • Serial update SQL
  • Parallel DML update SQL
  • DBMS_PARALLEL_EXECUTE
  • PL/SQL batching (as long as sensible restart-after-error logic is readily available)

all of which will achieve the required outcome but they have a significant redo and undo cost associated with them.  Also, depending on the size of the new column, such an update might create chaos with row migration, because whilst unlikely, there is definitely the potential for every row to grow beyond the available block space required to hold it in situ.

Enjoying a great conference at the #DOAG2016

It’s DOAG season again and the Oracle Community is having a good time here! That includes me, presenting about the  Multitenant architecture:

doag2016https://uhesse.files.wordpress.com/2016/11/doag2016.jpg?w=1240&h=930 1240w, https://uhesse.files.wordpress.com/2016/11/doag2016.jpg?w=150&h=113 150w, https://uhesse.files.wordpress.com/2016/11/doag2016.jpg?w=300&h=225 300w, https://uhesse.files.wordpress.com/2016/11/doag2016.jpg?w=768&h=576 768w, https://uhesse.files.wordpress.com/2016/11/doag2016.jpg?w=1024&h=768 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Redo a blogpost

This blogpost is about the Oracle redo log structures and redo efficiency in modern Oracle databases. Actually, a lot of subtle things changed surrounding redo (starting from Oracle 10 actually) which have gone fairly unnoticed. One thing the changes have gone unnoticed for is the Oracle documentation, the description of redo in it is an accurate description for Oracle 9, not how it is working in Oracle 10 or today in Oracle 12.1.0.2.

My test environment is a virtual machine with Oracle Linux 7.2 and Oracle 12.1.0.2.161018, and a “bare metal” server running Oracle Linux 6.7 and Oracle 12.1.0.2.160419. Versions are important, as things can change between versions.

Optimizer Compatibility, Short Pants and Hot Sauce

OK, so I’m all over the map, (technology wise) right now.  One day I’m working with data masking on Oracle, the next it’s SQL Server or MySQL, and the next its DB2.

Distributed Trap

Here’s an interesting (and potentially very useful) observation from an OTN database forum thread that appeared at the end of last week. It concerns the problems of pulling data from remote systems, and I’ll start by building some data:

Demographically correct test data

One of the main reasons behind finishing versions 1.5.0 of RANDOM_NINJA, was to be able to add the localization for different countries so that I could create test data sets, that are demographically correct.

So after adding that, the TESTDATA_NINJA package, can now create statistically correct data sets for 3 countries:

  • United States
  • China
  • Denmark

The following ratio of data will be correct according to UN Statistics and CIA World Book:

  • Age group - Age ratios will be divided in 0-14, 15-64 and 65+
  • Female/Male - Gender rations will be divided within the different age groups, according to statistics

Identification numbers and birthdays will also be in statistically correct ratios and in valid formats.

Sangam16 Session: Resolving Latch Contention

Many thanks to those who came to my session at Sangam, the annual conference of All India Oracle Users Group. You can download the presentation materials here:

Presentation here
Scripts here

As always, I will greatly appreciate to hear your thoughts on the session.. Please reach out to me via a comment on this post, or on the following:

Email: arup@proligence.com
Twitter: @ArupNanda
Facebook: ArupKNanda
Google+: +ArupNanda

OTN Ace Directors APAC Tour at Gold Coast

Thank you all for coming to my sessions at the Glod Coast conference of Oracle Technology Network +Oracle Community Network ACE Directors Tour in APAC. Please feel free to download the materials presented.

1. Secure your database in 1 day Presentation and Scripts
2. Prevent Bad SQLs in the Cloud Presentation
3. Becoming a Multitenant DBA Presentation

As always, I will appreciate to hear from you how you "really" felt about them--the good , the bad and the ugly. You can post a comment on the blog right here, or reach me via:

Sangam16 Session: Preventing SQL and PL/SQL Injection Attacks

Thank you all those who came to my session at Sangam 16, the conference of All India Oracle User Group, where I presented as a part of the OTN ACE Directors APAC Tour.

You can download the materials here. Slides and Scripts used in the demo

As always, I would highly appreciate your feedback, be it here as a comment, or via social media or email.

Email: arup@proligence.com
Twitter: @ArupNanda
Facebook.com/ArupKNanda
Google+: +ArupNanda