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 :

ORACLE 12C : Shared Pool and durations enhancement

Recently when diagnosing a memory issue on the shared pool ora-4031 (11.2.0.3.9) i discovered that the root cause was related to duration management. Although, we have a lot of free space on every sub-pool and no fragmentation some duration was saturated.Riyaj Shamsudeen described a similar problem in his blog  https://orainternals.wordpress.com/2009/08/06/ora-4031-and-shared-pool-duration/   so i will not discuss the problem i faced here. In this post i want to show some change that appeared in the 12c version that can reduce this type of problem.

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 :