January 2017

Removing Unnecessary Indexes: 2. Identifying Redundant Indexes

This is the second post in a series about unnecessary indexes and some of the challenges that they present.
The EDB360 utility (see described on Carlos Sierra's Tools & Tips blog) contains a report of redundant indexes within a database. The query in this post (also available on my website) is based on the one in EDB360, but here the column list is produced with the LISTAGG analytic function.

Friday Philosophy – How I Took The Step Up from Thought Leader to Paradigm Architect.

I’m sure you have come across the term “Thought Leader” before. You probably first came across it recently, in the last couple of years, and it is growing in occurrence. I’m always impressed by someone who describes themselves as a Thought Leader, I think it tells you a lot about the qualities of the person (*). At times I feel that I am also a Thought Leader – However, I recently decided to set myself the higher ambition of expanding my vision and becoming a Paradigm Architect. And I think I can tell you how you can do the same and also become a Paradigm Architect yourself.

Do, or do not. There is no "try".

About 7 months ago, I was reading an article about some of the new features that were in javascript/ecma/coffeescript or whatever it is called now. One of the things that stuck out, was promises. I liked the idea of "asynchronous" execution with a possible chained dependency, that you could just define and run, and then carry on with other tasks without having to wait for the result. What you do instead, is you receive a "promise" of the execution. A promise that at some point will contain the result from your call.

The Oracle database has the capability to do this already in dbms_scheduler using chains, but it is not dynamic and the complete flow has to be defined up front.

Trusted Information Sharing – ABAC Architecture

In my previous post, I introduced you to the two concepts of Role-Based Access Control (RBAC) and Attribute-Based Access Control (ABAC). ABAC resolves a number of the limitations associated with RBAC, as I discussed in that post. In this post, I wanted to drill into the architecture underlying ABAC a little bit more.

In simple terms, there are four main parts of the ABAC architecture. These are:

jmeter – getting started

jmeter

This blog post is just a start at documenting some of my experiences with jmeter. As far as load testing tools go, jmeter looks the most promising to me. It has an active community, supports many different databases and looks quite flexible as far as architecting different work loads goes.

The flexibility of jmeter also makes it hard to use. One can use jmeter for many other things besides databases so the initial set up is a bit oblique and there look to be many paths to similar results. As such, my understand and method for doing things will probably change considerably as I start to use jmeter more and more.

I’m installing it on a mac and using RDS instances.

installing jmeter

Real-Time Materialized Views in #Oracle 12c

helpshttps://uhesse.files.wordpress.com/2015/10/helps.png?w=600&h=558 600w, https://uh

How to move Partitions ONLINE and make them READ ONLY in #Oracle 12c

readonlyhttps://uhesse.files.wordpress.com/2017/01/readonly.png?w=574&h=60

What you "liked" last year…

Well…when I say “liked”, what I mean is “the stuff you all clicked on a lot” last year. Whether you liked it or not will remain one of those great mysteries Smile

The top 6 posts from 2016 were:

https://connormcdonald.wordpress.com/2015/11/25/ora-14758-last-partition-cannot-be-dropped/

https://connormcdonald.wordpress.com/2013/01/20/exchange-partition-those-pesky-columns/

2016 DBAKevlar Year in Review

How was 2016 for me?

Graphics for SQL Optimization

Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is

  • Draw a diagram of each table in the query with Children above Parents
  • Draw join lines between each join (many-to-many, one-to-many)
  • Mark each table with a predicate filter and calculate the amount of table filtered out

Then to find a great optimal optimization path candidate

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.