Peter Ramm's Oracle performance analysis stuff

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

Using literals instead of bind variables in SQLs may have several drawbacks: Performance slows down because of: worse cache hit ratio higher parse effort Growing SQL-area in shared pool pushes other major memory objects outside, especially DB-cache Each executed SQL has to be hard parsed separately Risk of SQL-injection may exist (depending from source of literal) Methods to find heavy executed SQLs with missing bind variables 1. Scan active session history for different SQL-ID but same execution plan WITH Ret AS (SELECT :Consider_Days_Back Days FROM DUAL) SELECT x.SQL_Plan_Hash_Value, x.Different_SQL_IDs, x.Last_Used_SQL_ID, u.UserName, x.First_Occurrence, x.Last_Occurrence, x.Elapsed_Secs FROM ( SELECT h.SQL_Plan_Hash_Value, COUNT(DISTINCT h.SQL_ID) Different_SQL_IDs, MAX(h.SQL_ID) KEEP (DENSE_RANK LAST ORDER BY h.Sample_Time) Last_Used_SQL_ID, User_ID, MIN(h.Sample_Time) First_Occurrence, MAX(h....

Unfortunately the number of free bytes in your tablespaces does not really ensure that you are still able to allocate new extents in tablespace. Except you have choosen UNIFORM EXTENT SIZE as allocation type, than your free space chunks may not be smaller than your uniform extent size and you can skip here. To allocate a new extent on one of your tablespace's objects you must ensure that there is an existing contiguous chunk of free space that your extent may totally fit in. With Panorama  you are enabled to explore with one click the fragmentation of your tablespace and remaining space for different extent sizes. There may be a remaining question: With what size will the next extent be created for my table or index to estimate wether it will fit in free space? You can recognize this by exploration of the existing extents of your object and the assumption that the next extent will be created with: the size of the largest existing extent for SYSTEM allocation t...

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...

As the name implies one would expect that Active Session History records all information to reconstruct the main activities of sessions that are active for a longer period. Is it really true? I thought so before, but it isn't. Consider the ASH-chart of this session running for approx. one hour as one unique PL/SQL package-method call. What you would expect is that over this time the session is always active doing PL/SQL- or SQL-execution work. That means this session is permanently active and wait states should be recorded in ASH. But ASH-report says that this session is mostly inactive consuming time but doing nothing on database. The reason for this confusion is: Active Session History does not record wait states for wait class "idle". In this special case the idle waits are caused by one frequently executed SQL-statement accidentially forced to run in parallel query mode by a PARALLEL-hint, but each SQL-execution took only some milliseconds. If you ...

Creating and destroying database sessions in loop may lead to significant database load. This blog post contains more about quantifying this load. Applications should use connection pooling at client or database side to prevent excessive session creation. But how can I identify the client processes causing this sessions? Scan DBA_Audit_Trail to identify context info If auditing logon/logoff-operations is activated for your database, scan DBA_Audit_Trail. You can do this by 2 was: Alternative 1: Use my tool "Panorama" to evaluate audit trail Panorama is available here. Go to menu "DBA general" / "Audit Trail": Select start and end time for selection Filter column "Action" for "LOGON" Set "Grouping" to "Minute" Execute selection via button "Show audit trail" Add columns of your choice to diagram by context menu (right mouse button) This way you can identify the machine, DB-user ...

Running an Oracle database with only 3 redo log groups often leads to suddenly frozen database, there all commit operations are suspended for some seconds or even some minutes. This freeze behavior may cause several errors in production environments: Services are not available, timeout exceptions are raised, process throughput decreases considerably etc. . Unfortunately only 3 redo log groups is the default for a database created by DBCA and therefore you may find many DB instances in production with only 3 redo log groups. But why should it be a problem to have too less redo log groups? For example we have a database with 3 redo log groups and as advisable for production databases, archiver mode is activated. Consider the redo logs as ring buffer where a fix number of redo log file groups is reused by the log writer process after the current log is completely written and a log switch is raised. The precondition for successful log switch is that the next log file group to s...

Panorama can be run as Docker image from now. It is available at dockerhub ( https://hub.docker.com/r/rammpeter/panorama ) and can be fetched by docker pull rammpeter/panorama You may run Panorama immediately by executing docker run --name panorama -p8080:8080 -d rammpeter/panorama More information about Panorama and how to customize your Docker container you may find at: http://rammpeter.github.io/panorama.html

Panorama now supports analysis of pluggable databases (PDB) too. Visibility of informations in CDB and PDB There are different informations visible wether you are selecting from DBA_xx-views or CDB_xx-views and your are connected to CDB or PDB Role / user connected Container-DB (CDB / root) Pluggable database (PDB) Kind of views CDB_xx DBA_xx CDB_xx DBA_xx system all PDBs including root all PDBs including root nothing your current PDB user (SELECT ANY DICTIONARY) all PDBs including root root CDB nothing your current PDB Posted on  "Analyze pluggable database with Panorama"

Caching SQL result sets or PL/SQL function results is very easy by done simply adding hint or flag “RESULT_CACHE”. Without monitoring the usage of result cache this may lead to overbooking the size of result cache. In excessive manner flooding the result cache with new entries may result in significant latch waits. This latch waits will influence other concurrent sessions and occur also for sessions using only few result cache entries. You should pay attention to size result cache according to your needs and avoid massive crowding out. So using only 80% to 90% of result cache is a good rule of thumb to avoid the risk of latch waits on result cache. Example problem: Consider the following PL/SQL-Function that computes something possibly time dependent: FUNCTION getCachedValue p_Parameter1 IN NUMBER, p_Parameter2 IN NUMBER DEFAULT NULL, p_Date IN DATE DEFAULT SYSDATE ) RETURN VARCHAR2 PARALLEL_ENABLE RESULT_CACHE; With usage in PL/SQL code but ignoring the time depen...

If you use foreign keys there’s one very common dogma: You always need to index all the column(s) for which you define a foreign key constraint! I definitely do not agree with this dogma and want to explain why. There are two reasons for indexing columns with foreign key constraints: Avoiding full table scans on referencing table during deletes on referenced tables  Avoiding blocking lock problems e.g. if you are doing DML on both referencing and referenced table  You may find a detailed description of the locking behaviour of foreign keys at chapter "Locks and Foreign Keys" here in the Oracle documentation . Considerations on full table scan due to foreign key constraint not protected by index If you do not index referencing column(s) than delete on referenced table leads to a full table scan on referencing table for every deleted row  Therefore you should index referencing column(s) especially in OLTP-environments if your referenced table has more than a s...

Sometimes in Panorama you have drilled down in analysis from step to step, cumulated several filter conditions and found what you are looking for. This finding you want to share with colleagues or store for later use. Panorama allows you at several pages to copy your current request parameter to clipboard and use ist later to reconstruct your current page. Saving request parameters: Choose “Copy request parameters to clipboard” from small menu at left corner of headline (if it is available for this function). You can use parameters from clipboard now to store or mail or … Restore a page with given parameters: Call menu “Spec. additions / Execute with given parameters” and paste your stored parameters in text area. Click execute to restore your page with this parameters.

An often underrated feature of Oracle databases since version 9i is  key compression for indexes . It allows reduction of index footprint until 1/2 of uncompressed size depending from key size and number of rows per key. Compressing an index is quite simple: ALTER INDEX MyIndex REBUILD COMPRESS; to compress all keys of an index or ALTER INDEX MyIndex REBUILD COMPRESS 2 to compress only the first two columns of an multicolumn index. But how to determine which indexes of an existing database system are worth compressing? You can generate weighted recommendation lists for index compression by executing the following SQL statements at your database with an user who has the grant SELECT ANY DICTIONARY. Version 1: Rate indexes by selectivity SELECT * FROM ( SELECT ROUND(i.Num_Rows/i.Distinct_Keys) Rows_Per_Key, i.Num_Rows, i.Owner, i.Index_Name, i.Index_Type, i.Table_Owner, i.Table_Name, t.IOT_Type, ( SELECT ROUND(SUM(bytes)...

This article describes how to analyze blocking locking lock constellations just now in database and also back in history. Especially this includes the identification of database sessions that are initially causing hierarchic blocking lock dependencies. Analyze current blocking lock situation with Panorama Choose menu “DBA general” / “DB locks” / “Current”: Hit button “Blocking DML-Locks” You see a constellation of four sessions blocking each other wile accessing table EMPLOYEE. For each blocking lock you see the waiting session, the blocking session and also the root blocking session which causes the blocking hierarchy. If the blocking session of the lock is also the root cause for the blocking hierarchy than this blocking session is marked orange. That means that you have to clarify this orange sessions problem (probably by killing this session) to release the whole hierarchy of blocked sessions. Via link you can further view detailed info about: blocking and ...

How to identify changing execution plans in Panorama Changing or alternating executions plans often contains the risk of unpredictable runtime for SQL-statements. There are several ways in Panorama to find SQL statements with different execution plans: 1. Lookup into current SGA: If you list SQLs from SGA (Menu „SGA/PGA details“ / „SQL-Area“ / „Current (SQL-ID)“), there’s the fifth column „P.“ in the table which shows the number of different plans in SGA for a SQL-statement (orange background if more than one plan occurred). 2. Lookup into SQL history: If you list historic SQLs from AWR history (Menu „SGA/PGA details“ / „SQL-Area“ / „historic“), there’s also a column „P.“ in the table which shows the number of different plans within the choosen period for a SQL-statement. 3. SQL details page for period in history At the SQL details page you find the number of different execution plans in the considered period at the right upper corner. 4. Complete time line of SQ...