Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

January 2016

Common GATHER_PLAN_STATISTIC confusion

Submitted by connor_mc_d on Fri, 2016-01-29 07:31

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

Submitted by dbakevlar on Fri, 2016-01-29 01:39

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

Submitted by Jonathan Lewis on Thu, 2016-01-28 14:02

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

Submitted by oraclebase on Thu, 2016-01-28 11:52

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

Submitted by connor_mc_d on Thu, 2016-01-28 00:31

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.

Submitted by Jonathan Lewis on Wed, 2016-01-27 10:07

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)

Submitted by connor_mc_d on Wed, 2016-01-27 07:57

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

Submitted by connor_mc_d on Wed, 2016-01-27 07:53

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

Submitted by connor_mc_d on Wed, 2016-01-27 05:01

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

Submitted by connor_mc_d on Wed, 2016-01-27 04:59

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.