Search

Top 60 Oracle Blogs

Recent comments

July 2012

Proactive Tuning

There’s no such thing as proactive tuning. Either you do it right before it goes into production (in which case it’s called “design”) or you react to the nasty surprises you get after it’s gone live.

Even if you do something about the problems before anyone has complained that’s still “reactive” not “proactive” – and the trick is to be reactive as soon as possible. For most companies the best strategy is probably to stick a big screen on the wall with the “Top Activity” page from OEM (or equivalent) so that the key players (DBAs and Developers) can keep a casual eye out for resource hogs and prioritise dealing with them as they appear.

Hate the player, not the game — or my case for Data Engineers

Has “database” become a dirty word within your organization lately? If you’re someone who has been a data technologist for the better part of your career, you may be wondering why the technologies you work with everyday seem to be acquiring such a bad rap. From NoSQL to No DB the current influx of brogrammers seem to take extreme pride in describing how they’re able to write code while avoiding any kind of database technology whatsoever.

The impetus for this post actually started with something I read on the ‘Net the other day about Command Query Responsibility Segregation (CQRS), and how I was initially excited about the concept.

Martin Fowler has a nice, gentle introduction the topic here.

OTN Tour of Latin America: Trinidad & Tobago…

We arrived at Trinidad & Tobago in the evening and it was hot and humid. I decided to wash some clothes in the hotel room sink, which was a mistake. It was so humid that even after about 36 hours my clothes were still damp and smelled a bit like wet dog when I packed them. Asking around it sounds like Guatemala will be just as humid, so I might have to use a hotel laundry service to get my clothes clean. :)

The linux ‘perf’ utility with EL 6

Some of you might have experimented with, or used Oprofile, ltrace/strace, Systemtap, and wished you could look further into the userland process, like dtrace does, because dtrace allows you to profile and look into a process’ function calls.

If you’ve used oprofile/strace/systemtap/etc., you probably found out you can see all the kernel functions which are processed, but it does not get into userspace, or with very limited information. Perhaps the only tool which is different is gdb, which enables you to see userspace function names, but gdb is not a profiler, it’s a debugger. And it works best with (I assume it made for) debugging symbols in the executable. Most (all I presume) commercial executables, like the oracle database executable, do not have debugging symbols.

Expanding SQL

Here’s a little thing that Dan Morgan mentioned to me some time ago. It’s a little routine from a package (owned by sys) that appeared in 11.2.0.3 that gives you some idea of the mess hidden behind a query that uses views. The procedure is dbms_sql2.expand_sql_text and takes two (CLOB) parameters, an IN parameter that is the text you want expanded, and an OUT parameter that is likely to be a long and messy piece of text. Rather than say any more, here’s an example of usage:

set linesize 70
set pagesize 0
set feedback off

declare
	m_sql_in	clob :='select * from dba_synonyms where owner = ''TEST_USER''';
	m_sql_out	clob := empty_clob();

begin

	dbms_sql2.expand_sql_text(
		m_sql_in,
		m_sql_out
	);

	dbms_output.put_line(m_sql_out);
end;
/

Random Plans

Have you ever felt that the optimizer was persecuting you by picking plans at random ? Perhaps you’re not paranoid, perhaps it’s part of Oracle Corp’s. master plan to take over the world. If you look closely at the list of hidden parameters you’ll find that some details of this cunning plan have leaked. In 10.1.0.2 Oracle created a new parameter _optimizer_random_plan with the description “optimizer seed value for random plans”. Who knows what terrible effects we may see when the default value of this parameter changes.

Scale Abilities shortlisted as UKOUG Partner of the Year

The UK Oracle User Group has revealed the shortlisted Partners for this year’s Partner of the Year Awards which features Oracle suppliers in a variety of areas including awards such as; E-Business Suite; Public Sector and Innovation Partner of the Year. We are pleased to announce that Scale Abilities has been shortlisted for Database Partner […]

InSync12 Conference: Melbourne 21-22 August 2012

I’ll again be presenting at this year’s InSync12 Conference, this year held in Melbourne on 21-22 August at the Sebel Albert Park Hotel. It’s Australia’s main Oracle conference of the year and should again be an excellent event with lots of great speakers. I’ll be presenting my paper on “Indexing In Exadata“, which should be [...]

Parallel Execution Challenge – It’s About Time

July 8, 2012 As I was reading a book, I saw a comment essentially stating that with multiple CPUs/cores, parallel execution will be faster than serial execution of the same query.  The book provided a test case - I decided to change around the test case a bit so that the results were a bit more fair, [...]

Forced Cursor Sharing And Virtual Columns

So you have that application that cannot be changed but makes use of some weird expressions that screw up the cardinality estimates of the optimizer.

Consider this simple example:

create table t as select rownum as id , case when mod(rownum, 100000) = 0 then 'y' else 'n' end as flag , rpad('x', 100) as filler from dual connect by level <= 1000000 ; exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1 for columns flag size 2', estimate_percent => 30) select flag, count(*) from t group by flag; explain plan for select count(*) from t where flag = 'y' ; set linesize 200 tab off pagesize 0 select * from table(dbms_xplan.display);