Search

Top 60 Oracle Blogs

Recent comments

Exadata

Mysterious new Oracle compression type

As part of our research for our joint presentation at OGH.nl and E4 my colleague Frits Hoogland made an interesting discovery. He verified the compression format for updated rows previously compressed using Hybrid Columnar Compression (HCC). In that particular example we researched HCC data on Exadata storage. As you may know, reading HCC data in its compressed form is limited to Exadata, Pillar Axiom, and the ZFS Storage Appliance (ZFSSA).

Background

Compression Units – 6

I received an email recently asking me if I knew how Oracle found specific rows and columns in a compression unit. This is a topic that I’ve spoken about a couple of times, and I’ve published several notes on the blog about it, including an image of a critical slide from one of my presentations, and I was expecting to find some notes somewhere about Oracle catalogues all the bits and pieces.

“external table write” wait events… but I am only running a query?

I was helping a customer debug some external table load problems.  They are developing some code to do massive inserts via external tables.  As the code was being tested, we saw a fair number of tests that were doing simple queries of an external table.  I expected to see “external table read” wait events, but was surprised when we saw more “external table write” wait events than reads.

I thought this was due to writes to the “log” file and possible “bad” file, but I had to be sure.  I searched the docs but could not find reference to this wait event.  I specifically was seeing the following:

How important is a Disaster Recovery site for you?

I regularly read threads on the oracle-l mailing list, and occasionally feel very tempted to reply to one. Just recently I saw one that I liked a lot. It is specifically about using an Oracle Database Appliance (ODA) as a Disaster Recovery (DR) solution for an Exadata system. The Exadata configuration was not specified, I assume it was a smaller (eighth rack/quarter rack) configuration.

There were lots of arguments pro and against that Exadata->ODA architecture, and that leads to a broader question: how important is DR for your organisation? This blog post is about my personal experience, and probably strongly influenced by where I live in work (Europe), yours might be different.

About the original discussion

How important is a Disaster Recovery site for you?

I regularly read threads on the oracle-l mailing list, and occasionally feel very tempted to reply to one. Just recently I saw one that I liked a lot. It is specifically about using an Oracle Database Appliance (ODA) as a Disaster Recovery (DR) solution for an Exadata system. The Exadata configuration was not specified, I assume it was a smaller (eighth rack/quarter rack) configuration.

There were lots of arguments pro and against that Exadata->ODA architecture, and that leads to a broader question: how important is DR for your organisation? This blog post is about my personal experience, and probably strongly influenced by where I live in work (Europe), yours might be different.

About the original discussion

Where does the Exadata storage() predicate come from?

On Exadata (or when setting cell_offload_plan_display = always on non-Exadata) you may see the storage() predicate in addition to the usual access() and filter() predicates in an execution plan:

SQL> SELECT * FROM dual WHERE dummy = 'X';

D
-
X

Check the plan:

12c pq_replicate

Another day, another airport lounge – another quick note: one of the changes that appeared in 12c was a tweak to the “broadcast” distribution option of parallel queries. I mentioned this in a footnote to a longer article a couple of months ago; this note simply expands on that brief comment with an example. We’ll start with a simple two-table hash join – which I’ll first construct and demonstrate in 11.2.0.4:

Analyzing IO at the Cell level with cellcli… a new and improved script

Recently I had the pleasure of corresponding with Hans-Peter Sloot.  After looking at my simple tool in this post to gather cell IO data from cellcli, he took it a several steps further and created a nice python version that goes to the next level to pull IO statistics from the cells.

current_rw_rq.py

This script provides breaks down the IO by “Small” and “Large” as is commonly done by the Enterprise manager.  It also provides a summary by cell.  Here is a sample output from this script.

HCC error on Exadata after partitioning maintenance

Recently I have been asked to investigate the following error on an Exadata system.

ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

Well, that’s simple I thought! Must be (d)NFS mounted storage, right? Everyone knows that you can have HCC on Exadata (and a few other storage products). So I looked at the problem and soon found out that the data files in question all resided on the cells. Here is the sequence of events:

cell flash cache read hits vs. cell writes to flash cache statistics on Exadata

When the Smart Flash Cache was introduced in Exadata, it was caching reads only. So there were only read “optimization” statistics like cell flash cache read hits and physical read requests/bytes optimized in V$SESSTAT and V$SYSSTAT (the former accounted for the read IO requests that got its data from the flash cache and the latter ones accounted the disk IOs avoided both thanks to the flash cache and storage indexes). So if you wanted to measure the benefit of flash cache only, you’d have to use the cell flash cache read hits metric.