A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c.
Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only.
This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that my colleague Nigel Bayliss, our Optimizer Product Manager, has blogged about it. If you use this functionality please read all the details about this change here:
- Global Temporary Tables and Upgrading to Oracle Database 12c – Don’t Get Caught Out
https://blogs.oracle.com/optimizer/entry/gtts_and_upgrading_to_oracle“[,,] 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 [..]”
Thanks Nigel!
–Mike
PS: Original post said: Oracle Database 12.1.0.2 but I misread it – and thanks for the hint – it got introduced with Oracle Database 12.1.0.1 🙂
