Search

OakieTags

Who's online

There are currently 0 users and 46 guests online.

Recent comments

Affiliations

SQL

On the Topic of Programming 1

August 26, 2012 (Forward to the Next Post in the Series) As those of you who have read this blog’s About page probably know, my day to day job responsibilities involve a lot of activities that are not specific to Oracle Database performance tuning, or even remotely Oracle DBA type activities.  Those extra acttivites are part of what [...]

Exclusion of Unioned SQL in Views – Followup

Last week I put up a post about how Oracle can filter out sections of a union view..

Within the comments I put up another example where the CBO did not filter out all but one of the Union views despite my replicating the exact WHERE clause of one of the unioned statements. Jonathan Lewis posted a followup to say “Oracle can be very touchy about how it plays this game” and made a prediction of how the CBO would handle a slightly different scenario.

This was the extra scenario and I include brief details on creating the unioned view too. NB all on Oracle 11.2.0.2. {non-Exadata :-) }

Exclusion of Unioned SQL in Views?

Question – you have a view definition of the following format:

select 1 as d_type, col_a,col_b,col_c
from TAB_X, TAB_Y, TAB_Z
where {your predicates}
UNION
select 2 as d_type, col_a,col_b,col_c
from TAB_P, TAB_Q, TAB_R
where {your predicates}
UNION
select 3 as d_type, col_a,col_b,col_c
from TAB_X X, TAB_Y, TAB_Z
where {your predicates}

You now select from the view and your code includes a filter predicate such as:

“WHERE D_TYPE = 1″

SQL Challenges

June 14, 2012 Dominic Delmolino put together a very interesting challenge.  The challenge is to produce something called a Pascal matrix using Oracle Database… more specifically, just SQL.  I had a vague recollection of Pascal matrixes when I read Dominic’s challenge.  Basically, the goal is to create a matrix similar to the following: The rule [...]

Shrinking Tables to Aid Full Scans

{This blog is about shrinking tables where the High Water Mark is higher than it needs to be. Prompted by one of the comments, I wrote a follow-up post on finding the High Water Mark and tables that consist mostly of empty space, which would be candidates for shrinking.}

This blog is about one of those things I do as almost an autonomous “not thinking about it” performance housekeeping task, one which I have been meaning to mention for ages.

There can be quite a lot to gain by checking out full scans on “small” tables and seeing if it is as efficient as it can be. Sometimes it is, sometimes it is not. Often it is “good enough”. Occasionally it is awful.

Recently I was just casting an eye over the “top 20″ SQL on a system for any code generating a lot of consistent gets. I came across the below:

Reproducing a Canned Report using a Single SQL Statement

April 11, 2012 I recently received an interesting request for assistance from an ERP email mailing list.  The author of the email wanted to reproduce a canned report found in the ERP package so that the information could be published on a Microsoft Sharepoint system.  The author of the email is using SQL Server for [...]

Monitoring Changes to Table Data

March 22, 2012 Suppose that you receive a request stating that a particular table in one of your databases must be monitored for changes.  One such table might be the one that lists the ERP system’s suppliers and their addresses – you would not want someone to be able to set up a legitimate supplier, and later [...]

V$SQL.IS_OBSOLETE

The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 11.2.0.3 instance.

Starting with 10g oracle introduced SQL_ID for simplicity; it is used in combination with CHILD_NUMBER to locate a particular row in the V$SQL. The documentation is clear (bold is mine):

V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered

SQL Quiz – How To Multiply across Rows

A colleague came to me a couple of days ago with a SQL problem. He had something like this:

@get_source

NAME          INPUT
------------- -----
GROUP_1       5
GROUP_2       3
GROUP_3       4
GROUP_4       7
GROUP_5       3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries

My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular :-)

I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?

I quickly realised that the problem was specific to one tablespace: