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.

What does it 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 and engineered systems (EXADATA)
    • Oracle database versions beginning with Oracle 10.2
  • Here you can find a function overview by available top level menu entries

Which preconditions are required?

  • Java runtime environment, version 7 or above to run Panorama application. Beginning with Java 8 you also need the Java Cryptography Extension Files installed.
  • Web browser to access Panorama-GUI
  • Your Oracle-User used for login with Panorama requires the following grants:
    • "SELECT ANY DICTIONARY" as default grant for access on DBA_xxx-views
    • Starting with Oracle 11.2.0.4 you also need the grant "OEM_MONITOR" to generate Oracle's builtin AWR-reports and ASH-reports
    • For getting results from DBMS_METADATA.GET_DDL you need the SELECT_CATALOG_ROLE
    • For getting results from DBMS_PERF (Perfmance Hub report) you need the role EM_EXPRESS_BASIC
  • Several functions of Panorama are functional only if either:
    • Oracle's "Diagnostic Pack" is licensed for the connected Oracle-DB or
    • You use data generated by Panorama-Sampler instead (see also)
  • Panorama's function "SQL monitor" is available only if Oracle's "Tuning Pack" is licensed for the connected Oracle-DB

How to get it?

How to run it?

Start running quickly

  • Download file Panorama.war
  • Open a terminal window, navigate to the directory containing Panorama.war and type
    java -jar Panorama.war
    no matter if your are running Windows, Linux or Mac-OS
  • This starts the embedded Jetty application server within one or two minutes, accessible at port 8080 via
    http://<your server ip>:8080
  • Now you can connect to Panorama's login screen in your browser via
    http://<your server ip>:8080/Panorama

Customize your installation

  • Environment variables that are evaluated by Panorama-Server:
    • PANORAMA_VAR_HOME
      : Panorama stores encrypted session connect data and usage log in your local filesystem.
      You can control the directory for this files by setting the environment variable
      PANORAMA_VAR_HOME
      to your target directory.
      Without defining
      PANORAMA_VAR_HOME
      it will use a subdir 'Panorama' at your systems default temporary directory.
    • PANORAMA_LOG_SQL=true
      : Write the complete text of each executed SQL-Statement to log output.
      This helps to reproduce how Panorama gets information
    • 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.
  • You can specify the following additional Java-options executing
    java <options> -jar Panorama.war
    :
    • -Xmx1024m -Xms1024m
      : startup and max. heap memory allocation for JVM,
      minimum value of 512 MB suggested,
      for production use choose 1024m or higher (suggestion: 4096m)
    • XX:MaxPermSize=512M
      : object space for java classes, suggested value 512MB (until Java 7, unnecessary beginning Java 8)
    • -Djava.io.tmpdir=mydir
      : define working directory for extraction of jar file, default is system's temp dir
    • -Dwarbler.port=9090
      : Use another http-port than default 8080 for Panorama's URL
  • Here you can find a sample shell-script to start Panorama for Unix/Linux
  • Panorama.war can also be run as web archive in any Java JEE application server or web container like Tomcat, Glassfish, JBoss etc. .
    For production use with SSL encrypted https-connections I personally use Tomcat.

Run demo installation

Start as Docker image

Panorama is also available as docker image at dockerhub.com for immediate execution at your docker host.
To download and start Panorama immediately type:
docker run --name panorama -p8080:8080 -d rammpeter/panorama
You can customize your Docker container:
  • Mount your host's tnsnames.ora at $TNS_ADMIN/tnsnames.ora into the container in folder /etc 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
  • 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 maximum Java heap space memory usable for Panorama in megabytes (Default=1024):
    -e MAX_JAVA_HEAP_SPACE_MB=1024
Example:
docker run --name panorama -p8080:8080 \
  -v $TNS_ADMIN/tnsnames.ora:/etc/tnsnames.ora \
  -v /var/opt/my_panorama_config:/var/opt/panorama \
  -e TNS_ADMIN=/etc -e TZ="Europe/Berlin" \
  -e MAX_JAVA_HEAP_SPACE_MB=1024 \
  -d rammpeter/panorama
Startup of application in docker container may require one or two minutes. Your docker container is up and running if this two lines appear in log calling
docker logs panorama
> INFO:oejs.ServerConnector:main: Started ServerConnector@6a83bd10{HTTP/1.1}{0.0.0.0:8080}
> INFO:oejs.Server:main: Started @137606ms
                

How to get help?

What about security?

  • For production purposes you should run Panorama with SSL-encrypted web traffic (https). Easiest way for SSL connections is to run Panorama in a configured Tomcat etc. or use Nginx/Apache as proxy.
  • Your oracle password is stored encrypted in server memory and will be decrypted shortly only for the process of establishing connection to your target database.
    The key used for this encryption consists of a server key which is salted with a unique client key specific to your browser instance.
    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 completely encrypted the same way like passwords.
  • You can customize the server key used for encryption by setting the environment variable
    SECRET_KEY_BASE
    to value of your choice.
    Every change of
    SECRET_KEY_BASE
    at server site leads to invalidation of all stored connection info.

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 'panorama_usage_info.log' in the directory targeted by environment variable
    PANORAMA_VAR_HOME

    You can view this log info via
    http://<your server ip>:8080/Panorama/usage/info
  • Panorama pools (reuses) DB-connections. You can view pooled connections at Panorama server via
    http://<your server ip>:8080/Panorama/usage/connection_pool

Possible runtime problems

  • Without defining environment variable
    PANORAMA_VAR_HOME
    before starting Panorama you will lose your stored database connection info during restart because Jetty chooses a new temporary subdir for every startup.
  • Beginning with Java8 u102 you need to install the "Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files for JDK/JRE 8" to use strong encryption with Panorama Panorama will remind you at startup if this is necessary. Here you may find the JCE-files for Java 8
  • Environment variable
    CLASSPATH
    should not be set before starting Panorama.war to ensure that only Panorama's elements are used, especially the builtin JDBC driver for Oracle
    If your are unsure for example what JDBC driver is used you can monitor class load paths by:
     java -verbose:class -jar Panorama.war | grep Oracle
  • Some Java-problems may occur with IBM JVM. If you have trouble with that please use JVM from SUN/Oracle.
  • Panorama runs with Java up tp Java 9. There's still an open issue between JRuby and Java 10, so running with Java 10 is not yet supported (2019/09).
  • Under some rare circumstances (e.g. at Windows 10) Panorama's URL is
    http://<your server ip>:8080
    without the suffix "Panorama"
  • If you encounter the error
    Java::JavaLang::OutOfMemoryError (PermGen space)
    with Java version less than Java 8 than define initial value and limit for perm space like
    java -XX:PermSize=128M -XX:MaxPermSize=256M
  • If you run Panorama on virtualized headless Linux server (e.g. in cloud environment) and your browser hangs after first request:
    • Reason: Your server does not generate enough entropy values in /dev/random, so read on /dev/random blocks during encryption operation
    • Solution: Start Panorama with non-blocking entropy generator by option
      java.security.egd=file:/dev/urandom
    • If using the embedded Jetty app-server then type
      java -Djava.security.egd=file:/dev/urandom -jar Panorama.war
    • If using Tomcat then set
      export CATALINA_OPTS=-Djava.security.egd=file:/dev/urandom
      before starting Tomcat