Peter Ramm's Oracle performance analysis stuff

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

If you find this message “ ORA-1652: unable to extend temp segment ” in alert.log there are two alternatives for reason: The session causing the ORA-1652 has allocated large memory in temporary tablespace itself and got ORA-1652 at the end of available space  Other sessions did successfully allocate large amount of memory in temp tablespace but this session with only small demand got ORA-1652  But how to identify the session that really claimed large amounts in temporary tablespace and therefore is responsible for the ORA-1652? With Panorama you can clarify this as follows: Go to menu “Schema / Storage” / “Temp usage” / “Historic”. Choose the concerning period and appropriate time unit.  Sort table by column “Max. TEMP allocated”  Show column “Max. TEMP allocated” in diagram via context menu click in column and select “Show column in diagram”  Now let’s check the temp usage peak at 00:10. One of several ways to identify the sessions caus...

You recognize a long running query caused by poor execution plan. You also know that until yesterday this query worked fine but now execution plan has suddenly changed due to ???. Filipe Martins named a solution here by transferring a good execution plan from AWR history into a SQL plan baseline. This solution is available in Panorama now. It allows you to quickly generate a SQL snippet for creation of SQL plan baseline by simple button click. Steps to fix this issue: Identify the problematic query, open SQL details page in Panorama for this statement. For example via menu “SGA/PGA details” / “SQL Area” / “Current” Hit button “Complete time line of SQL” at the bottom of the SQL details page Identify a day (or hour/minute) in history in the list with exactly one good execution plan. Column “Elapsed/Execution” may help you to rate the quality. Column “Plan hash value” allows you to differentiate different plans. Click the link in column “Start time” to open SQL de...

In menu ‘Spec. additions’ / ‘Dragnet investigation’ now you have the opportunity to add your own personal SQL-statements to the predefined list of dragnet SQLs. This SQLs are stored at Panorama’s server instance and are available for your personal browser instance only. Choose ‘Add personal selection’ from menu ‘≡’ and customize the JSON template for your purpose. You will get a new menu ‘Personal extensions’ in your list of SQLs then. If you want to persist this SQLs and provide them to all users of your Panorama instance, you can store them as JSON-array in a file ‘predefined_dragnet_selections.json’ stored at PANORAMA_VAR_HOME. The content of file ‘predefined_dragnet_selections.json’ is shown in list of dragnet SQLs if it exists in   PANORAMA_VAR_HOME directory. Click here to show all opportunities for your personal or global extensions to dragnet list.

Setting module and action info via  DBMS_Application_Info.Set_Module  gives valuable context info in V$Session, Active Session History etc. For jobs executing SQL via SQL*Plus mostly needed information in module/action is the name of the process executing sqlplus. To prevent jobs from directly executing  DBMS_Application_Info.Set_Module  and to ensure that every sqlplus-process is setting this context info, you can use the startup-script feature via login.sql. Ensure that environment variable SQLPATH will target to the directory that contains login.sql, so all executions of sqlplus will  immediately set their context info in v$Session. Example for login.sql: -- Description : Default login sequence for SQL*Plus, executed at start time of every SQLPlus-process if file is in search path -- Unix environment variable SQLPATH must contain directory of file "login.sql" SET TERMOUT OFF; -- Determine unique session identifier for unique ...

In current version of Oracle database there’s no way to breakdown historic statistic values to sessions if session has already terminated. Some times this information is strongly needed to identify bottlenecks. Actual situation was: – massive number of transactions which caused massive small write I/O reaching limits of physical disks – to identify root cause identification fo processes/sessions was needed – consideration can only be done in history with time gap of some hours, problematic sessions have been terminated for long time Solution: Create sampling process with minimal resource impact to CPU and I/O with result storage in database table. Preparation Create two tables, one for result storage and one for abortion of sampling process: CREATE TABLE SessMon_Semaphore (Name VARCHAR2(30)); COMMENT ON TABLE SessMon_Semaphore IS 'Control abortion of sampling process'; COMMENT ON COLUMN SessMon_Semaphore.Name IS 'Record identifier to store record lock on it...

Create SQL trace file for systems with connection pool If using connection pools the default SQL_TRACE=TRUE does not help because the DB-connection should only be traced if used for a special considered application. For identification of application you can use the context info set by DBMS_Application_Info.Set_Module. Trace the run of an single application For a special module info you can specify creation of trace file already before execution of this application. Setting trace config has to be done as SYSDBA. In addition you can set parameters for "waits" and "binds", default is FALSE. Examples Activate trace for application by module with recording of bind variables exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'SYS$USERS', module_name => 'ID_Application = 56', binds => TRUE); Deactivate trace for module exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'SYS$USERS', module_n...

With shown SQLs you can identify objects causing “library cache: mutex X”-waits in library cache. From oracle 11g this problem may be reduced by cloning objects in library cache via DBMS_SHARED_POOL.MARKHOT. Please keep in mind to set the exact namespace in call of DBMS_SHARED_POOL.MARKHOT, for PL/SQL packages it has to be called twice for package and body. View on Active Session History from current SGA: SELECT COUNT(*) Seconds_in_Wait, obj.NameSpace, h.Inst_ID, o.Owner, o.Type, o.Name, ROUND(AVG(Time_Waited)/1000,2) Avg_Time_waited_ms, MIN(Sample_Time) First_Sample, MAX(Sample_Time) Last_Sample FROM gv$Active_Session_History h LEFT OUTER JOIN gv$DB_Object_Cache o ON o.Hash_Value = h.P1 AND o.Inst_ID = h.Inst_ID LEFT OUTER JOIN DBA_Objects obj ON obj.Owner = o.Owner AND obj.Object_Name = o.Name AND obj.Object_Type = o.Type WHERE Event = 'library cache: mutex X' GROUP BY h.Inst_ID, o.Owner, o.Type, o.Name, obj.NameSpace HAVING COUNT(*) ...

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data with 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins p.s.: More effective than this solution may be caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS -------------------------------------------------------------------------------- -- Author : %$LastChangedBy: arosenfe $derived...

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data without 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins From 11g function result caching may be used instead of this solution p.s.: More effective than this solution is caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS ----------------------------------------------------------...

Short overview on peak I/O requests, transfer volume and CPU-usage for given time range. Based on average values within AWR-cycle. SELECT /* DB-Tools Ramm average system load */ Instance_Number, MIN(Begin_Time) Start_Range, MAX(End_Time) End_Range, MAX(Phys_Write_Total_kB_per_Sec) Max_Phys_Writes_kB_per_Sec, MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_Total_kB_per_Sec) Max_Write_per_Sec_BeginTime, MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Write_Total_kB_per_Sec) Cycle_Minutes_1, MAX(Phys_Read_Total_kB_per_Sec) Max_Phys_Read_kB_per_Sec, MAX(Begin_Time) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_Total_kB_per_Sec) Max_Read_per_Sec_BeginTime, MAX((End_Time-Begin_Time)*1440) KEEP (DENSE_RANK LAST ORDER BY Phys_Read_Total_kB_per_Sec) Cycle_Minutes_2, MAX(Phys_Write_IO_per_Sec) ...