Search

Top 60 Oracle Blogs

Recent comments

January 2016

Common GATHER_PLAN_STATISTIC confusion

Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here

But here’s a common cause of confusion when using it in SQL Plus:

Enterprise Manager Processes, EM12c vs. EM13c

To assist users as they plan out their upgrades and new Enterprise Manager environments, I wanted to go over a few subtle, but important changes from EM12c, 12.1.0.5 to the newest release, the much anticipated EM13c, 13.1.0.0.

Bitmap Efficiency

An interesting observation came up on the Oracle-L list server a few days ago that demonstrated how clever the Oracle software is at minimising run-time work, and how easy it is to think you know what an execution plan means when you haven’t actually thought through the details – and the details might make a difference to performance.

The original question was about a very large table with several bitmap indexes, and an anomaly that appeared as a query changed its execution plan.  Here are the critical sections from the plans (extracted from memory with rowsource execution statistics enabled):

(Not So) Internal Dialogue

internal-dialogueWhen I wrote about rehearsals in my public speaking tips series, I mentioned talking through small sections of the presentations as a means for rehearsals. I do this a lot! I live on my own, so this is not an internal dialogue. I say this stuff out loud.

This morning I was talking through some ideas as I left the house and cleared the ice off the car. I continued during the journey to work, including when I got out of the car to get a coffee from the Costa Express at the garage. Even as I was unlocking the office door.

FIRST_ROWS vs FIRST_ROWS_n

You might be thinking that FIRST_ROWS is pretty much the same as FIRST_ROWS_1, since they are both after the first row. However, there are some important distinctions to make here. The traditional ‘FIRST_ROWS’ parameter is not a genuine cost-based option, more a hybrid between the rule and cost.

Add primary key.

I thought I had written this note a few years ago, on OTN or Oracle-L if not on my blog, but I can’t find any sign of it so I’ve decided it’s time to write it (again) – starting as a question about the following code:

Histograms on data (not the optimizer)

I’ve been doing a series on Analytics here but if you need to group data into ranges, you don’t necessarily need a fancy analytic.

Automatic date formats

Notice in all of the examples below that the date string does not match the format mask.

When a date conversion fails, Oracle tries some similar formats to try succeed. This is actually documented as well here

 

SQL*Plus quick tip

SQL Plus does not appear to be bothered by the direction of slashes, ie “/” or “\” in its scripts

Hence as long as you have a “C:\tmp” folder on your Windows drive (or wherever your SQL Pus binaary is installed/running from), you can do:


spool /tmp/blah
@/tmp/my_file.sql

and it will work on Unix and Windows without alteration.

(This also works for SQLcl as well by the way)

SAMPLE costing

People often think when using the SAMPLE clause, that because they are only dealing with a subset of the data, that immediately it should be a much faster operation. And whilst sometimes this is indeed the case, it is not a guarantee.