Peter Ramm's Oracle performance analysis stuff

For performance analysis of problematic SQL statements it is crucial to have the extended statistics of the execution plan available. These extended statistics per plan line include as total and last gathered values: The number of starts of execution of this plan line The number of rows gotten by execution of this plan line The number of DB buffers scanned in consistent or current mode The number of disk reads and writes The elapsed time during execution of this plan line There are several ways to force the DB to record these extended statistics: Set the global initialization variable 'STATISTICS_LEVEL to 'ALL' to enable them for the whole DB 'ALTER SESSION SET STATISTICS_LEVEL=ALL;' to enable them for the current session The optimizer hint /*+ GATHER_PLAN_STATISTICS */ in the SQL to enable the extented only for this SQL statement Unfortunately, to activate the extended statistics for a specific SQL by adding the optimizer hint, you ...

If only following columns of an index are used as filter conditions and the first columns of the index are missing in the filter, Oracle's optimizer may use the INDEX SKIP SCAN operation. At SQL execution the DB will iterate in that case over all distinct values of the skipped column and proceed with B-tree access for the columns used as access criteria. The efficiency of a SKIP SCAN therefore depends on the number of distinct values for the skipped column(s). If the skipped column has only one distinct value, then the SKIP SCAN operation will succeed with 3-5 buffer gets similar to a regular RANGE SCAN. If the skipped column has lots of distinct values, then the B-tree access with the filter criteria will be executed as many times as the number of distinct values, resulting in thousands or millions of buffer gets for a single index access instead of 3-5. So far, this is mostly known for SKIP SCAN. But also if the SQL plan states an INDEX RANGE SCAN, this possibly problemati...

To view how the Oracle optimizer comes to his decision, there are several ways to create a trace file like ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS 'TRACE[rdbms.SQL_Optimizer.*][sql:6jksrnjx4zvuk]'; EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id, p_child_number, p_component, p_file_id); Especially the DBMS_SQLDIAG.DUMP_TRACE helps to quickly review the parse steps of a SQL that already has a valid plan in the SGA. Using the parameter p_component=>'Compiler' parses the SQL again even if it only needs a soft parse. Use this steps to view the optimizer trace also without filesystem access on DB server -- grab the SQL and reparse it with trace EXEC DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'6jksrnjx4zvuk', p_child_number=>0, p_component=>'Compiler', p_file_id=>'mine'); -- find the created trace file by the used p_file_id SELECT Inst_ID, ADR_Home, Trace_Filename, ...

If using user-defined PL/SQL functions in SQL, it is crucial for the optimizer to know if a function is deterministic or not. Deterministic means, that the result of a function call is stable for subsequent calls with the same call paramater values. You need to tell the optimizer that the result can be considered as stable by adding the keyword DETERMINISTIC to the function declaration. CREATE OR REPLACE FUNCTION MyFunc() RETURN NUMBER DETERMINISTIC ... If knowing that a function is deterministic, the SQL engine can cache the function results for repeated calls of this function. This can make a huge difference for the SQL execution effort. Consider an example: A table MyTab has 1 mio. records. A package function does some calculations returning a value that is used as filter criteria at access on MyTab. CREATE TABLE MyTab(ID NUMBER); INSERT INTO MyTab SELECT Level FROM DUAL CONNECT BY LEVEL Now let's check what happens. SET SERVEROUTPUT ON; SELECT * FROM MyTab WHERE ID = My...

For a small demo I needed a synthetic workload at an Oracle DB. We are running on Oracle Linux and want to execute a TPC benchmark at an SE2 instance. The tool of choice to do this quickly was HammerDB (https://www.hammerdb.com) which is also avaialble as Docker image. Assuming an Oracle DB is already running on port 1521 of the Docker host, these are the steps: Create a config script config.tcl: echo " # Select the Oracle database. dbset db ora # Select the TPC-C benchmark. dbset bm TPC-C # Oracle DBA user (e.g., SYSTEM) diset connection system_user SYSTEM # DBA user's password diset connection system_password oracle # Your DB connect string. Use IP of Docker host diset connection instance 10.214.3.3:1521/ORCLPDB1 # Scaling Factor (Number of Warehouses), e.g. 100 diset tpcc count_ware 100 # Virtual Users for schema build/load and run diset tpcc num_vu 8 # Benchmark schema user (will be created) diset tpcc tpcc_user TPCC # Benchmark schema password diset tpcc tpcc_pa...

One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report. This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more. The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL . Which edition or option pack license is needed Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function. As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL. The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor". Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation: The note "How To U...

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

A quite common and recurring problem by developers is the use of literals in SQLs instead of prepared statements with bind variables. This problem pattern has been haunting me for years, there was a post about that in this blog also from 2017. It is now time to sum up the ideas that have emerged in the meantime to quickly identify the most problematic occurrences on this topic. The severity of this issue depends on the variety of the used literals. The more different literals are used in the SQL, the greater the problem becomes. If the used value does not come from outside (SQL injection), it could be tolerable if using a limited number of values for a filter condition directly in the SQL resulting in some 10 or 100 variations of a SQL statement. On the other hand, it could also be helpful in these cases to use the clear values in SQLs instead of a bind variables to support the optimizer in the use of histograms for different optimal execution plans. However, if thousands or mi...

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

If you try to use parallel DML or direct load in SQL statements by placing the appropiate optimizer hints (PARALLEL, APPEND etc.), there might be several reasons why the DB ignores them. For example, if you want to execute DML in parallel you need to enable the session to do this before by executing ALTER SESSION ENABLE PARALLEL DML; . There are lots of similar pitfalls that may prevent the DB from using the expected execution path. The free performance analysis tool Panorama offers a way to scan your entire database for SQLs where parallel DML or direct load should have been used but in reality is not. Use the shown selection from menu "Dragnet investigation" (point 2.2.12) to execute this check and find SQLs that are not executed as expected. The result is sorted by the time this SQLs took to execute. The link in column "sql id" allows you to dig deeper in the details of the found SQL statement. Precondition is licensing of Oracle Diagnostics Pack or the u...