Posts

Oracle-DB: Identify non-relevant indexes for secure deletion

Image
A sensitive issue for DBAs and developers is often the detection and removal of indexes in Oracle databases that are not or no longer needed.
In practice systems can be found that allocate more than 50% of the storage with indexes without any productive relevance.
Nevertheless, this potential for a simple reduction of storage requirements and system load is regularly not used, freely after "never touch a running system" or "why should I burn my fingers here and then this index is still needed somewhere".
This article demonstrates how to securely determine irrelevant indexes using the freely available analysis tool "Panorama".

Why define indexes on tablesOptimizing accesses from User-SQL
- Reduction of access to the result-relevant records when reading table dataGuarantee of uniqueness
- Declaration of unique indexes or use of the index for primary key or unique constraintsProtection of Foreign Key Constraints
- Preventing full table scans when deleting or u…

Oracle-DB: List tables suitable for partition exchange

Partition Exchange requires a partitioned and a non-partitioned table with the same column and index structure. All indexes of the partitioned table have to be local partitioned.

The following SQL lists all combinations of structure-identical partitioned and non-partitioned tables that are suitable for partition exchange.

This information may be useful before changing the structure of tables or indexes to be informed about possible partition exchange operation and to prevent from crashing such operations.

WITH Tables AS (SELECT /*+ NO_MERGE MATERIALIZE */ tc.Owner, tc.Table_Name, COUNT(*) Columns, SUM(ORA_HASH(tc.Data_Type) * tc.Column_ID * tc.Data_Length * NVL(tc.Data_Precision,1) * NVL(DECODE(tc.Data_Scale, 0, -1, tc.Data_Scale),1)) Structure_Hash FROM DBA_Tab_Columns tc JOIN DBA_Tables t ON t.Owner = tc.Owner AND t.Table_Name = tc.Table_Name /* exclude views */ …

Using Panorama for autonomous database in Oracle cloud

Panorama is now able to connect to autonomous databases in the Oracle cloud.
At start of Panorama you must ensure that environment variable TNS_ADMIN targets to a directory containing:
the tnsnames.ora provided by Oracle cloudthe unzipped files from the Oracle wallet provided by Oracle cloud:Oracle wallet files (ewallet.sso, ewallet.p12) Java KeyStore (JKS) files (truststore.jks, keystore.jks).Connection properties required to use Oracle Wallets or Java KeyStore (ojdbc.properties)

Panorama: List Oracle trace files and it's content

Image
Starting with Release 12.2 of Oracle database you can list and view trace files from database server file system by SQL selects.

Dynamic performance views for representation of trace files are:

V$DIAG_TRACE_FILE: lists files from ADR (automatic diagnostic repository)V$DIAG_TRACE_FILE_CONTENTS: lists text content of trace files with one record per linePanorama now supports viewing server side trace files via menu "DBA general" / "Server Files" / "Server Trace Files".
This view shows all existing trace files for chosen period and optional including or excluding patterns for trace file name.




Click on trace file name shows the content of trace file with some context information.



If useful you can proceed to active session history of the DB-Session by clicking in column "Session".

This works for server trace files as well as for user trace files created by "ALTER SESSION SET SQL_TRACE=TRUE".

This way people without access rights on database …

Panorama: Determining candidates for storage reorganization in Oracle-DB

Image
Knowing that Oracle-DB does not automatically release storage below the high water mark it would be necessary to do it yourself.
Usually you may scan your database from time to time to identify tables, indexes or lobs where reorganization of these objects can make significant amount of storage in tablespace available for other objects.

The PL/SQL package DBMS_SPACE allows consideration of free space in DB blocks for an object.
This information can be used to forecast the effect of object reorganization.

Unfortunately DBMS_SPACE.Space_Usage takes longer time for evaluation of larger objects, so it is quite expensive to scan a whole schema or system using DBMS_SPACE.

Following I'll show how the performance analysis tool Panorama handles determining candidates for reorganization.

To determine candidates for deeper inspection Panorama uses a simple calculation based on avg. row-length, Pct_Free and Ini_Trans to list suspected objects that are worth to reorganize.
Sorting this list des…

Panorama: Show history of Dynamic Remastering in Oracle RAC-cluster

Image
Unfortunately, dynamic remastering in RAC clusters has very little official documentation.

Some useful information about this topic you may get here:

http://oracleinaction.com/dynamic-remastering/https://www.hhutzler.de/blog/a-closer-look-into-drm-dynamic-resource-management/https://orainternals.wordpress.com/2010/03/25/rac-object-remastering-dynamic-remastering/

As part of the performance analysis app Panorama you can view the instance-affinity of tables, indexes and their partitions as well as the history of DRM events per object or globally.
The history info is gotten from view gv$Policy_History.
The current affinity comes from view V$GCSPFMaster_Info.

The object detail view shows you the master instance of each object like here for a table:















Click on instance number list the complete history of DRM events for this object:



In addition you can also get an overview over all DRM actions of your database.

Starting with menu "Analyses/Statistics" / "RAC-related analysis&quo…

Panorama: Configure https access to Docker container with Nginx

The Oracle performance analysis app Panorama as Docker container does not natively support https-connections.

But securing http-access to Panorama can easily be reached by running the Panorama-container behind a reverse proxy.

This example shows using Nginx as reverse proxy and docker-compose to place Panorama behind the reverse proxy.

I've used already existing own SSL-certificates for the target host.
Obviously you may want to use certificates generated by Lets Encrypt but this often doesn't work in company environments behind firewalls.

Place the pem- and key-file of your certificate combined with the two files "docker-compose.yml" and "nginx.conf" in one directory and run "docker-compose up" to start Panorama and Nginx.

Example for nginx.conf (replace certificate file names):

# Nginx config for Panorama # Peter Ramm, 22.03.2019 events { } http { # Redirect all http traffic on port 80 to https 443 server { listen 80 default_server; …

Panorama: Oracle-DB's Performance-Hub report now integrated

Image
Starting with release 12.1 the Enterprise Manager Express of Oracle-DB contains an aggregated view of performance metrics called "Performance Hub".

Panorama, the free tool for performance analysis on Oracle-DB now also includes this Performance-Hub report.

You may call the Performance Hub report by menu "Analysis / Statistics" / "Genuine Oracle AWR-reports" / "Performance Hub".
After setting start and end date and optional RAC-instance the Performance Hub report opens in a discrete browser tab (if you allow Adobe Flash to execute).

The DB-user used for connecting Panorama to the database needs the role EM_EXPRESS_BASIC or DBA to be able to call the Performance Hub report.




Panorama: Long-term trend analysis of Oracle database workload

Image
Why Oracle DB allows retrospective evaluation of workload of active sessions by Active Session History (ASH) if you have Enterprise Edition and Diagnostics Pack.
The default retention time for ASH is 7 days back from now, usual settings for production are around 30 days.

But sometimes you need to analyze the evolution of database load over longer periods like over many years (e.g. as base for hardware planning and capital budgeting).
There are less builtin alternatives yet:
Increase the ASH retention time up to years leads to a huge amount of data, cumbersome or impossible to handleUsing the AWR-warehouse function in an EM Cloud Control installation may fit your needs, but especially for single or less database instances this causes an inappropriate effort. WhatPanorama-Sampler allows you to extract some information from ASH and store it compressed as summary other periods between one hour and one day. The Panorama-GUI contains table-views and diagrams for evaluation of stored workloa…