Peter Ramm's Oracle performance analysis stuff

Setting module and action info via  DBMS_Application_Info.Set_Module  gives valuable context info in V$Session, Active Session History etc. For jobs executing SQL via SQL*Plus mostly needed information in module/action is the name of the process executing sqlplus. To prevent jobs from directly executing  DBMS_Application_Info.Set_Module  and to ensure that every sqlplus-process is setting this context info, you can use the startup-script feature via login.sql. Ensure that environment variable SQLPATH will target to the directory that contains login.sql, so all executions of sqlplus will  immediately set their context info in v$Session. Example for login.sql: -- Description : Default login sequence for SQL*Plus, executed at start time of every SQLPlus-process if file is in search path -- Unix environment variable SQLPATH must contain directory of file "login.sql" SET TERMOUT OFF; -- Determine unique session identifier for unique ...

In current version of Oracle database there’s no way to breakdown historic statistic values to sessions if session has already terminated. Some times this information is strongly needed to identify bottlenecks. Actual situation was: – massive number of transactions which caused massive small write I/O reaching limits of physical disks – to identify root cause identification fo processes/sessions was needed – consideration can only be done in history with time gap of some hours, problematic sessions have been terminated for long time Solution: Create sampling process with minimal resource impact to CPU and I/O with result storage in database table. Preparation Create two tables, one for result storage and one for abortion of sampling process: CREATE TABLE SessMon_Semaphore (Name VARCHAR2(30)); COMMENT ON TABLE SessMon_Semaphore IS 'Control abortion of sampling process'; COMMENT ON COLUMN SessMon_Semaphore.Name IS 'Record identifier to store record lock on it...

Create SQL trace file for systems with connection pool If using connection pools the default SQL_TRACE=TRUE does not help because the DB-connection should only be traced if used for a special considered application. For identification of application you can use the context info set by DBMS_Application_Info.Set_Module. Trace the run of an single application For a special module info you can specify creation of trace file already before execution of this application. Setting trace config has to be done as SYSDBA. In addition you can set parameters for "waits" and "binds", default is FALSE. Examples Activate trace for application by module with recording of bind variables exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => 'SYS$USERS', module_name => 'ID_Application = 56', binds => TRUE); Deactivate trace for module exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => 'SYS$USERS', module_n...