Oracle … as usual

Oracle by Laurent Leturgez

Category Archives: Uncategorized

DOAG 2016 … a great conference !

From 15th till 18th November, I was at the DOAG Conference in Nurnberg and it was a very great conference and a great opportunity for the Oracle Community.

Last year, I decided to attend (and to present) at different conferences in Europe and these conferences represent a good opportunity to develop your network (mainly technical for me), to learn a lot about technical stuff from worldwide Oracle experts, and to share your experiences.

More, you will share very good moments with people you certainly know by social networking (twitter, blogs etc.).

Let me explain how it was during DOAG and why I enjoyed it.

I arrived at my hotel the day before the conference, I didn’t know Nurnberg and as the conference center was not located in the town center, I feared to be alone in this area and to meet people exclusively during the conference days. But here comes the Oracle community …

Once installed in my room, I went to twitter to view the last news and I saw a tweet from Jan Karremans ( who asked who was in his hotel (Yipiie !!! it’s mine too). So we plan to meet up in the hotel’s lobby in the evening. During this time, we found other people (Martin Widlake @MDWidlake, Neil Chandler @ChandlerDBA, Kamil Stawiarski @ora600pl etc.) that will enjoy to have a beer (or more 😉 ) in a pub just before the Oracle ACE dinner.

So we had a great time with those guys and I have a new opportunity to meet great people at the dinner which was organized by Oracle for its fellow advocates 😉

Day 1 : great conferences and a German Organization for a very nice event. After the conferences, comes the speaker dinner. With different guys, we had a beer (or two 🙂 ) before the dinner but once arrived at the restaurant, this one was full and we cannot join the speakers that came earlier. We were about 20 people and we decided to find a restaurant for our “unspeaker restaurant” event. Kamil found a great place and during this time, I had the pleasure to sit near Martin Widlake and Neil Chandler who made the demonstration of their “soooo” british sense of humor .. we had a lot of fun !! 😉

The last two days were more studious for me, I attended lots of sessions presented by so interesting and famous guys (Kerry Osborne: @KerryOracleGuy, Stefan Koehler: @OracleSK, Mauro Pagano: @Mautro, Franck Pachot: @FranckPachot, Ludovico Caldara: @ludodba etc.). The most interesting one was given by Toon Kooperlars and Bryn Llewellyn about the ThickDB concept. I’m specialized in infrastructure and this session was much oriented to development, it can be surprising but that’s the power of the community and the power to attend this kind of conference … you can discover new fields and develop your technical culture (even if I have some skills in database development 🙂 ).

Last day, I presented my session (about SIMD instructions) and I was very honored to receive some congratulations from Stefan Koehler who is one of the most interesting guy in the Oracle’s internal field (and member of the prestigious “Oak Table” network).

Thanks DOAG and thanks to all the speakers … it was a blast 😉

OTN Appreciation Day: SQL Patch

Feature: SQL Patch.

Why : Because, it can help you to use some hints on a non editable query (for example, in a packaged application). In order to stabilize performance of this kind of application, it’s very useful.

Why not: Because sometimes, having many execution plans for a SQL Statement is better than one.

If you don’t understand why such a short post, or why this post .. please read this:
Many thanks to Tim Hall (@oraclebase) for the Idea 😉


ITL slots high water mark

Recently, one of my customer had some performance issues on a batch ran every night.

After analyzing AWR, I found that the top wait event was “enq: TX – allocate ITL entry” on a particular table.

“enq: TX – allocate ITL entry” wait time is increased when an Interest Transaction List (ITL) can’t grow due to lack of space in the block header. (for more information, see this post from Arup Nanda : If you want to resolve it, you have to increase the INITRANS parameter of the object, and then rebuild it.

Another thing to point out: this wait event is counted by object (in V$SEGMENT_STATISTICS for example), but ITL allocation issues are specific to blocks, and ITL slots count can be different from a block to another one.

So far so good, but … but which value for initrans? To know it, you have to dump every block that contain data (block type : 0x06), and get the block that have the highest number of itl slot allocated. (you can make an arbitrary choice too … 😉 ).

Well, my customer ITL allocation issue was located on a table with more than 60000 blocks of data, so to get the best value for initrans, I had to write few lines of code.

First, I used some code from this blog post (from Oracle Diagnostician : to dump all the blocks of my object:

 for rec in (select file_id, block_id start_block, block_id + blocks - 1 end_block from dba_extents where segment_name = '&segname' and owner = '&ownname') loop
 execute immediate 'alter system dump datafile ' || rec.file_id || ' block min ' || rec.start_block || ' block max ' || rec.end_block;
 end loop;

Note : This operation can take some time to be executed, be patient (and don’t forget to check space in your ADR trace directory)

Now, I have a look into my ADR trace directory to get the dump file (which is quite huge).

Then, I wrote a small perl script to analyze this dump file and print high water mark of itl slots.

This very simple perl script can be downloaded here :

Here’s a little demo of this script upon a block dump file (which contains many dump blocks)

$ ./ p orcl_ora_14878.trc
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0011.01f.00000095  0x00c0364a.00c6.11  ----    1  fsc 0x03fe.00000000
0x02   0x0018.00a.00000044  0x00c0317b.0038.17  ----    1  fsc 0x03fe.00000000
0x03   0x0015.01b.0000004a  0x00c03b10.0044.02  ----    1  fsc 0x03fe.00000000
0x04   0x000e.007.00000077  0x00c03716.008f.03  ----    1  fsc 0x03fe.00000000
0x05   0x0010.020.00000081  0x00c030de.00b1.0d  ----    1  fsc 0x03fe.00000000
0x06   0x0019.01c.00000043  0x00c03555.0042.1c  ----    1  fsc 0x03fe.00000000
0x07   0x0014.01b.0000004c  0x00c036b1.005f.35  ----    1  fsc 0x03fe.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.23  --U-    7  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0013.01b.00000052  0x00c039f8.008b.24  --U-    6  fsc 0x0000.00480702
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

$ ./ c orcl_ora_14878.trc

Now I can set INITRANS to a value of over 8 (and don’t forget to rebuild it to set this on all data blocks) :

SQL> alter table spl initrans 8;

Table altered.

SQL > alter table spl move tablespace users;

Table altered.


I’m Back

A quick post to inform readers that I was a little bit busy last month because of my removal and because I have started a new job on a French Oracle consulting company DIGORA.

So I will write new posts next days !!!

See you soon !