Informe maxdb

MaxDB 7.6 Performance Analysis

Werner Thesing

1

Performance Analysis: Tools

x_cons
shows current DB activity (snapshot)

Database Analyzer
detects possible bottlenecks collects and stores data at given intervals

Diagnostic Monitor (Command Monitor)
Lists single long running SQL commands

Diagnose Analyze (Resource Monitor)
Displays accumulated data for all SQL commands© SAP 2007 / MaxDB 7.6 Internals – Performance Analysis / Page 2

Performance Analysis Tools
MaxDB provides various tools and methods for the analysis of performance bottlenecks and monitoring current database activities. Some of these tools were originally developed only for testing and analysis in MaxDB development, but can also be used by experienced database administrators for performanceanalysis. The following are of particular importance for performance analysis: The x_cons console for monitoring current operations The Database Analyzer program for analyzing performance bottlenecks The diagnostic function DIAGNOSE MONITOR for identifying long-running or poorly-processed SQL statements The diagnostic function DIAGNOSE ANALYZE for displaying information about all current SQLstatements x_cons and Database Analyzer are stabd-alone programs and are called from the operating system command line. DIAGNOSE MONITOR is a part of the core functions of MaxDB. In SAP WebAS, all functions and results can be controlled and analyzed using transaction DB50 => Current Status or DB50 => Problem Analysis. Required parameter settings, if any, are menu-driven.

2

DB-Console: x_consDatabase console x_cons features:
process overview configuration overview observing session activities and wait states watching I/O activities and wait queues measuring of detailed task specific times

Call:
x_cons [] [] e.g. x_cons E30 show active 10 6 advantage: deltainformation using ‚interval‘ and ‚repeat‘ dbmcli –d … -u ... [–n ] db_cons advantage: works per remote connection todatabase host

© SAP 2007 / MaxDB 7.6 Internals – Performance Analysis / Page 3

DB Console x_cons The database console x_cons gives you a quick overview of the operating system resources that the database system is using, the distribution of the database session among the operating system threads, and the status of the active database sessions. You can also use other functions that areintended mainly for support employees and developers.

Start on shell level: x_cons [] [] x_cons help returns a complete overview of all available command functions.

The database console can also be addressed remotely via the DBM server.

3

DB Console x_cons (II)
x_cons [] [] ): SHOW ACTIVE SHOW ALL SHOW AIO show statistics/states SHOW IO SHOW DEV_IO show move info (load balancing)SHOW MOVEINFO SHOW QUEUES SHOW REGIONS SHOW RTE SHOW RUNNABLE [ DW | SV | US | GC] UKT sleep statistic SHOW SLEEP SHOW STATE SHOW STORAGE suspend reasons show task counts show tasks move info show task queues show task regions show task statistics SHOW SUSPENDS SHOW T_CNT SHOW T_MOVE SHOW T_QUEUE SHOW T_REG SHOW T_STAT SHOW TASKS Thread time usage SHOW THRD_TIMES SHOW SLEEP SHOW VERSIONS cancels thecommand of task displays help file time measurement kills the session of task CANCEL HELP TIME KILL [ DW | SV | US | T] (backup only) [ DW | SV | US | GC]

© SAP 2007 / MaxDB 7.6 Internals – Performance Analysis / Page 4

CANCEL KILL

index index

cancels the command executed by task kills the session of task

SHOW [ LONG | COMPRESS ] (Unix) DEBUGLEV DEBUGTASK level index setdebug level for the kernel writes back trace of task to knldiag resets counter about the following objects: IO incl. local counters of any task forces error at next read to devno forces error at next write to devno enables time measurements execute shell command (Unix only) exit console driver (Unix only)

RESET obj_cnt T_CNT REGIONS (ALL) ERRIOR ERRIOW TIME ! command QUIT devno devno enable

4...