Use This 11g Workaround to Bypass Statspack Bug

If you have an 11gR2 database (we’ve seen it on 11.2.0.2 databases although it may be present in 11.2.0.3 as well) and you get this error when trying to run a Statspack snap: ORA-00001: unique constraint (PERFSTAT.STATS$FILE_HISTOGRAM_PK) violated ORA-06512: at “PERFSTAT.STATSPACK”, line 3813 ORA-06512: at “PERFSTAT.STATSPACK”, line 5079 ORA-06512: at “PERFSTAT.STATSPACK”, line 105 ORA-06512: at […]

Unindexed foreign keys & enq:TM – contention

I was recently reminded of the chaos that can be caused by an unindexed foreign key column when there are corresponding updates or deletes from the parent table. To illustrate this particular situation here is some example DDL CREATE TABLE parent_t ( parent_id INTEGER NOT NULL, parent_name VARCHAR2(30) NULL, CONSTRAINT parent_t_pk PRIMARY KEY (parent_id) ); […]

Use SQLT to diagnose difficult SQL performance issues

 SQLT, also known as SQLTXPLAIN is a tool provided by Oracle Server Technologies Center of Expertise that can be used to diagnose why a particular SQL statement is performing poorly.  It is not like AWR or Statspack which provide a system-wide view of performance.  SQLT is very focused and works on one SQL statement at […]

A Huge (Pages) Performance Problem

A recent late-night incident at a client site involved a database with extreme performance problems following a storage system failure and subsequent database restart. The client observed excessive swapping and paging with the run queue length breaking triple digits and the server and database becoming virtually unresponsive.  The server had not been restarted and the […]