Search

Top 60 Oracle Blogs

Recent comments

Oracle

Hacking for Skew

In my presentation to the UKOUG SIG yesterday “Struggling with Statistics – part 2” I described a problem that I wrote about a few months ago: when you join a fact table with a massively skewed distribution on one of the surrogate key columns to a dimension holding the unique list of keys and descriptions a query against a description “loses” the skew. Here’s an demo of the problem that’s a little simpler than the one in the previous article.

Oracle write consistency, bug, and scalable multi-thread de-queuing

A new blog post on the Databases at CERN blog:

A write consistency bug, how to see it with flashback query, and a scalable workaround.

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-oracle-write-consistency-bug-and-multi-thread-de-queuing

Oracle write consistency bug and multi-thread de-queuing

Column Group Catalog

I seem to have written a number of aricles about column groups – the rather special, and most useful, variant on extended stats. To make it as easy as possible to find the right article I’ve decided to produce a little catalogue (catalog) of all the relevant articles, with a little note about the topic each article covers. Some of the articles will link to others in the list, and there are a few items in the list from other blogs. There are also a few items which are the titles of drafts which have been hanging around for the last few years.

Three Months at Microsoft

Time Flies when you’re having fun or when you’re crazy busy-  I’m unsure which is more true.

I just completed my first review as an employee at Microsoft and good news!  They’re going to keep me. </p />
</p></div>

    	  	<div class=

Start/Stop your Autonomous Databases

The ATLAS experiment in LEGO®

Here are two blog posts on the Databases at CERN blog:

How to gather Oracle optimizer statistics with minimal risks of regression

The ATLAS experiment control room

Here is, on the Databases at CERN blog, an example to lower to risks when you need to gather statistics in production:

  • use pending stats to be able to test them before publishing
  • be ready to restore old ones if a critical regression comes after publishing

The whole demo (I encourage you to follow the Databases at CERN blog):

https://db-blog.web.cern.ch/blog/franck-pachot/2018-09-optimizer-statistics-gathering-pending-and-history

Here is a summary of commands used, for an easy copy/paste:

Column Stats

A little while ago I added a postscript about gathering stats on a virtual column to a note I’d written five years ago and then updated with a reference to a problem on the Oracle database forum that complained that stats collection had taken much longer after the addition of a function-based index. The problem related to the fact that the function-based index was supported by a virtual column that used an instr() function on a CLOB (XML) column – and gathering stats on the virtual column meant applying the function to every CLOB in the table.

So my post-script, added about a month ago, suggested adding a preference (dbms_stats.set_table_prefs) to avoid gathering stats on that column. There’s a problem with this suggestion – it doesn’t work

POUG 2018

I just returned from a great Technical Oracle Conference hosted in Poland by my friends Kamil (@ora600pl) and Luiza (@LuizafromPoland)! They were kind enough to allow me to speak on the topics of SQL Translation Framework and Advanced Query Rewrite. It was my second year in a row to attend this conference and I must say I was extremely impressed by the content and the speakers and the venue and the professional handling of the logistics. Great job all around! I’d highly recommend this conference, so stay tuned for announcements on POUG 2019. Here’s a shot of the Enkitec guys that spoke at the conference: @phurley @JulianDontcheff @fritshoogland

Stats time

I wrote a note a couple of years ago explaining how I used to get a rough idea (with some errors) of how much time was spent in the overnight stats collection by each object. One of the nice little enhancements that appeared in 12c was the appearance of a couple of functions that can report information about this type of thing, and more. These are the dbms_stats function report_stats_operations() and report_single_stats_operation() with the following definitions: