ORACLE 12C : Distributed Queries and bind variable peeking

In ORACLE 12C a new bug fix was introduced :

12534597

12.1.0.0

Bind Peeking is disabled for remote queries

select * from V$SYSTEM_FIX_CONTROL where bugno = ‘12534597’;

So from now on bind variable peeking will kick in also for distributed query.

Let’s run some test :

12C Temporary Undo : Under the hood

You have probably already heard of and used Temporary Undo (TEMP_UNDO_ENABLED) if not more info here and here .In this post i will try to show whats going on under the hood.So let begin :

ORACLE 12C new cost to time (I/O calibration effects)

As stated by Randolf Geist in his blog post oracle introduced a new model for predicting the execution time from the cost calculation starting from patch set 11.2.0.2 and this kick in after running the I/O calibration routine.Without I/O calibration the execution time will still be as usual COST*SINGLE-BLOCK read time.

Extract from Randolf Geist post  Cost Is Time: Next Generation :

“The cost that has been calculated according to the System Statistics model – which is already a time estimate based on three different components – time for single-block and multi-block reads as well as the estimated CPU time is now converted into a data volume by simply multiplying the resulting cost with the default block size. Dividing this data volume by the throughput as indicated by the I/O calibration results (it looks like the value MAX_PMBPS is relevant) arrives at a new estimated execution time.”

So what about 12C ? Let’s run some test and observe :

12C New limitation for LOW_VALUE, HIGH_VALUE and ENDPOINT_VALUE on character columns

When investigating statistics collected for new tables on a 12.1.0.2  database i have come across a new discover (for me). In earlier release (berfore 12c) when collecting statistics on a table only the first 32 bytes of a character string was saved for LOW_VALUE, HIGH_VALUE and ENDPOINT_VALUE.Let’s see what happen on a 12C database.

TEST CASE :

ORACLE Queryable patch inventory on HP-UX V3 : /usr/lib/hpux32/dld.so: Unable to find library ‘libjli.so’ ($ORIGIN)

This a quick note on recent problem i have faced when trying to access the Queryable patch inventory “OPATCH_XML_INV” and also when trying to use datapatch (which need to access the Queryable patch inventory) on ORACLE 12.1.0.2 installed on HP-UX V3 itanium 2 server (So i am unable to patch the database) :

ORACLE IN-MEMORY transaction journal or buffer cache ?

Oracle in-memory white paper stated that “When a DML statement changes a row in an object that is populated into the IM column store, the corresponding entries for that row is marked stale in the IMCU and a copy of the new version of the row is added to the in-memory transaction journal.”

“When a query with a newer SCN is executed against the object, it will read all of the entries for the columns in the IMCU except the stale entries. The stale entries will be retrieved either from the transaction journal or from the base table (buffer cache).”

UPDATE 30/03/2017 : It’s seem to be a documentation bug the new doc state : “The stale entries will be retrieved from the base table (i.e. row store).”

Lets try some example and observe :

Simulating V$KERNEL_IO_OUTLIER on LINUX using Systemtap

With the 12C version oracle come with many amazing feature, one of them is the integration of ORACLE database with the awesome dynamic tracing framework  DTRACE on SOLARIS system.In fact we can see this in action using  V$KERNEL_IO_OUTLIER view.Like explained by Andrey Nikolaev in  https://andreynikolaev.wordpress.com/2013/09/20/vkernel_io_outlier/ this view is based on a DTrace script running in the background.He also managed to rewrite the script so it can be used also on pre-12c Oracle databases.(That’s cool !!)

But what About Linux system ?

Improving IN-MEMORY query with Attribute Clustering (IMCU pruning and compression ratio)

Attribute clustering is a new feature of oracle 12c for more info check http://docs.oracle.com/database/121/DWHSG/attcluster.htm#DWHSG9338.

I will focus here only on how attribute clustering can improve in-memory query.For a description of in-memory pruning please check my previous post.

Here is a quick example to demonstate how Attribute clustering can improve in-memory query pruning and compression ratio. Here is the simple steps :

1. Create a table and load it in-memory.
2. Check for the min-max column for every IMCU and the total allocated space in-memory.
3. Use attribute clustering for one column and then reload the table in-memory.
4. Check for the min-max column for every IMCU and the total allocated space in-memory.
5. Campare the results.

ORACLE 12C in-memory : Part 4 (IMCU Stale entries)

“The more stale entries in an IMCU,the slower the scan of IMCU will become” (Oracle in-memory white papers)
In fact the stale row will be read from the transaction journal (if it’s there) or from the buffer cache.

The question is how much stale entries do we have ? How to reduce them if required ?

Oracle 12C In-memory : Part 3 (IMCU pruning)

For other parts : Part1 , Part2

Let’s see a quick example of in-memory pruning using storage index :

In-memory white paper : “A further reduction in the amount of data accessed is possible due to the In-Memory Storage Indexes that are automatically created and maintained on each of the columns in the IM column store. Storage Indexes allow data pruning to occur based on the filter predicates supplied in a SQL statement.An In -Memory Storage Index keeps track of minimum and maximum values for each column in an IMCU.When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column is examined to determine if any entries with the specified column value exist in each IMCU by comparing the specified value(s) to the minimum and maximum values maintained in the Storage Index. If the column value is outside the minimum and maximum range for an IMCU, the scan of that IMCU is avoided”

Quick example :