Oracle 12c New Features : Enhancements to System Statistics

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.

See Also:

Oracle Database SQL Tuning Guide for details