Oracle

fritshoogland's picture

Oracle 12 and latches

Oracle DBAs who are so old that they remember the days before Oracle 11.2 probably remember the tuning efforts for latches. I can still recall the latch number for cache buffers chains from the top of my head: number 98. In the older days this was another number, 157.

But it seems latches have become less of a problem in the modern days of Oracle 11.2 and higher. Still, when I generate heavy concurrency I can see some latch waits. (I am talking about you and SLOB mister Closson).

I decided to look into latches on Oracle 12.1.0.2 instance on Oracle Linux 7. This might also be a good time to go through how you think they work for yourself, it might be different than you think or have been taught.

Jonathan Lewis's picture

Descending Indexes

I’ve written about optimizer defects with descending indexes before now but a problem came up on the OTN database forum a few days ago that made me decide to look very closely at an example where the arithmetic was clearly defective. The problem revolves around a table with two indexes, one on a date column (TH_UPDATE_TIMESTAMP) and the other a compound index which starts with the same column in descending order (TH_UPDATE_TIMESTAMP DESC, TH_TXN_CODE). The optimizer was picking the “descending” index in cases where it was clearly the wrong index (even after the statistics had been refreshed and all the usual errors relating to date-based indexes had been discounted). Here’s an execution plan from the system which shows that there’s something wrong with the optimizer:

Jonathan Lewis's picture

PQ Index anomaly

Here’s an oddity prompted by a question that appeared on Oracle-L last night. The question was basically – “Why can’t I build an index in parallel when it’s single column with most of the rows set to null and only a couple of values for the non-null entries”.

That’s an interesting question, since the description of the index shouldn’t produce any reason for anything to go wrong, so I spent a few minutes on trying to emulate the problem. I created a table with 10M rows and a column that was 3% ‘Y’ and 0.1% ‘N’, then created and dropped an index in parallel in parallel a few times. The report I used to prove that the index build had run  parallel build showed an interesting waste of resources. Here’s the code to build the table and index:

kevinclosson's picture

This Is Not Glossy Marketing But You Still Won’t Believe Your Eyes. EMC XtremIO 4.0 Snapshot Refresh For Agile Test / Dev Storage Provisioning in Oracle Database Environments.

This is just a quick blog post to direct readers to a YouTube video I recently created to help explain to someone how flexible EMC XtremIO Snapshots are. The power of this array capability is probably most appreciated in the realm of provisioning storage for Test and Development environments.

Although this is a silent motion picture I think it will speak volumes–or at least 1,000 words.

Please note: This is just a video demonstration to show the base mechanisms and how they relate to Oracle Database with Automatic Storage Management. This is not a scale demonstration. XtremIO snapshots are supported to in the thousands and extremely powerful “sibling trees” are fully supported.

pete.sharman's picture

Snap Clone on Exadata

Recently I created a new screenwatch that walks you through the new features of Snap Clone on Exadata in Enterprise Manager 12c version 12.1.0.5. The screenwatch walked through using Snap Clone on Exadata, both from a multi-tenant architecture perspective and a standard non container database perspective. Unfortunately, putting both together meant the resultant video was around 20 minutes long.

Generally, we try to make our screenwatches shorter than that, as the evidence tends to show people lose concentration and interest if the screenwatches are that long. So we decided to split the screenwatch in two. One part would be for vanilla Snap Clone on Exadata, and the other was for Snap Clone Multitenant on Exadata.

fritshoogland's picture

How long does a logical IO take?

This is a question that I played with for a long time. There have been statements on logical IO performance (“Logical IO is x times faster than Physical IO”), but nobody could answer the question what the actual logical IO time is. Of course you can see part of it in the system and session statistics (v$sysstat/v$sesstat), statistic name “session logical reads”. However, if you divide the number of logical reads by the total time a query took, the logical IO time is too high, because then it assumed all the time the query took was spend on doing logical IO, which obviously is not the case, because there is time spend on parsing, maybe physical IO, etc. Also, when doing that, you calculate an average. Averages are known to hide actual behaviour.

Jonathan Lewis's picture

Missing Bloom

Here’s a little surprise that came up on the OTN database forum a few days ago. Rather than describe it, I’m just going to create a data set to demonstrate it, initially using 11.2.0.4 although the same thing happens on 12.1.0.2. The target is a query that joins to a range/hash composite partitioned table and uses a Bloom filter to do partition pruning at the subpartition level.  (Note to self: is it possible to see Bloom filters that operate at both the partition and subpartition level from a single join? I suspect not.). Here’s my code to create and populate both the partitioned table and a driving table for the query:

kevinclosson's picture

Announcing SLOB 2.3. Tarry Not, Get It While It’s Hot!

BLOG UPDATE 2015.07.16: SLOB 2.3.0.3-1 is now the current version.

This is just a quick post to announce SLOB 2.3. Please visit the SLOB Resources page to download the gzipped tar archive. The SLOB Resources page also has a link the SLOB 2.3 Documentation. SLOB Resources Page: Click Here. New in this release:

oraclebase's picture

Oracle OpenWorld Rejections : #TeamRejectedByOracleOpenWorld

Once again it is Oracle OpenWorld paper rejection season. :)

Invariably, us conference types start to have a bit of a moan about being rejected, hence my little jibe #TeamRejectedByOracleOpenWorld. In case people lost sight of this being a joke, here was one of my tweets on the subject.

“Setting up a helpline for #TeamRejectedByOracleOpenWorld to deal with all us people who can’t cope with not being heard for 5 mins. :)”

oraclebase's picture

YouTube : Rags to Riches in 1 Week?

youtubeIf you’ve followed me on Twitter you will have seen me posting links to videos on my YouTube channel. You can see me talking about starting the channel in the first video.

One week and 5 videos in and I’ve just hit 50 subscribers. Watch out PewDiePie!

Syndicate content