Panorama for Oracle databases

„Panorama” is my swiss army knife for performance analysis and troubleshooting on Oracle databases.

Completed over many years it contains solutions for analysis workflows based on practical experience.
As a GUI-application it ensures quick reaction with predefined workflows instead off dealing by hand with lots of SQL scripts.

Content

What does Panorama offer?

  • Dialogs and workflows for:
    • Performance-optimization of applications using Oracle databases
    • Analysis of runtimes and wait events, also back in history
    • Preparation of several Oracle database's internal information for easy evaluation
    • Systematic scan over your whole database for serveral performance pitfalls (more than 100 considered aspects)

  • it focuses on usage by DBAs and also by software developers with less Oracle knowledge
  • aims to many issues that are inadequately analyzed and presented by other existing tools such as Enterprise Manager
  • Panorama only reads via SELECT-SQLs from database. No write access, own database objects, compile etc. is needed at your database.
  • Supports:
    • Oracle databases on standard hardware as well as RAC-systems, engineered systems (EXADATA) and Autonomous Database in OCI
    • Tested against Oracle database versions beginning with Oracle 11.2
  • Here you can find a function overview by available top level menu entries

Resources

Which preconditions are required to use Panorama?

  • Java 21 or higher for Panorama.jar or runtime environment for Docker container.
  • Web browser with ES6-support to access Panorama-GUI (Firefox, Chrome, Safari, Edge)
  • Your Oracle-User used for login with Panorama requires at least the grant SELECT ANY DICTIONARY
    Additional grants/privileges are required for some particular functions:
    Grant Reason for the grant
    SELECT ANY DICTIONARY Default grant for access on DBA_xxx-views. This is the minimum requirement to run Panorama
    OEM_MONITOR Starting with Oracle 11.2.0.4 you need this grant to generate Oracle's builtin AWR-reports and ASH-reports
    SELECT_CATALOG_ROLE For getting results from DBMS_METADATA.GET_DDL and for Autonomous Database in Oracle cloud you need this role
    EM_EXPRESS_BASIC For getting results from DBMS_PERF (Performance Hub report) you need this grant
    ANALYZE ANY For getting results from DBMS_SPACE.SPACE_USAGE you need the ANALYZE privilege on the particular object or the ANALYZE ANY privilege
    SELECT ANY TRANSACTION Needed for selection on Flashback_Transaction_Query
    ADVISOR Needed for execution and result selection of the SQL Tuning Advisor using Package DBMS_SQLTUNE
    CREATE ANY SQL PROFILE Needed for creation of SQL profiles when using the SQL Tuning Advisor with Package DBMS_SQLTUNE
  • For non-admin users in the autonomous DB in the OCI cloud additional grants are required for some minor functions:
    GrantReason for the grant
    SELECT ON V$DIAG_ALERT_EXTAllow read on particular table
    READ ON SYS.DBMS_LOCK_ALLOCATEDAllow read access
    READ ON gv$BHAllow read access
    AUDIT_VIEWERAllow read access on unified audit view
  • Several functions of Panorama are functional only if either:
    • Oracle's "Diagnostic Pack" is licensed for the connected Oracle-DB or
    • You use the Panorama-Sampler instead to gather historic data (see also)
  • Panorama's function "SQL monitor" is available only if Oracle's "Tuning Pack" is licensed for the connected Oracle-DB

License

You can use Panorama free of charge under the terms and conditions of GNU General Public License.

How to run it?

Start running quickly

Panorama supports two ways to run it: as Java application or as Docker container.
Startup of the application may require some seconds up to a minute. Your application is up and running if this lines appear in the console log: 
* Listening on http://[::]:8080
Use Ctrl-C to stop

Run as Java application

  • Download the latest release of Panorama.jar from here
  • Start Panorama.jar by typing 
    java -jar Panorama.jar
    in a terminal

Run as Docker container

To download the image and start the Panorama application as Docker container type:
docker run -p 8080:8080 -d rammpeter/panorama
This starts the application within one or two minutes. Now you can connect to Panorama's login screen in your browser via 
http://<your server>:8080

