Search

Top 60 Oracle Blogs

Recent comments

performance

[Oracle] Insights into SQL hints - Embedded global and local hints and how to use them

Introduction

The idea for this blog post started a few weeks ago when i had to troubleshoot some Oracle database / SQL performance issues at client site. The SQL itself included several views and so placing hints (for testing purpose) into the views was not possible, especially as the views were used widely and not only by the SQL with the performance issue. In consequence this blog post is about the difference between embedded global and local hints and how to use them.

 

Friday Philosophy – Being Rejected by the Prom Queen

If you follow me on twitter (and if you are on twitter, why would you *not* follow me :-) See Twitter tag on right of page -> ) you will know what the title is all about. I posted the below on my twitter feed a few weeks ago:

Submitting to speak at #OOW15 is like asking out prom queens. You live in hope – but expect rejection :-)

{BTW if prom queens are not your thing and you would rather be asking out the captain of the football/ice hockey/chess team, the vampire slayer or whatever, just substitute as you see fit.}

SQL vs. PL/SQL

Which piece of code will be faster (clue – the table in question has no indexes):

Option 1 – pure SQL


update join1 set
        data = data||'#'
where   key_1=500
and     key_2=23851
and     key_3=57012
and     key_4=521
and     key_6=1
and     key_7=23352
;

Option 2 – a silly PL/SQL row by row approach:

Investigating the full table direct path / buffered decision.

A lot of blogposts and other internet publications have been written on the full segment scan behaviour of a serial process starting from Oracle version 11gR2. This behaviour is the Oracle engine making a decision between scanning the blocks of a segment into the Oracle buffercache or scanning these blocks into the process’ private process global area (PGA). This decision is even more important on the Exadata platform, because the Oracle engine must have made the decision to read the blocks into the process’ PGA in order to be able to do a smartscan. This means that if you are on Oracle 11gR2 already, and thinking about using the Exadata platform, the wait event ‘direct path read’ gives you an indication on how much potentially could be offloaded on Exadata, if you keep all the settings the same.

[Oracle] DB Optimizer Part XII - Revealing SQL Plan Directive details for existing/loaded cursor from CBO (and SQL Dynamic Sampling Services) trace

Introduction

The idea for this blog post is based on a recent Twitter discussion with Martin Berger, Martin Bach and Mauro Pagano about revealing SQL Plan Directive details for an existing cursor as walking through the standard Oracle data dictionary views can be very time consuming/slow and there are still some details missing about the dynamic sampling task itself, even if you have found what you are looking for.

 

With Modern Storage the Oracle Buffer Cache is Not So Important.

With Oracle’s move towards engineered systems we all know that “more” is being done down at the storage layer and modern storage arrays have hundreds of spindles and massive caches. Does it really matter if data is kept in the Database Buffer Cache anymore?

Yes. Yes it does.

Time for a cool beer

Time for a cool beer

With much larger data sets and the still-real issue of less disk spindles per GB of data, the Oracle database buffer cache is not so important as it was. It is even more important.

If you use swingbench take note of sbutil

This is going to be a very short post for a change. I have used Swingbench extensively and really love the tool. Many thanks to Dominic Giles!

Recently he announced a new tool on his blog that you can use to inflate your data volume. So instead of using the “-scale” argument when executing oewizard you can just keep the defaults and later on create as much data as you like. Here is an example, the reason for this post.

Setting the Scene

Additional information on Oracle 12c big table caching

Teaching is on the things I like doing, and currently I am investigating the Oracle 12c features around caching data in the various memory areas. Since the In-Memory (cost) option has been discussed by other far more knowledgeable people I would like to share some findings about the big table caching here.

Some Background

In Oracle 12c you have two additional options to cache information: full database caching and big table caching. The first is great if you have a massively big machine with lots and lots of DRAM plus a clever OS that can deal with the inevitable ccNUMA setup you will have to tackle. And maybe don’t want to pay for the In-Memory option. This post is not about full database caching, but rather about the other possibility to cache blocks.

Efficiency

Here’s a question to which I don’t know the answer, and for which I don’t think anyone is likely to need the answer; but it might be an entertaining little puzzle for thr curious.

Assume you’re doing a full tablescan against a simple heap table of no more than 255 columns (and not using RAC, Exadata, In-memory, etc. etc. etc.), and the query is something like:


select  {columns 200 to 250}
from    t1
where   column_255 = {constant}
;

To test the predicate Oracle has to count its way along each row column by column to find column 255. Will it:

Exadata Fast Data File Creation

This post is the result of some testing I performed on Exadata data file creation. You may know that Exadata offloading incorporates SQL optimisations as well as some infrastructure work. For quite some time Exadata allowed the DBA to create data files a lot quicker than on traditional systems. This has been documented before, for example by @mpnsh here