SQL and PL/SQL Experts Panel Jagan Athreya, Oracle Tom Kyte, Oracle Graham Wood, Oracle Steven Feuerstein, Oracle Copyright © 2014, Oracle and/or its aﬃliates. All rights reserved. | Oracle Database In-‐Memory Central § Analyze objects in In-‐Memory Column Store §  View in-‐memory space allocaUon and frequency of access § Monitor/manage In-‐Memory Column Store conﬁguraUon § Integrated with ASH AnalyUcs for performance analysis Copyright © 2014, Oracle and/or its aﬃliates. All rights reserved. | 2 Real-‐Time Monitoring of Adap:ve Queries §  Oracle Database 12c’s AdapUve Query OpUmizaUon improves query performance by dynamically opUmizing execuUon plans at run-‐Ume Adap:ve Query Op:miza:on Parallel Distribu:on Methods §  Displays run-‐Ume and ﬁnal execuUon plans Adap:ve Sta:s:cs Adap:ve Plans Job Methods Plans §  Real-‐Ume SQL Monitoring enhanced to support AdapUve Plans Dynamic Sta:s:cs Automa:c Op:miza:on §  Helps understand how plan evolved by showing discrepancies in expected and actual rows processed SQL Plan Direc:ves §  InformaUon updated dynamically as query progresses §  Indicates plan is adapUve, and show current Status: “Resolving” or “Resolved” Copyright © 2014, Oracle and/or its aﬃliates. All rights reserved. | 3 Performance Hub Reports §  New interacUve report for analyzing AWR data §  Performance Hub report generated from SQL*Plus §  @$ORACLE_HOME/rdbms/admin/perfubrpt.sql §  OR calling dbms_perf.report_perfub(….) funcUon §  Single view of DB performance §  ADDM, SQL Tuning, Real-‐Time SQL Monitoring, ASH AnalyUcs §  Switch between ASH analyUcs, workload view, ADDM ﬁndings and SQL monitoring seamlessly §  Supports both real-‐Ume & historical mode §  Historical view of SQL Monitoring reports Copyright © 2014, Oracle and/or its aﬃliates. All rights reserved. | 4 AWR Warehouse: Features Administrators use historical AWR reports in several database diagnos:c features Performance Home ASH AnalyUcs AWR Report Source Database 1 Source Database 1 Load AWR Snapshots into Repository Compare Period ADDM Compare Period Report Central AWR Repository Database 1 Snapshots Database 2 Snapshots Database 3 Snapshots …… Database n Snapshots §  Central warehouse conﬁgured for long term AWR data retenUon §  Historical and ongoing AWR snapshots collected from databases enabled for AWR warehouse §  ETL jobs moves snapshots from source databases into AWR warehouse §  RetenUon period conﬁgurable for weeks, months, years or forever (default) Source Database 3 Copyright © 2014, Oracle and/or its aﬃliates. All rights reserved. |