Top 60 Oracle Blogs

Recent comments

January 2013

Skip Scan 2

Here’s a question that is NOT a trick question, it’s demonstrating an example of optimizer behaviour that might come as a surprise.
I have an index (addr_id0050, effective_date), the first column is numeric, the second is a date. Here’s a query with an execution plan that uses that index:

No 3d charts in Excel? try R


I wanted to plot a set of data by 3 dimensions.

I wanted to plot I/O read latency by MB/s throughput by number of concurrent readers. Seemed simple. Well it turns out there is no good way to do it in Excel. Sure Excel has 3d charts but, attention, the z axis is treated like rows and not values. For example

Note that the z axis, “users” had 3 values marked  on the axis. Those 3 values are 1,16 and 64. Notice that  16 is as far from 1 as 64 is from 16, ie the distance is not proportional to the value.

There is a free plug-in for Excel called Excel3Dscatterplot, but the data is hard to read, for example

Luckily R saves the day. With R there are a number of easy ways to graph 3d data.

Nexus 4 : Update from LG over broken back panel…

I just got off the phone to LG about this piece of crap Nexus 4 phone. Their response is physical damage is not their responsibility, so I will have to pay for a repair. I suggested that designing a phone that will shatter when it is placed on a room temperature surface (yes, that really has happened to people) constitutes a design flaw and they should repair it for free. After much whining on my part their stance is unchanged at this time. I guess if enough people contact them to complain they will have to take responsibility…. Maybe…

So if you end up getting one of these super-fragile phones, please save yourself a lot of grief and buy a case that covers the back completely. It’s the only way you are going to make it past a week without breaking it.

Reviewing 2012 Search Terms

Since 2012 has drawn to a close I thought I’d take a gander at search terms that brought visitors to my blog over the past year.

Trivial pursuit, I guess.

Stale Stats

The question of how to list objects in need of stats collection appeared on the OTN database forum today from a DBA who had a system collecting stats at the weekend, but wanted to check whether there were any objects with stale stats during the week. It’s actually very easy to do this check because the schema and database calls to gather stats have an option to “list stale”; they also allow you to “list empty”, and “list auto” – the latter being the objects that would be subject to collection if you change the option to (the default) “gather”. “List auto” seems to be the concatenation of “list stale” and “list empty”, by the way.

So, after reading up the manual pages on dbms_stats ($ORACLE_HOME/rdbms/admin/dbmsstat.sql, or the PL/SQL Packages reference) all it takes is a little wrapping to do the job. The code below is a little overkill because it covers 6 different options in one go: