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.
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:
This blog post is inspired by a question from an attendee of Sigrid Keydana's DOAG 2015 conference session called "Raising the fetchsize, good or bad? Exploring memory management in Oracle JDBC 12c". Basically it was a question about what the wait event "SQL*Net more data to client" represents and what it really measures. In general you may use the following steps, if you don't know what a particular wait event means:
Most of us know that the Oracle DATE datatype has upper and lower limits. From the Oracle 11g Database Concepts manual:
Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default.
I never believe 100% anything I read, so I’ll try that. I’ll set my session to show dates with the AD/BC indicator and step back in time:
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 18.104.22.168 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.
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.
It is not often that something like the following happens on Google while searching for…
Today I had an error on my test environment which surprised me…initially… While I was…
I wouldn’t believe the bad XMLType performance statement given stated in Martin Preiss’ blog post,…
I had some time to spend, killing time, and thought about something that was “on…