Skip navigation.

Yann Neuhaus

Syndicate content
All blog entries from
Updated: 3 hours 5 min ago

QlikView – Load data in QlikView / QlikSence – Best practice and trick

Tue, 2015-04-14 07:45

In this blog, I will give you some best practices and tricks when you are loading table to generate your data. But before, I will review the different ways to load data in a QlikView or QlikSense report.

1.How to retrieve data in QlikView

You have 2 different possibilities to load your data in your report:

- Database connector

- File connector


   a) Database connector:

     If your data are located in a database, you must use this kind of connectors.

To connect to a database:

Open "Script Editor"


Click on “Tab” and “Add Tab…”


Give a name to the new Tab and clock “OK"


Select the data source


Select your connection (for OLE DB, ODBC, connections should be created in the ODBC administrator tool from windows)

Remark: You can use a special connection on the ETL tool from QlikView named QlikView Expressor


For this example, I want to connect on a Oracle database:

Select the connection and click “OK”


Select a table (1), than select the fields you want see in your report (2) and click “OK” (3)


TRICK 1: If you use the “Connect” option and you add the User ID and the password in the connection interface, they will be put in the SQL script in an encrypted format



   b) Data file:

You have 4 different options:


(1) Table Files: you can select the following kind of files


(2) QlikView File: You can load .qvw file (QlikView file)

(3) Web File: You can load a file coming from a website

(4) Field data: you can load specific rows coming from a field located in a database

In this example, we select a .qvd file using the 1 option (table file)


You have 2 options:

Click “Next” and “Next”: you access to the column choose interface

Remark: To remove a column, click on the cross. Then click “Next ”


Check the SQL. If it’s ok, click on “Finish”


Click on “Reload” to load the data


Best Practices: create a variable path

If you must load data coming from files located in a specific repository, and if this repository is going to change after the report is published in different environments, it is recommended to create a variable to define the folder path.

Go on the Main Tab and create the variable “FilePath”. Don’t forget the “;” at the end


On the other tab where you load data coming from file located in the same folder, add the variable before the name of the file.


After the deployment on other environment, you just have to update the variable and of course, reload the data.


2.Optimize the data recovery in QlikView / QlickSence

In this example, some tables are loaded just for one field. We can optimize this schema with using a mapping function. The goal is to limit the number of tables used directly in the schema.


Warning: The mapping function can only be used to add one field in a table.

In our example, we want to add the filed “Product Group desc” in the table “ITEM_MATSER”.

To create a mapping tab:

Add a new Tab just after the main tab


Use the function “MAPPING” in the script as follow:


In the destination table, add the field with the following function “Applymap” as follow:


(1) Put the name of the Mapping table you have created

(2) Put the name of the key field

(3) Put the name of the field you want to show in your table

Don’t forget to comment the script from your mapped table.

After refreshing the data, you will see that the table has disappeared and the filed has been added in the main table.



3.How to add a Master Calendar table

You can generate automatically a master calendar if you need to have all the days located in a period. This calendar will be generated in 3 different steps:

   a) Creation of the Min / Max date temporary table.

Create a new tab and add the following script:


   b) Creation date temporary table.

Add the following script to create a temporary table with all the dates between the MIN and MAX date you have define using the function “AUTOGENERATE”


Note that we drop the result from the MIN / MAX table at the end of the creation from the temporary table.

   c) Creation Master Calendar table.

After the generation from the temporary table, add the following script to create all the different date fields you need (Year, month, week …)


Remark: to join your tables, you must give to your new generated field the same name than the field you have used to create your calendar. The result should be like this.


I hope that these best pratices and tricks will help you !







Yesterday's AWR Straight to the Goal

Tue, 2015-04-14 06:43

Yesterday I was a speaker at Collaborate15 and did my presentation about reading an AWR report. That was great. Many people (and not enough seats).

Here are some aswers about questions that came later.

Cloning a PDB from a standby database

Mon, 2015-04-13 05:14

Great events like IOUG Collaborate is a good way to meet experts we know through blogs, twitter,etc. Yesterday evening, with nice music in the background, I was talking with Leighton Nelson about cloning PDB databases. Don't miss his session today if you are in Las Vegas. The big problem with PDB cloning is that the source must be read-only. The reason is that it works like transportable tablespaces (except that it can transport the datafiles through database link and that we transport SYSTEM as well instead of having to import metadata). There is no redo shipping/apply here, so the datafiles must be consistent.

Obviously, being read-only is a problem when you want to clone from production.

But if you have a standby database, can you open it read-only and clone a pluggable database from there? From what we know, it should be possible, but better to test it.

Here is my source - a single tenant standby database opened in read-only:

SQL> connect sys/oracle@// as sysdba
SQL> select banner from v$version where rownum=1;

Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> select name,open_mode,database_role from v$database;

--------- -------------------- ----------------

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
STDB1                          MOUNTED

Then from the destination I define a database link to it:

SQL> connect sys/oracle@// as sysdba
SQL> select banner from v$version where rownum=1;

Oracle Database 12c Enterprise Edition Release - 64bit Production

SQL> select name,open_mode from v$database;

--------- --------------------

SQL> select name,open_mode from v$pdbs;

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

SQL> create database link DBLINK_TO_STCDB connect to system identified by oracle using '//';

Database link created.

and create a pluggable database from it:

SQL> create pluggable database STDB2 from STDB1@DBLINK_TO_STCDB;

Pluggable database created.

SQL> alter pluggable database STDB2 open;

Pluggable database altered.

