Infrastructure at your Service

Execution plan Archives - Page 2 of 3 - Blog dbi services

Oracle Team

Postgres vs. Oracle access paths – intro

By | Oracle, Postgres | 2 Comments

By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading Oracle execution plans and no experience at all on Postgres. This is my way to…

Read More
David Barbarin

SQL Server 2016 : nonclustered columnstore index and aggregate pushdown

By | Development & Performance | 2 Comments

Did you miss aggregate pushdown capability shipped with columnstore index? Well, I remember the first time I heard about it is was when I read the very interesting blog post of Niko Neugebauer here with a very good covering of the topic (principle, advantages and limitations). So why to write a new blog post here? Well because since the last Niko’s blog article, this feature has been improved and I wanted to test it with…

Read More
David Barbarin

Dynamic filters and “kitchen sink” queries dilemma

By | Development & Performance | One Comment

Dealing with dynamic filters is a common scenario with management software or ERP. Indeed, users should like to have the flexibility to filter and sort their business data as they want in order to be efficient. Target and operate quickly on the right data is in line with the required performance every day. This is at least what I could notice from different customer places. So, through my mandates at customer places, I could see…

Read More
David Barbarin

SQL Server 2016 query store

By | Database Administration & Monitoring, Development & Performance | 2 Comments

One of the most new interesting features that will be introduced by SQL Server 2016 is certainly the query store that will complete the performance tuning toolbox of database administrators. Basically, the query store will track queries, their query plans and runtime statistics as well. It will be a great tool to detect regressing queries. I remember a real customer case a couple of months ago where I was involved in a database migration project…

Read More
David Barbarin

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
David Barbarin

SQL Server 2014: sys.dm_exec_query_profiles, a new promising feature

By | Application integration & Middleware | 3 Comments

Among the hidden features provided by SQL Server 2014 there is a very interesting dynamic management view named sys.dm_exec_query_profiles. This feature will help the database administrators and experienced developers to troubleshoot long running queries in real-time. I’m pretty sure you had to deal with the following questions: When will this query finish? What percentage of total workload is performed by this request? Which steps are the longest? Before SQL Server 2014 it was impossible to…

Read More
Oracle Team

Oracle SQL Monitoring reports in flash, html, text

By | Database management, Oracle | No Comments

By Franck Pachot . I have recently posted on the way I like to extract execution plans. When we have Tuning pack, I suggest to get them with SQL Real-Time Monitoring as an active report. However, Martin Preiss said in a comment that he prefers the text format – easier to search, copy, and paste. And that’s a very good remark. We still need plain text. My point is that if you send me the…

Read More
Oracle Team

Best practice for the sending of an Oracle execution plan

By | Database Administration & Monitoring, Oracle | 5 Comments

By Franck Pachot . You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer…

Read More
Nicolas Jardot

UKOUG 2012: Day 2 – Not only about Oracle Optimizer (CBO)

By | Technology Survey | No Comments

Yesterday was a very busy day at UKOUG 2012: many technical sessions about performances and also a time travel back in the evening. I really like sessions about performances (Oracle Optimizer) and Oracle behinds, but I will not focus on the technical side because it will take too much time (maybe in future blogs!). I will just summarize a little the main interesting topics. I had the opportunity to attend several sessions during this day…

Read More