I posted a couple of tweets yesterday about this crazy query I had to work with. I won’t Storify you to death, so I’ll sum it up quickly. 1269 lines (formatted, of course). 13 WITH clauses. 8 base tables. The error that was occurring was an Oracle one: ORA-00937, “not a single-group function.” That’s interesting because OBIEE, the BI Server,… Read more
Oracle Enterprise Manager 12c introduces the latest evolutionary stage of the Performance Pages demonstrated in the previous webinar. This session will demonstrate the new features such as ASH Analytics that make OEM more compelling and useful in more situations. A live Q+A session with Doug Burns will follow the presentation. Register now
… Read more
In the previous installment of this series we saw that Dynamic Sampling wasn’t of great help when dealing with certain types of data patterns. We tried to add a suitable index to improve the Dynamic Sampling results, but no luck so far. The solution in this case is that with statistics in place the index only gets used when adding… Read more
The following video and set of resources are from the webinar broadcast by Red Gate on 03 July 2012. Watch video Key performance analysis and tuning principles apply regardless of the version of Oracle you’re using, but the Oracle 10g Diagnostics Pack instrumentation and related OEM Performance Pages built on these principles to offer a new, more visual, performance analysis… Read more
In the previous parts of the series I outlined the basics on Dynamic Sampling. Let’s see how Dynamic Sampling deals with typical real-life data patterns. As you might be aware, quite often real data follows certain patterns for how data is clustered together, usually related to how the data arrives. This clustering (or scattering) of data within a table can… Read more
I’ve been impressed with the solutions to my little problem of generating a symmetric Pascal matrix using SQL. Charles Hooper in particular has provided some very nice commentary on the problem, complete with diagrams and 2 alternative solutions. I thought I’d walk through my solution in order to explain my thought process and see if it resonates with anyone. Usually when I think about… Read more
The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times. In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality… Read more
One thing that bothers a lot of PL/SQL developers is when they have to select some data from a table into local variables. The problem is that the data may or may not exist (i.e. return 0..1 rows). They’re several ways to write this code, some taking longer than others which is the pain point for developers. I’ve listed out… Read more
In this series of articles, we are discussing the collection of tools that make up the Oracle Flashback Technology suite. They are varied in their purpose and in their implementation, but together they act very much like a morning-after pill for your database, a means by which developers and administrators can protect their database from immediate past errors and indiscretions… Read more
One of the biggest problems in software performance today occurs when the people who write software are different from the people who are required to solve the performance problems that their software causes. It works like this: Architects design a system and pass the specification off to the developers. The developers implement the specs the architects gave them, while t… Read more
In the first in this series of articles on the tools in the Oracle Flashback Technology armoury, I discussed what is available to application developers, the various means by which they can rewind the clock and correct recently-past data errors. However, if this was a world in which the worst thing that could happen was a small mix-up with data,… Read more
For those not familiar with Richard Foote’s extensive blog about indexes (and if you’re not you should be) – the title of this note is a blatant hi-jacking of his preferred naming mechanism. It’s just a short note to remind myself (and my readers) that anything you know about Oracle, and anything published on the Internet – even by Oracle Corp. and… Read more
Imagine, if you will, that someone invented time travel. What would you do with it? Nip back into the past and fix that error you made? And maybe you’d want to do something about that silly, ill-advised haircut you had when you were 17. Well, the good news is that, in a manner of speaking, time travel has been invented… Read more
Introduction: Oracle Restart is a component added to the 11gR2 in order to improve the high availability of different database resources. Database instances, listeners are resources that can be automatically restarted after a hardware or software failure or whenever the server host restarts. These components can be easily added to the Oracle Restart using the crsctl add command. In this… Read more
The following video and set of resources are from the webinar broadcast by Red Gate on 7 June 2012. Oracle and SQL Server may both share a common language, but certain things are handled quite differently. Jonathan Lewis (OakTable Network, Oracle Ace Director) is used to seeing heap tables (almost) everywhere, but Grant Fritchey (Microsoft SQL Server MVP) is used… Read more
The following video and set of resources are from the webinar broadcast by Red Gate on 8 May 2012. The video above is accompanied by the following downloadable resources: Presentation slides (.pdf) Resource Consumption Metrics Examples (.txt) Workload Repository SQL Report (awrsqlrpt_1_16411_16412.html) SQL Monitoring Report (0-6x2sh0kvzac0t-2.html) ASH Report for BIUAT/biuat1 (ashrpt_1_0505_1117.html) Download all resources (.zip) SQL is utilized to return… Read more
This video is a recording from the webinar broadcast by Red Gate on 24th January 2012. You may’ve heard the idea that real developers don’t need tools, and the mark of a good developer is someone who can hand-code everything. But there are certain tasks where it doesn’t make sense to disregard using tools. In particular, critical tasks that need… Read more
Oracle RAC is designed to cater for Oracle specific applications and also non-oracle applications. These applications, both Oracle and non-Oracle, when deployed on RAC, are termed as resources and Oracle RAC can manage both kinds of resources on the clusterware stack. Examples of both oracle and non-oracle resources are: database instance, database services, xclock, Apache etc. The traditional way to… Read more
This video is a recording from the webinar broadcast by Red Gate on 29th March 2012. Oracle Database administrators have to cope with numerous challenges when migrating an existing non-cluster environment to a cluster environment. One of the most significant challenges a DBA faces is to migrate/convert an existing single instance to RAC. Oracle provides several easy methods to carry… Read more
This video and set of resources is a follow up to the webinar broadcast by Red Gate on 11 Apr 2012. Watch video The video above is accompanied by the following downloadable resources: Read me Presentation slides – PDF version Presentation slides – PowerPoint version Screenshots – Demo 1 (.pdf) Screenshots – Demo 2 (.pdf) Screenshots – Demo 3 (.pdf)… Read more
