Search

Top 60 Oracle Blogs

Recent comments

November 2015

CLOBS, from 11g to 12c

If you are a regular user of LOB’s in the database, take care when you switch from 11g to 12c.  Notice the subtle difference:

In 11g, you are permitted to, and recommended to, use SECUREFILE lobs, but they are not the default


SQL> select name, value
  2  from v$parameter
  3  where  name = 'db_securefile';

NAME                                     VALUE
---------------------------------------- ---------------
db_securefile                            PERMITTED

In12c, SECUREFILE lobs will be the default.

AIOUG Sangam15 : The Journey Begins

The day started pretty early. I was packing at 00:00 and the alarm went off at 05:00. Nothing like last minute packing to focus the mind. :)

The taxi to the airport was good. The driver was interesting, speaking to me about his visits to India and Pakistan. It certainly helps pass the time when the driver is chatty.

howto: github – talks branch

A couple of months ago I did a presentation and decided to put the files up in github (https://github.com/karlarao/conference_eco2015) just because I wanted to move away from dropbox for my scripts and resources. Well, that was very convenient because the audience can just download the master zip file and that’s it! But then I figured I don’t want to have the next presentation on another repo and it would look pretty messy on my git account. Ultimately I’d like all the presentations to be on one repo and only separated by folders but then by default if you just put it that way then the “download zip” will download all of the conference folders.

Lightweight application performance monitoring

Monitoring application performance, is always difficult. Sometimes you want to monitor every aspect (with a minor latency impact) and sometimes you just want to monitor the basics. Like counting how many times a specific business process was called, or measuring how long it took. You also want it to be lightweight, and you want it to be really simple. As in really really simple.

OOUG RAC day presentation files and scripts

Thanks for coming to my presentations in RAC day at Dublin, Ohio. Please find the presentation files below. Hopefully, I will get video files and upload that here too.

OOUG presentation files and scripts

md5 checksum of the zip file is:

$md5sum ooug_2015_pdf.zip
df8bdcbc02926e5bbd721514b473bf16  ooug_2015_pdf.zip

Oracle Midlands : Event #12

Just a quick reminder, Oracle Midlands Event #12 is just around the corner.

Update: The first talk is now “Why use PL/SQL?” by Bryn Llewellyn.

This is the day after I get back from India, right after my first day back at work. It’s going to be really hard to drag myself there, but I know it will be worth it!

Cheers

Tim…

 

 

 

You dont need that sequence number

I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code:


SELECT my_sequence.nextval INTO :some_variable FROM dual;
 
INSERT INTO my_table VALUES (:some_variable, ....);

My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before you inserted it ? It is simply, easier, and more efficient just to get it back from the insert statement itself.


INSERT INTO MY_TABLE (seq_col, ...)
VALUES (my_sequence.nextval, ...)
RETURNING seq_col INTO :some_variable;

And with 12c, sequences can now be nominated as part of the DEFAULT value for a column, so you don’t need to refer to it at all…and you STILL can have the number

Oracle 12c in-memory option and IO

This article is about the Oracle 12c in-memory option, and specifically looks at how the background worker processes do IO to populate the in-memory column store.

Hardware: Apple Macbook with VMWare Fusion 7.1.3.
Operating system: Oracle Linux 6.7, kernel: 3.8.13-118.el6uek.x86_64.
Database version: Oracle 12.1.0.2
Patch: opatch lspatches
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)
19303936;Database Patch Set Update : 12.1.0.2.1 (19303936)

But first things first, let’s setup the in-memory option first with a test table. The first thing to consider is to create the in-memory area to store the objects. I only want a single table stored in the in-memory area, so I can very simply look at the size of object:

Disappearing Histograms

In general once you have a histogram on a column you keep it.  However, this is not because DBMS_STATS simply maintains it because it is there, but because it is still appropriate to have it based on the column workload usage.
Since Oracle 10g, the default method for collecting histograms is AUTO, which means that Oracle determines whether to collect a histogram based on data distribution and the workload of the column.
What is less well known (including to me until recently), is that histograms can be removed if there is no column workload to justify them.

This can be an issue when for some reason you have imported statistics on a freshly rebuilt table with no column usage.  I think the first two scenarios are the most likely:

TABLE ACCESS INMEMORY FULL – but there may be more

While preparing to teach a class this week I have had the time to look into the In Memory (cost) option a bit closer. I noticed a few interesting things along the way and would like to share one of these here.

Background

One of the questions I was asking myself was:

“What happens if I scan segments that are within the IM area, and some are not?”

I was primarily thinking of joins in a DWH environment, but in order to keep the test case simple enough and reproducible I decided to go with a partitioned table where the current partition is assigned to the IMCS, and the rest is not. For this test I’m relying on the Swingbench SH schema. All of this runs on my laptop in a VM so I had to be selective when it comes to indexes. I also chose to NOT partition the tables at this stage, I wanted to chose my own partitioning scheme. For reference, here is the command that created the SH schema: