Peter Ramm's Oracle performance analysis stuff

One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report. This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more. The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL . Which edition or option pack license is needed Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function. As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL. The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor". Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation: The note "How To U...

The views gv$SegStat rsp. gv$Segment_Statistics provides several cumulative statistics per DB segment. This information is populated at AWR snapshots, visible via DBA_Hist_Seg_Stat. But sometimes you don't want to wait for the next AWR snapshot to get results or the resolution of the AWR snapshot period is too coarse. In this case the values from gv$Segment_Statistics are quite helpful if you could get the condensed results for the last x seconds only. Unfortunately, gv$Segment_Statistics contains only values cumulated since the last DB restart resp. since other events (possibly since the last load of blocks of a segment into the buffer cache). The following SQL collects the statistics value changes within the last x seconds by sampling twice and providing the differences. It shows all the segments and their statistics where values have changed in the considered period. The entire function is encapsulated within a single SELECT SQL, so that nothing needs to be installe...

Some times a SQL execution includes all partitions of a partitioned table or index into processing, although there are filter conditions used that contain also the partition key of that partitoned table or index. There are several possible reasons why the optimizer does not recognize that access could be reduced to one or less partitions (a.k.a. partition pruning): The partition key can be obfuscated behind a conversion function The partition key can be compared to a function result that is known only at execution time Being able to use partition pruning can make a huge difference for execution time of the SQL. Imagine you only need to scan one partition of a table instead of several thousands. Often only a small change in the SQL statement can drive the optimizer to use partition pruning. A small example where all partitions are scanned due to comparison of the partition key with a function result: Get the sum of order value for a table interval partitioned by day. SELE...

What does the database really do with your optimizer hints in SQL statements? Starting with Oracle 19c the column 'OTHER_XML' of V$SQL_Plan rsp. DBA_Hist_SQL_Plan or Plan_Table was extended by a section 'hint_usage'. This gives information about the state of optimizer hint usage correlated with the line in the execution plan. Example for section 'hint_usage' of OTHER_XML Lets take this simple but useless SQL as example: SELECT /*+ FULL(c) QB_NAME(Outer) NONSENS(c) */ * FROM cust.Customer c WHERE ID_Company IN ( SELECT /*+ USE_NL(cp) QB_NAME(Inner) */ 12 FROM sysp.Company cp WHERE RowNum < 5 ) AND RowNum < 20; The section 'hint_usage' in PLAN_TABLE.OTHER_XML looks like for this example: <hint_usage> <q> <n><![CDATA[INNER]]></n> <h o="EM"> <x><![CDATA[QB_NAME(Inner)]]></x> </h> ...

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

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

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

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