Search

OakieTags

Who's online

There are currently 0 users and 29 guests online.

Recent comments

Affiliations

Oracle

EM12c- Dealing with Unknown Targets

Most of you know I don’t like #ff00ff;">pink, but I also am not a fan of #ffff00;">yellow.

sql_trace

Here’s a convenient aid to trouble-shooting that appeared in 11g with its enhancements to setting events. It’s a feature that may help you to work out (among other things) why a given statement seems to have a highly variable performance profile. If you can find the SQL_ID for a parent cursor you can enable tracing for just that cursor whenever it executes, whoever executes it.

DBA Kevlar is BACK!! :)

So back at the end of the EM CAB, I’d received an email stating I had an over-sized database and too many hits to continue on Go Daddy as the host for my website.  I called into tech support to understand what options I had, but was never told that I had any option but leaving Go Daddy as my hosting service.  I was told I had two weeks per the email, but asked for three and was told on the phone that wasn’t a problem.  I then did some research a

Oracle Midlands : Event #4 – Registration Open

Registration has opened for the Oracle Midlands Event #4 on Monday July 14th.

This event includes a session on “Designing Efficient SQL” by Jonathan Lewis as well as lightning talks by Jonathan Lewis, Richard Harrison, Salih Oztop, Patrick Hurley and Martin Widlake.

Oracle Midlands : Event #3 Summary

I really enjoyed Oracle Midlands Event #3 last night. Christian Antognini spoke on “12c Adaptive Query Optimization” and “Row Chaining and Row Migration Internals”. I certainly learnt a lot, which is the whole point of this stuff!

After the event a few of us went across to a local pub and the geek talk continued. Cool++.

Thanks very much to Christian for coming all that way to speak to us. Thanks to those good people at Reg Gate for sponsoring the event. Thanks also to the Oracle ACE Program for letting Christian come to visit us. :)

The next event is on Monday 14th July. Hope to see you there!

Ignoring Hints

Does Oracle ignore hints – not if you use them correctly, and sometimes it doesn’t ignore them even when you use them incorrectly!

Here’s an example that I’ve run on 11.2.0.4 and 12.1.0.1


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum			id,
	rownum			n1,
	rpad('x',100)		padding
from
	generator	v1
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);
end;
/

create index t1_i1 on t1(id);
alter index t1_i1 unusable;

select n1 from t1 where id = 15;
select /*+ index(t1 (id)) */ n1 from t1 where id = 15;

Any guesses about the output from the last 4 statements ?

Combining Bloom Filter Offloading and Storage Indexes on Exadata

Here’s a little known feature of Exadata – you can use a Bloom filter computed from a join column of a table to skip disk I/Os against another table it is joined to. This not the same as the Bloom filtering of the datablock contents in Exadata storage cells, but rather avoiding reading in some storage regions from the disks completely.

So, you can use storage indexes to skip I/Os against your large fact table, based on a bloom filter calculated from a small dimension table!

How Exadata smartscans work

I guess everybody who is working with Oracle databases and has been involved with Oracle Exadata in any way knows about smartscans. It is the smartscan who makes the magic happen of full segment scans with sometimes enormously reduced scan times. The Oracle database does smartscans which something that is referred to as ‘offloading’. This is all general known information.

But how does that work? I assume more people are like me, and are anxious to understand how that exactly works. But the information on smartscans is extremely scarce. Of course there is the Oracle public material, which looks technical, but is little/nothing more than marketing. On My Oracle Support, I can’t find anything on the inner working. Even in the ‘Expert Oracle Exadata’ book (which I still regard as the best source of Exadata related information) there is no material on the mechanics of smartscans.

VirtualBox 4.3.12

VirtualBox 4.3.12 was been born yesterday. It’s a maintenance release, so a number of things have been fixed. The downloads and changelog are in the usual places.

Seems to install and work fine on Mac, Linux and Windows. :)

Cheers

Tim…

 

Subquery with OR

Prompted by a pingback on this post, followed in very short order by a related question (with a most gratifying result) on Oracle-L, I decided to write up a note about another little optimizer enhancement that appeared in 12c. Here’s a query that differs slightly from the query in the original article: