Peter Ramm's Oracle performance analysis stuff

For housekeeping of Unified Audit Trail in a recent project there were some objectives: A hard rule that audit records must be kept in all cases for at least x days A hard rule that the stored audit records should not exceed a limited storage size, except the the above age limit requires this A soft objective thet audit records should be stored as long as possible if the storage size limit is not reached The package DBMS_AUDIT_MGMT only provides the method CLEAN_AUDIT_TRAIL with the ability to cut the records to purge at a timestamp limit. This would require to estimate a maximum age of held audit records that ensures also in worst cases that the storage size limit will not be exceeded. In result, outside the worst case of audit volume you will purge the audit records far too early. You could have stored them much longer because there's enough space below the storage limit. The following SQL script accepts all named objectives by ensuring: Audit records younger ...

Sometimes it is useful to know about the network latency of client connections to database. Normally this would be done with the established network tools tnsping, ping, traceroute, tcptraceroute etc. from client or DB server side. But what if there is only SQL access to the DB server and no access to the client? This post shows how the network latency can be estimated using the recordings from ASH if the application uses a certain access pattern. The network latency between client and database server can be estimated by the number of number of SQL executions of a single session between two ASH snapshots. If an application executes the same very short running SQL against the DB over and over again in a loop, then: it is a bad architecture approach for the application because the network latency will cause a significant overhead for application performance however, this behavior gives a possibility for a weak estimation of the network latency between client and database ser...

After migrating DB instances to Exadata cloud service in Oracle's OCI cloud we noticed that selects from the view UNIFIED_AUDIT_TRAIL did not succeed in accepted time, even if they were quite simple. Looking at the execution plan shows that nearly the whole excution time is spent on full table scan on table X$UNIFIED_AUDIT_TRAIL. A click in the column "Waits" in that line shows the wait events for that execution plan line that have been recorded by ASH. Mostly X$-tables will present an SQL interface for Oracle's internal memory structures. In this case the wait event 'Disk file operations I/O' let the X$UNIFIED_AUDIT_TRAIL table look like a wrapper for file structures on disk. Trying to explain what happened Some blog posts and a note in the documentation gave the needed background info. Note:1. When the database is writeable, audit records are written to the unified audit trail. If the database is not writable (typically occurs when the datab...

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

Since release 18c there's an undocumented feature Parallel Shared Hash Join which introduces sharing memory between parallel query slaves. The required memory for these shared hash tables is allocated in a new memory region known as the Managed Global Area (MGA). See also doc. ID 2638904.1. This feature is particularly beneficial for costly HASH JOIN BUFFERED operations that spill large amounts of data into the temporary tablespace. These operations may benefit from transformation into HASH JOIN SHARED operations. By sharing hash tables between parallel query (PQ) processes, instead of each PQ server maintaining its own, runtime can be significantly reduced. This reduces overall memory requirements, enabling more data to be processed before spilling to disk in the temporary tablespace. There are several ways to activate the Parallel Shared Hash Join: set '_px_shared_hash_join'=true; at system or session level define the PQ distribution strategy for a particular...

Recently a problem occured using function based indexes: The optimizer switched to full table scan on a table with 3.2 billion rows although the used function expression is covered by a function based index. The expression often evaluates to NULL ond so this index has only 4.9 million rows ( < 1% of the table rows ) and a low selectivity of approx. 4 rows per key. Let's consider the simple case. The table INVOICE has two interesting columns: - CUSTOMER_ID 38 mio distinct values, not null, avg. 84 rows/key - OPEN_AMOUNT not null, 4,8 mio. records > 0 A function based index was created to quickly identify the small portion of rows of a certain customer where the OPEN_AMOUNT is > 0 CREATE INDEX IX_OPENAMOUNT ON INVOICE(CASE WHEN Open_Amount > 0 THEN Customer_ID END); This SQL is used to select the rows of a certain customer where the OPEN_AMOUNT is > 0 SELECT ID FROM Invoice WHERE CASE WHEN Open_Amount > 0 THEN Customer_ID END = :B1 ; The executi...

As of Oracle DB release 19.12 it is possible to use the new and the previous password for user logon simultaneously for a limited period. For details see New features guide . This is a great and long-awaited feature that lowers the barriers to changing passwords with less operational risk. But there's an obstacle for practical use: You must be able to monitor the users who are within this rollover period and are still using the old password. The only way of monitoring the use of old passwords known to me so far is to: Use Unified Auditing Create a policy for the LOGON action Check for the keyword "VERIFIER=12C-OLD" in the column AUTHENTICATION_TYPE of the logon records in unified audit trail. The following SQL provides an overview over all the users who are currently within a password rollover period, combined with info about old password usage: SELECT u.Account_Status, u.Password_Change_Date, u.Profile, u.UserName, u.Last_Login, a.DBUserName, a....

The release 19.19 of the Oracle DB got the long awaited populating of Access_Predicates and Filter_Predicates in DBA_Hist_SQL_Plan (as backport from 21c). So far so good, after decades of complaints. But as the execution plans already stored in the AWR before 19.19 are without a time reference, Access_Predicates and Filter_Predicates for SQLs are not filled, as long as the plans from before 19.19 still exist. These plans are independent of the AWR retention time and therefore remain in the old state without Access_Predicates and Filter_Predicates forever as long as the SQLs are still contained in upcoming AWR snapshots. A solution for that behaviour is, to manually remove the old plans to trigger storage of current plans from SGA again. If executed once as SYSDBA, this script deletes all the execution plans saved in the AWR for which the Access_Predicates and Filter_Predicates columns are not filled. These plans will be saved again from SGA with the next AWR snapshots, but then ...

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

Why Oracle DB allows retrospective evaluation of workload of active sessions by Active Session History (ASH) if you have Enterprise Edition and Diagnostics Pack. The default retention time for ASH is 7 days back from now, usual settings for production are around 30 days. But sometimes you need to analyze the evolution of database load over longer periods like over many years (e.g. as base for hardware planning and capital budgeting). There are less builtin alternatives yet: Increase the ASH retention time up to years leads to a huge amount of data, cumbersome or impossible to handle Using the AWR-warehouse function in an EM Cloud Control installation may fit your needs, but especially for single or less database instances this causes an inappropriate effort. What Panorama-Sampler allows you to extract some information from ASH and store it compressed as summary other periods between one hour and one day. The Panorama-GUI contains table-views and diagrams for evaluatio...