Infrastructure at your Service

 
Franck Pachot

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

By | Database management, Oracle | 11 Comments

By Franck Pachot . 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…

Read More
Microsoft Team

Weird issue with sys.fn_hadr_backup_is_preferred_replica() function

By | Hardware & Storage | No Comments

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?…

Read More
Microsoft Team

Windows Cluster vNext and cloud witness

By | Operation systems | No Comments

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…

Read More
Franck Pachot

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

By | Database management, Oracle | No Comments

By Franck Pachot . In a previous post I’ve shown that from 12.1.0.2 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()…

Read More
Nicolas Jardot

AOUG – Real World Performance Tour

By | Database management | No Comments

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. I think there is no real need to present the Real World…

Read More
Franck Pachot

Standard Edition on Oracle Database Appliance

By | Database management, Oracle | 2 Comments

By Franck Pachot . 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? Is ODA for small companies? My answer is definitely yes. Small companies can’t buy specific…

Read More
Morgan Patou

QlikView Tips & Tricks – part II

By | Application integration & Middleware | No Comments

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…

Read More
Stéphane Savorgnano

SQL Server: Change Data Capture for Oracle

By | Database management | 11 Comments

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. Prerequisites The database where you want to capture changes must be in ARCHIVELOG and OPEN. The…

Read More
Microsoft Team

A funny story with recompile hint at statement level

By | Application integration & Middleware | One Comment

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] (       …

Read More
Nicolas Jardot

Use a wallet to encrypt Oracle client passwords

By | Database management | 2 Comments

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 We find passwords for Oracle…

Read More
Franck Pachot

12c: shutdown abort a PDB?

By | Database management, Oracle | 3 Comments

By Franck Pachot . Can we shutdown abort a PDB? Let’s try: SQL> show con_id CON_ID —————————— 3SQL> shutdown abort; Pluggable Database closed. But is it really a shutdown abort? The shutdown abort we know in non-CDB – or in a CDB from CDB$ROOT – is used when the instance cannot checkpoint before closing the file. It leaves the files fuzzy. And shutdown abort crashes the instance – loosing all changes made in buffer cache…

Read More
Franck Pachot

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

By | Database management, Oracle | 6 Comments

By Franck Pachot . 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? I’ll show a feature that appeared in 11.2.0.2 (info from oracle-l) so let’s set the optimizer to behave as before that feature….

Read More