Configuration options for customizing the Panorama application (Java + Docker)

You can configure Panorama by providing a config file in YAML format or setting environment variables.
Using a config file is the preferred method to avoid the compromise of secrets in environemnt variables.

Attributes for customization as environment variable or config file entry are: 
MAX_CONNECTION_POOL_SIZE
 This setting allows you to define the maximum number of connections in Panorama's connection pool
If you don't set this the default is max. 100 connections. That means:
  • Up to 100 different database connections are cached for reuse in Panorama even if they are inactive.
  • Maximum 100 concurrent client requests can be served at the same time limited by the connection number.
 
MAX_JAVA_HEAP_SPACE_MB
 Set maximum Java heap space memory usable for Panorama in megabytes (Default=1024).
The suggested value for multiuser production use is 4096.
This attribute works for Docker container only and must be set as environment variable. If running as pure Java program (Panorama.jar) then use the Java parameter -Xmx4096m instead. 
PANORAMA_CONFIG_FILE
 Specify the location and file name of the config file in YAML format.
This attribute works only if set as environment variable. 
PANORAMA_LOG_LEVEL=debug|info|warn|error
 Set the level for log output in console.
Default log level is "info".
 
PANORAMA_LOG_SQL=true
 Force the output of executed SQL statements in console log.
This helps to reproduce how Panorama gets information from database (without setting PANORAMA_LOG_LEVEL=debug which logs the same + much more). 
PANORAMA_MASTER_PASSWORD
 Setting the master password as a config attribute adds an additional menu item "Spec. additions"/"Admin login" in Panorama GUI.
Applying this password at "Admin logon" adds additional control functions to the GUI menu, e.g. Panorama-Sampler configuration. 
PANORAMA_USAGE_INFO_MAX_AGE
 Maximum age in days for logging records in Usage.log to comply with european GDPR rules.
Set to 0 to completely disable logging into Usage.log.
 
PANORAMA_VAR_HOME
 Panorama stores encrypted session connect data and usage log in local filesystem.
This setting controls the used directory.
For the Panorama.jar the default is a subdir Panorama in a temporary folder. For the Docker container the default is /var/opt/panorama and it is suggested to mount this directory to the local filesystem outside the Docker container. 
SECRET_KEY_BASE
 Server side key used to encrypt stored connection info. See also What about security? 
SECRET_KEY_BASE_FILE
 Location of file with server side key used to encrypt stored connection info. See also What about security?

Configure a Panorama instance running by "java -jar Panorama.jar"

Command line options for 
java -jar Panorama.jar <options>
: 
  • -p or --port <port>
    : Server listens on that port. Default port is 8080.
  • -b or --bind <IP address>
    : Bind server to a specific address. Default is 0.0.0.0. (all addresses)

Configure a Panorama instance running as Docker container

You can configure your Docker container:
  • Mount your host's tnsnames.ora at $TNS_ADMIN/tnsnames.ora into the container in a folder and tell it Panorama by setting $TNS_ADMIN in container so Panorama can refer to your config: 
    -v $TNS_ADMIN/tnsnames.ora:/etc/tnsnames.ora -e TNS_ADMIN=/etc

    Caution: with Docker in Windows sometimes you have to mount the file tnsnames.ora with 
    -v $TNS_ADMIN/tnsnames.ora:/etc
  • Mount directory for Panorama's config files (/var/opt/panorama) to a directory of your choice outside the container so the config persists even if container is rebuilded
  • Set your timezone for Docker container (default is UTC): 
    -e TZ="Europe/Berlin"
  • Set additional customizing properties for Panorama application as in config file or as Docker container environment variables

Configuration examples

Create a config file 
/var/opt/panorama/config.yml
with your secure settings, use environment variables for some other: 
# User-specific configuration file for Panorama
PANORAMA_LOG_SQL:  true
PANORAMA_VAR_HOME: /var/opt/panorama
SECRET_KEY_BASE:   923863l82g2j4797h87g13451v4s589es27g...
Call Panorama using this config file:
  • Example for Panorama.jar: 
    PANORAMA_CONFIG_FILE=/var/opt/panorama/config.yml java -jar Panorama.jar -p 8080
  • Example for Docker: 
                                
    docker run --name panorama -p 8080:8080 \
  -v $TNS_ADMIN/tnsnames.ora:/etc/tnsnames.ora \
  -v /var/opt/panorama:/var/opt/panorama \
  -e TNS_ADMIN=/etc -e TZ="Europe/Berlin" \
  -e MAX_JAVA_HEAP_SPACE_MB=1024 \
  -e PANORAMA_CONFIG_FILE=/var/opt/panorama/config.yml
  -d rammpeter/panorama

Run demo installation

  • Run preinstalled Panorama at http://158.101.168.240:8080 with access to a demo database instance at Oracle cloud.
    Use one of the TNS-aliases PANORAMATEST_xxx with user = 'panorama_test' and password = 'TryItOut2019'

What about security?

  • For production purposes you should run Panorama with SSL-encrypted web traffic (https). Easiest way for SSL connections is to enclose Panorama behind a reverse proxy ( Nginx/Apache/Traefik).
    This blog post describes how to configure Panorama and Nginx with docker-compose for SSL-encryption.
  • Even if SSL encryption (https) is not used, the user and password credentials are always asynchronously encrypted at network transfer from browser to the Panorama server by a public key where the private key for decryption is only known inside the Panorama server code.
  • Your Oracle DB connection's password is stored encrypted at server side and will be decrypted shortly in server memory only for the process of establishing connection to your target database.
    The key used for this encryption consists of the server side key (
    SECRET_KEY_BASE
    ) which is salted with a unique client key specific to your browser instance (stored as browser cookie).
    This way only requests from your browser are able to reuse the stored connect info for reestablishing the database connection.
  • If you retain your login info for further fast logins (checkbox 'Save logins' in statup dialog), this info is stored in local server file system at 
    PANORAMA_VAR_HOME
    completely encrypted the same way like passwords.
  • You can provide your individual server side key used for encryption by one of the following:
    • create a config attribute 
      SECRET_KEY_BASE
      with the key value.
    • create a file with the key value and set the config attribute 
      SECRET_KEY_BASE_FILE
      to the location of this file.
    The key can contain all printable characters and should be at least 128 characters long.
    Every change of the given key at server side leads to invalidation of all stored connection info.

    If no fixed key is given by 
    SECRET_KEY_BASE
    or 
    SECRET_KEY_BASE_FILE
    then a system generated key is used for encryption.
    This system generated key is stored in the folder declared by 
    PANORAMA_VAR_HOME
    .
  • Caution: If you don't set a value for 
    PANORAMA_VAR_HOME
    then your systems temporary folder is used to store the encyryption key base as well as the saved encrypted login credentials.
    That means this information may be lost at OS or container restart!

Behind the scenes: Implementation details

  • Panorama is an Ruby on Rails application that runs with JRuby. Web resources you can find here:
  • If you use 
    tnsnames.ora
    for TNS name resolution it should be located at server site in 
    $ORACLE_HOME/network/admin
    or in a directory targeted by environment variable 
    TNS_ADMIN
    .
  • Panorama serves an arbitrary number of multiple web-client sessions at one time, each client may choose his own target database connection
  • Panorama internally uses a connection pool, so client request may reuse existing free connections from pool if same credentials are used (TNS-data, user/password)
  • Inactive database connections in connection pool are terminated 10 minutes after last usage
  • Panorama renders one single web page via Ajax-calls. This way the back button of your browser is not supported to return to former page content.
  • Panorama writes short usage information to local plain text file 'Usage.log' in the directory targeted by the config attribute 
    PANORAMA_VAR_HOME

    This log records cointain the client IP address, DB name, time, called function and used TNS alias/URL
    Setting PANORAMA_USAGE_INFO_MAX_AGE = 0 disables this logging completely.
    You can find a menu item "Usage history" to view this log info if logged in as admin (see PANORAMA_MASTER_PASSWORD)
  • Panorama pools (reuses) DB-connections. You can view pooled connections at Panorama server via 
    http://<your server ip>:8080/usage/connection_pool