So yes. This is possible. And you don't need Active Data Guard for that. As long as you can stop the apply for the time it takes to transfer the datafiles, then this is a solution for cloning. Of course, just do one clone and if you need others then you can do it from that first clone. And within the same PDB they can be thin clones if you can use snapshots.

Ok, It's 5 a.m here. As usual, the jetlag made me awake awake a bit early, so that was a good occasion to test what we have discussed yesterday...

RAC Attack! was another great success at C15LV

Sun, 2015-04-12 14:49

The RAC Attack  - install a RAC in your own laptop - is a great success at Las Vegas.

The idea is to help people follow the RAC Attack cookbook which is available at:

It is a complex configuration and there is always problems to troubleshoot:

  • get Virtual Box be able to run a 64-bits guest, and that might involve some BIOS settings
  • be able to install VirtualBox, and we have people with their company laptop where some security policies makes things difficule
  • Network configuration is not simple and any misconfiguration will make things more difficult later

So it is a very good exercise for troubleshooting.

The organisation way excellent: Organisation by Ludovico Caldara, infrastructure by Erik Benner, food sponsored by OTN, and Oracle software made available on USB sticks thanks to Markus Michalewicz. Yes the RAC Product Manager did the racattack installation.

 It's also a very good networking event where people meet people around the technology, thanks to IOUG Collaborate.

More Ninjas graduating the Dojo! #racattack @ioug @racsig #c15lv @OracleDBDev @Mythics

— Erik Benner (@Erik_Benner) April 12, 2015

When people manage to get a VM with the OS installed, they can get the red tee-shirt. Look at the timelapse of the full day and you will see more and more red T-shirts:

Do you wonder why we are so happy to see people having only the OS installed? Because it's the most difficult part. Creating a cluster on a laptop is not easy. You have to create the VM, you have to setup networking, DNS, etc.

Once this setup is good, then installing Grid Infrastructure and Database is straightforward with graphical installer.

Dbvisit replicate REDO_READ_METHOD

Thu, 2015-04-09 12:15

A frequent question about replication is the overhead in the source, because in a lot of cases the source is production. Dbvisit replicate comes with the possibility to do the minimum on the source: only the FETCHER process is there to read de redo logs and sends it to the MINE process wich can be on another server.

But maybe even that - reading those critical online redo logs - is worrying you. That's not a reason to avoid to do a PoC with the production as source. Let's see how we can use the 'archived logs only'.

DataGuard wait events have changed in 12c

Wed, 2015-04-08 11:11

There are several new features in 12c about Data Guard: cascaded standby, far sync instance. But there are also some architecture changes: new processes and new wait events.

QlikSense – The easy way to create a QlikView report

Tue, 2015-04-07 00:57

Today, one of the most used Business Intelligence products is undoubtedly QlikView. This Swedish product is one of the best positioned in the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms matrix and has been for a number of years.


To make Business Intelligence even more accessible to Business users, the Qlik Company decided to add an additional product in its range. This product should be more accessible and easier to use to create analytics reports. That’s why QlikSense was created.

1.Build a report in a few mouse clicks

One of the great strengths of QlikView is that the use of an analytical report has become very simple. The user of a QlikView report need only to select the various criteria they wish to apply to these tables and graphs so that they are automatically adapted to the application.



In this case, the user has just clicked on the criteria "Argentina" and all report data has been filtered to show only the data for this country.

However, the construction of a QlikView’s report is not so simple. The multitude of options available for creating each artifact can quickly become quite complex. This makes the end user still dependent on the developer's report both for its construction than for any changes they would make.

To make the creation of these reports more accessible to the majority, Qlik decided to launch Qlik Sence. This product brings simplicity in building reports because it’s using the Drag and Drop technology. The user can now create his tables and charts using only the mouse, making the creation of a report as easy as in Excel.

To create a new report in QlikSense, click "Create a new App"


Give the new application a name and click « Create »


Click on « Open»


As in any QlikView report, the first thing to do is to retrieve data. There are two options to do that:

"Quick Data Load" or " Load Data Editor."

  • “Quick Data Load" allows you to select any data coming from a flat file or located in a XLS sheet.
  • "Data Load Editor" allows you to create more complex data sources based on SQL -type queries


For our example, we are using « Data Load Editor ». After opening the application, you should only select the data source connector you want to use and set up your connection to your data source.


I will created later a new blog focused on the creation and the setup of data connection in QlikView and QlikSense.

In this case, we will use a .qvx file type. Such files are created through the QlikView ETL application called "QlikView Expressor ".

The load script is then displayed on the screen in SQL format.


You just have to launch the data load.


When it’s done, you should activate the « App Overview » mode.


Then, the user can create a new sheet:


He gives it a name and click on it.


To display the « Edit » mode, you just have to click on this icon:


At this point, the user can create his graphic using the Drag and drop mode


After chosen the chart type, select your dimensions and measures


And that’s the result.


Despite its simplicity, Qlik Sence retains the power of creating and setting chart from QlikView. But the bulk of operations is now made via selection of options, making much easier to create different artifacts.

2.Type "Map" chart

Qlik has not only added the notion of Drag and Drop. One of the great innovations in QlikSense regards the possibility to create very easily a type "Map" chart, that is to say containing geographical information. It was quite difficult to do it in QlikView because it required the use of fairly complex calculated variables. With QlikSense, you just have to use the type "Map" object specially created for this purpose and complete the requested information.

To create a chart "Map", select the chart type "Map"


The only constraint to achieve this kind of graph is to have a GeoMakePoint type object. It is created by combining two geolocation information type objects (latitude and longitude).

