에너쓰오라클

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에 Exadata를 위한 System Statistics 수집 기능 추가되었습니다. 근데 이건 11gR2(exadata X2)에서도 해줘야 됬던 기능인거 같은뎅..  

참고 : http://kerryosborne.oracle-guy.com/2013/09/system-statistics-exadata-mode/

암튼 system statistic 수집은 (workload statistics mode) 아래의 system 성능 정보를 수집하며 SQL optimizer가 SQL Plan 수립에 이 system 성능정보를 참고하므로 꼭 한번은, 그리고 system hardware 변경시에 수행해야 합니다..

• Single and multiblock read times
• mbrc
• CPU speed (cpuspeed)
• Maximum system throughput
• Average slave throughput

system statistics 수집 방법 

-- 특정 주기동안 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start') 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop') 

-- 특정 interval(분단위) 동안 수집 

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval=>N)

-- exadata 성능 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('exadata')

-- noworkload statistics 수집

SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS()

Enhancements to System Statistics

System statistics allow the optimizer to account for the hardware on which the database system is running. With the introduction of smart storage, such as Exadata storage, the optimizer needs additional system statistics in order to account for all of the smart storage capabilities.

The introduction of the new system statistics gathering method allows the optimizer to more accurately account for the performance characteristics of smart storage, such as Exadata storage.

Tom Kyte has picked his top 12 features of Oracle Database 12c and put them into a presentation. Here are his picks:

Tom Kyte, Vice President of Oracle, shares the top 12 features of 12c Databases at the Oracle Database 12c Launch 2013.

1. Even better PL/SQL from SQL

2. Improved defaults

3. Increased size limits for some datatypes

4. Easy top-n and pagination queries

5. Row pattern matching

6. Partitioning improvements

7. Adaptive execution plans

8. Enhanced statistics

9. Temporary undo

10. Data optimization capabilities

11. Application Continuity and Transaction Guard

12. Pluggable databases

On Oracle Database 12c, Part 1

On Oracle Database 12c, Part 2