Peter Ramm's Oracle performance analysis stuff

A quite common and recurring problem by developers is the use of literals in SQLs instead of prepared statements with bind variables. This problem pattern has been haunting me for years, there was a post about that in this blog also from 2017. It is now time to sum up the ideas that have emerged in the meantime to quickly identify the most problematic occurrences on this topic. The severity of this issue depends on the variety of the used literals. The more different literals are used in the SQL, the greater the problem becomes. If the used value does not come from outside (SQL injection), it could be tolerable if using a limited number of values for a filter condition directly in the SQL resulting in some 10 or 100 variations of a SQL statement. On the other hand, it could also be helpful in these cases to use the clear values in SQLs instead of a bind variables to support the optimizer in the use of histograms for different optimal execution plans. However, if thousands or mi...

The views gv$SegStat rsp. gv$Segment_Statistics provides several cumulative statistics per DB segment. This information is populated at AWR snapshots, visible via DBA_Hist_Seg_Stat. But sometimes you don't want to wait for the next AWR snapshot to get results or the resolution of the AWR snapshot period is too coarse. In this case the values from gv$Segment_Statistics are quite helpful if you could get the condensed results for the last x seconds only. Unfortunately, gv$Segment_Statistics contains only values cumulated since the last DB restart resp. since other events (possibly since the last load of blocks of a segment into the buffer cache). The following SQL collects the statistics value changes within the last x seconds by sampling twice and providing the differences. It shows all the segments and their statistics where values have changed in the considered period. The entire function is encapsulated within a single SELECT SQL, so that nothing needs to be installe...

Since release 18c there's an undocumented feature Parallel Shared Hash Join which introduces sharing memory between parallel query slaves. The required memory for these shared hash tables is allocated in a new memory region known as the Managed Global Area (MGA). See also doc. ID 2638904.1. This feature is particularly beneficial for costly HASH JOIN BUFFERED operations that spill large amounts of data into the temporary tablespace. These operations may benefit from transformation into HASH JOIN SHARED operations. By sharing hash tables between parallel query (PQ) processes, instead of each PQ server maintaining its own, runtime can be significantly reduced. This reduces overall memory requirements, enabling more data to be processed before spilling to disk in the temporary tablespace. There are several ways to activate the Parallel Shared Hash Join: set '_px_shared_hash_join'=true; at system or session level define the PQ distribution strategy for a particular...

Recently a problem occured using function based indexes: The optimizer switched to full table scan on a table with 3.2 billion rows although the used function expression is covered by a function based index. The expression often evaluates to NULL ond so this index has only 4.9 million rows ( < 1% of the table rows ) and a low selectivity of approx. 4 rows per key. Let's consider the simple case. The table INVOICE has two interesting columns: - CUSTOMER_ID 38 mio distinct values, not null, avg. 84 rows/key - OPEN_AMOUNT not null, 4,8 mio. records > 0 A function based index was created to quickly identify the small portion of rows of a certain customer where the OPEN_AMOUNT is > 0 CREATE INDEX IX_OPENAMOUNT ON INVOICE(CASE WHEN Open_Amount > 0 THEN Customer_ID END); This SQL is used to select the rows of a certain customer where the OPEN_AMOUNT is > 0 SELECT ID FROM Invoice WHERE CASE WHEN Open_Amount > 0 THEN Customer_ID END = :B1 ; The executi...

As of Oracle DB release 19.12 it is possible to use the new and the previous password for user logon simultaneously for a limited period. For details see New features guide . This is a great and long-awaited feature that lowers the barriers to changing passwords with less operational risk. But there's an obstacle for practical use: You must be able to monitor the users who are within this rollover period and are still using the old password. The only way of monitoring the use of old passwords known to me so far is to: Use Unified Auditing Create a policy for the LOGON action Check for the keyword "VERIFIER=12C-OLD" in the column AUTHENTICATION_TYPE of the logon records in unified audit trail. The following SQL provides an overview over all the users who are currently within a password rollover period, combined with info about old password usage: SELECT u.Account_Status, u.Password_Change_Date, u.Profile, u.UserName, u.Last_Login, a.DBUserName, a....

If you try to use parallel DML or direct load in SQL statements by placing the appropiate optimizer hints (PARALLEL, APPEND etc.), there might be several reasons why the DB ignores them. For example, if you want to execute DML in parallel you need to enable the session to do this before by executing ALTER SESSION ENABLE PARALLEL DML; . There are lots of similar pitfalls that may prevent the DB from using the expected execution path. The free performance analysis tool Panorama offers a way to scan your entire database for SQLs where parallel DML or direct load should have been used but in reality is not. Use the shown selection from menu "Dragnet investigation" (point 2.2.12) to execute this check and find SQLs that are not executed as expected. The result is sorted by the time this SQLs took to execute. The link in column "sql id" allows you to dig deeper in the details of the found SQL statement. Precondition is licensing of Oracle Diagnostics Pack or the u...