Peter Ramm's Oracle performance analysis stuff

Problem: A simple LOGON trigger that works on several DBs does not work as expected in one environment. The trigger code is: CREATE OR REPLACE TRIGGER Test_Logon AFTER LOGON ON DATABASE DECLARE Dummy NUMBER; BEGIN SELECT COUNT(*) INTO DUmmy FROM User_Tables WHERE Table_Name = 'DB_TRIGGER_TEST'; IF Dummy = 0 THEN -- Table created for owner of DB trigger EXECUTE IMMEDIATE 'CREATE TABLE DB_TRIGGER_TEST (TS DATE, Text VARCHAR2(1000))'; END IF; END; / The creation command succeeded without errors and the trigger exists and is enabled. At next user logon the expectation is that either an exception occurs or the table DB_TRIGGER_TEST is created in the schema of the trigger owner. But nothing happened at user logon, logon succeeds with no exception and no table DB_TRIGGER_TEST exists within the PDB. Missing execution of trigger is independent from trigger creator. Tested with SYS and a privileged user. Trigger creation was also tried with the DB-Vault ...

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