Peter Ramm's Oracle performance analysis stuff

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

This post describes how Panorama allows usage without violating Oracle's license conditions if you don't have Diagnostics Pack and Tuning Pack license. You can run Panorama with access on original AWR/ASH (which requires EE and Diagnostics Pack) or with access on Panorama's own sampled history data which doesn't require EE and Diagnostics Pack. Before accessing AWR/ASH-data Panorama asks you which mode you want to use. Several functions of Panorama require one of the following: Enterprise Edition and Diagnostics Pack licensed or Use Panorama's own data sampled by Panorama-Sampler instead Panorama's function "SQL monitor" is available only if you've licensed Oracle's "Tuning Pack". Accessing restricted dictionary data (like AWR) with Panorama has preconditions to ensure compliance with Oracle's license conditions: After connecting to a database with Panorama you have to acknowledge at first one of the four options:...

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

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