Hierarchical Blocking Session List

Hi, Today, I needed to list all blocking sessions hierarchically. As you know, all information about sessions (and blocking sessions) are in gv$session (or just v$session if you are using single instance) view. I wanted to see who is blocking who and blocker at the top. you can write simple connect by (or with clause)

Subquery Caching

Hi, I want to talk about very sweet performance “trick”. I love when I do something small and gain a lot 🙂 Today, I will demonstrate subquery caching in Oracle and try to explain how it works and it’s limitations. Probably, you are using some plsql functions in your select (or dml) statements. There is

Why should you use PLSQL_OPTIMIZE_LEVEL as 3

Hi, As you know PLSQL_OPTIMIZE_LEVEL parameter is used to optimize the codes while compiling them and it is really important. By default it’s value is 2 but you can increase it to 3 and you should, here is why. before begin, this is an extension for this post: http://mustafakalayci.me/2020/06/26/performance-gain-of-nocopy-parameters/ Very much thanks to Stephan Borsodi who

Enable Unified Audit on Linux and Windows

Hi, As you know, since 12c Oracle introduce Unified Auditing which has much more capabilities than standard auditing. Creating policies, audit conditions, top level auditing etc. By default Oracle uses “mixed” mode which allows you to run unified auditing commands and standard auditing. you can check if you are using unified auditing by default:

Blockchain vs Immutable tables

Hi, as you know 21c is here for linux and some new features are (as always) pretty exciting. two of them are Blockchain and Immutable tables. Let’s explain and test. first of all, those are new features for 21c but they are backported to 19c. at 19.10 you are able to use BLOCKCHAIN tables and

Pragma UDF & Deterministic

Hello everyone, I wanted to write about something simple and can improve the performance of your sql statements. Unfortunately, many database developers use too much plsql function in their SQL statements. I say too much because if you learn more about the SQL, you will use less plsql. SQL is very capable language to process

PLSQL and Flashback Data Archive Problems.

Hi, I have written about flashback data archive before. if you didn’t read please check these post first. http://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/ http://mustafakalayci.me/2020/09/10/flashback-data-archive-problems/ I’ve already mentioned about some problems on my second post but these are not kind of problems which will stop your work. The problem I am about talk is that kind of problem. From my