Sorted Hash Clusters have been around for several years, but I’ve not yet seen them being used, or even investigated in detail. This is a bit of a shame, really, because they seem to be engineered to address a couple of interesting performance patterns.
The basic concept is that data items that look alike are stored together (clustered) by applying a hashing function to generate a block address; but on top of that, if you query the data by “hashkey”, the results are returned in sorted order of a pre-defined “sortkey” without any need for sorting. (On top of everything else, the manuals describing what happens and how it works are wrong).
Here’s a funny little problem I came across some time ago when setting up some materialized views. I have two tables, orders and order_lines, and I’ve set up materialized view logs for them that allow a join materialized view (called orders_join) to be fast refreshable. Watch what happens if I refresh this view just before gathering stats on the order_lines table.
I have a little script that start with “set echo on”, then calls two packaged procedures, one to refresh the join view, the other to collect stats on the order_lines table; here’s the output from that script:
I’ve written a few notes about anomalies in subquery factoring (with subquery) in the past, principally making a fuss about the fact that moving an inline view into a “with subquery” can cause a plan to change even when the internal code moves the subquery back in line. With the arrival of 12c one of my first sets of tests was to rerun all the examples to see how many of them had been addressed. I hadn’t written about as many examples as I had thought, and some of them had been fixed before 12c, but here are few references to a couple of outstanding items that I thought worth a mention:
Now that 12c is out, here’s an idea that might save you some time even if you have no intention of migrating to, or even testing, the product for a couple of years. Download the “List of bugs fixed in 12c”: you may find that it’s the best starting point when you’re trying to solve a problem in your current version of Oracle.
A slightly more sophisticated version of the same thing – download and install the product, then take a dump of v$system_fix_control – that may also give you some insight into anomalies (that are not necessarily declared as bugs) in the way Oracle – and the optimizer in particular – behave. I updated the referenced note to add in a couple of figures for 12.1 – but one figure that’s not there is the number of database parameters: now at 368 (in my Beta 3 release) and 3,333 in the x$.
I’ll probably have to file this one under “Optimizer ignoring hints” – except that it should also go under “bugs”, and that’s one of the get-out clauses I use in my “hints are not hints” argument.
Sometimes an invisible index isn’t completely invisible.
Here’s a demonstration from 126.96.36.199 showing something which, to my mind, is a very annoying problem. The objects are in a tablespace that has been created with uniform extents of 1MB on an 8KB block size, using freelist management. I’ve rigged the Hakan factor to ensure that I get exactly 40 rows per block, and I’ve set the system statistics to ensure that a relatively small swing in cost results in a change in execution plan.
Actually it’s probably not the NOT IN that’s nasty, it’s the thing you get if you don’t use NOT IN that’s more likely to be nasty. Just another odd little quirk of the optimizer, which I’ll demonstrate with a simple example (running under 188.8.131.52 in this case):
Here’s a funny little bug – which probably won’t cause any damage – that may remind you that (most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming. It’s a bug I discovered by accident because I just wanted to check something about how a particular undo tablespace had been defined, and I called dbms_metadata instead of querying dba_tablespaces. Here’s the cut-n-paste from an SQL*Plus session on 184.108.40.206:
Here’s a suggestion to help you avoid wasting time. If you ever include the rowid in a query – not that that should happen very commonly – make sure you give it an alias, especially if you’re using ANSI SQL. If you don’t, you may find yourself struggling to work out why you’re getting an irrational error message. Here’s an example that appeared recently on the OTN forum, with the output cut-n-pasted from a system running 220.127.116.11:
select '1' from dual a left join ( select c.dummy, b.rowid from dual b join dual c on b.dummy = c.dummy ) d on a.dummy = d.dummy ; select * ERROR at line 1: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
The error doesn’t really seem to fit the query, does it?
If you want to bypass the problem all you have to do is give b.rowid (line 7) an alias like rid.
For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:
20:39:51 SQL> create table t1 (t1 timestamp); Table created. 20:39:55 SQL> insert into t1 values(systimestamp); 1 row created. 20:39:59 SQL> select t1 - systimestamp from t1; T1-SYSTIMESTAMP --------------------------------------------------------------------------- +000000000 04:59:50.680620 1 row selected. 20:40:08 SQL>
My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
18.104.22.168 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))
Here’s an oddity that I ran into a little while ago while trying to prepare a sample trace file showing a particular locking pattern; it was something that I’d done before, but trace files can change with different versions of Oracle so I decided to use a copy of 22.214.171.124 that happened to be handy at the time to check if anything had changed since the previous (11gR1) release. I never managed to finish the test; here are the steps I got through: