Oracle 12c New features : Session-Private Statistics for Global Temporary Tables

Oracle 12c 부터 session-specific GTT(Global Temporary table)에 대해 세션 개별의 통계정보 수집이 가능합니다. 

GTT의 경우 성능이슈로 permanent table을 만들어 drop 하는 등의 작업을 많이 했는데 

12c의 경우 session-private statistic 수집으로 이러한 이슈들이 해결되지 않을까 기대해 봅니다. 

SQL> exec dbms_stats.gather_table_stats(ownname=>'SH', tabname=>'TEMP_GTT');

but, Parallel DML (update, delete, merge)에 대한 제약은 여전히 11g와 동일하네요..

Session-Private Statistics for Global Temporary Tables

Traditionally, global temporary tables had only one set of statistics that were shared among all sessions even though the table could contain different data in different sessions. In Oracle Database 12c Release 1 (12.1), global temporary tables now have session-private statistics. That is a different set of statistics for each session. Queries issued against the global temporary table use the statistics from their own session.

Session-private statistics for global temporary tables improves the performance and manageability of temporary tables. Users no longer need to manually set statistics for the global temporary table on a per session basis or rely on dynamic sampling. This reduces the possibility of errors in the cardinality estimates for global temporary tables and ensures that the optimizer has the data to identify optimal execution plans.

See Also:

Oracle Database SQL Tuning Guide for details