Articles tagged

03 February 2017
03 February 2017

Understanding SQL Query Parsing: Part 4 – Understanding Bind Variable Peeking

3
In the 3rd part of this series, I introduced you to the concept of the bind variables. We also learned their importance in regard to cursors, i.e. how with the presence of bind variables, cursor reparsing can be reduced significantly. But as is famously said in the world of performance tuning, there is nothing black or white: it’s all grey… Read more
28 September 2016
28 September 2016

Investigating Indexes

0
Rebuilding indexes is an activity that you shouldn’t need to do often. There are always a few special cases – like when you’ve moved or recreated a table or if you’ve done a massive delete on a table – when it’s probably a reasonable idea but, in general, there are very few cases where there’s any great benefit to b… Read more
05 September 2016
05 September 2016

Text Indexes

0
The Oracle database has many features that are not spoken of frequently and a recent note by Connor McDonald reminded me that there are a couple of notes about optimising Text indexes that have been on my “must write” list for several years. If you’re not familiar with what text indexes can do for you, here’s a thumbnail sketch: creat… Read more
16 June 2016
16 June 2016

Defragmentation Can Degrade Query Performance

2
As data in tables is updated and / or deleted, pockets of empty space can be created, leading to fragmented free space. This free space is wasted space as it is insufficient to hold new data. Moreover, it can impact performance of the database as a higher number of sparsely-populated blocks need to be visited. Oracle 11g introduced online segment… Read more
29 January 2016
29 January 2016

Massive Deletes – Part1

3
In a recent article about dropping columns from a table I left hanging the question of what you might do after you have dropped the column, because dropping a column is really nothing more than a special case of the “massive delete”. In this article, then, I’m going to lay the groundwork for how we ought to think around t… Read more
and 11 October 2012
and 11 October 2012

Tune Your Indexing Strategy with SQL Server DMVs

SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.… Read more
25 September 2012
25 September 2012

Getting Started With XML Indexes

XML Indexes make a huge difference to the speed of XML queries, as Seth Delconte explains; and demonstrates by running queries against half a million XML employee records. The execution time of a query is reduced from two seconds to being too quick to measure, purely by creating the right type of secondary index for the query.… Read more