Queries against DBA_SEGMENTS are not shared (not using bind variables)
.webp)
I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there. 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 pr...