In the example below, the object GeoMakePoint "Location" is the location of cities.


Click in the " Layers " menu. In the "Add Layers " box, insert the GeoMakePoint object you just have created. And in the "Expression" box, add an aggregate expression.


Then, go to the "Background" menu, select the Slippy map server you want to use and copy the URL and the duties of the selected server

Small comment: All Slippy Map servers can be used by QlikSense. They are listed on the website of QlikView. To access it, simply click on the hyperlink "URL and Allocation".


To activate the different colors from your dimensions on the map, go in the menu « Apparence » and choose the following options:


Final result will be displayed on a map. Points are the dimension “city” and the size of the point will be the measure (Sum(Profit)).


This chart is as interactive as the other, it will also apply the filters selected by the user.

3.The mode "Story"

QlikSense also offers the possibility to create "Stories", a kind of Microsoft Powerpoint presentation. A "Story" is simply a result of various reports that have been fixed with the values that the user wishes to highlight. These presentations can also contain some kind of animation options from those used in Microsoft Powerpoint. These Stories are built from existing QlikSense reports on which the user takes a snapshot after having applied the desired filter.

To create a "Story" , simply activate the "App Overview" mode.


Then select the « story » mode and click on « new story ».


Give it a name, push « Enter » and click on the story.


Before creating the story, you should have taken snapshots from other sheets.

Warning : You can’t insert snapshots located in some other QlikSense applications.

To create a snapshot, open a Qliksense sheet located in your application.


Open the story you have made and insert the snapshots you need.


You can add some text zones, Microsoft Powerpoint animations, special forms and media objects.


And that’s the final result.


In conclusion, we can say that QlikView and QlikSense are complementary products that are not reserved to the same type of user.

  • QlikSense is easier to handle and is designed especially for use as a dashboarding tool
  • Using HTML 5 technology, it allows you to create reports for any kind of devices
  • Map type graphs are easier to create
  • You can create Stories

So we can say that QlikSense will be more easily used by the Business than QlikView because of its friendliness to use.

Oracle tuning silver bullet: add an order by to make your query faster

Wed, 2015-04-01 00:00

You have read all Cary Millsap work about Oracle database performance tuning. You know that there are no silver bullets. Reducing the response time requires a methodical approach in order to analyze the response time with the goal of eliminating all unnecessary work.

But I'll show something completly opposite here. A performance tuning silver bullet. Do more work in order to run it faster: just add an ORDER BY to your query and its faster. 

Weird issue with sys.fn_hadr_backup_is_preferred_replica() function

Mon, 2015-03-30 13:11

A couple of days ago, I faced a weird backup issue with SQL Server AlwaysOn and availability groups at one of my customer (thank to him to point out this issue :-) ). After installing our DMK tool (Database Management Kit) about database maintenance for AlwaysOn, my customer noticed that their databases have not backed up. Ouch … what’s going on? I never ran into this issue before... Do the problem comes from our tool?

In fact, our DMK uses the useful DMF sys.fn_hadr_backup_is_preferred_replica() to know which databases are candidate for backup operations on replicas at a given time and this is where our issue starts. Indeed, in a specific situation that includes both a case sensitive server collation and entering the name of replicas in lower case, we found that the result of this function is inconsistent. Let me show with an example.

In my customer’s context, the replica names have been filled out from a PowerShell script form in lower case as follows:




Let’s take a look at the system view to check the availability group configuration:


SELECT        replica_server_name,        availability_mode_desc,        failover_mode_desc FROM sys.availability_replicas




Let’s verify that the collation of the SQL Server instance is case sensitive …


SELECT SERVERPROPERTY('Collation') AS ServerCollation;




… and the backup preference policy is configured to “primary”


SELECT        name AS group_name,        automated_backup_preference_desc as backup_preference FROM sys.availability_groups




Finally, let’s verify the database inside the availability group:


SELECT AS group_name,        r.replica_server_name AS replica_name,        dcs.database_name,        drs.database_.state_desc AS db_state FROM sys.dm_hadr_database_replica_states AS drs JOIN sys.availability_replicas AS r        ON drs.replica_id = r.replica_id JOIN sys.availability_groups AS g        ON g.group_id = drs.group_id JOIN sys.dm_hadr_database_replica_cluster_states AS dcs        ON dcs.group_database_id = drs.group_database_id              AND dcs.replica_id = drs.replica_id




Ok now let’s take a look at the result of the DMF sys.fn_hadr_backup_is_preferred_replica() in this context. I put here a simplified sample portion of the TSQL code used in our DMK:


USE master; GO   DECLARE @db_name SYSNAME;   SELECT @db_name = name FROM sys.databases WHERE name = N'DUMMY';   SELECT        @@SERVERNAME AS server_name,        @db_name AS database_name,        sys.fn_hadr_backup_is_preferred_replica(@db_name) AS fn_result;


Concerning the primary:




Concerning the secondary :





If you perform the same by configuring this time the replica names in upper case, you will notice that the issue will disappear. When I think about this issue, it's true that in almost cases customers prefer to use the assistant wizard to configure availability groups and in this case do you notice that the replica names are always switched in upper case?

There also exists a Microsoft connect item about this problem but unfortunately it seems that it will not be solved by Microsoft … so be careful when you implement availability groups by script.

See you on the next availability group ventury!




Windows Cluster vNext and cloud witness

Sun, 2015-03-29 12:14

The next version of Windows will provide some interesting features about WFSC architectures. One of them is the new quorum type: "Node majority and cloud witness" which will solve many cases where a third datacenter is mandatory and missing to achieve a truly resilient quorum.

