Search

Top 60 Oracle Blogs

Recent comments

December 2013

When the Oracle wait interface isn’t enough, part 2: understanding measurements.

In my blogpost When the oracle wait interface isn’t enough I showed how a simple asynchronous direct path scan of a table was spending more than 99% of it’s time on CPU, and that perf showed me that 68% (of the total elapsed time) was spent on a spinlock unlock in the linux kernel which was called by io_submit().

This led to some very helpful comments from Tanel Poder. This blogpost is a materialisation of his comments, and tests to show the difference.

First take a look at what I gathered from ‘perf’ in the first article:

dbms_space usage

Just throwing out a brief comment (one of my many draft notes that I don’t have time to complete) about the dbms_space package. You’re probably familiar with this package and how, for ASSM segments, it can give you a measure of the available space in the blocks in a data segment, reporting 6 possible states of the blocks below the high high water mark (HHWM) e.g.:


Unformatted                   :       12
Freespace 1 (  0 -  25% free) :        0
Freespace 2 ( 25 -  50% free) :       14
Freespace 3 ( 50 -  75% free) :        0
Freespace 4 ( 75 - 100% free) :        0
Full                          :       34

The above is the output from a little procedure I wrote to wrap around a call to the procedure dbms_space.space_usage().

Data Flood

 tjblackwellIt is likely you are familiar with the statement that 71% of the Earth’s surface is water.  But did you know that overall 92% of the cost of business — the financial services business — is “data”?

http://www.wsta.org/resources/industry-articles/

 

 

 Data Flood

 

 

Production data is clearly critical for the core of businesses, such as

Unnest Oddity

Here’s a little oddity I came across in 11.2.0.4 a few days ago – don’t worry too much about what the query is trying to do, or why it has been written the way I’ve done it, the only point I want to make is that I’ve got the same plan from two different strategies (according to the baseline/outline/hints), but the plans have a difference in cost.

Here’s the definition of my table and index, with query and execution plan:

Readable Code for Modify_Snapshot_Settings

It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road cut-and-paste from this article instead and the world gets a few more lines of readable code as a result. :)

On a side note, let me re-iterate the importance of increasing the AWR retention defaults. There are a few opinions about the perfect settings but everyone agrees that the defaults are a “lowest common denominator” suitable for demos on laptops but never for production servers. The values below are what I’m currently using.

Readable Code for Modify_Snapshot_Settings

It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road cut-and-paste from this article instead and the world gets a few more lines of readable code as a result. :)

On a side note, let me re-iterate the importance of increasing the AWR retention defaults. There are a few opinions about the perfect settings but everyone agrees that the defaults are a “lowest common denominator” suitable for demos on laptops but never for production servers. The values below are what I’m currently using.

Readable Code for Modify_Snapshot_Settings

It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road cut-and-paste from this article instead and the world gets a few more lines of readable code as a result. :)

On a side note, let me re-iterate the importance of increasing the AWR retention defaults. There are a few opinions about the perfect settings but everyone agrees that the defaults are a “lowest common denominator” suitable for demos on laptops but never for production servers. The values below are what I’m currently using.

SQLPlus and white space in the output

I finally managed to work around My Most Annoying Problem (TM) with SQLPlus in my career. SQLPlus will mess up my output when I am copying/posting something in wordpress or elsewhere. Here’s an example to show you what I mean. The output in SQLPlus in my terminal window is all nice and pretty (it’s a print screen):

nice output

Many thanks to Jonathan Lewis and his presentation about generating test data by the way!

The above output looks nice at first glance, so I’m copying and pasting this into a document, only to see this (again a print screen; this time from TextWrangler):

Building vmware-tools in your Oracle Linux UEK3 VM

This is a quick writeup of an oddity I found while trying to install the vmwareware tools in an Oracle Linux host with the UEK3 kernel enabled (which is by default).

This is what is encountered during the vmware tools installation dialog when running vmwaretools.pl:

Searching for a valid kernel header path...
The path "" is not a valid path to the 3.8.13-16.2.2.el6uek.x86_64 kernel 
headers.
Would you like to change it? [yes]

The building of vmware tools fail because the kernel headers can not be found: the installer doesn’t see the kernel headers, whilst you probably installed it (it’s the kernel-uek-devel package belonging to the running kernel).

Data Pump Enhancements in Oracle Database 12c

Another one to file under “Not sexy but flippin’ awesome!”

If you are a DBA, you are going to spend a lot of time with Data Pump. All roads seem to lead back to it. :) There are some more headline worthy features, like transportable database, but the two that jumped out at me were actually pretty small, but awesome.

  • “TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y” – Switches table and/or index imports to NOLOGGING for the lifespan of the import operation.
  • “LOGTIME=ALL” – Puts a timestamp in the output message so you can see how long individual operations took.

I wrote up an article about it here.

Cheers

Tim…