This post is not about troubleshooting but rather about an interesting observation. I will start with a quick intro. Visibility map is a fork of the relation which maintains two bits: one for visible, the other for frozen tuples. These bits set per page. If all tuples in a page are visible to all active [ ]
One common performance issue in Linux systems is a program consuming unexpectedly high CPU. At first glance, high CPU usage alone doesn’t necessarily indicate a problem, but if the reason for the high usage is unclear, it warrants further investigation. One possible explanation (for the sake of this post) is that the program is spawning [ ]
When diagnosing a performance problem, interpreting the flow inside of the application is as important as other aspects of the whole process, such as tools and methodology. One of the key points is to figure out what the application threads are supposed to do and how these threads communicate. Once this is established, the rest [ ]
Lightweight locks (LWLocks) are a type of interprocess lock used by PostgreSQL to coordinate simultaneous access of multiple processes to a shared memory region. There are various reasons that can cause this lock to create issues in the database. I will now provide a short yet concise demo, followed by an exploration of the nature [ ]
It all started with a performance issue in one of our clusters. One of the components on the critical path turned out to be the OS scheduler which I analyzed to find out whether it was the offender itself or just a symptom, and the global slowness was caused by other factors in the stack. [ ]
Here are some six ways that can be used to force a query go with sequential scan access method in PostgresSQL. The objective is to eliminate any index access in the query. The test table looks as follows. Let s get started. Option I. Using enable_indexscan, enable_indexonlyscan and enable_bitmapscan parameters.The enable_indexscan parameter disables index access path. [ ]
Here is a quick and interesting observation regarding the %logons% statistics. The database version is Oracle 19c and, apparently, it applies to earlier versions as well. The question that I am trying to answer here is this How does a parallel query execution relate to %logon% statistics? Here is a test case. Let s check the [ ]
The quickest and simplest way of ensuring what has been populated in In-Memory column store (IMCS) is to query the V$IM_SEGMENTS view. Whenever an object is loaded into IMCS, the database will add a record about it into this view. In general, I guess the appearance of the IMCS as such is an attempt of [ ]
The following is a (short and quick) scenario I recently faced when analyzing a parallel query and its response time. This may not apply to earlier versions of Oracle though. The experiments were carried out in 19c. The weird behavior I observed was that V$SQL.EXECUTIONS column didn t get updated for the query under analysis when [ ]
I will not pretend that the idea presented in this post is aimed at addressing a number of troubleshooting scenarios but in some cases it may come in handy. For example, imagine a situation such that you cannot log in to the database because of a long-running query which makes it unresponsive. However, the OS [ ]