How to Gather SQL Resource Consumption Metrics in Oracle

Karen Morton on 26 April 2012 with 4 comments

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… Continue Reading →

Free eBook: Expert Oracle Database Architecture

All Things Oracle on 23 April 2012 with 0 comments

Download your FREE eBook Expert Oracle Database Architecture (Apress Media) provides a one-stop resource containing deep wisdom on the design, development and administration of Oracle applications, written by one of the world’s foremost Oracle experts, Thomas Kyte. This fully revised second edition covers the developments up to Oracle Database 11g. Each feature is taught in a proof-by-example manner, not only… Continue Reading →

Real Developers DO use Tools

Cary Millsap on 19 April 2012 with 0 comments

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… Continue Reading →

Oracle Cost-Based Optimizer Basics

Randolf Geist on 10 April 2012 with 0 comments

This video and set of resources is a follow up to the webinar broadcast by Red Gate on 11 Apr 2012. 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) optimizer_basics_inter_table_clustering_testcase.sql optimizer_basics_join_cardinality_testcase.sql… Continue Reading →

Session SQL

Jonathan Lewis on 17 February 2012 with 0 comments

From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like: select sess.sid,… Continue Reading →

An Easy Way to Obtain 10053 Trace

Syed Jaffer Hussain on 23 January 2012 with 0 comments

I have just learnt a couple of new methods to obtain an Optimizer (10053) trace for any sql statement that is already in the cache, without actually executing it and also enabling ‘events infrastructure’. With 11gR2 onwards, you can either use the DBMS_SQLDIAG.DUMP_TRACE package procedure or use new diagnostic events infrastructure to obtain the 10053 trace for a given sql_id…. Continue Reading →

Introduction to Materialized Views: Query Rewrite

David Njoku on 10 January 2012 with 0 comments

In the fairy tale, The Shoemaker and The Elves, a poor cobbler works to the best of his ability; and somehow, when he isn’t looking, magical beings would sneak in and polish and perfect his handiwork. It seems the people at Oracle are up on their fairy tales. Perfectly-tuned queries are the Holy Grail to developers and DBAs; we are… Continue Reading →

The Ramp

Cary Millsap on 20 December 2011 with 0 comments

I love stories about performance problems. Recently, my friend Debra Lilley sent me this one: I went to see a very large publishing company about 6 months after they went live. I asked them what their biggest issue was, and they told me querying in GL was very slow, and I was able to fix quite easily. (There was a… Continue Reading →

Indexes are Tables

Jonathan Lewis on 16 December 2011 with 3 comments

Oracle’s optimizer has many strategies for acquiring the data you want with the minimum of work. Some of these strategies are really quite subtle and sophisticated – think of the Star Transformation, for example, which uses a two-phase approach to dimension tables; or the Index Join which does hash joins between indexes to avoid visiting tables. Despite all the clever things… Continue Reading →

Which of these things is not like the other?

Jim Tilson on 11 November 2011 with 1 comments

Have you ever played the game where you try to identify the differences between two side-by-side seemingly-identical pictures? Sometimes answering the question “why does my query perform well in development, but it drags in test and production” is the same sort of game: you must identify where your environments differ, either obviously and subtly, then weigh those differences to determine… Continue Reading →