SQL Performance Tuning

Oracle PeopleSoft SQL Performance Tuning

PeopleSoft Trace Settings

By hiojay | December 12, 2012July 9, 2013 by hiojay

Update: I have changed this post based on Graham’s recent comments regarding Trace Magic since the trace settings that I recommended were not actually compatible with Trace Magic. Please see the following link for information regarding Trace Magic: https://communities.oracle.com/portal/server.pt?open=514&objID=224&mode=2&threadid=382561 However, when manually reading a trace file (e.g. in Sublime Text, Notepad++, etc.) I would recommend the following trace settings: -TRACE 135 -TOOLSTRACESQL 31 -TOOLSTRACEPC 4044 I still find these settings useful for non-performance related debugging issues.

Oracle SQL Performance Tuning

Composite / Concatenated SQL Indexes

By hiojay | October 15, 2010October 15, 2010 by hiojay

When a single index has multiple columns that are indexed, it is called a composite or concatenated index. Prior to the introduction of skip-scan functionality, queries could only use the index if the leading column of the index was used in the WHERE clause. Consider the example in the following listing where the EMP table has a concatenated index on Empno, Ename, and Deptno. Note that Empno is the first part, Ename is the second…