When building products that interact with multiple database platforms, the complexity can be both a challenge and an opportunity. For Subject Matter Experts (SMEs), observing design decisions made without sufficient knowledge of underlying database architecture can be particularly frustrating. These moments highlight the critical need for architectural foresight and platform-specific expertise to avoid pitfalls that compromise scalability, performance, and maintainability. I’ve been managing multiple platforms for a couple decades and in recent years, building products that support the most popular enterprise and open-source databases today. I’m keenly aware of lacking documentation, either due to limited resources or hopes of directing…
-
-
Every so often a DSS query that usually takes 10 minutes ends up taking over an hour. (or one that takes an hour never seems to finish) Why would this happen? When investigating the DSS query, perhaps with wait event tracing, one finds that the query which is doing full table scans and should be doing large multi-block reads and waiting for “db file scattered read” is instead waiting for single block reads, ie “db file sequential read”. What the heck is going on? Sequential reads during a full table scan scattered read query is a classic sign of reading…
-
What is right deep verses left deep? Good question. In join trees (not VST) the object on the left is acted upon first then the object on the right. Below are left deep and right deep examples of the same query, showing query text join tree join tree modified to more clearly show actions VST showing the same actions All of this boils down to the point that a right deep HJ can return rows earlier than a left deep HJ. A left deep HJ has to wait for each join to finished completely so that the result set can be hashed before the next…
-
Anyone who has been around Oracle performance over the years knows the grief that hard parsing SQL queries can cause on highly concurrent applications. The number one reason for hard parsing has been applications that don’t use bind variables. Without bind variables queries that would otherwise be shared get recompiled because their text is different and Oracle treats them as different queries. Oracle addressed this issue with a parameter called cursor_sharing. The parameter cursor_sharing has three values exact – the default similar – replace literals with bind variables, if a histogram keep literal in place force – replace literals with…
-
When running the same query multiple times, several questions come to mind: Does the query always execute in the same amount of time? If some executions are slower, what is the slowest execution time? When did the slowest exectution happen? What more can I find out about the slowest exectution? All of this can be answered from data in Active Session History or ASH. The following query finds the maximum, minimum and average execution times in seconds as well as the time of the slowest execution which is given by start time and end time of the slowest exectuiton:…