Multidimensional Visualization of Oracle Performance Using Barry007

Multidimensional Visualization of Oracle Performance Using Barry007† Tanel P˜odera and Neil J. Guntherb PoderC Pte. Ltd., Singaporea Performance Dynamcis Company, Castro Valley, California, USAb {tanel@poderc.com, njgunther@perfdynamics.com} Most generic performance tools display only system-level performance data us- ing 2-dimensional plots or diagrams and this limits the informational detail that can be displayed. Moreover, a modern relational database system, like Oracle, can concurrently serve thousands of client processes with different workload char- acteristics, so that generic performance-data displays inevitably hide important information. Drawing on our previous work, this paper demonstrates the applica- tion of Barry007 multidimensional visualization to the analysis of Oracle end-user, session-level, performance data, showing both collective trends and individual per- formance anomalies. 1 INTRODUCTION Like most other graphical performance monitoring tools, the performance visualization (PerfViz) mindset in the Oracle database world is currently very two-dimensional. The typical performance dashboard (Fig. 1) comprises standard strip charts with time running along the x-axis and the aggregation of certain database metrics posi- tioned on the y-axis. While this is often sufficient to get a rough idea of overall system performance, a dashboard view can be completely misleading [MM07]; especially when it comes to analyzing what individual Oracle pro- cesses are actually doing inside the database. The purpose of this paper to demonstrate how a multi- tude of Oracle performance metrics can be compressed into 2- and 3-dimensional visualizations by applying vari- ous barycentric coordinate transformations; collectively referred to as Barry007. In previous work [Gun92, JG07, GJ07, Gun08], we have applied barycentric coor- dinates to visualizing multiprocessor utilization (Barry3 coordinate system), web application response time data (Barry3 coordinates) and network-segment utilization † Copyright c⃝2008 Gunther, P˜oder. All Rights Reserved. This document may not be reproduced, in whole or in part, by any means, without the express permission of the authors. Permission has been granted to CMG, Inc. to publish in the Proceedings and the associated CD. Draft of May 30, 2018 Figure 1: Oracle 11g Enterprise Manager (Barry4 coordinate system). The numeric value N in the name BarryN indicates the number of performance metrics the can be visualized simultaneously. In the sub- arXiv:0809.2532v1 [cs.PF] 15 Sep 2008 sequent sections, we report on the latest application of Barry007 coordinates to the Oracle Wait Interface met- rics [Pod08], which are the primary performance indica- tors for Oracle database analysts. 2 CURRENT ORACLE INTERFACE Oracle Enterprise Manager, shown in Fig. 1, is a per- formance monitoring tool commonly used by the Oracle database analyst (DBA). Its focus is on the database (DB) response time components and it facilitates easy data drill-down and navigation. However due to its 2- dimensional layout, it tends to obscure the visual identi- fication of performance anomalies or performance trends of individual Oracle sessions or session groups. This lim- itation can lead to a situation where 1000 application sessions with optimal performance can skew the system- wide statistics enough that the deleterious symptoms of 100 suboptimal application sessions remain unnoticed. Oracle maintains its performance data as relational ta- bles in memory, known as v$ (pronounced “v-dollar”) tables, which can be queried using SQL calls. Oracle has had session-level response-time instrumentation built in since early 1990s. It is called the Oracle Wait Interface (hereafter, OWI) and it presents a view of the wait timing information contained in the v$ tables. Oracle DB execution (on CPU) OS syscall execution (on CPU) Disk queueing and service (off CPU) begin OWI wait T0= gettimeofday() end OWI wait T1= gettimeofday() IO queueing time IO service time CPU queuing time CPU service time CPU service time Time Figure 2: Definition of OWI waiting times A word of caution may be appropriate here for those not familiar with Oracle OWI nomenclature. What is termed “wait time” in OWI is actually a response time [wJH03]. In Fig. 2, each OWI wait metric (Rw) is the sum of all actual wait times (Wi), in the sense of waiting for service, measured during i intervals and the corresponding service times (Si). More formally, we can write: Rw = X i Wi + X i Si (1) There are some exceptions to this. For example, DB CPU PCT is purely a measure of processor service time (execution cycles) without any Wi components. This is determined by what instrumentation and data sources are available to the OWI. See Appendix A for further discussion of this point. The response time data is gathered at database-session level, allowing detailed performance analysis of individ- ual database users. This presents many opportunities for performance diagn

…(Full text truncated)…

This content is AI-processed based on ArXiv data.