Let’s imagine the following scenario that may concern the implementation of either an SQL Server availability group or a SQL Server FCI. Let’s say you have to implement a geo-cluster that includes 4 nodes across two datacenters with 2 nodes on each. To achieve the quorum in case of broken network link between the two datacenters, adding a witness is mandatory even if you work with dynamic weight nodes feature but where to put it? Having a third datacenter to host this witness seems to be the better solution but as you may imagine, it is a costly and not affordable solution for many customers.

Using a cloud witness in this case might be a very interesting workaround. Indeed, a cloud witness consists of a blob storage inside a storage account's container. From cost perspective, it is a very cheap solution because you have to pay only for the storage space you will use (first 1TB/month – CHF 0.0217 / GB). Let's take a look at the storage space consumed by my cloud witness from my storage account:





Interesting, isn’t it? To implement a cloud witness, you have to meet the following requirements:

  • Yourstorage account must be configured as a locally redundant storage (LRS) because the created blob file is used as the arbitration point, which requires some consistency guarantees when reading the data. All data in the storage account is made durable by replicating transactions synchronously in this case. LRS doesn’t protect against a complete regional disaster but it may be acceptable in our case because cloud witness is also dynamic weight-based feature.
  • A special container, called msft-cloud-witness, is created to this purpose and contains the blob file lied to the cloud witness.




How to configure my cloud witness?

In the same way than before. By using the GUI, you have to select the quorum type you want to use and then you must provide the storage account information (storage account name and the access key). You may also prefer to configure your cloud witness by using PowerShell cmdlet Set-ClusterQuorum as follows:




After configuring the cloud witness, a corresponded core resource is created with an online state as follows:




By using PowerShell:




Let’s have a deeper look at this core resource, especially the following advanced policies parameters isAlive() and looksAlive() configuration:




We may notice that the basic resource health check interval default value is configured to 15 min. Hmm, I guess that this value will probably be customized according to the customer architecture configuration.

Go ahead and let’s perform some basic tests with my lab architecture. Basically, I have configured a multi-subnet failover cluster that includes four nodes across two (simulated) datacenters. Then, I have implemented a cloud witness hosted inmy storage account “mikedavem”. You may find a simplified picture of my environment below:








You may notice that because I implemented a cloud witness, the system changes the overall node weight configuration (4 nodes + 1 witness = 5 votes). In addition, in case of network failure between my 2 datacenters, I want to prioritize the first datacenter in terms of availability. In order to meet this requirement, I used the new cluster property LowerQuorumPriorityNodeID to change the priority of the WIN104 cluster node.




At this point we are not ready to perform our first test: simulate a failure of the cloud witness:




Then the system recalculates the overall node weight configuration to achieve a maximum quorum resiliency. As expected, the node weight of WIN104 cluster node is changed from 1 to 0 because it has the lower priority.

The second consists in simulating a network failure between the two datacenters. Once again, as expected, the first partition of the WFSC in the datacenter1 keeps online whereas the second partition brings offline according the node weight priority configuration.




Is the cloud witness dynamic behavior suitable with minimal configurations?

I wrote a blog post here about issues that exist with dynamic witness behavior and minimal configurations with only 2 cluster nodes. I hoped to see an improvement on that side but unfortunately no. Perhaps with the RTM release … wait and see.


Happy clustering!



Index on SUBSTR(string,1,n) - do you still need old index?

Fri, 2015-03-27 03:57

In a previous post I've shown that from when you have an index on trunc(date) you don't need additional index. If you need the column with full precision, then you can add it to the index on trunc(). A comment from Rainer Stenzel asked if that optimization is available for other functions. And Mohamed Houri has linked to his post where he shows that it's the same with a trunc() on a number.

Besides that, there is the same kind of optimization with SUBSTR(string,1,n) so here is the demo, with a little warning at the end.

I start with the same testcase as the previous post.

SQL> create table DEMO as select prod_id,prod_name,prod_eff_from +rownum/0.3 prod_date from sh.products,(select * from dual connect by level>=1000);
Table created.

SQL> create index PROD_NAME on DEMO(prod_name);
Index created.

SQL> create index PROD_DATE on DEMO(prod_date);
Index created.

I've an index on the PROD_NAME and I can use it with equality or inequality predicates:

SQL> set autotrace on explain
SQL> select distinct prod_name from DEMO where prod_name > 'Z';
no rows selected

Execution Plan
Plan hash value: 72593368

| Id  | Operation          | Name      | Rows  | Bytes |
|   0 | SELECT STATEMENT   |           |     1 |    27 |
|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |
|*  2 |   INDEX RANGE SCAN | PROD_NAME |     1 |    27 |

Predicate Information (identified by operation id):

   2 - access("PROD_NAME">'Z')

And I also can use it with a LIKE when there is no starting joker:
SQL> select distinct prod_name from DEMO where prod_name like 'Z%';
no rows selected

Execution Plan
Plan hash value: 72593368

| Id  | Operation          | Name      | Rows  | Bytes |
|   0 | SELECT STATEMENT   |           |     1 |    27 |
|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |
|*  2 |   INDEX RANGE SCAN | PROD_NAME |     1 |    27 |

Predicate Information (identified by operation id):

   2 - access("PROD_NAME" LIKE 'Z%')
       filter("PROD_NAME" LIKE 'Z%')

