Hi, We are migrating from Oracle version 11.2.0.4 to 12.1.0.2.0. And we kept the optimizer_feature_enable at 11.2.0.2. This system is using global temporary table extensively. As i believe we are staying on feature 11.2 so the private session level statistics wont be visible to the optimizer as per below blog.
I am struggling to understand the below part in the blog.
https://blogs.oracle.com/optimizer/global-temporary-tables-and-upgrading-to-oracle-database-12c-dont-get-caught-out
"If your database applications are upgraded without taking into account the new default, then workloads relying on shared statistics won't see statistics on GTTs where they had seen them before. Dynamic sampling might kick in where it hadn't before"
Say the session stats is enabled in the table level(which is default for 12C) , but its not used by the optimizer (due to 11.2 OFE), and in that case , it will trigger dynamic sampling in absence of any statistics or null stats in that table, which was the case pre 12C too. So how its going to impact us negatively, if we don't set the GTT table level preference to SHARED in this scenario? And as because we don't gather stats on GTT and relying on the dynamic sampling , so even in case of 12C the session private statistics will not show anything but null for that session. So isn't that same as earlier dynamic sampling scenario?