The intention of this blogpost is to show the Oracle wait time granularity and the Oracle database time measurement granularity. One of the reasons for doing this, is the Oracle database switched from using the function gettimeofday() up to version 11.2 to clock_gettime() to measure time.
This switch is understandable, as gettimeofday() is a best guess of the kernel of the wall clock time, while clock_gettime(CLOCK_MONOTONIC,…) is an monotonic increasing timer, which means it is more precise and does not have the option to drift backward, which gettimeofday() can do in certain circumstances, like time adjustments via NTP.
The first thing I wanted to proof, is the switch of the gettimeofday() call to the clock_gettime() call. This turned out not to be as simple as I thought.
This is the second blogpost on using PL/SQL inside SQL. If you landed on this page and have not read the first part, click this link and read that first. I gotten some reactions on the first article, of which one was: how does this look like with ‘pragma udf’ in the function?
Pragma udf is a way to speed up using PL/SQL functions in (user defined function), starting from version 12. If you want to know more about the use of pragma udf, and when it does help, and when it doesn’t, please google for it.
create or replace function add_one( value number ) return number is pragma udf; l_value number(10):= value; begin return l_value+1; end; / select sum(add_one(id)) from t2;
As you can see, really the only thing you have to do is add ‘pragma udf’ in the declaration section of PL/SQL.
Whenever you use PL/SQL in SQL statements, the Oracle engine needs to switch from doing SQL to doing PL/SQL, and switch back after it is done. Generally, this is called a “context switch”. This is an example of that:
-- A function that uses PL/SQL create or replace function add_one( value number ) return number is l_value number(10):= value; begin return l_value+1; end; / -- A SQL statement that uses the PL/SQL function select sum(add_one(id)) from t2;
Of course the functionality of the function is superfluous, it can easily be done in ‘pure’ SQL with ‘select sum(id+1) from t2’. But that is not the point.
Also, I added a sum() function, for the sake of preventing output to screen per row.
A few months ago I wrote an article about installing an Oracle database on AWS.
I updated the images in that article last night to bring them in line with this video.
I also updated the associated article.
There’s been a lot of work in the area of profiling. One of the things I have recently fallen in love with is Brendan Gregg’s flamegraphs. I work mainly on Linux, which means I use perf for generating stack traces. Luca Canali put a lot of effort in generating extended stack profiling methods, including kernel (only) stack traces and CPU state, reading the wait interface via direct SGA reading and kernel stack traces and getting userspace stack traces using libunwind and ptrace plus kernel stack and CPU state. I was inspired by the last method, but wanted more information, like process CPU state including runqueue time.
This post is about manually calling and freeing a shared latch. Credits should go to Andrey Nikolaev, who has this covered in his presentation which was presented at UKOUG Tech 15. I am very sorry to see I did miss it.
Essentially, if you follow my Oracle 12 and shared latches part 2 blogpost, which is about shared latches, I showed how to get a shared latch:
SQL> oradebug setmypid Statement processed. SQL> oradebug call ksl_get_shared_latch 0x94af8768 1 0 2303 16 Function returned 1
Which works okay, but leaves a bit of a mess when freed:
In the first part of this series I said that RAM access is the slow component of a modern in-memory database engine and for performance you’d want to reduce RAM access as much as possible. Reduced memory traffic thanks to the new columnar data formats is the most important enabler for the awesome In-Memory processing performance and SIMD is just icing on the cake.
I have created a new youtube channel – and have uploaded some videos there already! Bookmark & Subscribe here:
More stuff is coming over the next weeks & months :-)
NB! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)
Oracle certifies many of their latest products for installation on Oracle Linux 6 and 7, so I guess the obvious question is, which should you pick?
I tend to have a dual approach to this. I always use the latest versions of everything for my play kit, but I tend to be a little conservative for production deployments, preferring to use the OS version the product was developed against. I’ve noticed Oracle Cloud and some VM templates are still using Oracle Linux 6, which makes me think Oracle are being a little conservative too.