That optimization is available for several releases (9.2 if I remember well but I didn' check).


But sometimes, when we want to check if a column starts with a string, the application uses SUBSTR instead of LIKE:

SQL> select distinct prod_name from DEMO where substr(prod_name,1,1) = 'Z';
no rows selected

Execution Plan
Plan hash value: 1665545956

| Id  | Operation          | Name      | Rows  | Bytes |
|   0 | SELECT STATEMENT   |           |     1 |    27 |
|   1 |  SORT UNIQUE NOSORT|           |     1 |    27 |
|*  2 |   INDEX FULL SCAN  | PROD_NAME |     1 |    27 |

Predicate Information (identified by operation id):

   2 - filter(SUBSTR("PROD_NAME",1,1)='Z')

But - as you see - there is no access predicate here. The whole index has to be read.

Of course, I can use a function based index for that:

SQL> create index PROD_NAME_SUBSTR on DEMO( substr(prod_name,1,1) );
Index created.

SQL> select distinct prod_name from DEMO where substr(prod_name,1,1) = 'Z';
no rows selected

Execution Plan
Plan hash value: 4209586087

| Id  | Operation                    | Name             | Rows  | Bytes |
|   0 | SELECT STATEMENT             |                  |     1 |    31 |
|   1 |  HASH UNIQUE                 |                  |     1 |    31 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEMO             |     1 |    31 |
|*  3 |    INDEX RANGE SCAN          | PROD_NAME_SUBSTR |     1 |       |

Predicate Information (identified by operation id):

   3 - access(SUBSTR("PROD_NAME",1,1)='Z')

One index only?

Then, as in the previous post about TRUNC I'll check if that new index is sufficient. Let's fdrop the first one.

SQL> drop index PROD_NAME;
Index dropped.
The previous index is dropped. Let's see if the index on SUBSTR can be used with an equality predicate:
SQL> select distinct prod_name from DEMO where prod_name = 'Zero';
no rows selected

Execution Plan
Plan hash value: 953445334

| Id  | Operation                    | Name             | Rows  | Bytes |
|   0 | SELECT STATEMENT             |                  |     1 |    27 |
|   1 |  SORT UNIQUE NOSORT          |                  |     1 |    27 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEMO             |     1 |    27 |
|*  3 |    INDEX RANGE SCAN          | PROD_NAME_SUBSTR |     1 |       |

Predicate Information (identified by operation id):

   2 - filter("PROD_NAME"='Zero')
   3 - access(SUBSTR("PROD_NAME",1,1)='Z')

Good. The index on substring is used for index range scan on the prefix, and then the filter occurs on the result. This is fine as long as the prefix is selective enough.

It is also available with inequality:
SQL> select distinct prod_name from DEMO where prod_name > 'Z';
no rows selected


Predicate Information (identified by operation id):

   2 - filter("PROD_NAME">'Z')
   3 - access(SUBSTR("PROD_NAME",1,1)>='Z')

And we can use it even when using a substring with a different number of characters:
SQL> select distinct prod_name from DEMO where substr(prod_name,1,4) = 'Zero';
no rows selected


Predicate Information (identified by operation id):

   2 - filter(SUBSTR("PROD_NAME",1,4)='Zero')
   3 - access(SUBSTR("PROD_NAME",1,1)='Z')

However, if we use the LIKE syntax:

SQL> select distinct prod_name from DEMO where prod_name like 'Z%';
no rows selected

Execution Plan
Plan hash value: 51067428

| Id  | Operation          | Name | Rows  | Bytes |
|   0 | SELECT STATEMENT   |      |     1 |    27 |
|   1 |  HASH UNIQUE       |      |     1 |    27 |
|*  2 |   TABLE ACCESS FULL| DEMO |     1 |    27 |

Predicate Information (identified by operation id):

   2 - filter("PROD_NAME" LIKE 'Z%')

The LIKE snytax does not allow to filter from the index on SUBSTR. So there are cases where we have to keep all indexes. Index on full column for LIKE predicates, and index on substring for SUBSTR predicates.

Note that indexes on SUBSTR are mandatory when you have columns larger than your block size, which is probably the case if you allow extended datatypes (VARCHAR2 up to 32k)

AOUG - Real World Performance Tour

Thu, 2015-03-26 13:26

This week, Tom Kyte, Graham Wood and Andrew Holdsworth were present in Europe for several dates. One of the events was organised by the Austrian Oracle User Group (AOUG) in collaboration with the German and Swiss User Group (DOAG and SOUG) and I had the chance to be there to attend to one session of the Real Worl Performance tour session in Vienna.

Standard Edition on Oracle Database Appliance

Wed, 2015-03-25 11:15

The Oracle Database Appliance is really interresting for small enterprises. It's very good hardware for very good price. It's capacity on demand licensing for Enteprise Edition. But small companies usually go to Standard Edition for cost reasons.

Then does it make sense to propose only Enterprise Edition to the small companies that are interrested by ODA?

QlikView Tips & Tricks - part II

Thu, 2015-03-19 15:00

In a previous blog entry, I started to share some Tips & Tricks about the installation and configuration of some QlikView components/software. I will try to complete this list with some other interesting things to know. On this blog entry, I will try to explain how to enable the Task Performance Summary, to debug or at least enable the Communication with Salesforce and how to install the QlikView Management Console Connector (QlikView Management API).

For the whole blog entry, let's define the following values:

  • %SYS_PROFILE% = C:/Windows/System32/config/systemprofile
  • %QV_SRV_HOME% = D:/Apps/QlikView Server (defined during the QlikView Server installation)
  • %DS_DATA_HOME% = D:/QlikView/DistributionService (defined in the QMC: System ˃ Setup ˃ Distribution Services ˃ QDS@hostname ˃ General ˃ Settings for QDS ˃ Application Data Folder)
  • %PROXY_HOST% =
  • %PROXY_PORT% = 11210
  • %PROXY_PORT_S% = 11211
  • %HOST_FQDN% =
  • %HOST_IP% =
  • %HOST_ALIAS% =
  • %QV_GRPS% = QV_GRP_SUP + QV_GRP_DEV + QV_GRP_ADM (some groups for QlikView, all under the domain "DOMAIN")
  • %QV_SYS_USER% = QV_SYS_USER (the "DOMAIN" user under which QlikView is running)

Each time you will see one of these parameters or values in the text below, don't forget to replace them with YOUR OWN values. I only associate them for an example and to help you to find a match in your environment.

I. Task Performance Summary

Introduced in QlikView 11.2 SR7 and disabled by default, the Task Performance Summary is a new feature of QlikView to analyse the performance of the Tasks (I'm sure you already understood that from the title!). This new feature simply launch a little benchmark during the execution of all tasks to record some useful data like:

  • Name of the process that ran the task with its PID
  • CPU used by the process (Average, Peak)
  • CPU used by the Windows Server (Peak)
  • Virtual RAM used by the process (Average, Peak)
  • Virtual RAM used by the Windows Server (Peak)
  • Physical RAM used by the process (Average, Peak)
  • Physical RAM used by the Windows Server (Peak)
  • Duration of the reload task

For debugging, performance analysis and performance improvements, it's pretty cool to have this kind of information. Enable the Task Performance Summary is quite simple:

  1. Login to the Windows Server with any Administrator account
  2. Open the file: %SYS_PROFILE%/AppData/Roaming/QlikTech/QlikViewBatch/Settings.ini
    1. Add at the end: EnableQVBProcessSummary=1
    2. Add an empty line at the end of the file (VERY IMPORTANT: the last line MUST be an empty line)
  3. Open a command prompt as Administrator and execute the command: "%QV_SRV_HOME%/Distribution Service/qvb.exe"
  4. In the command prompt, execute another command: services.msc
  5. Restart all QlikView Services
  6. Open the folder: %DS_DATA_HOME%/TaskResults/
  7. Refresh the folder's content until there is a new file created (if nothing new appears, force the execution of a QlikView Task)
  8. Open the last created xml file and check that inside there is a line which include CPU and RAM consumption

Task1.pngModification of the Settings.ini file to enable the Task Performance Summary

Task2.pngLine added in the TaskResults' xml files by the Task Performance Summary

That's it, the Task Performance Summary is now enabled and will record the performance of the future task's executions.

II. Communication with Salesforce

If it's your first time with the QlikView Salesforce Connector, then there is one thing that is essential to understand: the QlikView Salesforce Connector requires an internet access to work. To be more precise, the connector will have to be able to access to the Salesforce Website to retrieve some data models that will be used by QlikView for any communication with a Salesforce Data Source. Therefore, if your enterprise network uses a proxy or anything else to prevent or restrict the access to internet, then this will need to be fixed.

SalesForceIssueNW_P.pngLog file generated during a task execution when the proxy configuration isn't done

SalesForceIssueW_P.pngLog file generated during a task execution when the proxy configuration is properly done

On this section, I will just describe how to configure your Windows Server to allow the communication with the Salesforce Website by configuring the proxy settings:

  1. The QlikView Salesforce Connector must be properly installed
  2. Login to the Windows Server with the account under which QlikView is running
  3. Open: Internet Explorer ˃ Internet Options ˃ Connections ˃LAN settings
    1. Click on: Use a proxy server for your LAN
    2. Click on: Bypass proxy server for local addresses
    3. Click on: Advanced
    4. HTTP: Address = %PROXY_HOST%
    5. HTTP: Port = %PROXY_PORT%
    6. Secure: Address = %PROXY_HOST%
    7. Secure: Port = %PROXY_PORT_S%
    8. Exceptions =;localhost;%HOST_FQDN%;%HOST_IP%;%HOST_ALIAS%
  4. Click on: OK (3 times)

SalesForce1.pngConfiguration of the proxy in the Internet Explorer's options

After this modification, the communication and therefore the reload of QlikView Documents using Salesforce as a Data Source should be successful.

III. QMC Connector

In QlikView by default, the license management through the QlikView Management Console is quite difficult... That's why the QlikView Community is really active on this topic and some solutions have been developed to easily manage the QlikView licenses. The most common solution is to use the QMC Connector. This connector will use the QlikView Management API to manage a lot of things directly from a QlikView Document. The installation of this connector is quite easy:

  1. Download the QlikView Connector:
  2. Login to the Windows Server with any Administrator account
  3. Extract the QVSManager zip files into: C:/Program Files/Common Files/QlikTech/Custom Data/. This will create the folder "QVSManager" and in this folder, there should be 4 files
  4. Create a Windows local group:
    1. Open a command prompt as Administrator and execute: compmgmt.msc
    2. Open: System Tools ˃ Local Users and Groups ˃ Groups
    3. Create a new group with the following entries:
      1. Name = QlikView Management API
      2. Description = QlikView Management API
      3. Members = %QV_GRPS% + %QV_SYS_USER%
    4. Click on: Create
  5. Restart all QlikView Services
  6. Deploy the QlikView Document: QVSCALManager.qvw (I put it in a zip file for convenience)

QMCConnector1.pngDeployment of the file in the "Custom Data" directory of QlikView

QMCConnector2.pngCreation and configuration of the Windows Server's local group for the QVSManager

Once this new QlikView Document is ready (Document deployed, new task created and scheduled, aso...), you should be able to see it in the QlikView AccessPoint and manage the QlikView licenses directly from this new application. Believe me, it will greatly facilitate your work!

Okay, that was my second blog entry about QlikView, I guess that's enough for now :). I hope some of you found these tips useful and if needed, don't hesitate to let me a little comment below and I'll do my best to help you. See you soon!

SQL Server: Change Data Capture for Oracle

Thu, 2015-03-19 02:55

This new feature has been introduced in SQL Server 2012 and needs an Enterprise Edition. It uses a Windows Service which scans Oracle Logs and tracks DML changes from Oracle tables into SQL Server change tables.
In other words, Change data capture records Insert, Update and Delete activities that is applied to Oracle tables.
Let's see how it works.


The database where you want to capture changes must be in ARCHIVELOG and OPEN.
The user which will be used to connect to the Oracle database, must have DBA privileges.

Change Data Capture services installation

Installation will be done via msi packages. Those packages are not installed automatically with SQL Server 2012 or 2014. You will find them in your installation media under ToolsAttunityCDCOraclex641033.
There are two msi packages, one for the CDC Service Configuration named AttunityOracleCdcService.msi and another for CDC Designer named AttunityOracleCdcDesigner.msi.

Double click on AttunityOracleCdcService.msi and install the package, same for AttunityOracleCdcDesigner.msi.



When both installations are done, go to the Application Panel and launch “Oracle CDC Service Configuration”.


We will now have to prepare our SQL Server instance to use it. Each Oracle CDC Service instance lies a single SQL Server instance which will be used to manage it.
To create this instance click on "Prepare SQL Server" or right click on Local CDC Service and select “Prepare SQL Server”:


Once it is done select the SQL Server instance where you want to install the MSXDBCDC database and click on the Run button:


The database is created:


Let's check from SQL Server Management Studio the newly created database:


At this point we can create a new CDC service:


To create the windows service we have to provide the following information:

  • The Service Name
  • Use a local system account for the service account

In addition, we must provide the following information for the associated SQL Server instance:

  • Server name
  • Authentication and login

The next step consists in creating a master password for CDC service which will be used to create symmetric key.


After that we will create an instance from the CDC Designer.
When I open it, I have to enter the SQL server instance, I have created before, which is associated with the CDC service. Credentials are required to connect to the concerned SQL Server Instance.


After connecting to the SQL Server I can see all related Oracle CDC Services. Next, after selecting the OracleCDCService1, I will create a new instance for this service:


Provide a name to the future CDC Instance and then create the change associated database:


After clicking on “Create Database” button the wizard will assist us to create the new Oracle CDC instance and the change database.


Click Next.
We have now to fill out the form with following information:

  • the Oracle connection string to our listener
  • user name
  • password


Check if the connection to the source is successful:


Click Next and then click on the Add button to select tables and columns for capturing changes.


Select the schema and click on the Search button.
Select the SCOTT.EMP table and click on the Add button.


A message warns up that the SCOTT_EMP table has been added to the list:


Now, you can see the table in the list:


The Capture instance column, here SCOTT_EMP, will be used to name the capture instance specific to each table object in my SQL Server database.

At this point, no CDC gating role is specified so it means that no gating role will be used to limit access to the change data.

By default all the columns of the SCOTT.EMP table are selected for CDC. If I want to select just some columns I can click on the Edit button and choose the columns I want in CDC.
Click on OK when your selection is finished and after click on Next:


To be able to capture Oracle changes, supplemental logging have to be set up for the Oracle databases tables. For this purpose, a script have been generated automatically and have to be run immediately or later but you have to be aware that changes will not be captured until the script is executed.
Click on the “Run Script” button:


In order to run the script some credential must be provided and after click on the Run button:


The script has been executed with success:


Click on Next and in the last screen click on the “Run” button to finalize the CDC process:


It looks like I have some errors...


Let’s click on Details:


Apparently I forgot to set up my Oracle database for supplemental logging, let’s do it:


Now, we can click on the Next button:


We have successfully created an Oracle CDC instance. Let’s check on my SQL Server:


The mirror table will be always empty conceptually. The generated deployment script denies all DML permissions on the mirror table.

I have a new database named OracleCDCInstance1 with, for the moment, an empty table named SCOTT.EMP, which is the table I selected earlier from the wizard.

Back to my CDC Designer, I see that I have now an OracleCDCInstance1 which is for the moment not started:


Now let's start the instance:


The Detailed status has changed from Initial to IDLE:


It may take a few minutes (one or two) to start change capture process. You may notice the detailed status that will change from IDLE to PROCESSING. Likewise, Processing and Counters areas will also change as oracle logs are ridden.
Here I performed an update of the salary column in my table SCOTT.EMP from my Oracle database:


We can see that the twelves operations are reflected in the counters area as twelves reads:


If the Detailed Status changes to LOGGER instead of PROCESSING, it means that even you have a temporary delay mining Oracle logs and in this case PROCESSING will come back quickly even you have a problem of mining Oracle logs and in this case check the dbo.xdbcdc_trace in the MSXDBCDC database to check errors.

We are also able to check information from log traces by clicking on the “Collect diagnostics” link which will generate diagnostics data from both Oracle environment and dbo.xdbcdc_trace table into a trace file:


This feature gives the opportunity to use Change Data Capture in a SQL Server database using an Oracle database as a source. A major advantage of using CDC for Oracle is certainly the reduction of data level latency and at the same time, Oracle ETL knowledge becomes useless. It is also a good way to source SQL Server database with Oracle Data.
Hope this article will help you, envoy ;-)

A funny story with recompile hint at statement level

Wed, 2015-03-18 09:31

Last night, I had an interesting discussion with one of my MVP French friend that faces a weird situation where a query that uses a statement level RECOMPILE hint produces an execution plan that disappointed him. He told me we can simulate the same situation by using the AdventureWorks database and Sales.SalesOrderHeader table.

First, we have to add a nonclustered index on the orderdate column as follows:


CREATE NONCLUSTERED INDEX [idx_sales_salesorderheader_orderdate] ON [Sales].[SalesOrderHeader] (        [OrderDate] ASC )


Now, let me show you the query. In fact we compare two queries and two potential behaviours. Indeed, the first query will use a local variable and an inequality operator while the second query will be pretty the same except we add the recompile hint option.


declare @date datetime = getdate()   select top 1 SalesOrderID from sales.salesorderheader where orderdate >= @date order by SalesOrderID go   declare @date datetime = getdate()   select top 1 SalesOrderID from sales.salesorderheader where orderdate >= @date order by SalesOrderID option(recompile)


Let’s compare the estimated execution plan of the both queries. In fact, the estimated plan produced by the query optimizer is the same in both cases.




Next, let’s compare their real execution plans.

--> Concerning the first query:



--> Concerning the second query (with recompile hint):



Interesting, isn’t it? For the same query except the recompile option the query optimizer has decided to use an index scan operator in the first case and an index seek in the second case. At this point, of course we supposed that the recompile hint affects the query optimizer decision but how far? Well, the response lies in the way the query optimizer handles the parameter @date.

In the first query, the query optimizer is not aware of the @date parameter value at the compile time.





It means that it will not be able to use the density information and instead it will use the standard guess of 30% selectivity for inequality comparisons. If we take a look at the statistic object of the primary key we may see that 30% of the total rows is equal to 31465 * 0.3 = 9439


dbcc show_statistics ('sales.salesorderheader', 'PK_SalesOrderHeader_SalesOrderID') with stat_header;




But at this point, we may wonder why SQL Server is using the primary key rather than the index on the orderdate column for example. In fact, the primary key is a good candidate for this query includes an ORDER BY clause (order by SalesOrderId). It means that data is already ordered by SalesOrderId and the query optimizer doesn’t need to perform an extra step that consists in sorting data before using the top operator as shown below:


declare @date datetime = getdate()   select top 1 SalesOrderID from sales.salesorderheader where orderdate >= @date order by SalesOrderID   select top 1 SalesOrderID from sales.salesorderheader with (index (idx_sales_salesorderheader_orderdate) ) where orderdate >= @date order by SalesOrderID go




Let’s continue with the second query and notice how SQL Server is handling the @date parameter this time: the local variable value is transformed as parameter as shown below:




Hmm… does it mean that SQL Server is aware of local variable value when using the statement RECOMPILE hint? In fact, yes it does and this detail changes completely the story because in this case SQL Server is able to use the histogram of the index idx_salesorderheader_orderdate. However, we are in such situation where the predicate value is not represented on the histogram and SQL Server will use an estimate of 1 that implies to use an index seek operator. Likewise, we may wonder why SQL Server has decided to seek the index idx_salesorderheader_orderdate with a TopN Sort operator here. Once again, the query optimizer is cost-based and this strategy is surely the less costly. Let’s demonstrate by comparing the both strategies:


declare @date datetime = getdate()   select top 1 SalesOrderID from sales.salesorderheader with (index (PK_SalesOrderHeader_SalesOrderID) ) where orderdate >= @date order by SalesOrderID option(recompile)   select top 1 SalesOrderID from sales.salesorderheader where orderdate >= @date order by SalesOrderID option(recompile)




Let’s take a look at the statistics related on both CPU and IO consumption of the both queries

--> Concerning the first query (clustered index scan operator)

Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   (1 row(s) affected)   SQL Server Execution Times:    CPU time = 16 ms, elapsed time = 2 ms. SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 1 ms.


--> Concerning the second query (index seek operator). Yes, in this case you have only to read 2 pages (the index root page and one data page at leaf level)

Table 'SalesOrderHeader'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.   (1 row(s) affected)   SQL Server Execution Times:    CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 0 ms.


The bottom line: in certain cases we may be surprised by the choices made by the query optimizer but most of the time, we can trust it and this is what I wanted to point out in the blog post! The truth often lies elsewhere :-)

Happy optimization!

Use a wallet to encrypt Oracle client passwords

Tue, 2015-03-17 06:53

In our more and more connected world, security is a big concern. Storing password is a very important topic because if a machine is compromised, we can assume that any password stored in clear text are also compromised. I had the question from a customer how can we encrypt the password in our application and monitoring scripts? In this blog we will discuss one solution: using the Oracle client wallet

12c: shutdown abort a PDB?

Sun, 2015-03-15 23:56

Can we shutdown abort a PDB? Let's try:

SQL> show con_id
3SQL> shutdown abort;
Pluggable Database closed.

But is it really a shutdown abort?

Index on trunc(date) - do you still need old index?

Sun, 2015-03-15 15:16

Sometimes we have to index on ( trunc(date) ) because a SQL statement uses predicate on it instead of giving a range from midnight to midnight. When you do that you probably keep the index on the column. That's two indexes to maintain for DML. Do we need it?

ODA 12.1.X.X.X - add a multiplexed control file under ACFS

Thu, 2015-03-12 07:43

Since version 12, ODA stores databases on ACFS volumes instead of ASM directly. This slightly changed the way the files are managed and administer. This articles presents how to multiplex your control files on ACFS.