Peter Ramm's Oracle performance analysis stuff

A problem using interval partitions may be that the number of partitions may not exceed 1.048.575 (1024*1024-1).  If using interval partitions not only the physical partitions count. Instead, the number of possible partitions between the required first range partiton and the last interval partition counts for the limit (in consideration of the used interval).  If you exceed the limit, ORA-14300 (partitioning key maps to a partition outside maximum permitted number of partitions) is raised .  So if you have a rolling window with interval partitions, where new interval partitions at the upper bound are created and older partitions are dropped if no longer needed, you may run into ORA-14300 if your initially created range partition becomes to far away from the upper bound of interval partitions.  A possible solution should be to increase the high value of the initial range partition to lower the distance to the highest interval partition.  But how to do that? ...

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have ...

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...

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

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

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data with 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins p.s.: More effective than this solution may be caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS -------------------------------------------------------------------------------- -- Author : %$LastChangedBy: arosenfe $derived...

frequent access on small tables includes risk of hot blocks in db-cache, especially if tables are mass-joined by nested loop operations Joining this master tables by hash joins reduces number of block access in db cache, but requires large data transfer for hash operations and possibly access to TEMP tablespace Often these master data may be cached in local DB session, for session pooling environment or long living sessions in addition with content aging for guaranteed currentness of data This example shows a PL/SQL package for caching of table data without 11g-feature “function result caching” For usage runtime measuring should be done to decide between these caching or usage of hash joins From 11g function result caching may be used instead of this solution p.s.: More effective than this solution is caching master data within application layer without access to PL/SQL CREATE OR REPLACE PACKAGE Cache_TableAlias AS ----------------------------------------------------------...