In one of our mission-critical databases, I have discovered excessive memory utilization for SQLs querying DBA_SEGMENTS and they were not using bind variables. Total sharable memory usage for SQLs using the same plan_hash_value was up to 7GB on 4 instances. Sadly, it was an internal query issued from FBDA (Flashback Data Archiver) process.
Before I deep dive into the issue, I want to explain "What is hard parse?"
Experienced DBAs can skip this section.
What is Hard Parse?
When an application or user issues a SQL statement, a parse call to prepare the statement for execution is made. The parse call opens a cursor, which is a handle for the session specific private SQL area that holds a parsed SQL statement and other processing information. During that time, the database performs the Syntax Check, Semantic Check and Shared Pool Check. At the shared pool check phase, The text of the SQL statement is hashed. If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
If there is a matching hash value for an existing SQL statement in the shared pool, then the text of the matched statement is compared to the text of the hashed statement to verify if they are identical. Assuming that the "CURSOR_SHARING" parameter is set to its default of EXACT, to be considered identical, the text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments. Also, SQL statements that differ only in literals cannot use the same shared SQL area and hard parse is performed.
Hard parse is a resource-intensive process because during the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.
Reducing the number of hard parses results in reduced CPU usage.
So it is important to avoid hard parses for better performance, especially in OLTP systems. One of the DBA's primary jobs is detecting and pointing out those kinds of queries.
The reduction of hard parsing is mostly accomplished by using bind variables (not using literals) or modifying the "CURSOR_SHARING" parameter. For more information on the identification of SQL statements using literals, you can inspect Connor McDonald's post "
SQL statements using literals".
Excessive memory utilization from FBDA process
I will show how we detected the issue briefly. We used the query below to detect the top 20 SQL plans sharing the same plan and consuming the most memory from the shared pool.
The most memory-consuming plan_hash_value was 44909958. When I queried the SQL statements using that plan, what I found was that on four instances a total of 23910 SQL statements were sharing the same plan and they were only differentiating from each other with literal values. Below is a sample of these SQL statements.
These SQL statements were not issued from a user/developer session or an application. Actually, they were coming from sys internal sessions (FBDA background process). We diagnosed that with the below SQL statements.
Actually, the problematic query is a very simple one. In my humble opinion, the FBDA process is checking periodically (I guess hourly) the sum of blocks in dba_segments view for every object with a segment (tables, indexes, etc.) to calculate storage allocated from the Flashback Data Archive tablespace.
Also, the solution for the problematic query is straightforward, JUST TO USE BIND VARIABLES.
I searched Metalink for a bug or enhancement request, but I could not find anything really helpful.
Interestingly enough, on another database which also uses flashback time travel (formerly known as flashback data archive) feature efficiently, there were no signs of "excessive memory utilization from FBDA process". In the library cache, there were no SQL statements similar to our problematic query.
Here is the test case for the issue.
So far, the problem still exists in 19.16 Release Update.
But I will come up with a workaround for the issue. These SQL statements look like they are written to calculate storage allocated from the Flashback Data Archive tablespace. The FBDA process is checking periodically whether a QUOTA is exceeded or not.
To eliminate the excessive memory usage from FBDA process and avoid from ORA-4031;
REMOVE QUOTA CLAUSE FROM YOUR FLASHBACK DATA ARCHIVES with the below statement and also create your new flashback data archives without the quota clause.
No more checks for segment sizes.
Hope it helps.
.webp "Child cursor without bind variable")
Comments
Post a Comment