에너쓰오라클

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.

Oracle 12c부터는 DBMS_QOPATCh package를 통해서 sqlplus 상에서 patch 정보를 확인할 수 있답니다..

Database node가 많은 RAC 환경에서는 편할 수 있겠네요.. 

Queryable Patch Inventory

Using DBMS_QOPATCH, Oracle Database 12c provides a PL/SQL or SQL interface to view the database patches that are installed. The interface provides all the patch information available as part of the OPatch lsinventory -xml command. The package accesses the Oracle Universal Installer (OUI) patch inventory in real time to provide patch and patch meta information.

Using this feature, users can:

• Query what patches are installed from SQL*Plus.

• Write wrapper programs to create reports and do validation checks across multiple environments.

• Check patches installed on Oracle RAC nodes from a single location instead of having to log onto each one in turn.