FarragoSystemManagement

From Eigenpedia

Jump to: navigation, search

This page describes features useful to a system administrator for managing and monitoring a running farrago system.

  • The sql command ALTER SYSTEM can modify various system parameters.
  • Lists of active sessions and of active statements are available as virtual views, generated on demand with current information.
  • The view SESSIONS_VIEW lists all active sessions. Each row describes a session, giving a unique session id, and such attributes as the jdbc connection URL, the user, the transaction status, etc.
  • The view STATEMENTS_VIEW lists all executing statements. Each statement has a row, which gives a unique statement id, and such attributes as the containing session (as its session id, from the sessions_view), the start time, the SQL being executed, the current value of any dynamic parameters, etc.
  • The REPOSITORY_PROPERTIES_VIEW lists properties of the MDR repository used to store Farrago's catalog.
  • The UDX REPOSITORY_INTEGRITY_VIOLATIONS() can be used to check for integrity violations in the MDR repository.
  • The view OBJECTS_IN_USE_VIEW can be used to see which objects are in use by which statements.
  • The UDX THREADS can be used to list threads running in the JVM.
  • The UDX THREAD_STACK_ENTRIES can be used to dump the stack for each thread
  • The UDX SYSTEM_INFO can be used to retrieve information about the environment in which Farrago is running (e.g. environment variables, system properties, and OS and hardware information).
  • The UDX PERFORMANCE_COUNTERS can be used to monitor performance counters such as cache hit ratios.
  • The function SLEEP can be used to introduce an artificial delay into a SQL statement.
  • The procedure FLUSH_CODE_CACHE can be used to discard all entries from the global code cache.
  • The view SESSION_PARAMETERS_VIEW can be used to retrieve private parameter settings for the current session.
  • The procedure STAT_SET_ROW_COUNT can be used to synthesize an artificial row count for a table
  • The procedure STAT_SET_PAGE_COUNT can be used to synthesize an artificial page count for an index
  • The procedure STAT_SET_COLUMN_HISTOGRAM can be use to synthesize an artificial histogram for a column
  • The views PAGE_COUNTS_VIEW, ROW_COUNTS_VIEW, HISTOGRAMS_VIEW, and HISTOGRAM_BARS_VIEW can be used to retrieve statistics about tables, columns, and indexes
  • The view SEQUENCES_VIEW can be used to retrieve the state of generated columns
  • An administrator can kill an executing statement by calling the system procedure KILL_STATEMENT(statement_id) or KILL_STATEMENT_MATCH(sql fragment).

Killing a statement aborts its execution and frees all resources allocated to the statement. A corresponding JDBC Statement will be invalidated, and any JDBC ResultSet will be terminated (with an SQLException to indicate that the statement was killed from outside.)

KILL_STATEMENT() terminates a single statement, specified by id (as seen in the statements_view). KILL_STATEMENT_MATCH() terminates all statements whose sql commands contain the provided text. KILL_STATEMENT() is like the unix command kill, and KILL_STATEMENT_MATCH() is like pkill.

  • An administrator can kill an active session by calling the system procedure KILL_SESSION(id). The session is specified by the id that appears in the sessions_view.

Killing a session kills all statements executing in the session, aborts any transactions, and frees all allocated resources. A corresponding jdbc Connection is invalidated: any further use gets an SQLException. (However, if the optional boolean cancel_only parameter is specified as true for any of the kill calls, then the effect is just to request an asynchronous cancel and return immediately; no wait or resource release is performed, and the session or statement remains valid.)

(All the system views and system procedures described on this page are located in the schema sys_boot.mgmt. The code for the system procedures is in the java package net.sf.farrago.syslib).

Retrieved from "http://pub.eigenbase.org/wiki/FarragoSystemManagement"
Personal tools