Sessions with details Mustafa, 2023-09-212023-09-21 Hi, Today I just want to share a small select statement that I used (my original one is a little bit longer). I use this to get as much as “important” data from sessions in the database. By saying “important”, I mean the data that I use to identify the… Continue Reading
How to Demonize Apex V Function! Mustafa, 2021-10-312021-11-04 Hi, Answer of the question in the subject is “Using too much V in SQL statements”. Let’s see why and what should you do. First things first, I am NOT a APEX developer. I am a DBA and Database Developer but some of my clients are using APEX and I… Continue Reading
Hierarchical Blocking Session List Mustafa, 2021-10-19 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… Continue Reading
Subquery Caching Mustafa, 2021-10-172021-11-19 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… Continue Reading
Which Column has Changed in Every Row? Mustafa, 2020-11-022020-11-03 Hi, Edit: Thanks to Chris Saxon who is from asktom team, checked my code and warned me about a problem. Not to overload the PGA fetch_rows function only fetches 1024 rows at once so every row set chunk size is 1024. In this case for the 1025th row, previous row… Continue Reading
SQL Macro Part2 column_value = nvl(:param, column_value) Mustafa, 2020-10-302021-09-23 Hi, Since I am working on “SQL Macro”, I am keep testing things. so please see my previous post: https://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/ as mentioned in previous post, is it possible to get rid of column_value = nvl(:param, column_value) conditions? since they become a huge pain in big and complex queries. I provided… Continue Reading
SQL Macro is finally here! Mustafa, 2020-10-282020-10-29 Hi, I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send… Continue Reading
Timestamp Arithmetic and Well Known Mistakes Mustafa, 2020-07-28 Hi, Lately, I started to see many developer uses timestamp types (like timestamp or timestamp with time zone) wrong especially in time arithmetic. So as a short reminder, adding and subtracting numbers to/from a DATE value will add/subtract number of days: Oracle PL/SQL Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequently From Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- ------------------- 2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 123456789101112 Select Sysdate "TODAY", Sysdate + 1 "TOMORROW", --add 1 day Sysdate - 1 "YESTERDAY", --subtract 1 day Sysdate + (1/24) "ONE HOUR LATER", -- add one - twenty forth day, since a day has 24 hours, it adds 1 hour Sysdate - (1/24) "ONE HOUR BEFORE", -- remove 1 hour Sysdate + (1/(24*60)) "ONE MINUTE LATER", -- add 1 minute, also 24*60=1440 is used frequently Sysdate + (1/(24*60*60)) "ONE SECOND LATER" -- add 1 second, also 24*60*60=86400 is used frequentlyFrom Dual; TODAY TOMORROW YESTERDAY ONE HOUR LATER ONE HOUR BEFORE ONE MINUTE LATER ONE SECOND LATER------------------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------2020-07-28 14:07:38 2020-07-29 14:07:38 2020-07-27 14:07:38 2020-07-28 15:07:38 2020-07-28 13:07:38 2020-07-28 14:08:38 2020-07-28 14:07:39 Basically that is all if you use add… Continue Reading
Is It Possible to Change SQL Statement in the Database? Mustafa, 2020-07-042020-07-04 YES! Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change… Continue Reading