Yann Neuhaus

A recurrent question I have often heard about Hekaton objects is the following: Is an accidental deletion of the compilation files of a hekaton table on the file system irreversible and could this compromise the execution of SQL Server?

To check the SQL Server behaviour in such situation, we can perform the following test with an in-memory optimized table:

 

CREATE TABLE [dbo].[StorageTestTable] (        [c1] [int] NOT NULL,        [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL,   CONSTRAINT [index91113] PRIMARY KEY NONCLUSTERED HASH (        [c1] )WITH ( BUCKET_COUNT = 1048576) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) GO

During the creation of the hekaton table, several files are also created on the file system. We can easily identify that the files belong to the hekaton table by using the naming convention:

xtp_[database_id]_[object_id]

 

 

The database id is 8 and the object id is 277576027. We can retrieve the object name by using T-SQL:

 

SELECT
    name AS table_name,
    type_desc,
    is_memory_optimized,
    durability_desc
FROM sys.tables
WHERE [object_id] = 277576027;
GO

 

 

 

Now, let’s try to delete all the files by using the command line del:

 

 

We can see that all files except the dll file have been deleted. If we take a look at this file by using the process explorer tool from sysinternals to see if the dll is handled by a specific application, we notice that SQL Server is handling the dll file (as expected).

 

 

At this point, if we try to select data from the Hekaton table dbo.StorageTestTable, we notice it still works … that’s great !

SELECT * FROM dbo.StorageTestTable

 

 

Let's try something else and delete the dll file after shutting downing the SQL Server instance.

 

SHUTDOWN WITH NOWAIT;

This time, there is no "access denied" error message.

 

 

Now it’s time to restart the SQL Server instance and then take a look at the folder. What a surprise! All files have been recreated …

 

 

... and if we try to select data from the corresponding hekaton table a second time, we can see it still works, but how? In fact, SQL Server only needs the dll file associated with the hekaton table to use it. Each time the SQL Server instance or a database is restarted, the hekaton table dll is recompiled and injected to the SQL Server process. In order to do this, SQL Server uses both a C compiler and a linker located to the path MSSQL12.MSSQLBinnXtpVCBin. On my lab machine, it i:

C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLBinnXtpVCbin

 

As a reminder, creating a dll in C is a multistage process divided into two steps: compilation and linking. We can check how SQL Server compiles and generates the different files of the hekaton table during the start / restart phase by using the procmon tool from sysinternals. We just need to filter the process name for sqlservr.exe, cl.exe link.exe.

First when a Hekaton database is shutting down the hekaton tables, files are deleted as we can see on the following picture:

 

 

The procmon tool traces give us the following result:

 

The createFile() method is called with the following desired access:

 

...

The delete flag tells us that the file will immediately be deleted after all of its handles get closed (issued later by each CloseFile() function) . This means that all the hekaton table compilation files are opened and closed with this specific flag and will be deleted during the database shutdown.

Next, during the restart of the SQL Server instance, a procmon captures give us an interesting picture. For convenience, I’ve broken down the result on three pictures with explanations:

 

 

...

 

 

 

...

 

 

SQL Server recreates the folder hierarchy and the files associated with the hekaton table (disposition = Create) but that’s not all. Later in the procmon trace, we can see that the C compiler is called by the sqlserver.exe process as showed in the below picture:

 

 

 

…

In my test screeshot above the process ID (PID) 1144 is my SQL Server instance. Finally, we later see that the compiler calls the linker. The process id (PID) 1560 is the C compiler (cl.exe)

 

 

…

Once my SQL Server instance is up and running all the files are finally recreated. In short, we showed an existing process about C compilation. My purpose is not to give a C compilation course but just enough for you to get to know how a dll is compiled. Here is a very simplified process schema for the hekaton dll compilation:

Source code file (.c) --> compiler (cl.exe) --> object file (.obj) --> linker (link.exe) -->

dynamic library (.dll)

 

How about other files? The pdb file is created by the linker and is related to the target executable or the DLL. This file contains the complete debug information and among them the symbols that we can use with windbg (one of my favorite tool to try to understand how SQL Server works). Then, the file with the extension .out is a verbose file that contains log messages for troubleshooting and finally the xml file contains MAT representation (Mixed Abstract Tree) transformed by SQL Server into PIT (Pure Imperative Tree) representation in order to transform SQL Like- data types into C-like data types. We retrieve the transformation in the C file.

What can we conclude after seeing the SQL Server process using only the dll file during its execution? I would say it’s good news because an accidental deletion of these files will not compromise the good execution of SQL Server and hekaton objects. However, as usual, let's keep the good habits alive and don’t touch them! It's still bad!

Oracle has announced a big feature that should come in the next 12c patch set: the In-Memory option. It will soon be in beta version, so nothing will be published about it until it comes to production. Before that phase, I'm going to explain what we can expect from that feature, besides the 'In-Memory','columnar storage', and 'hybrid' buzzwords.

First, it is a redundant storage that is aimed to improve the query performance.
But this is not new: indexes and materialized views have the same goal.

Second: Redundant data will be stored In-Memory and will be optimized for it.
That is not new either if we consider TimesTen. TimesTen is an In-Memory relational database that can also be used in front of Oracle Database when used as an In-Memory Database Cache.

What is new is that it is a columnar storage. Columnar storage is already used by Oracle for compression (Hybrid Columnar Compression) but here, the goal is different. Very different. HCC uses CPU to save I/O and disk, and is mainly for read-only data. In-Memory option is for actively updated data residing in memory and the goal is to use less CPU. Let's explain this.

I'll use a well known relational table: SCOTT.EMP where I highligh the columns and rows that I'll need for my query:

 

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800   20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975   20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850   30 7782 CLARK MANAGER 7839 09-JUN-81 2450   10 7788 SCOTT ANALYST 7566 19-APR-87 3000   20 7839 KING PRESIDENT   17-NOV-81 5000   10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100   20 7900 JAMES CLERK 7698 03-DEC-81 950   30 7902 FORD ANALYST 7566 03-DEC-81 3000   20 7934 MILLER CLERK 7782 23-JAN-82 1300   10

 

This is a table. It has two dimensions: rows and columns. How do you read it ? There are two ways:

• Read it by column: I have employee numbers 7369, 7499, 7521, ... Their names are respectively SMITH, ALLEN, WARD, ... The first one is a CLERK, the second and third ones are SALESMEN, ...

• Read it by row: I have an employee that I identify as number 7369, his name is SMITH, he is a CLERK, his manager is... I have another employee which is number 7499, his name is ALLEN...

Which way do you choose ? You probably read it by row. Because it is easier to think about it when we group the entities we are talking about. When you have a new employee, you fill a form with his name, job, hiredate, etc. When you update some information, you lock the row, saying 'I'm working on employee 7369', and update the attributes you want to change. You do that because you prefer to cluster together the information that are related, it's easier to manage for our mind. Note that when you program, you often have the same logic. You manage objects, you implement CRUD operation to change their attributes. And your computer knows that, so it tries to be more efficient on clustered data, with prefetching or cache.

Relational Database did the same choice when physically manipulating data.
The SQL language let you have both approaches. You update one row, such as:
   UPDATE EMP SET ... WHERE EMPNO=7369;
or you can update one column, such as
   UPDATE EMP SET SAL=SAL*1.1;

But physically, data is stored by row and is manipulated by row, as when you read the table row by row. And it is a very efficient approach when you manipulate several attributes from one row: you do only one I/O to get the block from disk, you store only one block in cache, and the CPU is able to process everything in one block. Great.

But now let's see what happens when you don't need all the columns. Let's say you want to find who has a salary higher than 3000.

SELECT ENAME from EMP where SAL>=3000;

You have to read the whole table (full table scan) and read each block, and in each block read each row, and for each row find the salary, compare it to 3000 and if it is higher, find the name.

Note that I'm not talking about I/O. Even when my table fit in memory I have a lot of information to read, I have to transfert each value from memory to CPU, and use CPU cycles to process each value.

The first problem is that you have read information about 14 employees where you're interrested only in 3 of them. So you build an index on SAL. From the index you get the rowid for the employees that have SAL>=3000. And then for each of them you go to the row to get its name. Ok, that's not bad, but when you have a lot of queries like that, you will have to create a lot of indexes. You can create an index for each column but then there is the cost to combine to result. We are on OLTP actively updated data, so bitmap index is not a solution here. Or you can create an index for each kind of filtering you can do, but that's a lot of indexes to maintain.

But then there is the second problem. Even if you have to process only the 3 employees you are interrested in, you will have to read the whole row, which is stored in variable length, from begining to end, in order to find the only columns that you need. This is: read row directory, follow pointer to row, read EMPNO length, bypass it, read ENAME, get it, ... This is in memory, and has to be transferred to CPU to be processed, word by word.

To address that problem, you can add to your index all the columns you're interrested in. But then you will have too many indexes to maintain. And you know how hard it is to determine which indexes can be useful or not.

Now let's see how that works with the columnar approach we have seen above. Without any indexes, we will read the SAL column and the result is that item 8,9 and 13 are above 3000. Then we read the ENAME column and just have to get the 8,9 and 13 items. And that's all!

Without any additional structure we had to read only what we are interrested in. And there is more. On current processors when we have the same operation to do on a several values we can do in in a whole with a vector operation. Just pass a vector of salaries to the CPU and ask it to compute the '>=3000' on all of them. This is known as 'Single Instruction Multiple Data'. This is less roundtrips between memory and CPU.

The first CPU operation is a 'find >=3000' on the following vector:

800 1600 1250 2975 1250 2850 2450 3000 5000 1500 1100 950 3000 1300

The second CPU operation is a 'find values from previous result index' on the following vector:

SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

Compare that with the row approach, navigating within the block, each pointer or each column value being a CPU operation... It's hundreds of CPU cycles that will be saved. This is something that was possible before only on supercomputers. Today our processors can do Single Instruction Multiple Data vector processing, and this is why in-memory columnar storage is coming everywhere.

And the volume to transfer can be decreased when we compress those vectors. Remember when I've read the ENAME in column: the second and third employees are SALESMAN. This is RLE compression where you don't repeat previous values. You can do that with columns, probably not within a row (or your data model is not that relational I guess).

This is the goal of columnar storage: increase efficiency of memory-cpu processing for queries that don't need to read the whole row, and without having to create indexes. Oracle has always been able to handle mixed workload quite well (OLTP and reporting in the same database), thanks to the read consistency implementation. So it is a natural evolution to go to the 'hybrid' approach that adds analytic capabilities to pure OLTP data models. Of course, this is not the kind of thing that is easy to implement. The columnar storage must be maintained, and must be optimized for an efficient CPU processing. The values must be easy to compare byte per byte in a single CPU operation, even when compressed. And CPU vector operation often requires that the values have the same size, so this may require fixed size storage instead of the usual variable length storage that reduces space.

So behind the feature that is supposed to accelerate our application just by switching it on, we can expect some complex technology that is able to bring In-Memory capabilities while staying in our Oracle OLTP database. In relational databases, the first goal for years was to optimize the access to the storage. And this is done by regrouping all information related to the same object, in order to get them in one I/O. Now, the goal is to improve the transfer between memory and CPU by regrouping similar information from several objects, in order to process them in vectors by the CPU. This is an orthogonal approach: row storage vs. columnar storage. Oracle will implement both. And we, the DBAs, have to understand how it works in order to use it in the right place.

It has been a long time since the last Oracle 12c Optimizer article, but this one is the last on the series about customer issues in Oracle 11g. The goal of that series is to show how the new version 12c of Oracle Database helps solving these issues. This blog posting focuses on a well know Oracle behavior when gathering histograms on a varchar2 column.

The three cases have been presented during the session "Solving critical customer issues with the Oracle 12c Optimizer" at the Oracle Open World 2013, the UKOUG Tech13 and the dbi's 12c appetizers.

Microsoft will introduce four new security permissions in SQL Server 2014. One of them called SELECT ALL USERS SECURABLES is the subject of this post.  As explained by Microsoft SQL Server 2014 will allow a database administrator to manage data without seeing sensitive data or personally identifiable information. We can achieve a greater compliance but we must take care what is said because we could be wrong about the terms “manage without seeing sensitive data”. Let me explain.

If we have a naïve approach we could misunderstand this terms and think “oh great … with SQL Server 2014 we will prevent a database administrator to see all data (sensitive or not) but in fact I think (personal point of view) on reflection it is not exactly the goal of this server permission.

Basically “SELECT ALL USERS SECURABLES” permission is designed, when granted, to allow a user to view data in all databases he can connect. For auditing purposes, the new permission is very interesting and it can be also used to prevent someone to read. Furthermore, SQL Server 2005 introduced the concept of “securable” that can be the server.  One of the new server permission provided is CONTROL SERVER often associated with sysadmin fixed role because it will grant full server level permissions and automatically grant full access to all databases but unlike sysadmin fixed role it allow a more granular approach to granting and denying access to individual securable (login in our case) without bypassing the permission check algorithm. Thus, applying security principles to DBA team staff begins by avoiding to use sysadmin role and to prefer CONTROL SERVER permission. At this point I know, we could claim this is not that easy as CONTROL SERVER has multiple caveats but let me demonstrate by a practical example where we can use the new server permission “SELECT ALL USERS SECURABLES” to restrict database administrators to view data in all databases.

First we can create a server role introduced with SQL Server 2012 for the database administrator team:

 

CREATE SERVER ROLE dba; GO

 

Then we create a database administrator login dba1:

 

CREATE LOGIN dba1 WITH PASSWORD = 'dba1', CHECK_POLICY = OFF; GO

 

We add the login as member of the dba1 fixed role :

 

ALTER SERVER ROLE dba ADD MEMBER dba1; GO

 

Now it’s time to play with both the server permission CONTROL SERVER and SELECT ALL USER SECURABLES to prevent a database administrator to read data in all databases.

 

GRANT CONTROL SERVER TO dba; GO   DENY SELECT ALL USER SECURABLES TO dba; GO

 

We can now ensure it works by connecting with the login dba1.

 

SELECT SUSER_NAME() AS login_name, USER_NAME() as [user_name]; GO

 

In my configuration I have a database named SensitiveDB with a table named SensitiveTable.

 

 

USE SensitiveDB; GO   SELECT * FROM INFORMATION_SCHEMA.TABLES; GO

 

 

SELECT * FROM INFORMATION_SCHEMA.COLUMNS; GO

 

 

SELECT        SPECIFIC_CATALOG,        SPECIFIC_SCHEMA,        SPECIFIC_NAME,        ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES;

 

 

And so on … At this point we can view all objects in the database and their definitions. Now if we try to read data in the table SensitiveTable or from the view V_SensitiveTable :

 

SELECT * FROM dbo.SensitiveTable;   SELECT * FROM dbo.V_SensitiveTable;

 

 

As expected we don’t have the permissions to read data in the database or from anywhere. Ok great, we’ve done a good job. Database administrators can manage the SQL Server instance but they cannot read data! But is it really over? The answer is of course no. Why? At this point we have to remember about the server permission CONTROL SERVER. It will grant full permissions on the server and automatically on all the databases. It means a person which have this server permission can grant himself permissions to a securable even if it exists an explicit DENY for this securable. It is easy to retrieve which permissions are denied : 

 

-- Check if dba1 is member of a server role SELECT        sp.name,        sp.principal_id as login_principal_id,        sp.type_desc,        sp2.name AS role_name,        sp2.principal_id as role_principal_id FROM sys.server_principals AS sp        LEFT JOIN sys.server_role_members AS sr              ON sp.principal_id = sr.member_principal_id        LEFT JOIN sys.server_principals AS sp2              ON sp2.principal_id = sr.role_principal_id WHERE sp.name = 'dba1';

 

 

 -- Check permissions for all concerned principal_id SELECT        grantee_principal_id,        class_desc,        [permission_name],        state_desc FROM sys.server_permissions WHERE grantee_principal_id IN(267, 268);

 

 

The login dba1 can now grant itself the “SELECT ALL USER SECURABLES” permission for the securable dba …

 

USE [master]; GO   GRANT SELECT ALL USER SECURABLES TO dba; GO

 

… and read data in the SensitiveTable

 

 

What a surprise !!! Is it a bug, a mistake or a Microsoft marketing lie ? I admit I had this reaction when I saw it and I'm pretty sure others also ! After a long discussion with some others SQL Server MVPS and friends, I don’t think because event if a database administrator does not need the read data to perform its tasks there can be some cases where it may be necessary, for example troubleshooting a long request with a specific set of data. If you need a more restrictive role, you have to look at granting only those subsets of CONTROL SERVER.  I remember a discussion with one of my customer about the implementation of the separation of duties for database administrators particularly one question: Is the goal to prevent DBA staff to perform theirs tasks? Of course not but we want to audit action when a database administrator needs to elevate its privileges. Fortunately SQL Server provides an audit feature which can help to detect a malicious use of sysadmin permissions.

We could setup an audit trail that can track the event GRANT SELECT ALL USER SECURABLES permission and records events into the Windows security log or into the audit files which the database administrator has no access. Furthermore since SQL Server 2012 we can fail an action if it cannot be recorded to the audit target (ON FAILURE = FAIL_OPERATION)

 

USE [master] GO   -- Creation of audit target CREATE SERVER AUDIT [HIGH_PERMISSIONS_GRANTED] TO FILE (      FILEPATH = N'E:AUDIT'        ,MAXSIZE = 1024 MB        ,MAX_FILES = 10        ,RESERVE_DISK_SPACE = ON ) WITH (      QUEUE_DELAY = 1000        ,ON_FAILURE = FAIL_OPERATION ) WHERE ([statement] LIKE 'GRANT SELECT ALL USER SECURABLES%'); GO   ALTER SERVER AUDIT [HIGH_PERMISSIONS_GRANTED] WITH (STATE = ON) GO   -- Creation of audit specification target CREATE SERVER AUDIT SPECIFICATION [HIGH_PERMISSIONS_GRANTED_specification] FOR SERVER AUDIT [HIGH_PERMISSIONS_GRANTED] ADD (        SERVER_PERMISSION_CHANGE_GROUP        -- To be completed by others usefull actions to track ) WITH (STATE = ON); GO

 

Now if dba1 need to grant the server permission “SELECT ALL USER SECURABLES” automatically the event is recorded to the corresponded trace file:

 

 

The audit trace in this case ca be used to detect quickly the malicious use of the some grant actions and limit the damages. Of course behind the scene a robust process must be in place before implementing the tool.

To summarize, SQL Server 2014 facilitates the segregation of duties by implementing new server permissions but segregation of duties does not mean prevent in all cases some actions. To be efficient the segregation of duties consists of the implementation of automatic and preventives control. Feel free to share your opinion on this subject !

Disclaimer: I'll talk about an undocumented feature that appeared in Oracle 12c - undocumented except if you consider that being exposed in DBA_SEQUENCES as the PARTITION_COUNT is a kind of documentation. So, as the syntax is not documented (I got it only by guessing), you should not use it in production: there are probably some reasons why Oracle did not expose an interresting feature like that.

In these days, you probably have most of primary keys coming from a sequence: a generated number that is always increasing in order to be sure to have no duplicates. And of course you have a (unique) index on it. But what do you see when you have a high activity inserting rows concurrently to that table ?

Here is what you can see:

That came from a retail database where we did a load test simulating the order entry rate that was expected for production.
The AWR report shows the segments concerned:

No doubt: index hot block contention on primary key.

It is a well know issue. Because the index is sorted, the value coming from the sequence is always going to the last block. All sessions have to access to the same block and there is a 'buffer busy wait' contention on it. And when you are in RAC that block has to be updated by the different nodes and it's worse, showing all those 'gc' wait events.

In order to illustrate the different solution, I have created a DEMO table with 1 million rows. And then I insert 100000 additional rows and check how many blocks where touched:

create table DEMO_TABLE (id,n , constraint DEMO_PK primary key(id) ) 
 as select rownum,rownum from 
  (select * from dual connect by level<=1000),
  (select * from dual connect by level <=1000);

create sequence DEMO_SEQUENCE start with 1e7;

insert into DEMO_TABLE select DEMO_SEQUENCE.nextval,rownum 
 from (select * from dual connect by level<=1000),
 (select * from dual connect by level <=100);

And here is the number of index blocks touched:

select object_name,data_object_id,sum(tch) from sys.x$bh 
 join user_objects on user_objects.data_object_id=x$bh.obj 
 where object_name like 'DEMO%' 
 group by object_name,data_object_id order by 1 desc;

OBJECT_NAME DATA_OBJECT_ID SUM(TCH) DEMO_TABLE 97346 243 DEMO_PK 97347 243

And I'm interrested about index block split as well:

select name,value from v$mystat 
 join v$statname using(statistic#) 
 where name like 'leaf node%splits';

NAME VALUE leaf node splits 200 leaf node 90-10 splits 200

I touched 200 blocks only (the size of the index entry is approximatively the same as the table row size). The highest block is filled with an increasing value and only when it is full the insert allocates a new block and goes to it. This is optimal for one session, but it is the cause of contention on multi-threaded inserts because all sessions are touching the same block at the same time.

So what's the solution ? Of course, you want to have your primary key value distributed.
Reverse index ? Let's see:

alter index DEMO_PK rebuild reverse;
Index altered.

Then I run the same inserts and here are the statistics about index blocks:

OBJECT_NAME DATA_OBJECT_ID SUM(TCH) DEMO_TABLE 97349 247 DEMO_PK 97352 4392
NAME VALUE leaf node splits 182 leaf node 90-10 splits 0

Now I touched 4000 blocks (without any block split because each new value fit in the 5% pctfree after my index rebuild). Great: no contention. I mean... no buffer contention.

But think about it. Because of my reverse distribution, I touch now all the index blocks. Do they fit in my buffer cache anymore ? Probably not. And one day, when I have more data, I encounter i/o issues.

If you want to see an illustration of the contentions I am talking about here, you can check the slides 14 to 19 from the Oracle Real Performance Tour where Tom Kyte, Andrew Holdsworth & Graham Wood have shown a nice demo of that.
So we want to spread the values on several index blocks, but not on all index blocks. Hash partitioning can be used for that. Let's have 4 partitions:

alter table DEMO_TABLE disable constraint DEMO_PK;
create index DEMO_PK on DEMO_TABLE(id) global partition by hash(id) partitions 4;
alter table DEMO_TABLE enable constraint DEMO_PK;

and the result is quite good:

OBJECT_NAME DATA_OBJECT_ID SUM(TCH) DEMO_TABLE 97353 245 DEMO_PK 97357 76 DEMO_PK 97358 76 DEMO_PK 97359 76 DEMO_PK 97360 76
NAME VALUE leaf node splits 213 leaf node 90-10 splits 213

I've distributed my inserts over 4 partitions, having 4 hot blocks instead of one. This is a way to prevent buffer busy waits when having a few concurrent sessions inserting new values.

But the distribution is done on the value coming from the sequence. So each session will touch sequentially each of the 4 hot blocks. Even if this reduces the probablility of contention, it is not optimal. And if you're going in RAC you will see those 'gc' wait events again, with the hot blocks being accessed by all nodes.

The actual solution to the problem is not to distribute the insert based on the value, but having the distribution key based on the session identification. If each session has its own index block to insert into, then all contention is gone.

This is exactly what will be addressed by the 'partitioned sequence' feature that is not (yet) documented.

It generates sequence values in different ranges of value. And that range depends on the session (probably a hash function on the instance number and the session id).
I come back to my non-partitioned no-reverse index, and alter the sequence as:

alter sequence DEMO_SEQUENCE partition 4;

And here is the stats about index blocks: OBJECT_NAME DATA_OBJECT_ID SUM(TCH) DEMO_TABLE 97361 397 DEMO_PK 97364 404
NAME VALUE leaf node splits 351 leaf node 90-10 splits 351

First, you see that my table is larger. This is because the sequence number from the partitioned sequence is larger. It is build by prefixing the sequence number with a partition value, and that makes the binary representation larger. The table here have only two columns, but on a real table, the difference will not be so important. The index is bigger, that's a fact. However if you compare it with the reverse index (that has a lot of free space in the blocks) it is much better here. And you can reduce the sequence max value if you want a smaller id. But the very good thing is that the instances and sessions will work on different ranges, avoiding block contention, while keeping the index maintenance optimal in buffer cache.

Here is my sequence:

select sequence_name,min_value,max_value,last_number,partition_count from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE LAST_NUMBER PARTITION_COUNT DEMO_SEQUENCE 1 9999999999999999999999999999 10100000 4

Ok, that feature is really nice, but you can't use it until it is documented in a future release. So you have to do it yourself: concatenate a value hased from the instance/session in front of the number coming from the sequence. Of course, at the Real Performance Tour the undocumented solution was not raised, and the solution presented was prefixing the sequence. Here is just as an example:

insert into DEMO_TABLE 
 select 1e30*mod(to_number(sys_context('userenv','sid')),4)+DEMO_SEQUENCE.nextval,...

and here are the index statistics: OBJECT_NAME DATA_OBJECT_ID SUM(TCH) DEMO_TABLE 97365 351 DEMO_PK 97366 351
NAME VALUE leaf node splits 433 leaf node 90-10 splits 371

So while dreaming about a feature that you may be able to use in the future, you can acheive the same goal if you're ready to change your code. Anyway, achieving scalability, and good performance on high load often requires to touch the code a bit.

For my first blog post at dbi services, I think it could be a good opportunity to begin by discussing around SQL Server 2014, Hekaton memory optimized tables, and hash indexes. When you create a memory optimized table you have to consider the number of buckets that you have to reserve for its associated hash index. It’s an important aspect of configuration because after creating your table, you cannot change the number of buckets without recreating it.

For the SQL Server database administrators world, it is not usual because with other ordinary indexes (clustered, nonclustered indexes) or other special indexes (xml or spatial indexes) this parameter does not exist. Furthermore, Microsoft recommends to reserve a number of buckets equal at least two times the number of distinct values in the table! The first time I read the documentation I asked myself a few questions:

• Why reserve a fixed number of buckets?
• Why round the number of buckets to the power of 2?

In order to find the answers, I first had to understand how a hash index works.

Let's start from a basic definition of a hash table: it is a data structure used to implement an unsorted associative data array that can map a key to a value. To compute this key a hash function is used. The hash result is then stored into an array of buckets as shown to figure 1 (simplified schema).

 

 

Notice that the hashing keys are not sorted. For lookup operations it does not matter, but for range value scans the hash index does not perform well. In the hash table world, the ideal situation is one key for one unique bucket to allow constant time for lookups, but in reality it is not the case because of hash collisions (two distinct pieces of data can have the same hash value). However, the hash function should be efficient enough to spread keys into buckets uniformly to avoid this phenomenon as much as possible.

There are several strategies to resolve hash collisions and the lack of buckets. SQL Server uses a method that consists of a separated chaining. When a hash collision occurs or when the number of buckets is not sufficient, the row data is added to the row chain for the selecting bucket. In consequence, the cost of the table operation is that of scanning the number of entries in the row chain of the selecting bucket for the concerned index key. If the distribution is sufficiently uniform, the average cost of a lookup depends only on the average number of keys per bucket also called the load factor. The load factor is the number of total entries divided by the number of buckets. The larger the load factor, the more the hash table will be slow. It means for a fixed number of buckets the time for lookups will grow with the number of entries.

 

 

Now let’s start with a practical example of a memory-optimized table :

 

CREATE TABLE [dbo].[HekatonTableDurable] (        [c1] [int] NOT NULL,        [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL    CONSTRAINT [pk_HekatonTableDurable] PRIMARY KEY NONCLUSTERED HASH        (              [c1]        ) WITH ( BUCKET_COUNT = 1000) ) WITH (        MEMORY_OPTIMIZED = ON ,        DURABILITY = SCHEMA_AND_DATA ) GO

 

We create a simple memory optimized table with two columns and 1000 buckets to store the hash index entries. After creating this table, we can see the number of buckets allocated and we notice the final number is not the same, but rounded to the nearest power of two.

 

SELECT        OBJECT_NAME(object_id) AS table_name,        name AS index_name,        index_id,        type_desc,        [bucket_count] FROM sys.hash_indexes GO

 

 

This fact is very interesting because using a power of two to determine the position of the bucket pointer in the array is very efficient. Generally, with hash tables the hash function process is done in two steps to determine the index in the buckets array:

• hash value = hash function (key)
• position in the array = hash value % array size

The modulo operator in the case could be very expensive and can fortunately be replaced by a bitwise AND operator (because the array size is a power of two) which reduces the operation to masking and improves speed. To verify, we can do a simple test with a console application in C#:

 

using System; using System.Diagnostics;   namespace ConsoleApplication1 {    class Program    {        static void Main(string[] args)        {            int i = 0;            int result = 0;            int maxloop = 1000000000;              Stopwatch timer = new Stopwatch();              // Modulo operator            timer.Start();              while (i < maxloop)            {                result = modulo_operation(i, 4);                               i++;            }              timer.Stop();              Console.WriteLine("With modulo : (ms) ");            Console.WriteLine(timer.ElapsedMilliseconds);              i = 0;              // Bitwise AND            timer.Restart();              while (i < maxloop)            {                result = bitwiseand(i, 4);                  i++;            }              timer.Stop();              Console.WriteLine("With bitwise and : (ms)");            Console.WriteLine(timer.ElapsedMilliseconds);              Console.ReadLine();        }          // modulo        public static int modulo_operation (int x, int y)        {            return x % y;        }          // bitwise        public static int bitwiseand (int x, int y)        {            return (x & (y -1));        }    } }

 

By executing this console application on my dev environment (Intel Core i7-3610QM 2.30 GHz), we can notice that the bitwise and function performs faster than the modulo function for an iteration of 1 billion:

 

 

Then we can play with different combination of data rows and hash index buckets to understand why the number of bucket is very important.

Test 1: Filling up the table with 500 distinct rows and 1024 buckets

declare @i int = 0;   while @i < 500 begin        insert HekatonTableDurable values (@i, 'test_' + cast(@i as varchar(50)));        set @i += 1; end

 

By using the DMV sys.dm_db_xtp_hash_index_stats we can see useful information about buckets usage.

 

 

We retrieve the total number of buckets allocated for the index hash equal to 1024. Furthermore, we notice that we have 545 empty buckets and in consequence 1024 – 545 = 479 buckets in use. Normally, with a perfect distribution of key values in the bucket array, we should have 500 buckets in use, but we notice that we certainly already have some hash collisions. We can verify this in the value of the column max_chain_length which tells us there is at least one bucket with a row chain length of 2. In other words, we have at least one bucket that contains two rows with the same index hash. Notice that the load factor is equal to 500 / 1024 = 0.49

Test 2: Filling up the memory-optimized table with a load factor of 3. We will insert 3072 rows for a total of bucket count equal to 1024

 

 

As expected, all buckets are used (empty_bucket_count = 0). We have an average row chain length of 3 (avg_chain_length = 3) in the bucket array equal to the load factor. Some of the rows chain lengths are equal to five, certainly due to a hash collision (max_chain_length = 5).

Test 3: Filling up the memory-optimized table with duplicate rows

 

Before filling up the table, some changes in the schema are required. Let's create a second non-unique hash index (ix_OrderDate):

 

IF OBJECT_ID('dbo.HekatonTableDurable') IS NOT NULL        DROP TABLE [dbo].[HekatonTableDurable]; GO   CREATE TABLE [dbo].[HekatonTableDurable] (        [c1] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1024) ,        [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=1024) ) WITH (        MEMORY_OPTIMIZED = ON ,        DURABILITY = SCHEMA_AND_DATA ) GO

 

Then we can fill up the table with 500 rows that contain some duplicate rows:

 

declare @i int = 0;   while @i < 500 begin        insert dbo.HekatonTableDurable values (@i, case @i % 4 WHEN 0 THEN 'test_' + cast(@i as varchar(50)) ELSE 'test' END);          set @i += 1; end

 

Here is an overview of the number of duplicates rows in the table:

 

select        c2,        count(*) from dbo.HekatonTableDurable group by c2 order by count(*) desc

 

 

We can now check how the buckets are used for the two hash indexes:

 

select        object_name(i.object_id) as table_name,        i.name as index_name,        his.total_bucket_count,        his.empty_bucket_count,        his.avg_chain_length,        his.max_chain_length from sys.dm_db_xtp_hash_index_stats as his        join sys.hash_indexes as i              on his.index_id = i.index_id                     and his.object_id = i.object_id where i.object_id = object_id('dbo.HekatonTableDurable')

 

We retrieve the same number of used buckets for the primary hash index key (545) as first tests. This is because the hash function used by SQL Server is deterministic. The same index key is always mapped to the same bucket in the array. The index key is relatively well distributed. However, we can see a different result for the second hash index. We only have 119 buckets in use to store 500 rows. Furthermore, we notice that the maximum row chain length is very high and corresponds in fact to the number of duplicates values we inserted (375).

Test 4: Filling up the table with an insufficient number of buckets to uniformly store the hash indexes

 

For this last test, we will use 10 buckets to store 8192 distinct rows (a load factor = 819).

 

IF OBJECT_ID('dbo.HekatonTableDurable') IS NOT NULL        DROP TABLE [dbo].[HekatonTableDurable]; GO     CREATE TABLE [dbo].[HekatonTableDurable] (        [c1] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 10) ,        [c2] [char](100) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=10) ) WITH (        MEMORY_OPTIMIZED = ON ,        DURABILITY = SCHEMA_AND_DATA ) GO

 

 

In this case, only one bucket is used (no empty buckets) and the value of the avg_chain_length and the max_chain_length column are pretty close. This is an indication of a lack of buckets with a big row chain.

In this post, we have seen the basic concepts of the hash table, the importance of a correct configuration of the buckets and how to use the dynamic management view sys.dm_db_xtp_index_hash_stats to get useful statistics for hash indexes.

Delayed Transaction Durability is a new SQL Server 2014 feature that was not included in SQl Server 2014 CTP1 but discreetly published in CTP2. It is a very interesting functionality that helps reducing the IO contention for transaction log writes.

In other databases engine, you will find equivalents such as:  

• Oracle: 'COMMIT WRITE BATCH WAIT|NOWAIT' (link here)
• MySQL: 'group_commit' since version 4.x (link here)
• PostgreSQL: 'group_commit' introduce in version 9.2 (link here)

Je viens d'assister au Webinar Zero Downtime Migration pour Oracle, présenté par Chris Lawless qui est récemment passé de Product Manager Golden Gate à Product Manager Dbvisit.  Je vais détailler ici un point très important évoqué par Chris Lawless. La migration par réplication n'est pas seulement envisagée pour éviter un arrêt de service. Arrêt nécéssaire lors de la migration proprement dite (qui peut aller de quelques minutes à plusieurs heures en fonction de la taille et de la complexité de la solution) mais aussi pour les test avant la décision de Go-NoGo de réouverture du service sur la cible.

 

Migrer par réplication

Une migration par réplication, c'est d'abord pour éviter du stress, des coûts et du risque.

Un upgrade classique (dbua par exemple) a l'avantage de garantir l'intégrité des données sans avoir à impliquer les équipes applicatives: pas besoin d'avoir la liste des schemas, des synonymes ou db links, ni de vérifier s'il y a des opérations particulières (nologging) ou des types de données particuliers (XMLTYPE). Pas besoin de se poser des questions sur les triggers, les delete on cascade, etc.

Bien sûr les equipes applicatives seront impliquées pour les tests. Mais lors de la migration le DBA peut s'en occuper sans savoir ce qu'il y a dans sa base. C'est la raison pour laquelle c'est la méthode la plus utilisée. Il n'y a que lorsque la durée de l'arrêt de service peut poser un problème qu'on doit envisager une autre solution. Car un upgrade classique peut prendre une heure (suivant la taille du dictionnaire).

Une migration (lorsque il n'y a pas que la version d'Oracle qui change, mais le stockage, le serveur, la plateforme) peut être plus longue. Des solutions existent pour diminuer cette durée (Transportable Tablespaces, par exemple) mais bien sûr, l'opération sera plus complexe. Et plus complexe veut dire plus de temps, plus de risque, plus de stress.

Alors vient l'idée de migrer par réplication, sans arrêt de service, sans risque, sans stress.

Là, le DBA ne peut pas le faire à l'aveugle. C'est un projet à mettre en place avec les equipes applicatives pour déterminer quoi et quand répliquer. C'est d'ailleurs à mon avis un avantage: la migration est aussi une bonne occasion de faire un peu de ménage. Et pourquoi pas en profiter pour passer en UTF-8 aussi ?

Et il est de toute façon assez facile de tester si notre application supporte la réplication logique. Car bien sûr, le but n'est pas de passer 2 mois à résoudre des problèmes de réplication sur des triggers, contraintes on cascade, vues matérialisées, types de colonnes non supportés, etc.

Alors, pourquoi moins de risque et moins de stress ? Parce qu'on peut mettre en place la réplication sans déranger la production, et la laisser tourner plusieurs jours voir semaines. On ne va décider de basculer l'application sur la cible que lorsque tout est prêt et validé.

La migration se passe en 3 phases:

• Mise en place de la réplication.
  Ne nécessite pas d'arrêt de service, seulement un bref verrou TM Share sur les tables pour s'assurer qu'il n'y a pas de données non committées au point de départ de la copie des données. On aura bien sûr préparé toute la configuration avant sur un environnement de test.
  Une fois le verrou posé et relâché, les données sont copiées puis la réplication s'applique.

• Validation de la cible.
  Tout le temps nécessaire pour tester l'application sur l'environnement cible, avec des vraies données de production. On peut comparer les deux environnements pour vérifier la non regression. On peut valider les performances puisque la charge réelle de production arrive sur la cible. On peut même déjà y connecter les applications de production qui travaillent en read-only: reporting, exports,...
  Quand à la source, l'impact sur la production est très faible puisqu'il s'agit seulement de lire le redo généré, et ça peut être fait sur un autre serveur.
  Il n'y a pas de deadline pour ces tests autre que la date souhaitée de migration. C'est la grosse différence avec une migration classique où la durée des tests - avant de réouvrir le service - doit être à la fois rapide et fiable.

• Basculement de la prod.
  Les applications vont maintenant se connecter à la cible. On va le faire en même temps pour que tout le monde voit toutes les mises à jour. Mais de toute façon la réplication est toujours là: aucune donnée ne serait perdue si on en oubliait une. 
  Ceci se fait sans stress vu que tout l'environnement a été validé avant, pendant plusieurs jours, sur de la production normale. C'est un arrêt de quelques secondes, le temps que l'application se reconnecte. Et là pas de surprise: l'environnement cible a été validé et optimisé bien avant.

 

Licence temporaire et support 12c

Chris Lawless a aussi répondu à deux questions sur les licences et sur le support de la 12c

Au niveau du coût, un produit comme Dbvisit Replicate, qui a déjà un prix de licence tout à fait correct, peut aussi se louer pour une durée limitée si on le souhaite: par exemple pour les 3 mois du projet de migration. Et la version trial de 30 jours peut même permettre de faire un Proof Of Concept sans frais afin de valider la compatibilité de l'application avec la réplication logique. 

Alors pourquoi ne pas passer 1 jour à tester la réplication de notre application ? Dbvisit Replicate peut s'installer en une heure et se configurer rapidement pour répliquer un schema en temps réel. Ce qui permettra d'évaluer la charge de configuration en fonction du nombre d'exceptions à gérer.

Oracle 12c étant une cible supportée, c'est une solution envisageable en venant de n'importe quelle version supérieure à 9.2. Une cible Pluggable Database n'est pas encore supportée, mais de toute façon, Dbvisit s'adresse surtout à des coûts réduits, en Standard Edition. 

 

Quand migrer en 12c ?

Alors, pour apaiser la peur de passer en 12c (peur historique des premières releases d'une nouvelle version), pourquoi ne pas répliquer sa prod 10g ou 11g pendant un mois (version trial) et tester tranquillement son appli sur la cible 12c avec des données de prod ? 

In-Memory OLTP is one of the best new functionalities of SQL Server 2014 - in the same way as AlwaysOn with SQL Server 2012. Indeed, In-Memory OLTP is fully integrated with AlwaysOn and also with AlwaysOn Availability Group. During a test of AlwaysOn Availability Group under SQL Server 2014 CTP2, I discovered that a Readable Secondary Replica was not able to access the In-Memory OLTP objects. I have a Readable Secondary Replica named pc1 with a database containing two tables: one disk table and one In-Memory table.

 

 

When I try to query the disk table to discover how many rows it contains, I receive the following result:

 

 

But if I try to access my In-Memory table, it fails...

 

 

Apparently, it is impossible to access In-Memory objects from a Readable Secondary replica.

Bad news! The Readable Secondary feature is very interesting for reporting and if we cannot query all objects from our database, it is really restricted.

After some research I found the solution. A trace flag will solve this issue. To enable it, you have two solutions, as usual:

 

Via the SQL Server Configuration Manager

• Open it
• Navigate to SQL Server Services
• Right click on your SQL Server instance name and select Properties
• In pane "Startup Parameters", specify a startup parameter, here: -T9989
• Click the Add button and Apply
• You will have to restart your SQL Server Services

 

Via script

• Open a new query screen
• Type: DBCC TRACEON(9989,-1) to enable the trace flag for the complete instance
• Type: DBCC TRACEON(9989) to enable the trace flag for the current session
• Execute the script

 

Now, our new trace flag is enabled and we are able to query the Memory Optimized Tables and also use the Natively Compiled Stored Procedure.

 

 

Hopefully there is a way to bypass this restriction and use the full functionality of Readable Secondary.

I will try to go through all new available trace flags for SQL Server 2014 in my following blog postings ;-)

sqlplus / as sysdba
 oradebug setmypid
 oradebug unlimit
 oradebug hanganalyze 3
 oradebug dump ashdumpseconds 30
 oradebug systemstate 266
 oradebug tracefile_name

Your Oracle database - production DB, of course - is hanging. All users are blocked. You quickly check the obvious suspects (archivelog destination full, system swapping, etc.) but it's something else. Even you, the Oracle DBA, cannot do anything: any select is hanging. And maybe you're even not able to connect with a simple 'sqlplus / as sysdba'.

What do you do ? There may be several ways to investigate deeper (strace or truss for example) but it will take time. And your boss is clear: the only important thing is to get the production running again as soon as possible. No time to investigate. SHUTDOWN ABORT and restart.

Ok, but now that everything is back to normal, your boss rules has changed: the system was down for 15 minutes. We have to provide an explanation. Root Cause Analysis.

But how will you investigate now ? You have restarted everything, so all V$ information is gone. You have Diagnostic Pack ? But the system was hanged: no ASH information went to disk. You can open an SR but what information will you give?

Hang Analyze

The next time it happens, you need to have a way to get some information that can be analyzed post mortem. But you need to be able to do that very quickly just before your boss shouts 'shutdown abort now'. And this is why I've put it at the begining of the post, so that you can find it quickly if you need it...

That takes only a few seconds to generate all post-mortem necessary information. If you can take 1 more minute, you will even be able to read the first lines of hanganalyze output, and you will be able to identify a true hanging situation and maybe just kill the root of the blocking sessions instead of a merciless restart.

In order to show you the kind of output you get, I've run a few jobs locking the same resources (TM locks) - which is not a true hanging situation because the blocking session can resolve the situation.

Here is the first lines from the oradebug hanganalyze:

Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'PL/SQL lock timer'<='enq: TM - contention'<='enq: TM - contention'
     Chain 1 Signature Hash: 0x3b645f57
 [b] Chain 2 Signature: 'PL/SQL lock timer'<='enq: TM - contention'<='enq: TM - contention'
     Chain 2 Signature Hash: 0x3b645f57
 [c] Chain 3 Signature: 'PL/SQL lock timer'<='enq: TM - contention'<='enq: TM - contention'
     Chain 3 Signature Hash: 0x3b645f57

You know what is at the top of the blocking chain. Here the first job that locked the table was idle, using dbms_lock.sleep.
And just below you have all information about the sessions involved:

Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (demo.demo)
                   os id: 7929
              process id: 42, oracle@vboxora12c (J002)
              session id: 23
        session serial #: 7
    }
    is waiting for 'enq: TM - contention' with wait info:
    {
                      p1: 'name|mode'=0x544d0003
                      p2: 'object #'=0x1737c
                      p3: 'table/partition'=0x0
            time in wait: 3.142454 sec
           timeout after: 36.857546 sec
                 wait id: 10
                blocking: 0 sessions
            wait history:
              * time between current wait and wait #1: 0.027475 sec
              1.       event: 'jobq slave wait'
                 time waited: 0.459162 sec
                     wait id: 9
              * time between wait #1 and #2: 0.000026 sec
              2.       event: 'jobq slave wait'
                 time waited: 0.500681 sec
                     wait id: 8
              * time between wait #2 and #3: 0.000030 sec
              3.       event: 'jobq slave wait'
                 time waited: 0.500928 sec
                     wait id: 7
    }
    and is blocked by [...]

System State That was very useful information. We can have more (more process dumps) with higher level. But if we have a serious situation where we will restart the database, It's better to get a systemstate dump with level 266 - so that you have something comprehensive to provide to Oracle Support.
Systemstate has all information about System Objects (sessions, processes, ...) but you have to navigate into it in order to understand the wait chain. In my example:

SO: 0x914ada70, type: 4, owner: 0x91990478, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x91990478, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
(session) sid: 23 ser: 7 trans: 0x8ea8e3e8, creator: 0x91990478
...
service name: SYS$USERS
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 7929
machine: vboxora12c program: oracle@vboxora12c (J002)
Current Wait Stack:
 0: waiting for 'enq: TM - contention'
    name|mode=0x544d0003, object #=0x1737c, table/partition=0x0
    wait_id=10 seq_num=11 snap_id=1
    wait times: snap=15.991474 sec, exc=15.991474 sec, total=15.991474 sec
    wait times: max=40.000000 sec, heur=15.991474 sec
    wait counts: calls=6 os=6
    in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
  inst: 1, sid: 254, ser: 5
Dumping final blocker:
  inst: 1, sid: 256, ser: 5

This is a session that is waiting, and we have the final blocker: inst: 1, sid: 256, ser: 5

Then we get to the final blocker by searching the sid: 256:

SO: 0x9168a408, type: 4, owner: 0x9198d058, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
 proc=0x9198d058, name=session, file=ksu.h LINE:13580, pg=0 conuid=0
(session) sid: 256 ser: 5 trans: 0x8ea6b618, creator: 0x9198d058
...
service name: SYS$USERS
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 7925
machine: vboxora12c program: oracle@vboxora12c (J000)
Current Wait Stack:
 0: waiting for 'PL/SQL lock timer'
    duration=0x0, =0x0, =0x0
    wait_id=0 seq_num=1 snap_id=1
    wait times: snap=25.936165 sec, exc=25.936165 sec, total=25.936165 sec
    wait times: max=50.000000 sec, heur=25.936165 sec
    wait counts: calls=1 os=9
    in_wait=1 iflags=0x5a0
There are 5 sessions blocked by this session.
Dumping one waiter:
  inst: 1, sid: 254, ser: 5
  wait event: 'enq: TM - contention'
    p1: 'name|mode'=0x544d0004
    p2: 'object #'=0x1737c
    p3: 'table/partition'=0x0
  row_wait_obj#: 95100, block#: 0, row#: 0, file# 0
  min_blocked_time: 19 secs, waiter_cache_ver: 44

Analysing the System State takes much longer than the hanganalyze, but it has more information.

V$WAIT_CHAINS

When the blocking situation is not so desesperate, but you just want to see what is blocking, the hanganalyze information is also available online in V$WAIT_CHAINS. The advantage over ASH is that you see all processes (not only foreground, not only active ones).

Here is an example:

CHAIN_ID CHAIN CHAIN_SIGNATURE INSTANCE OSID PID SID BLOCK 1 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7929 42 23 TRUE 1 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7927 41 254 TRUE 1 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7925 39 256 FALSE 2 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7933 46 25 TRUE 3 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7931 45 260 TRUE 4 FALSE 'PL/SQL lock timer' <='enq: TM - contention' <='enq: TM - contention' 1 7935 47 262 TRUE ASH Dump

There is something else that you can get if you have Diagnostic Pack. The ASH information can be dumped to trace file even if it cannot be collected in the database.

oradebug dump ashdumpseconds 30

that will gather ASH from latest 30 seconds, and the trace file will even have the sqlldr ctl file to load it in an ASH like table.

sqlplus -prelim

But what can you do if you can't even connect / as sysdba ? There is the 'preliminary connection' that does not create a session: sqlplus -prelim / as sysdba
With that you will be able to get a systemstate. You will be able to get a ashdump. But unfortunately, since 11.2.0.2 you cannot get a hanganalyze:

ERROR: Can not perform hang analysis dump without a process state object and a session state object.

But there is a workaround for that (from Tanel Poders's blog): try to use a session that is already connected.
For exemple I use the DIAG background process (it's better not to use vital processes for that)

SQL> oradebug setorapname diag
Oracle pid: 8, Unix process pid: 7805, image: oracle@vboxora12c (DIAG)

Core message Even in hurry,

• Always check an hanganalyze to understand the problem.
• Always get a systemstate before a shutdown abort.

and you will have information to investigate later, or to provide to Oracle Support.

I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting "SQL Server 2014 - New Features: xVelocity memory optimized columnstore index" explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them.

Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That's good practice! But did you ever check that it works as expected?

What I mean is this:

• The archived logs that you don't need are reclaimable by the FRA when space is needed
• And the archived logs that are required for availability (standby or backup) are not deleted.

It's not an easy thing to check because Oracle doesn't show which archive log is reclaimable. Only the total reclaimable space is shown in v$recovery_area_usage. But that is not sufficient to validate which archivelog sequence is concerned. I'll show you below a query that returns the reclaimable status from the archived logs. And you will see that until 12c the APPLIED ON ALL STANDBY does not work as expected. You've probably seen a FRA full at standby site and solved it by deleting archived logs. But this is not the right solution because the FRA is supposed to do that.

Let's look at an example I encountered recently. The archivelog deletion policy is set correctly:

 

RMAN> show archivelog deletion policy;

RMAN configuration parameters for database with db_unique_name DATABASE_SITE2 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

 

This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations. Note that it works if I manually do a 'delete archivelog all;' but I expect that the archivelogs in the FRA becomes reclaimable automatically.

Unfortunately, this is not the case and the FRA is growing:

 


SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG                      61.11                     43.02             467

 

Let's check everything. We are on the standby database:

 


SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

 

The archivelogs are going to the Fast Recovery Area:

 


SQL> show parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST, valid_for=(ALL_LOGFILES,
                                                  ALL_ROLES)


 

All archived logs are applied (we are in SYNC AFFIRM):

 


DGMGRL> show database 'DATABASE_SITE2';

Database - DATABASE_SITE2

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    DATABASE

Database Status:
SUCCESS

 

Well, with that configuration, I expect that all archivelogs are reclaimable - except the current one.

Let's investigate. V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.

So I'll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:

 


SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
           ,count(*),min(sequence#),max(sequence#)
     from v$archived_log left outer join sys.x$kccagf using(recid) 
     where is_recovery_dest_file='YES' and name is not null
     group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/

APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES       NO      YES                429           5938           6366
YES       NO      NO                  37           6367           6403
IN-MEMORY NO      NO                   1           6404           6404

 

The problem is there: Because of a bug (Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA) the archivelogs are not marked as reclaimable when the database is in mount mode.

The workaround is to execute dbms_backup_restore.refreshagedfiles. This is what must be scheduled (maybe daily) on the standby. It can be a good idea to do it at the same time as a daily 'delete obsolete', so here is the way to call it from RMAN:

RMAN> sql "begin dbms_backup_restore.refreshagedfiles; end;";

But I've found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag - even when there is no change.

Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is.

It's different for the database where you do the backup, because you want to be sure that the backup is done before an archivelog is deleted: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At dbi services, we advise to keep the same configuration on all Data Guard sites for the same database so that a switchover can be done without any problem. For this reason, having a script that depends on the place where the backups are done is a better alternative than a configuration that depends on the database role.

Finally, here is the state of our reclaimable archivelogs after any of these solutions:

 


APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES       NO      YES                466           5938           6403
IN-MEMORY NO      NO                   1           6404           6404

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG                      61.11                     61.09             467

 

All applied archived logs are reclaimable and the FRA will never be full.

You can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy.

Here is the full query I use for that:

 

column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
 ,decode(rectype,11,'YES','NO') reclaimable,count(*)
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time
 ,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/

 

This is the result on primary where the last archivelog backup has run around 21:00


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
NO        YES                1 NO               277 15-jan 17:56 19-jan 09:49      5936     6212
NO        NO                 1 YES              339 19-jan 10:09 22-jan 21:07      6213     6516
NO        NO                 0 NO                33 22-jan 21:27 23-jan 07:57      6517     6549

That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK

And here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
YES       YES                0 NO               746 07-jan 13:27 17-jan 11:17      5320     6065
YES       NO                 0 YES              477 17-jan 11:37 23-jan 05:37      6066     6542
YES       NO                 0 NO                 8 23-jan 05:57 23-jan 08:14      6543     6550
IN-MEMORY NO                 0 NO                 1 23-jan 08:15 23-jan 08:15      6551     6551

This is good for my policy APPLIED ON ALL STANDBY - except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.

 

LOBs (Large OBjects) first appeared in Oracle 7 and were created to store large amount of data such as document files, video, pictures, etc. Today, we can store up to 128 TB of data in a LOB, depending on the DB_BLOCK_SIZE parameter settings. In this posting, I will show you how to load LOB files into the Oracle database and will present a way to identify the file type of the LOB stored in the database, based on the LOB value column only.

Each file type is associated to a "file signature". This signature is composed of several bytes (i. e. 00 01), and allows to uniquely identify a file type (i.e. zip file, png file, etc). The signature corresponds to the first bytes of the file. To identify the file type of a LOB stored in a database, we just have to extract the first bytes of the LOB value and then compare them to a list of known bytes. I have created a PL/SQL script for that purpose.

The first step is to create a table containing LOB files. Here, I will create a simple table to store internal Binary objects (BLOB). The statement used is:

 

CREATE TABLE app_doc (  doc_id NUMBER,  doc_value BLOB);

 

SQL> desc app_doc;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
DOC_ID                                             NUMBER
DOC_VALUE                                          BLOB

 

Now, I want to load a file from the operating file system to the table APP_DOC. The file to load is a picture saved as PNG format. The path is "/oracle/documents/my_pic.png".

First, we must create an Oracle directory in order to access the file:

 

SQL> CREATE OR REPLACE DIRECTORY DIR_DOCUMENTS AS '/oracle/documents';

 

We then use the DBMS_LOB package through a PL/SQL block to load a file in the database:

 

DECLARE
  src_lob  BFILE := BFILENAME('DIR_DOCUMENTS', 'my_pic.png');
  dest_lob BLOB;
BEGIN
  INSERT INTO app_doc VALUES(1, EMPTY_BLOB())
     RETURNING doc_value INTO dest_lob;
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);
  COMMIT;
END;
/

 

The table has now a new record:

 

SQL> select count (*) from app_doc;
  COUNT(*)
----------
         1

 

I have populated the table with 28 files of several types (png, pdf, bmp, etc.) and the table has 28 records. Here is the list of files loaded into the table:

 

oracle@vmtest:/oracle/documents/ [rdbms11203] ls -1
001.png
002.png
003.png
004.png
005.png
006.png
007.png
008.png
009.png
010.png
011.png
012.png
013.png
014.png
015.png
016.png
017.png
018.png
100021667-logo-dbi-services-sa.jpg
dbi_twitter_bigger.jpg
e18294-SecureFiles_and_Large_Objects_Developers_Guide.pdf
e25523-VLDB_and_Partitioning_Guide.pdf
file.zip
Mybmp2.bmp
Mybmp.bmp
Mydoc1.doc
mypdf.pdf
text3.txt

 

Here is an extract of the PL/SQL written to identify the LOB file type:

 

-- Create a temporary table to store known document types
CREATE GLOBAL TEMPORARY TABLE temp_doc_types (id NUMBER, type VARCHAR(5), sign VARCHAR(4)) ON COMMIT DELETE ROWS;

 

-- Populate the temporary table
INSERT INTO temp_doc_types VALUES (1,'jpeg','FFD8');
INSERT INTO temp_doc_types VALUES (2,'gif','4749');
INSERT INTO temp_doc_types VALUES (3,'png','8950');
INSERT INTO temp_doc_types VALUES (4,'bmp','424D');
INSERT INTO temp_doc_types VALUES (5,'pdf','2550');
INSERT INTO temp_doc_types VALUES (6,'doc','D0CF');
INSERT INTO temp_doc_types VALUES (7,'zip','504B');
INSERT INTO temp_doc_types VALUES (8,'rar','5261');

 

In this example, I chose to compare only the four first bytes of the LOB value. The number of bytes composing a file signature can be larger, but to simplify the example, I only used file signatures that have four bytes.

The number of files of each extension is returned by this statement:

 

SQL> select decode(type,NULL,'Unknown document type',type) as "TYPE OF DOCUMENT",
  2  count(*) as "NUMBER OF DOCUMENTS"
  3  from temp_doc_types a,
  4  (select (dbms_lob.substr(doc_value,2,1)) as FILE_TYPE from app_doc) b
  5  where a.sign(+)=b.FILE_TYPE group by a.type;

 

TYPE OF DOCUMENT      NUMBER OF DOCUMENTS
--------------------- -------------------
Unknown document type                   1
doc                                     1
bmp                                     2
png                                    18
pdf                                     3
jpeg                                    2
zip                                     1

 

The statement gets the first four bytes of each LOB and compares them to the temporary table, matching the LOB to a type.

• The decode function is used to display 'Unknown document type' when NULL is returned and no extension matches between the LOB and the temporary table. This is the case for text files which have no file signature.

• The DBMS_LOB.SUBSTR function is used to retrieve only the first four bytes from the LOB value. Parameters are the source LOB value, the amount of bytes to read (4), and the starting position (1).

• An outer join is used to count LOBs not matching the temporary table. This will correspond to unidentified files types.

 

To finish, I drop the temporary table before exiting the job:

 

-- The temporary table is dropped before exit
DROP TABLE temp_doc_types;
exit;

 

And just for the eyes, this is the result in a HTML fashion, generated directly from sqlplus using the MARKUP option and an css style sheet. I let you read the Oracle documentation in order to know how to use the MARKUP option ;-)

 

The concept of Multitenant databases, which was introduced with Oracle 12c in June 2013, allows to run several databases on a single instance. Oracle presents this feature as a good solution for Oracle patching. The reason behind it is that it is now possible to unplug a container database (called PDB) from its original container (called CDB), in order to plug it into a new local or remote Container with a higher level of PSU. In this post, I will show how you can install the new PSU 12.1.0.1.1 for Oracle 12c (released in October 2013) using Multitenant databases to keep the downtime as low as possible.

The following schema shows the concept:

 

 

We assume that a company is using a Container database CDB1 with two pluggable databases PDB1 and PDB2 running on it:

 

SQL> select name, open_mode from v$pdbs;

 

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE

 

These two databases are accessed by users and applications and a window maintenance is planned in order to install the latest PSU for Oracle 12c.

Using the OPatch utility, we can see the currently installed patches:

 

$ opatch lsinventory

 

 

We can see that the patch 16527374 is already installed. It has been installed in order to fix a bug with Enterprise Manager Express 12c and Multitenant databases. Also note the presence of the latest OPatch utility 12.1.0.1.2.

To unplug PDB2 from the container CDB1 and plug it into a new CDB with a higher PSU level, we need to install a second rdbms software with the same patching level (12.1.0.1.0 + EM Express bug fix), on which the new PSU will be installed.

An empty container database CDB2 has been created on the second environment, with the Oracle Home /u00/app/oracle/product/12.1.0/db_2. Now it is time to install the new PSU 12.1.0.1.1 on the second environment. The listener for this installation is named LISTENER_DB_2:

 

Step 1: Upgrade OPatch utility to the latest version (see note 6880880) if not already done.

 

Step 2: Download and unzip the PSU 12.1.0.1.1 on the server (patch 17027533).

$ unzip p17027533_121010_Linux-x86-64.zip -d /tmp

 

Step 3: Check conflicts between the PSU and already installed patches.

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

 

 

The patch already installed to fix the EM Express bug is in conflict with the new PSU. Oracle will remove the existing patch before installing the new PSU. This is not an issue here, since Oracle provides the patch 16527374 specific to Oracle 12.1.0.1.1. It will be possible to reinstall this patch once the PSU is applied.

 

Step 4: Shut down all databases and listeners running on the Oracle Home of the second environment.

Shut down CDB2:

SQL> shutdown immediate;

 

I did not create any listener for my second environment. If you plan to drop the current ORACLE_HOME after upgrade, you will have to create a new listener in the new ORACLE_HOME and to shut it down before upgrading.

 

Step 5: Install the new PSU.

$ cd /tmp/17027533
$ opatch apply

 

 

Note that the patch for EM Express bug fix has to be reinstalled for 12.1.0.1.1 release, since it has been removed during the PSU install. This bug is not covered by the PSU.

 

Step 6: Restart databases and listeners.

Restart the CDB2 database:

SQL> startup;

 

Step 7: Load modified SQL files into the database with Datapatch tool.

 

$ cd $ORACLE_HOME/OPatch$ ./datapatch -verbose

 

 

It is possible that the following error occurs:

DBD::Oracle::st execute failed: ORA-20001: Latest xml inventory is not loaded into table

 

In this case, the parameter _disable_directory_link_check must be set to TRUE (see Oracle note 1602089.1) and the database must be restarted:

alter system set "_disable_directory_link_check"=TRUE scope=spfile;

 

We have now CDB1 running in 12.1.0.1.0 PSU level, and CDB2 running in 12.1.0.1.1 PSU level. Until now, no downtime occured on PDB1 and PDB2 databases. All configuration and installation steps for the PSU have been performed on a non-productive environment.

The next steps consist in unplugging PDB2 from CDB1, in order to plug it into CDB2.

 

Step 8: Stop the user application and shutdown PDB2 from CDB1.

From this step on, the pluggable database must be closed. The downtime will start now.

 

SQL> connect sys/manager@CDB1 as sysdba
Connected.

 

SQL> ALTER SESSION SET CONTAINER = PDB2;
Session altered.

 

SQL> ALTER PLUGGABLE DATABASE CLOSE;
Pluggable database altered.

 

Step 9: Unplug PDB2 from CDB1.

 

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

 

SQL> ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO '/u01/oradata/CDB1/PDB2/PDB2.xml';
Pluggable database altered.

 

Step 10: Plug PDB2 into CDB2.

 

SQL> connect sys/manager@CDB2 as sysdba
Connected.

 

SQL> CREATE PLUGGABLE DATABASE PDB2
     USING '/u01/oradata/CDB1/PDB2/PDB2.xml'
     MOVE FILE_NAME_CONVERT = ('/u01/oradata/CDB1/PDB2','/u01/oradata/CDB2/PDB2');
Pluggable database created.

 

The use of the MOVE clause makes the new pluggable database creation very quick, since the database files are not copied but only moved on the file system. This operation is immediate if using the same file system.

 

SQL> ALTER SESSION SET CONTAINER = PDB2;
Session altered.

 

SQL> ALTER PLUGGABLE DATABASE OPEN;
Pluggable database altered.

 

The database PDB2 is now opened, and users can access to the database. Note that if installing CDB2 on a different host, users may have to update the TNS connect string.

 

Step 11: Load modified SQL files into the database with Datapatch tool.

Since the rdbms has been upgraded to 12.1.0.1.1 before any pluggable database has been plugged into CDB2, all newly plugged databases must execute the "datapatch" script in order to load the modified SQL files.

Run the following command once the CDB2 environment is set:

 

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

 

 

Important: If you are using static listener registration, do not forget to change your listener.ora in order to provide the true ORACLE_HOME path corresponding to the new environment.

The pluggable database is now fully ready and downtime only occured between steps 8 and 10. By using the MOVE clause, the only downtime corresponds to the time required for shutting down the database from the source CDB and starting the database on the destination CDB. It represents a few seconds... And if both CDB are running on the same host, the users will not have to update their TNS connect string in order to access the database.

Patching PDBs using this method might ease the DBA life in the future :-)

Because people know that I really like Oracle AWR reports, they send them to me from time to time. Here is one I have received for Christmas with the following question: 'Since our AIX server is virtualized, the response times are 4x longer. I feel that we are CPU bound, but cpu usage is mostly idle. Is there something hidden?' It is a question that is frequently raised with virtualized environments. Here, we will see that the system reports the CPU utilization as being only 30% busy. However, despite appearances, the report is coming from a very busy system suffering from CPU starvation. So, yes, something is hidden and we will see how we can reveal it. 

A CPU nowadays is not the physical processor we used to have. The example I have here comes from an IBM LPAR running AIX and I will explain Virtual CPU, Logical CPU and CPU multi-threading. Even if it's not Oracle specific, I'll use the figures from the AWR report which show the statistics gathered from the OS.

The AWR report covers 5 hours of activity where users are experiencing long response times:

 Snap IdSnap TimeSessionsCursors/Session Begin Snap: 27460 20-Dec-13 15:00:07 142 2.8 End Snap: 27465 20-Dec-13 20:00:49 101 2.2 Elapsed:   300.72 (mins)     DB Time:   855.93 (mins)    

Here is the average CPU load gathered from the OS:

Host CPU (CPUs: 20 Cores: 5 Sockets: )

Load Average BeginLoad Average End%User%System%WIO%Idle 9.82 9.26 18.7 12.0 4.4 69.3

 And because the report covers 5 hours we have an hourly detail in 'Operating System Statistics - Detail':

Snap TimeLoad%busy%user%sys%idle%iowait 20-Dec 15:00:07 9.82           20-Dec 16:00:15 12.50 26.67 14.09 12.58 73.33 6.21 20-Dec 17:00:26 15.53 41.47 24.44 17.03 58.53 5.84 20-Dec 18:00:34 6.44 36.91 25.20 11.70 63.09 4.55 20-Dec 19:00:40 6.52 24.39 15.00 9.40 75.61 3.35 20-Dec 20:00:49 9.26 24.34 14.99 9.35 75.66 1.88

The point is that when you look at that you think that the system is more than 50% idle. But that's wrong.

Look at the second line. Being only 26.67% busy with a load of 12.5 running processes would mean that the system is able to run 12.50/0.2667=47 processes concurrently. But that's wrong again: we don't have 47 CPU on the whole system.

Ratios are evil, because they hide the real numbers they come from. Let's see how the %cpu utilization is calculated.

StatisticValueEnd Value BUSY_TIME 11,162,236   IDLE_TIME 25,169,672   IOWAIT_TIME 1,584,726   SYS_TIME 4,360,698   USER_TIME 6,801,538   LOAD 10 9 NUM_CPUS 20   NUM_CPU_CORES 5   NUM_LCPUS 20   NUM_VCPUS 5  

During the 5 hours where the statistics were collected, we had 11,162,236 centiseconds of CPU that were used by running processes,  and 25,169,672 centiseconds of CPU resources were not used. Which supposes that we have in total 11,162,236+25,169,672 centiseconds, and that is about 100 hours of CPU time available. 100 hours of CPU during 5 hours of elapsed time supposes that we have 100/5=20 CPU in the system.

But that's wrong. We cannot run 20 processes concurrently. And the %cpu that is calculated from that is a lie because we cannot reach 100%.

This is virtualization: it lies about the available CPU power. So, do we have to calculate the %cpu from the number of cores that is 5 here ? Let's do it. We used 11,162,236 centiseconds of CPU, that is 31 hours. If we have 5 cpu during 5 hours then the cpu utilization is 31/(5x5)=124%

This is hyper-threading: we have more CPU than the number of cores.

Now it's time to define what is a CPU if you want to understand the meaning of the numbers.

The CPU is the hardware that executes the instructions of a running process. One CPU can cope with one process which is always working on CPU, or with two processes that spend half of their time outside of CPU (waiting for I/O for example), etc.

With multi-core processors, we have a socket that has several CPU cores in it. Cores in a socket can share some resources, but the core is processing the process instructions. So the number of CPU we're interrested in is the number of cores. The OS will calculate the %cpu from the number of cores, and that's the right thing to do. And Oracle uses the number of cores to calculate the license that you have to buy, and that's fair: it's the number of instances of their software that can run concurrently.

Now comes virtualization. In our exemple, we are on an IBM LPAR with 5 virtual CPU (the NUM_VCPU from the V$OSSTAT). What does that mean ? Can we have 5 processes running on CPU efficiently ? Yes if the other LPARs of the same hardware are not too busy. Then our 5 virtual CPU will actually run on 5 physical cores. But if all the LPAR are demanding CPU resources, and there's not enough physical CPU then the physical CPU resources will be shared. We will run on 5 virtual CPU, but those VCPU will be slower than physical ones, because there are shared.

Besides that, when the CPU has to access to RAM, there is a latency where the CPU is waiting before executing the next instruction. So the processor manufacturers introduced the ability to run another thread of process during that time (and without any context switch). This is called hyper-threading by Intel, or Symmetric Multiprocessing (SMP) by IBM. In our exemple we are on POWER7 processors that can do 4-way SMT. So theorically each of our 5 VCPU can run 4 threads: here are the 20 logical CPU that are reported. But we will never be able to run 4x more concurrent processes, so calculating %cpu utilization from that is misleading.

Then, how to get the real picture ? My favorite numbers come from load average and runqueue. 

Look at the line where load average was 15.53 and cpu utilization shows 41.47%. Because the 41.47% was calculated from the 20 CPU hypothesis, we know that we had on average 0.4147*20=8.294 process threads being running on CPU. And then among the 15.53 processes willing to run, 15.53-8.294=7.236 were waiting in the runqueue. When you wait nearly as much as you work you know that you are lacking resources: this is CPU starvation.

So hyperthreading is a nice feature. We can run 8 threads on 5 CPU. But don't be fooled by virtualization or hyper-threading. You should always try to know the physical resources that are behind it, and that usually requires the help of your system admin.

Oracle DBAAS (Database As a Service) offers a good solution for on demand access to software and infrastructure. In this posting, I will describe the DBAAS implementation with Enterprise Manager Cloud 12c.

 

Why Database as a Service is interesting

When a developer team needs a new database, the system administrator has to configure the host, the storage administrator has to configure the storage, and the database administrator has to download the latest oracle database version and install it on a host. These configuration operations last at least two days. If we need to implement a high availability solution and to configure the backup operations correctly, the total implementation time can go up to one week.

All these operations have a cost and as organizations have more and more applications to maintain, the IT directors are starting to have a closer look at data management.

This is why Oracle DBAAS (Database As a Service) can be an interesting solution.

 

Installing Oracle Database as a Service (DBAAS)

First, you have to implement the following plugins, which are required to enable Database as a Service (DbaaS) in EM Cloud 12.1.0.3.0:

• Enterprise Manager for Oracle Cloud
• Enterprise Manager for Oracle Virtualization
• Enterprise Manager for Oracle Consolidation Planning and Chargeback
• Enterprise Manager for Oracle Database
• Enterprise Manager for Storage Management

We have to deploy the plugin on the management server:

 

 

 

There will be a downtime during the plugin installation; the OMS server won’t be available during the configuration.

The following command will show us how the deployment is running on the OMS server:

 

oracle@vmtestoraem12c:/u01/app/oracle/MiddleWare_12103/oms/ [oms12c] emctl status oms -detailsOracle Enterprise Manager Cloud Control 12c Release 3WebTier is DownUnable to deploy or undeploy the plug-in. Oracle Management Server is down possibly because the following plug-ins are being deployed or undeployed from it. Try again after some time.Plug-in Deployment/Undeployment StatusPlug-in Name         : Oracle VirtualizationVersion             : 12.1.0.5.0ID                   : oracle.sysman.vtContent             : Plug-inAction               : DeploymentStatus               : DeployingSteps Info:Step                                     Start Time               End Time                 StatusSubmit job for deployment               9/6/13 9:16:34 AM CEST   9/6/13 9:16:34 AM CEST   SuccessInitialize                               9/6/13 9:16:37 AM CEST   9/6/13 9:16:43 AM CEST   SuccessInstall software                         9/6/13 9:16:44 AM CEST   9/6/13 9:16:45 AM CEST   SuccessValidate plug-in home                   9/6/13 9:16:47 AM CEST   9/6/13 9:16:47 AM CEST   SuccessPerform custom preconfiguration         9/6/13 9:16:47 AM CEST   9/6/13 9:16:47 AM CEST   SuccessCheck mandatory patches                 9/6/13 9:16:47 AM CEST   9/6/13 9:16:47 AM CEST   SuccessGenerate metadata SQL                   9/6/13 9:16:47 AM CEST   9/6/13 9:16:47 AM CEST   SuccessPreconfigure Management Repository       9/6/13 9:16:47 AM CEST   9/6/13 9:16:47 AM CEST   SuccessStop management server                   9/6/13 9:16:51 AM CEST   9/6/13 9:17:44 AM CEST   SuccessRegister DLF                             9/6/13 9:17:44 AM CEST   N/A                       RunningConfigure Management Repository         9/6/13 9:17:45 AM CEST  N/A                       RunningConfigure middle tier                   9/6/13 9:17:45 AM CEST   N/A                       Running

 

After a few minutes, the Web Tier and the Oracle Management server are up and running and the needed plugins are correctly deployed.

We also have to install the Cloud plugin in the same way we deployed the previous plugins.

Finally, the setup menu has the new Cloud infrastructure menu:

 

 

We have to create roles and users via the setup security administrators menu with its own Cloud User privileges in the following way:

A user named cloud_admin with the EM_CLOUD_ADMINISTRATOR role allowing it to setup and deploy the cloud.

 

A user named dbaas_administrator who has the em_ssa_administrator role to define quotas and constraints for the self-service user and grant them access privileges:

 

And finally, a user named dbaas_exploit will be the self-service portal user:

 

 

Once all the plugins are installed, the first operation consists in creating a PaaS infrastructure zone. A Paas (Paltform As A Service) zone is a group of hosts discovered in Enterprise Manager Cloud 12c.

Connected as cloud_admin. From the Enterprise menu, Middleware and database Cloud, we select Create Paas Infrastructure Zone:

 

 

We define a development zone with members of type host and we define the maximum CPU and memory utilization:

 

 

We define credentials and we add hosts:

 

 

We select the roles that can access this Paas zone:

 

 

Finally, the Pass infrastructure zone is correctly created:

 

 

In the same way, we can create a Production zone made of production hosts:

 

 

A database pool is a set of resources that can be used to provision a database instance in a Paas infrastructure zone. We can define pools for databases and pool for schemas.

A pool for databases is a collection of servers with database software installed.

A pool for schemas is a collection of databases containing schemas.

From a practical point of view, the database administrator will create provisioning profiles which will be stored in the software library. A provisioning profile can be an Oracle database with or without the software, or can an extract of a database. Provisioning profiles can be created using rman backups, dbca templates, snapshots, or expdp dumps.

In the following example, I will show you how to create a database pool with a rman backup and a schema pool.

Connected as DBAAS_ADMINISTRATOR, we can create a database pool as follows:

From the setup menu, we select Cloud and Database:

 

 

We fill in the pool name, the credentials, the PaaS zone, the platform version, and the oracle software version.

You should then immediately test the credentials:

 

 

Concerning the "Request Settings", I set everything on "No restriction":

 

 

I define the following quotas: 4 Gb memory, 100 Gb storage, and 10 schema service requests:

 

 

It’s time now to define a database provisioning profile. In my example, I will chosse Structure only with a database template:

 

 

Or an RMAN backup:

 

 

We submit the Database Provisioning Profile creation; we can view the deployment in the Procedure Activity screen:

 

 

The next page shows that the Profile has been correctly created in the Procedure Activity page:

 

 

The next step consists in creating a service template: We give a name and a description, and choose the database type.

We also have to specify a prefix for the database to be created in the future, as well as a domain name:

 

 

We define the storage type (ASM or File System), administrator Credentials, and select "Next":

 

 

We can also define specific initialization parameters:

 

 

We also have the possibility to define scripts to be run before and after the service instance creation:

 

 

 

Then we select a zone and assign a pool and define a role:

 

 

We can review our settings before submitting:

 

 

We can also define a schema pool. Let's create a new database pool:

 

As before, we set the services requests to no restrictions and allow the same quotas.

Then we create a profile and select export Schema definition:

 

 

We select the schema name and the directory location:

 

 

Let's review our settings before submitting:

 

 

Then we create a service template that will be available in the self-service portal:

 

We have the possibility to define the schemas details:

 

We select the zone and the resource pool the service template will provision:

 

 

We define the roles authorized to use this service template:

 

 

We review our settings:

 

 

Finally we have created two service templates: one for creating a 11.2.0.3 database and another containing a schema dump.

 

 

Notice that the provisioning profiles can be seen in the software library:

 

 

Now we have to access the self-service portal as a normal user. We will use the dbaas_exploit user and the Schema as a service.

From the Enterprise Menu, we select the Cloud and Self Service Portal:

 

 

 

Select My Database, and in the Request tab, select schema:

 

 

We select the Service Template named HR:

 

 

The original schema name HR will be created in the target database with the new name DBAAS_EXPLOIT_HR.

We fill in the password for this new schema and select submit:

 

 

The next screen shows the provisioning screen:

 

 

By selecting DBAAS_CREATE#63, we can follow the deployment:

 

 

In the target database, the dbass_exploit_hr schema is created and the data is successfully imported:

 

SQL> select username from dba_users where username like 'DBAAS%';

DBAAS_EXPLOIT_HR

DBAAS_EXPLOIT

DBAAS_ADMINISTRATOR

 

This way, non-privileged users have the possibility to acces specific Enterprise Manager Cloud 12c interface allowing them to use resources such as create database or import schemas.

The provisioning process creates a database service that is completely manageable by Oracle Enterprise Manager Cloud 12c:

Connected as DBAAS_ADMINISTRATOR.

 

Select Middleware and Database Home:

 

A new service template has been defined:

 

 

Conclusion

Database as a service presents a lot of advantages for the managing of huge infrastructures:

• The DBAs can define a catalog in the software library containing multiple Oracle Software versions, different kind of database instances (RAC or Cluster instances), or a list of schema dumps that will be available to end-users for deployment.
• Even if the DBAAS configuration and usage can be a hard and long work, in the end, it is a very efficient way to optimize and standardize the IT environment.
• Adding the DBAAS functionality speeds up the application deployment - and, as a result, enables significant cost savings.
• Obviously, the Oracle Cloud Management Pack and the Database Lifecycle Management Pack are necessary to use those features.

While exploring SQL Server 2014, I was surprised by the powerful Power Query feature. In the recent past, converting an internet table to Excel was a nasty work leading to format problems, implicit conversions, etc. With Power Query, importing tables from the internet is very easy!

Some customers or colleagues recently asked me about the SQL Server concept xVelocity and in particular the SQL Server 2014 feature new xVelocity memory optimized columnstore index. So I decided to write a few words on the dbi services blog.

The choice of the correct character set before you create an Oracle 12c database is an important decision. You have to know the type of data you have to store and the languages needed by the Oracle database. Oracle recommends to choose Unicode for its universality and compatibility. It supports all written languages and most of the technical and scientific symbols.

Before Oracle 12c changing the database character set was an pretty nasty task and the migration itself could cause incorrect data conversion or loss of data. The csscan utility was not very pleasant to use and there was no GUI at all.

Oracle 12c has introduced a new tool called DMU (Database Migration Assistant for Unicode).

The main advantages of the DMU are:

• GUI oriented tool guiding you through the workflow of the migration

• allows selective conversion of data (at the table, column and row level)

• gives you indication to help you in case of problems (failures during the scanning phase for example)

• possibility to see the data itself in the DMU GUI

• supports inline conversion of database contents

By the way, there is always the restriction about the data dictionary if there is any convertible data in it.

I used the DMU tool to migrate a database in we8mswin1252 Oracle 12c database to AL32UTF8 character set.

Under $ORACLE_HOME/dmu you just run dmu.sh and the GUI tool is running, you connect to the database you want to migrate. The first thing you have to do is to configure a DMU repository otherwise you have the following error message and the help tool tells you what to do:

 

 

Then, we configure the repository:

 

 

We select the target database character set:

 

And we select the tablespace where the repository will be installed.

Once the DMU correctly installed and configured we have to scan the objects in the database:

 

The scan wizard gives you a lot of possibilities (modifying the number of scanning processes, selecting tables …):

 

We have the possibility to edit the table plan conversion details:

The conversion steps are displayed:

We can select "convert" to run the character migration and finally, the database conversion is successful:

 

Conclusion

The new Oracle 12c DMU is an interesting tool. Its graphical interface is very useful and will largely facilitate the character set migration of a database.

Among the Oracle 12c New Features, there is the addition of an uninstall tool encapsulated in the Oracle database binaries. You do not need to download the deinstallation tool for Oracle products anymore (deinstall utility). This blog posting features an overview of this tool and its functionnalities.

 

Uninstalling Oracle 12c products

There are two possibilities if you want to uninstall Oracle 12c products (Oracle database, Oracle Grid infrastructure or Oracle client):

1. Running the deinstallation tool with the runInstaller command using the parameters –desinstall and –home from ORACLE_HOME/oui/bin/ 
2. Using the deinstallation tool which is also available as a separate command in the ORACLE_HOME deinstall directory after the installation of the Oracle product. It is located in ORACLE_HOME/deinstall. In this posting I am going to speak about the second solution.

This new deinstallation command line tool uses information gathered from the environment and information provided (if necessary) in order to create a parameter file. It is also possible to use the template parameter file located under ORACLE_HOME/deinstall/response. The deinstallation tool automatically stops Oracle software, and removes Oracle software and configuration files on the operating system.

As the Oracle documentation explains in the documentation, it is important to take into consideration the following:

"If the oraInventory contains no other registrered home besides the home that you are deconfiguring and removing, then the deinstall command removes the following files and directory contents in the Oracle base directory of the Oracle Database installation owner

• admin
• cfgtoollogs
• checkpoints
• diag
• oradata
• flash_recovery_area

Oracle strongly recommends that you configure your installations using an Optimal Flexible Architecture (OFA) configuration, and that you reserve Oracle base and Oracle home paths for exclusive use of Oracle software. If you have any user data in these locations in the Oracle base that is owned by the user account that owns the Oracle software, then the deinstall command deletes this data."

 
Using the deinstallation tool is a straightforward process. You simply have to execute the deinstall tool from ORACLE_HOME/deinstall directory a demonstrated below.

 

Testing the uninstall feature

Let's test this deinstallation tool with the command deinstall from $ORACLE_HOME/deinstall:

 

-bash-3.2$ cd /u00/app/oracle/product/12.1.0/db_0_1/deinstall/
-bash-3.2$ ./deinstall

Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u00/app/oraInventory/logs/



############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################### CHECK OPERATION START #########################

## [START] Install check configuration ##
Checking for existence of the Oracle home location /u00/app/oracle/product/12.1.0/db_0_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u00/app/oracle
Checking for existence of central inventory location /u00/app/oraInventory
Checking for sufficient temp space availability on node(s) : 'vmtestoel01'

## [END] Install check configuration ##


Network Configuration check config START
Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_check2012-07-29_03-00-16-PM.log
Network Configuration check config END

 

Database Check Configuration START
Database de-configuration trace file location: /u00/app/oraInventory/logs/databasedc_check2012-07-29_03-00-17-PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home []:
Database Check Configuration END


 

Oracle Configuration Manager check START
OCM check log file location : /u00/app/oraInventory/logs//ocm_check8060.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u00/app/oracle/product/12.1.0/db_0_1
Inventory Location where the Oracle home registered is: /u00/app/oraInventory
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u00/app/oraInventory/logs/deinstall_deconfig2012-07-29_03-00-14-PM.out'
Any error messages from this session will be written to: '/u00/app/oraInventory/logs/deinstall_deconfig2012-07-29_03-00-14-PM.err'


######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /u00/app/oraInventory/logs/databasedc_clean2012-07-29_03-00-37-PM.log
Network Configuration clean config START
Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_clean2012-07-29_03-00-37-PM.log
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END


 

Oracle Configuration Manager clean START
OCM clean log file location : /u00/app/oraInventory/logs//ocm_clean8060.log
Oracle Configuration Manager clean END


Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u00/app/oracle/product/12.1.0/db_0_1' from the central inventory on the local node :Done
Delete directory '/u00/app/oracle/product/12.1.0/db_0_1' on the local node : Done
The Oracle Base directory '/u00/app/oracle' will not be removed on local node. The directory is not empty.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END

## [START] Oracle install clean
##Clean install operation removing temporary directory '/tmp/deinstall2012-07-29_03-00-00PM' on node 'vmtestoel01'
## [END] Oracle install clean ##

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u00/app/oracle/product/12.1.0/db_0_1' from the central inventory on the local node.
Successfully deleted directory '/u00/app/oracle/product/12.1.0/db_0_1' on the local node.
Oracle Universal Installer cleanup was successful.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############

 

Do not forget to remove the deinstallation script in /tmp.

In my case, the desinstallation directory in /tmp directory used about 500MB of disk space as presented below:

 

-bash-3.2$ du -hs /tmp/deinstall2012-07-29_02-58-14PM/
545M /tmp/deinstall2012-07-29_02-58-14PM/

 

It is possible to specify some options for the desinstallation tool. These options can be listed with the help option such as demonstrated below:

 

oracle@vmtestoel01:/u00/app/oracle/product/12.1.0/db_0_1/deinstall/ [] ./deinstall -help


deinstall
[ -silent ]
[ -checkonly ]
[ -local ]
[ -paramfile ]
[ -params ]
[ -o ]
[ -tmpdir ]
[ -logdir ]
[ -help : Type -help to get more information on each of the above options. ]

 

Conclusion

Having the deinstallation tool encapsulated into the Oracle Installation Media is definitively an added value compared to the previous version. This tool is easy to use and allows configuring a parameter file in order to use it in a batch process. However, I have encountered strange problems with this tool in some specific context, such as demonstrated below. Unsetting the ORACLE_HOME solved the issue in my case.

 

Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_check2012-07-29_02-57-35-PM.log
#
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x00007f923401f933, pid=27863, tid=1087699264
#
# JRE version: 6.0_31-b02
# Java VM: Java HotSpot(TM) 64-Bit Server VM (20.6-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [libclntshcore.so.12.1+0x204933] long+0x33
#
# An error report file with more information is saved as:
# /tmp/deinstall2012-07-29_02-57-21PM/hs_err_pid27863.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#