A recent thread on the OTN database forum supplied some code that seemed to show that In-memory DB made no difference to performance when compared with the traditional row-store mechanism and asked why not. (It looked as if the answer was that almost all the time for the tests was spent returning the 3M row result set to the SQL*Plus client 15 rows at a time.)
The responses on the thread led to the question: Why would the in-memory (column-store) database be faster than simply having the (row-store) data fully cached in the buffer cache ?
Seamless cloning of an application stack is an outstanding goal. Seamless cloning of an application stack including the full production database, application server, and webserver in a few minutes with next to zero disk space used or configuration required is the best goal since Alexander Graham Bell decided he wanted a better way tell Mr. Watson to “come here.”
There has been a lot of activity on https://github.com/oracle lately. Apparently a place to keep…
Following on from a recent “check the space” posting, here’s another case of the code not reporting what you thought it would, prompted by a question on the OTN database forum about a huge space discrepancy in LOBs.
There’s a fairly well-known package called dbms_space that can give you a fairly good idea of the space used by a segment stored in a tablespace that’s using automatic segment space management. But what can you think when a piece of code (written by Tom Kyte, no less) reports the following stats about your biggest LOB segment:
January is winding down and RMOUG Training Days 2015 is just around the corner, taking up much of my after work hours.
With that, we are going to discuss a great performance console in the EM12c cloud control- Cluster Cache Coherency.
In an earlier (not very serious) post about count(*) I pointed out how the optimizer sometimes does a redundant “bitmap conversion to rowid” when counting. In the basic count(*) example I showed this wasn’t a realistic issue unless you had set cursor_sharing to “force” (or the now-deprecated “similar”). There are, however, some cases where the optimizer can do this in more realistic circumstances and this posting models a scenario I came across a few years ago. The exact execution path has changed over time (i.e. version) but the anomaly persists, even in 22.214.171.124.
First we create a “fact” table and a dimension table, with a bitmap index on the fact table and a corresponding primary key on the dimension table:
One thing you (ought to) learn very early on in an Oracle career is that there are always cases you haven’t previously considered. It’s a feature that is frequently the downfall of “I found it on the internet” SQL. Here’s one (heavily paraphrased) example that appeared on the OTN database forum a few days ago:
select table_name,round((blocks*8),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';
select table_name,round((num_rows*avg_row_len/1024),2)||’kb’ “size” from user_tables where table_name = ‘MYTABLE';
The result from the first query is 704 kb, the result from the second is 25.4 kb … fragmentation, rebuild, CTAS etc. etc.
A couple of days ago I highlighted an optimizer anomaly caused by the presence of an index with a descending column. This was a minor (unrelated) detail that appeared in a problem on OTN where the optimizer was using an index FULL scan when someone was expecting to see an index RANGE scan. My earlier posting supplies the SQL to create the table and indexes I used to model the problem – and in this posting I’ll explain the problem and answer the central question.
From those who are interested, hereby my slide deck I used during UKOUG Tech14, regarding…
I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 126.96.36.199 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases. Here’s some code to create a table and two indexes: