Simon Krenger

Storage costs are a major matter of expense in any large database environment. Therefore, in order to operate a cost-optimized environment, it makes sense to regularly review all databases in terms of datafile sizes. Usage of these files can vary dramatically, depending on the application using the database. Therefore, we need a way to quickly identify datafiles which we can shrink in order to save storage costs.

Because we cannot really influence how Oracle places the extents in a datafile, we cannot simply look at the “used” size of the datafile and resize it to that value, but need to find the HWM (high water mark) of each datafile.

With Oracle 12c, Oracle introduced a new way to insert primary keys into tables: The GENERATED BY DEFAULT and DEFAULT keywords.

In this post, I want to compare the performance of different methods to insert a primary key into a table in Oracle. The comparison will also include older methods, such as using a trigger or specifying the primary key in the INSERT statement. These methods can also be used in Oracle versions earlier than 12c.

First of all, let’s look at the possible ways to insert the value of a sequence into a table:

So recently I stumbled across a programming quiz to which I later returned because it somehow fascinated me.

Problem

Finding the first available number (or the smallest missing number) in a list is a common problem in Computer Science (for example for Defragmenting or generating keys) and describes the search for the smallest natural number, which is not part of a set X of natural numbers. X is a set of distinct natural numbers (and being a set, is not ordered).

We are now looking for a function with linear worst-case time complexity O(n).

Example

We define X as a set of distinct natural numbers:

X = {23,9,12,0,11,1,13,7,21,14,5,4,17,19,3,6,2}

So in this set, we find that the number 8 is the first available number (smallest missing number). So running the algorithm over the above set should return 8.

As many of my colleagues, I have been eager to try out the new Pluggable Database (PDB) feature of the newly released Oracle 12c Database. I installed the software, created the database using my default CREATE DATABASE statement I have lying around and then tried to create a pluggable database by issuing the following command:

SQL> create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/');
create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/')
                          *
ERROR at line 1:
ORA-65090: operation only allowed in a container database

It turns out a database explicitly needs to be created as a Container Database (CBD) in order to be able to create new PDBs!

So I get many visits for my post on Oracle Full Datapump Export Batch, so here is the same export script for UNIX systems.

Before using this script, please read my advice:

Since most of our databases are not licensed with the Oracle Enterprise Manager Diagnostic Pack, we cannot use AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor). So we have to use the good old Oracle STATSPACK.

The goal of this article is to provide a quick reference for installing and maintaining STATSPACK for an Oracle database. It is based on the excellent “STATSPACK Survival Guide” of Akadia AG. If you need more information on STATSPACK, please refer to the Survival Guide or the Oracle documentation, since this article is only a really quick summary on how to install, configure and use STATSPACK.

One thing DBAs regularly do is to gather information on the schemas in the database for reporting. So we want to know how many rows there are in the tables of the schema “SIMON”. When doing so, one is tempted to query the DBA_TABLES view:

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM dba_tables WHERE owner='SIMON' ORDER BY num_rows DESC;

TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
HUGE_TABLE                     DZ_DATA01                         8227990
[..]

However, if we query the table directly, we get a different result:

SQL> SELECT count(*) FROM simon.HUGE_TABLE;

  COUNT(*)
----------
   8230310

Why is that so? When reading through the description of the ALL_TABLES view, one finds the following note:

When deploying a new database, one thing I usually do is to set up a logrotate configuration for the Oracle Listener log. The Oracle Listener logs every connection he makes and when using an Application Server to interface the database, this file can grow quite a bit. So we need to make sure that we properly rotate this log and compress the old logs.

To achieve this, I usually create the following configuration for logrotate:

The Linux kernel provides the tmpfs, which basically creates a file system in memory. This temporary file system can be used to store temporary data, such as caches or log files. Read more about tmpfs in the kernel documentation: tmpfs.txt

After reading this excellent article about using tmpfs in Linux, I decided to put it to the test. Even though the Linux kernel already does a good job caching files, I wanted to see the performance of this solution by applying different loads on it. For this, I am using the IOzone tool I already used for my ZFS tests (1) (2) and my Amazon EC2 IO test.

So one might want to ask why you’d want to sacrifice the fantastic stability and openness of Debian to install a vanilla (= original) kernel (Debian currently has 2.6.32). There are quite a few reasons for doing so. For example, the current kernel (2.6.38) has TRIM support, which is something I am looking for when using SSDs. Also, maybe you want to have a bleeding edge kernel just for the fun of it. So lets get started.

First order of business is to download a few packages, download the latest kernel from kernel.org and then unpack the kernel in /usr/src/: