This was an incredibly well organized and well run event with lots of great speakers and great presentations. As a former board member of Tampa’s ORACLE users group, I know how hard it is to make an event like this happen, especially at this scale. Great job, @DbaKevlar and the rest!

Thanks to everyone who attended my presentation at RMOUG 2013 and if you did not have a chance to catch it, I’ll be presenting it again at IOUG Collaborate 2013.

Cheers!

In a nutshell, it means that your queries (at least, logically) do not pay attention to the outside world. I.e. your tables may be hit with thousands of changes per second, but your query will ignore all the hoopla and will always see only the data that existed when the query started.

Read consistency is quite addictive, because in ORACLE it is so trivial. Once you figure out that you don’t need to do much to get it (i.e. deal with locks or synchronize with other users), running queries and expecting consistent results every time quickly becomes a second nature.

And … it’s quite easy to to fall into a trap of thinking that working any other way for a query is impossible.

My job today is to show that this expectation is not entirely correct

So, what exactly does it mean for a query to be read consistent ?

I’m going to explore this point using a slightly convoluted (and specially crafted) example, that will query this simple table:

With this slightly complicated query:

Because I’m using nested loops the query above implements this fairly simple algorithm during its execution:

sleep(1) function here is a simple wrap around dbms_lock.sleep:

that causes the query to sleep for 1 second.

In other words, I’m probing inner table: T 5 times with 1 second delay between each try.

To make matters interesting, once the above query starts, I’m going to also start changing data in T from a separate session with this simple script:

The bottom line is: each probe in the query should deal with a slightly different data in the table and we can now see how ORACLE reacts to that.

As most people would expect, the result of the query, even with concurrent updates still looks like this:

In other words, the original query did NOT see any data changes coming from session 2. We queried table T 5 times over 5 seconds interval. Every time the data in T was slightly different, but our query always saw the same image, as it existed when query started. This is how read consistency rules the day.

Still, if this is what you’ve come to expect from ALL ORACLE queries, prepare to be surprised.

Let’s slightly modify our query, replacing our regular table with one of ORACLE dictionary views – v$session. In a nutshell, we’ll query our session status as we move along in our query.

Surprise! Look at the “wait times” – all of them are different.

We still query underlying table (v$session in this case) 5 times in a nested loop, but now every single time we look at the table we see different results! It’s almost like read consistency is not in the picture here.

Well, the truth is:

v$ views are mostly based on x$ objects, which, although look like tables to us and are even called (“fixed”) tables are something else entirely: x$ objects are kernel memory arrays that are exposed to us (for our convenience) through a table interface. And being arrays, they do NOT have read consistency!

To prove the point even further I’m going to slightly modify this example by changing order of tables in the FROM clause:

With SQL being declarative language, this shouldn’t affect the results, right ? Well, see for yourself:

The picture is completely different! Since we start with v$session this time, we read it only once and, of course, that means that wait times are the same everywhere (I guess, everything becomes consistent if you have one sample )

And there you have it folks! Read consistency is NOT a natural property of any ORACLE query. It requires work to enforce and sometimes ORACLE chooses not to do it.

This is cool, but how we make it useful ?
In the next article, I’ll talk about precisely that.

I.e. it doesn’t matter if you have 64 core systems with 100+ Gb of memory on either end if the majority of time is spent waiting for data to trickle across a slow 1 Gb network link.

Watching data trickle for hours, while the rest of the system is doing nothing is a pretty frustrating experience. But limitations breed creativity … so lately, I’ve been experimenting with several different copy techniques to see if there is any way transfer speed can be improved, perhaps using some of the idle capacity to speed things up.

Here is the short summary of my experiments (transferring 16Gb ORACLE data file across the WAN), which I summarized as a “speed and effect comparison” table. You can judge for yourself (with all the usual caveats that your results will depend on your system configuration and will probably vary yada yada yada) …

And here is the longer explanation if you are really interested

Traditionally, many people are using scp to copy files between systems, something like:

The problem is – scp is NOT very fast. I.e. regular speeds, achieved by scp are in the range of ~ 20-50 Mbs per second. To put it in perspective, it takes from ~ 4 to 13 minutes to copy a 16 Gb file between systems. Multiple it by, say, 8 files and now you are wasting 0.5 to 1.5 hours for the simple copy.

Which begs the question – how can we do better ?

The first observation with scp is that even at the top of the range the transfer speeds are NOT approaching the true NIC capacity (which, for 1 Gb NIC is slightly more than 100 Mbs per second).

So, we should do much better if we are able to “fill the pipe” completely.

“Filling the pipe” is precisely what bbcp command does – it opens multiple network streams and transfers a file in parallel, using most of the network capacity in the process. In my tests, bbcp consistently outperformed scp, reaching speeds of ~ 100-115 Mbs per second and cutting transfer time by the factor of 2.

There are, however, two problems with bbcp.

First of all, its default syntax is pretty scary. I.e. in my example, it looked like this:

But more importantly, using that much network for copy is dangerous as it does not leave much bandwidth for anything else on the host (i.e. regular ORACLE connections by apps).

Plus, it may affect other unrelated hosts if you happen to have multiple machines using the same network path and a slightly oversubscribed network.

In other words, bbcp should be used only if you do not care whether database on the box is accessible and also do not share the host/rack/routers with anybody else.

To be fair, you can use bbcp options to limit how much bandwidth it is using. But if you do that, the copy speed essentially reverts back to scp as it directly correlates to how much data you are pushing over the wire.

Bottom line, bbcp = not good, if your system is actually used !

Is there another alternative ?

Yes. Apparently, ORACLE data files are pretty compressible. We can gzip them on the source, transfer 5-10x times less data over the wire (yes, that seems to be the average compression rates, sometimes rates are even better) and unpack them on destination.

The problem, however is that instead of running a simple scp command, we need to run 3 commands on 2 separate systems:

• Source: gzip
• Source/Target: transfer, i.e. scp
• Target: unzip

which is a bit too complex if we just want to copy a bunch of files.

Fortunately, this technique can be simplified and generalized by using network streaming tools.

Here is an example of copying the same file using gzip and netcat. We still need to run 2 commands, but they are pretty simple:

nc here is a network streamer that sends data over to the wire on the sending end (port: 8888) and reads the data from the wire on the receiving.

I ran many such copies and every single time md5sum confirmed that data files were transferred correctly. Moreover, when something breaks (such as when a certain DBA would run Ctrl+C on either end), this event is very visible – you will recognize that an error has occurred and you need to re-transfer.

In most of my tests this combination of commands was even faster than bbcp (giving me an additional 15-25 % improvement), but, more importantly, it utilized only ~ 1/4 of the available network bandwidth, usually putting ~ 20-30 Mbps over the wire (even scp puts 60).

But we are still not done. Transfer speeds can be improved further if we are willing to use a bit of CPU on the source host.

As you might know, gzip is a sequential single threaded application, but we also have a parallel zip, named rather expressively as “pigz”:

Pigz is a essentially a gzip, but can use multiple parallel streams to compress/decompress the data. If we replace gzip with pigz, we can achieve fantastical speeds and cut our transfer time again by the factor of ~ 2-10, comparing to scp.

If you can fill network pipe completely (i.e. you are the only user in a system), the question naturally becomes: “Can we combine compression and multistream transfer for even faster speeds ?”.

As it happens, bbcp command has “compress me” option for input streams, so it seems a natural candidate here … However as hard as I tried I couldn’t make it work properly. In all of my tests, when bbcp compression was turned on, there was definite improvement in network utilization, but the transfer itself was dead slow … much slower than that of the original scp. If anybody knows how to use bbcp compression efficiently, I’ll appreciate the learning experience.

Still, the rather straightforward workaround is to still use tar/pigz/nc and just run several copies in parallel.

Pigz/nc transfer might be significantly faster, but, it might not be the easiest to monitor. While scp has a nice progress bar, pigz/nc just gives you a blank screen for the entire duration of the transfer. Fortunately, it is very easy to correct if you drop in a pipe viewer tool within pigz/nc pipe.

which should give you a nice progress bar, quite similar to scp:

While source/transfer commands are not too complex to master, there are still two commands that you need to run. To make things easier, it makes sense to script them together to remove another advantage of scp:

Finally, there is one advantage that scp still holds: its transfer is secure while pigz/nc transfers data in clear text. So, if you are using unsecured networks, this option is probably not for you.

Cheers,
Maxym Kharchenko

Sharding is an awesome technique and I’ll blog separately on it, but for now see my presentation and white paper.

Cheers

Of course, when you run DBMS_XPLAN.DISPLAY_CURSOR for the statement, it can put a nicely formatted note for you, something like:

But can you find it globally for ALL SQLs in the shared pool ?

It turns out that the answer was staring right in my face: v$sql has a SQL_PLAN_BASELINE column, which along with EXACT_MATCHING_SIGNATURE is a key into dba_sql_plan_baselines view.

If v$sql.SQL_PLAN_BASELINE is empty, then the baseline is NOT used, otherwise the column contains baseline plan name being used at the moment.

Anyway, here is a useful script to find out if your particular cursor is using plan(s) from baseline or not:

Cheers!

I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!

The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!

Well, if the problem is that a bad index is chosen – the fastest way to fix it is to hint the query to chose a good one.

However, there might be some problems with this approach too. Adding SQL hint is still a “source code change”. And in many cases, the source code is either not accessible or access is so complicated that hinting your query can take weeks … Yikes!

Fortunately, there is a a way to attach SQL hints directly in the database itself without all that hassle … It’s not fully supported, but it works … and it can save your butt if you need to “fix” things quickly …

Here is what you need to do:

Reason being, ORACLE needs to know where in the query to apply the hint.

Assuming, you know your query SQL_ID:

You should get something like:

where @SEL1 is your query block name.

Hints can be attached to your query in the form of a SQL profile.

However, ORACLE is very peculiar about profile hint syntax and, generally, requires extended form of hints that list table and query block names.

Simple hints (that you are probably used to) will not do!

I.e. to be recognized, this common hint:

will need to be transformed into its “extended” form:

If you are unsure what exactly ORACLE expects in “extended” hints, you can use this script to see extended hints for any SQL that exists in the shared pool.

This is the final step and it’s the one that is NOT “officially” supported.

Even though SQL profiles are basically just a bunch of hints that attach to your statement, ORACLE originally designed them as a way to “fix” bad cardinality estimations for “special” data.

Thus, the only “supported” way to create profiles is to have ORACLE run SQL tuning job on your query, i.e.:

which may create profile at the end if it helps the query (no guarantees, though!)

Plus, the only hints that profiles can officially have are: OPT_ESTIMATE (“extended” form of good old CARDINALITY hints).

But if you look closely at DBMS_SQLTUNE package, you will find undocumented IMPORT_SQL_PROFILE procedure that allows “free form” SQL Profiles to be created.

Like this:

force_match=TRUE means that SQL profile will be used for all similar statements, regardless of the value of literals (if SQL has any).

At this point the problem should be resolved. If you look at the new execution plan, you should see message like:

and execution plan should change.

If it hasn’t, either you made a typo in the hint definition or there is something else that prevents change in plans, i.e. current SPM baseline.

Correcting typos is easy: just drop existing profile and repeat step 3 with the new hint.

Good luck!

P.S. You can also follow this complete, albeit artificial example.

Well, I’ve heard a good one today:

Thanks, Arup. Stay thirsty my friends

There are all kinds of identifiers: SQL_HANDLE, PLAN_NAME, SIGNATURE, but if you look at them, nothing resembles SQL_ID.

And yet, almost everywhere ORACLE seems to be making SQL_ID as the primary SQL identifier.

Of course, you can always use SQL_TEXT to join to v$sql, but this seems to be a bit drastic.

Fortunately, there is a better way.

Starting from ORACLE 10g v$sql has 2 additional columns: exact_matching_signature and force_matching_signature and it turns out they exactly match dba_sql_plan_baseline.signature, so finding out baselines that “attach” to your particular statement becomes quite easy:

Why did ORACLE create a separate SQL identifier here ?

My guess is – SQL_IDs and SPM baselines are not exactly the same thing and do not have 1-1 mapping between them.

• SQL_ID is just another representation of SQL text (you can read about it in Tanel Poder’s blog)
• While baseline is about SQL plan (group of plans, really) and technically the same group of plans can be attached to many “distinct” SQLs.

Cheers!

For example, here is a simple way to “freeze” your ORACLE database, which I “discovered” while tracing system calls in LGWR process (ORACLE 11.2.0.2 on Linux 2.6.18 ×64).

Here goes … To freeze your ORACLE database start with the following unassuming strace command on your LGWR:

At this point, nothing bad happens yet, trace data is flowing into /tmp/l.out and the database is, in general, happy.

Weirdness starts when you detach (or kill) your strace process.

At the first glance, nothing seems to be wrong, the database is still running, sessions are connected and executing queries etc.

But when you try to COMMIT (or execute DDL which commits implicitly)

Your command will wait … and wait … and wait …

v$session will show that your session (along with all others trying to do commits) is waiting for log file sync wait event. But why ?

A quick look at LGWR process state clarifies the issue:

According to ps manual, T state means that the process is “Stopped, either by a job control signal or because it is being traced” (the latter is a lie because tracing has just stopped). And, unfortunately in this case, unless something happens, LGWR will be in this state forever stalling all database activity.

So, the question becomes, how can we “unstop” the LGWR ?

Surprisingly, the most straightforward method is to attach strace to LGWR again. This unfreezes the database and let transactions flow. Unfortunately, that also means that trace information is also collected, which generally slows things down.

To avoid it, a better way is to send LGWR (POSIX) SIGCONT signal to nudge it into “ready to run” state.

which solves the issue.

I’ve seen it on a couple of my systems and it “works” with both 11.1.0.7 and 11.2.0.2 databases. This seems like a bug and I do not know whether it only applies on my particular strace/Linux/ORACLE version(s), but, well, sometimes you just have to deal with the problems like that …

Cheers!

One of the smaller (and nicer) part of ORA_MEM package is ora_cpu.pl utility that shows graphically what active db processes are doing at the moment … be it running SQL, accessing db object or waiting for something.

As an added benefit, it also shows some important OS statistics, such as process state or ‘spot’ CPU utilization for your database sessions.

Previously, this tool was only available for AIX, but now I ported it to Linux as well.

If you run it as:

You will see a color coded picture like the one below (click on it to see the picture full scale):

Alternatively, run it with -h option to see what else the tool can do.

Linux version of ora_cpu.pl is now available at the TOOLS section of the site, or you can use this direct link to download.

Give it a try and Enjoy