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.
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 last 14 months I delivered a dozen of presentations covering the In-Memory Column Store. During many of them, I spent most of the time showing the audience several demos. The aim of this post is to share with you the scripts and a recording (MP4) of those demos.
Warning about Demos
The recordings show the results of running the scripts on an Exadata system. The performance figures are intended only to explain and compare different kinds of processing and to give you a feel for their impact. Since every system and every application has its own characteristics, the relevance of using each technique might be very different, depending on where it’s applied. Simply put, the scripts were engineered to clearly show specific behaviors.
In case you want to run the scripts, to setup the environment run imcs_prepare_schema.sql.
This article is about the Oracle 12c in-memory option, and specifically looks at how the background worker processes do IO to populate the in-memory column store.
Hardware: Apple Macbook with VMWare Fusion 7.1.3.
Operating system: Oracle Linux 6.7, kernel: 3.8.13-118.el6uek.x86_64.
Database version: Oracle 126.96.36.199
Patch: opatch lspatches
19392604;OCW PATCH SET UPDATE : 188.8.131.52.1 (19392604)
19303936;Database Patch Set Update : 184.108.40.206.1 (19303936)
But first things first, let’s setup the in-memory option first with a test table. The first thing to consider is to create the in-memory area to store the objects. I only want a single table stored in the in-memory area, so I can very simply look at the size of object:
In the previous post on the decision between buffered and direct path reads I showed the decision is depended on the version. Up to and including version 220.127.116.11 the size of a segment needs to be five times small table threshold in order to be considered for direct path reads, and starting from 18.104.22.168 the database starts considering direct path reads starting from small table threshold. The lower limit just discussed is small table threshold or five times small table threshold with lower versions, upper limit is called “very large object threshold” (VLOT) and is five times the size of the buffercache, which is the threshold after which a table scan always is going via direct path.
Since the direct path feature for serial processes was discovered after it became available in Oracle 22.214.171.124 (as far as I know, I haven’t checked Oracle 11.1), there have been a lot of blog posts on when this happens. A lot of these do not specify the Oracle version, which is a failure in my opinion. There are different decisions made in different versions.
The purpose of this blogpost is to show the results of my tests on when the Oracle database engine switches from buffered to direct path reads and vice versa. There probably are decisions made by the database engine for this feature based on internal kept statistics, like general activity and object usage, which means my tests might be different in your database. For that purpose I included an anonymous PL/SQL block in this post so you can replay the same test in your own database, except for the table, which you have to create yourself.
I don’t have an official OOW presentation in the conference this year. However, I am presenting a session at the Oak Table World 2015 event behind held concurrently with OOW 2015. My topic is “Exadata Database Machine Security” and I plan to review some of the newest updates to security for the Exadata Database Machine engineered system.
As the website indicates, the event is completely free and there is no pre-registration or enrollment required–just show up and come on in to hear some great speakers present on great topics. Hope to see you there on Monday, October 26, 2015!
As an Oracle performance engineer, I often work with customers on benchmarks and diagnosis of various issues. Now, everyone can gather an AWR report and send it my way, but that is just the tip of the iceberg. AWR reports are great for summarizing activity at a high level and can even find a SQL that is taking longer than expected. So, once we have identified a poor performing SQL, how do we fix it?
…that is where SQLMON comes into the picture.
The power of the SQLMON report is very well documented. It provides an insight into EXACTLY what was happening when that query was running on the system. It documents the run time, SQL plan, CPU activity, IO activity, Offload, etc… The report can be gathered by running the “dbms_sqltune.report_sql_monitor” utility to extract a report. Each SQL in Oracle maps to a specific “SQL_ID” as this is really just a hash of the sql text….But, that is not all.