Peter Ramm's Oracle performance analysis stuff

 If you want avoid blocking locks while using referential integrity constraints in an Oracle-DB, it is suggested to protect a foreign key constraint by an index. Not always it is useful and necessary to protect a foreign key constraint with an index. But this question was considered separately in this blog post. The question today is: What column structure must an index have to be accepted by the Oracle DB as protection for a multi-column foreign key constraint? To answer this question, several scenarios are tested. Test setup: CREATE TABLE Dim(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Dim ADD CONSTRAINT Dim_PK PRIMARY KEY (ID1, ID2, ID3); INSERT INTO Dim VALUES(1,1,1, 'First'); INSERT INTO Dim VALUES(2,2,2, 'Second'); CREATE TABLE Fact(ID1 NUMBER, ID2 NUMBER, ID3 NUMBER, Name VARCHAR2(200)); ALTER TABLE Fact ADD CONSTRAINT Fact_Dim_FK FOREIGN KEY (ID1, ID2, ID3) REFERENCES Dim(ID1, ID2, ID3); Scenario 1: No index second session is blo

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 handle Using 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. What Panorama-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 evaluatio

In addition to various functions, the free performance analysis tool  Panorama now also offers a browser dashboard for real-time monitoring of Oracle database performance. This shows key indicators for active sessions by wait class as well as top sessions and top SQLs in considered time period. Several values within this dashboard are presented as links which allow to dig in deeper using Panorama's  standard analysis workflows.  Choose menu entry "DBA general"/"Dashboard", the time to cover until now and a refresh rate: As long as gv$Active_Session_History has retrospective data it is shown in the graph for the chosen time period grouped by wait class. At next refresh time only the delta since last refresh is transferred from gv$Active_Session_History to your browser instance. Investigate a particular time period within the presented data The top sessions and SQLs are related to the current time frame you can select within the chart. If you select a particular

A problem using interval partitions may be that the number of partitions may not exceed 1.048.575 (1024*1024-1).  If using interval partitions not only the physical partitions count. Instead, the number of possible partitions between the required first range partiton and the last interval partition counts for the limit (in consideration of the used interval).  If you exceed the limit, ORA-14300 (partitioning key maps to a partition outside maximum permitted number of partitions) is raised .  So if you have a rolling window with interval partitions, where new interval partitions at the upper bound are created and older partitions are dropped if no longer needed, you may run into ORA-14300 if your initially created range partition becomes to far away from the upper bound of interval partitions.  A possible solution should be to increase the high value of the initial range partition to lower the distance to the highest interval partition.  But how to do that?  The first idea is to remove

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it a

 A generally assessed poor technique is to create an own dedicated database session for each atomic DB activity. You may sometimes encounter such strategies like: processing a large amount of items in a loop, each processing step in the loop creates a DB session, executes a small set of SQL statements and terminates the session a polling process checks a SQL result one time a second, each in a new DB session But what costs are generated by frequently connecting and disconnecting DB session? The internal recording of database activity (AWR/ASH) has no answer because establishing the DB connection is not a SQL activity. A simple comparison of system load gives a fuzzy hint to the price of connection creation. Example: An idle database instance on a single host with 4 older CPU cores (Intel Xeon E312xx, 2,6 GHz) an external (not on DB host) SQLPlus client which executes a single "SELECT SYSTIMESTMP FROM DUAL" per DB session Delay between the SQLPlus calls is calculated so that o

The recorded session activity in Oracle databases (Active Session History / ASH) enables the retrospective analysis of locking scenarios between database sessions. This post shows how the free performance analysis tool  Panorama  supports this investigation. Precondition for using ASH is the Enterprise Edition of Oracle DB and the licensing of the Diagnostics Pack. If you don't have licensed Diagnostics Pack or you are running Standard or Express Edition, then you can use the similar function of Panorama-Sampler to record the session activity. Panorama evaluates both sources (AWR/ASH or Panorama-Sampler) transparently in the same way. In general there are several ways in Panorama for retrospective evaluation of previous blocking lock situations: Top down analysis of lock dependency tree to identify a root blocking session Top down analysis of wait event dependencies to get an overview based on blocking and blocked events (new in Panorama) Bottom up analysis to find the root cause

Even if you think to have enough free space in tablespace your operation may end up in: ORA-01653 unable to extend table <xy> by <z> in tablespace <TS> The reason for this often unexpected behavior is: The table needs a contiguous free space in tablespace in next extent size to allocate a new extent. If free space in total is enough available but only in smaller slices than the required size of the extent to allocate than this will happen. Several years / Oracle versions ago fragmentation issues have been addressed by uniform extent size for tablespaces. Today this issue ist mostly addressed by locally managed tablespaces with an automatically defined limited number of used extent sizes. Locally managed tablespaces reduce the risk of fragmentation issues, but not completely. So especially if less space remains available in tablespace fragmentation sometimes becomes important. One way to check a database for the risk of running into ORA-01653 is to look fo

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 tables Optimizing accesses from User-SQL - Reduction of access to the result-relevant records when reading table data Guarantee of uniqueness - Declaration of unique indexes or use of the index for primary key or unique constraints Protection of Foreign Key Constraints - Preventing full table scans w

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 */

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 cloud the 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)

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 line Panorama now supports viewing server side trace files via menu "DBA general" / "Server Files" / "Server Trace Files". This way people without access rights on database server file systems (like software developer) are enabled to access the trace files they produce by their own. This view shows all existing trace files for chosen period and optional including or excluding patterns for trace file name. This works for server trace files as well as for user trace files created by: ALTER SESSION SET SQL_TRACE=TRUE; or ALTER SESSION SET EVENTS '10046 trace name context forever, level 4|8|12'

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 th