Peter Ramm's Oracle performance analysis stuff

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 ...

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 ...

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 c...

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...

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'...

Update 2023-05:  Checked the above issues against Rel. 19.18 It works much better now than before in Rel. 12.x.  Not completely without the risk of getting migrated rows and not deterministic at all. But with much smaller amount of situations resulting in increasing storage footprint and migrated rows.

Starting with release 10.1 Oracle introduced automatic shared memory management. Setting only SGA_TARGET=x without defining limits for the SGA-components (buffer cache, shared pool, large pool, java pool) allows your database to redistribute SGA memory between components due to current needs. This will work properly in many cases but sometimes there are some pitfalls: A very common problem on Oracle databases are applications without usage of bind variables in their SQL statements. Not using bind variables leads to excessive SGA memory consumption in shared pool and parse effort if you have a large number of SQLs executed with different values in filter conditions stored as literals. For example: SELECT a, b FROM Item WHERE ItemNo = 453612; SELECT a, b FROM Item WHERE ItemNo = 876886; etc. instead of SELECT a, b FROM Item WHERE ItemNo = :ItemNo; In such a situation the DB may resize the SGA. The size of the shared pool will grow thus reducing the size of the buffer cache...

SQL-Monitoring records individual executions of an SQL statement in detail if they fulfill one of the following prerequisites: Execution with parallel query CPU- or I/O-activity for more than 5 seconds Optimizer-hint `MONITOR` in SQL statement SQL-Monitoring reports are stored in table v$SQL_Monitor during SQL execution and for a very short period after execution. The precondition for using the monitoring reports is licensing of the Tuning Pack for the Enterprise Edition. Starting with Oracle 12.1, the short-living detailed monitoring reports from v$SQL_Monitor are also available for a longer time within the AWR retention period via DBA_Hist_Reports and DBA_Hist_Reports_Details. This report can be exported as HTML-page which uses Adobe-Flash to render the Database Activity Report for this SQL execution similar to Enterprise manager. To generate the HTML-report from v$SQL_Monitor call: SELECT DBMS_SQLTUNE.report_sql_monitor( ...

Long time ago Oracle introduced SQL patches as a way to influence execution plan of SQL with optimizer hints without changing the original SQL statement. This allows fixing execution plan issues without any change at the application executing this SQL. SQL patches don't require additional license in contrast to SQL plan baselines and SQL profiles and can also be used with Standard Edition. SQL patches are normally used in workflow of SQL Repair Advisor but can also be created manually. There are two variants to create a SQL patch for a SQL: By SQL-ID and by SQL text. (See also this posts: 1   2  ) I personally prefer SQL patch creation using SQL text because SQL-ID requires existence of SQL-Statement in SGA at SQL patch creation time. For Oracle 11.1 .. 12.1 create with package-method sys.DBMS_SQLDiag_Internal.i_create_patch BEGIN sys.DBMS_SQLDiag_Internal.i_create_patch( sql_text => 'SELECT SYSDATE FROM DUAL', hint_text => 'PARALLEL(8)...

Panorama is my free performance analysis tool for Oracle-DB. From now it offers it's own database workload recording 'Panorama-Sampler' as alternative to use of Oracle's origin AWR and ASH data. This enables you to use Panorama's functions for evaluation of historic workload info: with any database edition including Standard Edition without licensing of Diagnostics Pack for Enterprise Edition In addition to Oracle's AWR Panorama-Sampler may also record historic information for: Size evolution of tablespace objects Usage of DB-cache by objects Detailed info for blocking lock scenarios You may find more about Panorama and it's sampling feature including download link at: http://rammpeter.github.io/panorama_sampler.html http://rammpeter.github.io/panorama.html

A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...

A common problem with indexes is: creating an index is quite easy in consideration of the possible impact deleting an index is much more difficult because: Who has the heart to decide this index is really no longer needed?  This results in: Nearly every database system stores and maintains indexes that are not really needed. In worst cases this not needed indexes are more than half of the systems total storage Securely indentifying and deleting this unnecessary indexes may be a strong lever to improve performance and reduce resource consumption without any changes on application. How to identify unused indexes: 1. Use Oracle's own index usage monitoring  You can enable index usage monitoring by issueing  ALTER INDEX <Index_Name> MONITORING USAGE; Index usage is expressed as YES/NO-state that flips to YES if index is used by any executed SQL-statement. Combined with start monitoring timestamp you can identify indexes that are not used by SQL si...

SQL Translation Framework is one of the more hidden features of database release 12.1. It allows to completely replace SQL text with replacement text before processing this SQL. Primary aim for SQL Translation Framework is migrating client applications from other SQL dialects, but it is also a sharp weapon for performance troubleshooting. Compared with SQL plan baselines or SQL profiles it is not restricted to execution plan or optimizer hints, instead allows to replace the complete SQL text. This way this framework allows to fix any possible issues that may be solved by changes on SQL text without any change on the application executing the SQL.  You can rewrite the whole SQL-statement, e.g. add missing JOIN- or WHERE-conditions, extract parts into WITH-clause etc. Limitations for SQL text replacements are only: the result structure (number, order and types of result columns) must remain stable the bind variables must also remain stable regarding number, alias, order ...