APPS-ORACLE.RU

I've seen some blogs recommending that _partition_large_extents be set to FALSE for a variety of space conserving reasons without the authors thinking about the negative impact this is going to have on Smart Scan. Large Extents cause an INITIAL allocation of 8 MB and a NEXT allocation of 1 MB [...]

In 12.2, there is a stat “cell blocks pivoted” that points to a new optimization. When Smart Scan processes data, it has to create a new synthetic block that only contains the columns that are needed and the rows that pass the offloaded predicates.

If Smart Scan has to create a [...]

Several people have asked if there is any way to use INMEMORY on External Tables because the INMEMORY syntax is not supported in Create Table and Alter Table with Organization External (actually Create Table parses it but then ignores it).

While there is no way out of having to load the [...]

I finally found time to get back to External Tables and have a list of blog posts I need to write on this topic. Here's a brief one.

DBMS_ROWID will nicely break down a heap table's rowid for you into file number, block number, and row number but it doesn't handle [...]

This document is my attempt to bring together the available options that can be used to determine the root cause of an issue in order to create a roadmap to help support engineers narrow down the cause of concern.

It is a living document and will be edited and amended as [...]

I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.

I had previously commented on how to use the FPLIB facility in a trace event but the question came up today of how to trace HCC in an offload server. The facility name in this case is ADVCMP (Advanced Compression) and the hierarchy is:

I've noticed several people who were familiar with using trace events with cellsrv, were uncertain about how to use tracing the new offload server architecture. So whereas in the past you could have added tracing to SmartScan processing with:

the new syntax [...]

Just a few thoughts I've been meaning to blog for a while:

Very early on in the Exadata Smart Scan implementation a restriction was added to prevent offloading more than 255 columns. This was done because of performance issues observed by customers. Smart Scan [...]

We've observed databases with very large buffer caches where Serial Scans don't make use of Smart Scan when that would have executed faster: improvements to the decision making for Serial Scans have been made under bug 31626438. This fix is back-portable.

A key difference between PQ and [...]

One of the main performance technologies underlying ADW is the In-Memory format column cache and a question that has come up several times is: how does the columnar cache handle DMLs and cache invalidations. This blog post attempts to answer that question.

The original columnar cache was [...]

The RDBMS stats for Cellmemory are designed to closely follow the pattern used by the Inmemory stats

Each column in each one MB of disk blocks will be rewritten into one IMC format Column CU in flash and a set of Column [...]

Question: do I need to know anything in this blog post?

Answer: No, it is a true cache and CELLMEMORY works automatically

Question: so why should I read this blog post?

Answer: because you like to keep a toolkit of useful ways to control the system when needed

The Exadata engineering team [...]

Many people know that in 12.1.0.2 we introduced a ground-breaking columnar cache that rewrote 1 MB chunks of HCC format blocks in the flash cache into pure columnar form in a way that allowed us to only do the I/O for the columns needed but also to recreate the original [...]

In Part 2 we are going to look at making use of the trace events that show what was discussed in Part 1.
NB: Oracle no longer adds new numeric trace events, going forward new trace events use the Unified Tracing Service whose grammer is much simpler. The elements we [...]

I've read a number of blog entries from various people who've clearly put great diligence into trying to understand how the decision to use the buffer cache for a table scan or whether it is better to use direct read for the scan is made. Since this is critical decision [...]

I was asked a question yesterday that reminded me there are always people completely new to the topic who need an introduction – somewhere to start before the other articles make sense. So, here's my brief write-up of everything you need to know about the basic of Oracle Table [...]

Since Christmas I have been asked to investigate two different “failures to use Smart Scan”. It turns out they both fell into the same little known restriction on the use of Direct Read. Smart Scan critically depends on Direct Read in order to read the synthetic output blocks into private [...]

If you turn on NSMTIO tracing you will see references to VLOT:

I had said you could ignore VLOT and Frits Hoogland pointed out that tracing showed it had some impact, so let [...]

I heard someone assert that one reason you should only use 8k block sizes is that, and I quote, “Oracle only tests on 8k and doesn't really test 16k”. I tried googling that rumour and tracked it back to [...]

The question came up this week about whether the predicates for row level security are offloaded to Smart Scan. The simple answer is yes as long as the policy_function contains off-loadable predicates.

Let's see this in action. Using the public TPC-H schema we can set up the customer table so that [...]

Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):

and underscore parameters:

Since the beginning Oracle has provided four compression levels to offer a trade-off between the compression ratio and various other factors including table scans and the performance of single-row retrieval. I can not emphasize enough that the various trade offs mean that YMMV with the different [...]

When a background activity is happening on the cell you typically can't use RDBMS v$ views to monitor it in the same way. One such question is how to tell if a segment is fully loaded in the Exadata column cache since this does not appear in the equivalent In-Memory [...]

I normally blog about table scans on Oracle native data but Oracle also supports a wide variety of features for scanning external tables and I need to cover these too. One of the ways I learn new things is being sent a bug report and saying to myself “Oh! I [...]

12.1.0.2 introduced the new Columnar Flash Cache where 1MB of blocks that are all in HCC format are rewritten so as to make each column from each CU contiguous. This works by first writing all the block headers to an array, then writing all the CU headers to an [...]

I was looking through a test script and saw something I didn't know you could do in Oracle. I mentioned it to an Oracle ACE and he didn't know it either. I then said to the External Table engineers “Oh I see you've added this cool new feature” and he [...]

A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not [...]

One of the joys of regexp is that you can write a pattern that is painfully expensive to match and offloading these to the cell can cause significant impact on other users and overall throughput (including heartbeat issues). If you have a user who is prone to writing bad regexp [...]

There are three main tools available for cleaning up a segment (Alter Table Shrink, Alter Table Move, and export/import), but one of them isn't as helpful as you might have thought.

Consider the following sequence of events where we update the 256th column to cause widespread fragmentation: