158646276-Monitoring-Exadata-Performance.pptx

Exadata

1. Monitoring Exadata Performance --EXADATA COE 1

2. The monitoring tasks can be divided into the following categories: SQL statement monitoring Database layer utilization and efficiency monitoring Storage cell layer utilization and efficiency monitoring The monitoring tasks 2

3. SQL Statement Monitoring The SQL Statement Monitoring can be done in following ways Monitoring SQL Statements with Real-Time SQL Monitoring Reports Reporting Real-Time SQL Monitoring Data with DBMS_SQLTUNE Manually Querying Real-Time SQL Monitoring Data Monitoring SQL Statements using V$SQL and V$SQLSTATS 3

4. Click the SQL Monitoring link in the Enterprise Manager performance page SQL Statement Monitoring 4

5. SQL Statement Monitoring 5

6. SQL Statement Monitoring 6

7. SQL Statement Monitoring 7

8. SQL Statement Monitoring Without using Grid Control or Enterprise Manager Database Console the SQL monitoring details can be extract using the DBMS_SQLTUNE.REPORT_SQL_MONITOR package function . 8

9. SQL Statement Monitoring 9

10. We can access the data of Grid Control or Enterprise Manager Database Console from the V$ views directly. GV$SQL_MONITOR view contains the statement execution-level monitoring data. The GV$SQL_PLAN_MONITOR view contains execution plan line-level metrics ,monitored and updated in real time. We can query the IO_INTERCONNECT_BYTES and compare it to PHYSICAL_READ_BYTES and PHYSICAL_WRITE_BYTES to determine the offloading efficiency by each individual execution plan line. The GV$ACTIVE_SESSION_HISTORY view contains columns like SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION and SQL_PLAN_OPTIONS.We can query these columns, in addition to SQL_ID, to find the top rowsources of an SQL execution plan, too, instead of just listing the top SQL statement. SQL Statement Monitoring 10

11. The V$SQL and V$SQLSTATS views still do have some advantage over SQL Monitoring and ASH-style sampled data in a few cases. For example, if you want to measure metrics like the number of executions, buffer gets, parse calls, fetches, or rows returned by the SQL child cursor, you can get this data from both Real-time SQL Monitoring (V$SQL_MONITOR) or the V$SQL/V$SQLSTATS views, but not ASH. But the problem with SQL Monitoring is that it doesn’t monitor short-running queries at all, therefore making it unusable for keeping track of OLTP-style small queries executed many times per second. SQL Statement Monitoring 11

12. Exadata Storage cell monitoring done can be done in following ways Monitoring with metrics & alerts using cellcli . Monitoring of active requests on the Exadata Storage Server . Using Oracle Enterprise Manager for monitoring . Using Oracle Integrated Lights Out Manager . Storage Cell Layer Monitoring 12

13. show metric short names CellCLI> LIST METRICDEFINITION CD_IO_BY_R_LG CD_IO_BY_R_LG_SEC CD_IO_BY_R_SM CD_IO_BY_R_SM_SEC ….. ….. N_MB_SENT N_MB_SENT_SEC N_NIC_KB_RCV_SEC N_NIC_KB_TRANS_SEC N_NIC_NW N_RDMA_RE show metric description and details CellCLI> LIST METRICDEFINITION CL_CPUT DETAIL; name: CL_CPUT description: "Percentage of time over the previous minute that the system CPUs were not idle." metricType: Instantaneous objectType: CELL unit: % list latest snapshot in detail CellCLI> LIST METRICCURRENT CL_CPUT DETAIL; name: CL_CPUT alertState : normal collectionTime : 2011-05-16T09:31:53-05:00 metricObjectName : enkcel01 metricType: Instantaneous metricValue : 5.3 % objectType: CELL show historical metric snapshots CellCLI> LIST METRICHISTORY CL_CPUT; CL_CPUT enkcel01 2.2 % 2011-05-06T12:00:46-05:00 CL_CPUT enkcel01 2.4 % 2011-05-06T12:01:49-05:00 CL_CPUT enkcel01 2.1 % 2011-05-06T13:00:15-05:00 CL_CPUT enkcel01 0.3 % 2011-05-06T13:01:15-05:00 TRY_TM Monitoring MetricsUsing CellCLI 13

14. to display the alert definitions for the cell – CellCLI> LIST AERTDEFINITION HardwareAlert DETAIL name: HardwareAlert alertShortName: Hardware alertSource: Hardware alertType: Stateless description: "Hardware Alert" metricName: Alert History – CellCLI> LIST ALERTHISTORY WHERE severity = 'critical‘ AND examinedBy = '' DETAIL Acknowledging Alerts – CellCLI> ALTER ALERTHISTORY 123456633/All examinedBy=' JohnSmith ‘ Propagating Alerts via SNMP and SMTP – CellCLI> ALTER CELL smtpServer='smtp.example.com', - smtpFromAddr='[email protected]', smtpFrom='John Smith', - smtpToAddr='[email protected]', - snmpSubscriber=((host=host1),(host=host2)), - notificationPolicy='critical, warning, clear', - notificationMethod='mail, snmp ‘ Creating Threshold Alerts CellCLI> CREATE THRESHOLD gd_io_errs_min.gd_threshold warning=100, critical=200, - comparison='=' Monitoring Alerts Using CellCLI 14

15. Monitoring Active Requests – The I/O requests that are actively processed by the Exadata Storage Servers can be monitored using the activerequest – LIST ACTIVEREQUEST WHERE ioType = < type of the I/O activity > DETAIL Monitoring Active requests 15

16. The System Monitoring Plug-in for Oracle Exadata Storage Server enables you to manage and monitor Storage Server targets using Grid Control . To monitor Exadata Storage Server create a group and add storage cell metrics. Monitoring Using Grid Control Exadata Storage Server Plug-In 16

17. 17 Monitoring Using Grid Control Exadata Storage Server Plug-In

18. 18 Monitoring Using Grid Control Exadata Storage Server Plug-In

19. 19 Monitoring Using Grid Control Exadata Storage Server Plug-In

20. THANK YOU 20