Queries against DBA_SEGMENTS are not shared (not using bind variables)
.webp)
In one of the 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 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 c