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

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