Hi all. As part of a POC for measuring system performance, I wanted to invalidate cache used in some certain sql select statements in our Hana instance. Because cache used to mislead our performance test. And I did some reading in documentation and found this article (docs). And by running the commands below multiple times, I achieved what I wanted. I pinned and unpinned lots of plans with some hints. And I completed my experiment but I realized an issue at the end. Approximately 45 of the plans used in performance test cannot be unpinned.
ALTER SYSTEM PIN SQL PLAN CACHE ENTRY <PLAN_ID> WITH HINT (IGNORE_PLAN_CACHE , NO_RESULT_CACHE); ALTER SYSTEM UNPIN SQL PLAN CACHE ENTRY <PLAN_ID>;
So, there are 45 rows In "PINNED_SQL_PLANS" system view and all the rows have 0 in their PINNED_PLAN_ID column. There is no exception to it, this column consists of 0. I have no required plan Ids to run "ALTER SYSTEM UNPIN SQL PLAN CACHE ENTRY <PLAN_ID>;" That is why I can not unpin them.
Then, I also ran "ALTER SYSTEM UNPIN SQL PLAN CACHE ENTRY <PLAN_ID>" with every single PLAN_ID in "M_SQL_PLAN_CACHE" system view. I still couldn't unpin them. According to this official article, "Alter System Unpin.." command should've worked with plan ids in "M_SQL_PLAN_CACHE" view.
My question can be divided into 2 groups.
1- What does having 0 in PINNED_PLAN_ID column of all the rows in "PINNED_SQL_PLANS" mean?
2- I even tried unpinning all the plan ids listed in "M_SQL_PLAN_CACHE" system view but those 45 plans are still pinned. How do I unpin them?
I checked and those 45 plans pinned with IGNORE_PLAN_CACHE , NO_RESULT_CACHE are still effective. I need to find a way to unpin them.
Thanks,
Tunahan