Tanel Poder Blog

Here are the slides of a presentation I did at the IOUG Virtual Exadata conference in February.

This is the first entry in a series of random articles about some useful internals-to-know of the awesome Oracle Database In-Memory column store.

Despite the title, this is actually a technical post about Oracle, disk I/O and Exadata & Oracle In-Memory Database Option performance.

Here’s a little known feature of Exadata – you can use a Bloom filter computed from a join column of a table to skip disk I/Os against another table it is joined to.

There was an interesting question in Oracle-L about the JOXSHM_EXT_* files in /dev/shm directory on Linux.

If you haven’t read them – here are the previous articles in Oracle memory troubleshooting series: Part 1, Part 2, Part 3.

Here are the slides of some of my previous presentations (that I haven’t made public yet, other than delivering these at conferences and training sessions): Scripts and Tools That Make Your Life Easier and Help to Troubleshoot Better: I delivered this pre…

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…

It’s long-time public knowledge that X$ fixed tables in Oracle are just “windows” into Oracle’s memory.

When the Smart Flash Cache was introduced in Exadata, it was caching reads only.

This post also applies to non-Exadata systems as hard drives work the same way in other storage arrays too – just the commands you would use for extracting the disk-level metrics would be different.

In the previous post about in-memory parallel execution I described in which cases the in-mem PX can kick in for your parallel queries.

This post applies both to non-Exadata and Exadata systems.

In my previous post ( Advanced Oracle Troubleshooting Guide – Part 11: Complex Wait Chain Signature Analysis with ash_wait_chains.sql ) I introduced an experimental script for analysing performance from “top ASH wait chains” perspective.

I just learned something new yesterday when demoing large page use on Linux during my AOT seminar.

I received a question about ALTER SYSTEM in the comments section of another blog post recently.

Here’s a treat for the hard-core Oracle performance geeks out there – I’m releasing a cool, but still experimental script for ASH (or poor-man’s ASH)-based wait event analysis, which should add a whole new dimension into ASH based performance analysis.

In case you haven’t noticed, I will be delivering my Advanced Oracle Troubleshooting and Advanced Oracle Exadata Performance: Troubleshooting and Optimization classes again in Oct/Nov 2013 (AOT) and December 2013 (Exadata).

My previous post was about Oracle 12c SQL Scalar Subquery transformations.

So, how many of you do hate the dialog below?

I promised to write about Oracle 12c new features quite a while ago (when 12c got officially released), but I was actually on (a long) vacation then and so many cool 12c-related white-papers and blog entries started popping up so I took it easy for a whil…

You can download it from http://edelivery.oracle.com And now I (and other beta testers) can finally start writing about the new cool features in it!

Just a little reminder – next week (10-11th June) I’ll be delivering my last training session before autumn – a short 1-day (2 x 0.5 days actually) seminar about Getting the Most Out of Oracle’s Active Session History.

One of the most common Exadata performance problems I see is that the direct path reads (and thus also Smart Scans) don’t sometimes kick in when running full scans in serial sessions.

About 5 years ago I wrote about the risks that connecting to Oracle processes via debuggers may cause and what are (in my opinion) the safer and less safer options for taking stack samples from running Oracle processes.

In the previous post I explained how to list Exadata disk layout and topology details with the exadisktopo scripts, in this post I’ll introduce one celldisk overview script, which I use to quickly see the celldisk configuration, specs and error statuses.

Here are two more Exadata scripts for listing the end-to-end ASM<->Exadata disk topology from V$ASM_ views and from V$CELL_CONFIG.

Did you know that there’s something like Active Session History also in the Exadata storage cells?

You may have used the Oracle 11g V$SQL_HINT view already – it displays all the valid hints (both documented and undocumented ones) available in your Oracle version, for example: SQL> @hint merge NAME VERSION

If you attended my Exadata hacking session today, you saw me using the cellver.sql script which lists some basic configuration info about the currently connected storage cells: SQL> @exadata/cellver Show Exadata cell versions from V$CELL_CONFIG....

Jonathan Lewis has already written about this behavior from the angle of PARALLEL hints.

I don’t have much time for a thorough blog post, so I’ll just paste in an example output of my asqlmon.sql script, which uses ASH sql_plan_line columns for displaying where inside your execution plan response time has been spent.

Thank you for attending the Exadata Snapper (ExaSnapper) hacking session!

This blog entry is about modern Linuxes.

If you already downloaded snapper v4, then better re-download it again as the v4.03 also runs in SQL Developer!

Snapper used to require access to DBMS_LOCK, so it could sleep for X seconds between the “before” and “after” performance data snapshots.

I have fixed most of the bugs that showed up during the Snapper launch party session and uploaded the new version (v4) of Snapper here: https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql I have uploaded some of my videos to https://www.youtu…

The promised hacking session about Exadata performance troubleshooting will happen on Thursday 21st February 9am-11am PST (2 hours).

Here’s an example of a quick’n’dirty way of profiling stack traces on your command line.

Snapper V4 Webinar Snapper V4 is out!

Some years ago I wrote about how sqlplus allows you to run sqlplus scripts directly from HTTP and FTP locations instead of the local filesystem.

Ryota Watabe has translated some of my articles into Japanese and intends to translate more in the future (thanks for that!

Sometimes you see something like this in an execution plan: -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------…

I am lazy, therefore if I can type less, I will type less.

Here’s the latest version of my Oracle session level performance Snapper tool: https://github.com/tanelpoder/tpt-oracle/blob/master/snapper.sql I’m going to add more stuff to Snapper in coming days, but thought to release something for testing already :)…

Gwen Shapira has written a nice summary of a problem case where the classic wait interface based troubleshooting method is not always enough for troubleshooting low-level issues.

Jagjeet Singh mentioned that he has built a V2 of his cool SQL Dashboard sqlplus-based monitoring script, which now also shows a nice colored ASH graph output.

Hello all fellow Oracle geeks and technology enthusiasts!

It’s a well known fact that the cursor_sharing parameter can be set to FORCE for making Oracle replace any literals with system-generated bind variable placeholders and then calculating the SQL hash value for looking up an existing cursor in library cache.

So, you think MOATS was cool?!