Oracle

Jonathan Lewis's picture

count(*) – again !

Because you can never have enough of a good thing.

Here’s a thought – The optimizer doesn’t treat all constants equally.  No explanations, just read the code – execution plans at the end:

kevinclosson's picture

Recommended Reading: Oracle Database 12c NUMA-Related Topics

This is a short post to recommend some recent blog posts by Nikolay Manchev and Bertrand Drouvot on the topic of Oracle Database 12c NUMA awareness.

Nikolay provides a very helpful overview on Linux Control Groups and how they are leveraged by Oracle Database 12c. Bertrand Drouvot carried the topic a bit further by leveraging SLOB to assess the impact of NUMA remote memory on a cached Oracle Database workload. Yes, SLOB is very useful for more than physical I/O! Good job, Bertrand!

These are good studies and good posts!

Also, one can refer to MOS 1585184.1 for more information on Control Groups and a helpful script to configure CGROUPS.

The following links will take you to Nikolay and Bertrand’s writings on the topic:

Jonathan Lewis's picture

Most Recent

There’s a thread on the OTN database forum at present asking for advice on optimising a query that’s trying to find “the most recent price” for a transaction given that each transaction is for a stock item on a given date, and each item has a history of prices where each historic price has an effective start date. This means the price for a transaction is the price as at the most recent date prior to the transaction date.

Jonathan Lewis's picture

Count (*)

The old chestnut about comparing speeds of count(*), count(1), count(non_null_column) and count(pk_column) has come up in the OTN database forum (at least) twice in the last couple of months. The standard answer is to point out that they will all execute the same code, and that the corroborating evidence for that claim is that, for a long time, the 10053 trace files have had a rubric reporting: CNT – count(col) to count(*) transformation or, for an even longer time, that the error message file (oraus.msg for the English Language version) has had an error code 10122 which produced (from at least Oracle 8i, if not 7.3):

Jonathan Lewis's picture

Re-optimization

The spelling is with a Z rather than an S because it’s an Oracle thing.

Tim Hall has just published a set of notes on Adaptive Query Optimization, so I thought I’d throw in one extra little detail.

When the optimizer decides that a query execution plan involves some guesswork the run-time engine can monitor the execution of the query and collect some information that may allow the optimizer to produce a better execution plan. The interaction between all the re-optimization mechanisms can get very messy, so I’m not going to try to cover all the possibilities – read Tim’s notes for that – but one of the ways in which this type of information can be kept is now visible in a dynamic performance view.

martin.bach's picture

Installing Oracle 12.1.0.2 RAC on Oracle Linux 7-part 1

Now that 12.1.0.2 is certified on RedHat Linux 7 and spin-off environments it’s time to test the installation of RAC on such a system.

The installation of the OS is different from Oracle Linux 5 and 6-with these distributions was very straight forward how to install the operating system the method has changed significantly in release 7. I won’t cover the complete installation here, as always Tim Hall was quicker than me, but it makes me wonder who signed off the user interface for the partitioning “wizard”… I personally think that the kickstart partitioning-information is a lot easier to understand.

http://oracle-base.com/articles/linux/oracle-linux-7-installation.php

harald's picture

Cross-platform Transportable Tablespaces made easy

Back in Oracle8i the Transportable Tablespace feature was introduced to make it convenient to transport a large amount of data between databases. In Oracle10g this useful feature was enhanced with cross-platform support which allowed a tablespace, or set of tablespaces, to be transported between databases deployed on different hardware platforms (even between platforms with a […]

Jonathan Lewis's picture

Table Duplication

I’ve probably seen a transformation like the following before and I may even have written about it (though if I have I can’t the article), but since it surprised me when I was experimenting with a little problem a few days ago I thought I’d pass it on as an example of how sophisticated the optimizer can be with query transformation.  I’ll be talking about the actual problem that I was working on in a later post so I won’t give you the table and data definitions in this post, I’ll just show some SQL and its plan:

dbakevlar's picture

2014 Year in Review

As many bloggers and sites do this time of the year, here is my review of 2014. It was a great year and it was a lot of fun, as well as educational reviewing all the data.

DBAKevlar Blog

Busiest Day on my Blog:

Jonathan Lewis's picture

Just in case

For those who don’t read Oracle-l and haven’t found Nikolay Savvinov’s blog, here’s a little note pulling together a recent question on Oracle-L and a relevant (and probably unexpected) observation from the blog. The question (paraphrased) was:

The developers/data modelers are creating all the tables with varchar2(4000) as standard by default “Just in case we need it”. What do you think of this idea?

Syndicate content